DATA NORMALIZATION METHOD - Database Design and Development: An Essential Guide for IT Professionals - page 314

Initial table Initial relation

Executive Department Committee Executive Department Committee

Jones Administration Planning Jones

Jones
Jones
Jones
Jones
Jones
Cooper
Cooper
Cooper
Cooper
Cooper
Cooper

Administration Planning

Finance Technology

Finance Planning

Info. Technology

Info. Technology Planning

Cooper Marketing R & D

Administration Technology

Personnel Recruitment

Finance Technology

Production

Info. Technology Technology

Marketing R & D

Personnel R & D

Production R & D

Marketing Recruitment

Personnel Recruitment

Production Recruitment

Figure 10-8 Multivalued dependencies.

Fourth Normal Form

Before we discuss the fourth normal form for a data model, we need to define the
concept of multivalued dependencies. Consider the following assumptions about the
responsibilities and participation of company executives:

• Each executive may have direct responsibility for several departments.
• Each executive may be a member of several management committees.
• The departments and committees related to a particular executive are inde-
pendent of each other.

Figure 10-8 contains data in the form of an initial data model to illustrate these
assumptions. The first part of the figure shows the basic table and the second part
the transformed relation.
Note that for each value of Executive attribute, there are multiple values for
Department attribute and multiple values for Committee attribute. Note also that
the values of Department attribute for an executive are independent of the values
of Committee attribute. This type of dependency is known as multivalued depen-
dency. A multivalued dependency exists in a relation consisting of at least three
attributes A, B, and C such that for each value of A, there is a defined set of values
for B and another defined set of values for C, and furthermore, the set of values for
B is independent of the set of values for C.
Now observe the relation shown in the second part of Figure 10-8. Because the
relation indicating the relationship between the attributes just contains the primary
key, the relation is even in the Boyce-Codd normal form. However, by going through
the rows of this relation, you can easily see that the three types of anomalies—
update, deletion, and addition—are present in the relation.
This step in normalization process deals with this type of problem. Once this type
of problem is resolved, the data model is transformed to a data model in the fourth
normal form. In other words, the condition for a fourth normal form data model is
as follows: