Air-Ticket Reservation System

[shujat132]

Hi
I am fresh Graduate, but actually I am little bit week in databases, so I am trying to improve my skills, that is why I am reading a book of database, where a case (details of case study and tasks are attached in “case study.doc” document) is giving to handle.
So now I have to problems.

1. I have done its first part, which consist of 3 sub parts, and that is attached in document “Solution First task.doc”. Please check that and tell me whether I have done me correctly or not.
2. Second problem is that I am unable to deal its 2nd task, so I need your help. Please guide me for second parts details of 2nd task are given in document “second parts.doc”.

Thanks

[LastMitch]

I have done its first part, which consist of 3 sub parts, and that is attached in document “Solution First task.doc”. Please check that and tell me whether I have done me correctly or not.
Second problem is that I am unable to deal its 2nd task, so I need your help. Please guide me for second parts details of 2nd task are given in document “second parts.doc”.

You need to post what's inside those doc. You can't expect someone to download the doc and see what you are doing?

[shujat132]

*

case study.doc

*
Task#1:

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

Task#2:

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

Task#3:

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

**

Heading Here

Case Study

Heading Here

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).

check attached case study picture

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.

--- Best of Luck ---

Solution First task.doc

Task#1:

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

Entity Relationship Diagram
check attached image(case study diagram.jpg) which i have made

Task#2:

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

Tables

Jets

JetID   Number  Primary Key
JetName Text    
JetType Text    

Flights

FlightID            Number  Primary Key
FlightDate          Date    
FlightJetID         Number  Foreign Key (Refers JetID in Table Jets)
FlightSource        Text    
FlighDestination    Text    
FlightTime          Text    
RunWayID            Number  Foreign Key (Refers RunWayID in Table Runways)
ReservedBy          Number  Foreign key (Refers StaffMemberID in Table Staff)

Passengers

PassengerID     Number  Primary Key
PassengerName   Text    
PassengerCell   Text    

Staff

StaffMemberID           Number  Primary Key
StaffMemberName         Text    
StaffMemberDesignation  Text    
StaffMemberContact      Text    

Tickets

TicketID    Number  Primary Key
TicketType  Text    
PassengerID Number  
FlightType  Number  Foreign Key (Refers FlightID in Table Flights)

Announcements

AnnouncementID          Number  Primary Key
AnnouncementText        Text    
AnnouncementStartDate   Date    
AnnouncementExpDate     Date    
StaffMemberID           Number  Foreign Key (Refers StaffMemberID in Table Staff)

Runway

RunwayID        Number  Primary Key
RunwayTrack     Text    
TowerAssigned   Number  

So an abstract graphical view of the database may look like:
check image (tableInAccess.jpg)

Task#3:

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

De-normalize

Table collapsing: table collapsing de-normalization technique is used on one-to-one and many-to-many relations. In ER model given in task-1 we do not have man-to-many relations, however, one-to-one relations were identified between “Tickets”, “Passengers” entities and “Runway”, “Flights” and “Jets” entities.
First we collapse the “Passengers” and “Tickets” tables. We suppose that “PassengerCell” attribute is unnecessary so finally the resultant “Tickets” table after collapsing becomes:

Tickets

TicketID        Number  Primary Key
TicketType      Text    
PassengerID     Number  
PassengerName   String  
FlightID        Number  Foreign Key (Refers FlightID in Table Flights)

Next we collapse “Runway”, “Flights” and “Jets” tables

First collapsing “Runways” table with “Flights” (suppose we need “RunwayTrack” field only) we get:

Flights’

FlightID            Number  Primary Key
FlightDate          Date    
FlightJetID         Number  Foreign Key (Refers JetID in Table Jets)
FlightSource        Text    
FlighDestination    Text    
FlightTime          Text    
RunWayID            Number  Foreign Key (Refers RunWayID in Table Runways)
RunwayTrack         String  
ReservedBy          Number  Foreign key (Refers StaffMemberID in Table Staff)

Then we merge “Jets” Table with “Flights”. The final structure of “Flights” table becomes:

Flights

FlightID            Number  Primary Key
FlightDate          Date    
FlightJetID         Number  Foreign Key (Refers JetID in Table Jets)
JetName             String  
JetType             String  
JetType             String  
FlightSource        Text    
FlighDestination    Text    
FlightTime          Text    
RunWayID            Number  Foreign Key (Refers RunWayID in Table Runways)
RunwayTrack         String  
ReservedBy          Number  Foreign key (Refers StaffMemberID in Table Staff)

Finally the abstract view may look like:
please check attached image(finalShape.jpg)

second task.doc

Task:

In Sub Task no. 3 of first Task, you denormalized all one-to-one and many-to-many relationships; in this assignment you will denormalize all one-to-many relationships by taking into account the same Air Ticket reservation system, in order to optimize it further.

So, de-normalize all one-to-many relationships (left after Sub Task no. 3 in Task no. 1) using Pre-joining technique.

The pre-joined tables should:

• Contain no redundant columns (matching join criteria columns)
• Contain only those columns absolutely necessary for the application to meet its processing needs

Note:
i need to show the database resulted from the previous first task (after denormalization) and the resultant database after performing pre-join denormalization in this task.

[shujat132]

i have attached all images at once, but all were not uploaded
so now remaining images are attached,
please check them also

[LastMitch]

I have done its first part, which consist of 3 sub parts, and that is attached in document “Solution First task.doc”. Please check that and tell me whether I have done me correctly or not.

This is not an issue. You did your work already.

Second problem is that I am unable to deal its 2nd task, so I need your help. Please guide me for second parts details of 2nd task are given in document “second parts.doc”.

This is your 2nd task:

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

Follow the example here (Normalization):

http://www.deeptraining.com/litwin/dbdesign/fundamentalsofrelationaldatabasedesign.aspx

You have the original data so it would be easier for you do it and create the tables.

Just copy and paste the data into tables like the one in the link and then create query.

What you post is good I don't have a table structure you do so it would quicker if you did it.

It's not creating a diagram (image) you are using a actual database table to do this. Then create a image of the database table like the same one in the link.

[shujat132]

thank you so much LastMitch for you reply
but
actually i belive my table is already in 3rd NF, isn't?
also i wanna solve this entire case rather any other example, that example is not useful for me

and my next task is

Task:

**In Sub Task no. 3 of first Task, you denormalized all one-to-one and many-to-many relationships; in this assignment you will denormalize all one-to-many relationships by taking into account the same Air Ticket reservation system, in order to optimize it further.

So, de-normalize all one-to-many relationships (left after Sub Task no. 3 in Task no. 1) using Pre-joining technique.

The pre-joined tables should:

Contain no redundant columns (matching join criteria columns)
Contain only those columns absolutely necessary for the application to meet its processing needs
Note:
i need to show the database resulted from the previous first task (after denormalization) and the resultant database after performing pre-join denormalization in this task.
**

so please help me for task
thanks