Question 2: Triggers and Stored Procedures Download the file named “assignment5_database.sql” from D2L. Run the file from MySQL command window using the command (assuming the file is stored under c:\\): mysql> source c:\\assignment5_database.sql After the file completes, the tables shown above are created and populated with the shown data. Familiarize yourself with the database before starting to write your queries. Once you are familiar with the data, start working on the following questions. Answer the following questions based on the above database: Create a view named “Atlanta_Charter_Crew_V” that includes the following “For each charter of aircraft to Atlanta (ATL), print the charter date, charter distance, and the corresponding crew (employee) number, last name, first name, and crew job”. Your answer should include both the SQL statement for view creation along with the contents of the view (you get the contents of the view by running the command select * from Atlanta_Charter_Crew_V;). Modify the MODEL table to add the following attribute and insert the values shown in the following table. (Note: use ALTER TABLE and UPDATE commands.) Attribute nameAttribute DescriptionAttribute typeAttribute ValuesMOD_TOTAL_ WEIGHTWeight of each modelNumeric12,500 for C-90A7,300 for PA23-25019,770 for PA31-350 Create a trigger named trg_charter_time that will automatically update the AIRCRAFT table when a new CHARTER row is added. Use the CHARTER table’s CHAR_HOURS_FLOWN to update the AIRCRAFT table’s AC_TTAF, AC_TTEL, and AC_TTER values. The meaning for the AIRCRAFT table columns are as follows: AC_TTAF: Total time on the air frame (before maintenance)AC_TTEL: Total time on the left engine (before maintenance, also track single engine)AC_TTER: Total time on the right engine (before maintenance) So in the trigger, you need to decrease all of them by the number of CHAR_HOUR_FLOWN Create a trigger named trg_cust_charge that will automatically update the CUSTOMER table’s CUS_BALANCE when a new CHARTER row is added. Use the CHARTER table’s CHAR_TOT_CHG as the update source (Assume that all charter charges are charged to the customer balance.) In addition to the CHAR_TOT_CHG, add a $5 service charge for every 50 miles flown. Create a stored procedure to update an Aircraft’s time till maintenance. Procedure takes the Aircraft number (ac_number) as a parameter. The procedure then resets the aircraft’s attributes: ac_ttaf to 5000, ac_ttel to 7775 and ac_tter to 8805 (right engines last longer, less stress on take offs). Create a stored procedure that will take a Pilot’s number and percentage, then update the corresponding pilot’s annual salary by the input percentage (increase the annual salary, so you are giving the pilot a raise). Hint: you may have to do some additional work (alter table) on this one to get the table correct. Database name: Ch08_AviaCoTable name: CHARTERTable name: EARNEDRATINGCHAR TRIP CHAR DATE AC_NUMBER CHAR DESTINATION CHAR DISTANCE CHAR HOURS FLOWN CHAR HOURS WAIT CHAR FUEL GALLONS CHAR OL_QTS CUS CODE100115.122354,110016100142.10001 05-Feb-08 2289L906726EMP NUM RTG CODE EARNRTG DATE1.610002 05-Feb-08 2778VBNA7.886-99381GNV157415-Dec-0508-Nov-9310003 05-Feb-08 4278Y972101 CFI472610010004 06-Feb-08 1484PSTL100111001710012101 NSTR10235.735ATL23-Jun-9421-Apr-9310005 06-Feb-08 2289L101 MEL4722652117.110006 06-Feb-08 4278Y101 SELus343.42157410007 06-Feb-08 2778V62104 NSTRANO10014140.664496 SISAIGNV29-Jan-9710008 07-Feb-08 1484P10017104 MEL66S279.723499621574104 SEL12-Mar-95910010012768Z 00-9920 600032טנרBNA86635218-Nov-9717-Apr-9512-Aug-9523-Sep-9420-Dec-9510010 07-Feb-08 4278Y5366410011 07-Feb-08 1484P1.42215.110010105 NSTR10012 08-Feb-08 2778VMOB8844.8105 MEL45174310011SAIATL10013 08-Feb-08 4278Y13S SOL106 INSTR6.12.110017966164510014 09-Feb-08 4278Y6.7459.51001610015 09-Feb-08 2289L02-Apr-9610-Mar-942.ANO1001110016 09-Feb-08 2778V3121567 2106 SEL1001410017AOH105.5usSAI10017 10-Feb-08 1484P3.105-Nov-98805167.410018 10-Feb-08 4278Y3.845LO 601109 INSTRTable name: CREWTable name: CUSTOMER96 T15-Mar-97109 MELCHAR TRIP EMP NUM CREW JOBCUS CODE CUS LNAME CUS FNAME CUS NTIAL CUS AREACODE CUS_PHONE CUS BALANCE109 SEL05-Feb-96104 Plot844-2573109 SES12-May-96LO001000210010 RamasAlfredA615000000896.54101 Plot10011 Dunne713894-123810003105 Plot10012 SmithKathy615894-2285W.Table name: RATING109 Copilot10013 Olowski615894-21801285.19PO0010005106 Plot673.211014.5610014 OrlandoMyron615222-1672RTG CODERTG NAME101 Plot10015 OBrian713442-3381Certified Flight Instructor109 Plot9000Jarmes615297-1228000Certified Flight Instructor, Instrument104 PlotUMOg 9100290-2556LO00110017 Wiams615NSTRInstrumert200090001105 Copilot000000453.9810018 Farriss713382-7185Multiengine LandSingle Engine, LandSingle Engine, Sea106 PlotMEL105 Plot10019 SmithOlette6156000108 PlotTable name: EMPLOYEESES10011101 Plot10011104 CopilatEMP NUM EMP_ TITLE EMP LNAME EMP FNAME EMPNTIAL EMP DOB EMP HRE DATE15-Mar-198725-Apr-1988 Table name: MODEL20-Dec-199210012101 Plot100 Mr.15-Jun-194210013105 Plot101 Ms.Lewis19-Mar-196510014epuon106 Plot102 Mr,VandamRhett14-Nov-1958MOD CODE MOD MANUFACTURERMOD NAME MOD SEATS MOD CHG_MILE2.6710015101 Copilot28-Aug-200520-Od-199608-Jan-2006 PA31-350 Piper16-Oct-1974sauorC-90ABeechcraft10015104 Piot104 Mr.John08-Nov-197110016105 CoplotPA23-250 PiperAztecD.K105 Mr.WiliamsRobert14-Mar-197510016Navajo Chiettain235Pid 601106 Mrs.Jeanine12-Feb-196805-Jan-199110017107 Mr.Diante21-Aug-197402--1996104 Copilot105 Plot18-Nov-199414-Apr-199101-Dec-1992108 Mr.Wiesenbach Pad14-Feb-196610018R.109 Ms.TravisEizabeth18-un-1961110 Mrs.Genkazi19-May-1970Table name: AIRCRAFTTable name: PILOTAC NUMBER MOD CODE AC TTAF AC TTEL AC_TTEREMP NUM PIL LCENSEPIL RATINGSPL MED TYPE PL_MED_DATE PL PT135 DATE1484PPA23-2501833.11833.1101.8101 ATPATP/SELMELinstriÇFI11-Jan-0817 Jan-0802-Jan-0802-Feb-0820-Jan-081.768272778V6891123.41513.1C-90A4243.8104 ATPATP/SELMELinstr1.COMM/SELMELinstr/CFI18-Dec-07PA31-3507992.9789.5105 COM4278YPA31-35080-us02147.3622.1243.2106 COMCOMMISELMELAnstr10-Dec-07ATPISELMELISESAnstr/CFI 115-Jan-08WOO 60190-ue

