Reference no: EM13708216

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;