1. Create a trigger named trg_char_hours that will automatically update the AIRCRAFT table when a new CHARTER row is added. Use the CHARTER tables CHAR_HOURS_FLOWN to update the AIRCRAFT tables 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

AC_TTEL: Total time on the left engine (Also used to record single engine hours) AC_TTER: Total time on the right engine.

So in the trigger, you need to increase all of them with value of CHAR_HOURS_FLOWN

2. Create a stored procedure to update model charge per mile attribute. Procedure takes the model number as a parameter. The procedure increases the charge for this model by 20%.

Database name: Ch08 AviaCo Table name: CHARTER CHAR TRIP CHAR DATE AC NUMBER CHAR DESTINATION CHAR DISTANCE CHAR HOURS FLOWN CHAR HOURS WAIT CHAR FUEL GALLONS CHAR OIL OTS CUS CODE 10011 Table name: EARNEDRATINOG 10001 05-Feb-08 2289L 10002 05-Feb-08 2778V 10003 05-Feb-08 4278Y 1000406-Feb-08 1484P 10005 06-Feb-08 2289L 10006 06-Feb-08 4278Y 10007 06-Feb-08 2778V 10008 07-Feb-08 1484P 000907-Feb-08 2289L 10010 07-Feb-08 4278Y 10011 07-Feb-08 1484P 10012 08-Feb-08 2778V 10013 08-Feb-08 4278 10014 09-Feb-08 4278 10015 09-Feb-08 2289L 10016 09-Feb-08 2778V 10017 10-Feb-08 1484P 10018 10-Feb-08 4278Y ENA 10016 EMP NUM RTG CODE EARNRTG DATE 10014 10019 18-Feb-98 1574 644 1574 10017 10012 10014 10017 10016 21-Apr-93 15-Jul-96 TYS 140.6 104 INSTR 18-Nov-97 17-Apr-95 TYS 644 936 1645 10017 10016 106STR 105.5 10-Mar-94 10014 10017 TYS Table name: CREW Table name: CUSTOMER 15-Mar-97 05-Feb-96 CHAR TRIP EMP NUM | CREV Me | | CUS CODE | CUS LNAME | CUS FNAME CUSINTAL | CUS AREACODE | CUS PHONE | CUS BALANCE 10010 Ramas 10011 Dunne 10012 Smith 0013 Olowski 10014 Orlando 10015 O'Brian 10016 Brown 10017 iliams 10018 Farriss 10019 Smith 894-1238 1285.19 Table name: RATING RTG NAME 894-2180 Myron 1014.56 CF Certified Flight Instructor Certified Flight Instructor, Instrument 10007 Copilot 382-7185 809 Single Engine, Land Single Engine, Sea Table name: EMPLOYEE 10011 104 Copilot EMP NUM EMP TITLE EMP LNAME EMP FNAME EMP INTIAL EMP D0 EMP HIRE DATE 10012 10013 10014 100 Mr 15-Jun-1942 15-Mar-1987 19-Mar-1965 25-Apr-1988 Table name: MODEL 14-Nov-1958 102 Mr 20-Dec-1992 MOD-CODE! MOD-MANUFACTURER MOO MOD- EATS! MOD-CHG Copilot 16-Oct-1974 28-Aug-2005Aeechcraf 2.67 105 Mr 106 Mrs 20-Oct-1996 PA23-250 Piper 08-Jan-2006 PA31-350 Piper Navajo Chieftain 12-Feb-196805-Jan-1991 21-Aug-197402-Jul-1996 14-Feb-1966 18-Jun-1961 Duzak 10017 10018 10018 104 Copilot 108 Mr 18-Nov-1994 19-May-1970 01-Dec-1992 Table name: AIRCRAFT Table name: PILOT AC UMBER | MOD CODE | AC-TTAF | AC-TTEL | AC-TTER | | EMP_AUM | PIL LCENSE | PL RATINGS |PL-MED-TYPE | PL-MED DATE!PL PT135 DATE PA23-250 ATPISELMELAnstriCFI ATPISELMELAnstr COMMISELMELAnstr CF 2 4243.8 PA31-350 PA31-350 1513.1 21473 02-Feb-08 ATPISELMELISESAnstrICFI