Microsoft Office Tutorials and References
In Depth Information
primary key in the Department table, is the common field that ties together the rows of
the two tables. Each department has many employees; and each employee works in
exactly one department or hasn’t been assigned to a department, if the DeptNum field
value for that employee is null.
Figure A-5
A one-to-many relationship
Department
primary key for
the one-to-many
relationship
DeptNum
DeptName
DeptHead
24
Finance
27
27
Marketing
31
31
Technology
27
foreign key for
the one-to-many
relationship
Employee
primary key
EmployeeNum
FirstName
LastName
DeptNum
2173
Barbara
Hennessey
27
4519
Lee
Noordsy
31
8005
Pat
Amidon
27
8112
Chris
Wandzell
24
Many-to-Many Relationship
In Figure A-6, the Employee table (with the EmployeeNum field as its primary key) and the
Position table (with the PositionID field as its primary key) have a many-to-many relation-
ship. A many-to-many relationship (abbreviated as M:N ) exists between two tables when
each row in the first table matches many rows in the second table and each row in the sec-
ond table matches many rows in the first table. In a relational database, you must use a third
table (often called an intersection table , junction table , or link table ) to serve as a bridge
between the two many-to-many tables; the third table has the primary keys of the two many-
to-many tables as its primary key. The original tables now each have a one-to-many relation-
ship with the new table. The EmployeeNum and PositionID fields represent the primary key
of the Employment table that is shown in Figure A-6. The EmployeeNum field, which is a
foreign key in the Employment table and the primary key in the Employee table, is the com-
mon field that ties together the rows of the Employee and Employment tables. Likewise, the
PositionID field is the common field for the Position and Employment tables. Each employee
has served in many different positions within the company over time, and each position in
the company has been filled by many different employees over time.
Search JabSto ::




Custom Search