Consider the relational schema R(A, B, C, D). For each of the following functional dependencies identify the candidate key(s) for R and state its strongest normal form. If appropriate, decompose to BCNF.

Question

(a) B ? C, C ? A, C ? D
(b) B ? C, D ? A
(c) ABC ? D, D ? A
(d) A ? B, A ? C, BC ? D
(e) AB ? C, AB ? D, C ? A, D ? B

Answer 1

(a) Candidate key is B. Relation is in 2NF but not 3NF (C ? A, C ? D violate BCNF). Decompose to: AC, BC, and CD.
(b) Candidate key is BD. Relation is in 1NF but not 2NF (both FDs violate BCNF).
Decompose to: AD, BC, and BD.
(c) Candidate keys are ABC and BCD. Relation is in 3NF but not BCNF (D ? A and D is not a key). However, if we split R into AD and BCD we cannot preserve the first dependency, so there is no BCNF decomposition.
(d) Candidate key is A. Relation is in 2NF but not 3NF (because of FD BC ? D). This FD also violates BCNF since BC does not contain a key, so decompose to: BCD and ABC.
(e) Candidate keys are AB, BC, CD, and AD. Relation is in 3NF but not BCNF. First
decompose to: AC, BCD; this does not preserve AB ? C, AB ? D, and BCD is still
not in BCNF because of FD D ? B. Decompose further to: AC, BD, CD, ABC, and
ABD.