CPSC/DATA 350 — Applications of Databases — Fall 2024
Possible experience: +40XP
Due: Monday, Sep. 9
Consider the following 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 uses a Boeing 747, and always uses a Boeing 747.)
Incidentally, an airline never has two different flights with the same number (although United and American could each have their own "flight #916.")
Read the three paragraphs above once more, very slowly, and make sure you understand exactly what they are saying. Note especially the phrases "every day," "each day," "every morning," and "always." Then continue reading.
For each day that a flight actually takes place, the database must record which pilot and copilot are (or were) assigned to the aircraft, and which specific plane will be (or was) flown. Flight personnel can always serve either as the pilot or the co-pilot for any type of aircraft they are certified to fly. (The database must maintain a record of which personnel are certified to fly which types of aircraft.)
The database must also contain 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. (Note carefully that some of the items in the previous sentence are the same for all planes of a given aircraft type, while others differ for every individual plane. This should be reflected in your conceptual model.)
Flights take off from and land at airports (duh) 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 plane (either the airport where it is currently parked, or, if it is currently airborne, the airport it just departed from.)
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." ("Uniquely" means that no airport has more than one runway with the same bearing.) The length of each runway must also be stored, of course, 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.
Finally, for each scheduled flight the database must keep track of the number of passengers who have currently bought tickets for it. Each scheduled flight is also assigned to a gate at its origin airport and a gate at its destination airport, which the database should store.
Design a conceptual model for a database that will meet these requirements, and represent it with a UML diagram. Clearly indicate in UML notation each class, attribute, domain, and relationship between classes, including the multiplicity of each relationship.
You may either draw your diagram by hand (if so, please make it legible) or create it using one of numerous free UML modeling tools and printing it out.
Note that the above specification of requirements, like any specification, is incomplete, vague in areas, and potentially self-contradictory. Use your common sense and design intuition to infer the proper meaning from the ambiguous portions. I am the customer for this project, so please forward any questions you cannot resolve to me.
The definition of legible is whatever Stephen doesn't have to work hard to read. You may present me with a sample to judge if you're unsure.
Anything deemed illegible will be promptly returned for no credit.
Put your !LEGIBLE! hardcopy in the "350" manila envelope hanging outside my office door.
Come to office hours, or send me email with subject line "CPSC/DATA 350 Assignment #1 help!!"