FDS

79

Consider a relation R(A, B, C, D), with FDs AB -> C, BC -> D, CD -> A.
(a) Find the closure of AB.
(b) Find candidate keys.
(c) find the normal form of relation R. If the relation is not in BCNF then convert into BCNF?
R(A, B, C, D) with functional dependencies (FDs) AB -> C, BC -> D, CD -> A.

(a) Find the closure of AB:
Closure of AB: {AB}
Using AB -> C, we can add C to the closure: {ABC}
Now, using BC -> D, we can add D to the closure: {ABCD}
Finally, using CD -> A, we can add A to the closure: {ABCDA}

So, the closure of AB is {ABCDA}.

(b) Find candidate keys:
A candidate key is a minimal set of attributes that can uniquely identify each tuple in the relation. To find candidate keys, we can use the closure of different sets of attributes.

Candidate key including A:
Closure of A: {A}
Since {A} is not equal to the set of all attributes {ABCD}, A alone is not a candidate key.

Candidate key including B:
Closure of B: {B}
Since {B} is not equal to {ABCD}, B alone is not a candidate key.

Candidate key including C:
Closure of C: {C}
Since {C} is not equal to {ABCD}, C alone is not a candidate key.

Candidate key including D:
Closure of D: {D}
Since {D} is not equal to {ABCD}, D alone is not a candidate key.

Candidate key including AB:
Closure of AB: {ABCDA} (as found earlier)
Since {ABCDA} is equal to {ABCD}, AB is a candidate key.

Candidate key including BC:
Closure of BC: {BCD}
Since {BCD} is not equal to {ABCD}, BC alone is not a candidate key.

Candidate key including CD:
Closure of CD: {CDA}
Since {CDA} is not equal to {ABCD}, CD alone is not a candidate key.

So, the candidate key is AB.

(c) Find the normal form of relation R and convert into BCNF if needed:

First, let's check if the relation is in BCNF. A relation is in BCNF if, for every non-trivial functional dependency X -> Y, X is a superkey. A superkey is a set of attributes that uniquely determines all other attributes in the relation.

For the given FDs:

AB -> C (AB is a superkey, as AB is a candidate key)
BC -> D (BC is not a superkey)
CD -> A (CD is not a superkey)
Since BC and CD are not superkeys, the relation is not in BCNF.

The key here is to ensure that each new relation has a superkey as its primary key.

New relations:

R1(A, B, C)
Key: AB
FD: AB -> C

R2(B, C, D)
Key: BC
FD: BC -> D

R3(C, D, A)
Key: CD
FD: CD -> A

Now, each new relation is in BCNF, and the original relation has been decomposed.