CSCI 5333 DBMS
Spring 2020
Homework #8

Normalization Theory

(1) Use Armstrong's axioms and rules to prove that

F = {A->B, CD->E, AC->D, E->F}

implies AC-> F

(2) Consider R(P, Q, R, S, T, U) with

F = {RS->PQ, QR->S, S->P, U->ST, STU->R, Q->U}

(a) What are P+, Q+, R+, S+, T+, U+?

(b) What are the candidate keys? Why?

(c) Show all prime attributes and non-prime attributes?

(d) Give a canonical cover of F?

(e) What is the highest normal form (up to BCNF) of R? Why?

(f) If R is not in BCNF, can you provide a lossless FD preserving decompositions of R into BCNF relations?

(3) Consider R(A, B, C, D, E) with

F = {A->BCE, BC->D, AB->E, DE->C, AE->CD}

(a) What are A+, B+, C+, D+ and E+?

(b) What are the candidate keys? Why?

(c) Show all prime attributes and non-prime attributes?

(d) Give a canonical cover of F?

(e) What is the highest normal form (up to BCNF) of R? Why?

(f) If R is not in BCNF, can you provide a lossless FD preserving decompositions of R into BCNF relations?

(4) Prove or disprove the following statements.

(a) A relation R(A,B,C) can only have one minimal cover.

(b) If R(A,B,C) is in 2NF and has only one simple candidate key, it is also in 3NF.

(c) For R(A,B,C,D), it is known that AB and BC are candidate keys (and there may be others). It is possible that R may have 12 superkyes.

(d) If R(A,B,C) is in 3NF, it is also in BCNF.

(5) It is known that R(A,B,C,D,E) has exactly two candidate keys. Furthermore, one of the candidate key is known to be AB. What are the maximum and minimum number of superkeys R may have?

(6) Given R(A,B,C,D,E) {A->C, BC->D, AC->D, BD->E}

It is decomposed into R1(A,C,D), R2(B,C,D) and R3(B,D,E).

Is the decomposition lossy? Prove your assertion.

As usual, submit your homework through Blackboard using the file name <<Yourname>>_<<YourStudentNumber>>_h8.docx.