Answer Happy

THIS IS THE CODE FOR THE DATABASE IN SQL SERVER 8.PLEASE REALLY TAKE YOUR TIME TO HELP ON THESE QUESTIONS IF YOUCAN'T ANSWER THEM, PLEASE DON'T ANSWER:
create database assignment5;
use assignment5;
CREATE TABLE AIRCRAFT (
AC_NUMBER varchar(5) primary key,
MOD_CODE varchar(10),
AC_TTAF double,
AC_TTEL double,
AC_TTER double
);
INSERT INTO AIRCRAFTVALUES('1484P','PA23-250',1833.1,1833.1,101.8);
INSERT INTO AIRCRAFTVALUES('2289L','C-90A',4243.8,768.9,1123.4);
INSERT INTO AIRCRAFTVALUES('2778V','PA31-350',7992.9,1513.1,789.5);
INSERT INTO AIRCRAFTVALUES('4278Y','PA31-350',2147.3,622.1,243.2);
/* -- */
CREATE TABLE CHARTER (
CHAR_TRIP int primary key,
CHAR_DATE date,
AC_NUMBER varchar(5),
CHAR_DESTINATION varchar(3),
CHAR_DISTANCE double,
CHAR_HOURS_FLOWN double,
CHAR_HOURS_WAIT double,
CHAR_TOT_CHG double,
CHAR_OIL_QTS int,
CUS_CODE int
);
INSERT INTO CHARTERVALUES(10001,'2008-02-05','2289L','ATL',936,5.1,2.2,354.1,1,10011);
INSERT INTO CHARTERVALUES(10002,'2008-02-05','2778V','BNA',320,1.6,0,72.6,0,10016);
INSERT INTO CHARTERVALUES(10003,'2008-02-05','4278Y','GNV',1574,7.8,0,339.8,2,10014);
INSERT INTO CHARTERVALUES(10004,'2008-02-06','1484P','STL',472,2.9,4.9,97.2,1,10019);
INSERT INTO CHARTERVALUES(10005,'2008-02-06','2289L','ATL',1023,5.7,3.5,397.7,2,10011);
INSERT INTO CHARTERVALUES(10006,'2008-02-06','4278Y','STL',472,2.6,5.2,117.1,0,10017);
INSERT INTO CHARTERVALUES(10007,'2008-02-06','2778V','GNV',1574,7.9,0,348.4,2,10012);
INSERT INTO CHARTERVALUES(10008,'2008-02-07','1484P','TYS',644,4.1,0,140.6,1,10014);
INSERT INTO CHARTERVALUES(10009,'2008-02-07','2289L','GNV',1574,6.6,23.4,459.9,0,10017);
INSERT INTO CHARTERVALUES(10010,'2008-02-07','4278Y','ATL',998,6.2,3.2,279.7,0,10016);
INSERT INTO CHARTERVALUES(10011,'2008-02-07','1484P','BNA',352,1.9,5.3,66.4,1,10012);
INSERT INTO CHARTERVALUES(10012,'2008-02-08','2778V','MOB',884,4.8,4.2,215.1,0,10010);
INSERT INTO CHARTERVALUES(10013,'2008-02-08','4278Y','TYS',644,3.9,4.5,174.3,1,10011);
INSERT INTO CHARTERVALUES(10014,'2008-02-09','4278Y','ATL',936,6.1,2.1,302.6,0,10017);
INSERT INTO CHARTERVALUES(10015,'2008-02-09','2289L','GNV',1645,6.7,0,459.5,2,10016);
INSERT INTO CHARTERVALUES(10016,'2008-02-09','2778V','MQY',312,1.5,0,67.2,0,10011);
INSERT INTO CHARTERVALUES(10017,'2008-02-10','1484P','STL',508,3.1,0,105.5,0,10014);
INSERT INTO CHARTERVALUES(10018,'2008-02-10','4278Y','TYS',644,3.8,4.5,167.4,0,10017);
/* -- */
CREATE TABLE CREW (
CHAR_TRIP int,
EMP_NUM int,
CREW_JOB varchar(20),
primary key (CHAR_TRIP, EMP_NUM)
);
INSERT INTO CREW VALUES(10001,104,'Pilot');
INSERT INTO CREW VALUES(10002,101,'Pilot');
INSERT INTO CREW VALUES(10003,105,'Pilot');
INSERT INTO CREWVALUES(10003,109,'Copilot');
INSERT INTO CREW VALUES(10004,106,'Pilot');
INSERT INTO CREW VALUES(10005,101,'Pilot');
INSERT INTO CREW VALUES(10006,109,'Pilot');
INSERT INTO CREW VALUES(10007,104,'Pilot');
INSERT INTO CREWVALUES(10007,105,'Copilot');
INSERT INTO CREW VALUES(10008,106,'Pilot');
INSERT INTO CREW VALUES(10009,105,'Pilot');
INSERT INTO CREW VALUES(10010,108,'Pilot');
INSERT INTO CREW VALUES(10011,101,'Pilot');
INSERT INTO CREWVALUES(10011,104,'Copilot');
INSERT INTO CREW VALUES(10012,101,'Pilot');
INSERT INTO CREW VALUES(10013,105,'Pilot');
INSERT INTO CREW VALUES(10014,106,'Pilot');
INSERT INTO CREWVALUES(10015,101,'Copilot');
INSERT INTO CREW VALUES(10015,104,'Pilot');
INSERT INTO CREWVALUES(10016,105,'Copilot');
INSERT INTO CREW VALUES(10016,109,'Pilot');
INSERT INTO CREW VALUES(10017,101,'Pilot');
INSERT INTO CREWVALUES(10018,104,'Copilot');
INSERT INTO CREW VALUES(10018,105,'Pilot');
/* -- */
CREATE TABLE CUSTOMER (
CUS_CODE int primary key,
CUS_LNAME varchar(15),
CUS_FNAME varchar(15),
CUS_INITIAL varchar(1),
CUS_AREACODE varchar(3),
CUS_PHONE varchar(8),
CUS_BALANCE double
);
INSERT INTO CUSTOMERVALUES(10010,'Ramas','Alfred','A','615','844-2573',0);
INSERT INTO CUSTOMERVALUES(10011,'Dunne','Leona','K','713','894-1238',0);
INSERT INTO CUSTOMERVALUES(10012,'Smith','Kathy','W','615','894-2285',896.53);
INSERT INTO CUSTOMERVALUES(10013,'Olowski','Paul','F','615','894-2180',1285.18);
INSERT INTO CUSTOMERVALUES(10015,'O''Brian','Amy','B','713','442-3381',1014.55);
INSERT INTO CUSTOMERVALUES(10014,'Orlando','Myron','','615','222-1672',673.21);
INSERT INTO CUSTOMERVALUES(10016,'Brown','James','G','615','297-1228',0);
INSERT INTO CUSTOMERVALUES(10017,'Williams','George','','615','290-2556',0);
INSERT INTO CUSTOMERVALUES(10018,'Farriss','Anne','G','713','382-7185',0);
INSERT INTO CUSTOMERVALUES(10019,'Smith','Olette','K','615','297-3809',453.9);
/* -- */
CREATE TABLE EARNEDRATING (
EMP_NUM int,
RTG_CODE varchar(5),
EARNRTG_DATE date,
primary key (emp_num, rtg_code)
);
INSERT INTO EARNEDRATINGVALUES(101,'CFI','1998-02-18');
INSERT INTO EARNEDRATINGVALUES(101,'CFII','2005-12-15');
INSERT INTO EARNEDRATINGVALUES(101,'INSTR','1993-11-08');
INSERT INTO EARNEDRATINGVALUES(101,'MEL','1994-06-23');
INSERT INTO EARNEDRATINGVALUES(101,'SEL','1993-04-21');
INSERT INTO EARNEDRATINGVALUES(104,'INSTR','1996-07-15');
INSERT INTO EARNEDRATINGVALUES(104,'MEL','1997-01-09');
INSERT INTO EARNEDRATINGVALUES(104,'SEL','1995-03-12');
INSERT INTO EARNEDRATINGVALUES(105,'CFI','1997-11-18');
INSERT INTO EARNEDRATINGVALUES(105,'INSTR','1995-04-17');
INSERT INTO EARNEDRATINGVALUES(105,'MEL','1995-08-12');
INSERT INTO EARNEDRATINGVALUES(105,'SEL','1994-09-23');
INSERT INTO EARNEDRATINGVALUES(106,'INSTR','1995-12-20');
INSERT INTO EARNEDRATINGVALUES(106,'MEL','1996-04-02');
INSERT INTO EARNEDRATINGVALUES(106,'SEL','1994-03-10');
INSERT INTO EARNEDRATINGVALUES(109,'CFI','1998-11-05');
INSERT INTO EARNEDRATINGVALUES(109,'CFII','2003-06-21');
INSERT INTO EARNEDRATINGVALUES(109,'INSTR','1996-07-23');
INSERT INTO EARNEDRATINGVALUES(109,'MEL','1997-03-15');
INSERT INTO EARNEDRATINGVALUES(109,'SEL','1996-02-05');
INSERT INTO EARNEDRATINGVALUES(109,'SES','1996-05-12');
/* -- */
CREATE TABLE EMPLOYEE (
EMP_NUM int primary key,
EMP_TITLE varchar(4),
EMP_LNAME varchar(15),
EMP_FNAME varchar(15),
EMP_INITIAL varchar(1),
EMP_DOB date,
EMP_HIRE_DATE date
);
INSERT INTO EMPLOYEEVALUES(100,'Mr.','Kolmycz','George','D','1942-06-15','1987-03-15');
INSERT INTO EMPLOYEEVALUES(101,'Ms.','Lewis','Rhonda','G','1965-03-19','1988-04-25');
INSERT INTO EMPLOYEEVALUES(102,'Mr.','VanDam','Rhett','','1958-11-14','1992-12-20');
INSERT INTO EMPLOYEEVALUES(103,'Ms.','Jones','Anne','M','1974-10-16','2005-08-28');
INSERT INTO EMPLOYEEVALUES(104,'Mr.','Lange','John','P','1971-11-08','1996-10-20');
INSERT INTO EMPLOYEEVALUES(105,'Mr.','Williams','Robert','D','1975-03-14','2006-01-08');
INSERT INTO EMPLOYEEVALUES(106,'Mrs.','Duzak','Jeanine','K','1968-02-12','1991-01-05');
INSERT INTO EMPLOYEEVALUES(107,'Mr.','Diante','Jorge','D','1974-08-21','1996-07-02');
INSERT INTO EMPLOYEEVALUES(108,'Mr.','Wiesenbach','Paul','R','1966-02-14','1994-11-18');
INSERT INTO EMPLOYEEVALUES(109,'Ms.','Travis','Elizabeth','K','1961-06-18','1991-04-14');
INSERT INTO EMPLOYEEVALUES(110,'Mrs.','Genkazi','Leighla','W','1970-05-19','1992-12-01');
/* -- */
CREATE TABLE MODEL (
MOD_CODE varchar(10) primary key,
MOD_MANUFACTURER varchar(15),
MOD_NAME varchar(20),
MOD_SEATS double,
MOD_CHG_MILE double
);
INSERT INTO MODELVALUES('C-90A','Beechcraft','KingAir',8,2.67);
INSERT INTO MODELVALUES('PA23-250','Piper','Aztec',6,1.92);
INSERT INTO MODEL VALUES('PA31-350','Piper','NavajoChieftain',10,2.34);
/* -- */
CREATE TABLE PILOT (
EMP_NUM int primary key,
PIL_LICENSE varchar(25),
PIL_RATINGS varchar(25),
PIL_MED_TYPE varchar(1),
PIL_MED_DATE date,
PIL_PT135_DATE date
);
INSERT INTO PILOTVALUES(101,'ATP','SEL/MEL/Instr/CFII','1','2008-04-12','2007-05-16');
INSERT INTO PILOTVALUES(104,'ATP','SEL/MEL/Instr','1','2007-06-10','2008-03-23');
INSERT INTO PILOTVALUES(105,'COM','SEL/MEL/Instr/CFI','2','2008-02-25','2008-02-12');
INSERT INTO PILOTVALUES(106,'COM','SEL/MEL/Instr','2','2008-04-02','2007-12-24');
INSERT INTO PILOTVALUES(109,'COM','SEL/MEL/SES/Instr/CFII','1','2008-04-14','2008-04-21');
/* -- */
CREATE TABLE RATING (
RTG_CODE varchar(5) primary key,
RTG_NAME varchar(50)
);
INSERT INTO RATING VALUES('CFI','Certified FlightInstructor');
INSERT INTO RATING VALUES('CFII','Certified FlightInstructor, Instrument');
INSERT INTO RATINGVALUES('INSTR','Instrument');
INSERT INTO RATING VALUES('MEL','MultiengineLand');
INSERT INTO RATING VALUES('SEL','Single Engine,Land');
INSERT INTO RATING VALUES('SES','Single Engine,Sea');
Question 2: Triggers and Stored Procedures Table name: CHARTER CHAR_TRIP CHAR_DATE AC_NUMBER | CHAR_DESTINATION CHAR_DISTANCE CHAR_HOURS_FLOWN CHAR_HOURS_WAIT CHAR_FUEL_GALLONS CHAR_OIL QTS 10001 05-Feb-08 2289L 05-Feb-08 2778V 10002 10003 05-Feb-08 4278Y 10004 06-Feb-08 1484P 10005 06-Feb-08 2289L 10006 06-Feb-08 4278Y 10007 06-Feb-08 2778V 10008 07-Feb-08 1484P 10009 07-Feb-08 2289L 10010 07-Feb-08 4278Y 10011 07-Feb-08 1484P 10012 08-Feb-08 2778V 10013 10014 08-Feb-08 4278Y 09-Feb-08 4278Y 10015 09-Feb-08 2289 10016 09-Feb-08 2778V 10017 10-Feb-08 1484P 10018 10-Feb-08 4278Y Table name: CREW CHAR_TRIP EMP_NUM CREW JOB 104 Plot 101 Pilot 105 Plot 109 Copilot 10001 10002 10003 10003 10004 10005 10006 10007 10007 10008 10009 10010 10011 10011 10012 10013 10014 10015 10015 10016 10016 10017 10018 10018 106 Pilot 101 Plot 109 Plot 104 Pilot 105 Copilot 106 Plot 105 Plot 108 Pilot 101 Pilot 104 Copilot 101 Pilot 105 Plot 106 Pilot 101 Copilot 104 Plot 105 Copilot 109 Plot 101 Plot 104 Copilot 105 Plot ATL BNA GNV STL ATL STL GNV TYS GNV ATL BNA MOB TYS ATL GNV MOY STL TYS 10010 Ramas 10011 Dunne 10012 Smith Table name: AIRCRAFT AC NUMBER MOD CODE AC_TTAF 1484P PA23-250 2289L C-90A 2778V PA31-350 PA31-350 4278Y 10013 Olowski 10014 Orlando 10015 O'Brian 10016 Brown 10017 Williams 10018 Farriss 10019 Smith 104 Mr. 105 Mr. 106 Mrs. 107 Mr 108 Mr. 109 Ms. 110 Mrs. 936 320 1574 472 1023 472 1574 644 1574 1833.1 1833.1 4243.8 768.9 7992.9 1513.1 2147.3 622.1 998 352 884 644 936 1645 312 508 644 AC_TTEL AC_TTER Alfred Leona Kathy Paul Myron Amy James George Anne Olette Kolmycz Lewis Vandam 101.8 1123.4 789.5 243.2 Jones Lange Williams Duzak Diante Wiesenbach Travis Genkazi A K W F L Table name: CUSTOMER CUS_CODE CUS_LNAME CUS_FNAME CUS_INITIAL CUS_AREACODE CUS_PHONE CUS_BALANCE 844-2573 894-1238 894-2285 894-2180 222-1672 B G G K 5.1 1.6 Table name: EMPLOYEE G EMP_NUM EMP_TITLE EMP_LNAME EMP_FNAME EMP INITIAL 100 Mr George D 101 Ms. Rhonda Rhett Anne John 102 Mr. 103 Ms. Robert Jeanine Jorge Paul Elizabeth Leighla 7.8 2.9 5.7 2.6 7.9 4.1 6.6 6.2 1.9 4.8 101 ATP 104 ATP 105 COM 106 COM 109 COM 3.9 6.1 6.7 1.5 3.1 3.8 615 713 615 615 615 713 615 615 713 615 M P D K D R K W Table name: PILOT EMP_NUM PIL_LICENSE 2.2 0 0 4.9 3.5 5.2 0 0 23.4 3.2 5.3 4.2 4.5 2.1 0 0 0 4.5 442-3381 297-1228 290-2556 382-7185 297-3809 354.1 72.6 339.8 97.2 18-Jun-1961 19-May-1970 397.7 117.1 348.4 140.6 PIL RATINGS 1 1 ATP/SEL/MELAnstr/CFII ATP/SEL/MELAnstr COMM/SELMELAnstr/CFI 2 COMMISELMELAnstr ATP/SELIMELISESAnstr/CFI 1 2 459.9 279.7 66.4 215.1 174.3 302.6 459.5 67 2 105.5 167.4 0.00 0.00 896.54 1285.19 673.21 1014.56 EMP DOB EMP_HIRE DATE 15-Jun-1942 19-Mar-1965 14-Nov-1958 16-Oct-1974 08-Nov-1971 14-Mar-1975 12-Feb-1968 21-Aug-1974 14-Feb-1966 0.00 0.00 0.00 453.98 1 0 2 1 2 0 2 1 0 0 1 18-Nov-1994 14-Apr-1991 01-Dec-1992 0 1 0 2 0 0 0 Database name: Ch08_Avia Co CUS_CODE 10011 Table name: EARNEDRATING 10016 EMP_NUM RTG_CODE EARNRTG_DATE 10014 10019 10011 10017 10012 10014 10017 10016 10012 10010 10011 10017 10016 10011 10014 10017 101 CFI 101 CFI 101 INSTR CFI CFII INSTR MEL SEL SES 101 MEL 101 SEL 104 INSTR 104 MEL 104 SEL 105 CFI 105 INSTR 105 MEL 105 SEL 106 INSTR 106 MEL 106 SEL 109 CFI 109 CFI 109 INSTR 109 MEL 109 SEL 109 SES Instrument Multiengine Land Single Engine, Land Single Engine, Sea 15-Mar-1987 25-Apr-1988 Table name: MODEL 20-Dec-1992 28-Aug-2005 Beechcraft 20-Oct-1996 PA23-250 PA23-250 Piper 08-Jan-2006 PA31-350 PA31-350 Piper 05-Jan-1991 02-Jul-1996 Table name: RATING RTO_CODE RTO NAME Certified Flight Instructor Certified Flight Instructor, Instrument PIL_MED_TYPE PIL_MED_DATE | PL_PT135_DATE 20-Jan-08 18-Dec-07 05-Jan-08 10-Dec-07 22-Jan-08 11-Jan-08 17-Jan-08 02-Jan-08 02-Feb-08 15-Jan-08 18-Feb-98 15-Dec-05 08-Nov-93 MOD_CODE MOD_MANUFACTURER MOD_NAME MOD SEATS MOD CHG_MILE C-90A 8 2.67 6 1.93 2.35 KingAir Aztec Navajo Chieftain 23-Jun-94 21-Apr-93 15-Jul-96 29-Jan-97 12-Mar-95 18-Nov-97 17-Apr-95 12-Aug-95 23-Sep-94 20-Dec-95 02-Apr-96 10-Mar-94 05-Nov-98 21-Jun-03 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 23-Jul-96 15-Mar-97 05-Feb-96 12-May-96 10
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: 1) Create a view named "Special CHARTERS V" that includes the following "For each charter of aircraft to St. Louis (STL), print the charter date, charter hours wait, and the corresponding customer cus code, area code, and phone number". Your answer should include both the SQL statement for view creating along with the contents of the view (you get the contents of the view by running the command select from Special CHARTERS V). 2) 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 MOD_LIFT_WEIGHT Amount of weight each model can lift: Attribute type Numeric Attribute Values 12,000 for C-90A 7,200 for PA23-250 19,750 for PA31-350
3) Create a trigger named trg char hours 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 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 CHAR_HOUR_FLOWN 4) Create a trigger named trg cust balance 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 $1 service charge for every 50 miles flown. 5) 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 2%. 6) Create a stored procedure that will take an Employee number and percentage, then update the corresponding employee's hourly salary by the input percentage (increase the hourly salary, so you are giving the employee a raise). Hint: you may have to do some additional work on this one to get the table correct.