I discussed the concept of keys in general terms in Chapter 1, but it’s time to get a little more precise about the matter and to introduce some more terminology. First, here for the record is a precise definition of the term candidate key:
Definition: Let K be a subset of the heading of relvar R. Then K is a candidate key (or just key for short) for R if and only if it possesses both of the following properties:
Uniqueness: No valid value for R contains two distinct tuples with the same value for K.
Irreducibility: No proper subset of K has the uniqueness property.
Aside: This is the first definition we’ve encountered that involves some kind of irreducibility, but we’ll meet several more in the pages ahead—irreducibility of one kind or another is ubiquitous, and important, throughout the field of design theory in general, as we’ll see. Regarding key irreducibility in particular, one reason (not the only one) why it’s important is that if we were to specify a “key” that wasn’t irreducible, the DBMS wouldn’t be able to enforce the proper uniqueness constraint. For example, suppose we told the DBMS (lying!) that {SNO,CITY} was a key, and in fact the only key, for relvar S. Then the DBMS couldn’t enforce the constraint that supplier numbers are “globally” unique; instead, it could enforce only the weaker constraint that supplier numbers are “locally” unique, in the sense that they’re unique within the pertinent city. End of aside.
I’m not going to discuss the foregoing definition any further here, since the concept is so familiar[39]—but observe how the next few definitions depend on it:
Definition: A key attribute for relvar R is an attribute of R that’s part of at least one key of R.
Definition: A nonkey attribute for relvar R is an attribute of R that’s not part of any key of R.[40]
For example, in relvar SP, SNO and PNO are key attributes and QTY is a nonkey attribute.
Definition: A relvar is “all key” if and only if the entire heading is a key (in which case it’s the only key, necessarily)—equivalently, if and only if no proper subset of the entire is a key. Note: If a relvar is “all key,” then it certainly has no nonkey attributes, but the converse is false—a relvar can be such that all of its attributes are key attributes and yet not be “all key” (right?).
Definition: Let SK be a subset of the heading of relvar R. Then SK is a superkey for R if and only if it possesses the following property:
Uniqueness: No valid value for R contains two distinct tuples with the same value for SK.
More succinctly, a superkey for R is a subset of the heading of R that’s unique but not necessarily irreducible. In other words, we might say, loosely, that a superkey is a superset of a key (“loosely,” because of course the superset in question must still be a subset of the pertinent heading). Observe, therefore, that all keys are superkeys, but “most” superkeys aren’t keys. Note: A superkey that isn’t a key is sometimes said to be a proper superkey.
It’s convenient to define the notion of a subkey also:
Definition: Let SK be a subset of the heading of relvar R. Then SK is a subkey for R if and only if it’s a subset of at least one key of R. Note: A subkey that isn’t a key is sometimes said to be a proper subkey.
By way of example, consider relvar SP, which has just one key, {SNO,PNO}. That relvar has:
To close this section, note that if H and SK are the heading and a superkey, respectively, for relvar R, then the FD SK → H holds in R, necessarily; equivalently, the FD SK → Y holds in R for all subsets Y of H. The reason is that if two tuples of R have the same value for SK, then they must in fact be the very same tuple, in which case they obviously must have the same value for Y. Of course, all of these remarks apply in the important special case in which SK is not just a superkey but a key; as I put it earlier (very loosely, of course), there are always arrows out of keys. In fact, we can now make a more general statement: There are always arrows out of superkeys.
[39] Do note, however, that there’s no suggestion that relvars have just one key. Au contraire, in fact: A relvar can have any number of distinct keys, subject only to a limit that’s a logical consequence of the degree of the relvar in question. See Exercise 4.9.
[40] As a historical note, I remark that key and nonkey attributes were called prime and nonprime attributes, respectively, in Codd’s original normalization papers (see Appendix C).