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 rela-tional 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.

FIGURE P3.24 THE CH03_AVIACO DATABASE TABLES Table name: CHARTER Database name: Ch03_AviaCo 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-TTEL = Total time, left engine (hours) AC_TTER = Total time, right engine (hours) In a fully developed system, such attribute values would be updated by application software when the CHARTER table entries were posted. Table name: MODEL 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 The pilot licenses shown in the PILOT table include 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), which are enforced by the Federal Aviation Administration (FAA). Such businesses are known as "Part 135 operators." Part 135 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: SEL = Single Engine, Land MEL = Multiengine, Land SES = Single Engine, Sea Instr. = Instrument CFI= Certified Flight Instructor CFII = Certified Flight Instructor, Instrument Table name: EMPLOYEE Table name: CUSTOMER