The relation shown in Figure 15.11 lists members of staff (staffName) working in a given ward (wardName) and patients (patientName) allocated to a given ward. There is no relationship between members of staff and patients in each ward. In this example assume that staff name (staffName) uniquely identifies each member of staff and that the patient name (patientName) uniquely identifies each patient.

(a) Describe why the relation shown in Figure 15.11 is in BCNF and not in 4NF.

(b) The relation shown in Figure 15.11 is susceptible to update anomalies. Provide

examples of insertion, deletion, and update anomalies.

(c) Describe and illustrate the process of normalizing the relation shown in Figure 15.11 to 4NF.

A. wardName ???? staffName
wardName ???? patientName
Relation is in BCNF but there is a nontrivial multi-valued dependency in the relation, so relation is not in 4NF.
B. If we wanted to insert a new patient name, would have to add two records, one for each member of staff. If we wanted to update the name of patient Claire Johnson, we would have to update two records. If we wanted to delete the record corresponding to patient Claire Johnson, we would have to delete two records.
C. To remove the MVD, we create two new relations:
WardStaff (wardName, staffName)
WardPatient(wardName, patientName)