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:^[56]

     { 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 }

Observe now that the FD {CITY} → {STATUS} is lost in this decomposition. So one immediate lesson is that the issue of FD preservation can be relevant to the step from 1NF to 2NF—not just to the step from 3NF to BCNF, which is the step illustrated by the SJT example in the previous section.

How can we preserve the FD in this example? One answer is: By decomposing not on the basis of the FD {SNO} → {CITY}, but rather on the basis of the FD {SNO} → {CITY,STATUS}. Note, however, that this FD isn’t one of the FDs originally listed explicitly, nor is it one of the ones I said were obviously implied by those explicit ones; it’s thus unlikely to have been chosen as a basis for decomposition. Nevertheless, suppose we do choose it and perform the corresponding decomposition. Here’s the result:

     RX1A' { SNO , CITY , STATUS }
           KEY { SNO }

     RX1B' { SNO , PNO , QTY }
           KEY { SNO , PNO }

In this decomposition, STATUS appears in the relvar with key {SNO} and not the relvar with key {SNO,PNO}, and the FD {CITY} → {STATUS} is thereby preserved. Note: Of course, relvar RX1A' here is still not in 3NF, so we would probably want to decompose it further. Again, however, we need to be a little careful; to be specific, we need to decompose on the basis of the FD {CITY} → {STATUS}, not {SNO} → {STATUS}, or we’ll lose an FD again. But {CITY} → {STATUS} is the FD the conventional normalization procedure would tell us to use, so there shouldn’t be a problem here.

An alternative to the foregoing would be to decompose the original relvar RX1 on the basis of the FD {CITY} → {STATUS}:

     RX1A'' { CITY , STATUS }
            KEY { CITY }

     RX1B'' { SNO , PNO , CITY , QTY }
            KEY { SNO , PNO }

This decomposition also preserves the FD {CITY} → {STATUS}. Note, however, that this FD isn’t the one that causes the 2NF violation (it isn’t “an arrow out of a proper subkey”); again, therefore, it’s quite unlikely in practice, if we’re following the conventional normalization procedure, that we would have chosen it as a basis for decomposition at this stage. Note also that relvar RX1B'' here is still not in 3NF, so we would probably want to decompose it further. I’ll leave the details of that further decomposition for you to think about.