Achieving Operational Excellence. Building a
Relational Database for Inventory Management
This exercise requires that students know how to create queries and reports using information from multiple tables. The solutions provided here were created using the query wizard and report wizard capabilities of Access. Students can, of course, create more sophisticated reports if they wish.
The database would need some modification to answer other important questions about the business. The owners might want to know, for example, which are the fastest-selling bicycles. The existing database shows products in inventory and their suppliers. The owners might want to add an additional table (or tables) in the database to house information about product sales, such as the product identification number, date placed in inventory, date of sale, purchase price, and customer name, address, and telephone number. Management could use this enhanced database to create reports on best selling bikes over a specific period, the number of bicycles sold during a specific period, total volume of sales over a specific period, or best customers. Students should be encouraged to think creatively about what other pieces of information should be captured on the database that would help the owners manage the business. (Data file is presented in Excel table format)
1. Prepare a report that identifies the five most expensive bicycles. The report should list the bicycles in descending order from most expensive to lease expensive, the quantity on hand for each, and the markup percentage for each.
2. Prepare a report that lists each supplier, its products, their quantities on hand, and associated reorder levels. The report should be sorted alphabetically by supplier. Within each supplier category, the products should be sorted alphabetically.
3. Prepare a report listing only the bicycles that are low in stock and need to be reordered. The report should provide supplier information for the items identified.
4. Write a brief description of how the database could be enhanced to further improve management of the business. What tables or fields should be added? What additional reports would be useful?