Knowledge Creation through User-Guided Data Mining: A Database Case

1. PROJECT SUMMARY Terra Firma, Inc. (TFI) is a growing management consulting firm headquartered in Bangor, Maine. TFI has major, ongoing consulting assignments in several U.S. cities, and is currently expanding into other U.S. regions, as well as Western Europe. To transport its consultants to and from their assignments, the firm has purchased a small fleet of seven (previously owned) corporate jets. TFI now needs your assistance in building a reservation, scheduling, and reporting database system for its airline operations. It should be noted that one of the fringe benefits of Terra Firma employment is that employees are provided free transportation to and from the vacation locations of their choice, a benefit that most employees frequently use. 2. PROJECT BACKGROUND AND REQUIREMENTS The TFI Reservation System must be designed to satisfy several requirements described below. (Note: the attribute formats are given in parenthesis within the text below, e.g., D4 indicates a four digit numeric field, C5 indicates a five character alphanumeric field.) 2.1 Aircraft Information The airline has several types of aircraft, each type of aircraft, uniquely identified by a type code (C5), has a specified passenger capacity (D3), a flight range (D4), a type description (C25), and the maximum number of flight hours (D3) between scheduled maintenance appointments (an aircraft is out of service for one week during its scheduled maintenance). There are one or more aircraft of each type in the airline's fleet. Each specific aircraft has a unique serial number (C6), a type code, the date of its most recent maintenance (C8), and its current fair market value (D7). Each specific aircraft can fly at most one round trip each day. 2.2 Pilot Information Pilots, uniquely identified by a pilot ID (C5), are certified to fly only certain type(s) of aircraft. Pilot information includes the pilot's name (C8), address (C15), city (C15), state (C2), zip code (C5), phone number (C13), and the type codes of all aircraft s/he is certified to fly. Each pilot flies at most one round trip/day and at most 30 hours/week (air time), and is paid an annual salary of $80,000 plus 28% benefits; pilots are non-union employees. 2.3 Flight Information Each flight, identified by a unique flight number (C3), has an origination city code (C3) and a destination city code (C3) (there are currently no intermediate stops), estimated time of departure (D4) from the origination city (ETD), estimated time of arrival (D4) at the destination city (ETA), flight distance from origin to destination (in miles), a round trip id (C3) (a round trip includes the outbound leg and the return flight), and the commercial price (D5) of a 1st class round trip ticket to this destination. All times are recorded using a military designation (i.e., using a 24 hour clock), where 9:30 a.m. is 0930, 1:15 p.m. is 1315, etc.; both the ETD and ETA are specified in terms of the local standard time of the flight's origination city. Further, each origination/destination city has a city code (C3), the full name of the city (C10), state/country code, and a brief description of the runway facilities (C28). 2.4 Scheduled Flight Information A scheduled flight is a flight that has been scheduled for a specific date, and includes the flight number (C3), flight date (C8), aircraft serial number, and a pilot ID, in addition to the flight information listed above; a scheduled flight is uniquely identified by the combination of a flight number and date. 2.5 Passenger Information Passengers can make reservations on any scheduled flights; however, they are not assigned specific seats. For purposes of this database, we will assume that passengers are uniquely identified by name (C10); additional passenger information includes his/her department (C2), job grade (C2), and phone number (C13). When passengers book reservations on a scheduled flight, they must provide a project ID (C4) to which the fare will be charged. …