Databases Reference
In-Depth Information
RX2 { SNO , CLASS , CITY , STATUS }
KEY { SNO }
The predicate is
Supplier SNO is in class CLASS, is located in city CITY, and has status STATUS.
Suppose also that
(a) each class has just one associated status, and (b) each city has just one associated status as well, but (c) classes
and cities are otherwise quite independent of each other. Then the following FDs hold:
{ CLASS }
→
{ STATUS }
{ CITY }
→
{ STATUS }
Note:
I'm also assuming there's a business rule in effect that says that, for any given supplier, the city status is
equal to the class status (that's why we're able to get away with just one STATUS attribute).
Recall now that a relvar
R
is in 3NF if and only if, for every nontrivial FD
X
→
Y
that holds in
R
,
X
is a
superkey or
Y
is a subkey. Clearly, then, RX2 isn't in 3NF, because in the FD {CITY} → {STATUS}, {CITY}
isn't a superkey and {STATUS} isn't a subkey. The conventional normalization procedure would thus recommend
that we decompose the relvar by applying Heath's Theorem to that FD {CITY} → {STATUS}. But if we do, this is
what we get (two projection relvars both in 3NF):
RX2A { CITY , STATUS }
KEY { CITY }
RX2B { SNO , CLASS , CITY }
KEY { SNO }
Observe now that the FD {CLASS} → {STATUS} is lost in this decomposition. (Of course, if we had done
the decomposition on the basis of that FD instead of the FD {CITY} → {STATUS}, then this latter FD would have
been lost instead.) So now we see the issue of FD preservation can also be relevant to the step from 2NF to 3NF.
Now, we can preserve the FD in this example by decomposing on the basis of the FD {SNO} →
{CLASS,CITY}—though once again this FD is unlikely to have been chosen as a basis for decomposition, since it
wasn't stated explicitly.
7
Be that as it may, here's the result:
RX2A′ { CLASS , CITY , STATUS }
KEY { CLASS , CITY }
RX2B′ { SNO , CLASS , CITY }
KEY { SNO }
In this decomposition, {CLASS,CITY} is a (composite) foreign key in RX2B′, referencing RX2A′. Relvar RX2B′
is in 3NF. However, relvar R2XA′ isn't even in 2NF, since the FD {CLASS,CITY} → {STATUS} is clearly
reducible. So if we decide to keep that relvar, the FDs {CLASS} → {STATUS} and {CITY} → {STATUS} will
have to be separately stated and enforced. Alternatively, we could decompose the relvar into its projections on
{CLASS,STATUS} and {CITY,STATUS}, in which case an appropriate multirelvar constraint will have to be
separately stated and enforced.
Exercise for the reader:
What would that constraint look like?
7
Nor is it likely to have been, either, since {SNO} is a key (in fact the only key) for relvar RX2. If anything, we might expect to see two FDs
stated separately, viz., {SNO} → {CLASS} and {SNO} → {CITY}.
