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

2008

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)

  1. A.

    gives a lossless join, and is dependency preserving

  2. B.

    gives a lossless join, but is not dependency preserving

  3. C.

    does not give a lossless join, but is dependency preserving

  4. D.

    does not give a lossless join and is not dependency preserving

Attempted by 254 students.

Show answer & explanation

Correct answer: A

Concept

A decomposition {R1,...,Rn} of R is lossless-join if the natural join of the projected relations always reconstructs R with no spurious tuples — a sufficient chained-join test is that at each step the common attribute set functionally determines (via the closure of F) all the attributes unique to one side. It is dependency-preserving if the union of the functional dependencies obtained by projecting F onto each Ri logically implies every FD in the original set F — every constraint must be derivable within the projected set, even if only by transitivity, not necessarily written literally in any single relation.

Application

  1. Compute attribute closures under F = {A → B, B → C, C → D, D → B}: A+ = {A, B, C, D} (so A is a key of R); B+ = C+ = D+ = {B, C, D} — the cycle B → C → D → B makes B, C and D mutually determine each other.

  2. Lossless-join test (chained binary joins): join (A,B) with (B,C) — common attribute B, and B → C holds, so this join is lossless, giving (A,B,C); then join (A,B,C) with (B,D) — common attribute B, and B → D holds (derived via B → C → D), so this join is lossless too. Hence the full three-way decomposition is lossless.

  3. Dependency-preservation test — project F onto each relation:

    Relation

    Projected functional dependencies

    (A, B)

    A → B

    (B, C)

    B → C, C → B (since C⁺ contains B)

    (B, D)

    B → D, D → B

    Union of projected FDs: A → B, B → C, C → B, B → D, D → B.

  4. Recover the original C → D from the projected set: C → B (projected) combines with B → D (projected) by transitivity to give C → D. Since A → B, B → C and D → B are already present directly, every original dependency is implied by the projected set — so the decomposition is dependency-preserving.

Cross-check

Compute the closure of {C} directly under the projected set: start with {C}, apply C → B to add B, apply B → D to add D, giving {B, C, D} — which already includes D, confirming C → D holds under the projected set. This matches the closure computed directly under the original F, confirming consistency.

Result

The decomposition (A, B), (B, C), (B, D) is both lossless and dependency-preserving.

Explore the full course: Gate Guidance By Sanchit Sir