# 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 :
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

{ABFH,ACFH.BCFH} : CK

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 : B BD
 CK : {ABFH, BCFH, ACFH} ABCFH
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

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

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

### Incoming search terms:

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