An agency called Instant Cover supplies part-time/temporary staff to hotels with
ID: 3866631 • Letter: A
Question
An agency called Instant Cover supplies part-time/temporary staff to hotels within Scotland.
The table shown in Figure 1 lists the time spent by agency staff working at various hotels. The National Insurance Number (NIN) is unique for every member of staff.
Figure 1: Instant Cover’s contracts
The table shown in Figure 1 is susceptible to update anomalies. Provide examples of insertion, deletion, and modification update anomalies.
List the non-trivial functional dependencies in the table shown in Figure 1. State any assumptions you make about the data shown in this table.
Modify the table to be in Boyce-Codd normal form.
Display the data in Figure 1 using the tables in Boyce-Codd normal form.
NIN contractNo hours eName hNo hLoc 1135 C1024 1057 C1024 1068 CI025 1135 C1025 16 Smith H25 East Kilbride 24 Hocine D H25 East Kilbride 28 White T H4 Glasgow 15 Smith H4 GlasgowExplanation / Answer
Anomalies in the table:
Insertion anomaly
If a part time staff is available but he is not assigned any hotel, we have values for NIN and eName , but contractNo,hours , hNo and hLoc are empty in this case. So we cannot insert a new row in the table.
deletion anomaly
If NIN of an Employee is deleted from table when he finishes his work, the hNo and hLoc also gets deleted. So if an employee finishes his job, the hotel number and location gets deleted . If there is only one employee assigned to that hotel, the hotel information gets lost.
Updation anomaly
If hLoc changes for a hotel it needs to be changed for all hNo. So If hotel is moved to another location its address needs to be changed for different rows.
Functional dependencies
NIN -> eName
NIN,contractNo -> hours
hNo -> hLoc
BCNF
Employee(NIN,eName)
CONTRACT(NIN,contractNo,hours)
HOTEL(hNo,hLoc)
EMPLOYEE
CONTRACT
HOTEL
NIN eName 1135 Smith J 1057 Hocine D 1068 White TRelated Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.