## Questions on Lossy and Lossless DecompositionIf relation in the relational model or relational schema is not inappropriate normal form then decomposition of a relation is done. A relation schema R is decomposed/divided into two or more than two relations if decomposition is lossless join.
1. On taking Union of Attributes of relation R1 and relation R2 must be equal to the attribute of relation R, i.e. the attribute of relation R must be either in relation R1 or in relation R2. i.e. on adding an attribute of both the table (excluding duplicate attributes) we should get the total attribute of R. In case, this condition fails then no need to check further as this is the first prerequisite of the 2. On doing intersection of Attributes of relation R1 and relation R2 must not be NULL, i.e., at least there should be one common attribute in both the table based on which you join both the table, In case, this condition fails no need to check further as this is the second prerequisite of the 3. The common attribute must be a key for at least one relation (R1 or R2). i.e. suppose an attribute "A" is common in both R1 and R2 then either A should be key in R1 or A should be key in R2. In case this condition fails no need to check further as this is the last prerequisite of the Let us take an example of a relation R (X, Y, Z, W) with Functional Dependency set {X -> Y Z} is decomposed into relation R1( X, Y, Z) and relation R2( X, W ) which is a lossless join decomposition as: - First condition holds true as Attribute ( R1 ) U Attribute ( R2 ) = ( X, Y, Z ) U ( X, W ) = (X, Y, Z, W ) = Attribute ( R ).
- The second condition holds as Attribute ( R1 ) ∩ Attribute ( R2 ) = ( X, Y, Z ) ∩ ( X, W ) ≠ Φ
- The third condition holds as Attribute ( R1 ) ∩ Attribute ( R2 ) = X is a key of R1( X, Y, Z ) because X -> Y Z is given.
If we decompose a relation R into relations R1 and R2, All dependencies of R either must be a part of R1 or R2 or must be derivable from a combination of FD's of R1 and R2. For Example, A relation R (X, Y, Z, W) with Functional Dependency set {X -> Y Z} is decomposed into relation R1( X, Y, Z) and relation R2( X, W ) which is dependency preserving because FD X -> Y Z is a part of R1( X, Y, Z).
- Attribute ( R1 ) U Attribute ( R2 ) = X Y Z W = Attribute ( R ) { which satisfies the first condition of lossless join decomposition hence we check the next condition of same.
- Attribute ( R1 ) ∩ Attribute ( R2 ) = NULL, (i.e. there is no common attribute in relation R1 and relation R2 ) which violates the condition of lossless join decomposition. Hence the decomposition is not lossless.
Table: R
- Attribute(R1) U Attribute (R2) = Attribute (R)
- Attribute (R1) ∩ Attribute (R2) ≠ Φ
- Attribute (R1) ∩ Attribute (R2) -> Attribute (R1) or Attribute (R1) ∩ Attribute (R2) -> Attribute (R2)
Hence relation R (X Y Z W P) decomposed into R1( X Y Z ) and R2( Z W P ) is a Lossy decomposition.
Solution: For a relation R to be lossless decomposition R should satisfy following three conditions: - Attribute(R1) U Attribute (R2) = Attribute (R)
- Attribute (R1) ∩ Attribute (R2) ≠ Φ
- Attribute (R1) ∩ Attribute (R2) -> Attribute (R1) or Attribute (R1) ∩ Attribute (R2) -> Attribute (R2)
Since Condition 1 is not satisfied so we will not check condition 2 and 3 Hence relation R (X Y Z W P) decomposed into R1( X Y ) and R2( Z W ) is a Lossy decomposition.
- Attribute(R1) U Attribute (R2) = Attribute (R)
- Attribute (R1) ∩ Attribute (R2) ≠ Φ
- Attribute (R1) ∩ Attribute (R2) -> Attribute (R1) or Attribute (R1) ∩ Attribute (R2) -> Attribute (R2)
Since Condition 2 is not satisfied so we will not check condition 3 Hence relation R (X Y Z W P) decomposed into R1( X Y Z ) and R2( W P ) is a Lossy decomposition.
- Attribute(R1) U Attribute (R2) = Attribute (R)
- Attribute (R1) ∩ Attribute (R2) ≠ Φ
Hence relation R (X Y Z W P) decomposed into R1( X Y Z ) and R2( Z W P ) is a Lossless decomposition.
- Attribute(R1) U Attribute (R2) = Attribute (R)
- Attribute (R1) ∩ Attribute (R2) ≠ Φ
Hence relation R (X Y Z W P) decomposed into R1( X Y Z ) and R2( Z W P ) is a Lossless decomposition. |