Microsoft Office Tutorials and References
In Depth Information
Knowing the characteristics of a table leads directly to a definition of a relational data-
base. A relational database is a collection of tables (relations).
Note that this topic uses singular table names, such as Employee and Department, but
some people use plural table names, such as Employees and Departments. You can use
either singular table names or plural table names, as long as you consistently use the style
you choose.
Keys
Primary keys ensure that each row in a table is unique. A primary key is a column, or a
collection of columns, whose values uniquely identify each row in a table. In addition to
being unique , a primary key must be minimal (that is, contain no unnecessary extra
columns) and must not change in value. For example, in Figure A-2 the State table con-
tains one record per state and uses the StateAbbrev column as its primary key.
A table and its keys
Figure A-2
primary key
alternate keys
State
StateAbbrev
StateName
EnteredUnionOrder
StateBird
StatePopulation
CT
Connecticut
5
American robin
3,510,297
MI
Michigan
26
robin
10,120,860
SD
South Dakota
40
pheasant
775,933
TN
Tennessee
16
mockingbird
5,962,959
TX
Texas
28
mockingbird
22,859,968
Could any other column, or collection of columns, be the primary key of the State table?
• Could the StateBird column serve as the primary key? No, because the StateBird col-
umn does not have unique values (for example, the mockingbird is the state bird of
more than one state).
• Could the StatePopulation column serve as the primary key? No, because the
StatePopulation column values change periodically and are not guaranteed to be
unique.
• Could the StateAbbrev and StateName columns together serve as the primary key? No,
because the combination of these two columns is not minimal. Something less, such as
the StateAbbrev column by itself, can serve as the primary key.
• Could the StateName column serve as the primary key? Yes, because the StateName
column has unique values. In a similar way, you could select the EnteredUnionOrder
column as the primary key for the State table. One column, or a collection of columns,
that can serve as a primary key is called a candidate key . The candidate keys for the
State table are the StateAbbrev column, the StateName column, and the
EnteredUnionOrder column. You choose one of the candidate keys to be the primary
key, and each remaining candidate key is called an alternate key . The StateAbbrev col-
umn is the State table’s primary key in Figure A-2, so the StateName and
EnteredUnionOrder columns become alternate keys in the table.
Figure A-3 shows a City table containing the fields StateAbbrev, CityName, and
CityPopulation.
Search JabSto ::




Custom Search