TicketReservationSystem

About

This is the implementation of an scalable database system for train tickets booking in postgres. It can handle multiple user accesses to the database system through concurrency control protocols. The schedules can be added and the journey plans be easily searched though the simple queries.

Setup

• Clone the repository and cd into the folder containing setup.sql
• Start the postgresql by the following commands:

sudo -s -u postgres
psql

• Now load the tables and procedures by the command

\i setup.sql

• An implementation of an application designed to query this database by mulitple conncurrent threads can be found Here

Relations

• trains (number, name)

  • number: Can be any integer between 0 and 99999. Should be unique for each train.
  • Name: A string representing name of the train. Can be atmost 50 characters long

• runs (train_number, departure_date, ac_coach, sleeper_coach)

  • train_number: Foreign key denoting number of train from trains relation.
  • departure_date: Represents date of departure of train in YYYY-MM-DD format. Date should be enclosed with ''(single quotes) while inserting any record.
  • ac_coach: Number of AC coaches on the train on that particular day.
  • sleeper_coach: Number of Sleeper coaches on the train on that particular day.

• tickets (PNR, train_number, journey_date, passenger_name, coach, berth_type, berth_number)

  • PNR: A 19 characters long string consisting of numbers only, auto-generated by the application while booking tickets. Same for all passengers booking together.
  • journey_date: Date of journey in the format YYYY-MM-DD.
  • passenger_name: Name of passenger. Can be atmost 16 characters long.
  • coach: Coach number can vary from A1-A999 and S1-S999. A denotes AC coach and S denotes Sleeper coach. Stored as CHAR(4).
  • berth_type: Can be LB, UB, MB, SL, SU
  • berth_number: An integer denoting berth number of the concerned passenger. Varies from 1-18 in AC coach and 1-24 in Sleeper Coaches.

• stations (station_code, station_name)

  • station_code: An alphabetical combination denoting each staion. Can be atmost 4 characters long.
  • station_name: Name of the station of length atmost 30.

• schedules (from_station, to_station, train_number, source_day, destination_day, SDT, DAT)

  • from_station: Denotes the station from which train can depart.
  • to_station: Denotes the station code at which the train can reach(All pairs of stations are stored to be stored in this relation)
  • train_number: Train Number as a foreign key to trains relation.
  • source_day: The day at which trains departs from the from_station. The source station has day 1 and it increses after midnight as the train progressess.
  • destination_day: The day at which trains arrives at the to_station.
  • SDT: Departure time from the from_station in HH:MM:SS format
  • DAT: Arrival time at to_station in HH:MM:SS format.

Stored Procedures and Functions

• add_train(train_number, train_name)

  • Description: Takes the number and name of train and adds the entry into the trains relation(Format of trains relations should be strictly followed)

• release_into_booking(train_no ,dep_date , num_ac_coach ,num_sleeper_coach)

  • Description: The argument names are self-explainatory. Format of runs relation should be followed. Same train cannot run twice on the same day. It creates two tables, one each for sleeper and AC coaches and fill it with seats based on number of ac and sleeper coaches provided. Also it inserts corresponding entry in the runs relation.

• book_tickets(num_passengers , coach_type , train_no , journey_date , first_try ,name1, name2, .... )

  • num_passengers: It is an integer. It can be any number. Sufficient number of names must be given at the end of arguments for the function to execute correctly.
  • coach_type: Can be 'A' or 'S' for the choice of AC and Sleeper respectively.
  • train_no: Train number in the same format as in trains relation.
  • journey_date: Date of journey chosen by the passengers in the format 'YYYY-MM-DD'.
  • first_try: Can be 'true' or 'false'. It's use is described below.
  • name1, name2... : Names of passenger in the end seperated by comma. Must be equal to num_passengers.
  • How it works
    • Output Format: Array {EXIT CODE, PNR, berth_number1, coach1, berth_type1, berth_number2, coach2, berth_type2.....}
      • The order of berth_number, coach and berth_type is same for the passenger as in the arguments.
      • If ticket is not booked due to any reason, only one element is there in the output array. i.e ARRAY {EXIT CODE}.
    • It takes the input in the format specified above. In the first attempt, first_try is 'true'.
    • First it checks whether the train is released into the booking system or not. If not booking is failed with EXIT CODE -1.
    • Then it checks whether there are enough seats(locked or unlocked) in the system. If not, booking is failed with exit code -2.
    • Then, it tries to lock the rows to book the seats. If it is not able to lock enough rows, it returns with EXIT CODE -3. If it returns with this EXIT CODE, then application should retry calling the same function again with same arguments but with first_try = 'false'. This time, it will try to lock the entire table and it is guaranteed that it will not return with EXIT CODE -3 this time.
    • If it able to acquire locks for enough rows, then booking is successful and it will return with EXIT CODE 0 and will have other details like PNR, coach and berth of all passengers in the output array. The corresponding rows will be deleted from the table so that others cannot book the same ticket. Corresponding entries will be added in the tickets relation.
    • The Exit codes and their meaning can be summarised below:

      Exit Code	first_try='true'	first_try='false'
      0	The tickets are booked	The tickets are booked
      -1	Train not released into the booking system	Train not released into the booking system
      -2	Enough tickets are not available	Enough tickets are not available
      -3	Tickets might be available but couldn't get enough locks. Application should call the function again after setting first_try='false'	Not possible

• drop_seats_tables() - This function is mainly made for the trigger. It will automatically delete all tables, entries and tickets when the train is removed from the runs relation

Triggers

• del_seats_table
  • This trigger will delete tickets, tables of the corresponding train when the entry is deleted from the runs relation. The corresponding function is drop_seats_tables()

Journey Search Queries

• For the direct journey plan, use the following sql query replacing the appropriate station codes:

SELECT 
S.train_number,S.from_station,S.SDT as "Departure",S.to_station ,S.DAT as "Arrival" 
FROM schedules as S 
WHERE S.from_station = 'CDG' AND S.to_station = 'NDLS';

• For getting journey plan with one connecting train within 4 hours, use the following query:

SELECT S1.train_number,S1.from_station,S1.SDT,S1.to_station,S1.DAT,S2.train_number,S2.from_station,S2.SDT,S2.to_station,S2.DAT
FROM schedules as S1, schedules as S2
WHERE S1.from_station = 'CDG' AND S1.to_station = S2.from_station AND S2.to_station = 'NDLS'
AND (S2.SDT - S1.DAT <= '04:00:00') AND (S2.SDT-S1.DAT>'00:00:00') AND S1.train_number<>S2.train_number;