Lossless join decomposition solved example in normalization
Question:
Consider a relation R(A,
B, C, D) with the set of functional dependencies F = {AB → C,
BC → D,
CD → A}.
Assume that R is decomposed into R_{1}(A, B, C) and R_{2}(A, C,
D). Find whether the given decomposition is lossless or not.
Solution:
Lossless join decomposition
implies that the result of joining all the decomposed relations will create the
base relation again without any loss/gain in data.
If one of the following is
true, then the decomposition is said to be lossless;
 (R_{1} ∩ R_{2}) → R_{1}
 (R_{1} ∩ R_{2}) → R_{2}
If we apply intersection
between R1 and R2, we shall get,
(R_{1}
∩ R_{2}) = {A, B, C} ∩ {A, C, D} = AC.
There
is no functional dependency in F such that the AC is alone on the left hand
side. Hence, this decomposition is lossless.
Example:
Let us populate R with
sample data and try the experiment;
A

B

C

D

a_{1}

a_{2}

a_{3}

a_{4}

a_{1}

a_{4}

a_{3}

a_{2}

According to the
decomposition, we shall get R_{1} and R_{2} as follows;
R_{1}


A

B

C

a_{1}

a_{2}

a_{3}

a_{1}

a_{4}

a_{3}

R_{2}


A

C

D

a_{1}

a_{3}

a_{4}

a_{1}

a_{3}

a_{2}

Join back R_{1} and R_{2} must result in R if the decomposition is lossless.
R_{1}

⋈

R_{2}

=

R’



⋈


=


R’ is the result of
natural join of R_{1} and R_{2}, and R’ is not equal to R the base relation. Hence, the
decomposition is not lossless join decomposition.
***********
Go to Normalization solved exercises page
Go to Solved exercises in DBMS page
Normalization solved examples
normalization exercises solved
what is lossless decomposition
rules for lossless join decomposition
lossless decomposition example
how to find whether a decomposition is lossless or not
No comments:
Post a comment