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)