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.
(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.
A. This relation has a join dependency JD(hospitalName, itemDescription, supplierNo) among the three projections: R1(hospitalName, itemDescription), R2(hospitalName, supplierNo), and R3(itemDescription, supplierNo) of HospitalItemSupplier.
B. To remove the join dependency, we create the following 5NF relations:
HospitalItem(hospitalName, itemDescription)
HospitalSupplier(hospitalName, supplierNo)
ItemSupplier(itemDescription, supplierNo)
You might also like to view...
The search tools in the Search screen in Windows 8 are more full-featured than the tools in File Explorer
Indicate whether the statement is true or false
Which of the following is NOT an important factor to consider by a manager of a supermarket trying to schedule checkout cashiers?
A. the frequency with which new customers arrive B. the number of checkout cashiers available C. the number of items in a customer's shopping cart D. the number of aisles in the supermarket