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

deletions, and updates of product/quantity pairs would be a nightmare. Also, trying
to access the related data from the ''product side,'' for example looking for all
of the salespersons who have sold a particular product, would be very difficult.
And, incidentally, trying to make this work by putting the salesperson data into the
PRODUCT relation, instead of putting the product data into the SALESPERSON
relation as in Figure 5.11, would generate an identical set of problems. No,
the only way that's workable is to create an additional relation to represent the
many-to-many relationship. Each combination of a related salesperson and product
has its own record, making the insertion, deletion, and update of related items
feasible, providing a clear location for intersection data, and avoiding the issue of
variable-length records.
Finally, there is the question of whether an additional relation is required to
represent a many-to-many relationship if there is no intersection data. For example,
suppose that General Hardware wants to track which salespersons have sold which
products, but has no interest in how many units of each product they have sold.
The SALES relation of Figure 5.7e would then have only the Salesperson Number
and Product Number attributes, Figure 5.12. Could this information be stored in
some way other than with the additional SALES relation? The answer is that the
additional relation is still required. Note that in the explanation above of why an
additional relation is necessary in general to represent a many-to-many relationship,
the intersection data played only a small role. The issues would still be there, even
without intersection data.

One-to-One Binary Relationship After considering one-to-many and many-to-many
binary relationships in relational databases, the remaining binary relationship is the
one-to-one relationship. Each of General Hardware's salespersons has exactly one
office and each office is occupied by exactly one salesperson, Figure 5.13.

Salesperson Office

SALES relation
(without intersection data)

Salesperson Product

Numbe r Numbe r

137 19440

137 24013

137 26722

186 16386

186 19440

186 21765

186 24013

204 21765

204 26722

361 16386

F IGURE 5.12
The many-to-many SALES relation without
intersection data

361 21765

361 26722