Indicate the changes (using the shorthand representation) that you would need to
ID: 674861 • Letter: I
Question
Indicate the changes (using the shorthand representation) that you would need to make to the original Premiere Products database design (see Figure 2-1) to support the following requirements. A customer is not necessarily represented by a single sales rep, but can be represented by several sales reps. When a customer places an order, the sales rep who gets the commission on the order must be in the collection of sales reps who represent the customer.
REP REP LAST FIRST STREET CITY STATE ZIP COMMISSION RATE 33321 $20,542.50 0.05 33553 $39.216.00 0.07 65 PerezJua 1626 TaylorFillmore FL 33336$23,47.00 0.05 NUM NAME NAME 20 Kaiser Valerie 624 Randall Crove F1 35 Hull Richard 532 Jackson Sheldon F1 CUSTOMER CUSTOMER CUSTOMER STREET CITY STATE ZIP BALANCE CREDIT REP LIMIT NUM | 33336 | $6,550.00 | $7,500.00 333215431.50 510,000.00 35 | 33146| 148 Al's Appliance 2837 Fillmore | FL and SportCreenway Brookings3827 Devon Crove Fergusons | 382 Wildwood |Northfield | FL 282 Grove FL FL 3 356 $5,785.00| $7.500. 408 The 1828 Raven Crystal FL 33503 55,285.25 $5,000.00 35 462 3829 Central Crove FL33321 $3,412.00 10,000.00 65 Klines 838 Ridgeland Fillmore FL 33336 $12,762.00 15,000.00 20 ohnson's372 Oxford Sheldon FL 33553 $2,106.00 $10,000.00 65 524 608 Store Lee's Spart 282 Evergreen Altonville FL 32543 $2,851.00 $5,000.00 35 and Appliance Deerfield's 282 Columbia Sheldon FL 33553 $248.00 $7,500.00 35 687 725 Four Seasons 842 All Season 28 1akevlewGrove FL33321 20 FIGURE 1-2 Sample data for Premiere ProductsExplanation / Answer
Introduce a new table called RepCust as follows:
RepCustID
Customer_Num
Rep_Num
rc1
247
22
rc2
156
34
rc3
234
21
The Orders table has to include an extra column called Rep_Num
Order_Num
Rep_Num
Order_Date
Customer_Num
20345
22
01/01/2015
247
21367
34
05/06/2014
156
22379
21
03/04/2015
234
This will cater for customers being served by more than one sales Reps
RepCustID
Customer_Num
Rep_Num
rc1
247
22
rc2
156
34
rc3
234
21