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
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
(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
(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).