Databases Reference
In-Depth Information
For each subject, each student of that subject is taught by only one teacher.
Each teacher teaches only one subject.
Each student studies several subjects, and hence is taught by several teachers (in general).
Each subject is studied by several students (in general).
Each subject is taught by several teachers (in general).
Distinct students of the same subject might or might not be taught that subject by the same teacher.
A sample value for this relvar that conforms to these rules is shown in Fig. 6.2.
┌───────┬─────────┬─────────────┐
│ S │ J │ T │
├───────┼─────────┼─────────────┤
│ Smith │ Math │ Prof. White │
│ Smith │ Physics │ Prof. Green │
│ Jones │ Math │ Prof. White │
│ Jones │ Physics │ Prof. Brown │
└───────┴─────────┴─────────────┘
Fig. 6.2: Sample value for relvar SJT
What are the FDs for relvar SJT? From the first business rule, we have {S,J} → {T}. From the second, we
have {T} → {J}. A careful analysis of the remaining rules will show that no other FDs hold other than ones that are
either trivial or reducible (or both). Thus, the only nontrivial, irreducible FDs that hold are these two:
{ S , J }
→
{ T }
{ T }
→
{ J }
So what are the keys? Well, {S,J} is a key, since the entire heading is clearly functionally dependent on
{S,J} and not on any proper subset of {S,J}. Also, {S,T} is a key, because:
a.
It's certainly the case, given that the FD {T} → {J} holds, that the entire heading is functionally dependent
on {S,T}.
b.
It's also the case, given that the FDs {S} → {J} and {T} → {S} do
not
hold, that the entire heading isn't
functionally dependent on any proper subset of {S,T}.
So there are two keys, {S,J} and {S,T}.
4
Perhaps more to the point, {T} is
not
a key, and so relvar SJT is
subject to an FD that's not “an arrow out of a key” (i.e., it's not implied by keys, to state the matter a trifle more
formally). As a consequence, the relvar isn't in BCNF, though it is in 3NF. (
Exercise:
Check this claim.) And it
suffers from redundancy; for example, given the sample value shown in Fig. 6.2, the fact that Professor White
teaches Math appears twice. As you would expect, it also suffers from update anomalies; for example, with respect
4
Which overlap, as you can see. By the way, as with relvar SNP in Chapter 4, I've chosen not to make either of those keys primary, which is
why there's no double underlining in Fig. 6.2.
