Databases Reference
In-Depth Information
ground and state categorically that in my opinion, FD preservation is the objective to give up on, in those cases in
which there's a conflict. 5
ANOTHER EXAMPLE
I suggested at the beginning of the previous section that the SJT example might be considered pathological. Now,
however, I'm going to claim it's not, not entirely; I'm going to give several more examples that I think demonstrate
that the issue of FD preservation arises more often than you might think.
Normalization as commonly perceived is a process of stepping from 1NF to 2NF to 3NF (etc.) in sequence.
Let's agree to refer to that process as commonly perceived—i.e., stepping from 1NF to 2NF to 3NF (etc.) in
sequence—as “the conventional normalization procedure.” In this section and the next two, then, I want to present a
series of examples to demonstrate that the conventional normalization procedure isn't necessarily a good idea if
followed too blindly. My first example involves a relvar that looks like this:
RX1 { SNO , PNO , CITY , STATUS , QTY }
The name RX1 stands for “relvar example 1”; the predicate is Supplier SNO is located in city CITY, which has status
STATUS, and supplies part PNO in quantity QTY. Assume the following FDs hold in this relvar:
{ SNO } { CITY }
{ CITY } { STATUS }
{ SNO , PNO } { QTY }
It's intuitively obvious that the following FDs hold too, implicitly: 6
{ SNO } { STATUS }
{ SNO , PNO } { CITY , STATUS }
In fact, the second of these can be expanded to {SNO,PNO} → H , where H is the entire heading; in other words,
{SNO,PNO} is a key for relvar RX1.
Recall now that a relvar R is in 2NF if and only if, for every key K and every nonkey attribute A , the FD K
{ A } is irreducible. Clearly, then, RX1 isn't in 2NF, because the FD {SNO,PNO} → {CITY} is an FD of RX1 but
isn't irreducible; to be specific, it isn't irreducible because the FD {SNO} → {CITY} also holds in that relvar. The
conventional normalization procedure would thus recommend that we decompose the relvar by applying Heath's
Theorem to that FD {SNO} → {CITY}. But if we do, this is what we get:
RX1A { SNO , CITY }
KEY { SNO }
RX1B { SNO , PNO , STATUS , QTY }
KEY { SNO , PNO }
5 In the case at hand, of course, we must decompose the relvar as indicated if we want to be able to record the fact that (say) Professor Black
teaches physics even though Professor Black has no students at the moment.
6 I'll have quite a lot more to say on the question of FDs that hold implicitly (“implicit FDs”) in the next chapter, also in Chapter 11.
Search WWH ::

Custom Search