Normalize up to and including BCNF

QUESTION

For the diagram, normalize up to and including BCNF.

 

SUMMARY

There are 8 functional dependencies in the above relational table.

The candidate key = EF.

The prime attributes = {E, F} and the rest are non-prime.

Among all the given functional dependencies, only one dependency is in BCNF. On decomposing it to BCNF, we get 7 relational tables.

 

ANSWER

Functional dependencies = { A – BC, EF – GH , GH – I , I – J , F – K , BC – D , D – C , E – A}

Non-prime attributes = {A,B,C,D,G,H,I,J,K}

For a functional dependency to be in BCNF,(X-Y) then X must be a super key.

FD1:

A-BC = not super key

FD2:

EF-GH = super key

FD3:

GH-I = not super key

FD4:

I-J = not super key

FD5:

F-K = not super key

FD6:

BC-D = not super key

FD7:

D-C =  not super key

FD8:

F-A = not super key

Decomposing : 

R1(ABC):

F={A-BC}

A-B = sk

R2(BCD):

F={BC-D}

BD-D = sk

R3(EFGH):

F={EF-GH}

EF-GH = sk

R4(GHI):

F={GH-I}

GH-I = sk

R5(IJ):

F={I-J}

I-J = sk

R6(EA):

F={E-A}

E-A = sk

R7(FK):

F={F-K}

F-K = sk

Totally 7 relational tables now in BCNF.

 

Also Read: Write relational schema for the diagram