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.

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;