The Computer Science Department frequent flyers have been complaining to the Local Airport officials about the poor organization at the airport. As a result, the officials 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 stationed and maintained at the airport. The relevant information is as follows:
- Every airplane has a registration number and a specific model.
- A number of technicians work at the airport. You need to store the name, SSN, address, phone number, and salary of each technician.
- Each technician is an expert on one or more plane model(s), and his or her expertise may overlap with that of other technicians. This information about technicians must also be recorded.
- Traffic controllers must have an annual medical examination. For each traffic controller, you must store the date of the most recent exam.
- Each employee is uniquely identified by a social security number.
- The airport has a number of tests that are used periodically to ensure that airplanes are still airworthy. Each test has a Federal Aviation Administration (FAA) test number, a name, and a maximum possible score.
- The FAA requires the airport to keep track of each time a given airplane is tested by a given technician using a given test. For each testing event, the information needed is the date, the number of hours the technician spent doing the test, and the score the airplane received on the test.
Draw all the tables with relationships for the given system. Also specify the name and type of the fields, primary and external keys. No need to write SQL code for the tables; just draw the Designer View of the database. You can draw the diagram either on paper or by using MySQL.
Below is an example of a database designer view for a university:
university enrolled STDID: int(11) CourseName: char(10)
vun teacher TeacherID: int(11) TeacherName: char(30) Department: char(30)
vunive ID: int(11) Name: char(30) Department: char(30) StLevel: char(20) Age: int(11)
O university course Name: char(10) CourseTime: time Room: char(10) TeacherID: int(11)
