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

### Incoming search terms:

• questions on decomposition in dbms