A small airport:

We require a data base for a small airport. The data base should track airplanes, pilots, hangars, airplane types, mechanics and airplane owners.

The airport maintains the usual data,on airplanes: registration number, on airplane types: model, capacity and weight ,on hangars: hanger number, location and capacity,on pilots: name, license nr, address, on mechanics: name, phone nr, address and employee ID, on airplanes owners: name, address and phone nr.

Think about the entities and their attributes. You should first determine what entities want to track. Next determine what attributes are needed for each entity, and what relations exist between these entities. You should draw an E-R diagram to show these relations. Be sure to indicate all keys. As a final step write the queries in SQL. You will be also be expected to turn in your E-R diagram, SQL code and a test of your database with some test data that you may make up.

Also be sure to make reasonable assumptions, you will be graded on your design as well as other factors.

Queries:

1. What is the capacity of a particular airplane type?

2. What is the weight of a particular airplane?

3. Which type of airplanes can a particular pilot fly?

4. What airplane models are stored in a particular hangar?

5. Which hangar is a particular airplane stored in?

6. List all pilots that can fly a particular airplane.

7. List all the mechanics that can service a particular airplane.

8. List all mechanics that can service a particular airplane type.