Representing Binary HAS‑A Relationships

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

E
EMPLOYEE

C h a p t e r S i x Database Design Using Entity‑Relationship Models 163

(a)
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­