Part a: Here you are being asked to combine the tables to obtain a complete record of the order and the shipment. What I would like you to do is to create a query (i.e. a new table) that contains in it each line item of each order, its related shipment if there was one, and all relevant data related to it. Specifically include the following fields for each record: customer code, customer name, sales order number, item number, item name, quantity ordered, quantity shipped.

I suggest you first create a query that combines the ORDER table and the ORDER LINE ITEM INVENTORY table and another query that combines the SALE table and the SALE LINE ITEM INVENTORY table. You can then proceed to combine the two newly created queries into a query and put the query into report form and print it out.

Part b: You are being asked to select the inventory items for which there is no order. You may use the appropriate wizard (i.e. find unmatched query wizard) to perform this task easily.

Part c: You are being asked to select the customers that have not placed an order. You may use the same wizard as in part b to create the appropriate query.

Part d: You are being asked to select the inventory items that were ordered but have not been shipped. You may use the same wizard as in part b to create the appropriate query.

Part e: You are being asked to create a query that lists all the inventory items, calculates their total value, and then sort the items in descending order of value. Note that the inventory table contains both unit costs and unit prices (i.e. sales prices). I would like you to do the extension (calculation) for each inventory item of both the cost and the sales price and sort in descending order by cost. YOU SHOULD ALSO CALCULATE TOTALS FOR THE TOTAL COST AND TOTAL SALES PRICE OF INVENTORY. You should design this query by yourself. Do not use a wizard.

Hint 1: Before handing your problem in check your results with your manual solution to make sure that you have achieved the desired goal.

Hint 2: When establishing a relationship check to see that you dont need to establish the relationship between two fields (especially in the case of a concatenated key).In other words sometimes you may need to connect two fields together.

5.8 Relations in Third Normal Form (3NF) shaded_Attribute(s)|= Primary Key SALES-ORDERS so Number Cust_code SALES ORDER line item INVENTORY SO Number Item Number ty ordered 1010 WHEEL 1011 ETC 1012 WHEEL 1013 IBS 1014 ETC 1015 WHEEL 1016 ETC 1010 2010-0050 1010 1000-1 1011 1002-1 1011 1001-1 1012 1003-1 1012 1001-1 1013 1001-1 1014 1003-1 1015 1003-1 1016 3961-1041 1016 3965-1050 1016 1003-1 1016 1000-1 2 5 5 10 5 10 50 25 25 CUSTOMERS Cust CodeCust_Name ETC IBS RODEBYKB STANS WHEEL 5 50 Bikes Et Cetera Inter. Bicycle Sales Rodebyke Bic. & Mopeds Stan's Cyclery Wheelaway Cycle Center 5 4 INVENTORY_ITEMS Item Number Item Name 1000-1 20 in. Bicycle 1001-1 1002-1 1003-1 1280-054 2010-0050 050-2197 961-1010 961-1041 965-1050 970-1011 26 in. Bicycle 24 in. Bicycle 20 in. Bicycle Kickstand Formed Handlebar Pedal Tire, 26 in. Tire Tube, 26 in. Spoke Reflector Wheel, 26 in. 5.8 Relations in Third Normal Form (3NF) shaded_Attribute(s)|= Primary Key SALES-ORDERS so Number Cust_code SALES ORDER line item INVENTORY SO Number Item Number ty ordered 1010 WHEEL 1011 ETC 1012 WHEEL 1013 IBS 1014 ETC 1015 WHEEL 1016 ETC 1010 2010-0050 1010 1000-1 1011 1002-1 1011 1001-1 1012 1003-1 1012 1001-1 1013 1001-1 1014 1003-1 1015 1003-1 1016 3961-1041 1016 3965-1050 1016 1003-1 1016 1000-1 2 5 5 10 5 10 50 25 25 CUSTOMERS Cust CodeCust_Name ETC IBS RODEBYKB STANS WHEEL 5 50 Bikes Et Cetera Inter. Bicycle Sales Rodebyke Bic. & Mopeds Stan's Cyclery Wheelaway Cycle Center 5 4 INVENTORY_ITEMS Item Number Item Name 1000-1 20 in. Bicycle 1001-1 1002-1 1003-1 1280-054 2010-0050 050-2197 961-1010 961-1041 965-1050 970-1011 26 in. Bicycle 24 in. Bicycle 20 in. Bicycle Kickstand Formed Handlebar Pedal Tire, 26 in. Tire Tube, 26 in. Spoke Reflector Wheel, 26 in