part2

see attachments

Part 2

Data Queries (1

0 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 children’s bicycles. Currently, Kamen Rider’s 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 Rider’s 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 supplier’s 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. There’s 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.

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.

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 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
Part 2 Q3	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.

1

0

.

9

1 7 2

2 2 Road

1.

2

94.

1 7 2

3 3 Road

3 8 5

4 4

2 5 2

5 5 Mtn.

1.54

2 2 2

6 6 Mtn.

2 1 2

7 7

00

0.95

3 0 5

8 8 Hybrid

0.89

1.46

2 9 2

9 9 Hybrid

4 3 2

10 10

3 5 5

11 11 Leisure

0.66

4 1 2

12 Leisure

1 3 5

13 13 Leisure

4 4 2

14 14

3 10 5

15 15 Children

4 8 5

16 Children

1 6 5

17 Children

4 4 5

18 18 Road

3 5 5

19 Road

3 5 5

20 20 Road

4 0 2

21 21 Leisure

5 4 2

22 Leisure

5 5 4

23 Mtn.

5 3 3

24 Mtn.

5 1 2

ID	Product_Number	Product_Category	Product_Name	Purchase_Cost	Selling_Price		Supplier_Number	Quantity_on_Hand	Reorder_Level
										1						Road	Classic 1		0			9	¤																					2				7														4	¤ 290.49
Elegant 2			10	¤ 2				8																							5	¤														3		13
Runroad 1000	¤ 250.95	¤ 350.99
				Mtn.	Eagle 3	¤ 350.52	¤ 490.73
Eagle 2	¤ 401.		11	¤ 5			6
Eagle 1	¤ 410.01	¤ 574.01
		Hybrid	Runblend		20	¤		18	¤ 255.99
Eagle 7	¤		15	¤		21
Tea for Two	¤ 429.02	¤ 609.00
					Leisure	Runcool 3000	¤ 85.95	¤ 135.99
Starlight	¤ 100.47	¤		14
	12	Supreme 350	¤ 50.00	¤ 70.00
Blue Moon	¤ 75.29	¤ 105.41
			Children	Runkidder 100	¤ 50.95	¤ 75.99
Red Rider	¤ 15.00	¤ 25.50
	16	Coolest 100	¤ 69.99	¤ 97.98
	17	Green Rider	¤ 95.47	¤ 133.66
Runroad 4000	¤ 390.95	¤ 495.99
	19	Runroad 5000	¤ 450.95	¤ 599.99
Twist & Shout	¤ 490.50	¤ 635.70
Breeze	¤ 89.95	¤ 130.95
	22	Breeze LE	¤ 109.95	¤ 149.95
	23	Bluff Breaker	¤ 375.00	¤ 495.00
	24	Jetty Breaker	¤ 455.95	¤ 649.95

Supplier_Number

1 1

2 2

3 3

4 4

5 5

ID_No	Company_Name	Street_Address	City	State	ZIP_Code
Bicyclist’s Choice	1459 Washington Avenue	Sioux Falls	SD	57106
Bike-One	2000 Oceanside Lane	San Diego	CA	92110
Run-Up Bikes	1155 Fifth Avenue	Long Branch	NJ	07521
Simpson’s Bike Supply	107 Piedmont	Tulsa	ok	74114
The Bike Path	1999 Stephen Hands Path	East Hampton	NY	11937