Question 2: Triggers and Stored Procedures Download the file named “assignment5_database.sql” from D2L. Run the file from MySQL command window using the command (assuming the file is stored under c:\\): mysql> source c:\\assignment5_database.sql After the file completes, the tables shown above are created and populated with the shown data. Familiarize yourself with the database before starting to write your queries. Once you are familiar with the data, start working on the following questions. Answer the following questions based on the above database: Create a view named “Atlanta_Charter_Crew_V” that includes the following “For each charter of aircraft to Atlanta (ATL), print the charter date, charter distance, and the corresponding crew (employee) number, last name, first name, and crew job”. Your answer should include both the SQL statement for view creation along with the contents of the view (you get the contents of the view by running the command select * from Atlanta_Charter_Crew_V;). Modify the MODEL table to add the following attribute and insert the values shown in the following table. (Note: use ALTER TABLE and UPDATE commands.) Attribute name Attribute Description Attribute type Attribute Values MOD_TOTAL_ WEIGHT Weight of each model Numeric 12,500 for C-90A 7,300 for PA23-250 19,770 for PA31-350 Create a trigger named trg_charter_time that will automatically update the AIRCRAFT table when a new CHARTER row is added. Use the CHARTER table’s CHAR_HOURS_FLOWN to update the AIRCRAFT table’s AC_TTAF, AC_TTEL, and AC_TTER values. The meaning for the AIRCRAFT table columns are as follows: AC_TTAF: Total time on the air frame (before maintenance) AC_TTEL: Total time on the left engine (before maintenance, also track single engine) AC_TTER: Total time on the right engine (before maintenance) So in the trigger, you need to decrease all of them by the number of CHAR_HOUR_FLOWN Create a trigger named trg_cust_charge that will automatically update the CUSTOMER table’s CUS_BALANCE when a new CHARTER row is added. Use the CHARTER table’s CHAR_TOT_CHG as the update source (Assume that all charter charges are charged to the customer balance.) In addition to the CHAR_TOT_CHG, add a $5 service charge for every 50 miles flown. Create a stored procedure to update an Aircraft’s time till maintenance. Procedure takes the Aircraft number (ac_number) as a parameter. The procedure then resets the aircraft’s attributes: ac_ttaf to 5000, ac_ttel to 7775 and ac_tter to 8805 (right engines last longer, less stress on take offs). Create a stored procedure that will take a Pilot’s number and percentage, then update the corresponding pilot’s annual salary by the input percentage (increase the annual salary, so you are giving the pilot a raise). Hint: you may have to do some additional work (alter table) on this one to get the table correct.