Many to many relationships 5

[cjw2]

Hi could anyone clarify this for me??

I am working on a d base project for uni, i not asking for a solution just an insight into proper d base design etc, the d base has the following entities

Flight(flight_Reg(pk), date, time, destination airport, arrival airport, stopover airport)

Airport(city(pk),country(pk),no_Runways)

Aircraft(reg(pk),engine,make, etc..)

i have identified a many to many relationship between airport and stopover in flight as a flight can have many 0, 1 or more stopovers.

My question relates to the destination and arrival airport in flight.
While these are both airports they can only ever have one airport as the attribute therefore it i a one to many, or it could also be read that the flight has more than one airport in the entity thus it is a many to many relationship and will then be decomposed into two separate entities arrival and destination airport.

What is the correct way of reading problems like these???

 

[mrdenny]

The destination airport would be a 1 to many, as would the arrival airport.

Might I make a design sugestion.
Make another couple of tables:
Flight_Airports (Flight_Reg, StopType_ID, Airport)
StopType (StopType_ID (pk), StopType_Desc)

Stop Type would have records for Departure, Arrival, Layover. Then for each airport the flight connects to you have one record in the Flight_Airports table. Each flight will have at least 2 records. This allows you to have flights with as many layovers as needed without having to worry about changing your table design.

Did I answer your question that you asked?

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[SQLSister]

My first thought on this is that arrival and destination airport are clearly two different things and neither will have more than one value for a particular flight. There will never be more than arrival and departure airport and both pieces of data must be filled in. Therefore they should be in the same table as the rest of the flight information.

Stopovers, however have one to many relationship to the flight and should be in a related table which will be joined to the main table using a left join since there may be no stopovers.

Now if you need the airport information for both arrival and departure, you will need to join to the airport table twice, once for departure and once for the arrival. You may also need to join yet again for the stopover table.

In this case, It might be best to treat each leg of the flight separately and have a flight table with the flight number (and other information such as airline) and a flight leg table that would have the start and stop point for each leg and other information about the leg such as times. Inthis case every flight would have at least one entry inthe leg table and then you would only have to join to the airport table once to gain that additional data. It also makes it easiest to show the legs of the flight in tablular form.

Of course if you are doing itineraries for indivduals rather say scheduling aircraft and crews, you will need an itinerary table and an iteinerary segment table to display the various flight legs that the individual is flying on.

Now that I think on it some more, I think I prefer my second suggestion fo the flight table and the related flight leg table. I think it will give you the most flexible and easiest to query and possibnly most efficent way to query. And the easiest to expand to include the actual individual itineraries or crew/airplane itineraries if they cross flights over a period of time

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[johnherman]

Another design concept (option) to consider is that the stopover is really another arrival and destination, especially if people or cargo can board or debark at the stopover. In this case, a flight on a particular date has multiple arrivals and destinations and the primary key of the flight table becomes flight number + departure airport + arrival airport.

Then the total flight is arranged using self joins using the flight number and departure airport = arrival airport, etc. This design also allows aircraft flights to cross dates without unnecessary difficulty.


-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[cjw2]

Thank's for all the help has made things alot clearer and has made me realise that there always a number of different solutions or approaches to a problem.
Thanks again.

 

[willir]

cjw2

I have not "visited" this site for a while.

My approach would be to treat a flight as separate tasks in a one-to-many.

tblAirport
AirportCode - primary key
City - some cities can have more than one airport
AirportType - international (with customs), local, freight

tblAirLine
AirlineCode - primary key
AirlineName

tblAircraft
AircraftID - primary key
AircraftClass
Manufacturer
ModelNo
SeatingCapacity
FuelCapacity
MaxDistance

tblFlight
FlightID - primary key
Departure - foreign key to tblAirport.AirportCode
Arrival - foreign key to tblAirport.AirportCode

tblItinaryPlan
FlightID - foreign key to tblFlight.FlightID
AirportCode - foreign key to tblAirport.AirportCode
SortSequence - small interger
ItinaryType - departure, arrival, stopover, layover
ScheduleID - foreign key to tblSchedule

Note that this is part of a 1:M between tblFlight and tblItinaryPlan. I have treated the departure, arrival and any stop or layovers as one component of the trip. The start and end points are included tblFlight, but they still can be treated as records in the itinary -- I feel it makes a much nicer solution.

Okay, now for a few quirks
- Airlines can "share" a flight. United 100 and AirCanada 2000. Note that they can have different carriers and they will use different flight numbers when booking their flight BUT they are still the same flight. How do you accommodate this?
- tblItinaryPlan is just a plan or template. How and what do you need to include for the actual itinary?? Do not forget runways (which can change for each flight).
- I was vague on the schedule. This is also a very important component. Keep in mind that you will have a "planned" schedule and what happens in real life. Will you want to track on-time and reasons for delays?? (What would a business manager want?)

You can get more complicated as you dive into this...
- seating capacity
- aircraft type and distance to travel with refueling
- international, local and freight flights

Richard

 

[ceh4702]

You could think of this as a part that makes up a larger part. In other words flight A is made up of parts Sub-flight B + subflight C. They have a parent Child Relationship. The Subflight has the destination and the departure.

If a flight Number is used you can both get on and off in multiple areas, so both the departure and the arrival have a many to many relationship. You can get on at point a and get off at point b. You can get on at point b and get off at point c. You can get on at point a and get off at point c.

The times of the departure and arrival depend on the both the location, the flight, and maybe also the date if it is a special flight. Someone could have a junket or a special contract that is a one time only flight.

Flight Number has locations
Locations have arrivals and departures
Arrivals and Departures
have dates and/OR times or just DATE/TIME OR DAY OF WEEK
and times.
Flights can also have layovers where you land to refuel and you get back on the same flight. i.e. stop on international flight to refuel in greenland or hawaii.
Itenerary can be multiple flight numbers and you can have a layover between 2 separate flight numbers.

If you do not like my post feel free to point out your opinion or my errors.