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):

     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.^[57] 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?