There are two types of relationships in the E‑R model: HAS‑A relationships among
entities of different logical types and IS‑A relationships among entities that are sub
types of a common logical type. In this section we consider HAS‑A relationships;
later we discuss IS‑A relationships.
REPRESENTING ONE‑TO‑ONE RELATIONSHIPS
The simplest form of binary relationship is a one‑to‑one (1:1) relationship, in which
an entity of one type is related to no more than one entity of another type. In the
example of EMPLOYEE and AUTO, suppose that an employee is assigned exactly
one automobile and an auto is assigned to exactly one employee. An E‑R diagram
for this relationship is shown in Figure 6‑5.
Representing a 1:1 relationship with the relational model is straightforward. First
each entity is represented with a relation, and then the key of one of the relations is
placed in the other. In Figure 6‑6(a), the key of EMPLOYEE is stored in AUTO,
and in Figure 6‑6(b), the key of AUTO is stored in EMPLOYEE.
When the key of one relation is stored in a second relation, it is called a foreign
key. In Figure 6‑6(a), EmployeeNumber is a foreign key in AUTO, and in Figure
6‑6(b), LicenseNumber is a foreign key in EMPLOYEE. In this figure, foreign
keys are shown in italics, but sometimes you may see foreign keys depicted by a
dashed underline. In still other cases, foreign keys are not denoted in any special
way. In this text, when there is a danger of confusion, we show foreign keys in ital-
ics, but most of the time, they do not receive any special notation.
For a 1:1 relationship, the key of either table can be placed as a foreign key in
the other table. In Figure 6‑6(a) the foreign key EmployeeNumber is placed in
AUTO. With this design, we can navigate from EMPLOYEE to AUTO or from
AUTO to EMPLOYEE. In the first case, we have an employee and want the auto
assigned to that employee. To get the employee data, we use EmployeeNumber to
obtain the employee's row in EMPLOYEE. From this row, we obtain the
LicenseNumber of the auto assigned to that employee. We then use this number to
look up the auto data in AUTO.
Now consider the other direction. Assume that we have an auto and want the
employee assigned to that auto. Using the design in Figure 6‑6(a), we access the
C h a p t e r S i x Database Design Using Entity‑Relationship Models 163
EMPLOYEE (EmployeeNumber, EmployeeName, Phone .... LicenseNumber)
about the employee who has been assigned that auto appears in that row.
We take similar actions to travel in either direction for the alternative design, in
which the foreign key of LicenseNumber is placed in EMPLOYEE. Using this
design, to go from EMPLOYEE to AUTO, we go directly to the AUTO relation and
look up the row in AUTO that has the given employee's number as its value of
EmployeeNumber. To travel from AUTO to EMPLOYEE, we look up the row in
AUTO having a given LicenseNumber. From this row, we extract the Employ
eeNumber and use it to access the employee data in EMPLOYEE. Here we are
using the term look up to mean "find a row given a value of one of its columns."
Later, when we discuss particular DBMS models, we demonstrate how this is done.
Although the two designs in Figure 6‑6 are equivalent in concept, they may be
different in performance. For instance, if a query in one direction is more common
than a query in the other, we may prefer one design to the other. Also, if the DBMS
product is much faster in lookups on primary keys versus lookups on foreign keys,
we might also prefer one design to another.
Figure 6‑7 shows another 1:1 relationship, in which each EMPLOYEE has a
JOB‑EVALUATION and each JOB‑EVALUATION corresponds to a particular
employee. Observe from the hash marks that the relationship is mandatory in both
directions. When the relationship is 1:1 and is mandatory in both directions, it is
likely that the records are describing different aspects of the same entity, especially
if, as is the case in Figure 6‑7, both entities have the same key. When this occurs, the
records should generally be combined into one relation. Learn to regard such 1:1
mandatory relationships with suspicion.
The separation of an entity into two relations can sometimes be justified. One
justification concerns performance. For example, suppose that the JOB‑EVALUA
TION data is lengthy and is used far less frequently than is the other employee data.
In these circumstances it may be appropriate to store JOB‑EVALUATIONs in a
separate table so that the more common requests for nonevaluation employee data
can be processed faster.
FIG U RE 6‑7 EMPLOYEE 1:1 JOB-EVALUATION