Question on Decomposition of R till BCNF

Question 3 :
R(ABCDEFGH)
FD : {ABC → DE, E → BCG, F → AH}
Decompose the Relation R till BCNF.
Solution :
Step 1 : Find all the candidate keys of R.
         Candidate Key : {EF} AND {BCF}

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

All attributes of R not in Closure
(F)+ = {FAH}
 FAH
 FBCDEG
 F → AH √  E → BCG ×
Since ABC → DE is functionally dependency is lost in this decomposition step. So, to preserve the dependency, make a relation for ABC → DE
ABCDE
ABC → DE √
Applying Decomposition Algorithm to FD: E → BCG  FBCDEG
Compute Closure of LHS Relationi =
All Attributes in Closure
Relationj =
All attributes on LHS of FD

All attributes of R not in Closure
(E)+ = {EBCG}
 EBCG
 EFD
 E → BCG √ There is no FD exist for this
relation. So, discard it.
Check the CK of R is preserved in the decomposed relations- NO, 
So, make new relations for each CK which is not preserved.
Here, 2 Candidate Keys are there - {EF} and {BCF} and both are not 
preserved in any of the decomposition. So, making relations for each 
one as :
EF
BCF
Hence the decomposition in 2NF :
CK : F
FAH
CK : ABC
ABCDE
CK : E
EBCG
CK : EF
EF
CK : BCF
BCF
F → AH √ ABC → DE √ E → BCG √
Step 3 : Checking For 3NF :
         FD which violates 3NF : None
         Hence the decomposition is already in 3NF.
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 4 – Decompose relation R till BCNF

     

Incoming search terms:

  • Candidate keys for R- ABCDEFGH

Leave a Reply