Assignment#2 @ Database Systems Course
Tool Used: ERD PlusMin-Max notation is used to model constraints.
- 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.
- Modelling the specialization relationships to relational database.
- Modelling Service as a weak entity.
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. 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.
This project is licensed under the MIT License.