Find whether the given decomposition is Lossless or lossy decomposition

Find whether the given decomposition is Lossless or lossy decomposition

Question:

Suppose you are given a relation R(A,B,C,D). let us assume that set of functional dependencies F = { B → C, D → A} and R is decomposed into R1(B,C), and R2(A,D).

(a) Find the candidate key(s) for R. (b) State whether or not the proposed decomposition of R into smaller relations is a good decomposition and briefly explain why or why not.

Solution:

a) To find the candidate keys, we have to find the closure of attributes of LHS (left hand side) of all functional dependencies. If the closure includes all the attributes of the given relation, then that attribute (or set of attributes) is the candidate key.

B⁺ = BC. It does not include all attributes of R.

D⁺ = DA. It does not include all attributes of R.

(BD)⁺ = BCDA. It includes all attributes of R, hence BD is the candidate key.

b) The decomposition of R into R1 and R2 is lossy because there is no common attribute between R1 and R2 (R1∩R2 = ∅). Hence, the join of R1 and R2 will result in Cartesian product of these two relations which is not the base relation R.

***********

Go to Normalization solved exercises page

Go to Solved exercises in DBMS page

Normalization exercises

Decompose given relation

Find whether the decomposition is lossless or lossy

lossless join decomposition exercises

lossy join decomposition exercises