Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
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 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.

Database System Concepts
Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
See similar textbooks
icon
Related questions
Question

Please create this two trigger and write down as Mysql statement

  • 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.
Table name: CHARTER Database name: Ch08_AviaCo CHAR_TRIP CHAR DATE AC_NUMBER CHAR DESTINATION CHAR DISTANCE CHAR HOURS FLOWN CHAR HOURS WAIT CHAR_ FUEL GALLONS CHAR OIL QTS CUS_CODE 05-Feb-08 2289L. Table name: EARNEDRATING 906 320 10001 ATL 5.1 22 354.1 1 10011 10002 05-Feb-08 2778V BNA 1.6 726 10016 EMP_NUM RTG_CODE EARNRTG_DATE 10003 05-Feb-08 4278Y GNV 1574 7.8 339.8 10014 101 CFI 18-Feb-98 10004 06-Feb-08 1484P STL 472 2.9 4.9 97 2 10019 101 CFI 15-Dec-05 10005 06-Feb-08 2289L. ATL 1023 5.7 3.5 397.7 2 10011 101 INSTR 08-Nov-93 10006 06-Feb-08 4278Y STL 472 26 52 117.1 10017 101 MEL 23-Jun-94 21-Арг-93 15--96 10007 06-Feb-08 2778V GNV 1574 7.9 348.4 2 10012 101 SEL 10008 07-Feb-08 1484P TYS 644 4.1 140.6 10014 104 INSTR 10009 07-Feb-08 2289L GNV 1574 6.6 23.4 4599 10017 104 MEL 29-Jan-97 12-Mar-95 104 SEL 105 CFI 10010 07-Feb-08 4278Y ATL 998 6.2 3.2 279.7 10016 10011 07-Feb-08 1484P BNA 352 1.9 5.3 66.4 10012 18-Nov-97 10012 08-Feb-08 2778V MOB 884 4.8 4.2 215.1 10010 105 INSTR 17-Apr-95 12-Aug-95 23-Sep-94 10013 08-Feb-08 4278Y TYS 644 3.9 4.5 1743 1 10011 105 MEL 10014 09-Feb-08 4278Y ATL 936 6.1 2.1 3026 10017 105 SEL 10015 09-Feb-08 2289L GNV 1645 6.7 4595 2 10016 106 INSTR 20-Dec-95 106 MEL 10016 09-Feb-08 2778V 10-Feb-08 1484P 02-Apr-96 10-Mar-94 MQY 312 1.5 67.2 10011 10017 STL 508 3.1 1055 10014 106 SEL 10018 10-Feb-08 4278Y TYS 644 3.8 4.5 167.4 10017 109 CFI 05-Nov-98 109 CFI 21-Jun-03 Table name: CREW Table name: CUSTOMER 23-Ju-96 15-Mar-97 109 INSTR 109 MEL CUs CODE CUS LNAME CUS_FNAME CUS_NITIAL CUS AREACODE CUS_PHONE CUS_BALANCE CHAR_TRIP 10001 EMP_NUM CREW_JOB 104 Plot 101 Plot 109 SEL 05-Feb-96 10010 Ramas Alfred A 615 844-2573 0.00 109 SES 12-May-96 10002 10011 Dunne Leona K 713 894-1238 0.00 10003 10003 10004 105 Plot 10012 Smith Kathy 615 894-2285 896.54 109 Coplot Table name: RATING 10013 Olowski Paul F 615 894-2180 1285.19 106 Plot RTG CODE 10014 Orlando 222-1672 RTG_NAME Myron 615 673.21 10005 101 Plot 10015 O'Brian Amy 713 442-3381 1014.56 CFI Certified Flight Instructor Certified Flight Instructor, Instrument 10006 109 Pilot 10016 Brown James 615 297-1228 00 10007 104 Plot CFI 10017 Williams George 615 290-2556 0.00 105 Copliot 106 Plot 10007 INSTR Instrument 10018 Farriss Anne 713 382-7185 0.00 10008 MEL Multiengine Land 10019 Smith Olette K 615 297-3809 453.98 10009 105 Plot SEL Single Engine, Land 10010 108 Plot Table name: EMPLOYEE SES Single Engine, Sea 10011 101 Plot EMP DOB 15-Jun-1942 19-Mar-1965 14-Nov-1958 EMP_NUM EMP_TITLE EMP LNAME EMP_FNAME EMP_INTIAL Kolmycz 10011 104 Coplot EMP HRE DATE 10012 101 Plot 100 Mr George D 15-Mar-1987 10013 105 Pilot 101 Ms. Lewis Rhonda G 25-Apr-1988 Table name: MODEL 10014 106 Plot 102 Mr. Vandam Rhett 20-Dec-1992 MOD_CODE MOD MANUFACTURER MOD_NAME MOD_SEATS MOD_CHO MILE 10015 101 Coplot 103 Ms. 28-Aug-2005 20-Oct-1996 Jones Anne 16-Oct-1974 10015 104 Plot C-90A Beechcraft KingAir 8 2.67 104 Mr. Lange John 08-Nov-1971 PA23-250 Piper 08-Jan-2006 PA31-350 Piper Aztec 6 1.93 10016 105 Copilot 109 Plot 105 Mr. Williams Robert D 14-Mar-1975 12-Feb-1968 21-Aug-1974 10016 Navajo Chieftain 10 2.35 106 Mrs Duzak Jeanine K 05-Jan-1991 10017 101 Plot 107 Mr. Diante Jorge D 02-Jul-1996 10018 104 Coplot 108 Mr. Wiesenbach Paul 14-Feb-1966 18-Nov-1994 10018 105 Pilot 18-Jun-1961 14-Apr-1991 01-Dec-1992 109 Ms Travis Elizabeth 110 Mrs. Genkazi Leighla 19-May-1970 Table name: AIRCRAFT AC_NUMBER MOD_CODE AC_TTAF Table name: PILOT EMP_NUM PIL_LICENSE AC_TTEL AC_TTER PL_MED_TYPE PL_MED_DATE PL_PT135_DATE 11-Jan-08 PIL_RATINGS 1484P PA23-250 1833.1 1833.1 101.8 101 ATP ATP/SELMELAnstr/CFI 1 20-Jan-08 2269L C-90A 4243.8 768.9 1123.4 104 ATP ATP/SELMELAnstr 1 18-Dec-07 17-Jan-03 2778V PA31-350 7992.9 1513.1 789.5 105 COM COMM/SELMELAnstr/CFI 2 05-Jan-08 02-Jan-08 4278Y PA31-350 2147.3 622.1 243.2 COMM/SELMELAnstr ATP/SELMELSESAnstr/CFI 1 106 COM 2 10-Dec-07 02-Feb-08 109 COM 22-Jan-08 15-Jan-08
Transcribed Image Text:Table name: CHARTER Database name: Ch08_AviaCo CHAR_TRIP CHAR DATE AC_NUMBER CHAR DESTINATION CHAR DISTANCE CHAR HOURS FLOWN CHAR HOURS WAIT CHAR_ FUEL GALLONS CHAR OIL QTS CUS_CODE 05-Feb-08 2289L. Table name: EARNEDRATING 906 320 10001 ATL 5.1 22 354.1 1 10011 10002 05-Feb-08 2778V BNA 1.6 726 10016 EMP_NUM RTG_CODE EARNRTG_DATE 10003 05-Feb-08 4278Y GNV 1574 7.8 339.8 10014 101 CFI 18-Feb-98 10004 06-Feb-08 1484P STL 472 2.9 4.9 97 2 10019 101 CFI 15-Dec-05 10005 06-Feb-08 2289L. ATL 1023 5.7 3.5 397.7 2 10011 101 INSTR 08-Nov-93 10006 06-Feb-08 4278Y STL 472 26 52 117.1 10017 101 MEL 23-Jun-94 21-Арг-93 15--96 10007 06-Feb-08 2778V GNV 1574 7.9 348.4 2 10012 101 SEL 10008 07-Feb-08 1484P TYS 644 4.1 140.6 10014 104 INSTR 10009 07-Feb-08 2289L GNV 1574 6.6 23.4 4599 10017 104 MEL 29-Jan-97 12-Mar-95 104 SEL 105 CFI 10010 07-Feb-08 4278Y ATL 998 6.2 3.2 279.7 10016 10011 07-Feb-08 1484P BNA 352 1.9 5.3 66.4 10012 18-Nov-97 10012 08-Feb-08 2778V MOB 884 4.8 4.2 215.1 10010 105 INSTR 17-Apr-95 12-Aug-95 23-Sep-94 10013 08-Feb-08 4278Y TYS 644 3.9 4.5 1743 1 10011 105 MEL 10014 09-Feb-08 4278Y ATL 936 6.1 2.1 3026 10017 105 SEL 10015 09-Feb-08 2289L GNV 1645 6.7 4595 2 10016 106 INSTR 20-Dec-95 106 MEL 10016 09-Feb-08 2778V 10-Feb-08 1484P 02-Apr-96 10-Mar-94 MQY 312 1.5 67.2 10011 10017 STL 508 3.1 1055 10014 106 SEL 10018 10-Feb-08 4278Y TYS 644 3.8 4.5 167.4 10017 109 CFI 05-Nov-98 109 CFI 21-Jun-03 Table name: CREW Table name: CUSTOMER 23-Ju-96 15-Mar-97 109 INSTR 109 MEL CUs CODE CUS LNAME CUS_FNAME CUS_NITIAL CUS AREACODE CUS_PHONE CUS_BALANCE CHAR_TRIP 10001 EMP_NUM CREW_JOB 104 Plot 101 Plot 109 SEL 05-Feb-96 10010 Ramas Alfred A 615 844-2573 0.00 109 SES 12-May-96 10002 10011 Dunne Leona K 713 894-1238 0.00 10003 10003 10004 105 Plot 10012 Smith Kathy 615 894-2285 896.54 109 Coplot Table name: RATING 10013 Olowski Paul F 615 894-2180 1285.19 106 Plot RTG CODE 10014 Orlando 222-1672 RTG_NAME Myron 615 673.21 10005 101 Plot 10015 O'Brian Amy 713 442-3381 1014.56 CFI Certified Flight Instructor Certified Flight Instructor, Instrument 10006 109 Pilot 10016 Brown James 615 297-1228 00 10007 104 Plot CFI 10017 Williams George 615 290-2556 0.00 105 Copliot 106 Plot 10007 INSTR Instrument 10018 Farriss Anne 713 382-7185 0.00 10008 MEL Multiengine Land 10019 Smith Olette K 615 297-3809 453.98 10009 105 Plot SEL Single Engine, Land 10010 108 Plot Table name: EMPLOYEE SES Single Engine, Sea 10011 101 Plot EMP DOB 15-Jun-1942 19-Mar-1965 14-Nov-1958 EMP_NUM EMP_TITLE EMP LNAME EMP_FNAME EMP_INTIAL Kolmycz 10011 104 Coplot EMP HRE DATE 10012 101 Plot 100 Mr George D 15-Mar-1987 10013 105 Pilot 101 Ms. Lewis Rhonda G 25-Apr-1988 Table name: MODEL 10014 106 Plot 102 Mr. Vandam Rhett 20-Dec-1992 MOD_CODE MOD MANUFACTURER MOD_NAME MOD_SEATS MOD_CHO MILE 10015 101 Coplot 103 Ms. 28-Aug-2005 20-Oct-1996 Jones Anne 16-Oct-1974 10015 104 Plot C-90A Beechcraft KingAir 8 2.67 104 Mr. Lange John 08-Nov-1971 PA23-250 Piper 08-Jan-2006 PA31-350 Piper Aztec 6 1.93 10016 105 Copilot 109 Plot 105 Mr. Williams Robert D 14-Mar-1975 12-Feb-1968 21-Aug-1974 10016 Navajo Chieftain 10 2.35 106 Mrs Duzak Jeanine K 05-Jan-1991 10017 101 Plot 107 Mr. Diante Jorge D 02-Jul-1996 10018 104 Coplot 108 Mr. Wiesenbach Paul 14-Feb-1966 18-Nov-1994 10018 105 Pilot 18-Jun-1961 14-Apr-1991 01-Dec-1992 109 Ms Travis Elizabeth 110 Mrs. Genkazi Leighla 19-May-1970 Table name: AIRCRAFT AC_NUMBER MOD_CODE AC_TTAF Table name: PILOT EMP_NUM PIL_LICENSE AC_TTEL AC_TTER PL_MED_TYPE PL_MED_DATE PL_PT135_DATE 11-Jan-08 PIL_RATINGS 1484P PA23-250 1833.1 1833.1 101.8 101 ATP ATP/SELMELAnstr/CFI 1 20-Jan-08 2269L C-90A 4243.8 768.9 1123.4 104 ATP ATP/SELMELAnstr 1 18-Dec-07 17-Jan-03 2778V PA31-350 7992.9 1513.1 789.5 105 COM COMM/SELMELAnstr/CFI 2 05-Jan-08 02-Jan-08 4278Y PA31-350 2147.3 622.1 243.2 COMM/SELMELAnstr ATP/SELMELSESAnstr/CFI 1 106 COM 2 10-Dec-07 02-Feb-08 109 COM 22-Jan-08 15-Jan-08
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 6 steps

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education
SEE MORE TEXTBOOKS