Data warehousing (CS614)
Assignment # 1
Total
marks = 20
Deadline Date = 29-04-2013
Please carefully read the following instructions before
attempting the assignment.
Rules for Marking
It
should be clear that your assignment would not get any credit if:
- The assignment is submitted after due date.
- The submitted assignment does not open or file is corrupt.
- The assignment is copied. Note that strict action would be taken
if the submitted assignment is copied from any other student. Both
students will be punished severely.
1) You should consult recommended books to clarify your concepts as
handouts are not sufficient.
2) You are supposed to submit your assignment in .doc format. Any other formats like
scan images, PDF, Zip, rar, bmp, docx etc will not be accepted
3)
You are advised to upload your assignment at least two days before Due
date.
4)
This assignment file
comprises of Seven (7) pages.
Important Note:
Assignment comprises of 20 Marks. Note that
no assignment will be accepted after due date via email in any case (whether it
is the case of load shedding or emergency electric failure or internet
malfunctioning etc.). Hence, refrain from uploading assignment in the last hour
of the deadline, and try to upload Solutions at least 02
days before the deadline to avoid inconvenience later on.
For any query please contact: CS614@vu.edu.pk
Objective:
The objective of the assignment is to revise
the old concepts as well as setting focus on the new ones. Self practice will
not only clear your concepts further but will also provide a strong base in
future.
Reference:
Please
refer to the case study given at the end and complete the following tasks:
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.
Case Study
Air-Ticket Reservation System
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
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.
--- Best of Luck ---
