Databases Reference
In-Depth Information
To elaborate briefly: It's certainly the case that every FD that holds in S is implied by these three taken together, so
these three certainly constitute a cover. Also, each of the three has a singleton dependant; no attribute can be
dropped from any of the determinants; and none of the FDs can be discarded. It follows that the cover is in fact an
irreducible one. By contrast, the following sets of FDs are covers for the FDs that hold in S but aren't irreducible (in
each case, why not?):
{ SNO }
→
{ SNAME , CITY }
{ CITY }
→
{ STATUS }
{ SNO , SNAME }
→
{ CITY }
{ SNO }
→
{ SNAME }
{ CITY }
→
{ STATUS }
{ SNO }
→
{ SNAME }
{ SNO }
→
{ CITY }
{ CITY }
→
{ STATUS }
{ SNO }
→
{ STATUS }
Now let's get back to the 3NF procedure. In particular, let's see how it works out for the SJT example.
10
Just to remind you, the relvar had attributes S, J, and T; keys {S,J} and {S,T}; and was subject to the FD {T} → {J}.
So these FDs hold:
{ S , J }
→
{ T }
{ S , T }
→
{ J }
{ T }
→
{ J }
It's easy to see, however, that the FD {S,T} → {J} is redundant here—in fact, I effectively assumed as much
when I first discussed this example earlier in the chapter—and hence that the other two FDs together form an
irreducible cover (which I'll call
C
):
{ S , J }
→
{ T }
{ T }
→
{ J }
Now we can apply the 3NF procedure. We start with an empty set of headings
S
. The second step does two
things: It gathers together FDs in
C
that have the same left side—something that's effectively already been done in
the example—and then adds the sets (actually headings)
{ S , J , T }
{ T , J }
to
S
. The third step has no effect, since every attribute of the original relvar is now contained in at least one element
of
S
. The last step also has no effect, since the element {S,J,T} of
S
is a superkey for the original relvar. Overall,
therefore, the 3NF procedure tells us that relvar S can be nonloss decomposed, in an FD preserving way, into its
projections on {S,J,T} and {T,J}. Points arising:
10
Of course SJT is already in 3NF, but we can still apply the procedure to it─and I have my reasons, which will become apparent later, for
wanting to do so.
