Entity Relationship Diagram – Final Physical Data model Review

database-design

I am new to this programming world and I am learning drawing ER diagram for a small airport management suite and use the below physical data model in my project report before that, I want to verify If the the following are correct or not.
(i) Relationships
(ii) Primary Keys and Foreign Keys
(iii) In Actual_flights_ there is a aircraft_id which must be FK but it was not mentioned as FK against it in the diagram, same with pilot_id.
(iv) In table Hangers, Airport_ID is termed as FK but there is no relation between Hangers and Airports_ table.

Hence, I would like to verify before using the same ER. For your reference. I am currently developing database in access. Please help me!!
enter image description here
Entity Diagram

Best Answer

Yes, aircraft_id should be a FK to the Aircraft_ table, just as pilot_id should refer to the Pilot_ table. There are other problems, such as there is no way to limit pilots to only aircraft they are rated to fly. Any pilot may be thrown in with any aircraft. And not all airports can handle all aircraft. Many cannot handle the larger aircraft (know as heavies). One of the attributes I would think important for airports is how many heavy-capable runways they have. Come to think of it, how many runways total.

Look into other relationships. Write them out in plain English, such as: "Each aircraft has one or more owners." This shows a relationship between aircraft and owners that is not represented in your schema design.

The actual_flight_ table relates a pilot with an aircraft, thus the need for FKs, and other attributes into a, well, actual flight. This can also be stated in plain language so you can understand the entities and their relationships.