The questions should be done according to case study.

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, passenger details and reservation details.

TrainList - The record of train includes its number, name, source, destination, and days on which it is available,

Train status - dates for which tickets can be booked, total number of seats available, and number of seats already booked.

Passenger - contains the details of passengers with their ID, name, address, DOB, Gender.

Reservation - contains 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.

Cancellation Table The record from reservation is moved to cancellation table with the date of cancellation.

DESCRIPTION

A passenger 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.

If the ticket is booked, a record is added to the reservation table with all details of passenger and trains. IF not, no record is added to the table and message is displayed as No reservation available!.

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 from the reservation table and 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.

Step 1: Insert the data for 5 trains in trainList and update the status of the train in train_status table keeping tickets limit as 10 and WL as 2. Insert 3 data in passenger table. Do not insert any data in Reservation List. [5 marks]

Step 2: Create and test the following procedures [25 marks]

1. Booking: In this procedure, the train number, train date, and category is passed as the parameter along with the passangerID. On the basis of these values, 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 from the Passenger table and a new record is added to the reservation table with the generated TicketID(you may use sequence to do it). Display ticket ID on screen for the passenger with the confirmation message.

2. Cancel: In this procedure, ticket ID is passed by the passenger and corresponding record is searched in the reservations table. If the record exists, it is deleted from the table but copied to the cancellation table along with the cancellation date. 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. Message is displayed that the reservation is deleted.

3. Transaction_audit: This trigger should run when a booking is done or cancellation is done. This should keep the record of booking and the cancellation in the audit table. Make your own structure of the table.

Step 3: Write the queries for the following: [20 marks]

Q1: Display the list of all the reservations for today from the reservations table

Q2. Display the list of all the reservations for next seven days starting from today

Q3. Display the train that cannot take any further reservation

Q4. Show all the cancelled reservations date wise

Q5. List the details of the train with the maximum reservations/cancellations.