Entity Relationship Diagram - Final Physical Data model Review

Question

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!! Entity Diagram

Answer 1

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.

Answer 2

This is a star schema used in data warehousing -- Kimball methodology, aka dimensional modelling -- a central fact table surrounded by dimensions. This is a specific methodology used in OLAP applications, optimised for a very specific type of queries. See this example on SO.

In this methodology data integrity is enforced by the ETL (extract-transform-load) process, so those foreign keys you mentioned are not present. In many cases even the foreign keys in the fact table are logical only -- not enforced, as explained here.

Given your questions, you should be looking for an OLTP (online transaction processing) example, where the "classic" relational database design applies -- there are many examples on SO and here on DBA SE for example something like this one or this.