DATA MODELING - Fundamentals of Database Management Systems - page 27

products with the intersection data, quantity, represented in a separate box attached
to the relationship line. That is the natural place to draw it. Pictorially, it looks as
if it is at the intersection between the two entities, but there is more to it than that.
The intersection data describes the relationship between the two entities . We know
that an occurrence of the Sells relationship specifies that salesperson 137 has sold
some of product 24013. The quantity 170 is an attribute of this occurrence of that
relationship, further describing this occurrence of the relationship. Not only do we
know that salesperson 137 sold some of product 24013 but we know how many
units of that product that salesperson sold.

Associative Entity Since we know that entities can have attributes and now we
see that many-to-many relationships can have attributes, too, does that mean that
entities and many-to-many relationships can in some sense be treated in the same
way within E-R diagrams? Indeed they can! Figure 2.6 shows the many-to-many
relationship Sells converted into the associative entity SALES. An occurrence of
the SALES associative entity does exactly what the many-to-many relationship did:
it indicates a relationship between a salesperson and a product, specifically the
fact that a particular salesperson has been involved in selling a particular product,
and includes any intersection data that describes this relationship. Note very, very
carefully the reversal of the cardinalities and modalities when the many-to-many
relationship is converted to an associative entity. SALES is now a kind of entity in
its own right. Again, a single occurrence of the new SALES entity type records the
fact that a particular salesperson has been involved in selling a particular product.
A single occurrence of SALES relates to a single occurrence of SALESPERSON
and to a single occurrence of PRODUCT, which is why the diagram indicates that
a sales occurrence involves exactly one salesperson and exactly one product. On
the other hand, since a salesperson sells many products, the diagram shows that a
salesperson will tie into many sales occurrences. Similarly, since a product is sold
by many salespersons, the diagram shows that a product will tie into many sales
occurrences.
If the many-to-many relationship E-R diagram style of Figure 2.5 is equivalent
to the associative entity style of Figure 2.6, which one should you use? This is
an instance in which this type of diagramming is an art with a lot of leeway for
personal taste. However, you should be aware that over time the preference has
shifted towards the associative entity style of Figure 2.6, and that is what we will
use from here on in this topic.

SALESPERSON
PK

SALES PRODUCT

Salesperson
Number
Salesperson
Name
Commission
Percentage
Year of Hire

PK Salesperson

Number

PK Product

Number
Product
Name
Unit Price

Sold
Sold by

Sold
Sold
Product

PK Product

Number

Quantity

FIGURE2.6
Associative entity with intersection data