Tri State Airlines Database

Tri State Airlines $($TSA$)$ is a small air transportation company. The company wants to build a database which

would keep up$-$to$-$date information on airplanes, flights, pilots, passengers, reservations, and any other related

items. The major features of TSA's operation which may be relevant for the design of this database are

described below.

Tri State has several airplanes types of varying passenger capacities and flight ranges. Each type is identified

by a code. A brief type description should also be stored for each type.

There are one or more airplanes of each type in the airline's fleet. Every airplane has a unique serial number.

The airplanes undergo periodic service$/$maintenance operations. The dates for the last service and the next

scheduled service should be kept in the database.

A pilot is certified to fly one or more airplane types. For any pilot, the following data should be stored: a unique

pilot number, name, telephone number, email address, and mailing address.

A flight, identified by a unique flight number, has a city of origin and a city of destination. departure time from

the city of origin, and arrival time in the city of destination are recorded for each flight.

Each flight can be scheduled on different dates. An actual scheduled flight has a date, an assigned airplane,

and one pilot, besides the flight number. A scheduled flight is identified by schedule flight ID$.$

Passengers are booked on scheduled flights. A passenger is identified by a passenger ID$.$ TSA keeps the name

and telephone number of each passenger. The mailing address of the passenger should be stored as well.

Hint: All entities have been identified for you with bold font. You don't need to show foreign keys

and associated entities on your ERD.