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

a b c

Employee
Territories
EmployeeID
TerritoryID
KindOfWork

Products

OrderDetails

ProductID
ProductName
QuantityPerUnit
UnitPrice
...
CategoryName
Description
Picture

OrderID
ProductID
UnitPrice
Quantity
Discount

Fig. 2.7 Examples of relations that are not normalized

Consider now the relation Products in Fig. 2.7 b, which is a variation of
the relation with the same name in Fig. 2.4 . In this case, we have included
the category information (name, description, and picture) in the Products
relation. It is easy to see that such information about a category is repeated
for each product with the same category. Therefore, when, for example,
the description of a category needs to be updated, we must ensure that all
tuples in the relation Products , corresponding to the same category, are also
updated; otherwise, there will be inconsistencies.
Finally, let us analyze the relation EmployeeTerritories in Fig. 2.7 c, where an
additional attribute KindOfWork has been added with respect to the relation
with the same name in Fig. 2.4 . Assume that an employee can do many kinds
of work, independently of the territories in which she carries out her work.
Thus, the information about the kind of work of an employee will be repeated
as many times as the number of territories she is assigned to.
Dependencies and normal forms are used to describe the redundancies
above. A functional dependency is a constraint between two sets of
attributes in a relation. Given a relation R and two sets of attributes X and
Y in R , a functional dependency X → Y holds if and only if in all the tuples
of the relation, each value of X is associated with at most one value of Y .In
this case, it is said that X determines Y . Note that a key is a particular case
of a functional dependency, where the set of attributes composing the key
functionally determines all of the attributes in the relation. In what follows,
F will denote a set of functional dependencies and F + the set F augmented
with the set of all functional dependencies that can be inferred from F .For
example, if A → B and B → C ,then A → C can be inferred.
The redundancies in Fig. 2.7 a, b can be expressed by means of functional
dependencies. For example, in the relation OrderDetails in Fig. 2.7 a, there
is the functional dependency ProductID

Discount . Also, in the relation
Products in Fig. 2.7 b, we have the functional dependencies ProductID

→

→

CategoryID and CategoryID CategoryName .

The redundancy in the relation EmployeeTerritories in Fig. 2.7 ciscaptured
by another kind of dependency. Given two sets of attributes X and Y in a
relation R ,a multivalued dependency X →→ Y holds if the value of X

→