CS614 Data Warehousing Assignment 1 Solution Spring 2013

Task#1 [Marks: 5]:

Draw an Entity Relationship Diagram (ER Model) for the given case study along with proper associations and cardinalities.


Task#2 [Marks 5]:

Derive the tables from the ER model and normalize up to third normal form (3rd NF)


Task#3 [Marks 10]:

De-normalize the database (obtained after completing task#2) by using the “collapsing tables” technique.


Section 1: Introduction


1.1            Vision Statement:



1.1.1        Software Purpose


The main purpose of this software is to reduce the manual errors involved in the airline reservation process and make it convenient for the airline staff to perform their daily reservation related tasks e.g. tickets reservation, flight scheduling, announcements etc. in automated way.


1.1.2        Software Scope


This software provides options for passengers to view and search different flights along with their timings and reservation details for a particular date.


For administrators (staff members), it will facilitate them to manage the route schedules (adding, editing, canceling and viewing routes), reservations (booking, modifying, canceling and viewing) and staff.


1.1.3        Software Perspective


The Airline reservation system (ARS) is an independent application. It is a self-contained product, designed for Local Area Network (LAN) with easy-to-use and interactive graphical user interfaces. Separate interfaces will be provided for passengers and administrators. Passengers will be able to use the passenger-interface (on dedicated terminal computers) within the office premises to view, search and print the flight schedules, see flash news and news alerts etc. The administrators on the other hand will be able to use the given administrator-interface to manage ticket reservations and route schedules etc. However, all the information will be stored in a central database and both the passengers and administrators will use this information to perform their permitted activities. Also note that access to administrator-interface will be role based (i.e. different staff members will have different access level) while the passengers (using passenger-interface) will not require any authentication (i.e. any passenger can use it).





1.2      Document conventions and definitions:


ARS    – Airline Reservation System

LAN    – Local Area Network

GUI     – Graphical User Interface

OS       – Operating System

Admin – Administrator
Section 2: Overall description


2.1            User characteristics


No technical experience is required, basic knowledge of handling system is sufficient.

Users will use the system at three levels:


1) Super-admins

2) Sub-admins

3) Passengers


Super-admins include members from higher management. They will have full rights of the system, including all the sensitive tasks e.g. add, edit Flight schedule.


Sub-admins include the operating staff. They will have rights of passenger reservation including reserving, editing, viewing and canceling tickets.


Passengers, on the other hand will have access to see news, search route schedules, available seats and print these details.



2.2            Operating environment


ARS will be a client/server based system, running on LAN. Both clients and servers will be Linux based systems.


Entire application along with database will be installed on the central server. Administrators and passengers will be able to perform the intended functionality through GUI interfaces provided on individual machines.


Separate Terminal computers (3 to 4) will be provided to passengers, where they will be able to see and search flight schedules, available seats and announcements etc. A printer will also be installed with theses terminals so that passengers could print details of flight (and seats) they selected.

2.3       External Interfaces


The application will also need to connect to local airport services database system to get the current status of the runway and other notifications etc. The super admins will use this information to reserve the runway for them and to schedule their flights. Application will connect with this database system through provided interface.


The system also needs to connect to an international weather forecasting service to get weather forecasting details. This information will also be used for flight scheduling.


2.4            Assumptions and dependencies


It is assumed that passengers will first see the flight schedules and fare details from the passenger terminal computers (dedicated only for the passengers). After selecting a particular flight and seat, they will get print of it and will go to reservation booth, where sub admin staff member will reserve ticket for them by seeing the printed specification.


Also, any breaking news e.g. modification of flight timing etc. will be immediately added to the system by admins, and will immediately be visible through passenger interface.