Decompose the Relation R till BCNF – Question

Question 2 :
R(ABDLPT)
FD : {B → PT, T → L, A → D}
Decompose the Relation R till BCNF.
Solution :
Step 1 : Find all the candidate keys of R.
         Candidate Key : {AB}

Step 2 : Checking For 2NF :
   (a) FD which violates 2NF :
       B → PT
       A → D
   (b)
Applying Decomposition Algorithm to FD: B → PT  ABDLPT
Compute Closure of LHS Relationi =
All Attributes in Closure
Relationj =
All attributes on LHS of FD

All attributes of R not in Closure
(B)+ = {BPTL}
 BPTL
 BAD
 B → PT √
T → L  √
 A → D ×
Applying Decomposition Algorithm to FD: A → D  BAD
Compute Closure of LHS Relationi =
All Attributes in Closure
Relationj =
All attributes on LHS of FD

All attributes of R not in Closure
(A)+ = {AD}
 AD
 AB
 A → D √ There is no FD for this relation.
But {AB} is a CK and is missing
in other’s decomposed relation,
So, the relation “AB” is added.
Check the CK of R is preserved in the decomposed relations- yes, 
it is preserved in "AB" relation.
Hence the decomposition in 2NF :
CK : B
BPTL
CK : A
AD
CK : AB
AB
B → PT √
T → L  √
A → D √ AB : CK
Step 3 : Checking For 3NF :
   (a) FD which violates 3NF :
       T → L
   (b)
Applying Decomposition Algorithm to FD: T → L  BPTL
Compute Closure of LHS Relationi =
All Attributes in Closure
Relationj =
All attributes on LHS of FD

All attributes of R not in Closure
(T)+ = {TL}
 TL
 TBP
 T → L √  B → PT √
Check the CK of R is preserved in all the decomposed relations- yes
it is preserved in "AB" relation.
Hence the decomposition in 3NF :
CK : A
AD
CK : AB
AB
CK : T
TL
CK : B
TBP
A → D √ CK relation T → L √ B → PT √
Step 4 : Checking For BCNF :
         FD which violates BCNF : None
         Hence the decomposition is already in BCNF also.

Previous Home Next
Normal Forms Shortcuts for Gate Students Next: Question 3 – Decompose the relation R till BCNF

     

Incoming search terms:

  • a b realtion questions
  • bcnf decomposition questions

Leave a Reply