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

SALES relation (modified)

Salesperson Product

Numbe r Numbe r Year Quantity

137 19440 1999 132

137 19440 2000 168

137 19440 2001 173

137 24013 2000 52

137 24013 2001 118

137 26722 1999 140

137 26722 2000 203

137 26722 2001 345

186 16386 1998 250

F IGURE 5.10
Modified SALES relation of the General
Hardware Company relational database,
including a Year attribute

186 16386 1999 245

186 16386 2000 581

186 16386 2001 669

24013, and 26722, plus the associated intersection data, Figure 5.11a. Alternatively,
one could envision a single additional attribute in the SALESPERSON relation into
which all the related product number and intersection data for each salesperson
would somehow be stuffed, Figure 5.11b (although, aside from other problems,
this would violate the rule that every cell in a relation must have only a single
value). In either case, it would be unworkable. Because, in general, each salesperson
has been involved in selling different numbers of product types, each record of
the SALESPERSON relation would be a different length. Furthermore, additions,

(a) Additional Product and Quantity columns

Salesperson Salesperson Commission Year

Number Name Percentage of Hire Product Qty Product Qty Product Qty Product Qty

137 Baker 10 1995 19440 473 24013 170 26722 688

186 Adams 15 2001 16386 1745 19440 2529 21765 1962 24013 3071

204 Dickens 10 1998 21765 809 26722 734

361 Carlyle 20 2001 16386 3729 21765 3110 26722 2738

(b) One additional column for Product and Quantity Pairs

Salesperson Salesperson Commission Year

Number Name Percentage of Hire Product and Quantity Pairs

137 Baker 10 1995 (19440, 473) (24013, 170) (26722, 688)

186 Adams 15 2001 (16386, 1745) (19440, 2529) (21765, 1962) (24013, 3071)

204 Dickens 10 1998 (21765, 809) (26722, 734)

361 Carlyle 20 2001 (16386, 3729) (21765, 3110) (26722, 2738)

F IGURE 5.11
Unacceptable ways of storing a binary many-to-many relationship