Study On Online Flight Booking Systems Computer Science Essay

Published: Last Edited:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

Having identified the technologies we wanted to use, and a schedule of work, we immediately started work on a prototype system in Microsoft Access. This helped us to refine our ideas. This page is an informal account of how we started.

Justin Barratt created the logical data model, identifying the entities, primary keys and attributes, and their cardinality (logical terms, but, in essence, a physical database of tables, columns, data-types, constraints etc.) He then built a form, which used a number of SQL views/queries, to help the group visualise how a flight booking page might work, and to demonstrate working elements within a real database. The insert below shows the flight search page from this prototype system.

Having demonstrated how he built the prototype and test data, Justin went on to build the physical database in SQL Server and made this database available to others. Noman immediately started work on the database connectivity aspect to the groups chosen web-platform,

Knowing Noman to be a bit of a graphics wizard, (the Spacejet logo is his work) he was asked to deliver the roughest database connectivity example he could muster-with no formatting or time spent or fancy presentation whatsoever-and he delivered very quickly. Noman pieced together the framework for the remaining pages and the all-important site security. He demonstrated his findings, which allowed the team to visualise the overall design of the Spacejet Booking System, and showed others how is used.

Having ticked almost of the all planning items listed under the schedule of work, we started work on the development.

The Spacejet Booking System

The core elements of the Spacejet system are standard online database application components (a back-end, a middle-tier, and a front-end) and don't require introduction as high level objects. However, the site security element does deserve a quick mention.'s in-built Membership Provider is a reusable component for managing web-application user accounts and roles. It is separate from the System Database, and the accounts it generates are referenced by the Spacejet database. The Security Database AccountId is inherited as an 'external foreign key' in Spacejet Customer table.

The Back-End

In a real system, we would have, of course, spent time identifying every possible data attribute that a real system might need but we thought this unnecessary. We concentrated on identifying the core tables and mandatory data columns only. For instance, the customer table in the below diagram does not include address and post code fields - to include them would require additional effort for the web-developer, and take longer to demonstrate. The group concentrated on roughing-out the system before looking at the detail.

The Customer Table

Admittedly, the term customer is a little vague for a flight booking database, but it is intentional. Our customer table stores both passengers and bookers. A booker is someone who books flights; for themselves or for others (when a secretary books a flight for a colleague or when a parent books a flight for a child, for instance). Passengers and Bookers are people, who share common attributes. Therefore, we decided to store these records in a single table. The merging of bookers and passengers does make our model harder to comprehend-beyond 3NF-- but we thought it important to include this in our design.

Separate to the internally generated candidate key, CustomerNo, the customer table has two alternate keys, one of which is null-able. The CustomerEmail field is equivalent to a UserId for non-account holders; it is used to identify a returning guest customer. This value is mandatory. In the real world, husbands and wives often share email addresses, so this identifier may not be a good choice of key but, for the purposes of our assignment, we thought it adequate. The CustomerAccountId is the external id generated by the security database. This value is optional, to allow guest users.

The Booking Table

A booking can be likened to an electronic basket to which flights are added. It is the header which binds one or more booked flights together. The foreign key CustomerNo is an 'optional' value which 'may refer to' a customer record, in this instance the customer is a 'booker'. When a guest is adding flights to a basket, the CustomerNo is null (unknown) until the booking is confirmed. The BookingTotal value is stored instead of derived, because a change in flight price after booking would result in an incorrectly derived value.

The Booked Flight Table

A BookedFlight record links the booking to one or more chosen flights, and documents the number of tickets/seats required for each. This table is the physical implementation of the many-to-many relationship between the Booking and Flight tables. This tables' identity is inherited from both parent tables to form a composite primary key.

The Ticket Table

The Ticket table is another physical representation of a many-to-many relationship, but this time between the BookedFlight and the Customer tables. In this instance, each ticket record is 'owned by' a passenger. The referenced customer is the 'holder' of a ticket. Although not shown, a candidate key column, named TicketNo, could easily be generated for a ticket.

"The cardinality 'owned by' in the above paragraph should not be confused with the relational term 'owned by' which describes dependency between a parent and child table." JUSTIN BARRATT

The Airport Table

The Airport table is a list of Airports, both places of departure and arrival.

The Flight Table

The Flight table is a list of scheduled flights, which are available for booking. This table 'refers to' the Airport table twice, once for the place of departure and once for the flight destination. The seat availability is managed simply, by way of the counter, FlightSeats, which is reduced as flights are booked and increased when cancelled.

