Question

(a) Find a canonical cover for the FDs of the following table. (b) Put the table into 3NF.

T(A, B, C, D, E)

B --> CD

CD --> A

BC --> A

CD --> E

B --> E

Answer 1

Canonical Cover is the simplified version of given set of functional dependency. It is also called as irreducible set.

Steps:

Consider the FD B-> CD

Find the B+ by ignore that FD B->CD and consider the remaining FD

(B)⁺= {B,E} in this CD is not present in the closure set. so it is important we cant remove it

now consider the second FD CD - > A

(CD)⁺ = { C,D, E} in this A is not present in the closure set. so its important

now consider the third FD BC-> A

(BC)⁺= { B,C, D, E, A} in this A is present in the closure set. so we can remove this FD because by removing thes BC->A is not change in the set of FD's

now consider CD-> E

(CD)⁺= { C,D, A} it's important

Finally consider the FD B-> E

(B)⁺= { B, C, D, E, A} we can remove this FD

Finally after reducing the

canonical cover for the FDs is :

B -> CD

CD -> A

CD -> E

3NF:

It must be in 2NF. It should not contain transitive dependency.

Consider the FDs

B-> CD, CD-> A, CD-> E

in this B-> CD is Fully Functional Dependency and B is a Primary Key.

CD-> A and CD- E are contains transitive dependency

So we have following Relation Tables(A,B,C,D,E)

Table1 attributes are (B, C, D) B is a Primary Key

Table2 attributes are (C, D, A) CD is a Candidate Key

Table3 Attributes are (C,D, E)