Simple Airline Ticket Reservation System Using SQL server Transaction & Stored Procedure |
您所在的位置：网站首页 › 一体机相机镜头怎么拆 › Simple Airline Ticket Reservation System Using SQL server Transaction & Stored Procedure |
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.' ENDScript 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. |
【本文地址】
今日新闻 |
推荐新闻 |