Concurrent Reservations

Assume an airline database.
A query gets the following information:
AIRLINE	FLIGHT	origin	destination	depart	arrive	capacity
DELTA	001		VTC	ORD		#:##	#:##	##
DELTA	002		PRD	RHI		#:##	#:##	##
other information is available.
Please note this is the result of a query to find available flights;
this is NOT in Normal Form. (Review question: Why not?)

For simplicity, imagine more than one person is trying to book a seat on these flights at the same time.
(Actually, in the B.C. Era, (Before Corona), millions of people were simultaneously trying to book seats on thousands of flights, simultaneously.)

In your database, you have available the following operations, and more:
Check for possible flight connections
Check for available capacity
reserve a specific seat
Get payment information
Confirm payment
Commit to flight reservation
cancel flight reservation
BeginTransaction
EndTransaction

In real life, if you let your computer sit for 10 minutes, you risk losing your reservations, and starting over. Also in real life, you can check for seating before paying, only to find no seats left after you have paid.

Your goal is to come up with an algorithm/ordering so that:
Once a transaction has committed, it does not need to be rolled back, normally.
A plane is never booked beyond capacity.
(Which happens in real life, until recently.)
No two people are ever given the same seat assignment on the same flight.
Any series of requests is maintained as serialize-able.

Given your algorithm:

A-From the perspective of the airline, what problems can occur, using your algorithm?

B-From the perspective of the passenger, what problems can occur, using your algorithm?

Don't think your problems are done, even if you somehow come up with a algorithm that is serialize-able.  
C-Assume at the last minute an airplane breaks.
(Assume the rubber band broke.) ;)

From a data base perspective, what can or should the airline do to take care of the passengers?

If you are 'live' and have a lab partner:
Each of you should independently come up with an algorithm, and then compare.
If possible, come up with a consensus algorithm.

If you are watching the recorded  lab, come up with an algorithm.  Send it to me.
If you wish, I will if possible match you with another person for comparison by email.

In light of your Database understanding, do you have any thoughts about:
should airlines overbook?
Why do airlines charge more for last minute flights?
Is there a better way to do an airline database?

DELTA is the name of an actual airline.
Used without bothering to ask permission, since it is a public corporation.
VTC ORD and RHI are codes for three actual airports; sadly, VTC airport is not in Vermont.

 AIRLINE	FLIGHT	origin	destination	depart	arrive	capacity

In current practice, a flight number determines the origin and destination but not the time of a flight.
(Multiple flights from airport A to airport B share the same flight number, even at different times or on different days.)

Below is still likely an oversimplified database concept:

AIRLINE table (full name, abbreviation, any other info about airline.)
FLIGHT table (AIRLINE, FLIGHT, origin, destination, normalDuration)
DEPARTURE table ( AIRLINE, FLIGHT,  date, time, gate)
PLANE table (planeID, make, model, serial, capacity)
FLIES (planeID,  AIRLINE, FLIGHT, origin, destination, date, time)
STATUS (planeID, statusCode, delayDeparture, delayArrival, alternateGoal)

You have to schedule the PLANES so they mesh nicely in a schedule.

And then you have to schedule the CREWS so they are at the same city where the PLANE is.
(Sometimes but not always they arrive on the plane, of course.)

And you have to deal with weather delays and cancellations, re-routing, mechanical problems, air traffic control issues, terrorist actions, and presidents getting a haircut while AirForce One sits on a runway.

Can you come up with a better set of tables?
Anyone want a job doing Database for an airline?