Question on Decomposition of Normal Forms

Question on Decomposition of Normal Forms –

Question 4 :
R(ABCDEFGH)
FD : {AB → C, AC → B, AD → E, B → D, BC → A, E → G}
Decompose the Relation R till BCNF.
Solution :
Step 1 : Find all the candidate keys of R.
         Candidate Key : {ABFH}, {BCFH} and {ACFH}

Step 2 : Checking For 2NF :
   (a) FD which violates 2NF :
       AB → C
       AC → B
       AD → E
       B → D
       BC → A
   (b)
Applying Decomposition Algorithm to FD: AB → C  ABCDEFGH
Compute Closure of LHS Relationi = All Attributes in Closure Relationj = All attributes on LHS of FD ∪ All attributes of R not in Closure
(AB)+=
   {ABCDEG}
 ABCDEG
 ABFH
AB → C √
BC → A √ ,
  Because (BC)+ = {ABCDEG}
AC → B √ , 
  Because (AC)+ = {ABCDEG}
AD → E ×
B → D ×
E → G √
No FD exist for relation “ABFH”.But {ABFH} is a candidate key. So, remains in Decomposition.
BC → A and AC → B are in 2NF also because as AB is the candidate key for
the relation "ABCDEG",
we can replace B by AC (Because AC → B) as AB = A(AC) = AC : CK
and we can replace A by BC (Because BC → A) as AB = (BC)B = BC : CK
Hence AB,BC,AC all three are the candidate keys for the relation "ABCDEG"
Applying Decomposition Algorithm to FD: AD → E  ABCDEG
Compute Closure of LHS Relationi = All Attributes in Closure Relationj = All attributes on LHS of FD ∪ All attributes of R not in Closure
(AD)+ = {ADEG}
 ADEG
 ADBC
 AD → E √ E → G √ AB → C √ BC → A √ AC → B √ B → D ×
Applying Decomposition Algorithm to FD: B → D  ADBC
Compute Closure of LHS Relationi = All Attributes in Closure Relationj = All attributes on LHS of FD ∪ All attributes of R not in Closure
(B)+ = {BD}
 BD
 BAC
B → D √ AB → C √ BC → A √ AC → B √
Check the CK of R is preserved in the decomposed relations- NO, 
So, make new relations for each CK which is not preserved.
Here, 3 Candidate Keys are there out of which 2 CK are not preserved-
{BCFH} and {ACFH}. So, making relations for each one as :
BCFH
ACFH
Hence the decomposition in 2NF :
CK : AD
ADEG
CK : B
BD
CK : AB,BC,AC
ABC
AD → E √ E → G √ B → D √ AB → C √ AC → B √ BC → A √
CK : ABFH
ABFH
CK : BCFH
BCFH
CK : ACFH
ACFH
Step 3 : Checking For 3NF :
         FD which violates 3NF :
         E → G
Applying Decomposition Algorithm to FD: E → G  ADEG
Compute Closure of LHS Relationi = All Attributes in Closure Relationj = All attributes on LHS of FD ∪ All attributes of R not in Closure
(E)+ = {EG}
 EG
 EAD
 E → G √ AD → E √
Hence the decomposition in 3NF :
CK : E
EG
CK : AD
EAD
CK : B
BD
CK : AB,BC,AC
ABC
E → G √ AD → E √ B → D √ AB → C √ AC → B √ BC → A √
CK : ABFH
ABFH
CK : BCFH
BCFH
CK : ACFH
ACFH
Step 4 : Checking For BCNF :
         FD which violates BCNF : None
         Hence the decomposition is already in BCNF.
]]>

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top