CSCI345: Airline Project

Updated 2018.03.14

Problem Description

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:

Project Requirements

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

3. Build test database and queries

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 queries (2% 1%).

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.

  1. Create the passenger list for a particular flight. Input will be the primary key for the flight. Return each passenger's last name, first name in alphabetical order. (4% 5%) Name this query 01FlightList.
  2. Find all flights from a particular airport between any two dates (including both dates). Input will be the airport code, the start date, and the end date. Return flight number, departure date, time, and destination in order by date, time. (4% 5%) Name this query 02FlightsBetweenDates.
  3. Find all flights booked for a particular passenger. Input will be the passenger ID. Return the flight number, date, time, and destination, in order by date, time. (4% 5%) Name this query 03FlightsForOnePassenger.
  4. Find all pilots qualified to work on a particular flight. Input will be the key for the flight. Return the pilot's last name and first name, in alphabetical order by last name, first name. (4% 5%) Name this query 04QualifiedForFlight.
  5. Find the service information for a particular aircraft. Input will be the key for the airplane. Return all of the service information and date, in order by date. (4% 5%) Name this query 05ServiceRecord.

Hand in a report that includes the following (all in a single Word document):

  1. (3%) A printout of each of the above queries in SQL followed by a dump of the result of each of the queries on the test database (put the results below their corresponding query) and specify the parameter(s) that you used and display the results for that parameter value. There should be at least two tests of each query
  2. A printout of the relationships in the database (Database Tools | Relationships). (1%)

Put the database file in the drop box.

4. Build forms and reports

Design, and implement using Access, two of the forms needed for the Airline Database:

  1. A form to view the data for one passenger at a time. The user should be able to view a passenger, to enter a new passenger, and to update an existing passenger's information. One passenger should appear on the form at a time. (6%) Name this form 01Passenger.
  2. A form to view the passenger list for a flight and to add a new passenger to that flight. The user should be able to scroll through the flights and view a subform with all of the passengers for that flight. The passenger list should list the passenger ID, first and last names, and phone number. (6%) Name this form 02FlightLists.

 

Design, and implement using Access, the following reports:

  1. A summary of flight crew assignments between any two dates specified by the user ordered by date and then flight number. The date should appear, followed by a list of flight numbers, with the crew members (employee ID, first name, last name) assigned to that flight on that date listed below each flight number. (6%) You will need to base this report on a parameterized query. Name this report 01CrewAssignments.
  2. A summary of aircraft information for all aircraft ordered by serial number. Include both generic information (aircraft type, seating capacity, etc.) and specific information (repair record). (6%) Name this report 02AircraftReport.

 

Hand in a Word document (hard copy) which includes the following:

  1. An example of each form and description of how the form is to be used.
  2. A printout of each report.

Put the database file in the drop box.