This approach will be slightly different than what we have done so far in that you will be reverse engineering a database from an existing data set. This is much more common in a business setting than designing a database from scratch with an ERD Using Access, create each of the five tables displayed in the images at the end of this document. Answer the following questions in a Word Document 1. 2. For each relationship, identify which table is a Parent tables and which table is a child table? Are there any linking tables? If so, which ones? Identify all of the primary keys and foreign keys for each table. a. b. c. Create the appropriate relationships between the tables in Access. This should be based on your analysis the analysis you completed for question 2 3. 4. Enter the data that currently resides in the data set. 5. Write a query that displays the name and address for each customer's sales representative. This should combine the SLSREP and the CUSTOMER tables, and reports CUSTNUM, CUSTNAME, SLSRNAME and SLSRADDR information 6. Construct another query which reports the same information as in number 4, but now only for sales representative 3 Construct a query that shows exactly what each customer has ordered. The information should include customer number, name, description of each item, quantity and price of each item and the total price (which is the quantity times the quote_price) Create a form that allows for easy creation of new customers 7. 8. Existing Data Set Order ORDNUM ORDDATE CUSTNUM 12489 03/14/02 12490 03/14/02 12491 03/16/02 1249203/16/02 12493 03/17/02 12494 03/20/02 12495 03/20/02 124 311 412 256 522 124 522 Part PARTNUM PARTDESC QONHAND REORDQ AX12 AZ52 BA74 BH22 BT04 BZ66 CA14 CB03 CX11 CZ81 IRON SKATES BASEBALL TOASTER STOVE WASHER SKILLET BIKE MIXER WEIGHTS CLASS WRHSNUM LISTPRCE 17.95 24.95 4.95 34.95 2 402.99 3 311.95 19.95 187.50 57.95 2 108.99 104 20 40 95 80 HW 10 SG 20 SG 50 HW 10 AP 40 AP 2 HW 40 SG 50 HW 50 SG 2 52 2 112 208 Customer CUSTNUM CUSTNAME CUSTADDR BEGBAL CREDLIM SLSRNUM 124ADAMS, SALLY481 OAK, LANSING, MI 256SAMUELS, ANN 215 PETE, GRANT, MI 311 366 ADAMS, SALLY16 ELM, LANSING, MI 405WILLIAMS, AL 519 WATSON, GRANT, MI 201.75 412ROBERTS, JUDY481 OAK,LANSING, MI 522NELSON, MARY 108 PINE, ADA, MI 567 BAKER, JOE808 RIDGE, HARPER, MI 201.20 622MARTIN, DAN 500 800 300 1000 800 500 800 300 500 418.75 10.75 320.75 908.75 3 DANIELS, TOM 914 CHERRY,KENT, M 6 3 12 215.25 49.50 12 6 3 419 CHIP, GRANT, MI 575.50 PartOrd ORDNUM PARTNUM QUANTITY QUOTPRCE 14.95 402.99 311.95 175.00 22.95 22.95 4.95 175.00 402.99 108.99 12489 12490 12490 12491 12492 12493 12493 12493 12494 12495 AX12 BT04 BZ66 CB03 CX11 4 BA74 CB03 BTO4 CZ81 4 2 Sales Rep SLSRNUM SLSRNAME SLSRADDR JONES, MARY123 MAIN, GRANT, MI SMITH, WILLIAM 102 RAYMOND, ADA, MI BROWN, SAM TOTCOMM COMMRATE 0.05 0.05 0.07 2150 4912.5 2150 12 419 HARPER, LANSING, MI This approach will be slightly different than what we have done so far in that you will be reverse engineering a database from an existing data set. This is much more common in a business setting than designing a database from scratch with an ERD Using Access, create each of the five tables displayed in the images at the end of this document. Answer the following questions in a Word Document 1. 2. For each relationship, identify which table is a Parent tables and which table is a child table? Are there any linking tables? If so, which ones? Identify all of the primary keys and foreign keys for each table. a. b. c. Create the appropriate relationships between the tables in Access. This should be based on your analysis the analysis you completed for question 2 3. 4. Enter the data that currently resides in the data set. 5. Write a query that displays the name and address for each customer's sales representative. This should combine the SLSREP and the CUSTOMER tables, and reports CUSTNUM, CUSTNAME, SLSRNAME and SLSRADDR information 6. Construct another query which reports the same information as in number 4, but now only for sales representative 3 Construct a query that shows exactly what each customer has ordered. The information should include customer number, name, description of each item, quantity and price of each item and the total price (which is the quantity times the quote_price) Create a form that allows for easy creation of new customers 7. 8. Existing Data Set Order ORDNUM ORDDATE CUSTNUM 12489 03/14/02 12490 03/14/02 12491 03/16/02 1249203/16/02 12493 03/17/02 12494 03/20/02 12495 03/20/02 124 311 412 256 522 124 522 Part PARTNUM PARTDESC QONHAND REORDQ AX12 AZ52 BA74 BH22 BT04 BZ66 CA14 CB03 CX11 CZ81 IRON SKATES BASEBALL TOASTER STOVE WASHER SKILLET BIKE MIXER WEIGHTS CLASS WRHSNUM LISTPRCE 17.95 24.95 4.95 34.95 2 402.99 3 311.95 19.95 187.50 57.95 2 108.99 104 20 40 95 80 HW 10 SG 20 SG 50 HW 10 AP 40 AP 2 HW 40 SG 50 HW 50 SG 2 52 2 112 208 Customer CUSTNUM CUSTNAME CUSTADDR BEGBAL CREDLIM SLSRNUM 124ADAMS, SALLY481 OAK, LANSING, MI 256SAMUELS, ANN 215 PETE, GRANT, MI 311 366 ADAMS, SALLY16 ELM, LANSING, MI 405WILLIAMS, AL 519 WATSON, GRANT, MI 201.75 412ROBERTS, JUDY481 OAK,LANSING, MI 522NELSON, MARY 108 PINE, ADA, MI 567 BAKER, JOE808 RIDGE, HARPER, MI 201.20 622MARTIN, DAN 500 800 300 1000 800 500 800 300 500 418.75 10.75 320.75 908.75 3 DANIELS, TOM 914 CHERRY,KENT, M 6 3 12 215.25 49.50 12 6 3 419 CHIP, GRANT, MI 575.50 PartOrd ORDNUM PARTNUM QUANTITY QUOTPRCE 14.95 402.99 311.95 175.00 22.95 22.95 4.95 175.00 402.99 108.99 12489 12490 12490 12491 12492 12493 12493 12493 12494 12495 AX12 BT04 BZ66 CB03 CX11 4 BA74 CB03 BTO4 CZ81 4 2 Sales Rep SLSRNUM SLSRNAME SLSRADDR JONES, MARY123 MAIN, GRANT, MI SMITH, WILLIAM 102 RAYMOND, ADA, MI BROWN, SAM TOTCOMM COMMRATE 0.05 0.05 0.07 2150 4912.5 2150 12 419 HARPER, LANSING, MI