Suppose that someone tells you that an attribute that is part of a composite primary key is also a candidate key. How would you respond to that statement?
This argument is incorrect if the composite PK contains no redundant attributes. If the composite primary key is properly defined, all of the attributes that compose it are required to identify the remaining attribute values. By definition, a candidate key is one that can be used to identify all of the remaining attributes, but it was not chosen to be a PK for some reason. In other words, a candidate key can serve as a primary key, but it was not chosen for that task for one reason or another. Clearly, a part of a proper (―minimal‖) composite PK cannot be used as a PK by itself.
More formally, you learned in Chapter 3, ―The Relational Database Model,‖ Section 3.2, that a candidate key can be described as a superkey without redundancies, that is, a minimal superkey. Using this distinction, note that a STUDENT table might contain the composite key
STU_NUM, STU_LNAME
This composite key is a superkey, but it is not a candidate key because STU_NUM by itself is a candidate key! The combination
STU_LNAME, STU_FNAME, STU_INIT, STU_PHONE
might also be a candidate key, as long as you discount the possibility that two students share the same last name, first name, initial, and phone number.
If the student’s Social Security number had been included as one of the attributes in the STUDENT table—perhaps named STU_SOCSECNUM—both it and STU_NUM would have been candidate keys because either one would uniquely identify each student. In that case, the selection of STU_NUM as the primary key would be driven by the designer’s choice or by end-user requirements. Note, incidentally, that a primary key is a superkey as well as a candidate key.
