Database Answers African Airways Flights

Home Ask a Question Careers Contact us Data Models First Timers Search Site Map
Airline Bookings Scheduling
Back to the Data Model
This is the Start of the User specification of the Requirements :-

A Database is required for a System to manage Bookings on an Airline in Africa.

We can identify the following 'Things of Interest' :-
  • Aicraft
  • Customers
  • Flights
  • Legs Here are the requirements. We need a database design that would meet our requirements by ensuring that at any point, we don't end up with more passengers than the bus or plane can handle. We appreciate that some checks may be done by a program outside the db design but we need the database design to be correct. A local (fictional) Airline called African Airways operates some Flights as follows:
  • Monday : NAIROBI – LONDON – AMSTERDAM
  • Tuesday : AMSTERDAM – LONDON - NAIROBI It is possible for this airline to add an additional destination after Amsterdam (so no limit to how far they can fly). The airline is operating a 10 seater aircraft. The aircraft can change any time so this seat capacity depends on the aircraft any time. Usually, airlines have different fares for each journey due to CLASSES so that should also be taken care of. SO we assume 2 classes :- 1. BUSINESS (USD 200) 2. ECONOMY (USD 150). (NAIROBI - AMSTERDAM and vice versa). We can take any amount for the shorter distances. SEATING CAPACITY The database needs to ensure that we can be able to manage the seating capacity as bookings happen for different sectors. Of course people can book NAIROBI – AMSTERDAM or NAIRBO-LONDON or LONDON – AMSTERDAM Any of these combinations should be taken care to ensure that we do not get passengers overbooked at any segment. e.g if we have 8 people booked LONDON – AMSTERDAM, then we cannot have more than 2 people booked on NAIROBI – AMSTERDAM. Although we can have 10 people booked on NAIROBI – LONDON ! We are designing an airline reservation system that needs to take care of a multi city seat capacity/booking. This would probably also apply for a bus reservation. Basically, a plane/bus starts from point A to go to point D via B and C. Each of these are sectors that can be booked independently. i.e a passenger can book any of the following; 1. A to B 2. A to C via B 3. B to C 4. C to D 5. A to D 6. Etc
  • Update on December 13th. TERMINOLOGY - The word LEG will be used to refer to a segment, sector or leg, such as Nairobi to London is a LEG. NAIROBI-LONDON-AMSTERDAM is a trip which has 2 LEGs Bernard says he is "concerned about storing 'total booked' and capacity remaining in a field value. It means when a passenger (aka PAX) books AND then cancels the booking, we have to go updating various tables instead of just doing a simple select sum to find out total booked and then do a deduction of capacity less total booked. Is that correct ? A normal flight can have as many as 12 fares from my small research usually classified between Business and Economy. Hard coding the fares will give you headache." DRAFT SQL Draft SQL for the View to calculate the total bookings and seats remaining on any Flight and any Date :- The figure of 10, for aircraft seating capacity, could be a parameter strored in a Table. CREATE VIEW Total_Bookings_VW View AS SELECT leg_name,actual_departure_time ,SUM(*) AS total_booked_head_count ,10 - SUM(*) AS capacity_remaining from Bookings ,Journey_Legs ,Legs WHERE Bookings.booking_id = Journey_legs.booking_id AND Legs.leg_id = Journey_Legs.leg_id GROUP BY leg_name,actual_departure_time

  • Barry Williams 15th. December 2008 Principal Consultant Database Answers London, England

     

    Home Ask a Question Careers Contact us Data Models First Timers Search Site Map