Assignment # 5 (Due, Friday, November 17, 5PM) Access Database Exercise: Building a Relational Database for a Small Business Sylvester’s Bike Shop, located in Vancouver, British Columbia, sells road, mountain, hybrid, leisure, and children’s bicycles. Currently, Sylvester’s purchases bikes from four suppliers, but plans to add new suppliers in the near future. This rapidly growing business needs a database system to manage this information. Initially, the database should house information about suppliers and products. The database will contain four tables: a customer table, a supplier table, a product table and a purchases table. The reorder level in the product table refers to the number of items in inventory that triggers a decision to order more items to prevent a stockout. (In other words, if the number of units of a particular inventory falls below the reorder level, the item should be reordered.) The user should be able to perform several queries and produce several managerial reports based on the data contained in these tables. Using the information found in the initial database provided, modify the relational database for Sylvester’s. Perform the following activities. 1. Add all relevant relationships among tables in the database. Ensure that you enforce referential integrity. 2. Create a form to be used for searching/creating new customer records. (Name your form Question2) 3. Create a form that allows you to view and add purchases to a customer record within a single form. (Name your form Question3) 4. 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. (Markup percentage is the difference between the selling price and the purchase cost, divided by the purchase cost). (Name your report Question4) 5. Prepare a report that identifies the value of inventory on hand for each product and provides the grand total inventory value of all products. (Name your report Question5) 6. Prepare a report that lists each supplier, its products, the respective 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. (Name your report Question6) 7. Prepare a report listing only the bicycles that are low in stock and need to be reordered. (Those for which the quantity on hand is less than the reorder point). The report should provide supplier information for the items identified. This report should also include the total cost of purchasing all items identified as requiring reorder. (Name your report Question7) 8. Prepare a report that identifies the total value of purchases made by each customer, sorted alphabetically by their last name. (Name your report Question8) To access a copy of the initial database, perform the following steps; 1. Right-click “My Computer”. 2. Select “Map Network Drive…” 3. In the Folder box, type \\fileweb1\info101 4. Note the drive which will be mapped and select Finish. 5. When the window appears, copy the access database, Sylvester to your h: drive. 6. Access the database files from the copy you have just created to perform the steps required in the assignment. 7. When you have completed the assignment, attach the copy of the database you have modified to an email message, addressed to me (email@example.com).
Pages to are hidden for
"Access Database Exercise"Please download to view full document