Several types of relationships can be defined in a database. Consider the possible relationships between employees and departments. An employee can work in only one department; this relationship is single-valued for employees. On the other hand, one department can have many employees; this relationship is multi-valued for departments. The relationship between employees (single-valued) and departments (multi-valued) is a one-to-many relationship. The following types of relationships are discussed in this section:
To define tables for each one-to-many and each many-to-one relationship:
In the following example, the "many" side of the first and second
relationships is "employees" so we define an employee table, EMPLOYEE.
Table 5. Many-to-One Relationships
| Entity | Relationship | Entity |
|---|---|---|
| Employees | are assigned to | departments |
| Employees | work at | jobs |
| Departments | report to | (administrative) departments |
In the third relationship, "departments" is on the "many" side, so we define a department table, DEPARTMENT.
The following shows how these relationships are represented in tables:
The EMPLOYEE table:
| EMPNO | WORKDEPT | JOB |
|---|---|---|
| 000010 | A00 | President |
| 000020 | B01 | Manager |
| 000120 | A00 | Clerk |
| 000130 | C01 | Analyst |
| 000030 | C01 | Manager |
| 000140 | C01 | Analyst |
| 000170 | D11 | Designer |
The DEPARTMENT table:
| DEPTNO | ADMRDEPT |
|---|---|
| C01 | A00 |
| D01 | A00 |
| D11 | D01 |
A relationship that is multi-valued in both directions is a many-to-many relationship. An employee can work on more than one project, and a project can have more than one employee. The questions "What does Dolores Quintana work on?", and "Who works on project IF1000?" both yield multiple answers. A many-to-many relationship can be expressed in a table with a column for each entity ("employees" and "projects"), as shown in the following example.
The following shows how a many-to-many relationship (an employee can work on many projects, and a project can have many employees working on it) is represented in a table:
The employee activity (EMP_ACT) table:
| EMPNO | PROJNO |
|---|---|
| 000030 | IF1000 |
| 000030 | IF2000 |
| 000130 | IF1000 |
| 000140 | IF2000 |
| 000250 | AD3112 |
One-to-one relationships are single-valued in both directions. A manager manages one department; a department has only one manager. The questions, "Who is the manager of Department C01?", and "What department does Sally Kwan manage?" both have single answers. The relationship can be assigned to either the DEPARTMENT table or the EMPLOYEE table. Because all departments have managers, but not all employees are managers, it is most logical to add the manager to the DEPARTMENT table, as shown in the following example.
The following shows how a one-to-one relationship is represented in a table:
The DEPARTMENT table:
| DEPTNO | MGRNO |
|---|---|
| A00 | 000010 |
| B01 | 000020 |
| D11 | 000060 |