Airline Reservation System

Airline Reservation System
In this project, you will design and implement a simple interactive airline reservation system similar to orbitz ( The system is

a typical client-server application with a web-based front-end for the user and a database backend. This project will focus only on the backend

and NOT on the front end. This document provides a high-level description of the project and outlines the deliverables expected of your team. It

is crucial that you have a good solid design before you start coding.
The purpose of the online system is to create convenient and easy-to-use online system for passengers, trying to buy airline tickets. The system

is based on a relational database with its flight management and reservation functions. We will have a database supporting dozens of major

cities around the world as well as hundreds of flights by various airline companies. Above all, we hope to provide a comfortable user experience

along with the best pricing available.
Project is worth 20% of your final grade.
Each team can have maximum of three members.
Users of the system should be able to retrieve flight information between two given cities with the given dates/times of travel from the

database. A route from city A to city B is a sequence of connecting flights from A to B such that: a) there are at most two connecting stops,

excluding the starting city and destination city of the trip, b) the connecting time is between one to two hours. The system will support two

types of user privileges, Customer and Employee. Customers will have access to customer functions, and the employees will have access to both

customer and flight management functions.
The customer should be able to do the following functions:
• Make a new reservation.
o One-way
o Round-Trip
• Cancel an existing reservation.
• View his itinerary.

The Employee should have following management functionalities:
• Customer functions.
• Reporting
o Get all customers who have seats reserved on a given flight.
o Get all flights for a given airport.
o Calculate total sales for a given flight.
• Administrative
o Add/Delete a flight
o Add a new airport
o Update fare for flights.

Each flight has a limited number of available seats. There are number of flights that go from/to different cities at different dates and time.
Part 1: Database Design Document (Due Date: March 30th): (20 points)
This will include a precise description of the project as you understood it, the complete ER diagrams outlining the database schema, and the

tools and technologies that you intend to use for the project. Convert the E/R diagram to a relational schema. Indicate primary keys, foreign

keys, triggers and any other constraints you may have. Clearly specify any assumptions you make and your rationale.
Implement the SQL database schema for the design using CREATE TABLE commands. Have enough cities, flights and flight legs to test the above

functionalities. Assume suitable data types for the attributes.
To Submit:
• E/R model and relation schema of your database.
• SQL queries used to create the tables.

Part 2:PL/SQL Code along with the Admin and User functions(50 points)
This part of the project should have all the functions implemented in PL/SQL. For example: the admin should be able to create new flights, which

would be entered in the appropriate table, the user should be able to make a reservation, etc.
To Submit:
• PL/SQL code for all the working functions.
• A snapshot of each of the functions working
• A snapshot of the reporting of the admin
Final Project Demo: (30 points)
You will come up with a summary report which outlines your learning experience in the project and identify changes/modifications to the design.

Include the contributions of both team members.