Normal Forms Questions – Part 2

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.

]]>