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.
SNo |
: |
1 |
SName |
: |
Impress Accessories |
SAddress |
: |
23, Jalan
Ampang, |
Postcode |
: |
50450 |
City |
: |
KL Table 1 |
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.
No |
ItemName |
UnitPrice |
StoreUnit |
SNo |
P1 |
Fully
Synthetic Engine Oil (3L) 0W20 – Axia |
90.00 |
10 |
5 |
P2 |
Long Life
Collant (1L) |
20.00 |
15 |
2 |
P3 |
Air filter
– Myvi & Bezza |
18.00 |
15 |
5 |
P4 |
Leather
Steering Wheel Cover |
85.00 |
5 |
1 |
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%
Get Free Quote!
363 Experts Online