Question

Consider the relation R with four attributes ABCD. For each of the two following sets of FDs, determine whether or not the R would be in BCNF, and if it is not, then decompose it into BCNF, and prove the result would produce a lossless join. 1. A → B,BC → D, A →C 2. AB → C,AB → DC → A, D + B

Answer 1

1.

FDs : A -> B , BC -> D, A -> C

From the given FDs, we can determine that A is the candidate key. For a relation to become BCNF, left hand side of the FD should be super key. But, in BC -> D, BC is not super key. Therefore, R is not in BCNF.

BC -> D is a transitive dependency ( non-prime attribute -> non-prime attribute form) , So, (BC)⁺ will form a new relation. The new relation R1 is (B, C, D) with FDs {BC->D}. and R is (ABC) with FDs {A->B , A->C }.

The above decomposition is in BCNF and loss less join because common attributes (B,C) is candidate key in R1.

2.

Given FDs :

AB -> C, AB -> D, C-> A, D -> B

For the given FDs, we can determine that AB, AD, BC, CD are candidate keys. Since, in C -> A and D -> B , C and D are not superkeys, the given relation is not in BCNF.

C -> A and D -> B are partial dependencies. So, C⁺ and D⁺ for new relations.

New relations are R1(C, A) and R2(D, B) and R ( C, D) .

R1(C, A) will have { C -> A }

R2(D, B) will have { D -> B }

and R(C, D) will have CD as candidate key.

This decomposition is in BCNF but not lossless join.