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

mines the value of a second attribute in a relation, we say that the second attribute
is functionally dependent on the first attribute. The discussion on functional depen-
dencies in Chapter 8 concluded with a functional dependency rule. Let us repeat
the functional dependency rule:
Each data item in a tuple of a relation is uniquely and functionally determined by the
primary key, by the whole primary key, and only by the primary key.
Examine the dependencies of data items in the PROJECT-ASSIGNMENT rela-
tion in Figure 10-3. You know that this relation is in the first normal form, having
gone through the process of removal of repeating groups of attributes. Let us inspect
the dependency of each attribute on the whole primary key consisting of EmpId
and ProjNo. Only the following attributes depend on the whole primary key:
ChrgCD, Start, End, and Hrs. The remaining nonkey attributes do not appear to be
functionally dependent on the whole primary key. They seem to functionally depend
on one or another part of the primary key.
This step in the normalization process deals specifically with this type of problem.
Once this type of problem is resolved, the data model becomes transformed to a
data model in the second normal form. In other words, the condition for a second
normal form data model is as follows:
If a data model is in the second normal form, no nonkey attributes may be dependent
on part of the primary key.
Therefore, if there are partial key dependencies in a data model, this step resolves
this type of dependencies. Here is what must be done to make this transformation.
Transformation to Second Normal Form (2NF)
Remove partial key dependencies.
If you look at the other attributes in the PROJECT-ASSIGNMENT relation in
Figure 10-3, you will note that the following attributes depend on just EmpId, a part
of the primary key: Name, Salary, Position, Bonus, DptNo, DeptName, and Manager.
The attribute ProjDesc depends on ProjNo, another part of the primary key. These
are partial key dependencies. This step resolves partial key dependencies. Now look
at Figure 10-4 that shows the resolution of partial key dependencies. The relations
shown in this figure are in the second normal form.
Note how the resolution is done. The original relation has been decomposed into
three separate relations. In each relation, to make sure that each row is unique,
duplicate rows are eliminated. For example, multiple duplicate rows for employee
Simpson have been replaced by a single row in the EMPLOYEE relation.
Decomposition is an underlying technique for normalization. If you go carefully
through each of the three relations, you will be satisfied that none of these has any
partial key dependencies. So this step has rectified the problem of partial key
dependencies. But what about the types of anomalies encountered during data
manipulation?
Let us examine whether the transformation step has rectified the types of update,
deletion, and addition anomalies encountered before the model was transformed