Case Study-2

Railway Reservation

AIM

The railway reservation system facilitates the passengers to enquire about the trains available on the basis of source and destination, booking and cancellation of tickets, enquire about the status of the booked ticket, etc.

The aim of case study is to design and develop a database maintaining the records of different trains, train status, and passengers. The record of train includes its number, name, source, destination, and days on which it is available, whereas record of train status includes dates for which tickets can be booked, total number of seats available, and number of seats already booked. The database has been developed and tested on the Oracle.

DESCRIPTION

Passengers can book their tickets for the train in which seats are available. For this, passenger has to provide the desired train number and the date for which ticket is to be booked. Before booking a ticket for a passenger, the validity of train number and booking date is checked. Once the train number and booking date are validated, it is checked whether the seat is available. If yes, the ticket is booked with confirm status and corresponding ticket ID is generated which is stored along with other details of the passenger. After all the available tickets are booked, certain number of tickets are booked with waiting status. If waiting lot is also finished, then tickets are not booked and a message of non-availability of seats is displayed.

The ticket once booked can be cancelled at any time. For this, the passenger has to provide the ticket ID (the unique key). The ticket ID is searched and the corresponding record is deleted. With this, the first ticket with waiting status also gets confirmed.

LIST OF ASSUMPTIONS

Since the reservation system is very large in reality, it is not feasible to develop the case study to that extent and prepare documentation at that level. Therefore, a small sample case study has been created to demonstrate the working of the reservation system. To implement this sample case study, some assumptions have been made, which are as follows:

  1. The number of trains has been restricted to 5.
  2. The booking is open only for next seven days from the current date.
  3. Only two categories of tickets can be booked, namely, AC and General.
  4. The total number of tickets that can be booked in each category (AC and General) is 10.
  5. The total number of tickets that can be given the status of waiting is 2.
  6. The in-between stoppage stations and their bookings are not considered.
E-R DIAGRAM
bm02-ufig1
DESCRIPTION OF TABLES AND PROCEDURES

Tables and procedures that will be created are as follows:

  1. TrainList: This table consists of details about all the available trains. The information stored in this table includes train number, train name, source, destination, fair for AC ticket, fair for general ticket, and weekdays on which train is available.

    Constraint: The train number is unique.

  2. Train_Status: This table consists of details about the dates on which ticket can be booked for a train and the status of the availability of tickets. The information stored in this table includes train number, train date, total number of AC seats, total number of general seats, number of AC seats booked, and number of general seats booked.

    Constraint: Train number should exist in TrainList table.

  3. Passenger: This table consists of details about the booked tickets. The information stored in this table includes ticket ID, train number, date for which ticket is booked, name, age, sex and address of the passenger, status of reservation (either confirmed or waiting), and category for which ticket is booked.

    Constraint: Ticket ID is unique and the train number should exist in TrainList table.

  4. Booking: In this procedure, the train number, train date, and category is read from the passenger. On the basis of the values provided by the passenger, corresponding record is retrieved from the Train_Status table. If the desired category is AC, then total number of AC seats and number of booked AC seats are compared in order to find whether ticket can be booked or not. Similarly, it can be checked for the general category. If ticket can be booked, then passenger details are read and stored in the Passenger table.
  5. Cancel: In this procedure, ticket ID is read from the passenger and corresponding record is searched in the Passenger table. If the record exists, it is deleted from the table. After deleting the record (if it is confirmed), first record with waiting status for the same train and same category are searched from the Passenger table and its status is changed to confirm.
RELATIONAL DATABASE SCHEMA FOR CASE STUDY
  1. TrainList(TrainNumber, TrainName, Source, Destination, AC_Fair, GEN_Fair, MonAvailable, TueAvailable, WedAvailable, ThuAvailable, FriAvailable, SatAvailable, SunAvailable)
  2. Train_Status(TrainNumber, TrainDate, AC_Seat, GEN_Seat, B_AC_ Seat, B_GEN_Seat)
  3. Passenger(T_ID, TrainNumber, BookedDate, Name, Age, Sex, Address, Status, Category)

