Resources needed:
1. A windows PC with Microsoft Access installed.
2. Store and Regional Sales Database file.
Scenario:In this exercise, you will use database software to design a database for managing inventory for a small business. Sylvester’s Bike Shop, located in San Francisco, California, sells road, mountain, hybrid, leisure, and children’s bicycles. Currently, Sylvester’s purchases bikes from three suppliers but plans to add new suppliers in the near future. The Relational Database has already been created for you and is the file named ESS13ch06_Student. Below are the basic design of some of the tables and how one might start to look at how to design tables and how the will relate to each other.
Proposed Data Warehouse Format and Sample Files
Proposed Data Warehouse Format
| Product _ID | Product_ Description | Cost_per_ Unit | Units_ Sold | Sales_Region | Division | Customer_ID |
The following are sample files from the two systems that would supply the data for the data warehouse:
Mechanical Parts Division Sales System
| Prod_No | Product_ Description | Cost_per_Unit | Units_Sold | Sales_Region | Customer_ID |
| 60231 | 4” Steel bearing | 5.28 | 900,245 | N.E. | Anderson |
| 85773 | SS assembly unit | 12.45 | 992,111 | M.W | Kelly Industries |
Corporate Sales System
| Product_ID | Product_ Description | Unit_Cost | Units_Sold | Sales_
Territory |
Division |
| 60231 | Bearing, 4” | 5.28 | 900,245 | Northeast | Parts |
| 85773 | SS assembly unit | 12.45 | 992,111 | Midwest | Parts |
Deliverables: perform the following activities.
To see the relationships right-click on “Bicycles Low in Stock” and choose “Design View”. Manipulated the boxes for each table so that you can see the entire columns in each table.
Provide the following for each table (10 Points for each table):
Bicycles Low in Stock:
· Place a Screen shot showing each table and their relationships here:
· What is the Primary Key for:
o Product Table___________
o Supplier Table____________
· What is the foreign Key and what table has the foreign key?___________________
Do the same for the other 3 tables.
Create the following reports:
· Prepare a report that identifies the five most expensive bicycles. The report should list the bicycles in descending order from most expensive to least expensive, the quantity on hand for each, and the markup percentage for each. (10 Points)
· Prepare a report that lists each supplier, its products, the 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. (20 Points)
· 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 identified items. (10 Points)
· Write a brief description of how the database could be enhanced to improve management of the business further. What tables or fields should be added? What additional reports would be useful? (20 Points)
