Database Concepts - Data Warehouse Systems: Design and Implementation - page 27

determines a set of values for Y , which is independent of R \ XY ,where
' ' indicates the set difference. In this case, we say that X multidetermines

Y . In the relation in Fig. 2.7 c, the multivalued dependency EmployeeID

\

→→

KindOfWork holds, and consequently, TerritoryID KindOfWork . Func-

tional dependencies are special cases of multivalued dependencies, that is,
every functional dependency is also a multivalued dependency. A multivalued
dependency X →→ Y is said to be trivial if either Y ⊆ X or X ∪ Y = R ;
otherwise, it is nontrivial . This is straightforward since in those cases,
R \ XY =

→→

∅

holds.
A normal form is an integrity constraint aimed at guaranteeing that a
relational schema satisfies particular properties. Since the beginning of the
relational model in the 1970s, many types of normal forms have been defined.
In addition, normal forms have also been defined for other models, such as
the entity-relationship model. In the following, we consider only five normal
forms that are widely used in relational databases.
As already said, the relational model allows only attributes that are atomic
and monovalued. This restriction is called the first normal form .
The second normal form prevents redundancies such as those in the
table OrderDetails in Fig. 2.7 a. To define the second normal form, we need
the concept of partial dependency, defined next:
￿ An attribute A in a relation schema R is called a prime attribute if it
belongs to some key in R .Otherwise,itiscalled nonprime .
￿ In a relation schema R such that X is a key of R , Z ⊂ X ,and Y is a
nonprime attribute, a dependency of the form Z → Y is called partial .
A relation R is in the second normal form with respect to a set of functional
dependencies F if F + does not contain any partial dependency. In other
words, a relation schema is in the second normal form if every nonprime
attribute is fully functionally dependent on every key in R . In the example of
Fig. 2.7 a, Product

Discount is a partial dependency. Therefore, the relation
is not in the second normal form. To make the relation comply with the
second normal form, the attribute Discount must be removed from the table
OrderDetails and must be added to the table Products in order to store the
information about the product discounts.
The third normal form prevents redundancies such as those in the table
Products in Fig. 2.7 b. In order to define the third normal form, we must define
one additional concept:
￿ A dependency X → Z is transitive if there is a set of attributes Y such
that the dependencies X → Y and Y → Z hold.
A relation R is in the third normal form with respect to a set of
functional dependencies F if it is in the second normal form and there are
no transitive dependencies between a key and a nonprime attribute in F + .
The table Product above is not in the third normal form, since there is a
transitive dependency from ProductID to CategoryID and from CategoryID to

→