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 :
       AD → E
       B → D
   (b)
Applying Decomposition Algorithm to FD: AD → E  ABCDEFGH
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

AD : CK

 ADBCFH

{ABFH,ACFH.BCFH} : CK

AD → E √
 E → G √ 
AB → C  √
AC → B  √
B → D   x
BC → A  √
Since all are in 2NF except B → D, 
Applying Decomposition Algorithm to FD: B → D  ABCDFH
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

B : CK

 BACFH

{ABFH, ACFH, BCFH} : CK

 B → D √
AB → C √
BC → A √
AC → B √
Check the CK of R is preserved in the decomposed relations- Yes, 

Hence the decomposition in 2NF :
CK : AD
ADEG
CK : B
BD
CK : {ABFH, BCFH, ACFH}
ABCFH
AD → E √
E → G √
B → D √
AB → C √
AC → B √
BC → A √
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

E : CK

 EAD

AD : CK

 E → G √
AD → E √
Check the CK of R is preserved in the decomposed relations- Yes

Hence the decomposition in 3NF :
CK : E
EG
CK : AD
ADE
CK : B
BD
CK : ABFH, BCFH, ACFH
ABCFH
E → G √
AD → E √
B → D √
AB → C √
AC → B √
BC → A √
Step 4 : Checking For BCNF :
         FD which violates BCNF :
         AB → C 
         AC → B 
         BC → A
Applying Decomposition Algorithm to FD: AB → C  ABCFH
Compute Closure
of LHS
Relationi =
All Attributes in Closure
Relationj =
All attributes on LHS of FD

All attributes of R not in Closure
(AB)+= 
   {ABC}
 ABC

AB : CK

 ABFH

{ABFH} : CK

AB → C √
BC → A x
AC → B x 
No FD exist for relation
“ABFH”.But {ABFH} is a
candidate key. So,
remains in Decomposition.
Applying Decomposition Algorithm to FD: BC → A  ABC
Compute Closure
of LHS
Relationi =
All Attributes in Closure
Relationj =
All attributes on LHS of FD

All attributes of R not in Closure
(BC)+= 
   {ABC}
 ABC

AB : CK
BC : CK

 BC

 

AB → C √
BC → A √
AC → B x 
No FD exist for relation
“BC”. So Discarded.
Applying Decomposition Algorithm to FD: AC → B  ABC
Compute Closure
of LHS
Relationi =
All Attributes in Closure
Relationj =
All attributes on LHS of FD

All attributes of R not in Closure
(AC)+= 
   {ABC}
 ABC

AB : CK
BC : CK
AC : CK

 AC

 

AB → C √
BC → A √
AC → B √
No FD exist for relation
“AC”. So Discarded.
Check the CK of R is preserved in the decomposed relations- No 
 {BCFH and ACFH} are two candidate keys which are not preserving
 dependency. So, make new relations for each CK which is not preserved :
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
Previous Home Next
Normal Forms Shortcuts for Gate Students Introduction to SQL : Structured Query Language

     

Incoming search terms:

  • decompose relation questions
  • decomposition in 2nf and 3nf practice questions
  • decomposition with normal forms

Leave a Reply