Description

Introduction

This coursework is to be attempted individually. You are not allowed to work with anyone else during the completion of this coursework. You must submit your work to Google classroom on or before the due date/time.

Learning Outcome to be assessed

1.    Explain model 'relationships' and apply 'normalization' when constructing databases.

2.    Query in both native Access and SQL syntax.

Detail of the task:

Task 1

1. Using the information in Figure A, you are required to normalize from UNF until 3NF. State any assumptions/justifications for the design decisions that you make.

Figure A

Task 2

You are given the following business scenario.

SmartService is a newly setup car repair and servicing workshop which is located at Kuala Lumpur’s Golden Triangle. The strategic location, customer oriented services and reasonable price are the main reasons of the workshop gain popularity within a short period of time. There is an average of 50 cars daily are being sent to the workshop for repair or/and servicing.  SmartService has a car servicing appointment system to manage their customers’ appointments. SmartService requires their customers to make an appointment before they send in their car for repair or/and servicing. This is to prevent customers waiting for a long period of time before being served by the staff.  In addition, the workshop is also able to send a text message or email reminder to their customer on the car servicing appointment. The workshop also allows the customers to cancel their servicing appointment for any reason(s).  

The car servicing appointment system contains a mini database to assist the management of customers’ appointment.  The mini database complies with the following business rules:

i.           A customer can make many appointments but each appointment made by one customer.

ii.          An appointment is tied to a car, but a car can be tied with many appointments.

iii.         An appointment involves different kinds of repairing and servicing works. Each work can be applied to many appointments. 

The current mini database is expected to be extended to support more business function. One of the proposals made is to keep track of the staff information. The following is sample of a staff information plan to be recorded. 

SmartService is expected to have continuous growth as the management sense the importance of using the computerized system to assist the workshop for their daily operation.

Your task includes:

1.    Produce and Entity Relationship Diagram (ERD) using Crow’s Foot Notation, which illustrates appropriate entities, relationships, primary keys and foreign keys. 

2.    Construct SQL statement for the following.

a.    Create a database name SmartDB in order to implement the application database.

b.    Create a table named Supplier to keep the information show in the above business scenario.  

c.    Add the information shown in Table 1 into the Supplier table.

d.    Add a new column called LastUpdate into the Supplier table. The field is used to record the latest date that the Supplier record which has been modified.

e.    Remove the Supplier table from the database.

3.    The following is the item data which plan to be stored in the SmartService’s mini database. 

                                                                                 Table 2

Construct SQL statement for the following.

a.    List all item records and arrange the records based on the unit price in descending order.

b.    List all items with the price range of 50 to 100.

c.    List the items who with the store unit at most 5. Specify the item name and unit price.

d.    What is the average unit price for the items for each of the suppliers?

e.    Increase the unit price for each item by 10%