Airline Reservation System In this project, you will design and implement a simple interactive airline reservation system similar to orbitz (www.orbitz.com). 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. Requirements 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 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.

Airline Reservation System

In this project, you will design and implement a simple interactive airline reservation system similar to orbitz (www.orbitz.com). 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.

Requirements

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

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

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