Consider relation with the following set of dependencies MN RQ
Consider a relation R = {M, N, O, P, Q, R, S, T } with the following set of dependencies :
MN -> Q
M -> RQ
N -> R
R -> ST
Next, consider the following set of decompositions for the relation scheme R :
D1 = {R1 , R2, R3, R4} : R1 = {M , N, O, P}, R2 = {M , P, Q } , R3 = {N , R} , R4 = {R, S, T}
D2 = {R1 , R2, R3, R4} : R1 = {M , N, O}, R2 = {P, Q} , R3 = {N , R}, R4 = {R, S, T}
Which of the above decomposition (1) has/have lossless join property?
- A.
Only D1
- B.
Only D2
- C.
Both D1 and D2
- D.
Neither D1 nor D2
Attempted by 27 students.
Show answer & explanation
Correct answer: A
CKs = {MNP} {where P is an MVA}
For D1:
R1(MNOP),R2(MPQ),R3(NR),R4(RST)
MP is a common attribute
Where MP is a CK in R2
Hence, we can join R1 & R2
R12(MNOPQ),R3(NR),R4(RST)
N is a common attribute
Where N is a CK in R3
Hence, we can join R12 & R3.
R123(MNOPQR),R4(RST)
R is a common attribute
Where R is a CK in R4
Hence, we can join R123 & R4.
Conclusion:
D1 is a lossless join decomposition.
For D2:
R1={M,N,O},R2={P,Q},R3={N,R},R4={R,S,T}
No common attribute among R1 and R2, hence it fails the lossless join condition.