ERD for airports and routes database

Question

I am making designing a database of airports and how they are linked to each other via route. Idea is simpler version of openflights.

This is my ERD:

It has four entities namely: Airport, Route, Airline and Airplane. Relations are as follows: binary relation(departure and destination) between airport and route, Airline serves that route and finally airline has a airplane which is usually used on that route.

I am not sure of validity of entities involved and their relations. It would be nice if someone can point to my mistakes.

Answer 1

I would not call this a routing table.

Routes have way points and tracks, which are subject to change. For example, the route from KFJK to KORD is COATE Q436 EMMMA WYNDE5 (these are waypoints and WYNDE5 is the arrival at O'Hare).

Your diagram is more of a destinations table. Most flights are single leg ("direct") and for multi-leg flights you need to track those separately.

I would track flights (which have a unique number) instead of your "route number" variable. A flight can be flown by different equipment. For example, the same flight can be flown in a 777 or a A380.

Many airlines fly the same route but use different flights; each flight consists of the following:

1. Unique number
2. Aircraft Type
3. Origin
4. Destination (these can be multiple or intermediate).

The route flown is predefined. Have a look at this table of flights between Kennedy International and Chicago O'Hare.

If you click on the ident column (this is the flight number), you'll see the route flown for that flight.

I would make the following changes:

1. Remove 'speed' from the aeroplane table; and rename this table to 'equipment'.
2. Rename 'alias' to 'ICAO' and add their ICAO code there (its three letters).
3. Airlines don't have routes; so I would rename routes to 'destinations'.
4. Airports don't have route numbers; so I'm not sure what that is doing there.
5. Airports have two main identifiers, the ICAO identifier code (4-letters) and the IATA identifer (3-letters). I would add both of these.
6. I would rename route number to 'flight number', and then add the actual flight number to it; with each leg entered individually. This will take care of your multi-leg (multi-stop) flights.
7. I would add a flight_equipment table to have a link between each flight, and which equipment is used. Right now there is no link between the flights and the airplanes.

Answer 2

Several questions come to mind:

• Route
  • What are are_No, Dep_air and Dest_air?
  • no_of_stops: Is the number really sufficient or do you need the stops?
  • Why is ends_ad and starts_from and intersection? (hope I've read this right?)
• airport
  • Why does it have a route_No?
• Route < has > airline
  • Is this really M-M? Wouldn't each airline operate their own routes even if they use the same airports?
• airline
  • What is modelNo?
  • no_of_planes: You already got that information implicitly, usually no need to store it again.
  • aeroplane: Are these the actual airplanes (e.g. one airline would have several instances of Airbus A380?). If so, an additional table defining the types might make sense.

General:

• Make sure to use consistent naming:
  • Case: Route vs airline
  • Avoid abbreviations (personal opinion): Dest_air vs destination_airport
  • Consistent use of underscores: modelNo vs route_No

Edit: Since you decided to ignore intermediary stops, this is what your model could look like:

• airline
  • airline_id (PK)
  • name, alias, ...
• airline_aeroplane
  • airline_aeroplane_id (PK)
  • airline_id (FK)
  • model, make, capacity, ...
• airport
  • airport_id (PK)
  • name, city, country, ...
• route
  • route_id (PK)
  • route_number, number_of_stops, ...
  • departure_airport (FK)
  • destination_airport (FK)
• airline_route
  • airline_id (FK) (PK)
  • route_id (FK) (PK)