Page 1 of 2
DB - Assignment # 3
Submit only through Google Classroom. No email submissions accepted. No deadline extensions.
Question 1: An agency called Instant Cover supplies part-time/temporary staff to hotels within Scotland. The table
shown below, lists the time spent by agency staff working at various hotels. The National Insurance Number (NIN)
is unique for every member of staff.
a. The table shown above is susceptible to update anomalies. Provide examples of insertion, deletion, and
update anomalies.
b. Describe and illustrate the process of normalizing the table shown above to 3NF, by identifying the
functional dependencies represented by the attributes. State any assumptions you make about the data
shown in the table.
Question # 2: Examine the Patient Medication Form for the Wellmeadows Hospital shown below:
a. Identify the functional dependencies represented by the attributes shown in the form above. State
any assumptions that you make about the data and the attributes.
b. Describe and illustrate the process of normalizing the attributes shown in the form above to
produce a set of well-designed 3NF relations.
c. Identify the primary, alternate, and foreign keys in your 3NF relations.
NIN contractNo hours eName hNo hLoc
1135 C1024 16 Smith J H25 East Kilbride
1057 C1024 24 Hocine D H25 East Kilbride
1068 C1024 28 White T H4 Glasgow
1135 C1024 15 Smith J H4 Glasgow
Page 2 of 2
Question # 3:
Good Luck
