Final Exam

EAGLEONE is an aircraft charter company that supplies on-demand charter flight services, using a fleet of aircrafts throughout the US from its airport bases at Chicago, NY, LA and Dallas. It has rapidly expanded in last 10 years, however, it maintains all its data in manual registers. In recent times, it has been cited many times by FAA for failure to provide mandatory reports and a recent audit found many failures in meeting FAA requirements for certified staff, regular inspection and maintenance of aircraft. Various history reports on flying, inspection and required service are to be submitted to FAA each quarter for each Aircraft and Pilot. FAA requires jet aircraft to have a pilot and a co-pilot, while propeller type aircraft can have one pilot.

Customers charter the aircraft for their requirements. Each rental transaction booking may involve one or more trip segments. The total charge for a rental is determined based on the trip segments and flying and waiting hours for each segment. At the end of a rental transaction, customers are presented an invoice based on the actual number of hours in the included trip segments, which may be different from the initially scheduled hours. When the invoice is presented, customers pay cash, use a credit card, or write a check. For each trip segment, the company needs keep track of the distance covered, gasoline used, and flying and waiting time in addition to the take off and landing airports. The invoice charges are based on the number of the hours flown, waiting period and Aircraft type. The applicable rates for each model are stored in a rate table.

Company has many Aircrafts for each model. Each aircraft is identified by a (unique) registration number assigned by FAA. For each model the company keeps track of all recommended service and inspection requirements for hydraulics, engine, brakes, tires, and electronics. These requirements that depend on the model are specified in terms of numbers of hours flown and number of landings made by the aircraft. For example, a Beechcraft912 model Engine is inspected every 100 flying hours, hydraulics are checked every 200 hours, brakes are checked every 5 landings, tires are checked every 10 landings, and electronic instruments are checked every 500 hours or 50 landings, whichever occurs first. Servicing of engine is mandatory at 400 hours.

In addition to the above requirements, the company also keeps track of the following information for each Aircraft. Whenever an aircraft is inspected, it is necessary to keep track of the name of the certifying inspector, date and time of inspection, and comments for each area of inspection. Whenever a service is done on the aircraft, it is necessary to record the name of mechanic and the inspector along with date and time and remarks on the maintenance performed.

The company also keeps track of all its employees including pilots (beside normal employee data it should also include hours flown, Pilot license No, License type and date of expiry), inspectors (data should include date of certification, expiration date, and technical specialties), mechanics (data should include manufacturer’s certification No, date of certification, date of expiration, and aircraft models covered).

Your job is to design an appropriate system for this business. And then create a limited prototype with at least six tables. You are required to submit the following:

  1. Process decomposition for the entire system.
  2. E-R model for the entire system (with normalized table structures). Wherever relevant include class/subclass structures in your E-R models.
  3. Process to entity matrices for the entire system
  4. Context diagram
  5. List of all reports
  6. Procedure for booking a rental in EPC form
  7. The implemented system (with at least 6 tables) should keep track of relevant data and produce any 5 reports out of the following.
  1. List of pilots that are due for recertification in the next quarter.
  2. Annual statement for customers, covering their rentals, trips, and total billing for the year.
  3. List of Aircraft on rent, ready and waiting for customers, and under repair.
  4. List of Airports touched in last one year.
  5. An invoice for a customer for a rental made.
  6. Total hours flown by each aircraft in the database.
  7. Any other useful report
  8. A screen showing available aircraft and pilots for a future rental based on location and dates needed.

Create your own sample data. Each table should have at least 10 or more data lines. Make helpful queries, data input forms, and reports for users to use directly or through a menu. Must have at least one report with two tables joined.

  1. Submit hard copies of all documents including tables, reports, diagrams, and a CD/storage media with the Access system along with instructions to run the program in readme.txt file. Make sure you provide all the documents that will help in understanding and maintaining the system.