In this lab, you will: Required: 1. Create the relationships necessary among the three tables. Use the following ER diagram (Exhibit 4.42) as a guide: Raw_Materials 1 Raw_Materials Code Raw Materials Description Raw_Materials_Price Purchase Orders 1 Purchase_Order_ID Purchase Order Date Purchase_Employee_ID Supplier_ID Raw_Materials Code Quantity_Purchased Suppliers 1 Supplier_ID Supplier_Company_name Supplier Address Supplier_City Supplier_St Supplier Zip 2. Create a pivot table (make sure to place a check mark in the box to add the data to the Data Model when inserting the pivot table). 3. In the pivot table, add the following fields: Raw_Materials_Description (rows), Supplier_St (rows), and a count of Purchase_Order_ID (Values). Ask the Question: How can we pull in descriptive attributes from separate tables into the transaction table that we wish to use for analysis? Master the Data: Apply the same steps as Lab 4-4 to the Lab 4-4 Alt Data.xlsx dataset. The 4-4 Alt dataset contains three tables related to the purchasing process. Build the relationships in Excel so that you can create a pivot table using all three tables. Software needed Excel Screen capture tool (Windows: Snipping Tool; Mac: Cmd+Shift+4) Data: Lab 4-4 Alt Data.xlsx. Perform the Analysis: Refer to Lab 4-4 Alternative in the text for instructions and Lab 4-4 steps foreach the of lab parts. Share the Story: Because of the relationships that you built, you can build pivot tables with any of the fields from all three tables. Supplier_ID Raw Materials Code 3 3 2 2 1 1 2 1 1 1 3 1 2 3 3 2 3 1 1 2 1 2 1 1 1 Purchase_Order ID Purchase_Order_Date Purchase Employee_ID 2 20500 11/1/19 1009 3 20501 11/5/19 1009 4 20502 11/10/19 1009 5 20503 11/13/19 1008 6 20504 11/16/19 1009 7 20505 11/19/19 1008 8 20506 11/23/19 1009 9 20506 11/23/19 1009 10 20507 11/26/19 1009 11 20508 11/29/19 1009 12 20509 12/4/19 1009 13 20510 12/9/19 1009 14 20511 12/12/19 1008 15 20512 12/16/19 1009 16 20513 12/19/19 1008 17 20514 12/24/19 1009 18 20515 12/28/19 1009 19 20516 1/2/20 1008 20 20517 1/5/20 1009 21 20518 1/9/20 1009 22 20519 1/14/20 1008 23 20520 1/18/20 100 24 20521 1/23/20 1008 25 20522 1/26/20 1009 26 20523 1/31/20 1009 27 20524 2/4/20 1009 28 20525 2/8/20 1008 29 20525 2/8/20 1009 30 20525 2/8/20 1008 31 20526 2/13/20 100g 32 20527 2/16/20 1009 33 20527 2/16/20 1009 34 20528 2/21/20 1008 35 20529 2/24/20 1008 36 20530 2/28/20 100% 37 20530 2/28/20 1009 38 20531 3/4/20 1008 39 20532 3/7/20 1008 40 20533 3/12/20 1009 41 20534 3/15/20 1008 42 20535 3/19/20 1009 43 20536 3/23/20 1009 20537 3/27/20 1008 45 20538 3/31/20 1009 46 20539 4/4/20 1008 47 20540 4/8/20 1008 48 20541 4/11/20 1009 49 20541 4/11/20 1008 50 20542 4/14/20 1009 51 20542 4/14/20 1009 52 20543 4/18/20 1009 53 20544 4/21/20 1 003 3 3 3 1 1 2 3 Quantity_Purchased 1001 1003 1003 1002 1001 1001 1001 1003 1001 1001 1001 1001 1003 1001 1002 1002 1002 1003 1001 1003 1001 1003 1001 1002 1001 1001 1002 1002 1003 1001 1001 1003 1001 1003 1001 1003 1003 1002 1002 1002 1002 1001 1003 1002 1003 1001 1003 1003 1003 1002 1002 1002 Raw Material Purchase_Price 3 19.99 10 29 29 12 44.99 4 19.99 12 19.99 5 19.99 1 29 1 1 19.99 4 19.99 12 19.99 11 19.99 6 29 7 19.99 11 44.99 11 44.99 4 44.99 11 29 10 19.99 12 29 1 19.99 12 29 6 19.99 10 44.99 B 19.99 1 19.99 5 44.99 2 44.99 6 29 11 19.99 12 19.99 6 29 3 19.99 10 29 2 19.99 2 29 7 29 2 44.99 12 44.99 5 44.99 7 44.99 12 19.99 6 29 5 44.99 29 3 19.99 10 29 3 29 6 29 5 44.99 1 44.99 4 44.99 3 3 3 2 2 2 1 3 3 3 3 1 1 2 3 3 2 2 1 3 2 2 3 3 1 1 1 1 2 A 1 Supplier_ID 2 1 3 2 Supplier_St MN WA AR 4 3 5 4 Supplier_company_name Supplier_Address Supplier_City Northern Brewery Homebrew S1 6021 Lyndale Ave S Minneapolis Hops Direct, LLC 686 Green Valley Road Mabton The Home Brewery 455 E. Township St. Fayetteville The Payroll Company 408 N. Walton Blvd Bentonville City of Fayetteville - Water 113 West Mountain Stre Fayetteville Southwestern Gas P.O. Box 98890 Las Vegas Bob's Repair 3541 Horse Meadow Dri Fayetteville Farmer's Insurance 1943 College Avenue Fayetteville Lindsey Management Co. 1200 East Joyce Blvd. Fayetteville Supplier_Zip Supplier_Phone 55419 612-843-4444 98935 509-837-3616 72703 479-587-1440 72712 1-866-899-8439 72701 479-521-1258 877-860-6020 72703 479-345-2345 72701 479-236-0013 72703 479-521-6686 Notes Bulk Malt Supplier Bulk Hops Supplier Small batch and Speciality Ingredient Supplier Payroll Water Electric and Gas Repairs Insurance Rent AR AR NV 6 5 7 6 8 7 9 8 AR AR AR 10 9 11 fx 44.99 A B C D E 1 Raw_Materials_Code Raw_Materials_Description 2 1001 Malt-Barley 3 1002 Malt - Wheat 4 1003 Hops 5 6 7 Raw Materials Price $19.99 $44.99 $29.00 8 Required: 1. What is the Foreign Key in the Purchase_Orders table that allows you to relate it to the Suppliers table? O Customer_ID Employee_ID O Purchase_Order_ID O Supplier_ID 2. What is the Foreign Key in the Purchase_Orders table that allows you to relate it to the Raw_Materials table? O Customer_ID Raw_Materials_Code O Supplier_ID O Purchase_Order_ID 3. How many Purchase Orders for Hops were created for suppliers in Arkansas? O 52 5 04 08 4. How many Purchase Orders were created for suppliers in Arkansas (for any products)? 8 5. Change the PivotTable so that you see the sum of quantity purchased associated with each product and state. What was the quantity of hops purchased from suppliers in Minnesota? 28 42 344 50