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}
|
|
|
|
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} |
|
|
|
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} |
|
|
|
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 :
Hence the decomposition in 2NF :
|
|
|
|
| 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} |
|
|
|
E → G √ |
AD → E √ |
|
Hence the decomposition in 3NF :
|
|
|
|
|
| E → G √ |
AD → E √ |
B → D √ |
AB → C √
AC → B √
BC → A √ |
Step 4 : Checking For BCNF :
FD which violates BCNF : None
Hence the decomposition is already in BCNF.
]]>