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.
• The overall order total is not stored. Instead, the computer calculates the total whenever an order is printed or displayed on the screen.In order to set up its data base, Premiere Products has made the following assumptions:
• Each representative is related to the many (or at least one) customers that he or she represents, and each customer is related to the one rep who represents the customer. Note that there is always one rep for each customer.
• A customer may place many orders or may not place any. On the other hand, an order is placed by only one customer.
• A part can be associated with many orders or may not be associated to any order. But an order has to have at least one part related to it.
• A part is supplied by one or many suppliers. A supplier supplies one or many parts.
Premiere Products engaged a database consultant to design the database tables that are necessary for the management of the company's data. The consultant recommended that the database of Premiere Products must have the following seven tables:
• CUSTOMER
• ORDERS
• SALES_REP
• PART
• ORDERLINE
• SUPPLIER
• PART_SUPPLIER
Forms, Queries, and Reports
Use the database that you createdand work on the following tasks:
Forms:
1. Create a split form for the Supplier table. Save the form as ‘Assign3_Form1.'
Queries:
1. List the customer name and credit limit as "Credit Limit Amount" of all customers who have the string 'Shop' in any part of their name. Save the query as ‘Assign3_Query1.'
2. Give the part number, description, and on-hand value (OnHand*Price) for each part in item class ‘AP'. Save the query as ‘Assign3_Query2.'
3. List the total and average balances of customers in different cities (i.e. Fillmore, Grove etc.). List the results in ascending order by City. Save the query as ‘Assign3_Query3.'
4. List the part number, description, class, and price of the parts that are supplied by a supplier from ‘Carbondale.' Save the query as ‘Assign3_Query4.'
Reports:
1. Create a report for the customers whose balance is greater than $ 3,000.00. List CustomerNum, CustomerName, Street, City, State, Zip, and balance of these customers in the report. Sort the list in the descending order of balance. Choose appropriate column headings and report styles. The title of the report should be "Customer with Balances." Save the report as ‘Assign3Report1.'
Save the form, the queries, and the report in the database and submit the updated database through Blackboard.