Give relational algebra expressions to calculate the following: (a) AC NUMBERs of aircraft that probably have an original left engine. (This can be detected if the number of hours on left engine is equal to the number of hours on the airframe.) (b) AC_NUMBERs of aircraft that have at most 9 seats. (c) Aircraft model names in our database that have never been flown to Atlanta. (d) First and last names of pilots whose title are Mrs. and who have flown a charter to Atlanta. Do not assume that the contents of the relations are as shown. (So you cannot just look at the tables by yourself and know that the Super King Air and Navajo Chieftan aircraft are the 10-seat models. You can rely on the relations having the attributes shown, though.) List the tables that have foreign keys. Which have more than one foreign key? For the MODEL table, give a superkey involving involving two attributes, with one of the attributes being the actual primary key. Draw an ER diagram for Ch03_AviaCo. 104 50 Table name: CHARTER Database name: Ch03_AviaCo OUR TROUR DATE CHAR PLOT OUR_COPLOTAC NUMBE OUR DESTINATION OUR DISTANCE CHAR KURS-FLOW OUR HOURS WAT OHARRA GALLONS OUROL OSCUS_COCE 0001 05-Feb-10 2200 ATL 50 22 1 10011 05.10 101 PTV GNIA 18 00 726 10016 10003 05.10 105 10S 42TBY ONV 15140 78 00 08 2 10014 0804 06-7-10 106 1630 SIL 4720 29 972 1 10019 SOOS 06 Feb 10 101 2207 ATL 10230 57 35 27 2 10011 OBOS 007-10 100 42POY SRL 4720 25 1171 O 10017 1007 01718 Feb 104 105 TV GNV 15740 79 2 10012 10000 OT Feb 10 106 14 TVS 5446 40 00 1406 1 10014 0809 077-10 105 GNV 15740 66 234 4509 0 0 10017 10010 07 Feb 10 - 109 4279 ATL 3D 62 32 2797 0 10010 011 077-10 101 104 100 1920 10 53 904 1 10012 10012 010 101 2770 0040 40 42 2151 10010 10019 03.10 105 TVS 500 30 1783 1 10011 0014 09-10 106 4278Y ATL 990 60 21 3026 0 10017 10015 09-10 104 101 2201 ONY 16450 67 00 4905 2 10010 S0018 07-10 109 105 2770 MOY 3120 15 00 072 0 10011 2017 107-10 101 16 SIL 5080 31 00 1055 10014 10016 Feb 18 10/10 105 104 78Y BM SA 45 1024 0 10017 TYS 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_TTAF AC_TTEL AC_TTER AC-TTEL = Total time, left engine (hours) 1484P PA23-250 1833.1 1833.1 101.8 AC_TTER = Total time, right engine (hours) 2289L C-90A 4243.8 768.9 1123.4 2778V PA31-350 7992.9 1513.1 789.5 In a fully developed system, such attribute values 4278Y PA31-350 2147.3 622.1 243.2 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_MILE B200 Beechcraft Super KingAir 10 1.93 C-90A Beechcraft KingAir 8 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 column lists 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 eight passenger seats available. FIGURE P3.24 THE CH03_AVIACO DATABASE TABLES (CONTINUED) Database name: Ch03_AviaCo Table name: PILOT EMP_NUM PIL_LICENSE 101 ATP 104 ATP 105 COM 106 COM 109 COM PIL_RATINGS PIL_MED_TYPE PIL_MED_DATE PIL_PT135_DATE ATP/SEL/MELAnstr/CF|| 1 20-Jan-18 11-Jan-18 ATP/SEL/MELAnstr 1 18-Dec-17 17-Jan-18 COMM/SELAMELAnstr/CFI 2 05-Jan-18 02-Jan-18 COMM/SEL/MELAnstr 2 10-Dec-17 02-Feb-18 ATP/SEL/MEL/SESAnstr/CFI 1 22-Jan-18 15-Jan-18 The pilot licenses shown in the PILOT table include the ATP = Airline Transport Pilot and COM = 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 135 operations require that pilots successfully complete flight proficiency checks each six months. The Part 135" flight proficiency check date is recorded in PIL_PT135_DATE. To fly commercially, pilots must have at least a commercial license and a 2nd class medical certificate (PIL_MED_TYPE = 2.) The PIL_RATINGS include SEL = Single Engine, Land MEL = Multi-engine Land SES = Single Engine (Sea) Instr. = Instrument CFI = Certified Flight Instructor CFII = Certified Flight Instructor, Instrument Table name: EMPLOYEE EMP_INITIAL D G M EMP_NUM EMP_TITLE EMP_LNAME 100 Mr Kolmycz 101 Ms. Lewis 102 Mr Vandam 103 Ms. Jones 104 Mr. Lange 105 Mr Williams 106 Mrs. Duzak 107 Mr Diante 108 Mr. Wiesenbach 109 Ms Travis 110 Mrs. Genkazi EMP_FNAME George Rhonda Rhett Anne John Robert Jeanine Jorge Paul Elizabeth Leighla D EMP_DOB EMP_HIRE_DATE 15-Jun-62 15-Mar-08 19-Mar-85 25-Apr-06 14-Nov-78 18-May-13 11-May-94 26-Jul-17 12-Jul-91 20-Aug-10 14-Mar-95 19-Jun-17 12-Feb-88 13-Mar-18 01-May-95 02-Jul-16 14-Feb-86 03-Jun-13 18-Jun-81 14-Feb-16 19-May-90 29-Jun-10 K D R K w Table name: CUSTOMER CUS_INITIAL A w F CUS_CODE CUS_LNAME CUS_FNAME 10010 Ramas Alfred 10011 Dunne Leona 10012 Smith Kathy 10013 Olowski Paul 10014 Orlando Myron 10015 O'Brian | Amy 10016 Brown James 10017 Williams George 10018 Farriss Anne 10019 Smith 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 0.00 0.00 0.00 453.98 B G G K
