Project Specification Page 1

ONLINE AIRLINES RESERVATION

Abstract

A Private company is running passenger flights all over INDIA. In view of their
expanding popularity they have decided to automate their work and connect all their
branches together. This would enable us customers to book their tickets from any of the
branches(even if the customer does not reside in the source or in destination place)
They require a distributed application to enable this operation. Their requirements are
as follows :

 The user should be able to add/modify/delete the details of a flight. Only

authorized person should do these operations.
 The user should be able to reserve/cancel a ticket. The reservation could be
done for a specific flight or for a specific date.
 Whenever there is a cancellation, the waiting list tickets should automatically be
confirmed.
 Provision should be given to provide concession for some passengers.

The tickets could be booked for a single person or a group of persons. In case of a group
of persons, they have common details like the reservation number, address, invoice
number, etc and some individual details like seat number, confirmation status, age, etc.
In case of a group of persons, a single payment is only done.

The Reservation Process

The reservation process should allow the customer to :

 Reserve Seats.
 Cancel an existing reservation.

The flight details required for the reservation are:

 Flight Name.
 Flight Number.
 Categories available.
 Cost of a ticket in each category.
 Availability status of tickets in the specified category.
 Source of travel.
 Destination of travel.
 Time table of all flights in the sector between one source and destination.

The Passenger details required for the reservation are :

 Name
 Age
 Gender
 Phone Number(s)
 Email address(if any)
 Credit card details
 Type
 Number
 Expiry Date
 Address details

Page 1 SRM Systems & Software P Ltd.

Project Specification Page 2

Payment Process

The payment process should

 Calculate the payable amount

 Register the payment
 Return an invoice as a proof of payment

The details in the invoice are :

 Invoice Number
 Source or travel
 Destination of travel
 Class of travel
 Passenger details
 Seat number
 Concession details(if any)
 Cheque/Credit card details (if any)
 Amount Payable
 Date of payment

Page 2 SRM Systems & Software P Ltd.

 Project Specification Page 3


Data Flow Diagram

User

New User
No Signup
Validate

Yes

Registration Enquiry Ticket Blocking Reports

Flight Details Day wise

Customer
Availability of seats Flight Wise
Collection

Database

Page 3 SRM Systems & Software P Ltd.

Project Specification Page 4

Database Schema

Page 4 SRM Systems & Software P Ltd.

Project Specification Page 5

Detailed Explanation of the Database Diagram

Flight_Master –Gives the main details of the flight(Details that are not repeated for the same flight)

Table: FLIGHTMASTER
Column Name Data Type Description
FlightNo VARCHAR(7) Can contain alphabets and numbers
FlightName VARCHAR(20) Represents the airlines and the types of the flight.
Source VARCHAR(20) Represents the starting point of the flight.
Destination VARCHAR(20) Represents the ending point of the flight.
TotalCapacity NUMERIC(4) Gives the total number of seats in the flight.
DurationOfFlight NUMERIC(4,2) Gives the time taken for the flight from the source to the destination.

