8. An agency called Instant Cover supplies part-time/temporary staff to hotels i

Question

8. An agency called Instant Cover supplies part-time/temporary staff to hotels in Scotland. Figure 12.4 lists the time spent by agency staff working at various hotels. The national insurance number (NIN) is unique for every member of staff. Use Figure 12.4 to answer questions (a) and (b) a. This table is susceptible to update anomalies. Provide examples of insertion, deletion and update anomalies. b. Normalize this table to third normal form. State any assumptions NIN ContractNo Hours eName 1135 C1024 1057C1024 1068 C1025 1135 |C1025 hNo hLoc H25 East Killbride 16 24 28 15 Smith J. Hocine D. H25 East Killbride White T. Smith J. H4 Glasgow H4Glasgow Figure 12.4 For question 8, by A. Watt.

Explanation / Answer

a. Anomalies:

Insertion Anomaly --- If a new member needs to insert, along with NIN and eName contractNo,hNo and hLoc needs to be inserted also even if the employee does not have any contract and is assigned hotel.

Updation Anomaly --- if the contract hours need to be updated for a member , it needs to be updated for all the replicated entries for that contract No and NIN. If it is not done,inconsistent data exists in the table.

Deletion Anomaly --- If a member quits his job, the details of hotel also get deleted.If the hotel is assigned to only that member, the information about the hotel will be lost.

b. Normalization

1NF(all attributes should be atomic)

HotelContract(NIN,ContractNo,Hours,eName,hNo,hLoc)

2NF( All non key attributes should be fully functionally dependent on the primary key)

NIN -> eName

NIN,ContractNo -> Hours

hNo -> hLoc

so tables in 2NF are:

Employee(NIN, eName)

EmployeeContract(NIN,ContractNo , Hours,hNo)

Hotel(hNo, hLoc)

underlined are primary keys and italicized are foreign keys. Some attributes are both

3NF

All the tables in 2NF are also in 3NF as there is no transitive dependency.

Do ask if any doubt. Please upvote.

Related Questions

Navigate

• Browse (All)
• Browse 8
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.