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...
Which of the following statements is false?
a. Individuals who have no sense of right or wrong exhibit amoral behavior. b. Ethical decisions are usually influenced by personal ethics. c. Unethical behavior is always illegal. d. Life experience affects an individual's personal ethics.
Each chart type has predefined layouts that you can quickly apply to your chart
Indicate whether the statement is true or false
Cross-references are included in an index
Indicate whether the statement is true or false
Which web attack is possible due to a lack of input validation?
A. Extraneous files B. Clickjacking C. SQL injection D. Cross-site request forgery