Table: FLIGHTDESC
Column Name Data Type Description
FlightNo VARCHAR(7) Refers to the FlightNo field of the FlightMaster table.
Category VARCHAR(5) A code to represent the different categories available.
CostOfTicket NUMERIC(8,2) Gives the cost of the one full ticket without concessions.
StartingSeatNo NUMERIC(3) The Starting seat no. For the category (The seats in a particular category are assumed to continuous
EndingSeatNo NUMERIC(3) The ending seat no . for the category.

Table: FLIGHTDETAILS
Column Name Data Type Description
FlightNo VARCHAR(7) Refers to the FlightNo field of the FlightMaster table.
DayOfFlight VARCHAR(20) Represents the days of the week on which the flight is operated.
TimeOfFlight DATETIME Represents the time at which the flight starts from the source.

Table: CURRENTDETAILS
Column Name Data Type Description
FlightNo VARCHAR(7) Refers to the FlightNo field of the FlightMaster table.
Category VARCHAR(5) Refers to the Category field of the FlightDesc table
AvailableSeats NUMERIC(3) Represents the number of seats that are unreserved for the date and time of departure specified by
next field.
DateOfDeparture DATETIME Represents the date and time of departure.

Passenger_Master – Gives the details common to the passengers of a group

Column name Data type Constraints Description

Res_No Number Primary key Represents the Reservation
number
Res_Date Date Not Null Represents the date on which
the reservation is made

Flight_No Varchar(7) Foreign key Refers to the Flight_No filed of

the Flight_master table
Departure_Date Date Not null Represents the Flight_No filed
of the Flight_Master table
Address Varchar(30) Not null Gives the permanent address of
the passenger
State Varchar(20) Not null Represents the state to which
the passenger belongs
Phone_No Number Represents the phone number of
the passenger
E-mail Address Varchar(20) Represents the E-mail address
of the passenger

Passenger_Details – Gives the individual details of the passenger in a group

Column name Data type Constraints Description

Pass_No Number Primary key Represent the passenger number
Page 5 SRM Systems & Software P Ltd.
Project Specification Page 6
Pass _Name Varchar(20) Not null Gives the name of the passenger
Gender Chat Not null Gives the gender of the
passenger
Age Number Not null Gives the age of the passenger
Passport_No Varchar(20) Represents the passport number
of the passenger
Visa_Type Varchar(20) Represents the visa type of the
passenger
Res_No Number Foreign key Refers to the Res_No field of
the Passenger_ Master table
Confirmed Boolean Not Null Shows whether the tickets is
confirmed or not
Seat_No Number Represents the seat number
reserved by the passenger, if the
tickets is confirmed.
Cost Number(8,2) Represents the cost of the ticket,
after concession(if any)

Concession_Master – Defines the different types of concession offered by the Airlines

Column name Data type Constraints Description

Type Varchar(10) Primary Key Represents the type of
concession availed by the
passengers.
Percentage Number(2) Not Null Represents the Percentage of
concession for that particular
type of concession.
Description Varchar(20) Gives the description of
concession.

Concession_Details –Gives the details of the Concession offered to the passenger

Column name Data type Constraints Description

Type Varchar(10) Foreign key Refers to the Type field of the
Concession_Master table.
Age Number Not Null Refers to the age field of the
passenger_details table.
Pass_No Number Foreign Key Refers to the Pass_No field of
the Passenger_Details table.
Concession_Amt Number(7,2) Not Null Represents the amount of
concession that coulkd be
availed by the passenger.
Res_No Number Foreign Key Refers to the Res_No field of
the Passenger_Master table.

Payment_Deatils –Gives the payment details for each reservation

Column name Data type Constraints Description

Invoice_No Number Primary Key Represents the invoice number
Res_No Number Foreign Key Refers to the Res_No field of
the Passenger_Master table
Mode Varchar(2) Not Null Represents the mode of
payment. The mode could be
cash/cheque/credit card
Amount Number(10,2) Not Null Represents the amount of
Page 6 SRM Systems & Software P Ltd.
Project Specification Page 7
payment to be made
Card_Type Varchar2(10) --- Represents the type of credit
card (if the mode of payment is
by credit card)
Card_No Number(10) Represents the credit card
number
Card_holder_Name Varchar(20) Represents the cardholder’s
name
Expiry_Date Date Represents the expiry date of
the card
Cheque_No Number(6) Represents the cheque number
(in case of payment by cheque)
Cheque_Date Date Represents the cheque date
Bank_Drawn Varchar(10) Represents the bank on which
the cheque is drawn

Waiting_list-Gives the details of the waiting list for each trip in each category

Column name Data type Constraints Description

Pass_No Number Not Null Refers to the pass_No field of
the passenger_Details table
Filght_No Varchar(7) Foreign Key Refers to the Flight_No field of
the passenger_master table
Date_of_departure Date Not Null Refers to the date and time of
departure for the particular
flight
Category Varchar(5) Not Null Refers to the Category field of
the Flight_Desc table
Waiting_No Varchar(7) Not Null Representing waiting list
number of the passenger

Cancellation Master-Defines the percentage of amount to be deducted from the cost of the ticket during
cancellation.
Column name Data type Constraints Description
Code Number Primary Key Represents a code for the range
Description Varchar(10) Not Null Gives the range of time before
the flight
Percentage Number(2) Not Null Represents the percentage to be
deducted from the total amount
while canceling if the specified
time of cancellation falls within
the above range

Cancellation –Gives the main details of the cancellations done

Column name Data type Constraints Description

Cancel_No Number Primary Key Represents the cancellation
number
Cancel_Date Date Not Null Represents the date of
cancellation of the ticket
Code Numberq Foreign Key Refers to the code field in the
cancellation_Master table
Flight_No Varchar(20) Foreign Key Refers to the flight_no field of
the passenger_master table
Res_No Number Foreign Key Refers to the Res_no field of the
Passenger_master table
Cancellation_Amt Number(8,2) Not Null Represents the amount to be
Page 7 SRM Systems & Software P Ltd.
Project Specification Page 8
deducted from the total cost of
the ticket

Cancellation_Details –Gives the details of each passenger of the group for whom the cancellation has
been done

ColumnName DataType Constraints Description

Cancel_No Number Foreign Key Refers to the Cancel_No field of
the Cancellation_Details tabel.
Pass_No Number Foreign Key Refers to the pass_No field of
the Passenger_details table

Screens Required

1. Welcome Screen/HomePage
2. Flight Addition Screen1
3. Flight Addition Screen2
4. Flight Addition Screen3
5. Flight Modification
6. Flight Deletion
7. Reservation screen1 - Group details
8. Reservation screen2 - Passenger details
9. Reservation screen3 - Concession details
10. Payment
11. Invoice
12. Cancellation
13. Reservations view
14. Cancellations view
15. Wating list view
16. Fight Currentstatus view
17. Details of flights
18. Concession Master screen
19. Cancellation Master screen

Screens 2, 3, 4, 5, 6, 12, 13, 18, and 19 should have restricted access. So, they have to be
protected using a password.

Welcome Screen/ Home page

This screen is the first screen to be displayed when the application starts. It contains the
links to the other screens. Also, after each process is over, the user returns to this screen.

Flight Addition screen1

This screen enables us to add new entries for the Flight_Master table.

As soon as the Flight No. is entered, it is checked for uniqueness with the Flight_Master
table. If not, another Flight No. is entered.

Once the data for this screen is entered (and confirmed), the record is added to the
database and Flight Addition screen2 should be displayed.

Page 8 SRM Systems & Software P Ltd.

Project Specification Page 9

Flight Addition screen2

This screen enables us to add new entries for the Flight_Desc table.

The Flight_No field of the table need not be displayed. If displayed, it should contain the
same number as in the previous screen and should not be modified. While entering the
record in the Flight_Desc table, the Flight_No should be same as that in the
Flight_Master table.

The Ending_SeatNo should be greater than Starting SeatNo.

The user should be able to enter more than onr set of data using this screen. After each set
of data is entered, it is saved in the database.

Once all the data for this screen have been entered. Flight Addition screen3 should be
displayed.

Flight Addition screen3

This screen is used to enter new records to the Flight_Details table.

The Flight_NO field of the table need not be displayed. If displayed, it should contain the
same number as I in the previous screen and should not be modified. While entering the
record in the Flight_Details table, the Flight_No should be same as that in the
Flight_Master table.

The user should be able to enter more than one set of data using this screen. After each
set of data is entered, it is saved in the database.

After completing this screen, a message can be displayed, confirming the addition of a
new flight.

Flight Modification

First, the flight number for which modification is required is entered. This number should
be checked against the Flight_No Flight_No field of the Flight_Master table. Then, a list
containing the fields that can be modified is displayed. ( The Flight_No field cannot be
modified ). The list contains fields from the Flight_Master, Flight_Desc and
Flight_Details tables. The user has to select the rewuired field from this list. The Current
value of the field selected from the list is displayed. The user enters the new value and
confirms the change. The corresponding change is done to the database.

Flight Deletion

The flight number of the flight to be deleted is entered by the user. After confirmation, the
corresponding records are deleted from all the tables.

Reservation screen1 – Group details

Page 9 SRM Systems & Software P Ltd.

Project Specification Page 10
This screen enables the user to enter new records in the Passenger_Master table. The
reservation can be allowed only from 30 days prior to the date of departure.

The Res_No field should be automatically generated ( The Res_No in the last row of the
Passenger_Master table is retrieved and is incremented by 1). It can be generated just
before saving, in order to facilitate multi-user access. If the payement is to be done for a
group of passengers, then the Res_No is the same for the whole group.

There are two methods for selecting the required flight and the date of departure.

First Method : In this method, the user navigates to the Details of flight screen to select
the required flight and returns to Reservation screen1. Now , this screen is displayed with
the flight number, source and destination filled up. The departure dates for the specified
flight, for the next 30 days is displayed. (The Flight_Details table is used here). The user
then selects the departure date from the list. The time of departure of the flight on that
date is displayed immediately.

Second Method: In this method, the user enters the sources, destination and the date of
departure. The day corresponding to this date is generated and the flights available on
that day are listed using the Flight_Details table. The user selects the flight of his/her
choice. The time of the flight on that date is displayed immediately.

The remaining details are then entered and stored in the table. The user then moves on to
Reservation screen2 – Passenger details.

Reservation Screen2 – Passenger details

The Res_No from the previous screen is displayed in this screen and should not be
modified.

The availability of a seat in the specified flight and category in the specified date and
time is checked against the current_Details table. If the required record is not found in the
table, then a new record is inserted in the table with the Available_Seats field equal to the
capacity of the specified flight is set to “false” and the record is entered in the
Waiting_List table. Care should be taken to ensure that the Waiting list numbers are in
ascending order for each flight in each category. If the number of free seats is greater
than zero then it is decremented by one. Also, the seat number is alloted can be
Ending_SeatNO in the Flight_Desc table).

