Properties of Functional Dependency in DBMS
1.Reflexivity: If A is a set of attributes and B is a subset of A, then the
functional dependency A → B holds true.
For example, { Employee_Id, Name } → Name is valid.
2. Augmentation: If a functional dependency A → B holds true, then
appending any number of the attribute to both sides of dependency
doesn't affect the dependency. It remains true.
For example, X → Y holds true then, ZX → ZY also holds true.
For example,
if { Employee_Id } → { Name } holds true then,
{ Employee_Id, Age } → { Name, Age }
3. Transitivity: If two functional dependencies
X → Y and Y → Z hold true, then X → Z also holds true.
For example,
if { Employee_Id } → { Name } holds true and
{ Name } → { Department } holds true,
then { Employee_Id } → { Department } also holds true.
Decomposition of Relation
Decomposition of a relation is done when a relation in a relational model is not
inappropriate normal form.
Decomposition should be lossless as well as dependency preserving.
If we decompose a relation R into relations R1 and R2,
To check for lossless join decomposition using the FD set, the following conditions must
hold:
 |
Characteristics of Lossless decomposition
1)The Union of Attributes of R1 and R2 must be equal to the attribute of R. Each attribute
of R must be either in R1 or in R2.
Att(R1) U Att(R2) = Att(R)
2) The intersection of Attributes of R1 and R2 must not be NULL.
Att(R1) ∩ Att(R2) ≠ Φ
3. The common attribute must be a key for at least one relation (R1 or R2)
Att(R1) ∩ Att(R2) -> Key(R1)
or Att(R1) ∩ Att(R2) -> Key(R2)
For Example, Consider Lossless Decomposition of A relation R (A, B, C, D) with FD
set{A->BC} is decomposed into R1(ABC) and R2(AD)
Ans: -->
First condition holds true as Att(R1) U Att(R2) = (ABC) U (AD) = (ABCD) = Att(R).
Second condition holds true as Att(R1) ∩ Att(R2) = (ABC) ∩ (AD) = {A} ≠ Φ
The third condition holds as Att(R1) ∩ Att(R2) = A is a key of R1(ABC) because
A->BC is given.
Dependency preserving Decomposition
If we decompose a relation R into relations R1 and R2, All dependencies of R either must
be a part of R1 or R2 or must be derivable from a combination of functional dependency of
R1 and R2.
For Example,
A relation R (A, B, C, D) with FD set{A->BC} is decomposed into R1(ABC) and R2(AD)
It is dependency preserving because FD A->BC is a part of R1(ABC).
Example
Consider a schema R(A, B, C, D) and functional dependencies A->B and C->D. Then the decomposition of R into R1(AB) and R2(CD) is ________
(A) dependency preserving and lossless join
(B) lossless join but not dependency preserving
(C) dependency preserving but not lossless join
(D) not dependency preserving and not lossless join
Answer:
Att(R1) U Att(R2) = ABCD = Att(R)
Att(R1) ∩ Att(R2) = Φ, which violates the condition of lossless join decomposition.
Hence the decomposition is not lossless.
For dependency preserving decomposition, A->B can be ensured in R1(AB) and C->D can be ensured in R2(CD). Hence it is dependency preserving decomposition.
So, the correct option is C.