Premiere products database

Premiere Products – Data Management

Premiere Products is a distributor of appliances, house wares, and sporting goods. Since its inception, the company has used spreadsheet software to maintain customer, order, inventory, and sales representative data. Redundancy and difficulty in accessing related data are the major problems that employees at Premiere Products encounter with the spreadsheets. The company’s business activities have grown significantly in the recent years and it is no longer feasible to use spreadsheets to maintain its data.

The management of Premiere Products has determined that Premiere Products must maintain, in a new database, the following information about its sales representatives, customers, parts inventory, and suppliers:

· Premiere Products must store the sales rep number that uniquely identifies a representative. In addition, it has to store the sales rep last name, first name, address, total commission, and commission rate.

· The company must store the customer number, which uniquely identifies a customer, customer name, address, current balance, and credit limit for each customer, as well as the number of sales representative who represents the customer.

· The company must store the part number, description, number of units on hand, item type, number of the warehouse where the item is stored, and unit price for each part in inventory.

· Premiere Products must store information about the suppliers who supply parts. The supplier is uniquely identified by supplier number. In addition, a supplier has a name, address, city, contact name and telephone number.

Premiere Products must also store information about orders. The orders have three components: The heading (top) of the order contains the order number and the date; the customer’s number, name, and address; and the sales rep 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. Finally, 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, they 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 number of the sales rep who represents the customer are stored with the customer information. The name of the sales rep is stored with the sales rep information.

· For each order line, the database 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 information 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.