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: Purchase Orders Raw Materials 1 Raw Materials Code Raw_Materials Description Raw Materials_Price Purchase_Order_ID Purchase Order Date Purchase Employee_ID Supplier_JD 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. D H Purchase Employee_ID Raw_Materials_Code Quantity_Purchased 3 2 1001 1003 1003 2 1 1 2 2 1 1 1 3 1 2 3 3 2 3 1 1 2 1 2 1 Supplier ID 1009 1009 1009 1008 1009 1008 1009 1009 1009 1009 1009 1009 1008 1009 1008 1009 1009 1008 1009 1009 1008 1008 1008 1009 1009 1009 1008 1009 1008 1009 1009 1002 1001 1001 1001 1003 1001 1001 1001 1001 1003 1001 1002 1002 1002 1003 1001 1003 Purchase_Order ID 20500 20501 20502 20503 20504 20505 20506 20506 20507 20508 20509 20510 20511 20512 20513 20514 20515 20516 20517 20518 20519 20520 20521 20522 20523 20524 20525 20525 20525 20526 20527 20527 20528 20529 20530 20530 20531 20532 20533 20534 20535 20536 20537 20538 20539 20540 20541 20541 20542 20542 20543 20544 1001 1003 Purchase Order Date 11/1/19 11/5/19 11/10/19 11/13/19 11/16/19 11/19/19 11/23/19 11/23/19 11/26/13 11/29/19 12/4/19 12/9/19 12/12/19 12/16/19 12/19/19 12/24/19 12/28/19 1/2/20 1/5/20 1/9/20 1/14/20 1/18/20 1/23/20 1/26/20 1/31/20 2/4/20 2/8/20 2/8/20 2/8/20 2/13/20 2/16/20 2/16/20 2/21/20 2/24/20 2/28/20 2/28/20 3/4/20 3/7/20 3/12/20 3/15/20 3/19/20 3/23/20 3/27/20 3/31/20 4/4/20 4/8/20 4/11/20 4/11/20 4/14/20 4/14/20 4/18/20 4/21/20 3 3 1 2 3 3 3 3 2 2 2 1 3 3 3 Raw Material Purchase Price 3 19.99 10 29 6 29 12 44.99 4 19.99 12 19.99 5 19.99 1 29 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 8 19.99 1 1 19.99 5 44.99 2 44.99 6 29 11 19.99 12 19.99 6 29 3 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 6 29 3 19.99 10 29 3 29 29 5 44.99 1 44.99 4 44.99 1001 1002 1001 1001 1002 1002 1003 1001 1001 1003 1001 1003 1001 1003 1003 1002 1002 1002 1002 1001 1003 1009 1008 1008 1008 1009 1008 1008 1009 1008 1009 1009 1008 1009 1008 1008 1009 1008 1009 1009 1009 1008 3 1 1 2 3 2 2 1 3 3 2 3 3 3 1 1 1 1 2 1002 1003 1001 1003 1003 1003 1002 1002 1002 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 O 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 O 5 O4 08 4. How many Purchase Orders were created for suppliers in Arkansas (for any products)? O 18 O 5 052 08 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 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: Purchase Orders Raw Materials 1 Raw Materials Code Raw_Materials Description Raw Materials_Price Purchase_Order_ID Purchase Order Date Purchase Employee_ID Supplier_JD 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. D H Purchase Employee_ID Raw_Materials_Code Quantity_Purchased 3 2 1001 1003 1003 2 1 1 2 2 1 1 1 3 1 2 3 3 2 3 1 1 2 1 2 1 Supplier ID 1009 1009 1009 1008 1009 1008 1009 1009 1009 1009 1009 1009 1008 1009 1008 1009 1009 1008 1009 1009 1008 1008 1008 1009 1009 1009 1008 1009 1008 1009 1009 1002 1001 1001 1001 1003 1001 1001 1001 1001 1003 1001 1002 1002 1002 1003 1001 1003 Purchase_Order ID 20500 20501 20502 20503 20504 20505 20506 20506 20507 20508 20509 20510 20511 20512 20513 20514 20515 20516 20517 20518 20519 20520 20521 20522 20523 20524 20525 20525 20525 20526 20527 20527 20528 20529 20530 20530 20531 20532 20533 20534 20535 20536 20537 20538 20539 20540 20541 20541 20542 20542 20543 20544 1001 1003 Purchase Order Date 11/1/19 11/5/19 11/10/19 11/13/19 11/16/19 11/19/19 11/23/19 11/23/19 11/26/13 11/29/19 12/4/19 12/9/19 12/12/19 12/16/19 12/19/19 12/24/19 12/28/19 1/2/20 1/5/20 1/9/20 1/14/20 1/18/20 1/23/20 1/26/20 1/31/20 2/4/20 2/8/20 2/8/20 2/8/20 2/13/20 2/16/20 2/16/20 2/21/20 2/24/20 2/28/20 2/28/20 3/4/20 3/7/20 3/12/20 3/15/20 3/19/20 3/23/20 3/27/20 3/31/20 4/4/20 4/8/20 4/11/20 4/11/20 4/14/20 4/14/20 4/18/20 4/21/20 3 3 1 2 3 3 3 3 2 2 2 1 3 3 3 Raw Material Purchase Price 3 19.99 10 29 6 29 12 44.99 4 19.99 12 19.99 5 19.99 1 29 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 8 19.99 1 1 19.99 5 44.99 2 44.99 6 29 11 19.99 12 19.99 6 29 3 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 6 29 3 19.99 10 29 3 29 29 5 44.99 1 44.99 4 44.99 1001 1002 1001 1001 1002 1002 1003 1001 1001 1003 1001 1003 1001 1003 1003 1002 1002 1002 1002 1001 1003 1009 1008 1008 1008 1009 1008 1008 1009 1008 1009 1009 1008 1009 1008 1008 1009 1008 1009 1009 1009 1008 3 1 1 2 3 2 2 1 3 3 2 3 3 3 1 1 1 1 2 1002 1003 1001 1003 1003 1003 1002 1002 1002 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 O 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 O 5 O4 08 4. How many Purchase Orders were created for suppliers in Arkansas (for any products)? O 18 O 5 052 08 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