Airport-Database

Assignment#2 @ Database Systems Course

ERD Description

Tool Used: ERD Plus
Min-Max notation is used to model constraints.

Entities and Relationships:

• Airplane has total participation with Plane_type and Hangar.
• One Airplane will have only one Plane_type.
• Airplane will have only one owner but one Owner can own many Airplanes. This relationship has attribute of purchase date (Pdate).
• One to many Airplanes can be stored in one Hangar.
• Service is supposed as a weak entity. Airplane has identifying relationship with Service.
• A person can be an Employee, a Pilot or an Owner. Disjoint specialization is depicted here.
• An Employee has total participation with maintaining Service.
• One Employee can work on many Plane_types. Total Participation between Employee and Plane type exists.
• A Pilot can fly many plane types. There is Total Participation between Pilot and Plane_type.
• An Owner can either be Person or a Corporation but not both. Hence, Union relationship is depicted for Owner entity.
• An Owner has total participation with Airplane.

Some Challenges:

• Modelling the specialization relationships to relational database.
• Modelling Service as a weak entity.

Solutions:

For specialization; person, corporation, and owner tables are made. Where Owner contains the ssn of person owner and name of corporation owner.
When person is owner, the Corporation Name column is NULL and vice versa.
For weak entity, the primary key of Airplane (on which service is dependent) was included as foreign key.

Relational Database Description

The database relational diagram has been provided. Each entity and relationship has been mapped to a table.
Some challenges were faced during Query 13, 14 and 18. These were solved by adding multiple joins, subqueries and using union.

A View of the Database

Authors

• Vaneeza
• Minha

License

This project is licensed under the MIT License.