Database Reference
In-Depth Information
STUDENT_ACTIVITY
ACTIVITY_FEE
Figure 3-14
the 2NF StUDENt_
aCtIVItY and aCtIVItY_FEE
relations
The Activity column in STUDENT_ACTIVITY becomes a foreign key. The new relations
are shown in Figure 3-14. Now, are the two new relations in 2NF? Yes. STUDENT_ACTIVITY
still has a composite primary key, but now has no attributes that are dependent on only a part
of this composite key. ACTIVITY _FEE has a set of attributes ( just one each in this case) that
are dependent on the entire primary key.
Third Normal Form
However, the conditions necessary for 2NF do not eliminate all anomalies. To deal with ad-
ditional anomalies, Codd defined 3NF. A relation is in 3NF if and only if
it is in 2NF
and
there
are no non-key attributes determined by another non-key attribute.
The technical name for a
non-key attribute determined by another non-key attribute is
transitive dependency
. We can
therefore restate the definition of 3NF: A relation is in 3NF if and only if
it is in 2NF
and
it has no
transitive dependencies.
Thus, in order for our relation
R (
A
,
B
, N, O, P)
to be in 3NF, none of the
non-key attributes
N
,
O
, or
P
can be determined by
N
,
O
, or
P
.
For example, consider the relation STUDENT_HOUSING (
StudentID
, Building, Fee)
shown in Figure 3-15. The STUDENT_HOUSING schema is:
STUDENT_HOUSING (
StudentID
, Building, HousingFee)
Here we have a single-attribute primary key, StudentID, so the relation is in 2NF because
there is no possibility of a non-key attribute being dependent on only part of the primary key.
Furthermore, if we know the student, we can determine the building where he or she is residing, so:
(StudentID)
S
Building
However, the building fee is independent of which student is housed in the building, and,
in fact, the same fee is charged for every room in a building. Therefore, Building determines
HousingFee:
(Building)
S
(HousingFee)
STUDENT_HOUSING
Figure 3-15
the 2NF StUDENt_
HOUSING relation
Search WWH ::
Custom Search