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

So, the database will somehow have to keep track of this many-to-many
relationship between salespersons and products. The way that a many-to-many
relationship is represented in a relational database is by the creation of an additional
relation, in this example, the SALES relation in Figure 5.7e. The SALES relation
of Figure 5.7e is intended to record the lifetime sales of a particular product
by a particular salesperson. Thus, there will be a single row in the relation for
each applicable combination of salesperson and product (i.e., when a particular
salesperson has actually sold some of the particular product). For example, the first
row of the SALES relation indicates that salesperson 137 has sold product 19440.

(a) SALESPERSON relation

Salesperson Salesperson Commission Year

Number Name Percentage of Hire

137 Baker 10 1995

186 Adams 15 2001

204 Dickens 10 1998

361 Carlyle 20 2001

(b) CUSTOMER relation

Customer Customer Salesperson

Number Name Numbe r HQ City

0121 Main St. Hardware 137 New York

0839 Jane's Stores 186 Chicago

0933 ABC Home Stores 137 Los Angeles

1047 Acme Hardware Store 137 Los Angeles

1525 Fred's Tool Stores 361 Atlanta

1700 XYZ Stores 361 Washington

1826 City Hardware 137 New York

2198 Western Hardware 204 New York

2267 Central Stores 186 New York

(c) CUSTOMER EMPLOYEE relation

Custome r Employee Employee

Number Number Name Title

0121 27498 Smith Co-Owner

0121 30441 Garcia Co-Owner

0933 25270 Chen VP Sales

0933 30441 Levy Sales Manager

0933 48285 Morton President

1525 33779 Baker Sales Manager

2198 27470 Smith President

2198 30441 Jones VP Sales

FIGURE5.7
General Hardware Company relational
database including the PRODUCT and
SALES relation

2198 33779 Garcia VP Personnel

2198 35268 Kaplan Senior Accountant

(Continues)