Create in MS Access:

1. Create all necessary tables based on the entities identified in each of these two workbooks.

2. For each table, set the appropriate data type for attributes and specify the primary key.

3. In the Relationship window in Access, connect tables derived from each workbook by setting up foreign keys and enforcing referential integrity. Note that some table(s) may participate in more than one relationship.

4. In the same relationship window, connect the tables from OrderSummary workbook with tables from EmployeeSkill workbook (so that all tables are connected).

5. In either step 3 or step 4 above, you may run into an error caused by a data point violating the referential integrity, identify the data error and update the value with the nearest value from the associated parent table.

6. Create a query in Access named HW3_Query1_Orders to show the detailed information of all orders (i.e. the first 18 rows of the original OrderSummary table with all columns).

7. Create a query named HW3_Query2_Orders&Customers to show the entire original OrderSummary workbook (all rows and columns).

8. Create a query named HW3_Query3_OrdersByCust1&2 to show all orders placed by customers 1 and 2.

9. Create a query named HW3_Query4_AvgPricefromFL to show the average price of all products ordered by customers from FL.

10. Create a query named HW3_Query5_Supervisedby9002 to show the information of all employees supervised by Employee 9002 and their supervisor's information (i.e. ID, name, hiredate and title)." So essentially this query will require you to show the same employee related columns twice, first for the employee, then for their supervisor.

11. Extra credit (optional): Create a query named HW3_Query6_ExtraCredit to show the total dollar amount of all orders in charged by employees supervised by 9002.

