Microsoft Office Tutorials and References
In Depth Information
who heads the department. In other words, each DeptHead field value in the Department
table matches exactly one EmployeeNum field value in the Employee table. At the same
time, each EmployeeNum field value in the Employee table matches at most one
DeptHead field value in the Department table—matching one DeptHead field value if the
employee is a department head, or matching zero DeptHead field values if the employee
is not a department head. For this one-to-one relationship, the EmployeeNum field in the
Employee table and the DeptHead field in the Department table are the fields that link the
two tables, with the DeptHead field serving as a foreign key in the Department table and
the EmployeeNum field serving as a primary key in the Employee table.
Some database designers might use EmployeeNum instead of DeptHead as the field
name for the foreign key in the Department table, because they both represent the
employee number for the employees of the company. However, DeptHead better identi-
fies the purpose of the field and would more commonly be used as the field name.
Figure A-7
A one-to-one relationship
Department
primary key
(Department
table)
foreign key for
the one-to-one
relationship
DeptNum
DeptName
DeptHead
24
Finance
8112
27
Marketing
2173
31
Technology
4519
Employee
primary key for
the one-to-one
relationship
EmployeeNum
FirstName
LastName
DeptNum
2173
Barbara
Hennessey
27
4519
Lee
Noordsy
31
8005
Pat
Amidon
27
8112
Chris
Wandzell
24
Entity Subtype
Suppose the company awards annual bonuses to a small number of employees who fill
director positions in selected departments. As shown in Figure A-8, you could store the
Bonus field in the Employee table, because a bonus is an attribute associated with
employees. The Bonus field would contain either the amount of the employee’s bonus
(record 4 in the Employee table) or a null value for employees without bonuses (records 1
through 3 in the Employee table).
 
Search JabSto ::




Custom Search