Exercise 3
Question 1
Consider a relation R(A,B,C,D,E) with the following dependencies:
AB -> C
CD -> E
DE -> B
Is AB a candidate key of this relation? If not, is ABD? Explain your answer.
Question 2
Consider the relation R, which has attributes that hold schedules of courses and sections at a
university; R = {Course_no, Sec_no, Offering_dept, Credit_hours, Course_level, Instructor_ssn,
Semester, Year, Days_hours, Room_no, No_of_students}. Suppose that the following functional
dependencies hold on R:
{Course_no} → {Offering_dept, Credit_hours, Course_level}
{Course_no, Sec_no, Semester, Year} → {Days_hours, Room_no, No_of_students, Instructor_ssn}
{Room_no, Days_hours, Semester, Year} → {Instructor_ssn, Course_no, Sec_no}
Try to determine which sets of attributes form keys of R.
Question 3
Consider the following relation for published books:
BOOK (Book_title, Authorname, Book_type, Listprice, Author_affil, Publisher)
Author_affil referes to the affiliation of the author. Suppose the following dependencies exist:
Book_title -> Publisher, Book_type
Book_type -> Listprice
Author_name -> Author-affil
(a) What normal form is the relation in? Explain your answer.
(b) Decompose the relation into a set of 3NF relations if it is not in 3NF.
Question 4
Consider the relation REFRIG (MODEL#, YEAR, PRICE, MANUF_PLANT, COLOR), which is
abbreviated as REFRIG(M, Y, P, MP, C), and the following set of F of functional dependencies:
F={M -> MP, {M,Y} -> P, MP -> C}
(a) Evaluate each of the following as a candidate key for REFRIG, giving reasons why it can or
cannot be a key: {M}, {M,Y}, {M,C}
(b) Based on the above key determination, state whether the relation REFRIG is in 3NF and in BCNF,
giving proper reasons.
(c) Consider the decomposition of REFRIG into D={R1(M,Y,P), R2(M,MP,C)}. Is this
decomposition lossless? Show why.
Question 5
Consider a relation 𝑅(𝐴, 𝐵, 𝐶, 𝐷, 𝐸, 𝐺, 𝐻) and its FD set 𝐹 = {𝐴𝐵 → 𝐶𝐷, 𝐸 → 𝐷, 𝐴𝐵𝐶 → 𝐷𝐸, 𝐸 →
𝐴𝐵, 𝐷 → 𝐴𝐺, 𝐴𝐶𝐷 → 𝐵𝐸}. Answer the following questions and justify your answers.
1) List all the candidate keys for 𝑅.
2) Determine the highest normal form of 𝑅 with respect to 𝐹.
3) Is the decomposition {𝐴𝐵𝐶𝐷, 𝐷𝐸𝐺𝐻} (with the same FD set 𝐹) of 𝑅 lossless-join?
4) Find a minimal cover 𝐹𝑚 for 𝐹.
5) Decompose into a set of 3NF relations if it is not in 3NF. Make sure your decomposition is
dependency-preserving and lossless-join.
6) Decompose it into a collection of BCNF relations if it is not in BCNF. Make sure your
decomposition is lossless-join.