Keys - Database Design for Mere Mortals - page 199

• E MP L AST N AME is ineligible because it can contain duplicate values. As you've
learned, the values of a candidate key must be unique. In this case there can be
more than one occurrence of a particular last name.
• E MP F IRST N AME and E MP L AST N AME are eligible. The combined values of both
fields will supply a unique identifier for a given record. Although multiple occur-
rences of a particular first name or last name will occur, the combination of a given
first name and last name will always be unique. (Some of you are probably saying,
“This is not necessarily always true.” You're absolutely right. Don't worry; we'll
address this issue shortly.)
• E MP Z IPCODE is ineligible because it can contain duplicate values. Many people
live in the same zip code area, so the values in E MP Z IPCODE cannot possibly be
unique.
• E MP H OME P HONE is ineligible because it can contain duplicate values and is sub-
ject to change. This field will contain duplicate values for either of these two reas-
ons.
1. One or more family members work for the organization.
2. One or more people share a residence that contains a single phone line.
You can confidently state that the EMPLOYEES table has two candidate keys: E MPLOYEE
ID and the combination of E MP F IRST N AME and E MP L AST N AME .
Mark candidate keys in your table structures by writing the letters “CK” next to the name
of each field you designate as a candidate key. A candidate key composed of two or more
fields is known as a composite candidate key, and you'll write “CCK” next to the names
of the fields that make up the key. When you have two or more composite candidate keys,
use a number within the mark to distinguish one from another. If you had two composite
candidate keys, for example, you would mark one as “CCK1” and the other as “CCK2.”
ApplythistechniquetothecandidatekeysfortheEMPLOYEEStablein Figure8.1 . Figure
8.2 shows how your structure should look when you've completed this task.