Draw ER diagram using Chen's notation

Australasia AirTaxi (AAT) operates a fleet of aircraft under the Civil Aviation Safety Authority (CASA). AAT headquarter is based in Sydney and the aircraft are available for air taxi charter operations between Australia and South Asia.

Each air taxi trip is reserved by only one customer and it yields revenue for AAT. This revenue is generated by the charges a customer pays upon the completion of a flight. The flight charges are a function of aircraft model used, distance flown, waiting time, special customer requirements, and crew expenses.

Payment: The payment is done as follows:

A customer who does not have an AAT credit authorization has to pay the full amount prior to the trip. Any reservation not paid within 10 business days is automatically cancelled.

A customer who has an AAT 30-day credit authorization has to pay the total in full within 30 days of the completion of the flight.

Customers who have an AAT Platinum account can pay a part of the charter bill and charge the remainder to the account, they can also charge the entire bill to the account. The charge amount may not exceed the available credit.

Customers may pay all or part of the existing balance for previous trips at any time. Such payments are not necessarily tied to a specific trip.

All customers mileages charge includes the expense of the pilot(s) and other crew required by the contract. However, if customers request additional crew not included in the contract, those customers are charged for the crew members on an hourly basis. The hourly crew-member charge is based on each crew members qualifications.

The distance flown charges are calculated by multiplying the distance travelled (mileage) by the aircraft models travelling charge.

Crew assignments: The database must be able to handle crew assignments. Each trip requires the use of an aircraft, and a crew flies each aircraft.

The smaller, piston-engine charter aircraft require a crew consisting of only a single pilot.

All jets and other aircraft that have a gross take-off weight of at least 12,500 pounds require a pilot and a co-pilot.

Some of the larger aircraft used to transport passengers may require flight attendants as part of the crew.

Some of the older aircraft require the assignment of a flight engineer.

Larger cargo-carrying aircraft require the assignment of a loadmaster.

The flights aircraft waiting charges are computed by multiplying the hours waited by the models hourly waiting charge. Crew expenses are limited to meals, lodging, and ground transportation.

AAT database must be designed to generate a monthly summary of all trips, expenses, and revenues derived from the records. Such records are based on the data that each pilot in command (PIC) is required to record for each trip: trip date(s) and time(s), destination(s), aircraft number, pilot data and other crew data, distance flown, fuel usage, and other data pertinent to the charter flight.

Such trip data is then used to generate monthly reports that detail revenue and operating cost information for customers, aircraft, and pilots. Most pilots and other crew members are AAT employees; in some cases, AAT uses contract pilots and crew members

Operations are conducted under a strict set of requirements that govern the licensing and training of crew members. For example, pilots must have earned a commercial transport license that requires appropriate ratings, which are specific competency requirements.4

Pilot Licenses: Although pilot licenses and ratings are not time limited, exercising the privilege of the license and ratings requires both a current medical certificate and a current Checkride. The following distinctions are important:

The medical certificate may be Class I or Class II. The Class I medical is more stringent than the Class II, and it must be renewed every six months. The Class II medical must be renewed yearly. If the Class I medical is not renewed during the six-month period, it automatically reverts to a Class II certificate. If the Class II medical is not renewed within the specified period, it automatically reverts to a Class III medical, which is not valid for commercial flight operations.

A Checkride is a practical flight examination that must be successfully completed every six months. The Checkride includes all flight manoeuvres and procedures.

Nonpilot certificates: Nonpilot crew members must also have the proper certificates to meet specific job requirements.

For example, loadmasters need an appropriate certificate, as do flight attendants.

Crew members such as loadmasters and flight attendants may be required in operations that involve large aircraft with a takeoff weight of more than 12,500 pounds and more than 19 passengers; these crew members are also required to pass a written and practical exam periodically.

AAT is required to keep a complete record of all test types, dates, and results for each crew member, as well as examination dates for pilot medical certificates.

In addition, all flight crew members including pilots are required to submit to periodic drug testing; the results must be tracked as well.

Many crew members have licenses and certifications in several areas. For example, a pilot may have a loadmaster certificate. However, a pilot cannot be assigned to be a loadmaster. Similarly, a flight attendant may have earned a commercial pilots license but cannot be assigned as a pilot for a trip. These certifications must be tracked in the database.

Recurrence Training: Pilots and other crew members must receive recurrence training appropriate to their work assignments. Recurrence training is based on an approved curriculum that is job specific. For example, pilot recurrence training includes a review of all applicable flight rules and regulations, weather data interpretation, company flight operations requirements, and specified flight procedures. AAT is required to keep a complete record of all recurrence training for each crew member subject to the training.

AAT is required to maintain a detailed record of all crew credentials and all training. The company must keep a complete record of each requirement and of all compliance data.

To conduct a flight, AAT must have a properly maintained aircraft available. A pilot who meets all of the CASAs licensing and currency requirements must fly the aircraft as Pilot in Command (PIC). For aircraft that are powered by piston engines or turboprops and have a gross takeoff weight under 12,500 pounds, single-pilot operations are permitted as long as a properly maintained autopilot is available. However, even if single-pilot operations are permitted, many customers require the presence of a co-pilot who is capable of conducting the flight operations.

Aircraft Leasing: AAT leases turbojet-powered aircraft, which are required to have a crew consisting of a pilot and co-pilot. Both the pilot and co-pilot must meet the same licensing, ratings, and training requirements.

AAT also leases larger aircraft that exceed the 12,500-pound gross takeoff weight. Those aircraft might carry enough passengers to require the presence of one or more flight attendants. If those aircraft carry cargo that weighs more than 12,500 pounds, a loadmaster must be assigned as a crew member to supervise the loading and securing of the cargo. The database must be designed to meet the anticipated capability for additional charter crew assignments.