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


No comments:

Post a Comment

Important properties of two phase locking protocol and its variants

Important properties of two phase locking protocol and its variants Properties of     2PL Serializability  2PL ensures co...