Question 2. Use the Baseball database you created in the first week for this assignment (copy and save as YourLastNameLec3). Now by analyzing the baseball inventory data, we know one table design is not a good design because we put item information and supplier information into one table. A better design is to have two tables. Please:

(2a). Create a new table for Suppliers, set the primary key, input data (Note: the supplier data are from Week 1 Assignment).

(2b). Change the structure of the original baseball inventory table (Note: removing SupplierName, and SupplierTelephone, but keeping SupplierCode as the foreign key)

Then create Access GUI queries (please save each query)

(2c). Display item id, description, selling price, supplier name for all items.

(2d). Display the item id, description, cost, and units on hand of all items supplied by Beverage Holders

(2e) Display the average selling price of supplier Beverage Holderss items

(2f) Find a count of different types of items grouped by supplier names

Question 3. Use the baseball database you created in question 2 (now you have two tables) for this assignment. Write SQL queries (please save each query):

(3a). Display the item id, description, and on-hand value (UnitsOnHand*Cost), supplier name of all items.

(3b). Display the average selling price of supplier Logo Goods (we only know this supplier name) items. Use Join

(3c). Display the average selling price of supplier Logo Goods (we only know this supplier name) items. Use Subqueryested query

(3d). Display the average selling price of supplier Beverage Holderss items

(3e). display supplier name and the count of different types of items grouped by supplier names.

(3f). display item id, decription, selling price for the item that has the lowest selling price.

Note: when you join two or more tables together, dont use INNER JOIN, LEFT JOIN, RIGHT JOIN format, otherwise, you will get 0 point.

Please submit the Access file for Q2 and Q3.

here is the data

All Access Obje... B BaseballTeam ItemID 3663 Search... Tables B BaseballTeam 3683 4563 4593 5923 Description UnitsOnHan, Baseball Cap 30 Coasters 12 Coffee Mug 20 Glasses 8 Jacket 12 Short 10 Sport Towel 24 Sweatshirt 9 Tee Shirt 32 Coffee Travel Mug 11 Cost - Selling Price - SupplierCoc - SupplierNar - SupplierTeli - $10.15 $18.76 LG Logo Goods 517-555-3853 $7.45 $9.00 BH Beverage Hold 317-555-4747 $1.85 $4.75 BH Beverage Hold 317-555-4747 $8.20 $10.75 BH Beverage Hold 317-555-4747 $44.75 $54.95 LG Logo Goods 517-555-3853 $14.95 $19.95 AC Al Clothes 616-555-9228 $3.25 $6.75 LG Logo Goods 517-555-3853 $27.45 $ $34.95 AC Al Clothes 616-555-9228 $9.50 $14.95 AC Al Clothes 616-555-9228 $2.90 $3.25 BH Beverage Hold 317-555-4747 $0.00 $0.00 5953 6189 6343 7810 7930 *