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

It does if a student belongs to just one club or participates in just one activity, but it does
not if a student belongs to more than one club or participates in more than one activity.
Looking at the data, student Davis with StudentID 200 participates in both Skiing and
Swimming, so StudentID does not determine Club. StudentID does not determine ActivityFee
or AmountPaid, either.
Now consider the StudentName column. Does StudentName determine StudentID? Is,
for example, the value 'Jones' always paired with the same value of StudentID? No, there are
two students named 'Jones', and they have different StudentID values. StudentName does not
determine any other column in this table, either.
Considering the next column, Activity, we know that many students can belong to a club.
Therefore, Activity does not determine StudentID or StudentName. Does Activity determine
ActivityFee? Is the value 'Skiing', for example, always paired with the same value of ActivityFee?
From these data, it appears so, and using just this sample data, we can conclude that Activity
determines ActivityFee.
However, this data is just a sample. Logically, it is possible for students to pay different
costs, perhaps because they select different levels of activity participation. If that were the
case, then we would say that
(StudentID, Activity) S ActivityFee
To find out, we need to check with the users. Here, assume that all students pay the same
fee for a given activity. The last column is AmountPaid, and it does not determine anything.
So far, we have two functional dependencies:
StudentID S StudentName
Activity S ActivityFee
Are there other functional dependencies with composite determinants? No single column
determines AmountPaid, so consider possible composite determinants for it. AmountPaid is
dependent on both the student and the club the student has joined. Therefore, it is determined
by the combination of the determinants StudentID and Activity. Thus, we can say
(StudentID, Activity) S AmountPaid
So far we have three determinants: StudentID, Activity, and (StudentID, Activity). Are any
of these candidate keys? Do any of these determinants identify a unique row? From the data, it
appears that (StudentID, Activity) identifies a unique row and is a candidate key. Again, in real
situations, we would need to check out this assumption with the users.
STUDENT_ACTIVITY_PAYMENT is not in BCNF because columns StudentID and
Activity are both determinants, but neither is a candidate key. StudentID and Activity are only
part of the candidate key (StudentID, Activity).
By The WAy Both StudentID and Activity are part of the candidate key (StudentID,
Activity). This, however, is not good enough. A determinant must have all of
the same columns to be the same as a candidate key. Remember, as we stated above:
I swear to construct my tables so that all non-key columns are dependent on the key,
the whole key, and nothing but the key, so help me Codd!
To normalize this table, we need to construct tables so that every determinant is a candi-
date key. We can do this by creating a separate table for each functional dependency as we did
before. The result is:
STUDENT ( StudentID , StudentName)
ACTIVITY ( Activity , ActivityFee)
PAYMEN T ( StudentID , Activity , AmountPaid)