An airline company wishes to construct a database to store information about passengers, flights, personnel and aircraft. The following information is to be included in the database:
Customers (Passengers): The airline keeps track of passenger information consisting of a customer ID (a number), first name and last name, an address (street address, city, state, and ZIP code), a phone number (just one), and optionally a company (employer) name. Customer ID uniquely identifies a customer. Passengers are booked on one or more flights. A customer does not become a customer until he books a flight.
Flights: Flight information consists of a flight number, a departure date, a departure time, a departure airport, a destination date, a destination time, a destination airport, an aircraft, the number of passengers, and a flight crew. Since a flight number is used to identify a flight that departs at a given time from a given airport (e.g. 10:30 a.m. flight from SUX is always flight number 123; there may be a flight 123 every day from SUX at 10:30 a.m.), flight number alone will not uniquely identify a flight. There will never be two flights with the same number on the same day (e.g. there can only be a single flight 123 on any given day).
Employees: The number of people making up the flight crew (both cockpit crew and flight attendants) varies from flight to flight. Personnel information consists of an employee number (unique), a name (first and last), an address (street address, city, state, ZIP), a phone number (just one), a job title, and a salary. Cockpit crew (pilot, copilot and navigator) are special cases of employees since the airline also keeps information about the types of aircraft they are qualified to fly. The only additional information that the airline keeps track of for a flight crew person is whether that person is qualified on a particular aircraft type or not. An employee may be qualified to fly more than one type of aircraft (e.g. employee 12345 is qualified to fly a 747 and a 757).
Aircraft: Aircraft information consists of a serial number (unique), the type of the aircraft, and the service record for the aircraft. Serial numbers are 7 digits long. Aircraft type is a 5-character codeó2 letters to identify the company and 3 more characters to identify the model, e.g. Bo747). A given aircraft may have many service events. The aircraft type information includes the aircraft type, (e.g. Bo747 or DC010), the manufacturer, the maximum flight distance, the fuel capacity and the number of seats available. The airline may have many aircraft of a given type (e.g. aircraft 1234567 is a Bo747, and aircraft 1234568 is also a Bo747). All of the aircraft of a given type has the same manufacturer, maximum flight distance, fuel capacity, and number of seats. The service record contains an entry for each time the aircraft was serviced. An aircraft can be serviced many times, but a new one will not have any service entries. A service entry includes a date, the number of kilometers flown since the last service entry, the type of service performed on the aircraft, and a description (memo) of any problems found. An airplane cannot have more than a single service entry for a given date. All information about the service performed on that plane on that date will be in the memo field, regardless of how many individual repairs were performed on that date.
Airports: Airport information consists of the airport code (a 3-letter identifier, e.g. SUX), the name of the airport (e.g. "O'Hare"), the city, the country, the number of runways, and the types of aircraft that can land at the airport. Many types of aircraft may be able to land at a given airport.
The airline needs to be able to:
The project is to be done individually. The database is to be implemented using Microsoft Access. The project will be done in 4 parts:
1. Create the conceptual design (see project overview)
2. Create the relational design (see project overview)
3. Build test database and queries
4. Build forms and reports
Build a test database in Access for
the relational design created in the part 2 of the project. Ensure that there
is sufficient data in the database to provide reasonable answers to test
Design in SQL, and implement using Access, the following test queries. All queries must use parameters entered by the user. The fields that are underlined and red below represent the parameters in a query. Use the names given in bold for your queries.
in a report that includes the following (all in a single Word document):
Put the database file in the drop box.
Design, and implement using Access, two of the forms needed for the Airline Database:
Design, and implement using Access, the following reports:
Hand in a Word document (hard copy) which includes the following:
Put the database file in the drop box.