24. For each table, identify each of the following when possible:

a) The primary key

b) A superkey

c) A candidate key

d) The foreign key(s)

e) A secondary key

25. Create the ERD. (Hint: Look at the table contents. You will discover that an AIRCRAFT can fly many CHARTER trips but that each CHARTER trip is flown by one AIRCRAFT, that a MODEL references many AIRCRAFT but that each AIRCRAFT references a single MODEL, and so on.)

26. Create the relational diagram.

27. Modify the ERD you created in Problem 25 to eliminate the problems created by the use of synonyms. (Hint: Modify the CHARTER table structure by eliminating the CHAR_PILOT and CHAR_COPILOT attributes; then create a composite table named CREW to link the CHARTER and EMPLOYEE tables. Some crew members, such as flight attendants, may not be pilots. Thats why the EMPLOYEE table enters into this relationship.)

28. Create the relational diagram for the design you revised in Problem 27.

You want to see data on charters flown by either Robert Williams (employee number 105) or Elizabeth Travis (employee number 109) as pilot or copilot, but not charters flown by both of them. Complete Problems 2931 to find this information.

29. Create the table that would result from applying the SELECT and PROJECT relational operators to the CHARTER table to return only the CHAR_TRIP, CHAR_PILOT, and CHAR_COPILOT attributes for charters flown by either employee 105 or employee 109.

30. Create the table that would result from applying the SELECT and PROJECT relational operators to the CHARTER table to return only the CHAR_TRIP, CHAR_PILOT, and CHAR_COPILOT attributes for charters flown by both employee 105 and employee 109.

31. Create the table that would result from applying a DIFFERENCE relational operator of your result from Problem 29 to your result from Problem 30.

Table name: CHARTER Database name: Ch03 AviaCo 05 5 The destinations are indicated by standard three-letter airport codes. For example STL St. Louis, MO Table name: AIRCRAFT ATL = Atlanta, GA BNA Nashville, TN AC NUMBER | MOD CODE | ACTTAF | ACTTEL ACTTER 484P AC-TTAFAircraft total time, airframe (hours) AC-TTEL = Total time, left engine (hours) AC-TTER 0tal time, right engine (hours) PA23-250 0181 4243 8 2778V PA31-350 PA31-330 79929 15137895 In a fully developed system, such attribute values 6221 2432 would be updated by application software when the CHARTER table entries were posted. M00 CCOE MOD 8200 C-904 PA23-250 Piper PA31-350 Piper ACTURER MO MOD SEATS! MOD CHO Super KingAir 2.67 Aztec Navajo Chieftoin 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 Table name: PILOT Database name: Ch03 AviaCo EMP PL PIL MED_TYPE PL_MED_DATE ATPISELMEL InstriCFll ATPISELMELInstr COMMISELMELInstr CFI2 COMMISELMELInstr ATPISELMELSESAnst CFI 11-Jan-16 17-Jan-16 02-Jan-16 02-Feb-16 15 Jan-16 ATP 18-Dec-15 10-Dec-15 22-Jan-16 COM The pilot licenses shown in the PILOT table include ATP Airline Transport Pilot and COMMCommercial Pilot. Businesses that operate on-demand air services are governed by Part 135 of the Federal Air Regulations (FARs), which 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 every 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 second-class medical certificate (PIL MED TYPE = 2). The PIL_ RATINGS include: Instr. Instrument CFII Certified Flight Instructor, Instrument CFI Certified Flight Instructor Table name: EMPLOYEE I EMP-NUM! EMP-TITLE | EMP EMPFNAME EMP-INITIAL | EMP EMP HIRE DATE Kolmycz Vandam Jones Rhet 101 Ms. Rhonda 19-Mar-65 8 May 11-May 74 5 Jul99 104 M 105 Mr Lange illiams ak Robet Jeanine 14-Mar-75 12-Feb 68 y-75 13-Mar-89 02-Ju-97 107 Mr Wiesenbach Paul 14-Feb-66 18 Juri-51 19 May-70 109 Ms. Eizaboth 14-Fob 06 Table name: CUSTOMER CUS CODE CUS CUS FNAME I CUSINITIAL ODE SPHONE CUS 10010 Ramas 10011 Dunne 10012 Smth 10013 Olowski 10014 Olando 10015 O'Brian 10016 Brown 10017 Williams 10018 Farriss 10019 Smth 844-2573 894-1238 894-2285 894-2180 222-1672 442-3381 290-1228 290-2556 382-7185 297-3809 1285.19 673.21 James 453 98