Assignment
EduToys, Inc., sells a wide variety of educational toy products to its customers through its website. Jeannie Dobson, director of information services at EduToys, recently developed a relational database to store critical information that the management team needs to more effectively serve EduToys' customers. The database, which is provided in the file C18_01.mdb, consists of five related tables: Company, Customer, Inventory, Orders, and Toys.
The Company table consists of the following information on each of the 159 companies that manufacture and supply products to EduToys: identification number, name, and telephone number. The Customer table maintains the following data on each of the 307 customers who purchased at least one item from EduToys's online store during the first 10 months of operation (January-October 1998): identification number, last name, first name, age, gender, street address, city, state, zip code, and telephone number. The Inventory table consists of the following information on each of the 201 products that EduToys purchases from its various suppliers: identification number, name, quantity in current inventory, quantity on order, and expected delivery date of order. The Orders table records the following information for each of the customer transactions that took place during the first 10 months of 1998: transaction identification number, date, customer identification number, customer credit card number, product identification number, and quantity purchased. Finally, the Toys table maintains the following data on each of the products sold by EduToys: product identification number, company (i.e., supplier) identification number, product name, type of product, appropriate age group for product, unit price, and detailed product description.
As part of your internship with EduToys, you have been asked by your supervisor to make a report that responds to the following questions. Your supervisor encourages you to make extensive use of the database in completing this assignment. Also, she wants you to retain copies of all Excel spreadsheets that you prepare to generate the needed information.
Task
A. How do EduToys's past customers break down by age and gender?
B. Which of EduToys's past customers have spent amounts that fall in the top 20% of all transactions (as measured in dollars)? Report the first name, last name, street address, city, state, and zip code for each of these customers.
C. Which products have generated sales revenues (in dollars) that fall in the top 25% of all such revenue contributions? Report the current inventory level, quantity on order, and supplier of each of these best-selling products.
D. How do the given 1998 sales (in dollars) break down by product type and product age group?
E. What proportion of all given transactions were conducted through the use of each type of credit card (including American Express, Discover, MasterCard, and Visa)?
F. What changes or additions would you recommend making to the present database? Provide the reasoning behind each of your recommendations.