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

RESPONSIBILITY MEMBERSHIP

Executive Department

Executive Committee

Jones
Jones
Jones
Cooper
Cooper
Cooper

Administration
Finance
Info. Technology
Marketing
Personnel
Production

Jones
Jones
Cooper
Cooper

Planning
Technology
R & D

Recruitment

Figure 10-9 Data model in fourth normal form.

If a data model is in the fourth normal form, no multivalued dependencies exist.

Here is what must be done to make this transformation.

Transformation to Fourth Normal Form (4NF)

Remove multivalued dependencies.

Figure 10-9 shows the resolution of the multivalued dependencies. The two rela-
tions are in the fourth normal form.
When you examine the two relations in Figure 10-9, you can easily establish that
these relations are free from update, deletion, or addition anomalies.

Fifth Normal Form

When you transform a data model into second, third, and Boyce-Codd normal
forms, you are able to remove anomalies resulting from functional dependencies.
After going through the steps and arriving at a data model in the Boyce-Codd
normal form, the data model is free from functional dependencies. When you
proceed further and transform the data model into a fourth normal form relation,
you are able to remove anomalies resulting from multivalued dependencies.
A further step transforming the data model into fifth normal form removes
anomalies arising from what are known as join dependencies. What is the definition
of join dependency? Go back and look at the figures showing the steps for the earlier
normal forms. In each transformation step, the original relation is decomposed into
smaller relations. When you inspect the smaller relations, you note that the original
relation may be reconstructed from the decomposed smaller relations. However, if
a relation has join dependencies and if we are able to decompose the relation into
smaller relations, it will not be possible to put the decomposed relations together
and recreate the original relation. The smaller relations cannot be joined together
to come up with the original relation. The original relation is important because that
relation was obtained directly from information requirements. Therefore, in what-
ever ways you may decompose the original relation to normalize it, you should be
able to go back to the original relation from the decomposed relations.
Figure 10-10 shows a relation that has join dependency. Note the columns in the
relation shown in the figure.
This relation describes the materials supplied by suppliers to various buildings
that are being constructed. Building B45 gets sheet rock from supplier S67 and