Database Reference
In-Depth Information
In the Rep table, you see that there are three reps whose numbers are 20, 35, and 65. The name of sales
rep 20 is Valerie Kaiser. Her street address is 624 Randall. She lives in Grove, FL, and her zip code is 33321.
Her total commission is $20,542.50, and her commission rate is 5% (0.05).
Premiere Products has 10 customers, which are identified with the numbers 148, 282, 356, 408, 462,
524, 608, 687, 725, and 842. The name of customer number 148 is Al
7
'
s Appliance and Sport. This customer
'
s
address is 2837 Greenway in Fillmore, FL, with a zip code of 33336. The customer
s current balance is
$6,550.00, and its credit limit is $7,500.00. The number 20 in the RepNum column indicates that Al
'
'
s Appli-
ance and Sport is represented by sales rep 20 (Valerie Kaiser).
Skipping to the table named Part, you see that there are 10 parts, whose part numbers are AT94, BV06,
CD52, DL71, DR93, DW11, FD21, KL62, KT03, and KV29. Part AT94 is an iron, and Premiere Products has
50 units of this part on hand. Irons are in item class HW (housewares) and are stored in warehouse 3. The
price of an iron is $24.95. Other item classes are AP (appliances) and SG (sporting goods).
Moving back to the table named Orders, you see that there are seven orders, which are identified with
the numbers 21608, 21610, 21613, 21614, 21617, 21619, and 21623. Order number 21608 was placed on
October 20, 2013, by customer 148 (Al
'
s Appliance and Sport).
NOTE
In some database systems, the word “Order” has a special purpose. Having a table named Order could cause problems in
such systems. For this reason, Premiere Products uses the table name Orders instead of Order.
The table named OrderLine might seem strange at first glance. Why do you need a separate table for the
order lines? Couldn
t the order lines be included in the Orders table? The answer is yes. The Orders table
could be structured as shown in Figure 1-6. Notice that this table contains the same orders as shown in
Figure 1-5, with the same dates and customers. In addition, each table row in Figure 1-6 contains all the
order lines for a given order. Examining the fifth row, for example, you see that order 21617 has two order
lines. One of the order lines is for two BV06 parts at $794.95 each, and the other order line is for four CD52
parts at $150.00 each.
'
Orders
OrderNum
OrderDate
CustomerNum
PartNum
NumOrdered
QuotedPrice
21608
10/20/2013
148
AT94
11
$21.95
21610
10/20/2013
356
DR93
1
$495.00
DW11
1
$399.99
21613
10/21/2013
408
KL62
4
$329.95
21614
10/21/2013
282
KT03
2
$595.00
21617
10/23/2013
608
BV06
2
$794.95
CD52
4
$150.00
21619
10/23/2013
148
DR93
1
$495.00
21623
10/23/2013
608
KV29
2
$1,290.00
FIGURE 1-6
Alternative Orders table structure
Q&A
Question: How is the information in Figure 1-5 represented in Figure 1-6?
Answer: Examine the OrderLine table shown in Figure 1-5 and note the sixth and seventh rows. The sixth
row indicates that there is an order line in order 21617 for two BV06 parts at $794.95 each. The seventh row
indicates that there is an order line in order 21617 for four CD52 parts at $150.00 each. Thus, the informa-
tion in Figure 1-6 is represented in Figure 1-5 in two separate rows rather than in one row.
