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.
A one-to-one relationship
foreign key for
primary key for
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).