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

So you note that this step has resolved the problem of transitive dependencies
and the data manipulation problems, at least the ones we have considered. Before
we declare that the resultant data model is free from all types of data dependency
problems, let us examine the model one more time.
Boyce-Codd Normal Form
Consider the EMPLOYEE-PROJECT relation in Figure 10-5. Think about the
ChrgCD attribute. A particular charge code indicates the specific employee's role
in an assignment. Also, each project may be associated with several charge codes
depending on the employees and their roles in the project. The charge code is not
for the project assignment. The attribute ChrgCD does not depend on the full
primary key or on a partial primary key. The dependency is the other way around.
In the EMPLOYEE-PROJECT relation, EmpId depends on ChrgCD and not
the other way around. Note how this is different from partial key dependency. Here
a partial key attribute is dependent on a nonkey attribute. This kind of dependency
also violates the functional dependency rule for the relational data model.
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 Boyce-
Codd normal form. In other words, the condition for a Boyce-Codd normal form
data model is as follows:
If a data model is in the Boyce-Codd normal form, no partial key attribute may be
dependent on another nonkey attribute.
Here is what must be done to make this transformation.
Transformation to Third Normal Form (3NF)
Remove anomalies from dependencies of key components.
Figures 10-6 and 10-7 show the resolution of the remaining dependencies. The
relations shown in both figures are all in the Boyce-Codd normal form.
Note how the resolution is done. The EMPLOYEE-PROJECT relation is decom-
posed into two additional relations, CHRG-EMP and PROJ-CHRG. Note that
duplicate rows are eliminated when the additional relations are formed.
Again, note that decomposition is a basic technique for normalization. The final
set of relations in Figures 10-6 and 10-7 is free from all types of problems resulting
from invalid functional dependencies.
HIGHER NORMAL FORMS
Once you transform an initial data model into a data model conforming to the prin-
ciples of the fundamental normal forms, most of the discrepancies are removed. For
all practical purposes, your resultant data model is a good relational data model. It
will satisfy all the primary constraints of a relational data model. The major prob-
lems with functional dependencies are resolved.