Implementation in Oracle

  1. CREATE TABLE TrainList 
    (
   TrainNumber        NUMERIC(4),
   TrainName          VARCHAR(20),
   Source             VARCHAR(20),
   Destination        VARCHAR(20),
   AC_Fair            NUMERIC(10),
   GEN_Fair           NUMERIC(10),
   MonAvailable       VARCHAR(1),
   TueAvailable       VARCHAR(1),
   WedAvailable       VARCHAR(1),
   ThuAvailable       VARCHAR(1),
   FriAvailable       VARCHAR(1),
   SatAvailable       VARCHAR(1),
   SunAvailable       VARCHAR(1),
PRIMARY KEY (TrainNumber)
);
  2. CREATE TABLE Train_Status 
    (
   TrainNumber         NUMERIC(4),
   TrainDate           DATE,
   AC_Seat             NUMERIC(4),
   GEN_Seat            NUMERIC(4),
   B_AC_Seat           NUMERIC(4),
   B_GEN_Seat          NUMERIC(4),
   FOREIGN KEY (TrainNumber) REFERENCES TrainList(TrainNumber)
);
  3. CREATE TABLE Passenger 
      (
   T_ID                VARCHAR(15),
   TrainNumber         NUMERIC(4),
   BookedDate          DATE,
   Name                VARCHAR(20),
   Age                 NUMERIC(3),
   Sex                 VARCHAR(1),
   Address             VARCHAR(35),
   Status              VARCHAR(1),
   Category            VARCHAR(10),
   PRIMARY KEY (T_ID),
   FOREIGN KEY (TrainNumber) REFERENCES TrainList(TrainNumber)
);
  4. Booking Procedure 
    DECLARE
  TN NUMERIC(4);
  DT DATE;
  CAT VARCHAR(10);
  ST VARCHAR(1);
    
  CURSOR getTrain IS
  SELECT * FROM Train_Status
  WHERE TrainNumber=TN AND TrainDate=DT;
  trec Train_Status%ROWTYPE;
    
BEGIN
    
  DBMS_OUTPUT.PUT_LINE(‘READING VALUES’);
  TN := &TN;
  DT := ‘&DT’;
  CAT := ‘&CAT’;
    
  OPEN getTrain;
  LOOP
    FETCH getTrain INTO trec;
    EXIT WHEN getTrain%NOTFOUND;
    IF(CAT = ‘AC’)THEN
     IF(trec.AC_Seat-trec.B_AC_Seat)>0 THEN
      ST := ‘C’;
      DBMS_OUTPUT.PUT_LINE(‘Seat Confirmed’);
     ELSIF(trec.AC_Seat-trec.B_AC_Seat) > -2 THEN
      ST := ‘W’;
      DBMS_OUTPUT.PUT_LINE(‘Seat Waiting’);
     ELSE
      DBMS_OUTPUT.PUT_LINE(‘Seat Not Available’);
      EXIT;
     END IF;
     IF ST = ‘C’ OR ST = ‘W’ THEN
       UPDATE Train_Status SET B_AC_Seat = B_AC_Seat + 1 WHERE
       TrainNumber = TN and TrainDate=DT;
    END IF;
ELSIF(CAT = ‘GEN’) THEN
   IF(trec.GEN_Seat-trec.B_GEN_Seat)>0 THEN
     ST := ‘C’;
     DBMS_OUTPUT.PUT_LINE(‘Seat Confirmed’);
   ELSIF(trec.GEN_Seat-trec.B_GEN_Seat) > -2 THEN
     ST := ‘W’;
     DBMS_OUTPUT.PUT_LINE(‘Seat Waiting’);
   ELSE
     DBMS_OUTPUT.PUT_LINE(‘Seat Not Available’);
     EXIT;
   END IF;
     IF ST = ‘C’ OR ST = ‘W’ THEN
     UPDATE Train_Status SET B_GEN_Seat = B_GEN_Seat+1
     WHERE TrainNumber = TN and TrainDate=DT;
   END IF;
ELSE
  DBMS_OUTPUT.PUT_LINE(‘Invalid Category’);
END IF;
IF ST = ‘C’ OR ST = ‘W’ THEN
  INSERT INTO Passenger VALUES(‘&T_ID’, TN, DT, ‘&Name’, &Age,
  ‘&Sex’, ‘&Address’, ST, CAT);
