Part A:
Normalisation
1. Using the table and data given below, normalise the table to 3NF.
2. Use correct dependency diagram's to show each normalisation level.
3. Create an ERD to show the resultant tables at the 3NF level with all the needed attributes, Primary/Foreign keys etc.
|
Trainor ID
|
Trainer Name
|
Room No
|
Room Seats
|
Class Session
|
Class Namo
|
Class Day
|
School
|
|
101
|
Alamo Fredrick
|
54A
|
36
|
9:00
|
ITC114
|
Mon
|
Computing
|
|
102
|
George Edwards
|
22
|
28
|
9:00
|
ITC333
|
Mon
|
Computing
|
|
|
103Liliy
Nely
|
59
|
38
|
12:30
|
8US350
|
Tues
|
Business
|
|
104
|
Eddy Brown
|
53
|
30
|
12:30
|
ITC114
|
Thurs
|
Computing
|
|
102
|
George Edwards
|
59
|
38
|
4:00
|
ITC504
|
Fri
|
Computing
|
|
1031
|
illy Noly
|
18
|
24
|
12:30
|
8US360
|
Wedn
|
Business
|
|
102
|
George Edwards
|
53
|
30
|
9:00
|
ITC201
|
Wedn
|
Computing
|
|
105-Sam Wang
|
22
|
28
|
4:00
|
ITC556
|
Tues
|
Computing
|
|
106Yifene Chong
|
58
|
38
|
9:00
|
ITC558
|
Thurs
|
Computing
|
Part B: Modify existing ERD and Create additional entities as required
At a later interview with the manager you learnt that 11TH refers to each customer request as a 'SERVICE' and they keep logs of each service and what actual work is requested by the customer at the time of their call - that is because a customer may request more than one job that may even be in different trade or specialisation area. For example, a customer may have a leaking tab in the bathroom, a flickering light in the kitchen, and/or some broken roof tiles that need fixing.
Customers have the option to make partial payments for a service, as long as they pay no later than one week after the completion of any service. Details about each payment relating to a service, amount of payment and date need to be recorded.
Customers may know about HTH through any of these three (3) sources: A friend, Internet search, or a Flyer that was distributed in a recent promotional campaign. HTH need to track the source that referred each customer.
Modify your ERD according to the new information you have received, as the above business rules/constraints.
Ensure that you use the ERD solution from previous assessment 2A as a guide (this will be uploaded on Interact at the end of week 7), then add any necessary entities, relationships, primary/foreign keys, etc.
Part C:
Create the RDM with appropriate attributes, primary and foreign keys, based on all the entities following the format given below:
STAFF (idstaff, firstName, middName, lastName, addressLinel, addressLine2, suburb, state, postcode, contactNumber, gender, idCentre) PRIMARY KEY (idStaff)
FOREIGN KEY idCentre REFERENCES Centre;