Question

Find the decopmosition of R into R1(A, B, C), R2(B, C,D ) and R3(C, D, E)

Let R(A,B,C,D,E) be a relation with FDs F = {AB-CDAE, C-D, D-E} The decomposition of R into R1(A, B, C), R2(B, C, D) and RP(C, D, E) is (2 Points) Select one: Lossy and Not Dependency Preserving. Lossless and Not Dependency Preserving. Lossy and Dependency Preserving. Lossless and Dependency Preserving.

Answer 1

The given relation is R (A, B, C, D, E).

The given set of functional dependency F is :

AB -> CD

A -> E

C -> D

D -> E

The closure of the given set is :

AB -> C [ Decomposition rule ]

AB -> D [ Decomposition rule ]

A -> E

C -> D

D -> E

C -> E [ Transitive rule ]

The decomposition of R is done into 2 tables.

For table R1(A, B, C),

The set of functional dependencies F1 is :

AB -> C

For table R2(B, C, D),

The set of functional dependencies F2 is :

C -> D

For table R3 ( C, D, E ),

The set of functional dependencies F3 is :

C -> D

D -> E

C -> E

For checking lossless property :

The common attirbute between R2(B, C, D) and R3 ( C, D, E ) is CD and CD is a superkey in relation R3. When the attributes of R2 and R3 are combined to form a new table R4 ( B, C, D, E), then the common attribute between R1(A, B, C) and R4 ( B, C, D, E), is BC and BC is a superkey in the combined relation R4.

So, the decomposition is a lossless decomposition.

For checking dependency preserving,

Closure of ( F1 U F2 U F3 ) is :

AB -> C

C -> D

D -> E

C -> E

AB -> D [ Transitive rule ]

The closure of the original set of functional dependency F is :

AB -> C [ Decomposition rule ]

AB -> D [ Decomposition rule ]

A -> E

C -> D

D -> E

C -> E [ Transitive rule ]

So, the functional dependency A -> E is not present in the closure of ( F1 U F2 U F3 ) and so this closure is not equal to the closure of F.

The decomposition is not dependency preserving.

Hence, the answer is option b that is lossless and not dependency preserving.