Decompose the Relation R till BCNF – Question

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.

]]>