CS计算机代考程序代写 Functional Dependencies Exercise 3

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.