1. Using Figure P7.1 in the text as a guide, write the SQL/DDL to create the Job and EMPLOYEE tables Note the cardinality requirements for the relationship indicates a foreign key and values are required. Write the SQL commands to add the first three records to the JOB table and the first three records to the EMPLOYEE table. Consider using your personal database (mysql> use ;) on cs1103.cs.unb.ca to test your work. 2. What happens if you create the EMPLOYEE table before the JOB table? Why? 3. What happens if you add an employee record with a JOB _CODE not present in the JOB table? Why? FIGURE P8.1 THE CHOP_CONSTRUCTCO DATABASE Database name: Ch08_ConstructCo Relational diagram aos OB_CODE JOB DESCRIPTION JOB CHG HOUR OR_LAST UPDATE ASSIGNMENT ASSICNNUM ASSION DATE MONUM EMFNUM ASSIGLIOR ASSIGN.CHGH ASSICN HOURS ASSIGN CHARGE FONNUM Maria EMPLOYEE 9 EMPTUM EMD UNIME EM FRAME EMP INITIAL EMP HIREDATE 08.CODE EM YEARS Table name: JOB JOB_CODE JOB DESCRIPTION JOB_CHO_HOUR JOB LAST UPDATE 500 Programmer 35.75 20-Nov-17 501 Systems Analyst 95.75 20-Nov-17 - 502 Database Designer 125 00 24-Mar-18 503 Electrical Engineer 84 50 20-Nov-17 504 Mechanical Engineer 67 90 20-Nov-17 505 Ovi Engineer 55 78 20-Nov.17 506 Clerical Support 25 87 20-Nov-17 507 DSS Analyst 45.95 20.Nov.17 Applications Designer 48.10 24. Mar 18 509 Bio Technician 3455 20-Nov-17 510 General Susport 1836 20-Nov-17 Table name: EMPLOYEE EMP_NUM EMP_LNAME EMP_FNAME EMP_NTIAL EMP_HREDATE JOB_CODE EMP_YEARS 101 News John G 08-Nov.00 502 12 102 Senior David H 12-4-89 501 23 103 Arbough June E 01-Dec-96 500 18 104 Ramoras Anne K 15-Nov-87 501 25 105 Johnson Alice 01-Feb-93 502 19 PROIEC 108 Smithfield William 22-Jun-04 500 8 8 107 Alonzo D PROJ NAME 10-Oct-93 500 19 FROL VALUE 108 Washington Ralph B 22-Aug-91 501 21 PROJ BALANCE 109 Smith Larry w EMP.NUM 18-Jul-97 501 15 110 Olenko Gerald A 11-Dec-95 505 17 111 Wabash Geoft B 04-Apr-91 506 21 112 Smithson Darlene M 23-Oct-94 507 18 113 Joenbrood Delbert K 15-Nov-96 508 16 114 Jones Annelise 20-Aug-93 508 19 115 Bawangi Travis B 25-Jan-92 501 20 116 Pratt Gerald L 05-Mar-97 510 15 117 Williamson Angie H 19-Jun-96 509 16 118 Frommer James J 04-Jan-05 510 7 Table name: ASSIGNMENT ASSION_NUM ASSION_DATE PROJ_NUM EMP_NUM ASSION_JOE ASSION_CHO_IRASSION_HOURS ASSION_CHARGE 1001 22.Mw.18 18 103 8450 35 29575 1002 22-M-18 22 117 509 42 145.11 1003 22 M18 18 117 509 34.55 23 69.10 10044 22-M 18 18 103 SUS 34.50 190.55 1005 22 Mr.182 106 501 98.75 22 212.85 1006 22-Mar-1022 104 501 36.75 42 40630 1007 22-My-18 28 113 508 50.75 33 1925 1008 22. Mw.18 18 103 503 84.50 02 7605 1009 23-Mar-18 15 115 501 96.75 55 54180 1010 23.M.18 15 117 509 34.55 2.1 82.92 1011 23. M-10 25 105 502 105.00 43 45150 503 3455 508 Table name: PROJECT PROJ_NUM PROJ_NAME PROJ_VALUE PROJ BALANCE EMP_NUM 15 16 7 500 501 502 503 504 505 506 507 508 509 510 Programmer Systems Analyst Database Designer Electrical Engineer Mechanical Engineer avi Engineer Cerical Support DSS Analyst Applications Designer Bo Technician General Support 35.75 95.75 12500 84.50 67 90 55.78 25 87 45.95 48.10 34 35 18 36 20-Nov-17 20-Nov-17 24-Mar-18 20-Nov-17 20-Nov-17 20-Nov-17 20-Nov-17 20-Nov-17 24-Mar-18 20-Nov-17 20-Nov-17 69.10 Table name: PROJECT PROJ_NUM PROJ_NAME PROJ_VALUE 15 Evergreen 1453500.00 18 Amber Wave 3500500.00 22 Rolling Tide 805000.00 25 Starflight 2650500.00 116 Pratt Gerald 05-Mar-97 510 117 Williamson Angie 19-Jun-96 509 118 Frommer James J 04-Jan-05 510 Table name: ASSIGNMENT ASSION_NUM ASSION_DATE PROJ_NUM EMP_NUM ASSION_JOE ASSION_CHO_HR ASSION_HOURS ASSION_CHARGE 1001 22-Mar-18 18 103 503 84.50 3.5 295.75 1002 22-Mar-18 22 117 509 34.55 4.2 145.11 1003 22-Mar 18 18 117 509 34.55 2.0 1004 22-Mar-18 18 103 SOS 84.50 5.3 498.55 1005 22-Mar-18 25 108 501 96.75 2.2 212.85 1006 22-Mor 10 22 104 501 36.75 406.36 1007 22-Mar-18 25 113 508 50.75 3.3 192.85 1008 22-Mar-18 18 103 503 84.50 0.9 76.05 1009 23-Mar-18 15 115 501 98.75 5.5 541.80 1010 23-Mar-18 15 117 509 34.55 2.1 82.92 1011 23-Mar-18 25 105 502 105.00 4.3 451.50 1012 23-Mer 18 18 108 501 96.75 3.1 328.95 1013 23 Mar 18 25 115 501 96.75 2.0 193.50 1014 23-Mar-18 22 104 501 96.75 2.3 270.90 23-Mar-18 15 103 503 84.50 6.1 515.45 1016 23-Mar-18 22 105 502 10500 4.7 493.50 1017 23-Mr-18 18 117 509 3455 3.3 131.29 1018 23-Mar-18 25 117 509 34.55 2.2 76.01 1019 24-Mar-18 25 104 501 110.50 4.3 541.45 1020 24-Mar-18 15 101 125.00 3.1 387.50 1071 24.M.18 22 108 501 11050 2.7 298 35 1022 24-Mar-18 22 115 501 110.50 4.9 541.45 1023 24-Mar-18 22 105 502 12500 3.5 43750 1024 24-Mar-18 15 103 84.50 3.3 278.85 1025 24-M-18 18 117 509 3455 4.2 145.11 PROJ_BALANCE EMP_NUM 1002350.00 103 2110346.00 108 500345.20 102 2309880.00 107 1015 502 503