Normal Forms Questions – Part 2

Decompose the relation R, till BCNF.

Question 1 :
R(ABCDEFGHIJ)
FD : {AB → C, A → DE, B → F, F → GH, D → IJ}
Solution :
Step 1 : Find all the candidate keys of R.
         Candidate Key : {AB}

Step 2 : Checking For 2NF :
   (a) FD which violates 2NF :
       A → DE
       B → F
   (b)
Applying Decomposition Algorithm to FD: A → DE  ABCDEFGHIJ
Compute Closure of LHS Relationi =
All Attributes in Closure
Relationj =
All attributes on LHS of FD

All attributes of R not in Closure
(A)+ = {ADEIJ}
 ADEIJ
 ABCFGH
 A → DE √
D → IJ  √
 AB → C √
B → F    ×
F → GH √
Applying Decomposition Algorithm to FD: B → F  ABCFGH
Compute Closure of LHS Relationi =
All Attributes in Closure
Relationj =
All attributes on LHS of FD

All attributes of R not in Closure
(B)+ = {BFGH}
 BFGH
 BAC
 B → F √
F → GH √
 AB → C √
Check the CK of R is preserved in the decomposed relations- yes, 
it is preserved in "BAC" relation.
Hence the decomposition in 2NF :
CK : A
ADEIJ
CK : B
BFGH
CK : AB
BAC
A → DE √
D → IJ  √
B → F √
F → GH √
AB → C √
Step 3 : Checking For 3NF :
   (a) FD which violates 3NF :
       D → IJ
       F → GH
   (b)
Applying Decomposition Algorithm to FD: D → IJ  ADEIJ
Compute Closure of LHS Relationi =
All Attributes in Closure
Relationj =
All attributes on LHS of FD

All attributes of R not in Closure
(D)+ = {DIJ}
 DIJ
 DAE
 D → IJ √  A → DE √
Applying Decomposition Algorithm to FD: F → GH  BFGH
Compute Closure of LHS Relationi =
All Attributes in Closure
Relationj =
All attributes on LHS of FD

All attributes of R not in Closure
(F)+ = {FGH}
 FGH
 FB
F → GH √  B → F √
Check the CK of R is preserved in the decomposed relations- yes
it is preserved in "BAC" relation.
Hence the decomposition in 3NF :
CK : D
DIJ
CK : A
DAE
CK : F
FGH
CK : B
BF
CK : AB
BAC
D → IJ √ A → DE √ F → GH √ B → F √ AB → C √
Step 4 : Checking For BCNF :
         FD which violates BCNF : None
         Hence the decomposition is already in BCNF also.

Previous Home Next
Normal Forms Shortcuts for Gate Students Next : Question 2 – Decompose the Relation R till BCNF – Question

     

Incoming search terms:

  • relation (a b c d e f )with a as the only key assume that the dependencies e->f c->deh hold on r is r in 2nf if not decompose it to 2nf

Leave a Reply