– Part III
29
Chapter 15 Advanced Normalization
Review Questions
15.1 Describe the purpose of using inference rules to identify functional dependencies for a given
relation.
Even if we restrict our attention to nontrivial functional dependencies with one-to-one (1:1)
relationships that hold for all time, the complete set of functional dependencies for a given
15.2
The set of all functional dependencies that are implied by a given set of functional
(1) Reflexivity: If B is a subset of A, then A B
Note that each of these three rules can be directly proved from the definition of functional
dependency. The rules are complete in that given a set X of functional dependencies, all
(See Section 15.1)
To begin to identify the set of functional dependencies F for a relation, typically we first
identify the dependencies that are determined from the semantics of the attributes of the
– Part III
30
15.3 Discuss the purpose of Boyce-Codd Normal Form (BCNF) and describe how BCNF differs
from 3NF. Provide an example to illustrate your answer.
BCNF is based on functional dependencies that take into account all candidate keys in a
15.4 Describe the concept of multi-valued dependency and describe how this concept relates to
4NF. Provide an example to illustrate your answer.
Multi-valued Dependency (MVD) Represents a dependency between attributes (for
15.5 Describe the concept of join dependency and describe how this concept relates to 5NF.
Provide an example to illustrate your answer.
– Part III
31
Exercises
15.6 On completion of Exercise 14.14 examine the 3NF relations created to represent the attributes
shown in the Wellmeadows Hospital form shown in Figure 14.18. Determine whether these
relations are also in BCNF. If not, transform the relations that do not conform into BCNF.
15.7 On completion of Exercise 14.15 examine the 3NF relations created to represent the attributes
shown in the relation that displays dentist/patient appointment data in Figure 14.19. Determine
whether these relations are also in BCNF. If not, transform the relations that do not conform into
BCNF.
The only relations that may violate BCNF are those that have more than one candidate key.
32
15.8 On completion of Exercise 14.16 examine the 3NF relations created to represent the attributes
shown in the relation displaying employee contract data for an agency called Instant Cover in
Figure 14.20. Determine whether these relations are also in BCNF. If not, transform the relations
that do not conform into BCNF.
15.9 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.
Figure 15.11 The WardStaffPatient relation.
(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.
– Part III
33
15.10 The relation shown in Figure 15.12 describes hospitals (hospitalName) that require certain
items (itemDescription), which are supplied by suppliers (supplierNo) to the hospitals
(hospitalName). Furthermore, whenever a hospital (h) requires a certain item (i) and a
supplier (s) supplies that item (i) and the supplier (s) already supplies at least one item to that
hospital (h), then the supplier (s) will also be supplying the required item (i) to the hospital
(h). In this example, assume that a description of an item (itemDescription) uniquely
identifies each type of item.
Figure 15.12 The HospitalItemSupplier relation.
(a) Describe why the relation shown in Figure 15.12 is not in 5NF.
(b) Describe and illustrate the process of normalizing the relation shown in Figure 15.12
to 5NF.