Specialization: The word 'airport' does not accurately reflect our space travel theme, but we chose this name for clarity. However, by doing so, we have 'specialized' the system for air travel only. By renaming the table 'Airport' to 'Port' and the table 'Flight' to 'Journey' the system could be enhanced to allow bookings for a number of transport modes.

The Middle-Tier

Our use of the term 'middle-tier' refers to a collection of stored procedures in our database. The use of this term may not be entirely correct. One of our reasons for using stored procedures was to facilitate the merging of systems in the next assignment; we thought our ready-made disassociation from the database would help. The main reason was to simplify the development of the front-end. Stored procedures are not entirely disassociated from the backend; of course, because these are components managed within the Relational Database Management System (we must connect to the system database to use them). But we thought this was the right choice for this assignment.







Though simply named, most of our stored procedures encapsulate complex actions which we deemed too convoluted for the web-developer to program.

Note: The system diagram does not show a direct connection between the website and the database, but one exists.

The spCustomerProfileAdd Procedure

When registering an account via the web-site, the user account information is saved to the security database. At this point we capture the details entered, including the unique account id generated by the security database, and save this information to the customer table. This provides a working copy of the customer details in our system database from registration.



The member account to be referenced.


The name of the customer.


The email address of the customer.



The generated customer number.

The spCustomerNumberGet Procedure

The spCustomerNumberGet stored procedure is used to retrieve a CustomerNo form the database. When logging on to the site, the user account details are retrieved from a table in the Security Database but the System Database CustomerNo is not yet known. spCustomerNumberGet retrieves the CustomerNo for a given CustomerAccountId. Not every user creates an account on the site, and this why there is separate identification for Customers and Accounts.

Parameters: @AccountId

Return: @CustomerNo

The spBookedFlightAdd Procedure

On adding the first flight to the basket, this procedure first generates a new Booking record and then adds the flight, by way of an inserted BookedFlight record. In addition to the inserted BookedFlight record, a number of ticket records are inserted. The ticket numbers (the seat numbers 1 to 8) are derived from a Left-Outer-Join between the Ticket table and a virtual 'FreeSeats' table. This reveals a list of un-booked seat numbers on the flight. The procedure loops through the free seats, and inserts a ticket record for each passenger. Finally, after the required number of tickets has been added, the stock level is reduced in the Flight table.



The flight to be booked.


The number of tickets required.


The booking. New booking created if null.


The customer. Guest assumed if null



The booking. The generated basket number.

The spBookedFlightRemove Procedure

When searching for flights, this procedure allows flights to be removed from the basket. It also deletes generated ticket records, by way of a cascading delete, and restores the seat availability in the flight table. The procedure also deletes the booking record, when the last flight is removed from the basket. Incidentally, our multi-process stored procedures all use 'Begin Transaction' and 'End Transaction' statements to ensure that the work is completed as a unit.



The booking.


The flight to remove.


Nothing returned.

The spBookingCustomerAllocate Procedure

At the point of confirming a new booking, a guest user is asked to enter the name and email address. Prior to this point, the customer is unknown and the basket is ownerless. If the customer has booked flights previously, as a guest or otherwise, or was included in someone else's passenger list, the spBookingCustomerAllocate procedure attempts to find this record. A customer email address search is performed and the customer number is returned if found. If a matching customer is not found then a new customer record is inserted and a newly generated customer number is returned instead.



The booking number.


The customer name.


The customer email.



The found or newly created customer number.

The spTicketCustomerAllocate Procedure

When adding a flight to the basket, ticket records are generated for the number of passengers specified. At this point, however, the tickets are passenger-less. This is because the passenger details are given after the tickets have been reserved. The TicketCustomer-Allocate procedure is called iteratively for each passenger entered. It receives the parameters @BookingNo, @CustomerName, and @CustomerEmail, and does two things. First, it attempts to locate a matching customer record and inserts one if not found. Second, it allocates the ticket to the found or inserted Customer record.



The booking number.


The customer name.


The customer email.



The number of the seat allocated.

The Front-End

