GATE | Gate IT 2008 | Question 59

Let R (A, B, C, D) be a relational schema with the following functional dependencies:

A → B, B → C,
C → D and D → B. 

The decomposition of R into 
(A, B), (B, C), (B, D)

(A) gives a lossless join, and is dependency preserving
(B) gives a lossless join, but is not dependency preserving
(C) does not give a lossless join, but is dependency preserving
(D) does not give a lossless join and is not dependency preserving

Answer: (A)
Explanation: Background :

• Lossless-Join Decomposition:
  Decomposition of R into R1 and R2 is a lossless-join decomposition if at least one of the following functional dependencies are in F+ (Closure of functional dependencies)

      R1 ∩ R2 → R1
     OR
      R1 ∩ R2 → R2
  

• Dependency Preserving Decomposition:
  Decomposition of R into R1 and R2 is a dependency preserving decomposition if closure of functional dependencies after decomposition is same as closure of of FDs before decomposition.
  A simple way is to just check whether we can derive all the original FDs from the FDs present after decomposition.
  
  

Question :
Let R (A, B, C, D) be a relational schema with the following functional dependencies:

A -> B, B -> C,
C -> D and D -> B. 

The decomposition of R into 
(A, B), (B, C), (B, D)

Note that A, B, C and D are all key attributes. We can derive all attributes from every attribute.

Since Intersection of all relations is B and B derives all other attributes, relation is lossless.

The relation is dependency preserving as well as all functional dependencies are preserved directly or indirectly. Note that C -> D is also preserved with following two C -> B and B -> D.

Quiz of this Question