Reference no: EM13703939

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.

Trainer_ID	Trainer_Name	Room_No	Room_Seats	Class_Session	Class_Name	Class_Day	School
101	Alana Fredrick	54A	36	9:00	ITC114	Mon	Computing
102	George Edwards	22	28	9:00	ITC333	Mon	Computing
103	Lilly Nelly	59	38	12:30	BUS350	Tues	Business
104	Eddy Brown	53	30	12:30	ITC114	Thurs	Computing
102	George Edwards	59	38	4:00	ITC504	Fri	Computing
103	Lilly Nelly	18	24	12:30	BUS360	Wedn	Business
102	George Edwards	53	30	9:00	ITC201	Wedn	Computing
105	Sam Wang	22	28	4:00	ITC556	Tues	Computing
106	Yifeng Chong	58	38	9:00	ITC556	Thurs	Computing

Part B: Modify existing ERD and Create additional entities as required

Use the solution guide from Assessment Item 2 to complete this part: This will be released to you on Interact under week 6.

At a later interview with the manager you learnt that HTH 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, addressLine1, addressLine2, suburb, state, postcode, contactNumber, gender, idCentre)

PRIMARY KEY (idStaff)

FOREIGN KEY idCentre REFERENCES Centre;