Database Reference
In-Depth Information
Figure 8.10. Testing candidate keys in the CUSTOMERS table
Always remember that a field must comply with
all
of the Elements of a Candidate Key in
order to qualify as a candidate key. You must immediately disqualify the field if it does not
fulfill this requirement.
As you examine the table, you draw these conclusions.
•
S
TATUS
is ineligible because it will probably contain duplicate values.
As business
grows, Mike is going to have many “Valued” customers.
•
C
UST
H
OME
P
HONE
is ineligible because it will probably contain duplicate values.
The sample data reveals that two customers can live in the same residence and
have the same phone number.
•
C
UST
F
IRST
N
AME
and C
UST
L
AST
N
AME
are ineligible because they will probably
contain duplicate values.
The sample data reveals that the combination of first
name and last name can represent more than one distinct customer.
These findings convince you to establish an artificial candidate key for this table. You then
createafieldcalledC
USTOMER
ID,confirmthatitcomplieswiththerequirementsforacan-
didate key, and add the new field to the table structure with the appropriate designation.
Figure 8.11
shows the revised structure of the CUSTOMERS table.