If the source or destination is a foreign country then the Passport number and the Visa
type are collected from the customer, else they are omitted.

The cost of the ticket is retrived from the Flight_Desc table. If the user is eligible for a
concession, then the Reservation screen3 – Concession details is displayed, else the cost
of the ticket is stored in the Cost field of the Passenger Details table and added to the total
amount (A variable carries the total amount for the whole group. This variable is
initialized when this screen is called for the first time for each group, and the cost of the
ticket for each passenger fo the group gets added on to it).

Page 10 SRM Systems & Software P Ltd.

Project Specification Page 11
The record is stored in the table and the above process is continued for another passenger
(if any) in the group.

After all the passenger details are collected, a final confirmation is requested from the
user. If the operation is canceled at this point, then the corresponding records are deleted
from the Passenger_Master, Passenger_Details and Concession_Details tables and the
required changes are done in the other tables and the control is transferred to the Home
page. If confirmed, the user moves on to the Payment screen.

Reservation screen3-Concession details

The Pass_No and Res_No are displayed as in the previous screens and should not be
modified. The concession types are retrieved from the Concession_Master table and
displayed as a list. When the user selects one of the types the Concession amount is
calculated using the ticket amount(retrieved in the previous screen) and the Percentage
field in the Concession_Master table. The final cost of the ticket after the concession is
calculated and added on to the total amount(as described in the above screen)and also
stored in the Cost field of the Passendger_Details table. The control is returned to
Reservation screen2-Passsenger Details to enter the details of the next passenger.

