Introduction
This Project aims to model the working of an online airway reservation system. The project has been built and tested on the Oracle database, and uses JAVA as the programming language. JDBC was used as the interface between the database and JAVA.
This Project is built to model the reservation system as closely as possible. Some features are:
User Interfaces:
The web is used as an interface with the users.
Hardware Interfaces:
No special hardware is required for this product.
Software Interfaces: 1. Java
2. JDBC
3. Apache Tomcat servlets
4. Oracle 8i SQL server
Overview
This Project aims to model the working of an online airway reservation system. The project has been built and tested on the Oracle database, and uses JAVA as the programming language. JDBC was used as the interface between the database and JAVA.
This Project is built to model the reservation system as closely as possible. Some features are:
- Separate profiles for users, containing all their personal data.
- Possibility of multiple bookings per profile.
- Finding flight schedule information according to various constraints and conditions.
- Handling of flight schedules across multiple stops, and multiple airports in one city.
- Handling of different kinds of schemes for frequent fliers, and for flights.
- Separate profile type for officials.
- Appropriate error checking.
All these and other transactions and features have been described in detail in the following section.
Airport: An airport consists of a name, the city it is in, and its airport id.
Flight : A flight is identified by its flightid. A flight denotes an unique “planeâ€, i.e. one which is scheduled to run at a certain time, from one place to another. A flightruns over a set of routes.
Route: A route is simply a tuple of airports: (StartAirport, EndAirport), and every route has a unique route id. A flight runs over a route only if it runs from the startairport to the endairport, possibly halting in between at other airports. A route is elementary for a flight if the flight runs nonstop from the start airport to the end airport.
Ticket : A ticket is uniquely identified by a ticketid. The ticket may be a passenger ticket or a cargo ticket, and can be booked under a passenger profile or a user profile. A ticket is booked on a flight for a route that the flight is associated with. A passenger ticket contains details about the passenger, and a cargo ticket about a cargo. The Scheme : A scheme consists of discount percentages on various classes awarded on certain flights, and for certain people or round trips. Scheme ids have a type code defining what they they are valid for, and a period code showing whether they are valid as of now or no. A scheme is defined for a flight and for a particular route.
Official: An official is a person who can book tickets for others, and can find retrieve the complete list of passengers boarding a flight. An official works at an airport.
Profile: A profile denotes that a person has been verified to be genuine and can book tickets/ execute certain queries.
The above descriptions more or less define all the entities that are in the ER diagram of the database project. Some of the important and not so obvious relationships are explained
FlightRouteScheme: This is a ternary relation that says that a flight runs over a route using a particular scheme. The scheme can be null, but not the flight id and route id. The attributes in this include:
Fare for the flight between the two stops given by the route specified.
.Any scheme valid on this flight, for this route
.Number of booked seats on this flight, route.
A flag value indicating whether this is an elementary or complex  route for this flight.
FlightRouteTicket: This ternary relation says that a ticket is booked on a certain flight over one route that the flight allows. It has only the primary keys of each entity.
ProfileTicket relation: This consists of two relations: user profile related to ticket and the official profile related to ticket. This is done to keep the user and official profiles separate and independent from each other.
Definitions
Plane type: This defines the physical type of the plane. It dictates the capacity of first, executive, business and economy seats that a flight can have.Airport: An airport consists of a name, the city it is in, and its airport id.
Flight : A flight is identified by its flightid. A flight denotes an unique “planeâ€, i.e. one which is scheduled to run at a certain time, from one place to another. A flightruns over a set of routes.
Route: A route is simply a tuple of airports: (StartAirport, EndAirport), and every route has a unique route id. A flight runs over a route only if it runs from the startairport to the endairport, possibly halting in between at other airports. A route is elementary for a flight if the flight runs nonstop from the start airport to the end airport.
Ticket : A ticket is uniquely identified by a ticketid. The ticket may be a passenger ticket or a cargo ticket, and can be booked under a passenger profile or a user profile. A ticket is booked on a flight for a route that the flight is associated with. A passenger ticket contains details about the passenger, and a cargo ticket about a cargo. The Scheme : A scheme consists of discount percentages on various classes awarded on certain flights, and for certain people or round trips. Scheme ids have a type code defining what they they are valid for, and a period code showing whether they are valid as of now or no. A scheme is defined for a flight and for a particular route.
Official: An official is a person who can book tickets for others, and can find retrieve the complete list of passengers boarding a flight. An official works at an airport.
Profile: A profile denotes that a person has been verified to be genuine and can book tickets/ execute certain queries.
The above descriptions more or less define all the entities that are in the ER diagram of the database project. Some of the important and not so obvious relationships are explained
FlightRouteScheme: This is a ternary relation that says that a flight runs over a route using a particular scheme. The scheme can be null, but not the flight id and route id. The attributes in this include:
Fare for the flight between the two stops given by the route specified.
.Any scheme valid on this flight, for this route
.Number of booked seats on this flight, route.
A flag value indicating whether this is an elementary or complex  route for this flight.
FlightRouteTicket: This ternary relation says that a ticket is booked on a certain flight over one route that the flight allows. It has only the primary keys of each entity.
ProfileTicket relation: This consists of two relations: user profile related to ticket and the official profile related to ticket. This is done to keep the user and official profiles separate and independent from each other.
Coding
Some of the functionalities are: Any person can query for flight scheduled according to specified conditions.
A Person can book/cancel a ticket only after he logs in.
A user can sign up for a profile if he doesnt have one already.
On logging in, the user has options to
1.Book tickets
2.Edit Profile information
3.Cancellation of tickets
4.View all current tickets booked by him
5.Logout
A person can get all information regarding a ticket if he keys in the ticketID.
A person can get all information about a flight if he keys in the flightID.
A person can get the schedule of all flights running for the next 20 days. The above transactions in general involve a sequence of queries to the database. Some of these queries are as follows:
Sample Queries:
1. Get all cities
select distinct city
from airport
where (airportid in (select startairport from route)) or airportid in ((select endairport from route));
2. Get flight data on a particular date:
with ridtable as
(select rid
from route
where (route.startairport in (select airportid
from airport
where city="CITYNAME") and route.endairport in (select airportid from airport where city="CITYNAME2")))
select flightid, rid, start_time, end_time, (bus).seats, (exec).seats, (econ).seats, (first).seats
from flight_route_scheme, flight
where (flight.flightid = flight_route_scheme.flightid ) and (flight.status="Active") and (rid in ridtable) and (YEAR=extract(year from start_time)) and (MONTH=extract(month from start_time) and DAY=extract(day from start_time)) ;
3. For morning flights:
Replace the select clause in query 2 by:
select flightid, rid, start_time, end_time, (bus).seats, (exec).seats, (econ).seats, (first).seats
from flight_route_scheme, flight
where (flight.flightid = flight_route_scheme.flightid ) and (flight.status="Active") and (rid in ridtable) and TRUNC(start_time)=DATE and (cast(start_time as time))>'05:00:00 AM' and (cast(start_time as time))<'04:00:00 PM';
For AM/PM flights, we modify the last part of the where clause appropriately
4. Ordering the flights in the above queries:
Add the clause : order by(cast(start_time as time)) desc; to the above query to order the flights in order of departure times.
5. To get all info about a ticket from the ticket id:
select username, name, age, sex, booking_date, status, schemeid, rid, flightid, class
from flight_route_ticket natural inner join user_ticket natural inner join passengerticket natural inner join ticket
where ticketid='0000000001';
6. Get city and airport names of start and destination from the airport id:
select S.city, T.city , S.name, T.name
from (airport) S, (airport) T
where (S.airportid in(select startairport from route where rid='R0001')) and (T.airportid in (select endairport from route where rid='R0001'));
7. Get city and airport names of start and destination from the route id
select S.city, T.city , S.name, T.name
from (airport) S, (airport) T
where (S.airportid in(select startairport from route where rid='R0001')) and (T.airportid in (select endairport from route where rid='R0001'));
8. Get all tickets booked by a user profile:
select username, name, age, sex, booking_date, status, schemeid, rid, flightid
from flight_route_ticket natural inner join user_ticket natural inner join passengerticket natural inner join ticket
where user_ticket.username= 'san'; 9.Get all information about a flight from the flightid: select flightid, cast(start_time as date) as A, startairport, cast(end_time as date) as B, endairport , S.city, T.city
from (flight_route_scheme) F ,route,(airport) S, (airport) T
where flightid='I0004' and F.rid=route.rid and startairport=S.airportid and endairport=T.airportid; Below is a snippet of JAVA code used to book the flight tickets:
rs = jdbc.execute("select max(ticketid)+1 from ticket");
String ticketgenid="0000000000";
if(rs.next()){
String temp=Integer.toString(rs.getInt(1));
ticketgenid=ticketgenid+temp;
ticketgenid=ticketgenid.substring(ticketgenid.length()10);
}
else{
ticketgenid="0000000000";
}
rs = jdbc.execute("select route.rid,("+queryobject.getStartClass()+").fare from flight_route_scheme,route,(airport)S,(airport)T where T.airportid=route.startairport and S.airportid=route.endairport and T.city='"+queryobject.getFromPlace()+"' and S.city='"+queryobject.getToPlace()+"' and route.rid=flight_route_scheme.rid and flightid='"+queryobject.getStartFlight()+"'");
String routerid="";
int fare_paid=0;
gotoPage("/index.jsp",request,response);
if(rs.next()){
// routerid=rs.getString(1);
// fare_paid = rs.getInt(2);
}
else{
String error="Sorry No such flight available";
session.setAttribute("errormessage",error);
gotoPage("/error.jsp",request,response);
}
String qinsert=" ";
/*
jdbc.executeUpdate("insert into ticket values('"+ticketgenid +"',trunc(current_date),'"+fare_paid+"')");
jdbc.executeUpdate("insert into passengerticket values('"+ticketgenid +"','"+name+"','"+age+"','"+sex+"','" +passportid+"','"+address+"','"+schemeid+"','"+queryobject.getStartClass() +"',trunc(current_date),'"+fare_paid+"')");
jdbc.executeUpdate("insert into user_ticket values('"+ticketgenid +"','"+userobject.getusername()+"')");
jdbc.executeUpdate("insert into flight_route_ticket values('"+queryobject.getStartFlight() +"','"+ "','"+routerid+"','" +ticketgenid +"')");
String from_place=queryobject.getToPlace();
String temp_place="";
while(!(queryobject.getFromPlace()).equals(from_place)){
String inquery="select T.city,("+queryobject.getStartClass()+").seats from flight_route_scheme,route,(airport) S,(airport) T where flight_route_scheme.flightid='"+queryobject.getStartFlight()+"' and flight_route_scheme.rid=route.rid and route.startairport=S.airportid and flight_route_scheme.flag=1 and S.city='"+from_place+"' and route.endairport=T.airportid" ;
rs = jdbc.execute(inquery);
if(rs.next()){
temp_place=rs.getString(1);
}
else{
String error="Sorry No such flight available";
session.setAttribute("errormessage",error);
gotoPage("/error.jsp",request,response);
}
rs = jdbc.execute("select route.rid,("+queryobject.getStartClass()+").fare from flight_route_scheme,route,(airport)S,(airport)T where T.airportid=route.startairport and S.airportid=route.endairport and T.city='"+from_place+"' and S.city='"+temp_place+"' and route.rid=flight_route_scheme.rid and flightid='"+queryobject.getStartFlight()+"'");
String temprouteid="";
if(rs.next()){
temprouteid=rs.getString(1);
}
else{
String error1="Sorry No such flight available";
session.setAttribute("errormessage",error1);
gotoPage("/error.jsp",request,response);
}
jdbc.executeUpdate("update flight_route_scheme set ("+queryobject.getStartClass()+").seats =("+queryobject.getStartClass()+").seats+1 where flightid='"+queryobject.getStartFlight()+"' and rid='"+temprouteid +"'" );
}
if(returntrip=="yes"){
}
// jdbc.executeUpdate(qinsert);
*/ // }
jdbc.commitset(2);
jdbc.commitset(1);
//end of JAVA CODE
Session Management:
We have used session management to keep track of user profile, and when a user logs in/logs out.
A new session is created when the page is accessed first.
Within a session, information is stored about user (if any) logged in, and results of the query that he/she executes in order to book or cancel tickets.
A session is invalidated when a user logs out, or when there are multiple logins on the same account.
Storing the timetable for flights:
The date, time values are stored and compared using timestamp and date types in SQL. Extensive use is made of casting in order to obtain the appropriate values in the correct format from the queries.
Error Handling:
Appropriate error handling has been done in the project. All errors are routed to the pages which process the error according to some session attribute, and display appropriate messages. The following errors are detected and error messages are displayed: Whenever a user tries to book a ticket/ cancel a ticket, he is asked to login if he hasnt already done so, and is redirected to the main page. If a user tries to login using a wrong password, the system asks him/her to relogin. On querying for a flight schedule, we check that if the round trip option is selected then the date of the return journey must be after the date of the first trip. Due to session management, a user cannot ‘copy paste’ the link(URL) of an old session and try to run it again. If fields like the flightid while booking are left unspecified then the system indicates error and asks the user to correct it. Booking transactions are carried out as atomic transactions.
A Person can book/cancel a ticket only after he logs in.
A user can sign up for a profile if he doesnt have one already.
On logging in, the user has options to
1.Book tickets
2.Edit Profile information
3.Cancellation of tickets
4.View all current tickets booked by him
5.Logout
A person can get all information regarding a ticket if he keys in the ticketID.
A person can get all information about a flight if he keys in the flightID.
A person can get the schedule of all flights running for the next 20 days. The above transactions in general involve a sequence of queries to the database. Some of these queries are as follows:
Sample Queries:
1. Get all cities
select distinct city
from airport
where (airportid in (select startairport from route)) or airportid in ((select endairport from route));
2. Get flight data on a particular date:
with ridtable as
(select rid
from route
where (route.startairport in (select airportid
from airport
where city="CITYNAME") and route.endairport in (select airportid from airport where city="CITYNAME2")))
select flightid, rid, start_time, end_time, (bus).seats, (exec).seats, (econ).seats, (first).seats
from flight_route_scheme, flight
where (flight.flightid = flight_route_scheme.flightid ) and (flight.status="Active") and (rid in ridtable) and (YEAR=extract(year from start_time)) and (MONTH=extract(month from start_time) and DAY=extract(day from start_time)) ;
3. For morning flights:
Replace the select clause in query 2 by:
select flightid, rid, start_time, end_time, (bus).seats, (exec).seats, (econ).seats, (first).seats
from flight_route_scheme, flight
where (flight.flightid = flight_route_scheme.flightid ) and (flight.status="Active") and (rid in ridtable) and TRUNC(start_time)=DATE and (cast(start_time as time))>'05:00:00 AM' and (cast(start_time as time))<'04:00:00 PM';
For AM/PM flights, we modify the last part of the where clause appropriately
4. Ordering the flights in the above queries:
Add the clause : order by(cast(start_time as time)) desc; to the above query to order the flights in order of departure times.
5. To get all info about a ticket from the ticket id:
select username, name, age, sex, booking_date, status, schemeid, rid, flightid, class
from flight_route_ticket natural inner join user_ticket natural inner join passengerticket natural inner join ticket
where ticketid='0000000001';
6. Get city and airport names of start and destination from the airport id:
select S.city, T.city , S.name, T.name
from (airport) S, (airport) T
where (S.airportid in(select startairport from route where rid='R0001')) and (T.airportid in (select endairport from route where rid='R0001'));
7. Get city and airport names of start and destination from the route id
select S.city, T.city , S.name, T.name
from (airport) S, (airport) T
where (S.airportid in(select startairport from route where rid='R0001')) and (T.airportid in (select endairport from route where rid='R0001'));
8. Get all tickets booked by a user profile:
select username, name, age, sex, booking_date, status, schemeid, rid, flightid
from flight_route_ticket natural inner join user_ticket natural inner join passengerticket natural inner join ticket
where user_ticket.username= 'san'; 9.Get all information about a flight from the flightid: select flightid, cast(start_time as date) as A, startairport, cast(end_time as date) as B, endairport , S.city, T.city
from (flight_route_scheme) F ,route,(airport) S, (airport) T
where flightid='I0004' and F.rid=route.rid and startairport=S.airportid and endairport=T.airportid; Below is a snippet of JAVA code used to book the flight tickets:
rs = jdbc.execute("select max(ticketid)+1 from ticket");
String ticketgenid="0000000000";
if(rs.next()){
String temp=Integer.toString(rs.getInt(1));
ticketgenid=ticketgenid+temp;
ticketgenid=ticketgenid.substring(ticketgenid.length()10);
}
else{
ticketgenid="0000000000";
}
rs = jdbc.execute("select route.rid,("+queryobject.getStartClass()+").fare from flight_route_scheme,route,(airport)S,(airport)T where T.airportid=route.startairport and S.airportid=route.endairport and T.city='"+queryobject.getFromPlace()+"' and S.city='"+queryobject.getToPlace()+"' and route.rid=flight_route_scheme.rid and flightid='"+queryobject.getStartFlight()+"'");
String routerid="";
int fare_paid=0;
gotoPage("/index.jsp",request,response);
if(rs.next()){
// routerid=rs.getString(1);
// fare_paid = rs.getInt(2);
}
else{
String error="Sorry No such flight available";
session.setAttribute("errormessage",error);
gotoPage("/error.jsp",request,response);
}
String qinsert=" ";
/*
jdbc.executeUpdate("insert into ticket values('"+ticketgenid +"',trunc(current_date),'"+fare_paid+"')");
jdbc.executeUpdate("insert into passengerticket values('"+ticketgenid +"','"+name+"','"+age+"','"+sex+"','" +passportid+"','"+address+"','"+schemeid+"','"+queryobject.getStartClass() +"',trunc(current_date),'"+fare_paid+"')");
jdbc.executeUpdate("insert into user_ticket values('"+ticketgenid +"','"+userobject.getusername()+"')");
jdbc.executeUpdate("insert into flight_route_ticket values('"+queryobject.getStartFlight() +"','"+ "','"+routerid+"','" +ticketgenid +"')");
String from_place=queryobject.getToPlace();
String temp_place="";
while(!(queryobject.getFromPlace()).equals(from_place)){
String inquery="select T.city,("+queryobject.getStartClass()+").seats from flight_route_scheme,route,(airport) S,(airport) T where flight_route_scheme.flightid='"+queryobject.getStartFlight()+"' and flight_route_scheme.rid=route.rid and route.startairport=S.airportid and flight_route_scheme.flag=1 and S.city='"+from_place+"' and route.endairport=T.airportid" ;
rs = jdbc.execute(inquery);
if(rs.next()){
temp_place=rs.getString(1);
}
else{
String error="Sorry No such flight available";
session.setAttribute("errormessage",error);
gotoPage("/error.jsp",request,response);
}
rs = jdbc.execute("select route.rid,("+queryobject.getStartClass()+").fare from flight_route_scheme,route,(airport)S,(airport)T where T.airportid=route.startairport and S.airportid=route.endairport and T.city='"+from_place+"' and S.city='"+temp_place+"' and route.rid=flight_route_scheme.rid and flightid='"+queryobject.getStartFlight()+"'");
String temprouteid="";
if(rs.next()){
temprouteid=rs.getString(1);
}
else{
String error1="Sorry No such flight available";
session.setAttribute("errormessage",error1);
gotoPage("/error.jsp",request,response);
}
jdbc.executeUpdate("update flight_route_scheme set ("+queryobject.getStartClass()+").seats =("+queryobject.getStartClass()+").seats+1 where flightid='"+queryobject.getStartFlight()+"' and rid='"+temprouteid +"'" );
}
if(returntrip=="yes"){
}
// jdbc.executeUpdate(qinsert);
*/ // }
jdbc.commitset(2);
jdbc.commitset(1);
//end of JAVA CODE
Session Management:
We have used session management to keep track of user profile, and when a user logs in/logs out.
A new session is created when the page is accessed first.
Within a session, information is stored about user (if any) logged in, and results of the query that he/she executes in order to book or cancel tickets.
A session is invalidated when a user logs out, or when there are multiple logins on the same account.
Storing the timetable for flights:
The date, time values are stored and compared using timestamp and date types in SQL. Extensive use is made of casting in order to obtain the appropriate values in the correct format from the queries.
Error Handling:
Appropriate error handling has been done in the project. All errors are routed to the pages which process the error according to some session attribute, and display appropriate messages. The following errors are detected and error messages are displayed: Whenever a user tries to book a ticket/ cancel a ticket, he is asked to login if he hasnt already done so, and is redirected to the main page. If a user tries to login using a wrong password, the system asks him/her to relogin. On querying for a flight schedule, we check that if the round trip option is selected then the date of the return journey must be after the date of the first trip. Due to session management, a user cannot ‘copy paste’ the link(URL) of an old session and try to run it again. If fields like the flightid while booking are left unspecified then the system indicates error and asks the user to correct it. Booking transactions are carried out as atomic transactions.
External Interface Requirements
The web is used as an interface with the users.
Hardware Interfaces:
No special hardware is required for this product.
Software Interfaces: 1. Java
2. JDBC
3. Apache Tomcat servlets
4. Oracle 8i SQL server
Post a Comment