Database Reference
In-Depth Information
Tax ID
can be a candidate key for an organization.
Account Code
can be a candidate key
for an account.
Sometimes a single attribute identifies an entity instance such as an
ISBN
for a title. Some-
times it takes more than one attribute to uniquely identify an entity instance. For example,
both a
Promotion Type Code
and
Promotion Start Date
may be necessary to identify a
promotion. When more than one attribute makes up a key, we use the term
composite key
.
Therefore,
Promotion Type Code
and
Promotion Start Date
together are a composite
candidate key for a promotion. A candidate key has three main characteristics:
•
Unique
. There cannot be duplicate values in the data in a candidate key, and it can-
not be empty (also known as
nullable
). Therefore, the number of distinct values of
a candidate key must be equal to the number of distinct entity instances. When the
entity
Title
has
ISBN
as its candidate key, if there are 500 title instances, there will
also be 500 unique ISBNs.
•
Stable
. A candidate key value on an entity instance should never change. If a can-
didate key value changes, it creates data quality issues because there is no way to
determine whether a change is an update to an existing instance or a new instance.
•
Minimal
. A candidate key should contain only those attributes that are needed to
uniquely identify an entity instance. If four data elements are listed as the compos-
ite candidate key for an entity but only three are needed for uniqueness, then only
those three should be in the key.
For example, each
Student
may attend one or many
Classes
, and each
Class
may contain
one or many
Students
. Here are some sample instances for each of these entities:
Student
Student Number
First Name
Last Name
Birth Date
SM385932
Steve
Martin
1/25/1958
EM584926
Eddie
Murphy
3/15/1971
