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
