Part 1: Conceptual Model

Create a conceptual model based on the requirement paragraphs below. Clearly indicate in UML notation each class name, all column names and all relationships between classes, including the multiplicity of each relationship.

Requirements

The following are requirements for a database to manage airline flights:

A flight is a standard, scheduled transit of a particular type of aircraft from one location to another. Barring cancellations, each flight takes place every day. Note that a given flight always has the same origin, the same destination, is scheduled to depart and land at the same time, and always uses the same type of aircraft. (For instance, United 117 is scheduled to depart each day at 7:55am from Denver International Airport and land at 12:15pm at Reagan National Airport, and this happens every morning Sunday through Saturday. The flight always uses a Boeing 747.) An airline never has two different flights with the same number (although United and American could each have their own "flight #916,".)

Read that paragraph above once more, very slowly, and make sure you understand it. Note especially the phrases "every day," "each day," "every morning," and "always."

For each day that a flight actually takes place, the database must record which pilot and copilot are assigned to the aircraft, and which specific aircraft will be used. The database stores the contact information for pilots (and co-pilots), as well as which types of aircraft each of them is certified to fly. It also maintains information about the airplanes themselves, storing for each plane the make and model, how many passengers it can accommodate, the year it was manufactured, the maximum altitude (in feet) that it can fly, how many flight miles it has accumulated, the fuel level, and the minimum runway length (in feet) that it requires for takeoff and landing.

Flights take off from and land at airports, each of which has its own three-letter abbreviation (e.g., "DEN" is Denver International Airport, and "DCA" is Reagan National in Washington, D.C.) The database maintains these abbreviations, in addition to the full name of each airport, and its location (city and state.) It also must track the current location of each aircraft (either the airport where it is currently parked, or the airport it just departed from if the aircraft is currently airborne.)

Airports have runways, each of which is uniquely identified by its bearing (this is a direction, like "N" for "due north" or "SSE" for "South by Southeast.") The length of each runway must also be stored, as well as whether or not the runway supports instrument-only landings. During the course of the day, inbound and outbound flights can temporarily reserve runways for landing and takeoff, and information about these runway reservations must be obtainable from the database.

Passengers can buy tickets to travel from one city to another, and the database must track information about these journeys, which may involve more than one "leg" of travel (ie., not a nonstop flight.) In addition to customer contact information, the database will be used to store the date of travel and all individual flights from origin to final destination. The database must also reflect which gate each inbound and outbound flight will be using for passenger boarding at its origin and destination.