Skills needed:

Given a non-normalized Relation (Table) and a set of functional dependencies FDs, analyze the FDs to normalize the relation up to 3NF or BCNF to avoid anomalies:

1- Identify determinants from FDs

2- Identify types of FDs and draw FDD for a relation.

3- Identify types of FDs required to be eliminated from a table to be in the desired NF

4- Identify Primary Keys in the relations based on FDs

5- Identify the attribute to remove from one relation to a new relation

6- Identify the Foreign Keys when partitioning the relation

7- Write SELECT queries to reconstruct the original relation from the normalized relations

Tasks: The following XyzMaintenance table is used by a XyzHotel to keep track of room inspections, fixes, and information of staff who worked on these inspections.

Task 1:

a) This table is susceptible to modification anomalies. Provide examples of insertion, deletion, and update anomalies.

b) List The Functional Dependencies of the relation schema of the table XyzMaintenance (RoomNo, RoomFeatures, InspectionDate, InspNo, StaffNo, StaffName, Specialty, StaffPhone, RoomIssues, StaffNotes)

c) Draw FDD (Functional Dependency Diagram) of the relation XyzMaintenance showing the type of each Functional Dependency (Partial, Full, Transitive).

d) what are the Keys of the relation and their type (Primary, candidate)

Task 2:

Apply the normalization process Up to 3NF on XyzMaintenance. State why the relation is not in each normal form first, then apply the normalization process and Explain your steps using relation notations only (examples are not required) and show the keys in each new relationship.

Task 3:

BCNF: Transform the relations (if required) resulted from the previous Task (task 3) into BCNF. Show the FDs of each relation first, then check if the relation satisfies BCNF or not. If not, then transform it into BCNF

That is all the information I have. Need to know specifically what else is required?

RoomNo 225 Inspection Date 03/02/15 inspNo 1 StaffNo 16 StaffName John Doe Specialty Carpenter StaffPhone 333-123-4444 Room Features 32" TV, Fridge, 2 Queen Beds Microwave StaffNotes Lock fixed 06/015/15 1 12 Tracy C. Electrician 666-525-5555 TV power line replaced 666-525-5555 2 1 12 12 Tracy C. Tracy C. Electrician Electrician 126 666-525-5555 45" TV, Fridge, 1 04/11/15 King Bed, Coffee Roomissues Front door lock TV not working None Bathroom lights not working Bed headboard unstable No Hot water Bathroom light bulbs replaced Headboard fixed 2 16 John Doe Carpenter 333-123-4444 115 03/02/15 1 10 Mike K Plummer 32" TV, 2 Queen Beds 333-123-6789 Heating element changed RoomNo 225 Inspection Date 03/02/15 inspNo 1 StaffNo 16 StaffName John Doe Specialty Carpenter StaffPhone 333-123-4444 Room Features 32" TV, Fridge, 2 Queen Beds Microwave StaffNotes Lock fixed 06/015/15 1 12 Tracy C. Electrician 666-525-5555 TV power line replaced 666-525-5555 2 1 12 12 Tracy C. Tracy C. Electrician Electrician 126 666-525-5555 45" TV, Fridge, 1 04/11/15 King Bed, Coffee Roomissues Front door lock TV not working None Bathroom lights not working Bed headboard unstable No Hot water Bathroom light bulbs replaced Headboard fixed 2 16 John Doe Carpenter 333-123-4444 115 03/02/15 1 10 Mike K Plummer 32" TV, 2 Queen Beds 333-123-6789 Heating element changed