Microsoft Office Tutorials and References
In Depth Information
A many-to-many relationship
Figure A-6
primary key
(Position table)
Employee
Position
primary key
(Employee
table)
EmployeeNum
FirstName
LastName
DeptNum
PositionID
PositionDesc
PayGrade
1
Director
45
2173
Barbara
Hennessey
27
4519
Lee
Noordsy
31
2
Manager
40
8005
Pat
Amidon
27
3
Analyst
30
8112
Chris
Wandzell
24
4
Clerk
20
composite key of the
intersection table
Employment
EmployeeNum
PositionID
StartDate
EndDate
foreign keys related
to the Employee and
Position tables
2173
2
12/14/2008
4519
1
04/23/2010
4519
3
11/11/2004
04/22/2010
8005
3
06/05/2009
08/25/2010
8005
4
07/02/2007
06/04/2009
8112
1
12/15/2009
8112
2
10/04/2008
12/14/2009
One-to-One Relationship
In Figure A-5, recall that there’s a one-to-many relationship between the Department table
(the primary table) and the Employee table (the related table). Each department has many
employees, and each employee works in one department. The DeptNum field in the
Employee table serves as a foreign key to connect records in that table to records with
matching DeptNum field values in the Department table.
Furthermore, each department has a single employee who serves as the head of the
department, and each employee either serves as the head of a department or simply
works in a department without being the department head. Therefore, the Department and
Employee tables not only have a one-to-many relationship, but these two tables also have
a second relationship, a one-to-one relationship. A one-to-one relationship (abbreviated
1:1 ) exists between two tables when each row in each table has at most one matching
row in the other table. As shown in Figure A-7, each DeptHead field value in the
Department table represents the employee number in the Employee table of the employee
Search JabSto ::




Custom Search