END IF;
END LOOP;
CLOSE getTrain;
END ;/
  5. Cancel Procedure 
    DECLARE
    
   TID VARCHAR(15);
   TN NUMERIC(4);
   DT DATE;
   CAT VARCHAR(10);
   ST VARCHAR(1);
   CURSOR Get_waiting IS
   SELECT * FROM Passenger WHERE (Status = ‘W’ AND
   TrainNumber = TN AND BookedDate = DT AND Category = CAT) FOR
   UPDATE OF Status;
   prec Passenger%ROWTYPE;
    
BEGIN
    
   TID := ‘&TID’;
   SELECT TrainNumber, BookedDate, Status, Category INTO TN, DT,
   ST, CAT FROM Passenger WHERE T_ID = TID;
   IF TN IS NOT NULL THEN
   DELETE FROM Passenger WHERE T_ID = TID;
   IF CAT = ‘AC’ THEN
      UPDATE Train_Status SET B_AC_Seat = B_AC_Seat - 1
      WHERE TrainNumber=TN AND TrainDate=DT;
   ELSE
      UPDATE Train_Status SET B_GEN_Seat = B_GEN_Seat - 1
      WHERE TrainNumber=TN AND TrainDate=DT;
   END IF;
   DBMS_OUTPUT.PUT_LINE(‘TICKET CANCELLED’);
ELSE
   DBMS_OUTPUT.PUT_LINE(‘NO SUCH BOOKING EXISTS’);
END IF;
IF (ST = ‘C’) THEN
   OPEN Get_waiting;
   FETCH Get_waiting INTO prec;
   IF Get_waiting%FOUND THEN
      UPDATE Passenger SET Status = ‘C’
      WHERE CURRENT OF Get_waiting;
   END IF;
   CLOSE Get_waiting;
END IF;
    
END ;
/

Sample Queries

  1. INSERT INTO TrainList 
    VALUES (2444, ‘Bhubaneswar Rajdhani’, ‘New Delhi’, ‘Bhubaneswar’,
2250, 1850, ‘Y’, ‘Y’, ‘N’, ‘Y’, ‘Y’, ‘Y’, ‘N’);
  2. SELECT * 
    FROM TrainList;
    bm02-ufig2
  3. SELECT TrainNumber, TrainName, AC_Fair, GEN_Fair 
    FROM TrainList
WHERE Source = ‘New Delhi’ AND Destination = ‘Puri’;
    bm02-ufig3
  4. INSERT INTO Train_Status 
    VALUES (2444, ‘02-MAY-2009’, 10, 10, 0, 0);
  5. SELECT * 
    FROM Train_Status
WHERE TrainNumber = 2444;
    bm02-ufig4
  6. SQL> @e:RailwayReservationBooking 
    Enter value for tn: 2444
old 14:         TN := &TN;
new 14:         TN := 2444;
Enter value for dt: 02-MAY-2009
old 15:         DT := ‘&DT’;
new 15:         DT := ‘02-MAY-2009’;
Enter value for cat: AC
old 16:         CAT := ‘&CAT’;
new 16:         CAT := ‘AC’;
Enter value for t_id: 3
Enter value for name: PREETI
Enter value for age: 29
Enter value for sex: F
Enter value for address: ROHINI
old 73: INSERT INTO Passenger VALUES(‘&T_ID’, TN, DT, ‘&Name’,
&Age, ‘&Sex’, ‘&Address’, ST, CAT);
new 73: INSERT INTO Passenger VALUES(‘3’, TN, DT, ‘PREETI’, 29,
‘F’, ‘ROHINI’, ST, CAT);
READING VALUES
Seat Confirmed
  7. SQL> @e:RailwayReservationBooking 
    Enter value for tn: 2444
