Instructions.
HW 3 Attached Files: 1S_3063_HW3.xlsx (16.482 KB) HW 3 allows you to implement the data models we learned so far to an actual database. Please review the two lectures for Chapter 4 before starting this assignment. This assignment requires you to build a relational database in MS Access based on the data included in the attached spreadsheet file (S_3063_HW3.xlsx). This spreadsheet contains two workbooks: An Order Summary workbook and a EmployeeSkill workbook. Create an MS Access file based on these two workbooks and name the Access file as HW3_LastName_First Name. Complete the following tasks (for your convenience, the same instructions are provided in the third workbook in the excel file): 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 Order Summary table with all columns). 7. Create a query named HW3_Query Orders&Customers to show the entire original Order Summary 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_ Supervised by9002 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. Note that all assignments are individual assignments and you should work on the assignment independently. Only the access file needs to be turned in. HW 3 Attached Files: 1S_3063_HW3.xlsx (16.482 KB) HW 3 allows you to implement the data models we learned so far to an actual database. Please review the two lectures for Chapter 4 before starting this assignment. This assignment requires you to build a relational database in MS Access based on the data included in the attached spreadsheet file (S_3063_HW3.xlsx). This spreadsheet contains two workbooks: An Order Summary workbook and a EmployeeSkill workbook. Create an MS Access file based on these two workbooks and name the Access file as HW3_LastName_First Name. Complete the following tasks (for your convenience, the same instructions are provided in the third workbook in the excel file): 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 Order Summary table with all columns). 7. Create a query named HW3_Query Orders&Customers to show the entire original Order Summary 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_ Supervised by9002 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. Note that all assignments are individual assignments and you should work on the assignment independently. Only the access file needs to be turned in. File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments i PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing X D29 X foc A B C D E F H K L 1 Employee_ID Employee_FNc Employee_LN: Employee_HireDat Employee_Title Supervisor_ID Skill_ID Skill_Name Skill_Description Certified_Date 2 2345 Johnny Jones 2/14/1993 DBA 9002 100 Basic Database i Create and manage database user accounts. 2/14/1993 3 2345 Johnny Jones 2/14/1993 DBA 9002 110 Basic Web Desi Create and maintain HTML and CSS documents. 8/9/1999 4 2345 Johnny Jones 2/14/1993 DBA 9002 180 Advanced Datal Manage Database Server Clusters. 2/14/1993 5 3373 Franklin Johnson 3/15/2000 Purchasing Agent 9002 120 Advanced Spre: Use of advanced functions, user-defined functi 6/20/2007 6 4893 Patricia Richards 6/11/2002 DBA 9002 180 Advanced Datal Manage Database Server Clusters. 6/11/2002 7 4893 Patricia Richards 6/11/2002 DBA 9002 220 Advanced Datal Use of advanced data manipulation methods fo 9/20/2008 8 6234 Jasmine Patel 8/10/2003 Programmer 9002 110 Basic Web Desig Create and maintain HTML and CSS documents. 8/10/2003 9 6234 Jasmine Patel 8/10/2003 Programmer 9002 200 Advanced C# Pr Create multi-tier applications using multi-threa 8/10/2003 10 6234 Jasmine Patel 8/10/2003 Programmer 9002 210 Basic Database I Create simple data retrieval and manipulations 1/29/2008 11 8273 Marco Bienz 7/28/2004 Analyst 13932 110 Basic Web Desig Create and maintain HTML and CSS documents. 3/8/2005 12 8273 Marco Bienz 7/28/2004 Analyst 13932 190 Advance Proces Evaluate and Redesign cross-functional interna 8/19/2008 13 9002 Ben Joiner 5/20/2008 Clerk 14311 110 Basic Web Desig Create and maintain HTML and CSS documents. 5/16/2009 14 9002 Ben Joiner 5/20/2008 Clerk 14311 120 Advanced Spre: Use of advanced functions, user-defined functi 5/16/2009 15 9283 Juan Chavez 7/4/2008 Clerk 13932 16 9382 Jessica Johnson 8/2/2008 Database Programmer 13932 140 Basic Database I Create simple data models. 8/2/2008 17 9382 Jessica Johnson 8/2/2008 Database Programmer 13932 210 Basic Database I Create simple data retrieval and manipulations 8/2/2008 18 9382 Jessica Johnson 8/2/2008 Database Programmer 13932 220 Advanced Datat Use of advanced data manipulation methods fo 5/1/2009 19 10282 Amanda Richardson 4/11/2009 Clerk 13932 20 13383 Raymond Matthews 3/12/2010 Programmer 9002 170 Basic C# Prograr Create single-tier data aware modules. 3/12/2010 21 13567 Robert Almond 9/30/2010 Analyst 130 Basic Process M Create core business process models using stan 9/30/2010 22 13567 Robert Almond 9/30/2010 Analyst 140 Basic Database Create simple data models. 5/23/2011 23 13932 Megan Lee 9/29/2011 Programmer 14311 24 14311 Lee Duong 9/1/2012 Programmer 110 Basic Web Desig Create and maintain HTML and CSS documents. 9/1/2012 25 150 Master Databas Create integrated trigger and procedure packages for a distributed environment. 26 160 Basic Spreadshe Create single tab worksheets with basic formulas OrderSummary EmployeeSkill Instructions File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing X R27 X Cherry A B DE F G H 1 OrderID OrderDate Orderl Employ Custon CustomerName CustomerAddress CustomerCity 2 1001 10/21/2010 1 2345 1 Contemporary Casuals 1355 S Hines Blvd Gainesville 3 1001 10/21/2010 2 2345 1 Contemporary Casuals 1355 S Hines Blvd Gainesville 4 1001 10/21/2010 3 2345 1 Contemporary Casuals 1355 S Hines Blvd Gainesville 5 1002 10/21/2010 1 6324 8 California Classics 816 Peach Rd. Santa Clara 6 1003 10/22/2010 1 4893 15 Mountain Scenes 4132 Main Street Ogden 7 1004 10/22/2010 1 8273 5 Impressions 5585 Westcott Ct. . Sacramento 8 1004 10/22/2010 2 8273 5 Impressions 5585 Westcott Ct. Sacramento 9 1005 10/24/2010 1 9283 3 Home Furnishings 1900 Allard Ave. Albany 10 1006 10/24/2010 1 9283 2 Value Furniture 15145 S.W. 17th St. Plano 11 1006 10/24/2010 2 9283 2 Value Furniture 15145 S.W. 17th St. Plano 12 1006 10/24/2010 3 9283 2 Value Furniture 15145 S.W. 17th St. Plano 13 1007 10/27/2010 1 6234 11 American Euro Lifestyles 2424 Missouri Ave N. Prospect Park 14 1007 10/27/2010 2 6234 11 American Euro Lifestyles 2424 Missouri Ave N. Prospect Park 15 1008 10/30/2010 1 10282 12 Battle Creek Furniture 345 Capitol Ave. SW Battle Creek 16 1008 10/30/2010 2 10282 12 Battle Creek Furniture 345 Capitol Ave. SW Battle Creek 17 1009 11/5/2010 1 8273 4 Eastern Furniture 1925 Beltline Rd. Carteret 18 1009 11/5/2010 2 8273 4 Eastern Furniture 1925 Beltline Rd. Carteret 19 1010 11/5/2010 1 13383 1 Contemporary Casuals 1355 S Hines Blvd Gainesville 20 7 Period Furniture 394 Rainbow Dr. Seattle 21 6 Furniture Gallery 325 Flatiron Dr. Boulder 22 9 Mand H Casual Furniture 3709 First Street Clearwater 23 10 Seminole Interiors 2400 Rocky Point Dr. Seminole 24 13 Heritage Furnishings 66789 College Ave. Carlisle 25 14 Kaneohe Homes 112 Kiowai St. Kaneohe 26 OrderSummary Employeeskill Instructions + Ready K M N Custom CustomerPo: OrderedQua ProductID Product Description ProductFinish Product Standard Pric FL 32601-2871 2 1 End Table Cherry $175.00 FL 32601-2871 2 2 Coffee Table Natural Ash $200.00 FL 32601-2871 1 4 Entertainment Cent Natural Maple $650.00 CA 96915-7754 5 3 Computer Desk Natural Ash $375.00 UT 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 2 8 Computer Desk Walnut $250.00 NY 12209-1125 4 4 Entertainment Cent Natural Maple $650.00 TX 75094-7743 1 4 Entertainment Cent Natural Maple $650.00 TX 75094-7743 2 5 Writers Desk $325.00 TX 75094-7743 2 7 Dining Table Natural Ash $800.00 NJ 07508-5621 3 1 End Table Cherry $175.00 NJ 07508-5621 2 2 Coffee Table Natural Ash $200.00 MI 49015-3401 3 3 Computer Desk Natural Ash $375.00 MI 49015-3401 3 8 Computer Desk Walnut $250.00 NJ 07008-3188 2 4 Entertainment Cent Natural Maple $650.00 NJ 07008-3188 3 7 Dining Table Natural Ash $800.00 FL 32601-2871 10 8 Computer Desk Walnut $250.00 WA 97954-5589 CO 80514-4432 FL 34620-2314 FL 34646-4423 PA 17013-8834 HI 96744-2537 !!! B 1 + 100% HW 3 Attached Files: 1S_3063_HW3.xlsx (16.482 KB) HW 3 allows you to implement the data models we learned so far to an actual database. Please review the two lectures for Chapter 4 before starting this assignment. This assignment requires you to build a relational database in MS Access based on the data included in the attached spreadsheet file (S_3063_HW3.xlsx). This spreadsheet contains two workbooks: An Order Summary workbook and a EmployeeSkill workbook. Create an MS Access file based on these two workbooks and name the Access file as HW3_LastName_First Name. Complete the following tasks (for your convenience, the same instructions are provided in the third workbook in the excel file): 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 Order Summary table with all columns). 7. Create a query named HW3_Query Orders&Customers to show the entire original Order Summary 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_ Supervised by9002 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. Note that all assignments are individual assignments and you should work on the assignment independently. Only the access file needs to be turned in. HW 3 Attached Files: 1S_3063_HW3.xlsx (16.482 KB) HW 3 allows you to implement the data models we learned so far to an actual database. Please review the two lectures for Chapter 4 before starting this assignment. This assignment requires you to build a relational database in MS Access based on the data included in the attached spreadsheet file (S_3063_HW3.xlsx). This spreadsheet contains two workbooks: An Order Summary workbook and a EmployeeSkill workbook. Create an MS Access file based on these two workbooks and name the Access file as HW3_LastName_First Name. Complete the following tasks (for your convenience, the same instructions are provided in the third workbook in the excel file): 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 Order Summary table with all columns). 7. Create a query named HW3_Query Orders&Customers to show the entire original Order Summary 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_ Supervised by9002 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. Note that all assignments are individual assignments and you should work on the assignment independently. Only the access file needs to be turned in. File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments i PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing X D29 X foc A B C D E F H K L 1 Employee_ID Employee_FNc Employee_LN: Employee_HireDat Employee_Title Supervisor_ID Skill_ID Skill_Name Skill_Description Certified_Date 2 2345 Johnny Jones 2/14/1993 DBA 9002 100 Basic Database i Create and manage database user accounts. 2/14/1993 3 2345 Johnny Jones 2/14/1993 DBA 9002 110 Basic Web Desi Create and maintain HTML and CSS documents. 8/9/1999 4 2345 Johnny Jones 2/14/1993 DBA 9002 180 Advanced Datal Manage Database Server Clusters. 2/14/1993 5 3373 Franklin Johnson 3/15/2000 Purchasing Agent 9002 120 Advanced Spre: Use of advanced functions, user-defined functi 6/20/2007 6 4893 Patricia Richards 6/11/2002 DBA 9002 180 Advanced Datal Manage Database Server Clusters. 6/11/2002 7 4893 Patricia Richards 6/11/2002 DBA 9002 220 Advanced Datal Use of advanced data manipulation methods fo 9/20/2008 8 6234 Jasmine Patel 8/10/2003 Programmer 9002 110 Basic Web Desig Create and maintain HTML and CSS documents. 8/10/2003 9 6234 Jasmine Patel 8/10/2003 Programmer 9002 200 Advanced C# Pr Create multi-tier applications using multi-threa 8/10/2003 10 6234 Jasmine Patel 8/10/2003 Programmer 9002 210 Basic Database I Create simple data retrieval and manipulations 1/29/2008 11 8273 Marco Bienz 7/28/2004 Analyst 13932 110 Basic Web Desig Create and maintain HTML and CSS documents. 3/8/2005 12 8273 Marco Bienz 7/28/2004 Analyst 13932 190 Advance Proces Evaluate and Redesign cross-functional interna 8/19/2008 13 9002 Ben Joiner 5/20/2008 Clerk 14311 110 Basic Web Desig Create and maintain HTML and CSS documents. 5/16/2009 14 9002 Ben Joiner 5/20/2008 Clerk 14311 120 Advanced Spre: Use of advanced functions, user-defined functi 5/16/2009 15 9283 Juan Chavez 7/4/2008 Clerk 13932 16 9382 Jessica Johnson 8/2/2008 Database Programmer 13932 140 Basic Database I Create simple data models. 8/2/2008 17 9382 Jessica Johnson 8/2/2008 Database Programmer 13932 210 Basic Database I Create simple data retrieval and manipulations 8/2/2008 18 9382 Jessica Johnson 8/2/2008 Database Programmer 13932 220 Advanced Datat Use of advanced data manipulation methods fo 5/1/2009 19 10282 Amanda Richardson 4/11/2009 Clerk 13932 20 13383 Raymond Matthews 3/12/2010 Programmer 9002 170 Basic C# Prograr Create single-tier data aware modules. 3/12/2010 21 13567 Robert Almond 9/30/2010 Analyst 130 Basic Process M Create core business process models using stan 9/30/2010 22 13567 Robert Almond 9/30/2010 Analyst 140 Basic Database Create simple data models. 5/23/2011 23 13932 Megan Lee 9/29/2011 Programmer 14311 24 14311 Lee Duong 9/1/2012 Programmer 110 Basic Web Desig Create and maintain HTML and CSS documents. 9/1/2012 25 150 Master Databas Create integrated trigger and procedure packages for a distributed environment. 26 160 Basic Spreadshe Create single tab worksheets with basic formulas OrderSummary EmployeeSkill Instructions File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing X R27 X Cherry A B DE F G H 1 OrderID OrderDate Orderl Employ Custon CustomerName CustomerAddress CustomerCity 2 1001 10/21/2010 1 2345 1 Contemporary Casuals 1355 S Hines Blvd Gainesville 3 1001 10/21/2010 2 2345 1 Contemporary Casuals 1355 S Hines Blvd Gainesville 4 1001 10/21/2010 3 2345 1 Contemporary Casuals 1355 S Hines Blvd Gainesville 5 1002 10/21/2010 1 6324 8 California Classics 816 Peach Rd. Santa Clara 6 1003 10/22/2010 1 4893 15 Mountain Scenes 4132 Main Street Ogden 7 1004 10/22/2010 1 8273 5 Impressions 5585 Westcott Ct. . Sacramento 8 1004 10/22/2010 2 8273 5 Impressions 5585 Westcott Ct. Sacramento 9 1005 10/24/2010 1 9283 3 Home Furnishings 1900 Allard Ave. Albany 10 1006 10/24/2010 1 9283 2 Value Furniture 15145 S.W. 17th St. Plano 11 1006 10/24/2010 2 9283 2 Value Furniture 15145 S.W. 17th St. Plano 12 1006 10/24/2010 3 9283 2 Value Furniture 15145 S.W. 17th St. Plano 13 1007 10/27/2010 1 6234 11 American Euro Lifestyles 2424 Missouri Ave N. Prospect Park 14 1007 10/27/2010 2 6234 11 American Euro Lifestyles 2424 Missouri Ave N. Prospect Park 15 1008 10/30/2010 1 10282 12 Battle Creek Furniture 345 Capitol Ave. SW Battle Creek 16 1008 10/30/2010 2 10282 12 Battle Creek Furniture 345 Capitol Ave. SW Battle Creek 17 1009 11/5/2010 1 8273 4 Eastern Furniture 1925 Beltline Rd. Carteret 18 1009 11/5/2010 2 8273 4 Eastern Furniture 1925 Beltline Rd. Carteret 19 1010 11/5/2010 1 13383 1 Contemporary Casuals 1355 S Hines Blvd Gainesville 20 7 Period Furniture 394 Rainbow Dr. Seattle 21 6 Furniture Gallery 325 Flatiron Dr. Boulder 22 9 Mand H Casual Furniture 3709 First Street Clearwater 23 10 Seminole Interiors 2400 Rocky Point Dr. Seminole 24 13 Heritage Furnishings 66789 College Ave. Carlisle 25 14 Kaneohe Homes 112 Kiowai St. Kaneohe 26 OrderSummary Employeeskill Instructions + Ready K M N Custom CustomerPo: OrderedQua ProductID Product Description ProductFinish Product Standard Pric FL 32601-2871 2 1 End Table Cherry $175.00 FL 32601-2871 2 2 Coffee Table Natural Ash $200.00 FL 32601-2871 1 4 Entertainment Cent Natural Maple $650.00 CA 96915-7754 5 3 Computer Desk Natural Ash $375.00 UT 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 2 8 Computer Desk Walnut $250.00 NY 12209-1125 4 4 Entertainment Cent Natural Maple $650.00 TX 75094-7743 1 4 Entertainment Cent Natural Maple $650.00 TX 75094-7743 2 5 Writers Desk $325.00 TX 75094-7743 2 7 Dining Table Natural Ash $800.00 NJ 07508-5621 3 1 End Table Cherry $175.00 NJ 07508-5621 2 2 Coffee Table Natural Ash $200.00 MI 49015-3401 3 3 Computer Desk Natural Ash $375.00 MI 49015-3401 3 8 Computer Desk Walnut $250.00 NJ 07008-3188 2 4 Entertainment Cent Natural Maple $650.00 NJ 07008-3188 3 7 Dining Table Natural Ash $800.00 FL 32601-2871 10 8 Computer Desk Walnut $250.00 WA 97954-5589 CO 80514-4432 FL 34620-2314 FL 34646-4423 PA 17013-8834 HI 96744-2537 !!! B 1 + 100%
