Given the set of functional dependencies F {fd1, fd2, fd3, fd4, fd5, fd6, fd7, fd8, fd9, fd10} introduced originally in Chapter 7, Exercise 14:
fd1: Tenant#  {Name, Job, Phone#, Address} fd2: Job  Salary

a. Using the universal relation schema (URS) and its associated primary key developed in Chapter 7, Exercise 14, indicate the immediate normal form violated in each of the functional dependencies given above and explain how the particular normal form is violated in each case.
b. If the URS is not in BCNF, design a relational schema that:
€¢ is in BCNF so that all anomalies due to functional dependencies are eradicated, and
€¢ yields all lossless-join decompositions
c. List the functional dependencies in F that are not preserved in this design.
d. Show the final design. The design should be parsimonious (i.e., minimal set in BCNF). Also, clearly indicate entity integrity and referential integrity constraints.
e. Revise the above design so that all dependencies are preserved in a lossless-join decomposition with the least sacrifice in the achieved level of normal form.
f. Reverse engineer the design to the conceptual level and show it as a Presentation Layer ER diagram.

Transcribed Image Text:

fd3: Name → Gender fas: Name. Phone#} → (Tenant, Deposit) fd7: Area → {Rent. County} fd9: {Lot. County} {Survey#. Area) fd4: Phone# → Address fd6: County Tax rate fd8: Survey#-) Lot | fd10: {Surveys, Area) -) County