Questions on Lossy and Lossless Decomposition

If 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.

Following conditions must hold: To check for lossless join decomposition using Functional Dependency set.

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 lossless join decomposition

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 lossless join decomposition.

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 lossless join decomposition.

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:

1. First condition holds true as Attribute ( R1 ) U Attribute ( R2 ) = ( X, Y, Z ) U ( X, W ) = (X, Y, Z, W ) = Attribute ( R ).
2. The second condition holds as Attribute ( R1 ) ∩ Attribute ( R2 ) = ( X, Y, Z ) ∩ ( X, W ) ≠ Φ
3. The third condition holds as Attribute ( R1 ) ∩ Attribute ( R2 ) = X is a key of R1( X, Y, Z ) because X -> Y Z is given.

Dependency Preserving Decomposition

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).

Question: Consider a schema R(X, Y, Z, W) and functional dependencies FD = { X -> Y and Z -> W }. Then the decomposition of relational schema R into relation R1( X Y ) and relation R2( Z W ) is

Answer: For lossless join decomposition, these three conditions must hold:

1. 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.
2. 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

Question 1: Consider a relation schema R(X Y Z W P ) (above table R) is decomposed into R1( X Y Z ) and R2( Z W P). determine whether the above R1 and R2 are Lossless or Lossy?

Solution: For a relation R to be lossless decomposition R should satisfy the following three condition:

1. Attribute(R1) U Attribute (R2) = Attribute (R)
2. Attribute (R1) ∩ Attribute (R2) ≠ Φ
3. Attribute (R1) ∩ Attribute (R2) -> Attribute (R1) or Attribute (R1) ∩ Attribute (R2) -> Attribute (R2)

Cond 1: satisfied as Attribute(R1) U Attribute (R2) = Attribute (R) = (X Y Z W P )

Cond 2: satisfied as Attribute (R1) ∩ Attribute (R2) ≠ Φ = ( Z )

Cond 3: Not satisfied as common attribute Z is not key in any relation R1 or R2 ( you can check from table values of column Z is repeating)

Hence relation R (X Y Z W P) decomposed into R1( X Y Z ) and R2( Z W P ) is a Lossy decomposition.

Question 2: Consider a relation schema R( X Y Z W P) (above table R) is decomposed into R1( X Y ) and R2( Z W ). determine whether the above R1 and R2 are Lossless or Lossy?

Solution: For a relation R to be lossless decomposition R should satisfy following three conditions:

1. Attribute(R1) U Attribute (R2) = Attribute (R)
2. Attribute (R1) ∩ Attribute (R2) ≠ Φ
3. Attribute (R1) ∩ Attribute (R2) -> Attribute (R1) or Attribute (R1) ∩ Attribute (R2) -> Attribute (R2)

Cond 1: Not satisfied as Attribute(R1) U Attribute (R2) ( X Y Z W ) ≠ Attribute (R) = ( X Y Z W P)

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.

Question 3: Consider a relation schema R( X Y Z W P ) (above table R) is decomposed into R1( X Y Z ) and R2( W P), determine whether the above R1 and R2 are Lossless or Lossy?

Solution: For a relation R to be lossless decomposition R should satisfy the following three conditions:

1. Attribute(R1) U Attribute (R2) = Attribute (R)
2. Attribute (R1) ∩ Attribute (R2) ≠ Φ
3. Attribute (R1) ∩ Attribute (R2) -> Attribute (R1) or Attribute (R1) ∩ Attribute (R2) -> Attribute (R2)

Cond 1: satisfied as Attribute(R1) U Attribute (R2) = Attribute (R) = (X Y Z W P )

Cond 2: Not satisfied as Attribute (R1) ∩ 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.

Question 4: Consider a relation schema R( X Y Z W P ) (above table R) is decomposed into R1( X Y Z W ) and R2( W P). determine whether the above R1 and R2 are Lossless or Lossy?

Solution: For a relation R to be lossless decomposition R should satisfy following three conditions:

1. Attribute(R1) U Attribute (R2) = Attribute (R)
2. Attribute (R1) ∩ Attribute (R2) ≠ Φ
3. Attribute (R1) ∩ Attribute (R2) -> Attribute (R1) or Attribute (R1) ∩ Attribute (R2) -> Attribute (R2)

Cond 1: satisfied as Attribute(R1) U Attribute (R2) = Attribute (R) = (X Y Z W P )

Cond 2: satisfied as Attribute (R1) ∩ Attribute (R2) ≠ Φ = ( W )

Cond 3: satisfied as common attribute W key (we can check from table values of column W is unique)

Hence relation R (X Y Z W P) decomposed into R1( X Y Z ) and R2( Z W P ) is a Lossless decomposition.

Question 5: Consider a relation schema R( X Y Z W P ) (above table R) is decomposed into R1( X Y Z W) and R2( X Z W P). determine whether the above R1 and R2 are Lossless or Lossy?

Solution: For a relation R to be lossless decomposition, R should satisfy following three conditions:

1. Attribute(R1) U Attribute (R2) = Attribute (R)
2. Attribute (R1) ∩ Attribute (R2) ≠ Φ
3. Attribute (R1) ∩ Attribute (R2) -> Attribute (R1) or Attribute (R1) ∩ Attribute (R2) -> Attribute (R2)

Cond 1: satisfied as Attribute(R1) U Attribute (R2) = Attribute (R) = (X Y Z W P)

Cond 2: satisfied as Attribute (R1) ∩ Attribute (R2) ≠ Φ = (X Z W)

Cond 3: satisfied as common attribute X Z W is key (we can check from table values of column W is unique and any combination of W will also be unique)

Hence relation R (X Y Z W P) decomposed into R1( X Y Z ) and R2( Z W P ) is a Lossless decomposition.