Question

Please answer ASAP

Database

Consider the relation scheme R = {A, B, C, D, E} with the FDs
A --> BC
CD --> E
Consider the following decompositions:
(4.a) R1 = {A, B, C} and R2 = {C, D, E}
(4.b) R1 = {A, B, C} and R2 = {A, D, E}
(4.c) R1 = {A, B} and R2 = {A, C, D, E}
(4.d) R1 = {A, B, C}, R2 = {C, D, E} and R3 ={A, D}.
For each decomposition, determine if it is lossless or not. Explain why.

Answer 1

Solution:

Given,

=>Relation = R(A, B, C, D, E)

=>Set of functional dependencies = {A ->BC, CD -> E}

(a) R1 = {A, B, C} and R2 = {C, D, E}

The answer will be "No"

Explanation:

=>R1 = {A, B, C} with functional dependency = {A -> BC}

Candidate key = {A}

=>R2 = {C, D, E} with functional dependency = {CD -> E}

Candidate key = {CD}

Checking lossless:

=>Attr(R1) U Atr2(R2) = {A, B, C, D, E} = Attr(R) hence first condition is satisfied.

=>There is common attribute "C" between R1 and R2 relations hence second condition is satisfied.

=>The common attribute "C" is not candidat key for any of the relations R1 and R2 hence third condition is not satisfied.

=>As third condition is not satisfied hence decomposition is not lossless decomposition.

(b) R1 = {A, B, C} and R2 = {A, D, E}

The answer will be "Yes"

Explanation:

=>R1 = {A, B, C} with functional dependency = {A -> BC}

Candidate key = {A}

=>R2 = {A, D, E} with no functional dependency.

Checking lossless:

=>Attr(R1) U Atr2(R2) = {A, B, C, D, E} = Attr(R) hence first condition is satisfied.

=>There is common attribute "A" between R1 and R2 relations hence second condition is satisfied.

=>The common attribute "A" is candidat key for relation R1 hence third condition is satisfied.

=>As all the conditions are satisfied hence decomposition is lossless join decomposition.

(c) R1 = {A, B} and R2 = {A, C, D, E}

The answer will be "Yes"

Explanation:

=>R1 = {A, B} with functional dependency = {A -> B}

Candidate key = {A}

=>R2 = {A, C, D, E} with functional dependency = {CD -> E}

Candidate key = {ACD}

Checking lossless:

=>Attr(R1) U Atr2(R2) = {A, B, C, D, E} = Attr(R) hence first condition is satisfied.

=>There is common attribute "A" between R1 and R2 relations hence second condition is satisfied.

=>The common attribute "A" is candidat key for relation R1 hence third condition is satisfied.

=>As all the conditions are satisfied hence decomposition is lossless join decomposition.

(d) R1 = {A, B, C}, R2 = {C, D, E} and R3 = {A, D}

The answer will be "Yes"

Explanation:

=>R1 = {A, B, C} with functional dependency = {A -> BC}

Candidate key = {A}

=>R2 = {C, D, E} with functional dependency = {CD -> E}

Candidate key = {CD}

=>R3 = {A, D} with no functional dependency

Checking lossless:

=>Attr(R1) U Atr2(R2) U Attr(R3) = {A, B, C, D, E} = Attr(R) hence first condition is satisfied.

=>There is common attribute "A" between R1 and R3 relations hence second condition is satisfied.

=>The common attribute "A" is candidat key for relation R1 hence third condition is satisfied.

=>R1 U R2 = {A, B, C, D} and R3 have CD attribute common which is the candidate key for R3.

=>As all the conditions are satisfied hence decomposition is lossless join decomposition.

I have explained each and every part with the help of statements attached to the answer above.