DATA MODELING - Fundamentals of Database Management Systems - page 26

Consider the many-to-many relationship between salespersons and products
in Figure 2.4c. As usual, salespersons are described by their salesperson number,
name, commission percentage, and year of hire. Products are described by their
product number, name, and unit price. But, what if there is a requirement to keep
track of the number of units (call it ''quantity'') of a particular product that a
particular salesperson has sold ? Can we add the quantity attribute to the product
entity box? No, because for a particular product, while there is a single product
number, product name, and unit price, there would be lots of ''quantities,'' one
for each salesperson selling the product. Can we add the quantity attribute to the
salesperson entity box? No, because for a particular salesperson, while there is a
single salesperson number, salesperson name, commission percentage, and year of
hire, there will be lots of ''quantities,'' one for each product that the salesperson
sells. It makes no sense to try to put the quantity attribute in either the salesperson
entity box or the product entity box. While each salesperson has a single salesperson
number, name, commission percentage, and year of hire, each salesperson has
many ''quantities,'' one for each product he sells. Similarly, while each product
has a single product number, product name, and unit price, each product has many
''quantities,'' one for each salesperson who sells that product. But an entity box in
an E-R diagram is designed to list the attributes that simply and directly describe
the entity, with no complications involving other entities. Putting quantity in either
the salesperson entity box or the product entity box just will not work.
The quantity attribute doesn't describe either the salesperson alone or the
product alone. It describes the combination of a particular salesperson and a
particular product. In general, we can say that it describes the combination of a
particular occurrence of one entity type and a particular occurrence of the other
entity type. Let's say that since salesperson number 137 joined the company, she has
sold 170 units of product number 24 013. The quantity 170 doesn't make sense as
a description or characteristic of salesperson number 137 alone. She has sold many
different kinds of products. To which one does the quantity 170 refer? Similarly,
the quantity 170 doesn't make sense as a description or characteristic of product
number 24 013 alone. It has been sold by many different salespersons.
In fact, the quantity 170 falls at the intersection of salesperson number 137 and
product number 24013. It describes the combination of or the association between
that particular salesperson and that particular product and it is known as intersection
data . Figure 2.5 shows the many-to-many relationship between salespersons and

SALESPERSON
PK

PRODUCT

Salesperson
Number
Salesperson
Name
Commission
Percentage
Year of Hire

PK Product

Number
Product
Name
Unit Price

Sells
Sold by

FIGURE 2.5
Many-to-many binary relationship with
intersection data

Quantity