Databases Reference
In-Depth Information
instance of that entity from every other instance of that entity. Examples you
might encounter include:
▲
Each employee is identified by his or her employee ID number.
▲
Each customer is assigned a unique customer number.
▲
Each inventory item has a unique inventory number, used to identify and
track that item.
In the United States, a common choice for the employee primary key is the
employee's social security number (SSN). Every employee is required to have one
by law, so you know that it will be present as an attribute. Also, each employee's
SSN is unique, so it uniquely identifies each instance of the entity employee.
Why doesn't everyone use the SSN as the primary key? You might want to
treat that information as sensitive, to help prevent identity theft, for example. In
that case, you need to “make up” a primary key. It's common to have the data-
base system or application generate these values for you automatically. Many
database systems let you add an attribute that is commonly known as an iden-
tifier, based on integer values and often using a one-up numbering system to
identify each entity instance.
This is another case where it's important that you understand the context in
which a term is used. The term
identifier
can refer to any value you choose as
the entity's primary key. It can also refer, as we have just seen, to an automati-
cally generated value that is used as an identifier or primary key.
Note that in the case of employees, you must uniquely identify each indi-
vidual employee. For inventory items, you would probably just want to identify
each unique type of item. For example, you would want to uniquely identify 12-
foot fiberglass ladders as a unique type of item, but if you have 100 ladders, you
wouldn't want to keep track of each one of them separately. Instead, you would
manage them as a group, identifying the group and the number of items in that
group that you have on hand.
Be careful to select a value that doesn't change as the primary key attribute
(identifier). Each item probably has a unique shelf location, for example, but
there's also a possibility that the location could change in the future. Typically,
once an identifying attribute is assigned to an item, it rarely (if ever) changes.
Relationships
That brings us to relationships. We discuss relationships in detail later in this chap-
ter, but let's look at a couple of simple examples now to introduce the concept.
Looking at employees and Figure 3-6, each employee must turn in a weekly time
card in order to get paid. This gives us a relationship between employees and time
cards, employees and pay checks, and possibly, depending on how you want to
track things in the database, between time cards and paychecks. You also have
