Databases Reference
In-Depth Information
rate, spreadsheets have inherent size limitations that will eventually force the company to split its order data
into multiple spreadsheets. Splitting the spreadsheets would create further redundancy.
Having decided to replace its spreadsheet software, management has determined that Premiere Products
must maintain the following information about its sales reps, customers, and parts inventory:
●
The sales rep number, last name, first name, address, total commission, and commission rate for
each sales rep.
●
The customer number, name, address, current balance, and credit limit for each customer, as well
as the number of the sales rep who represents the customer.
●
The part number, description, number of units on hand, item class, number of the warehouse
where the item is stored, and unit price for each part in inventory.
Premiere Products must also store information about orders. Figure 1-2 shows a sample order.
3
PREMIERE
PRODUCTS
ORDER:
21617
DATE:
10/23/2010
Heading
CUSTOMER:
608
Johnson's Department Store
372 Oxford
Sheldon FL 33553
SALES REP:
65
Juan Perez
PA RT
NUMBER
PA RT
DESCRIPTION
NUMBER
ORDERED
PRICE
TOTAL
BV06
Home Gym
2
4
794.95
150.00
1589.90
600.00
Order Lines
CD52
Microwave Oven
Body
Extensions
Footing
2189.90
ORDER TOTAL >>
FIGURE 1-2
Sample order
The sample order has three components:
●
The heading (top) of the order contains the order number and date; the customer's number, name,
and address; and the sales rep's number and name.
●
The body of the order contains one or more order lines, sometimes called line items. Each order
line contains a part number, a part description, the number of units of the part ordered, and the
quoted price for the part. Each order line also contains a total, usually called an extension, which
is the result of multiplying the number ordered by the quoted price.
●
The footing (bottom) of the order contains the order total.
Premiere Products must also store the following items for each customer's order:
●
For each order, the company must store the order number, the date the order was placed, and the
number of the customer that placed the order. The customer's name and address and the num-
ber of the sales rep who represents the customer are stored with customer information. The name
of the sales rep is stored with the sales rep information.
●
For each order line, the company must store the order number, the part number, the number of
units ordered, and the quoted price. Remember that the part description is stored with the infor-
mation about parts. The result of multiplying the number of units ordered by the quoted price is
not stored because the computer can calculate it when necessary.
●
The overall order total is not stored. Instead, the computer calculates the total whenever an order
is printed or displayed on the screen.
The problem facing Premiere Products is common to many businesses and individuals that need to store
and retrieve data in an efficient and organized way. Furthermore, most organizations are interested in more
