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.