Hi i have a question regarding many to many relationships.

I am working on a c/work involving entities Airport, Aircraft and Flight. The flight has info including destination airport, departure airport and any stop over airports, date etc.

I have identified that the relationship between flight and airport is a many to many because the flight can have many stop over airports and the airport handles many flights.
BUT what about the attributes destination airport and departure airport i believe that these can only ever have one value i.e. one departure airport and one destination airport, but on the other hand it could be said that the flight has many airports??
does this mean if i decompose the many to many relationship that these fields (destination airport and departure airport)need to be added to the new entity??

I think you are looking at the possibility of another entity called Flight Segments.
This could possibly have the attributes flight_id, flight_segment_number, departing_airport and destination.

The flight table would have attributes departure_airport and destination_airport, with the destination referring to the final destination of the flight.

So, you'd have the entities aircraft, airport, flight and flight_segment.

Ravi

Thank's for your help
I had a simialr idea with introducing an entity route, but does this still not have the same problem as before where by the entity route then has a many to many relationship with airport? as the route will have two airports in the table?
I'm a bit unclear with many to many in this case as a flight only has one destination airport and one arrival airport but they are both airports so does this make it a many to many?

Another idea i had was to have an entity for departure airports and one for arrival airports these entities being made up of the flight_ID and the airport_ID from the respective entities. But this seems a bit far fetched to me? Also it would mean i would have to introduce a new entity to hold details of stopover airports of the journey?
How far sholud you decompose entites is wrong to crreate to many new entities?

I do not see any problems.

Here are the four entities:

Aircraft: (aircraft_id, make, ....)

Airport: (Airport_code, city, country, ....)

Flight: (Flight_id, aircraft_id (FK to aircraft), departing_airport_code (FK to airport), final_destination_code (FK to airport))

Flight_Segment: (Flight_id, segment_number, departing_airport (FK to airport), arriving_airport (FK to airport))

So you do have a many to many relationship with airport. But then, so what?

Does this capture your needs? Is it in third normal form? If the answers to these questions is yes, go ahead and start coding.

If the answer is no, find out what requirements are missing.

Any more entities than these really do not seem to add anything to the model. Remember, the simplest design that meets the needs and that does not violate fundamentals is the best starting point.

Ravi

Isn't there a redundancy in the departing_airport_code and final_destination_code in the flight relation? The airport with segment = 1 is the starting of the trip and with the max segment is the destination.

Well, I guess removing the airports from the flight wouldn't handle diversions but for the vast majority of flights you’re going to store at least 2x the amount of data. Is this primarily for the ‘intended’ flight plan and not necessarily where the plane actually went? If not, you could have it so that the flight_segment table is filled in with intended airports/legs but then have another relation for, perhaps, unscheduled stops.

Also segment is useful to easily show the route, you could also calculate it where previous_destination = current airport (that is, you can make a closure over the airports to build the path).

Just some silly thoughts, yes.

Matt,

I agree that there is redundancy in that the columns departing_airport_code and the final destination_code can be deduced from the flight segments. One could do away with them.

The other issue about closure, it seems to be similar to the problem of tracking history and contiguous dates. We want to be sure that the end-point of one segment (history or flight) is the starting point of the next. That is, they must be non-overlapping. There are many ways to handle this programmatically, but none declaratively that I know of.

For myself, I once wrote some code that would write the code for various triggers for overlapping time fields. Ought to be easy to generalize this to any other type of field, not just date-time fields.

The intended and actual flight plans are not always identical, of course. Then, depending upon what the business needs, one may choose to create a new entity for the actual flight. This need be populated only when there is deviation from the intended flight plan. A simple view would let the users/developers see the actual segments, whether these were according to original plan or differed from the intended.

Ravi

CHECK constraints

Which DBMSs actually support check constraints that look at multiple rows together? I know they are part of the ANSI standard, but Oracle for example doesn't have them.

good question, tony

I think PostgreSQL's "Rule" system might be a form of table predicate, although I haven't looked at it too closely.

Thanks again for your input v helpful.
As regards to the many to many relationship in the books i have read and at uni we have been told to decompose any many to many relationships.
I dont understand the reasons why, other than MS access does not like them, but thats the way i must do it. Thanks again for your help it good to speak to those in the know. Cheers.

To help understand why many-to-many relationships are often (almost always) decomposed using an intermediaate intersection entity, just try it out.

Design two versions of a very simple system, one in which the M:M relationship is not decomposed, and another in which it is decomposed.

See if you have any problems adding data, updating/deleting, etc. That will demonstrate why we must (or must not) decompose a M:M relationship.
Ravi

The only database system that I can think of that handles many-to-many relationships well is Pick (and its derivatives like Revelation). None of the relational systems really like many-to-many relationships.

-PatP

What an extraordinary statement! In what sense do RDBMSs not handle many-to-many relationships well? Seems to me they do it extremely well.

perhaps what pat meant (and ravi, with his undecomposed relationship) is that an m-m relationship is not implemented directly by relational databases, but rather, is implemented as two 1-m relationships to a junction or association or linking table

try implementing a m-m with just two tables to see how difficult it is