FIGURE P3.24 The Ch03_AviaCo database tables (continued) Table name: PILOT Database name: Ch03_AviaCo EMP_NUM PIL_LICENSE PIL RATINGS PIL_MED TYPE PIL_MED_DATE PIL_PT135_DATE 101 ATP ATP/SEL/MEL/Instr/CFII 1 20-Jan-10 11-Jan-10 104 ATP ATP/SEL/MEL/Instr 1 18-Dec-09 17-Jan-10 105 COM COMM/SEL/ME/Instr/CFI 2 05-Jan-10 02-Jan-10 106 COM COMM/SEL/MEL/Instr 2 10-Dec-09 02-Feb-10 109 COM ATP/SEL/MEL/SES/Instr/CFII 1 22-Jan-10 15-Jan-10 The pilot licenses shown in the PILOT table include the ATP = Airline Transport Pilot and COMM = Commercial Pilot. Businesses that operate on-demand air services are governed by Part 135 of the Federal Air Regulations (FARs) that are enforced by the Federal Aviation Administration (FAA). Such businesses are known as Part 135 operators." Part 125 operations require that pilots successfully complete flight proficiency checks every six months. The Part 135 flight proficiency check data are recorded in PIL_PT135_DATE. To fly commercially, pilots must have at least a commercial license and a second-class medical certificate (PIL_MED_TYPE = 2). The PIL_RATINGS include SEL = Single Engine, Land SES = Single Engine, Sea CFI = Certified Flight Instructor MEL = Multiengine, Land Instr. = Instrument CFII = Certified Flight Instructor, Instrument Table name: EMPLOYEE 102 Mr. EMP_NUM EMP_TITLE EMP_LNAME EMPFNAME EMP INITIAL 100 Mr. Kolmycz George D 101 Ms Lewis Rhonda G Vandam Rhett 103 Ms. Jones Anne 104 Mr Lange John 105 Mr Williams Robert 106 Mrs Duzak Jeanine 107 Mr. Diante Jorge D 108 Mr Wiesenbach Paul 109 Ms Travis Elizabeth K 110 Mrs. Genkazi Leighla W ELOXOXYS EMP DOB EMP HIRE DATE 15-Jun-42 15-Mar-88 19-Mar-65 25-Apr-86 14-Nov-58 18-May-93 11-May-74 26-Jul-99 12-Jul-71 20-Aug-90 14-Mar-75 19-Jun-03 12-Feb-68 13-Mar-89 01-May-75 02-Jul-97 14-Feb-66 03-Jun-93 18-Jun-61 14-Feb-06 19-May-70 29-Jun-90 Table name: CUSTOMER CUS INITIAL A K W F CUS CODE CUS LNAME 10010 Ramas 10011 Dunne 10012 Smith 10013 Olowski 10014 Orlando 10015 O'Brian 10016 Brown 10017 Williams 10018 Farriss 10019 Smith CUS_FNAME Alfred Leona Kathy Paul Myron Amy James George Anne Olette CUS AREACODE 615 713 615 615 615 713 615 615 713 615 CUS PHONE 844-2573 894-1238 894-2285 894-2180 222-1672 442-3381 297-1228 290-2556 382-7185 297-3809 CUS BALANCE 0.00 0.00 896.54 1285.19 673 21 1014.56 B G 0.00 G K 0.00 0.00 453.98 Use the database shown in the two Figures below to answer problems 5-6 106 1000 40V 00 00 1 FOR 10013 10015 10014 16450 Table name: CHARTER Database name: Cho3_AviaCo CHAR_TRP CHAR_DATE CHAR_POOT CHAR_COPILOTAC MUNEER CHAR_DESTINATION CHAR_OSTANCE CHAR_HOURS FLOWN CHAR_HOURS_WAIT CUS_CO 10001 05-Feb 10 104 2204 ALL 9360 51 10000 05-Feb-10 101 27 BNA 10010 3000 1000 16 10010 05-Feb 10 105 101 GNV 15740 10004 00 06 Feb 10 STE 10014 4720 29 49 10005 06 Feb 10 101 10019 ATL 90230 57 35 06 Feb 10 109 10010 STL od 4720 26 10000 52 06 Feb 104 10017 1062 GNY or 30 15740 79 00 10000 07.Feb 10 106 Tys 6440 4.1 10012 1000 07.Feb 10 10014 105 220 GNV 15740 66 234 1000 10017 09.Feb 10 100 AT 62 32 10011 Feb 10 10016 101 104NP ONA 3520 19 53 10012 10002 101 2770 MOB 49 42 00 Pub 10 10 42 10010 105 TYS 5440 39 45 10010 OF 106 28 ATL 9360 61 21 08-Feb-10 104 101220 10017 67 00 10016 09-Feb-10 109 105 270 MOY 10018 10017 10-F610 3120 15 00 101 STL 5080 10011 10010 10Feb 10 00 105 10014 1048 TYS 6440 38 10017 The destinations are indicated by standard three-letter airport codes. For example, STL = St. Louis, MO ATL = Atlanta, GA BNA = Nashville, TN Table name: AIRCRAFT AC-TTAF = Aircraft total time, airframe (hours) AC_NUMBER MOD_CODE AC_ITAF AC_TTEL AC_TTER AC-TTEL = Total time, left engine (hours) 1484P PA23-250 1833.1 1833.1 1018 AC_TTER = Total time, right engine (hours) 2289L C-80A 4243.8 7689 1123.4 2778V PA31-350 79929 1513.1 789.5 In a fully developed system, such attribute values 4278Y PA31-350 21473 622.1 2432 would be updated by application software when the CHARTER table entries were posted. Table name: MODEL MOD_CODE MOD_MANUFACTURER MOD_NAME MOD_SEATS MOD_CHG_MLE C-90A Beechcraft Kingar 2.67 PA23-250 Piper Aztec 6 1.93 PA31-350 Piper Navajo Chieftain 10 2.35 Customers are charged per round-trip mile, using the MOD_CHG_MILE rate. The MOD_SEATS gives the total number of seats in the airplane, including the pilot and copilot seats. Therefore, a PA31-350 trip that is flown by a pilot and a copilot has six passenger seats available. d) (TTAC_NUMBER, MOD CODE(AIRCRAFT)) ^ (TMOD_CODE, MOD-NAME(MODEL)) (e) TMODEL.MOD_CODE, AC NUMBER(AIRCRAFT X MODEL). In this query, there are two different columns that could be called MOD CODE and the MODEL prefix indicates the one we want. You will do too much work if you carefully copy over attributes that don't end up in the final projection. Focus on the important attributes for this query, and getting the final number of rows correct. (f) TMOD.CODE(MODEL) TMOD CODE(AIRCRAFT) (g) TCHAR_TRIP.CHAR-PILOT.MOD_CODE,AC-TTAF, MOD-NAME (OCHAR_PILOT>107(CHARTER) XC AIRCRAFT) ~ MODEL) (h) TCHAR_TRIP,CHAR_HOURS WAIT (CHARTER) MACHAR HOURS WAIT >MOD SEATS TTMOD_CODE.MOD SEATS(MODEL) (i) CHARTERU AIRCRAFT