Question on Decomposition of R till BCNF

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.

]]>