Databases Reference
In-Depth Information
Product 19440
Hammer
Salesperson 137
Mr. Baker
Product 24013
Saw
Product 26722
Pliers
FIGURE5.8
Many-to-many relationship between
salespersons and products as shown in
the SALES relation
Salesperson 186
Ms. Adams
wants to record
how many
of each particular product each salesperson has sold
since the product was introduced or since the salesperson joined the company. So,
it sounds like there has to be a ''Quantity'' attribute. And, an attribute describes
an entity, right? Then, which entity does the Quantity attribute describe? Does
it describe salespersons the way the Year of Hire does in the SALESPERSON
relation? Does it describe products the way Unit Price does in the PRODUCT
relation? Each salesperson has exactly one date of hire. Each product has exactly
one unit price. But a salesperson doesn't have just one ''quantity'' associated with
her because she sells many products and similarly, a product doesn't have just one
''quantity'' associated with it because it is sold by many salespersons.
While year of hire is clearly a characteristic of salespersons and unit price is
clearly a characteristic of products, ''quantity'' is a characteristic of
the relationship
between salesperson and product
. For example, the fact that salesperson 137 appears
in the first row of the SALES relation of Figure 5.7e along with product 19440
indicates that he has a history of selling this product. But do we know more about
his history of selling it? Yes! That first row of Figure 5.7e indicates that salesperson
137 has sold 473 units of product 19440. Quantity
describes the many-to-many
relationship
between salespersons and products. In a sense it falls at the intersection
between the two entities and is thus called ''intersection data,'' Figure 5.9.
Since the many-to-many relationship has its own relation in the database and
since it can have attributes, does that mean that we should think of it as a kind of
entity? Yes! Many people do just that and refer to it as an ''associative entity,'' a
concept we first described when discussing data modeling in Chapter 2!
Additional Many-to-Many Concepts
Before leaving the subject of many-to-many relation-
ships, there are a few more important points to make. First, will the combination
of the two primary keys representing the two entities in the many-to-many
relationship always serve as a unique identifier or primary key in the additional
relation representing the many-to-many relationship? The answer is that this
depends on the precise nature of the many-to-many relationship. For example,
Search WWH ::
Custom Search