THE RELATIONAL DATABASE MODEL: INTRODUCTION - Fundamentals of Database Management Systems - page 121

Salesperson 137 Office 1253

Salesperson 186 Office 1227

Salesperson 204 Office 1284

F IGURE 5.13
A one-to-one binary relationship

Salesperson 361

Office 1209

Figure 5.14f shows the addition of the OFFICE relation to the General
Hardware relational database. The SALESPERSON relation has the Office Number
attribute as a foreign key so that the company can look up the record for a salesperson
and see to which office she is assigned. Because this is a one-to-one relationship and
each salesperson has only one office, the company can also scan down the Office
Number column of the SALESPERSON relation, find a particular office number
(which can only appear once, since it's a one-to-one relationship), and see which
salesperson is assigned to that office. In general, this is the way that one-to-one
binary relationships are built into relational databases. The unique identifier, the
primary key, of one of the two entities in the one-to-one relationship is inserted into
the other entity's relation as a foreign key. The question of which of the two entities
is chosen as the ''donor'' of its primary key and which as the ''recipient'' will be
discussed further when we talk about logical design in Chapter 7.
But there is another interesting question about this arrangement. Could the
SALESPERSON and OFFICE relations of Figure 5.14 be combined into one
relation? After all, a salesperson has only one office and an office has only
one salesperson assigned to it. So, if an office and its unique identifier, Office
Number, ''belongs'' to one particular salesperson, so does that office's Telephone