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

previous step, we removed partial key dependencies. Let us examine the resulting
data model to see whether any more irregular functional dependencies still exist.
Remember the goal is to make each relation in the data model into a form where
each data item in a tuple is functionally dependent only on the full primary key and
nothing but the full primary key.
Refer to the three relations shown in Figure 10-4. Let us inspect these relations
one by one. The attribute ProjDesc functionally depends on the primary key ProjNo.
So this relation PROJECT is correct. Next look at the relation EMPLOYEE-
PROJECT. In this relation, each of the attributes ChrgCD, Start, End, and Hrs
depends on full primary key EmpId, ProjNo.
Now examine the relation EMPLOYEE carefully. What about the attributes
Position and Bonus? Bonus depends on the position. Bonus for an Analyst is dif-
ferent from that for a Technician. Therefore, in that relation, the attribute Bonus is
functionally dependent on another attribute Position, not on the primary key. Look
further. How about the attributes DeptName and Manager? Do they depend on
the primary key EmpId? Not really. These two attributes functionally depend on
another attribute in the relation, namely, DptNo.
So what is the conclusion from your observation? In the relation EMPLOYEE,
only the two attributes Name and Salary depend on the primary key EmpId. The
other attributes do not depend on the primary key. Bonus depends on Position;
DeptName and Manager depend on DptNo.
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 third
normal form. In other words, the condition for a third normal form data model is
as follows:
If a data model is in the third normal form, no nonkey attributes may be dependent
on another nonkey attribute.
In the relation EMPLOYEE, dependency of the attribute DeptName on the
primary key EmpId is not direct. The dependency is passed over to the primary key
through another nonkey attribute DptNo. This passing over of the dependency
means that the dependency on the primary key is a transitive dependency—passed
over through another nonkey attribute, DptNo. Therefore, this type of problematic
dependency is also called a transitive dependency in a relation. If there are transi-
tive dependencies in a data model, this step resolves this type of dependencies. Here
is what must be done to make this transformation.
Transformation to Third Normal Form (3NF)
Remove transitive dependencies.
Figure 10-5 shows the resolution of transitive dependencies. The relations shown
in the figure are all in the third normal form.
Note how the resolution is done. The EMPLOYEE relation is further decom-
posed into two additional relations, POSITION and DEPARTMENT. In each rela-
tion, to ensure that each row is unique, duplicate rows are eliminated. For example,
multiple duplicate rows for the position Analyst in the EMPLOYEE relation have
been replaced by a single row in the POSITION relation.