old 14:         TN := &TN;
new 14:         TN := 2444;
Enter value for dt: 02-MAY-2009
old 15:         DT := ‘&DT’;
new 15:         DT := ‘02-MAY-2009’;
Enter value for cat: AC
old 16:         CAT := ‘&CAT’;
new 16:         CAT := ‘AC’;
Enter value for t_id: 4
Enter value for name: PREMASHIS
Enter value for age: 26
Enter value for sex: M
Enter value for address: MAYURVIHAR
old 73: INSERT INTO Passenger VALUES(‘&T_ID’, TN, DT, ‘&Name’,
&Age, ‘&Sex’, ‘&Address’, ST, CAT);
new 73: INSERT INTO Passenger VALUES(‘4’, TN, DT, ‘PREMASHIS’,
26, ‘M’, ‘MAYURVIHAR’, ST, CAT);
READING VALUES
Seat Confirmed
  8. SQL> @e:RailwayReservationBooking 
    Enter value for tn: 2444
old 14:         TN := &TN;
new 14:         TN := 2444;
Enter value for dt: 02-MAY-2009
old 15:         DT := ‘&DT’;
new 15:         DT := ‘02-MAY-2009’;
Enter value for cat: AC
old 16:         CAT := ‘&CAT’;
new 16:         CAT := ‘AC’;
Enter value for t_id: 12
Enter value for name: UMA
Enter value for age: 26
Enter value for sex: F
Enter value for address: SUBHASHPLACE
old 73: INSERT INTO Passenger VALUES(‘&T_ID’, TN, DT, ‘&Name’,
&Age, ‘&Sex’, ‘&Address’, ST, CAT);
new 73: INSERT INTO Passenger VALUES(‘12’, TN, DT, ‘UMA’, 26,
‘F’, ‘SUBHASHPLACE’, ST, CAT);
READING VALUES
Seat Waiting
  9. SQL> @e:RailwayReservationBooking 
    Enter value for tn: 2444
old 14:         TN := &TN;
new 14:         TN := 2444;
Enter value for dt: 02-MAY-2009
old 15:         DT := ‘&DT’;
new 15:         DT := ‘02-MAY-2009’;
Enter value for cat: AC
old 16:         CAT := ‘&CAT’;
new 16:         CAT := ‘AC’;
Enter value for t_id: 13
Enter value for name: MEENU
Enter value for age: 26
Enter value for sex: F
Enter value for address: PITAMPURA
old 73: INSERT INTO Passenger VALUES(‘&T_ID’, TN, DT, ‘&Name’,
&Age, ‘&Sex’, ‘&Address’, ST, CAT);
new 73: INSERT INTO Passenger VALUES(‘13’, TN, DT, ‘MEENU’, 26,
‘F’, ‘PITAMPURA’, ST, CAT);
READING VALUES
Seat Waiting
  10. SQL> @e:RailwayReservationBooking 
    Enter value for tn: 2444
old 14:         TN := &TN;
new 14:         TN := 2444;
Enter value for dt: 02-MAY-2009
old 15:         DT := ‘&DT’;
new 15:         DT := ‘02-MAY-2009’; Enter value for cat: AC
old 16:         CAT := ‘&CAT’;
new 16:         CAT := ‘AC’; Enter value for t_id: 14
Enter value for name: KHYATI
Enter value for age: 26
Enter value for sex: F
Enter value for address: PITAMPURA
old 73: INSERT INTO Passenger VALUES(‘&T_ID’, TN, DT, ‘&Name’,
&Age, ‘&Sex’, ‘&Address’, ST, CAT);
new 73: INSERT INTO Passenger VALUES(‘14’, TN, DT, ‘KHYATI’, 26,
‘F’, ‘PITAMPURA’, ST, CAT); READING VALUES
Seat Not Available
  11. SELECT * 
    FROM Passenger
WHERE TrainNumber = 2444;
    bm02-ufig5
  12. SQL> @E:RailwayReservationCancel 
    Enter value for tid: 9
   old 11:       TID := ‘&TID’;
   new 11:       TID := ‘9’;
   TICKET CANCELLED
  13. SELECT * 
    FROM Passenger
WHERE TrainNumber = 2444;
    bm02-ufig6
  14. SELECT * 
    FROM Train_Status
WHERE TrainNumber = 2444;
    bm02-ufig7

NOTE To display the output messages of the procedures, the command Set Serveroutput On is given before executing the procedures.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset