Table P6.11 Sample CHARTER Records

a.     Write the relational schema and draw the dependency diagram for the table structure. Make sure that you label all dependencies. CHAR_PAX indicates the number of passengers carried. The CHAR_MILES entry is based on round-trip miles, including pickup points. (Hint: Look at the data values to determine the nature of the relationships. For example, note that employee Melton has flown two charter trips as pilot and one trip as copilot.)

The dependency diagram is shown in Figure P6.11a.

Figure P6.11a The Dependency Diagram for Problem 11a

The relational schema is written as follows:

CHARTER(CHAR_TRIP, CHAR_DATE, CHAR_CITY, CHAR_MILES, CUST_NUM, CUST_LNAME, CHAR_PAX, CHAR_CARGO, PILOT, COPILOT, FLT_ENGINEER, LOAD_MASTER, AC_NUMBER, MODEL_CODE, MODEL_SEATS, MODEL_CHG_MILE)

b. Decompose the dependency diagram in Problem 11a to create table structures that are all in 3NF and write the relational schema. Make sure that you label all dependencies.

 The normalized dependency diagram is shown in Figure P6.11b. (Note the addition of MOD_CODE in the AIRCRAFT table to serve as the AIRCRAFT table’s FK to MODEL.)

Figure P6.11b The Normalized Dependency Diagram for Problem 11b

c.      Draw the Crow’s Foot ERD to reflect the properly decomposed dependency diagrams you created in Problem 11b. Make sure that the ERD yields a database that can track all of the data shown in Problem 11. Show all entities, relationships, connectivities, optionalities, and cardinalities.

 The initial Crow’s Foot ERD is shown in Figure P6.11c.

Figure P6.11c The Initial Crow’s Foot ERD for Problem 11c

While the ERD shown in Figure P6.11c faithfully reflects the results generated by the normalization process, it has a major design flaw. This flaw has the following consequences:

• If additional crewmembers such as copilots, loadmasters, and flight engineers are not assigned to the flight, the CHARTER table will include many nulls. (Many of the smaller aircraft that used in charter flying require only that a pilot and a functioning autopilot be In fact, the Federal Air Regulations (FARs) that govern charter aviation permit single pilot operations for aircraft that have less than a 12,500-lbs. gross take-off weight and that are not turbine-powered.)
• The inclusion of COPILOT, FLT_ENGINEER, and LOAD_MASTER also produce synonyms in the CHARTER table.
• As the aircraft used in the charter flights become larger and more complex, crews become larger, thus producing more synonyms and more potential nulls. (Not to mention that the CHARTER table will have to be modified to accept additional crew members such as flight attendants. )

The problems associated with the ERD shown in Figure P6.11c are eliminated through the composite entity named CREW in Figure P6.11d. Note that this modification makes it possible to assign any number of crewmembers. To ensure that the crewmembers are properly qualified, a job attribute can be added to the EMPLOYEE entity and the applications software can then assign crewmembers based on job classifications such a pilot, loadmaster, flight attendant, etc. Because only some employees are qualified as crewmembers, CREW is optional to EMPLOYEE. But each crewmember must be an employee, so EMPLOYEE is mandatory to CREW.

Figure P6.11d The Final Crow’s Foot ERD for Problem 11c

Note that the application shown in Figure P6.11e -- based on the design shown in Figure P6.11d -- enables the end user to input only those crew members that are required for the charter flight. (In this case, only two crew members are required, but the design permits the addition of many more crew members without making structural changes in the database tables. Such flexibility is the essence of good design.)

Figure P6.11e Sample Charter Record