Payment

The total amount from the reservation screen is the amount to be paid and is stored in the
Amount field of the Payment_Details table. The Res_No is the same as in the previous
screens.

The Invoice_No field should be automatically generated(The Invoice_No in the last row
of the Payment_Details table is retrieved and is incremented by 1). It can be generated
just before saving, in order to facilitate multi-user access.

Depending on the mode of payment, the credit card details of the cheque details are
entered. If the payment is through cash then these details are neglected. On confirming
the details of payment, the record is inserted in the Payment_Details table and the Invoice
is generated.

Invoice

The Invoice consists of the ticket details and the cheque/credit card details as required.

Cancellation

This screen is used to record the cancellation of tickets. This screen affects the
Cancellation and Cancellation_Details tables and makes use of the Cancellation_Master
table. The Cancel_No of the Cancelaltion table will be automatically generated (The
Cancel_No in the last row of the Cancellation table is retrieved and is incremented by 1).
It can be generated just before saving, in order to facilitate multi-user access.

The Cancel_Date is the system date. The user should be able to navigate to the
Cancellations view screen and return to this screen. The list of codes is displayed along
with the description and the user selects the appropriate code.
Page 11 SRM Systems & Software P Ltd.
Project Specification Page 12

As soon as the reservation number and the passenger number are entered, the cost of the
ticket is retrieved from the Passenger_Details table and the corresponding percentage of
the ticket amount is deducted based on the cancellation code. This deducted amount is
added to the variable Cancel_amount(This variable is used to find out the total amount to
be deducted on cancellation.) The amount to be returned after deduction is added to the
variable Return_amount (This variable is used to find the total amount to be returned to
the passenger). The user should be able to cancel more than one ticket in the same group.

