Simple Airline Ticket Reservation System Using SQL server Transaction & Stored Procedure

In this article, we’ll go through the design logic and steps to build a simple airline ticket reservation system by the help of transaction and stored procedure in SQL server.

Transaction is a unit of work that makes data modification against a database. It is a group of SQL statements that are all committed together or none of them are committed. The design of transaction is to ensure data integrity and consistency (as well as the ACID properties). Transaction can be used in real-world scenarios such as bank transactions, airline reservations, remittance of funds and so on.

Business scenario & Logic of design:

Suppose we have a flight schedule table, a customer table, and a booking table. For simplicity purpose, we suppose each flight can only have one customer. If a flight is occupied, then no other customer can access the same flight. If there is no exact matching flight (in terms of date, leave_from, going_to) or there is no available flight (state_sold = 1) for the customer, then no information will be inserted to the database. If all conditions match, we will insert the customer information into the customer table, update the flight state from 0 to 1, and insert the booking record in the booking table. All the above operations (1 Insert, 1 Update, 1 Insert) have to be committed successfully at the same time. Otherwise, if there is any error, the transaction has to be rolled back

Script 1: Creating the base tables: flight, customer & booking

CREATE TABLE flight
(flight_no INT PRIMARY KEY,
date DATE,
leave_from VARCHAR(20),
going_to VARCHAR(20),
state_sold BIT)

CREATE TABLE customer
(cust_id INT PRIMARY KEY,
cust_name VARCHAR(30))

CREATE TABLE booking
(flight_no INT, 
date DATE,
cust_id INT)

INSERT INTO flight VALUES
(1, '11/14/2020', 'San Francisco', 'New York', 0),
(2, '11/14/2020', 'San Diego', 'San Jose', 0),
(3, '11/14/2020', 'Dallas', 'Boston', 0),
(4, '11/14/2020', 'Denver', 'Chicago', 0),
(5, '11/14/2020', 'San Francisco', 'Sacramento', 0),
(6, '11/14/2020', 'San Luis Obispo', 'Portland', 0);

Script 2: Create a stored procedure with a transaction inside

CREATE PROCEDURE sp_air_tran 
@newcust_id INT,
@newcust_name VARCHAR(30),
@date DATE,
@leave_from VARCHAR(20),
@going_to VARCHAR(20)

AS
DECLARE @flight_no INT
DECLARE @inserr INT
DECLARE @upderr INT
DECLARE @maxerr INT
SET @maxerr = 0

 
-- Add a booking transaction if it matches the date, starting place and destination
IF EXISTS (SELECT date, leave_from, going_to, state_sold
FROM flight
WHERE date = @date
AND leave_from = @leave_from
AND going_to = @going_to
AND state_sold = 0)
BEGIN
	PRINT 'There is available flight'

	BEGIN TRANSACTION
	-- Add a customer if is isn't on file
	IF EXISTS (SELECT @newcust_id, @newcust_name INTERSECT SELECT cust_id, cust_name FROM customer)
		PRINT 'There is already an existing record for this customer.'
	ELSE
		INSERT INTO customer (cust_id, cust_name) VALUES
		(@newcust_id, @newcust_name)

		-- Save error number returned from Insert statement
		SET @inserr = @@error
		IF @inserr > @maxerr
		SET @maxerr = @inserr

	-- Update the flight state of sold
	UPDATE flight
	SET state_sold = 1 
	WHERE date = @date
	AND leave_from = @leave_from
	AND going_to = @going_to

	-- Save error number returned from UPDATE statement
	SET @upderr = @@error
	IF @upderr > @maxerr
	SET @maxerr = @upderr

	-- Give the value to the variable
	SET @flight_no = (SELECT flight_no 
	FROM flight WHERE date = @date
	AND leave_from = @leave_from
	AND going_to = @going_to)

	-- add a booking record
	INSERT INTO booking (flight_no, date, cust_id) VALUES
	(@flight_no, @date, @newcust_id)

	-- Save error number returned from Insert statement
	SET @inserr = @@error
	IF @inserr > @maxerr
	SET @maxerr = @inserr

	-- If any error occurres, roll back
	IF @maxerr != 0
	BEGIN
		ROLLBACK
		PRINT 'Transaction rolled back'
	END
	ELSE
	BEGIN
		COMMIT
		PRINT 'Transaction committed'
	END

	PRINT 'Insert error number: ' + CAST(@inserr AS NVARCHAR(8))
	PRINT 'Update error number: ' + CAST(@upderr AS NVARCHAR(8))

	RETURN @maxerr
END
ELSE
BEGIN
	PRINT 'There is no available flight.'
END

Script 3: Execution of airline ticket reservation

-- The executions are committed successfully because there are matching flights
EXECUTE sp_air_tran 1, 'Jimmy', '11/14/2020', 'San Francisco', 'New York'
EXECUTE sp_air_tran 2, 'John', '11/14/2020', 'San Luis Obispo', 'Portland'

-- The transaction is not executed
 because the flight is booked
EXECUTE sp_air_tran 3, 'Kelly', '11/14/2020', 'San Luis Obispo', 'Portland'
EXECUTE sp_air_tran 4, 'Judy', '11/15/2020', 'San Diego', 'San Jose'

-- The transaction is not executed because the flight is not matched
EXECUTE sp_air_tran 5, 'Lily', '11/14/2020', 'Denver', 'Boston'

-- The execution for the same customer booking another available flight
 is committed, however the customer table is not inserted
EXECUTE sp_air_tran 1, 'Jimmy', '11/14/2020', 'Dallas', 'Boston'

In this demo, this transaction design is not really rolled back because we have well-established IF ELSE statements to make sure only matching flight information will trigger a transaction. However, we do set up the check points after every DML statement to roll back if any error occurs. In addition, if there are any other unexpected issues, like system failure or power outage, the transaction can prevent from relevant operations executing half way through. All required operations for a reservation should be done together or rolled back properly.