Consider the relation \(R(P,Q,S,T,X,Y,Z,W)\) with the following functional…
2021
Consider the relation \(R(P,Q,S,T,X,Y,Z,W)\) with the following functional dependencies.
\(PQ\rightarrow X;\quad P\rightarrow YX;\quad Q\rightarrow Y; \quad Y\rightarrow ZW\)
Consider the decomposition of the relation R into the constituent relations according to the following two decomposition schemes.
\(D_1:\quad R=[(P,Q,S,T);\;(P,T,X);\;(Q,Y);\;(Y,Z,W)]\)
\(D_2:\quad R=[(P,Q,S);\;(T,X);\;(Q,Y);\;(Y,Z,W)]\)
Which one of the following options is correct?
- A.
\(D_1 \)is a lossless decomposition, but\(D_2\)is a lossy decomposition - B.
\(D_1 \)is a lossy decomposition, but\(D_2\)is a lossless decomposition - C.
Both
\(D_1 \)and\(D_2\)are lossless decompositions - D.
Both
\(D_1 \)and\(D_2\)are lossy decompositions
Attempted by 146 students.
Show answer & explanation
Correct answer: A
Key insight: test each decomposition by checking intersections of relations and whether those intersections functionally determine one of the joined relations (lossless-join test).
For D1: Start with (P,Q,S,T) and (P,T,X). Their intersection is {P,T}. Since P -> X (given), {P,T} determines (P,T,X), so this join is lossless. Next join with (Q,Y): intersection contains Q and Q -> Y, so that join is lossless. Finally join with (Y,Z,W): intersection contains Y and Y -> ZW, so that join is lossless. Therefore the whole decomposition D1 is lossless.
For D2: The relation (T,X) does not share attributes with (P,Q,S), (Q,Y) or (Y,Z,W) that would functionally determine (T,X). Although P -> X, P is not present in (T,X), so X is separated from its determinant P. After joining the other relations we never obtain an attribute in common with (T,X), so the join can produce spurious tuples and the decomposition is lossy.
Conclusion: Decomposition D1 is lossless. Decomposition D2 is lossy.
A video solution is available for this question — log in and enroll to watch it.