We found the Integrated Development Environment (IDE) in Visual Web Developer 2010 surprisingly easy to use, though some group members found the concepts of event-driven programming and OOP difficult to grasp. The interface controls, such as dropdown lists and data grids, etc, are easily placed (though not so easily positioned as win-form controls) and can easily be connected to a database via connection objects. We needed to write a number of 'events' to action button clicks and grid-row selects etc. As mentioned on the Technologies page, our chosen 'code-behind' (the term used for the language option in is

Home Page

The Booking Wizard

Step 1: Select Fights

Step 2: Add Passengers

Step 3: Payment Details

Step 4: Confirmation

The Registration Page

The Login Page

The Booking Information Page

The Admin Page

The Home Page

The Home Page is the first page to be displayed when a user navigates to the site. At the top of this page, a menu bar shows links to the Booking Information and Registration pages. There is also a link to the Login page, via a button. The most obvious feature of this page is the Flight Search facility. The user can search the flights on using the following criteria.

Departure Airport

Arrival Airport

Depart Date

Return Date

No. Of Passengers

The Booking Wizard

When a user initiates a search from the home page, they are redirected to the Booking Wizard. The Booking Wizard is a four step process, which splits the booking experience into smaller parts, making it convenient for them to book tickets. This wizard is based on ASP's View Control which is a part of .net Framework. The Booking Wizard also has a progress bar at the top which informs the user about the completed steps; the current step and the remaining steps. These are highlighted with different colours.

Step 1: Select Fights

Step 2: Add Passengers

Step 3: Payment Details

Step 4: Confirmation

Step 1: Select Flights

This page (wizard page) is where the user is presented with the results of their initial flight query. The user can refine their search from this page. For clarity, and convenience, the flight search results are divided into outbound and inbound grids. The grids are populated with SQL, which is dynamically constructed from the search criteria. The grids display the following flight details.

Flight Number

Departure Date and Time

Arrival Date and Time

Number of Seats Available


Note: By default, the user is presented with flexible +/- 1 day flights.

Grid Rows are selected individually, and this enables the user to select a flight from each grid and then add the selected flights to the basket by clicking on the "Add Flight(s) to Basket" button. The "Add Flight(s) to Basket" button only gets enabled when the user selects a flight from the grid. The basket holds the information about the flights selected by the user showing the number of flights and the total fare. The user can remove any flight from the basket by selecting the flight and clicking "Remove selected flight from Basket".

Step 2: Add Passengers

After adding the flights to the basket, the user proceeds to this step to add the details of the passengers travelling on the flight in the Passenger Grid. It may be the case that the person who is booking the flight might not be travelling but the system requires storing the information of that user. The user is asked to fill out the details in the boxes labelled "Who is booking?" and "Send confirmation e-mail to?" If the user is already logged in, these fields are automatically populated from the session variables. There is also a checkbox with label "Are you travelling on this flight". By selecting this check the Customer Name and Email is added to the passenger details grid. This saves the user from retyping the Name and Email. Once the passenger details are validated the user continues to the payment details.

Step 3: Payment Details

At this step, the user is presented with the payment options to confirm their booking and get the tickets. The current system accepts only credit card as the payment method. Currently there is no authentication process for credit card in this system because that is beyond the scope of this project. After entering the payment details the user confirms the booking by clicking on the "Confirmation" button.

Step 4: Confirmation

Confirmation is the last step of the booking wizard; this page confirms that the flight has been booked. An email sent to the customers address also confirms the booking. The unique booking number is automatically generated by the system.

The Registration Page

This page allows a user to register their details to the site. The user is asked to provide their name (preferred user name), password, email address, security question and answer. This registration form is based on's built in create user wizard control which is linked to the membership database.

The Login Page

This page allows a user to logon to the site. It shows textboxes for a username and password and a button to submit these details. The login form is also based on built in login control, which validates the username and password against the membership database. A successful logon redirects the user to the home page. An authenticated administrator is redirected to the Admin page.

The Booking Information Page

This page has a grid which retrieves the Booking information relating to a specific booking number from the database. If a customer wants to get information related to booking, he has to enter the booking number and click "Retrieve Information" button which fills the grid with all the flights related to that booking number. At this stage the system does not allow the user to edit the booking details e.g. changing the passenger details.

The Admin Page

If the logged in user is an administrator, he will be redirected to this page. All other users will be redirected to the login page. This page serves as an Admin panel where an administrator can have access to different maintenance options. At the moment we only have Flight Maintenance section available. The Flight Maintenance 'Filter By' dropdown shows a single option, currently Flight No.), a textbox to enter the value i.e. Flight No. and a "Search" button. The administrator will get the results of the query in grid view control. The administrator can update or delete any flight from this page.


