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)
You might also like to view...
Which of the following masks is appropriate for a masked text box into which the user will enter a license plate consisting of two digits followed by 4 letters?
(A) &&LLLL (B) &&&&&& (C) 00LLLL (D) None of the above
Answer the following statements true (T) or false (F)
1. In Emacs, deleting text or killing text means the same thing. 2. One way to exit Emacs is to turn the terminal off. 3. Point and Mark are used to indicate the boundaries of the section (region) of the ext to be rearranged. 4. While in Emacs, you can open more than one file. 5. The commands for search forward and backward for occurrences of a specified text are [Ctrl-s] and [Ctrl-r] accordingly.
iWork is the ________ package made especially for Apple computers.
What will be an ideal response?
When exporting from Access to Excel, you can use an Access table or ________.
Fill in the blank(s) with the appropriate word(s).