Databases Reference
In-Depth Information
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.
Aside:
Relvar RX1A here is certainly in 2NF. By contrast, relvar RX1B isn't, because the FD {SNO,PNO}
→ {STATUS} is reducible. So we can apply Heath's Theorem again to decompose it into its projections on
{SNO,STATUS} and {SNO,PNO,QTY}, both of which are in 2NF; however, the damage has already been
done, as it were─the FD {CITY} → {STATUS} has already been lost.
End of aside.
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.
… AND ANOTHER
Let's look at another example. Suppose suppliers are partitioned into classes (C1, C2, etc.), so we have a relvar
RX2 that looks like this (as I did with RX1, I'll ignore supplier names for simplicity):
