Page 119 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes Z are independent, and Z is also multi-valued. Now, more formally, X Y is said to hold for
R(X, Y, Z) if t and t are two tuples in R that have the same values for attributes X (t [X] = t [X])
1 2 1 2
then R also contains tuples t and t (not necessarily distinct) such that:
3 4
t [X] = t [X] = t [X] = t [X]
1 2 3 4
t [Y] = t [Y] and t [Z] = t [Z]
3 1 3 2
t [Y] = t [Y] and t [Z] = t [Z]
4 2 4 1
In other words if t and t are given by:
1 2
t = [X, Y , Z ], and
1 1 1
t = [X, Y , Z ]
2 2 2
then there must be tuples t and t such that:
3 4
t = [X, Y , Z ], and
3 1 2
t = [X, Y , Z ]
4 2 1
We are, therefore, insisting that every value of Y appears with every value of Z to keep the
relation instances consistent. In other words, the above conditions insist that Y and Z are
determined by X alone and there is no relationship between Y and Z since Y and Z appear in
every possible pair and hence these pairings present no information and are of no significance.
Only if some of these pairings were not present, there would be some significance in the pairings.

Notes If Z is single-valued and functionally dependent on X then Z = Z . If Z is multivalue
1 2
dependent on X then Z <> Z .
1 2
The theory of multivalued dependencies is very similar to that for functional dependencies.
+
Given D a set of MVDs, we may find D , the closure of D using a set of axioms. We do not discuss
the axioms here. You may refer this topic in further readings.
We have considered an example of Programmer(Emp name, projects, languages) and discussed
the problems that may arise if the relation is not normalised further. We also saw how the
relation could be decomposed into P1(emp name, projects) and P2(emp name, languages) to
overcome these problems. The decomposed relations are in fourth normal form (4NF), which
we shall now define.
We now define 4NF. A relation R is in 4NF if, whenever a multivalued dependency X Y
holds, then either
1. The dependency is trivial
2. X is a candidate key for R.

The dependency X ø or X Y in a relation R (X, Y) is trivial, since they must hold for all
R (X, Y). Similarly, in a trivial MVD (X, Y) Z must hold for all relations R (X, Y, Z) with only
three attributes.
If a relation has more than one multivalued attribute, we should decompose it into fourth
normal form using the following rules of decomposition:

For a relation R(X,Y,Z), if it contains two nontrivial MVDs X Y and X Z then decompose
the relation into R (X,Y) and R (X,Z) or more specifically, if there holds a non-trivial MVD in a
1 2
relation R (X,Y,Z) of the form X Y, such that X) Y = , that is the set of attributes X and Y
are disjoint, then R must be decomposed to R (X,Y) and R (X,Z), where Z represents all attributes
1 2
other than those in X and Y.

112 LOVELY PROFESSIONAL UNIVERSITY