1. Consider a relation R(A,B,C,D,E,F,G,H) with the following functional dependencies:
A -> BCD
AD -> E
EFG -> H
F -> GH
(a) Based on these functional dependencies, there is one minimal key for R. What is it?
(b) One of the four functional dependencies can be removed without altering the key. Which one?
2. Consider the following relational schema:
UnivInfo(studID, studName, course, profID, profOffice)
Each tuple in relation UnivInfo encodes the fact that the student with the given ID and name took the given course from the professor with the given ID and office. Assume that students have unique IDs but not necessarily unique names, and professors have unique IDs but not necessarily unique offices. Each student has one name; each professor has one office.
(a) Specify a set of completely nontrivial functional dependencies for relation UnivInfo that encodes the assumptions described above and no additional assumptions.
(b) Based on your functional dependencies in part (a), specify all minimal keys for relation UnivInfo.
(c) Is UnivInfo in Boyce-Codd Normal Form (BCNF) according to your answers to (a) and (b)? If not, give a decomposition of UnivInfo into BCNF.
(d) Now add the following two assumptions: (1) No student takes two different courses from the same professor; (2) No course is taught by more than one professor (but a professor may teach more than one course). Specify additional functional dependencies to take these new assumptions into account.
(e) Based on your functional dependencies for parts (a) and (d) together, specify all minimal keys for relation UnivInfo.
(f) Is UnivInfo in BCNF according to your answers to (d) and (e)? If not, give a decomposition of UnivInfo into BCNF.