Advanced Database Management System - Tutorials and Notes: Lossless join decomposition solved example in normalization

## Thursday, 12 April 2018

### Lossless join decomposition solved example in normalization

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 R1(A, B, C) and R2(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;

• (R1 ∩ R2) R1
• (R1 ∩ R2) R2
If we apply intersection between R1 and R2, we shall get,
(R1 ∩ R2) = {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 a1 a2 a3 a4 a1 a4 a3 a2

According to the decomposition, we shall get R1 and R2 as follows;

 R1 A B C a1 a2 a3 a1 a4 a3

 R2 A C D a1 a3 a4 a1 a3 a2

Join back R1 and R2 must result in R if the decomposition is lossless.

R1
R2
=
R’

 A B C a1 a2 a3 a1 a4 a3

 A C D a1 a3 a4 a1 a3 a2

=

 A B C D a1 a2 a3 a4 a1 a2 a3 a2 a1 a4 a3 a2 a1 a4 a3 a4

R’ is the result of natural join of R1 and R2, and R’ is not equal to R the base relation. Hence, the decomposition is not lossless join decomposition.

***********

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