One of the groups' key objectives was to ensure that every team member met or exceeded the learning outcomes required by this assignment. The group set a mandatory requirement that every member will download and install SQL Server 2008 Express and Visual Web Developer Express ( and learn how to use them, in order to contribute to the work. Once done, Justin demonstrated SQL Server and Noman demonstrated Visual Web Developer. Both Justin and Noman provided training and set tasks for the less experienced members. Noman introduced the group to Google Wave and Team Viewer, the online collaboration tools, so we had every means of communication. It was accepted that some members had more to learn than others, but we agreed that we would each all like to give and receive an equal contribution in the peer assessment--a team effort. However, despite all this opportunity, sadly, some members contributed nothing at all.

Some of us have learnt a great deal about Online Database Applications programming.

Navigation Map

User Guide

Getting Started

You can browse the Spacejet website, search for flights, book flights as a guest or as a registered user. To use the Spacejet Booking system you will need a computer with an internet connection and a browser such as Internet Explorer or Google Chrome.


You can register by going to the registration page, which is accessible via the register button on the menu bar.

On the registration page you have to fill out a small and easy form with the following details.

On successful completion of the registration process the following message will confirm that your account has been successfully created.

Click "Continue" to be redirected to the home page. You are ready to search for flights.

Logging in

As a registered user, you will have a username and password. Enter the login page by clicking the "login" button on the menu bar, at the top of the page.

The login page shows two entry boxes; one for your user id and one for you password. Enter these details in the boxes provided and click the Login button.

If the login validation is not successful, you will not be able to continue.

After successful logon, you will be redirected to the home page where you will see a welcome message, showing your name, at the top of the page. The login button has now changed to logout-as you are already logged in, of course. Don't forget to logout before leaving.

Browsing Flights

You can start you flight search from the quick search facility which is available on the homepage.

To search for a flight you have to:

Choose where you are Flying From, from the dropdown list.

Choose where you are Flying To, from the dropdown list.

Select the Depart date - click the box to view the data-picker.

Optionally, select the Return date - click for data-picker.

Select the number of Passengers travelling.

Click the Search Flights Button

Selecting Flights

After searching for flights you will be redirected to the flight selection page where you will see the results of your query in a formatted grid/s. There are two grids on the page; one for outbound flights and one for inbound flights-as indicated by the labels above them. To add flights to your basket you must:

Select the required flight(s) from the grid - click 'select'.

Click the "Add Flight(s) to Basket" button

After adding one or more flights to your basket, you will see an updated label showing the number of flights in the basket and the total cost. To remove the flights from the basket you must:

Select the flight you want to remove from the basket.

Click the "Remove Selected Flight from Basket" button.

If you are happy with the flights in your basket, click the 'Add Passengers' button.

Adding Passenger Details

The Add Passengers page is where you enter the details of the passenger/s travelling on the flight/s you have chosen. The first two fields on this page are about the person booking-you! Because you have logged in, the boxes labelled "Who is booking the flight?" and "Send confirmation E-mail to:" are automatically populated with your details. Only a guest user is required to enter information in these fields.

If you are travelling on the flight/s please tick the box labelled "Are you travelling on this flight?" This will copy your details to the passenger list automatically.

Payment Details

This page is where you enter payment details and make your payment. You will see the following fields on the payment details page. Complete all the fields and click on the "Confirmation" button.


If your payment is successful you will receive an on-screen confirmation message informing you that your booking has been completed. You will be provided with a unique Booking Number which holds the information about your booking.

You will also receive a confirmation email with the booking number.

Monitoring your progress

A progress bar is displayed on the top of page during the booking process which will notify you about the completed steps, the current step you are in and the steps which needs to be completed.

Retrieving Booking Information

You can also view the details of your booking on our website. To do this you have to visit the booking information page from the link available in the top menu bar.

The booking information page contains a field where you have to enter your booking number. After entering the number click "Retrieve Information" button.

If the Booking number is correct then you will see the details of your booking on the same page.

Getting Help

We hope you have found the Spacejet Booking system simple to use and self-explanatory. However if you have any difficulty in using the site, or come across any issues, please contact us at: [email protected] Don't forget to include your name (and booking details if you have them) with a clear description of the problem. We will do our best to fix the problem as soon as possible.

Thank you!

The Spacejet Team.