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 R₁(A, B, C) and R₂(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₁ ∩ R₂) → R₁

• (R₁ ∩ R₂) → R₂

If we apply intersection between R1 and R2, we shall get,

(R₁ ∩ R₂) = {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 R₁ and R₂ as follows;

R1
A	B	C
a1	a2	a3
a1	a4	a3

R2
A	C	D
a1	a3	a4
a1	a3	a2

Join back R₁ and R₂ 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 R₁ and R₂, 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