The next normal form I want to discuss, briefly, is superkey normal form, SKNF. Let me immediately say that SKNF doesn’t seem to be very important in its own right; the main reason I mention it at all is that many textbooks give what’s essentially a definition of it as an incorrect definition of fifth normal form. For example, the following is a paraphrased extract from a textbook of my own[126] (warning! untruths coming up!):
Relvar R is in 5NF if and only if every nontrivial JD that holds in R is implied by the keys of R, where:
Part a. of this definition is correct, of course, but part b. isn’t. To see why not, consider the following counterexample. Let relvar SNC be the projection of the suppliers relvar S on the attributes {SNO,SNAME,CITY}. SNC is in 5NF. Yet the following JD—
{ { SNO , SNAME } , { SNO , CITY } , { SNAME , CITY } }
—holds in relvar SNC, and the {SNAME,CITY} component isn’t a superkey for that relvar.
Observe now that the foregoing “definition” refers explicitly to nontrivial JDs. Thus, you might be thinking that what we need to do, in order to correct it, is to replace nontrivial by irreducible (notice that the JD just shown, the one that holds in SNC, is reducible—the {SNAME,CITY} component could be dropped without loss). However, such is not the case. Here’s a more complicated counterexample:
Let relvar R have attributes A, B, and C (only); let AB, BC, and CA each be keys of R; and let the JD
{AB,BC,CA}—call it J—hold in R.Then (a) no additional dependencies are implied by J and those keys, apart from trivial ones; (b) J is irreducible with respect to R. (These two points might not be obvious, but they are in fact correct.)
It follows that R isn’t in 5NF (the membership algorithm fails on J), and yet each component of J is a superkey.
Note: If you’d prefer a slightly more concrete example, take A, B, and C to be “favorite color,” “favorite food,” and “favorite composer,” respectively, and let the predicate be There exists a person whose favorite color is A, favorite food is B, and favorite composer is C. Further, let there be business rules to the effect that:
No two distinct persons have more than one favorite in common.
No three distinct persons are such that, for each favorite, two of those three have it in common.
Exercise: Invent some sample data for this relvar. If you try this exercise, I think you’ll see why the specified key constraints and JD make sense.
With the foregoing by way of motivation, then, let’s define another normal form:
Now, I’ve said that SKNF isn’t really very interesting. That’s true—but there’s at least one theorem that concerns it:
Theorem: 5NF implies SKNF, but the reverse is not true; also, SKNF implies 4NF, but the reverse is not true.
In other words, SKNF falls strictly between 4NF and 5NF (i.e., it’s stronger than 4NF and weaker than 5NF). That said, however, I should add that SKNF and 5NF coincide in the common special case in which the pertinent relvar R has just one key (as indeed is obvious from the definitions)—another reason, perhaps, for thinking SKNF isn’t all that interesting in its right. Nevertheless, the fact remains that there’s a logical difference between SKNF and 5NF, which is why I include it in this chapter.