Microsoft Office Tutorials and References
In Depth Information
StateAbbrev as a primary key (State table) and a foreign key (City table)
Figure A-4
primary key (State table)
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
composite primary key (City table)
City
foreign key
StateAbbrev
CityName
CityPopulation
CT
Hartford
124,397
CT
Madison
18,812
CT
Portland
9,543
MI
Lansing
115,518
SD
Madison
6,223
SD
Pierre
14,052
TN
Nashville
549,110
TX
Austin
690,252
TX
Portland
16,219
A nonkey field is a field that is not part of the primary key. In the two tables shown in
Figure A-4, all fields are nonkey fields except the StateAbbrev field in the State and City tables
and the CityName field in the City table. Key is an ambiguous word because it can refer to a
primary, candidate, alternate, or foreign key. When the word key appears alone, however, it
means primary key and the definition for a nonkey field consequently makes sense.
Relationships
In a database, a table can be associated with another table in one of three ways: a one-to-
many relationship, a many-to-many relationship, or a one-to-one relationship.
One-to-Many Relationship
The Department and Employee tables, shown in Figure A-5, have a one-to-many relation-
ship. A one-to-many relationship (abbreviated 1:M or 1:N ) exists between two tables
when each row in the first table (sometimes called the primary table ) matches many rows
in the second table and each row in the second table (sometimes called the related table )
matches at most one row in the first table. “Many” can mean zero rows, one row, or two
or more rows. The DeptNum field, which is a foreign key in the Employee table and the
Search JabSto ::




Custom Search