Database Reference
In-Depth Information
We have only one candidate key in the
Class
entity, so
Class Full Name
becomes our
primary key. We have to make a choice in
Student
, however, because we have two candid-
ate keys. Which
Student
candidate key would you choose as the primary key?
In selecting one candidate key over another as the primary key, consider succinctness and
security. Succinctness means if there are several candidate keys, choose the one with the
fewest attributes or shortest in length. In terms of privacy, it is possible that one or more at-
tributes within a candidate key will contain sensitive data whose viewing should be restric-
ted. We want to avoid having sensitive data in our entity's primary key because the primary
key can propagate as a foreign key and therefore spread this sensitive data throughout our
database. Foreign keys will be discussed shortly.
Considering succinctness and security in our example, I would choose
Student Number
over the composite
Student First Name
,
Student Last Name
, and
Student Birth Date
. It
is more succinct and contains less sensitive data. Here is our data model with primary and
alternate keys:
Primary key attributes are shown above the line in the rectangles. You will notice two num-
bers following the key abbreviation “AK.” The first number is the grouping number for
an alternate key, and the second number is the ordering of the attribute within the altern-
ate key. So there are three attributes required for the
Student
alternate key:
Student First
Name
,
Student Last Name
, and
Student Birth Date
. This is also the order in which the
alternate key index will be created because
Student First Name
has a “1” after the colon,
Student Last Name
a “2,” and
Student Birth Date
a “3.”
Attendance
now has as its primary key
Student Number
and
Class Full Name
, which ap-
pear to make a valid primary key. Note that the two primary key attributes of
Attendance
are followed by “FK”. These are foreign keys, to be discussed shortly.
So to summarize, a candidate key consists of one or more attributes that uniquely identify
an entity instance. The candidate key that is determined to be the best way to identify each
record in the entity becomes the primary key. The other candidate keys become alternate
keys. Keys containing more than one attribute are known as composite keys.
At the physical level, a candidate key is often translated into a unique index.
