For an Iddependent Weak Entity, It Is Necessary to Add the Key of the Parent

162P a r t I I ID a t a b a s e and Database Application D e s i g n

For an IDdependent weak entity, it is necessary to add the key of the parent

entity to the weak entity's relation, and this added attribute becomes part of the

weak entity's key. Thus in Figure 64(c), we have added InvoiceNumber, the key of

INVOICE, to the attributes in LINEITEM. The key of LINEITEM is the compos-

ite (InvoiceNumber, LineNumber). With relations that represent IDdependent

weak entities, the key always is composite. Now consider the representation of ER

model relationships.

Representing Binary HASA Relationships

There are two types of relationships in the ER model: HASA relationships among

entities of different logical types and ISA relationships among entities that are sub

types of a common logical type. In this section we consider HASA relationships;

later we discuss ISA relationships.

REPRESENTING ONETOONE RELATIONSHIPS

The simplest form of binary relationship is a onetoone (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 ER diagram

for this relationship is shown in Figure 65.

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 66(a), the key of EMPLOYEE is stored in AUTO,

and in Figure 66(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 66(a), EmployeeNumber is a foreign key in AUTO, and in Figure

66(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 66(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 66(a), we access the

FIGURE 65

Example of a 1:1 Relationship

C h a p t e r S i x Database Design Using EntityRelationship Models 163

FIGURE 66

Alternatives for Representing 1: 1 Relationships: (a) Placing the Key of

EMPIA)YEE in AUTO and (b) Placing the Key of AUTO in EM[PLOYEE

EMPLOYEE (EmployeeNumber, EmployeeName, Phone .... )

AUTO (LicenseNumber, SerialNumber, Color, Make, Model .... EmployeeNumber)

(a)

EMPLOYEE (EmployeeNumber, EmployeeName, Phone .... LicenseNumber)

AUTO (LicenseNumber, SerialNumber, Color, Make, Model ....)

(b)

EMPLOYEE table and look up the row that has the given license number. The data

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 66 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 67 shows another 1:1 relationship, in which each EMPLOYEE has a

JOBEVALUATION and each JOBEVALUATION 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 67, 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 JOBEVALUA

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 JOBEVALUATIONs in a

separate table so that the more common requests for nonevaluation employee data

can be processed faster.

FIG U RE 67EMPLOYEE 1:1 JOB-EVALUATION

Suspicious 1:1 Relationship