After the details of all the cancellations in the group are entered, the Cancel_amount and
Return_amount are displayed and the Cancel_amount is stored in the Cancellation_Amt
field of the Cancellation_Details table.

Then, the Current_Details table is updated with the number of seats that have become
free due to the cancellation. Further, the Waiting_List table is checked to see if there are
any entries for th especified flight and the corresponding category. If so, they are
removed from the waiting list and their tickets are confirmed, in the order of the waiting
list entry. The Waiting_List numbers of the other passengers for the same category and
the same flight are updated.

Note

There should be a periodical updation in which the waiting list entries for a flight that has
departed are erased.

Reservations view

This screen lists the details of the Passenger_Master table for the range of dates specified.

Cancellations view

This screen lists the details of the Cancellation table for the specified range of dates. It
also displays the details of the Cancellation_Details for the records for which it is
requested.

This screen can also be used to view the Cancellations or a specified flight on a specified
date and time.

Provision for viewing the details for the Cancellation_Master table can also be given.

Waiting list view

This screen is used to view the Waiting list details for a specified flight on a specified
date and time.

Flight Current Status View

This screen lists all the details of a flight for a specified date time. It takes the flight
number as the input and fetches the details from the Current_Details table. The screen
can also be designed to list all the reservation details for the same.
Page 12 SRM Systems & Software P Ltd.
Project Specification Page 13

Details of Flights

This screen can be designed to display different combinations of information from the
Flight_Master, Flight_Desc, Flight_Details and Current_Details tables based on user’s
choice.

Some of the combinations are as follows:

a. Based on the date of departure – Flight_No, Flight_Name, Source, Destination,
Duration_of_flight, Time_of_flight
b. Based on the flight name – Flight_No, Duration_of_flight, Cost-of_ticket,
Day_of_flight, Time_of_flight
c. Based on Category, Source and Destination – Flight_No, Flight_Name,
Cost_of_ticket, Available_Seats, Date_of_Departure

Concession Master Screen

This screen is used to create a new concession type or modify an existing one. It can also
be used to delete an existing concession type. The contents of this screen are stored in
the Concession Master table.

Cancellation Master Screen

This screen is used to set the percentage of the ticket amount to be deducted when a ticket
is cancelled. This screen can be used to add a new record to the table to modify the
existing contents or a to delte the existing contents. Care should be taken to ensure that
the Code field of the Concession_Master is unique. This screen gives full access to
Concession_Master table

Suggestions
The project can be developed in 2 ways:
1. Using Java, Java RMI, JDBC and Oracle
2. Using HTML, Javascipt, Servlets, JDBC and Oracle

Using Java, Java RMI, JDBC and Oracle

The screens that have restricted access are placed in the server side and the remaining
screens appear in the client side. All the data from the client are passed on to the server.
The server is connected to the database using JDBC.

Using HTML, Javascipt, Servlets, JDBC and Oracle

The screens are designed using HTML. The validations are done using Javascript. The
data is then passed onto Servlets, which does the processing and sends the data to the
Oracle database using JDBC connectivity. Also, any data requested by the client is
retrieved from th edatabase using Servlets and sent to the client.

Page 13 SRM Systems & Software P Ltd.