Airline Reservation Report

Airline Reservation and Management System Ameya Muley(05005011) V. Anuradha (04026014) Tanmay Khirwadkar(05005016) Hemendra Srivastava(05D05014) November 13, 2007

1

Project Specification

Our project is a database specific to an Airline, with interfaces for passengers where they can look up flight schedules and availability of reservation, and for the airliner employees to maintain logistics, and view flight information, supplies, airport details and member profiles.

2

ER Design

Refer Figure 2 for the ER diagram.

3

Relational Schema 1. airport(airport-code, address, city) 2. flight logistics(flight no, dep date, seats avbl, food v, food n, aircraft make) 3. ticket(ticket no,status,food pref,type,passport no,country,dep date,flight no) 4. aircrafts types (make, seats)

5. flight(flight no, dep time, arr time, fare, src airport-code, dest airport code, src airport terminal, dest airport termin 6. days of flight(flight no, day) 7. passenger(country,passport no,First-Name, Last-Name, Address, Phone-No) 8. member(country, passport no, member id, password, flyer points)

4

Normalization 1. Relation formed from entity-set Airport Airport(airport-code,address,city) airport-code → address, city Relation is in 3NF and BCNF Explanation: 3NF:LHS of only non-trivial FD in F+ is a primary key and hence superkey. BCNF:LHS of only non-trivial FD in F+ is primary key and hence a superkey.

1

2. Relation formed from the weak-entity set flight logistics with deviation Flight-logistics(flight no, date of dep, seats avbl, food v, food n) flight no, date of dep → seats avbl, food v, food n Relation is in 3NF and BCNF Explanation: 3NF:LHS of only non-trivial FD in F+ is primary key and hence a superkey. BCNF:LHS of only non-trivial FD in F+ primary key and hence is a superkey. 3. The relation for the ”has booked” relationship has been merged into the relation for ”ticket” because each ticket is associated with exactly one passenger and exactly one flight logistic. Ticket(ticket no,status,food pref,type,Passport-Number,Country,Date of Dep,flight no) ticket no → status,food pref,type,Passport-Number,Country,Date of Dep,flight no Relation is in 3NF and BCNF Explanation: 3NF:LHS of only non-trivial FD in F+ is primary key and hence a superkey. BCNF:LHS of only non-trivial FD in F+ is primary key and hence a superkey. 4. Relation resulting from multi-valued attribute ”days of flight” in entity-set ”Flight” Days of Flight(flight no, day of week) Relation is in 3NF and BCNF Explanation: BCNF, 3NF : No non-trivial FD in F 5. Relation resulting from deviation, for many-one relationship ”uses” from Flight-logistics to Aircraft Aircraft (flight no, date of dep, sr no, make, no of seats) flight no, date of dep → sr no, make, no of seats sr no → make make → no of seats Not in 3NF Not in BCNF Explanation: 3NF: Only candidate key of Aircraft is (flight no, date of dep). The non-trivial functional dependency sr no → make, no of seats violates 3NF because ’sr no’ is not a superkey and ’make’ is not part of any candidate key. BCNF: Since relation is not 3NF it cannot be in BCNF. BCNF Decomposition: Uses (flight no, date of dep, sr no) Aircraft(sr no, make, no of seats) The relation Aircrafts is not in 3NF. Explanation: The only candidate key of Aircrafts is ’sr no’. The non-trivial functional dependency make → no of seats violates 3NF because make is not a superkey and no of seats is not part of any candidate key. BCNF Decomposition Aircrafts (sr no, make)

2

Aircraft-Types (make, no of seats) 6. Relation resulting from deviation, for many-one relationship ”requires” from Flight-logistics to Flight and without deviation from relationship ”flies”. Note that in the relationship ”flies” each flight is associated with exactly one source and destination airport. Flight(flight no,date of dep, dep time, arr time, fare, src airport-code, dest airport-code, src airportterminal,dest airport-terminal) flight no,date of dep → dep time, arr time, fare, src airport-code, dest airport-code, src airport-terminal, dest airport-terminal flight no → dep time, arr time, fare, src airport-code, dest airport-code, src airport-terminal, dest airportterminal Not in 3NF Not in BCNF Explanation: Only candidate key of Flight is (flight no, date of dep). The non-trivial functional dependency flight no → dep time, arr time, fare, src airport-code, dest airport-code, src airport-terminal, dest airport-terminal violates 3NF because ’flight no’ is not a superkey and ’dep time’ is not part of any candidate key. BCNF: Since relation is not 3NF it cannot be in BCNF. BCNF Decomposition: Flights (flight no, dep time, arr time, fare, src airport-code, dest airport-code, src airport-terminal,dest airportterminal) Scheduled-flights (flight no, date of departure) 7. The E/R approach to convert the entities ”Passenger” and ”Member” into relations. Passenger(Country, Passport-Number,First-Name, Last-Name, Address, Phone-No) Country, Passport-Number → First-Name, Last-Name, Address, Phone-No Relation is in 3NF and BCNF Explanation: 3NF:LHS of only non-trivial FD in F+ is primary key and hence a superkey. BCNF:LHS of only non-trivial FD in F+ is primary key and hence a superkey. Member(Country, Passport-Number,Member-Id, Password, Flyer-Points) Country, Passport-Number → Member-Id, Password, Flyer-Points Member-Id → Country, Passport-Number, Password, Flyer-Points, Relation is in 3NF and BCNF Explanation: 3NF: LHS of FD : Country, Passport-Number → Member-Id, Password, Flyer-Points is primary key and hence a superkey. LHS of FD : Member-Id → Country, Passport-Number, Password, Flyer-Points is a superkey. BCNF: LHS of FD : Country, Passport-Number → Member-Id, Password, Flyer-Points is primary key and hence a superkey. LHS of FD : Member-Id → Country, Passport-Number, Password, Flyer-Points is a superkey.

3

Relational Schema from ER Diagram: 1. Airport(airport-code,address,city) 2. Flight-logistics(flight no, date of dep, seats avbl, food v, food n, aircraft sr no) 3. Ticket(ticket no,status,food pref,type,Passport-Number,Country,Date of Dep,flight no) 4. Aircrafts (sr no, make) 5. Aircrafts-Types (make, no of seats) 6. Flight(flight no, dep time, fare, arr time, src airport-code, dest airport-code, src airport-terminal, dest airportterminal) 7. Days of Flight(flight no, day of week) 8. Passenger(Country, Passport-Number,First-Name, Last-Name, Address, Phone-No) 9. Member(Country, Passport-Number,Member-Id, Password, Flyer-Points)

5

User Interface

5.1

Passenger Interface

Querying availability of reservation and information about flights between any two airports. Information about flights includes all flights between the source and the destination on that day, and the fares. Monetary transactions(eg. credit-card payments) has not been modeled. Ticket booking is limited to allotment of a ticket number.

5.2

Member Interface

1. Login for members / new member sign up 2. Querying availability of reservation and information about flights between any two airports, as in the passengers interface. Again, monetary transactions has not been modeled. Ticket booking is limited to allotment of a ticket number. 3. Viewing the flying history: listing all of the members flights since becoming a member. 4. Cancellation of tickets. 5. Members will gather Frequent-Flyer Points (proportional to the fare) as they book tickets. A member will be awarded Frequent-Flyer Points for each flight that he flies proportional to the fare of that flight. If a member has gathered sufficient FF Points, he will be eligible for a free ticket. He will then have used up Points proportional to the actual fare of the ticket. The cancellation of a ticket will result in the cancellation of the addition to the Frequent-Flyer Points. Free tickets bought on FF Points cannot be cancelled. If a member books multiple tickets, only the ticket on his name will be considered for FF Points, and the rest of the tickets will be considered to be ordinary passenger tickets.

4

5.3

Airlines Interface

Querying for 1. Flight Information (A flight is referred to by the flight number and the date.) (a) Availability of seats and fares. (b) Food requirements and other supplies (Passengers will specify food requirements while booking) (c) Information (names, addresses, passport numbers, phone numbers, flyer points) about the passengers on the flight (d) Flight schedule: source, destination, times and terminals of arrival and departure (e) Aircraft details: model number, make, total seats 2. Members Profile (a) Personal details such as name, address, phone number, flying history (as in the members interface) (b) Frequent Flyer points 3. Airport Information (a) Flights arriving to and departing from an airport on a day (b) Total food supplies required on a given day at a given airport (c) Terminal usage (terminal number) by flights of the carrier (d) Queries such as all passengers flying on a particular route during a particular period at least x times. The variable parameters in this query viz. route, period and number of times can be specified by the user. The passengers returned by the query can be awarded flyer-points through the interface.

6

Implementation Details

We have used the MVC architecture where interface uses JSP and JavaScript for verification of the forms, control uses servlet and the model consists of postgres database. The data was uploaded from CSV files into Postgresql using the COPY command.

7

Test Cases

The test cases will be uploaded separately as an .xls file.

8

Status of the project

All the features mentioned in the functional specification submitted have been implemented.

9

Role of team members • Tanmay: ER-Diagram and Test-Plan, Conversion to Relational Schema, Normalization, SQL-Schema creation scripts, Controller for passenger and member interface. • Ameya: ER-Diagram and Test-Plan, Conversion to Relational Schema, Normalization, SQL-Data population scripts, Controller for airlines interface.

5

• Anuradha:ER-Diagram and Test-Plan, Conversion to Relational Schema, Normalization, Data population, User-Interface, JSPs • Hemendra: System Testing, User-Interface

10

Analysis

We compared the passenger and member interface with ticket booking interface for Kingfisher Airlines. Our system provided most of the functionalities provided there. The only deviation is use of passport numbers in our system to identify passengers. If monetary transactions(credit-card payments) are modelled the passenger and member interface can be used by an airline company.We could not find a publicly available interface for comparing the airlines interface. However, the interface provides functionalities for most of the needs of an airline company that we could think of.

6

last_name first_name no_of_seats address

model

name phone_no

aircraft

passport_no

passenger uses seats_avbl

IS A food_v

member

flight_logistics

food_n date date

password

fare

member_id has_booked

food_pref

flyer_points requires fare

airport

from

flies to

flight

city terminal flight_no

day dep_time

: double line

arr_time

date is a discriminating attribute of flight_logistics

7

Figure 1: The ER diagram