Create the relational diagram.

Use the database shown in Figure P3.24 to answer this Problem. ROBCOR is an aircraft charter company that supplies on-demand charter flight services using a fleet of four aircraft. Aircrafts are identified by a unique registration number. Therefore, the aircraft registration number is an appropriate primary key for the AIRCRAFT table.

Figure P3.24:

The nulls in the CHARTER table€™s CHAR_COPILOT column indicate that a copilot is not required for some charter trips or for some aircraft. Federal Aviation Administration (FAA) rules require a copilot on jet aircraft and on aircraft having a gross take-off weight over 12,500 pounds. None of the aircraft in the AIRCRAFT table are governed by this requirement; however, some customers may require the presence of a copilot for insurance reasons. All charter trips are recorded in the CHARTER table.

ROBCOR is an aircraft charter company that supplies on-demand charter flight services, using a fleet of four aircraft. Aircraft are identified by a (unique) registration number. Therefore, the aircraft registration number is an appropriate primary key for the AIRCRAFT table.

The nulls in the CHARTER table€™s CHAR_COPILOT column indicate that a copilot is not necessarily required for some charter trips or for some aircraft. Federal Aviation Administration (FAA) rules require a copilot on jet aircraft and on aircraft having a gross take-off weight over 12,500 pounds. None of the aircraft in the AIRCRAFT table is governed by this requirement; however, some customers may require the presence of a copilot for insurance reasons. All charter trips are recorded in the CHARTER table.

Transcribed Image Text:

Database name: Ch03_AviaCo Table name: CHARTER OHAR TRP CHAR DATE CHAR PLOT CHAR COPLOT AC NUMEER CHAR DESTNATION CHAR DSTANCE CHAR HOURS FLOWN CHAR HOURS WAIT CHAR FUEL GALLONS CHAR OL OTS CUS COCE 22 354.1 10011 10001 9360 05-Feb-18 104 220 ATL 51 05Feb-1 10002 101 2778V BNA 320.0 16 00 726 10016 339 10003 105 106 0SFeb-10 109 4278Y GNV 1S740 78 00 10014 10019 10004 06Feb-10 14 220 STL 4720 29 10005 10006 10007 06-Feb-18 10 ATL 1023.0 57 35 3977 21 10011 06Feb-18 109 4278Y STL 4720 26 $2 1171 10017 06Feb-16 104 105 277aV 79 00 3484 10012 1484 10008 07-Feb-18 106 TYS 644.0 00 140.6 10014 10009 10010 07Feb-10 105 220 1S40 66 234 4599 10017 07feb-18 109 4278V 104 140 900.0 62 32 2797 1006 10011 07Feb-18 101 BNA 3520 19 53 664 10012 10012 08Feb-10 06Feb-10 101 2778V MOB 804.0 215.1 10010 TYS ATL 39 1743 10013 105 427Y 644.0 45 10011 10014 09-Feb-18 106 4278Y 906.0 61 2.1 3026 4595 10017 10015 09Feb-18 104 101 220 ONV 16450 67 00 21 1006 10011 10014 09Feb-10 10Feb-18 10016 109 105 277V MOY 3120 10017 10018 101 ST. 31 1055 140 10Feb-16 105 104 4278Y TYS 6440 38 45 1674 10017 The destinations are indicated by standard three-letter airport codes. For example, ATL - Atlanta, GA STL - St. Louis, MO BNA - Nashville, TN Table name: AIRCRAFT AC NUMBER MO0 CODE - Aircraft total time, airframe (hours) - Total time, left engine (hours) - Total time, right engine (hours) AC_TTAF AC_TTEL AC_TTER AC TTAF AC TTEL AC TTER 101.8 1833 1 42438 1484P PA23-250 1833.1 2289L C-90A 768 9 1513.1 6221 1123.4 PA31-350 PA31-350 789.5 2778V 4278Y 7992 9 2147 3 243 2 In a fully developed database system, such attribute values would be updated by application software when the CHARTER table entries are posted. Table name: MODEL MOD CODE MOD_MANUFACTURER B200 MOD SEATS MO0 CHO MLE MOD_NAME Super KingAr 10 1.93 Beechcraft C-90A Beechcraft 267 KingAir PA23-250 Piper Aztec 1.93 PA31-350 Piper Navajo Chieftain 10 235 Customers are charged per roundtrip 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 eight passenger seats available. Table name: PILOT EMP NUM PL UICENSE PIL_RATINGS PIL_MED_TYPE PIL_MED_DATE PL_PT135_DATE Database name: Ch03_AviaCo 101 ATP ATP/SELMELAnstr/CFI 20-Jan-18 11-Jan-18 104 ATP ATP/SELMELinstr 18-Dec-17 17-Jan-18 105 COM COMM/SELMELAnstr/CFI 05-Jan-18 02-Jan-18 106 COM COMMSELMELAnstr 10-Dec-17 02-Feb-18 109 COM ATPISELMELSESAnstr/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 (FARSs) 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 - Single Engine (Sea) Certified Flight Instructor MEL - Multi-engine Land Instr. - Instrument SES CFI CFII - Certified Flight Instructor, Instrument Table name: EMPLOYEE EMP_NUM EMP_TITLE EMP_LNAME EMP_FNAME EMP_NTIAL EMP DOB EMP HIRE DATE 100 Mr. Kolmycz George Rhonda 15-Jun-62 15-Mar-08 101 Ms. Lewis 19-Mar-85 25-Apr-06 102 Mr. Vandam Rhett 14-Nov-78 18-May-13 11-May-94 12-u-91 103 Ms. Jones Anne 26-Jul-17 104 Mr. Lange John 20-Aug-10 105 Mr. Wliams Robert 14-Mar-95 19-Jun-17 106 Mrs Duzak Jeanine к 12-Feb-88 13-Mar-18 107 Mr. Diante Jorge Paul Eizabeth 01-May-95 14-Feb-86 02-Jul-16 108 Mr. Wesenbach 03-un-13 109 Ms. Travis к 18-Jun-81 14-Feb-16 110 Mrs Genkazi Leighla 19-May-90 29-Jun-10 Table name: CUSTOMER CUS CODE CUS INAME CUS FNAME CUS PHONE CUS INITIAL CUS AREACODE CUS BALANCE 10010 Ramas Alfred 615 844-2573 0.00 10011 Dunne Leona к 713 894-1238 0.00 896.54 1205. 19 673 21 1014.56 0.00 0.00 0.00 453.98 10012 Smith 10013 Olowski Kathy Paul 615 894-2285 615 894-2180 10014 Orlando Myron Amy James 615 222-1672 10015 OBrian 10016 Brown 10017 Wiliams 10018 Famiss 10019 Smith 713 615 615 713 615 442-3381 297-1228 290-2556 382-7185 297-3809 George Anne Olette