The Relational Model and Normalization - Database Processing: Fundamentals, Design, and Implementation - page 134

The third source of anomalies is esoteric. These problems involve specific, rare, and even
strange data constraints. Accordingly, we will not discuss them in this text.

From First Normal Form to Boyce-Codd Normal Form Step by Step
Any table that meets the definition of a relation in Figure 3-4 is defined as being in 1NF. This
means that the following must hold: The cells of a table must be a single value, and neither re-
peating groups nor arrays are allowed as values; all entries in a column must be of the same data
type; each column must have a unique name, but the order of the columns in the table is not
significant; no two rows in a table may be identical, but the order of the rows is not significant.

Second Normal Form
When Codd discovered anomalies in 1NF tables, he defined 2NF to eliminate some of these
anomalies. A relation is in 2NF if and only if it is in 1NF and all non-key attributes are deter-
mined by the entire primary key. This means that if the primary key is a composite primary key,
then no non-key attribute can be determined by an attribute or set of attributes that make up
only part of the key. Thus, if you have a relation R ( A , B , N, O, P) with the composite key (A, B) ,
then none of the non-key attributes N , O , or P can be determined by just A or just B .
Note that the only way a non-key attribute can be dependent on part of the primary key
is if there is a composite primary key. This means that relations with single-attribute primary
keys are automatically in 2NF.
For example, consider the STUDENT_ACTIVITY relation:

STUDENT_ACTIVITY ( StudentID , Activity , ActivityFee)

The STUDENT_ACTIVITY relation is shown with sample data in Figure 3-13. Note that
STUDENT_ACTIVITY has the composite primary key (StudentID, Activity), which allows us
to determine the fee a particular student will have to pay for a particular activity. However,
because fees are determined by activities, Fee is also functionally dependent on just Activity
itself, and we can say that Fee is partially dependent on the key of the table. The set of func-
tional dependencies is therefore:

(StudentID, Activity) S (ActivityFee)
(Activity) S (ActivityFee)

Thus, there is a non-key attribute determined by part of the composite primary key, and
the STUDENT_ACTIVITY relation is not in 2NF. What do we do in this case? We will have to
move the columns of the functional dependency based on the partial primary key attribute
into a separate relation while leaving the determinant in the original relation as a foreign key.
We will end up with two relations:

STUDENT_ACTIVITY ( StudentID , Activity )
ACTIVITY_FEE ( Activity , ActivityFee)

STUDENT_ACTIVITY

Figure 3-13
the 1NF StUDENt_
aCtIVItY relation