Database Reference
In-Depth Information
Figure 8.3. Can you identify any candidate keys in the PARTS table?
Atfirstglance,youmaybelievethatP
ART
N
AME
,M
ODEL
N
UMBER
,thecombinationofP
ART
N
AME
andM
ODEL
N
UMBER
,andthecombinationofM
ANUFACTURER
N
AME
andP
ART
N
AME
are potential candidate keys. After investigating this theory, however, you come up with
the following results.
•
PART
N
AME
is ineligible because it can contain duplicate values.
A given part name
will be duplicated when the part is manufactured in several models. For example,
this is the case with Faust Brake Levers.
•
M
ODEL
N
UMBER
is ineligible because it can contain null values.
A candidate key
value must exist for each record in the table. As you can see, some parts do not
have a model number.
•
P
ART
N
AME
and M
ODEL
N
UMBER
are ineligible because either field can contain null
values.
The simple fact that M
ODEL
N
UMBER
can contain null values instantly dis-
qualifies this combination of fields.
•
M
ANUFACTURER
N
AME
and P
ART
N
AME
are ineligible because the values for these
fields seem to be optional.
Recall that a candidate key value cannot be optional in
whole or in part. In this instance, you can infer that entering the manufacturer
name is optional when it appears as a component of the part name; therefore, you
cannot designate this combination of fields as a candidate key.
It's evident that you don't have a single field or set of fields that qualifies as a candidate
key for the PARTS table. This is a problem because each table must have at least
one
can-
didate key. Fortunately, there is a solution.
