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 
Relation_{i =
All Attributes in Closure} 
Relation_{j =
All attributes on LHS of FD
∪
All attributes of R not in Closure} 
(B)^{+} = {BPTL} 



B → PT √
T → L √ 
A → D × 

Applying Decomposition Algorithm to FD: A → D 
BAD 

Compute Closure of LHS 
Relation_{i =
All Attributes in Closure} 
Relation_{j =
All attributes on LHS of FD
∪
All attributes of R not in Closure} 
(A)^{+} = {AD} 



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 :



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 
Relation_{i =
All Attributes in Closure} 
Relation_{j =
All attributes on LHS of FD
∪
All attributes of R not in Closure} 
(T)^{+} = {TL} 



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 :




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.
Incoming search terms:
 bcnf practice questions with solutions
Related