Question: customers have been complaining to dane county airport officials about...
Customers have been complaining to Dane County Airport officials about the poor organization at the airport. As a result, the officials have decided that all information related to the airport should be organized using a DBMS, and you have been hired to design the database. Your first task is to organize the information about all the airplanes that are maintained at the airport. The relevant information is given as follows:
Each airplane has a unique registration number and belongs to a specific model.
There are several airplane models that are accommodated at the airport, and each one is identified by a model number (e.g., DC-10) and has a capacity and a weight. At some points of time, some models may have several airplanes at the airport, while others may not have any airplane at the airport.
All airport employees, including technicians and traffic controllers, have a unique social security number, a name, a phone number, and one or more addresses which consist of a street number, a street name, and a city name. Note that an employee cannot be both a technician and a traffic controller.
Each technician has some years of airplane-maintaining experience and is an expert on one or more airplane models, and his=her expertise may overlap with that of other technicians. However, some airplane models may not have experts at the airport.
Traffic controllers must have an annual medical examination. For each traffic controller, you must store the date of the most recent examination.
An airplane may be assigned to several fights. Each fight is associated with at least one airplane.
A fight is identified by a fight number and belongs to an airline. A fight may have many fares. Each fare must belong to one fight.
Each fare has a code and an amount. Different fares for different fights may have the same code and the same amount. However, different fares for the same fight must have different codes.
Draw an ER diagram for the airport database. Make any additional assumptions if needed, but state them clearly.