Microsoft Access Data Queries
Part 2
Software skills: Database design and querying
Business skills: Inventory management
In this exercise, you will use database software to design a database for managing inventory for a small business. Kamen Rider, located in New York, sells road, mountain, hybrid, leisure, and childrens bicycles. Currently, Kamen Riders purchases bikes from three suppliers but plans to add new suppliers in the near future. Using the information found in the tables given in the small desktop database, help manage information about Kamen Riders suppliers and products. Perform the following.
1. Execute a query that identifies the five most expensive bicycles (for customers). The query should list the bicycles in descending order from most expensive to least expensive (by Selling Price), the quantity on hand for each, and the markup percentage for each. Save this query as Q1_Five_Most_Expensive_Bicycles. Hint: Profit Mark Up = (Selling Price Cost Price)/Cost Price. (2.5 points)
2. Execute a query that lists each supplier, its products, the quantities on hand, and associated reorder levels. For each supplier, the products should be sorted alphabetically. Include a criterion for bicycles that have Quantity_on_Hand that is more than 5. Hint: This requires hierarchical sorting and a numerical filter. Save this query as Q2_Supplier_Product (2.5 points)
3. Execute a query listing only the bicycles with names starting with E as well as those that end with er, and their corresponding suppliers. In your query, include the Product Name, Purchase Cost, and the suppliers Company_Name. This needs a little bit of thinking. Save this query as Q3_BR_bicycles (2.5 points).
4. To enhance the database, we need a table of supplier information. Create a table called Supplier_Info that has supplier telephone numbers. For full credit here, include an appropriate input mask. Populate the table with the appropriate fields. Populate the table with the suppliers to demonstrate our ability to enter data to a database and to check that it works. Be sure to enter all suppliers (there should be 5). Make up the telephone numbers. This requires some thinking on what field(s) to include other than telephone numbers in the new table. Recall that databases should be normalized, and we should join apples to apples, oranges to oranges; and we want to avoid redundancies. For purposes of this assignment, create the table accordingly. Theres no need to join other tables. Save this table as Supplier_Info (2.5 points).
Your work will be evaluated on the accuracy of the results, quality of your answers, and ability to apply the concepts. Submit your work on Canvas. There should be 2 documents: (1) a single word document (or pdf) for part 1, and (2) the database file (Access file) for part 2 by the due date. Late submissions will result in a 5% penalty for each day late.
Part 2 Data Queries (10 points)
Software skills: Database design and querying
Business skills: Inventory management
In this exercise, you will use database software to design a database for managing inventory for a small business. Kamen Rider, located in New York, sells road, mountain, hybrid, leisure, and childrens bicycles. Currently, Kamen Riders purchases bikes from three suppliers but plans to add new suppliers in the near future. Using the information found in the tables given in the small desktop database, help manage information about Kamen Riders suppliers and products. Perform the following.
1. Execute a query that identifies the five most expensive bicycles (for customers). The query should list the bicycles in descending order from most expensive to least expensive (by Selling Price), the quantity on hand for each, and the markup percentage for each. Save this query as Q1_Five_Most_Expensive_Bicycles. Hint: Profit Mark Up = (Selling Price Cost Price)/Cost Price. (2.5 points)
1. Execute a query that lists each supplier, its products, the quantities on hand, and associated reorder levels. For each supplier, the products should be sorted alphabetically. Include a criterion for bicycles that have Quantity_on_Hand that is more than 5. Hint: This requires hierarchical sorting and a numerical filter. Save this query as Q2_Supplier_Product (2.5 points)
1. Execute a query listing only the bicycles with names starting with E as well as those that end with er, and their corresponding suppliers. In your query, include the Product Name, Purchase Cost, and the suppliers Company_Name. This needs a little bit of thinking. Save this query as Q3_BR_bicycles (2.5 points).
1. To enhance the database, we need a table of supplier information. Create a table called Supplier_Info that has supplier telephone numbers. For full credit here, include an appropriate input mask. Populate the table with the appropriate fields. Populate the table with the suppliers to demonstrate our ability to enter data to a database and to check that it works. Be sure to enter all suppliers (there should be 5). Make up the telephone numbers. This requires some thinking on what field(s) to include other than telephone numbers in the new table. Recall that databases should be normalized, and we should join apples to apples, oranges to oranges; and we want to avoid redundancies. For purposes of this assignment, create the table accordingly. Theres no need to join other tables. Save this table as Supplier_Info (2.5 points).
Your work will be evaluated on the accuracy of the results, quality of your answers, and ability to apply the concepts. Submit your work on Canvas. There should be 2 documents: (1) a single word document (or pdf) for part 1, and (2) the database file (Access file) for part 2 by the due date. Late submissions will result in a 5% penalty for each day late.
Part 2
Q1
2.5 points
Outstanding
Very accurate query. Efficient and effective.
1.25 points
Acceptable
Somewhat accurate query. May have some errors or may not be a very efficient way to solve the problem. Some parts may not run properly.
0 points
Troll
Irrelevant or did not complete.
Part 2
Q2
2.5 points
Outstanding
Very accurate query. Efficient and effective.
1.25 points
Acceptable
Somewhat accurate query. May have some errors or may not be a very efficient way to solve the problem. Some parts may not run properly.
0 points
Troll
Irrelevant or did not complete.
Part 2
Q3
2.5 points
Outstanding
Very accurate query. Efficient and effective.
1.25 points
Acceptable
Somewhat accurate query. May have some errors or may not be a very efficient way to solve the problem. Some parts may not run properly.
0 points
Troll
Irrelevant or did not complete.
Part 2
Q4
2.5 points
Outstanding
Very accurate table. Very good choice of fields. Good use of the input mask. Accurate records were entered. The table works effectively.
1.25 points
Acceptable
Somewhat accurate table. May have some errors related to the input mask, choice of fields or accuracy of records entered. The table may not work properly.
0 points
Troll
Irrelevant or did not complete. ID Product_Number Product_Category Product_Name Purchase_Cost Selling_Price Supplier_Number Quantity_on_Hand Reorder_Level
1 1 Road Classic 109 207.49 290.49 1 7 2
2 2 Road Elegant 210 281.52 394.13 1 7 2
3 3 Road Runroad 1000 250.95 350.99 3 8 5
4 4 Mtn. Eagle 3 350.52 490.73 2 5 2
5 5 Mtn. Eagle 2 401.11 561.54 2 2 2
6 6 Mtn. Eagle 1 410.01 574.01 2 1 2
7 7 Hybrid Runblend 2000 180.95 255.99 3 0 5
8 8 Hybrid Eagle 7 150.89 211.46 2 9 2
9 9 Hybrid Tea for Two 429.02 609.00 4 3 2
10 10 Leisure Runcool 3000 85.95 135.99 3 5 5
11 11 Leisure Starlight 100.47 140.66 4 1 2
12 12 Leisure Supreme 350 50.00 70.00 1 3 5
13 13 Leisure Blue Moon 75.29 105.41 4 4 2
14 14 Children Runkidder 100 50.95 75.99 3 10 5
15 15 Children Red Rider 15.00 25.50 4 8 5
16 16 Children Coolest 100 69.99 97.98 1 6 5
17 17 Children Green Rider 95.47 133.66 4 4 5
18 18 Road Runroad 4000 390.95 495.99 3 5 5
19 19 Road Runroad 5000 450.95 599.99 3 5 5
20 20 Road Twist & Shout 490.50 635.70 4 0 2
21 21 Leisure Breeze 89.95 130.95 5 4 2
22 22 Leisure Breeze LE 109.95 149.95 5 5 4
23 23 Mtn. Bluff Breaker 375.00 495.00 5 3 3
24 24 Mtn. Jetty Breaker 455.95 649.95 5 1 2
ID_No Supplier_Number Company_Name Street_Address City State ZIP_Code
1 1 Bicyclist’s Choice 1459 Washington Avenue Sioux Falls SD 57106
2 2 Bike-One 2000 Oceanside Lane San Diego CA 92110
3 3 Run-Up Bikes 1155 Fifth Avenue Long Branch NJ 07521
4 4 Simpson’s Bike Supply 107 Piedmont Tulsa ok 74114
5 5 The Bike Path 1999 Stephen Hands Path East Hampton NY 11937