J A B C E 1 Employee_ID Employee_FNam Employee_LNan Employee_HireDate Employee_Title 2 2345 Johnny Jones 2/14/1993 DBA 3 2345 Johnny Jones 2/14/1993 DBA 4 2345 Johnny Jones 2/14/1993 DBA 5 3373 Franklin Johnson 3/15/2000 Purchasing Agent 6 4893 Patricia Richards 6/11/2002 DBA 7 4893 Patricia Richards 6/11/2002 DHA 8 6234 Jasmine Patel 8/10/2003 Programmer 9 6234 Jasmine Patel 8/10/2003 Programmer 10 6234 Jasmine Patel 8/10/2003 Programmer 11 8273 Marco Bienz 7/28/2004 Analyst 12 8273 Marco Bienz 7/28/2004 Analyst 13 9002 Ben Joiner 5/20/2008 Clerk 14 9002 Ben Joiner 5/20/2008 Clerk 15 9283 Juan Chavez 7/4/2008 Clerk 16 9382 Jessica Johnson 8/2/2008 Database Programmer 17 9382 lessica Johnson 8/2/2008 Database Programmer 18 9382 lessica Johnson 8/2/2008 Database Programmer 19 10282 Amanda Richardson 4/11/2009 Clerk 20 13383 Raymond Matthews 3/12/2010 Programmer 21 13567 Robert Almond 9/30/2010 Analyst 22 13567 Robert Almond 9/30/2010 Analyst 23 13932 Megan Lee 9/29/2011 Programmer 24 14311 Lee Duong 9/1/2012 Programmer 25 26 27 F H Supervisor_ID Skill_ID Skill_Name Skill Description Certified_Date 9002 100 Basic Database M Create and manage database user accounts. 2/11/1993 9002 110 Basic Web Design Create and maintain HTML and CSS documents. 8/9/1999 9002 180 Advanced Databa Manage Database Server Clusters. 2/14/1993 9002 120 Advanced Spread: Use of advanced functions, user-defined functions, at 6/20/2007 9002 180 Advanced Databa Manage Database Server Clusters. 6/11/2002 9002 220 Advanced Databa Use of advanced data manipulation methods for mul 9/20/2008 9002 110 Basic Web Design Create and maintain HTML and CSS documents. 8/10/2003 9002 200 Advanced C# Prop Create multi-tier applications using multithreading 8/10/2003 9002 210 Basic Database M Create simple data retrieval and manipulation staten 1/29/2008 13932 110 Basic Web Design Create and maintain HTML and CSS documents. 3/8/2005 13932 190 Advance Process Evaluate and Redesign cross-functional internal and e 8/19/2008 14311 110 Basic Web Design Create and maintain HTML and CSS documents. 5/16/2009 14311 120 Advanced Spread: Use of advanced functions, user-defined functions, al 5/16/2009 13932 13932 140 Basic Database D. Create simple data models. 8/2/2008 13932 210 Basic Database M Create simple data retrieval and manipulation stater 8/2/2008 13932 220 Advanced Databa Use of advanced data manipulation methods for mul 5/1/2009 13932 9002 170 Basic C# Program Create single tier data aware modules. 3/12/2010 130 Basic Process Mo Create core business process models using standard I 9/30/2010 140 Basic Database D. Create simple data models. 5/23/2011 14311 110 Basic Web Design Create and maintain HTML and CSS documents. 9/1/2012 150 Master Database Create Integrated trisger and procedure packages for a distributed environment. 160 Basic Spreadshee Create single tab worksheets with basic formulas CustomerCity Gainesville Gainesville Gainesville Santa Clara Ogden Sacramento Sacramento Albany plano Plano 1 1 1006 Plano B D F 1 OrderiD Order Date Order Employeeinchar Customerld CustomerName Customer Address 2. 1001 10/21/2010 1 2345 1 Contemporary Casuals 1355 S Hines Blvd 3 1001 10/21/2010 2 2345 1 Contemporary Casuals 1355 S Hines Blvd 4 1001 10/21/2010 3 2345 1 Contemporary Casuals 1355 S Hines Blvd 1002 10/21/2010 1 6324 8 California Classics 816 Peach Rd 6 1003 10/22/2010 1 4893 15 Mountain Scenes 4132 Main Street 7 1004 10/22/2010 1 8273 5 Impressions 5585 Westcott Ct. 8 1004 10/22/2010 2 8273 5 Impressions 5585 Westcott Ct. 9 1005 10/24/2010 9283 3 Home Furnishings 1900 Allard Ave. 10 1006 10/24/2010 1 9283 2 Value Furniture 15145 S.W. 17th St 11 10/24/2010 2 9283 2 Value Furniture 15145 5. W 17th St. 12 1006 10/24/2010 3 9283 2 Value Furniture 15145 S.W. 17th St. 13 1007 10/27/2010 1 6234 11 American Euro Lifestyles 2424 Missouri Ave N 1007 10/27/2010 2. 6234 11 American Euro Lifestyles 2424 Missouri Ave N 15 1008 10/30/2010 1 10282 12 Battle Creek Furniture 345 Capital Ave. SW 1008 10/30/2010 2 10282 12 Battle Creek Furniture 345 Capitol Ave. SW 17 1009 11/5/2010 1 8273 4 Eastern Furniture 1925 Beltline Rd. 18 1009 11/5/2010 2 8273 4 Eastern Furniture 1925 Beltline Rd 19 1010 11/5/2010 1 13383 1 Contemporary Casuals 1355 S Hines Blvd 20 7 Period Furniture 394 Rainbow Dr 6 Furniture Gallery 325 Flatiron De 22 9 M and H Casual Furniture 3709 First Street 3 10 Seminole Interiors 7400 Rocky Point Dr. 24 13 Heritage Furnishings 66789 College Ave. es 14 Kaneohe Homes 112 Kiowal St. 26 27 Prospect Park Prospect Park Battle Creek Battle Creek Carteret Carteret Gainesville Seattle Roulder Clearwater Seminale Carlisle Kaneohe M P. Custome Customer Post Ordereduan Products Product Description ProductFinish Product Standard Price FL 32501-2871 1 End Table Cherry $175.00 FL 32601-2871 2 Coffee Table Natural Ash $200.00 FL 32601-2871 1 4 Entertainment Center Natural Maple $650.00 CA 96915-7754 5 3 Computer Desk Natural Ash $375.00 UJT 84403-4432 3 3. Computer Desk Natural Ash $375.00 CA 94206-4056 2 6 8-Drawer Desk White Ash $750.00 CA 94206-4056 8 Computer Desk Walnut $250.00 NY 12209-1125 4 4 Entertainment Center Natural Maple $650.00 TX 75094-7743 4 Entertainment Center Natural Maple $650.00 TX 75094-7743 2 5 Writers Desk Cherry $325.00 TX 75094-7743 2 7 Dining Table Natural Ash $800.00 NI 07508-5621 3 1 End Table Cherry $175.00 NI 07508-5621 2 2 Coffee Table Natural Ash $200.00 MI 49015-3401 3 3 Computer Desk Natural Ash $375.00 M 49015-3401 3 8 Computer Desk Walnut $250.00 NJ 07008-3188 2 4 Entertainment Center Natural Maple $650.00 NJ 07008-3188 3 7 Dining Table Natural Ash $800.00 FL 32601 2871 10 Computer Desk Walnut $250.00 WA 97954.5589 CO 80514-4432 FL 34620-2314 FL 34546-4423 PA 17013-8834 HI 96744-2537 J A B C E 1 Employee_ID Employee_FNam Employee_LNan Employee_HireDate Employee_Title 2 2345 Johnny Jones 2/14/1993 DBA 3 2345 Johnny Jones 2/14/1993 DBA 4 2345 Johnny Jones 2/14/1993 DBA 5 3373 Franklin Johnson 3/15/2000 Purchasing Agent 6 4893 Patricia Richards 6/11/2002 DBA 7 4893 Patricia Richards 6/11/2002 DHA 8 6234 Jasmine Patel 8/10/2003 Programmer 9 6234 Jasmine Patel 8/10/2003 Programmer 10 6234 Jasmine Patel 8/10/2003 Programmer 11 8273 Marco Bienz 7/28/2004 Analyst 12 8273 Marco Bienz 7/28/2004 Analyst 13 9002 Ben Joiner 5/20/2008 Clerk 14 9002 Ben Joiner 5/20/2008 Clerk 15 9283 Juan Chavez 7/4/2008 Clerk 16 9382 Jessica Johnson 8/2/2008 Database Programmer 17 9382 lessica Johnson 8/2/2008 Database Programmer 18 9382 lessica Johnson 8/2/2008 Database Programmer 19 10282 Amanda Richardson 4/11/2009 Clerk 20 13383 Raymond Matthews 3/12/2010 Programmer 21 13567 Robert Almond 9/30/2010 Analyst 22 13567 Robert Almond 9/30/2010 Analyst 23 13932 Megan Lee 9/29/2011 Programmer 24 14311 Lee Duong 9/1/2012 Programmer 25 26 27 F H Supervisor_ID Skill_ID Skill_Name Skill Description Certified_Date 9002 100 Basic Database M Create and manage database user accounts. 2/11/1993 9002 110 Basic Web Design Create and maintain HTML and CSS documents. 8/9/1999 9002 180 Advanced Databa Manage Database Server Clusters. 2/14/1993 9002 120 Advanced Spread: Use of advanced functions, user-defined functions, at 6/20/2007 9002 180 Advanced Databa Manage Database Server Clusters. 6/11/2002 9002 220 Advanced Databa Use of advanced data manipulation methods for mul 9/20/2008 9002 110 Basic Web Design Create and maintain HTML and CSS documents. 8/10/2003 9002 200 Advanced C# Prop Create multi-tier applications using multithreading 8/10/2003 9002 210 Basic Database M Create simple data retrieval and manipulation staten 1/29/2008 13932 110 Basic Web Design Create and maintain HTML and CSS documents. 3/8/2005 13932 190 Advance Process Evaluate and Redesign cross-functional internal and e 8/19/2008 14311 110 Basic Web Design Create and maintain HTML and CSS documents. 5/16/2009 14311 120 Advanced Spread: Use of advanced functions, user-defined functions, al 5/16/2009 13932 13932 140 Basic Database D. Create simple data models. 8/2/2008 13932 210 Basic Database M Create simple data retrieval and manipulation stater 8/2/2008 13932 220 Advanced Databa Use of advanced data manipulation methods for mul 5/1/2009 13932 9002 170 Basic C# Program Create single tier data aware modules. 3/12/2010 130 Basic Process Mo Create core business process models using standard I 9/30/2010 140 Basic Database D. Create simple data models. 5/23/2011 14311 110 Basic Web Design Create and maintain HTML and CSS documents. 9/1/2012 150 Master Database Create Integrated trisger and procedure packages for a distributed environment. 160 Basic Spreadshee Create single tab worksheets with basic formulas CustomerCity Gainesville Gainesville Gainesville Santa Clara Ogden Sacramento Sacramento Albany plano Plano 1 1 1006 Plano B D F 1 OrderiD Order Date Order Employeeinchar Customerld CustomerName Customer Address 2. 1001 10/21/2010 1 2345 1 Contemporary Casuals 1355 S Hines Blvd 3 1001 10/21/2010 2 2345 1 Contemporary Casuals 1355 S Hines Blvd 4 1001 10/21/2010 3 2345 1 Contemporary Casuals 1355 S Hines Blvd 1002 10/21/2010 1 6324 8 California Classics 816 Peach Rd 6 1003 10/22/2010 1 4893 15 Mountain Scenes 4132 Main Street 7 1004 10/22/2010 1 8273 5 Impressions 5585 Westcott Ct. 8 1004 10/22/2010 2 8273 5 Impressions 5585 Westcott Ct. 9 1005 10/24/2010 9283 3 Home Furnishings 1900 Allard Ave. 10 1006 10/24/2010 1 9283 2 Value Furniture 15145 S.W. 17th St 11 10/24/2010 2 9283 2 Value Furniture 15145 5. W 17th St. 12 1006 10/24/2010 3 9283 2 Value Furniture 15145 S.W. 17th St. 13 1007 10/27/2010 1 6234 11 American Euro Lifestyles 2424 Missouri Ave N 1007 10/27/2010 2. 6234 11 American Euro Lifestyles 2424 Missouri Ave N 15 1008 10/30/2010 1 10282 12 Battle Creek Furniture 345 Capital Ave. SW 1008 10/30/2010 2 10282 12 Battle Creek Furniture 345 Capitol Ave. SW 17 1009 11/5/2010 1 8273 4 Eastern Furniture 1925 Beltline Rd. 18 1009 11/5/2010 2 8273 4 Eastern Furniture 1925 Beltline Rd 19 1010 11/5/2010 1 13383 1 Contemporary Casuals 1355 S Hines Blvd 20 7 Period Furniture 394 Rainbow Dr 6 Furniture Gallery 325 Flatiron De 22 9 M and H Casual Furniture 3709 First Street 3 10 Seminole Interiors 7400 Rocky Point Dr. 24 13 Heritage Furnishings 66789 College Ave. es 14 Kaneohe Homes 112 Kiowal St. 26 27 Prospect Park Prospect Park Battle Creek Battle Creek Carteret Carteret Gainesville Seattle Roulder Clearwater Seminale Carlisle Kaneohe M P. Custome Customer Post Ordereduan Products Product Description ProductFinish Product Standard Price FL 32501-2871 1 End Table Cherry $175.00 FL 32601-2871 2 Coffee Table Natural Ash $200.00 FL 32601-2871 1 4 Entertainment Center Natural Maple $650.00 CA 96915-7754 5 3 Computer Desk Natural Ash $375.00 UJT 84403-4432 3 3. Computer Desk Natural Ash $375.00 CA 94206-4056 2 6 8-Drawer Desk White Ash $750.00 CA 94206-4056 8 Computer Desk Walnut $250.00 NY 12209-1125 4 4 Entertainment Center Natural Maple $650.00 TX 75094-7743 4 Entertainment Center Natural Maple $650.00 TX 75094-7743 2 5 Writers Desk Cherry $325.00 TX 75094-7743 2 7 Dining Table Natural Ash $800.00 NI 07508-5621 3 1 End Table Cherry $175.00 NI 07508-5621 2 2 Coffee Table Natural Ash $200.00 MI 49015-3401 3 3 Computer Desk Natural Ash $375.00 M 49015-3401 3 8 Computer Desk Walnut $250.00 NJ 07008-3188 2 4 Entertainment Center Natural Maple $650.00 NJ 07008-3188 3 7 Dining Table Natural Ash $800.00 FL 32601 2871 10 Computer Desk Walnut $250.00 WA 97954.5589 CO 80514-4432 FL 34620-2314 FL 34546-4423 PA 17013-8834 HI 96744-2537