Airport Management Information System
In this project, you are assigned to design, organize and implement a RDBMS for Ercan Airport Management that will store, manipulate and retrieve all airport related information. The database you will design will organize the information about all the airplanes stationed and maintained at the airport as well as:
The DB should store detailed information about the airplanes (i.e. plane_no, model_no, capacity etc.),
The location airplanes are located when not flying (i.e. hangar_no, location etc.) should also be stored. However, airplanes are not staying in hangars forever, your database should store IN and OUT dates/times.
The airport has a number of tests that are used periodically to ensure that airplanes are still airworthy.
It is also required 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.
There are different types of workers in the airport such as traffic controller, airport employees and technicians (testing and repairing aircrafts).
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.
All airport employees (including technicians) belong to a union. You must store the union membership number of each employee. You can assume that each employee is uniquely identified by a social security number.
A brief description and assumptions about tables and relations,
- ER Diagram (your design should contain at least 8 tables),
- Relational Data Model,
- Data Definition Language Details (DDL), including all necessary constraints,
- Data Manipulation Language (DML),
- Normalization Steps (upto 3NF),
- 15 queries retrieving statistical information to help management (use the functions/methods you learned, such as join, subquery, group by, to_char etc.)
These are the minimal requirements which means they have to be implemented, but implementing only the requirements that is mentioned above will not be enough to get full mark. You should extend this design and create a real-life database using MySQL or any other database program.