Database Design Project: Jet Airways Flight Booking System

Desklib provides solved assignments and past papers to help students succeed.

Table of Contents
Introduction................................................................................................................................2
Task No. 1 Understanding of database......................................................................................3
Task No. 2 Conceptual Modelling.............................................................................................8
Task No. 3 Logical Modelling.................................................................................................11
Task No. 4: Physical Modelling..............................................................................................14
Conclusion................................................................................................................................18
References................................................................................................................................19
List Of Figures
Figure 1: Hierarchical Database Model.....................................................................................4
Figure 2: Network Database Model (Source: Owlgen, 2019))............................................5
Figure 3: Relational Database Model (Source: Diranieh.com, 2019)................................6
Figure 4: Conceptual Diagram.................................................................................................10
Figure 5: Logical Diagram.......................................................................................................13
Figure 6: UML Diagram..........................................................................................................13
Figure 7 myadmin ERD...........................................................................................................14
Figure 8 Table structure...........................................................................................................14
Figure 9 Table Employee.........................................................................................................14
Figure 10 Employee Data.........................................................................................................14
Figure 11 Table Pilot................................................................................................................15
Figure 12 Pilot Data.................................................................................................................15
Figure 13 Table Payment.........................................................................................................15
Figure 14 Payment Data...........................................................................................................15
Figure 15 OtherStaff Table......................................................................................................15
Figure 16 OtherStaff Data........................................................................................................15
Figure 17 Passenger Table.......................................................................................................15
Figure 18 Passenger Data.........................................................................................................16
Figure 19 AirLine Table...........................................................................................................16
Figure 20 AirLine Data............................................................................................................16
Figure 21 Planes Table.............................................................................................................16
Figure 22 Planes Data..............................................................................................................16
Figure 23 Alter Table Commands............................................................................................17

Introduction
The below made report is based on the scenario of online flight booking and covers the entire
scenario of booking, cancelling and getting information of the Airline. The Jet Airways
Airline’s database is being created below along with the theoretical part that is being used for
the creation of the database. Draw.io is used in the below made report for the creation of the
conceptual, logical and UML Diagram. PhpMyAdmin is the main software that is used for
the practical implementation.

Task No. 1 Understanding of database
1. RDBMS V/S FILE-BASED SYSTEM
The first system to be discussed is the File-based system and it is a main method that is being
used in order to store as well as for keeping the files onto the hard-disk. Reading as well as
writing of the data could be done here. This system is already coded inside the OS i.e.
Operating System (Kotlyar et al., 2015). This means that on installing the Operating System
in the system, the installation automatically gets done. Relational Database Management
System is the next system that helps in storing the data based onto the relational model.
Dissimilarities
 The first dissimilarity is the consistency. Talking about the File based system, it is
considered as the very less consistent when compared with the Relational Database
Management System. Consistency is the one of the main feature of the Relational
Database Management System.
 The next important feature is the Data Security. Here, discussing about the Relational
Database Management System, it is way far secure than the File-based system. Security is
the main root of the Relational Database Management System. While comparing the File-
based System, it is not at all secure to keep the data there.
 Moving on the next major dissimilarity is the Centralization. Looking forward for the
acquiring of the centralisation, it is considered as really difficult for acquiring it in the
File-based System whereas it is much easier to get achieved in the Relational Database
Management System.
 Redundancy of the data is also one of the major factors for finding out the dissimilarity
among both the systems. If talking about the File-based System, the redundancy of data
could easily be encountered whereas If talking about the Relational Database
Management System the redundancy of the data could not be found.
Relational Database
Management System.
File-Based System
Advantages 1. Secure and Reliable
2. Easy to be
maintained.
3. Data retrieving is
quite easy.
1. Backing up is quite
easy.
2. Data sharing could
easily be done.
3. Data retrieving is
quite easy.
Dis-Advantages 1. High Maintenance
cost.
2. Proper Limitations
1. Redundancy of data
is found.
2. Highly inconsistent.

are set.
3. High Complexity.
3. No Data Security.
Limitations 1. Field Size is limited.
2. Complexity is quite
high.
3. Expensive.
1. Very less data security.
2. Consistency is quite
low.
2. Various Data Models
Hierarchical Database Model
The first data model to be discussed is the Hierarchical Database model. Inverted tree is the
other name given to this model. The top most node of this data model is known as the Root.
Top to down is the structure that is being followed here. The technique that is being used here
for the arrangement of the data is in the form of Organization Chart.
Figure 1: Hierarchical Database Model
Advantages
 The first major advantage is the retrieving of the data is really very easier.
 Data is automatically being defined here in this model. This is the next advantage.
 Adding of the data or removing of the data are the operations that are easily implemented
in the Hierarchical model (Zhang et al., 2016).
Dis-Advantages
 Single change at the single place in the data would definitely affect the entire database.
 Talking about the communication part, it is quite difficult in the Hierarchical Database
Model.

Network Database Model
Using Hierarchical Database Model gives some of the weaknesses, Network Database Model
is introduced for overcoming those weaknesses. The relationship among the objects as well as
the relations is done here in quite a flexible way. Lattice structure could easily be found here.
Figure 2: Network Database Model (Source: Owlgen, 2019))
Advantages
 Accessing the data with the help of this model becomes too easier.
 Understanding is quite easy here in this model.
 Integration of the data is the other important feature of the Network Database Model.
Dis-Advantages
 High complexity is the first disadvantage of the Network Database Model.
 The next disadvantage is the Structural dependence.
Relational database Model
Similar to the previously discussed Network Database Model, Relational is developed here
for overcoming the limitations of both network as well as the hierarchical database model.
Drawbacks such as the complexity as well as the inflexibility are being removed here. The
relational database model is the 2-dimensional structure.

Figure 3: Relational Database Model (Source: Diranieh.com, 2019)
Advantages
 Data retrieving is easier.
 Easily understandable and less complex.
 Scalability is the next advantage.
Dis-Advantages
 Implementation cost is quite high.
 Less number of operations are found here.
3. Approaches
Top-Down Approach
Design by Analysis is the other name used for the Top-down Approach. Breaking the entities
is being done here for fulfilling the requirements Nenova, (Z.D. and May, J.H., 2016).
Analysing of the design is being done here.
Strengths
 This is considered as one of the most important approach as this is used in order to help in
saving the time for the implementation of the project and quality is not at all being
compromised here.
 Reliability as well as the fast decision making are the other advantages or the strengths.
Weaknesses
 Good knowledge as well as the skills are required here.
 The next issue in this approach is the limited number of participation.
Bottom-Up Approach

The next approach is the Bottom-up Approach and Design by Synthesis is the other name
given to this approach. Collection of the information is being done here at first, and later on
the entire process gets completed.
Strengths
 Using this approach, the solutions are easily provided.
 Easy to get implemented and the implementation process is really very easy.
Weaknesses
 The cost of implementing this approach could get increased in some moment of time.
 Low effectiveness as well as the efficiency is addressed here in this approach.
This assignment is made and done using the Top-down approach as it is way more effective
and efficient.

Task No. 2 Conceptual Modelling
1. Employee
ATTRIBUTES DATATYPE
E_No. Int
E_Name Varchar
E_Salary Int
E_Address Varchar
F_No. Int
Branch_No. Int
2. Airline
ATTRIBUTES DATATYPE
Branch_ID Int
Origin Varchar
TotalNumberOfFlights Int
TotalBookings Int
TotalCancellations Int
3. Passenger
ATTRIBUTES DATATYPE
P_ID Int
Branch_ID Varchar
Payment_ID Varchar
P_Name Int
P_Contact Date
P_Email Varchar
4. Pilots
ATTRIBUTES DATATYPE
E_No. Int
TypeOfPlane Varchar

5. Planes
ATTRIBUTES DATATYPE
F_No. Int
Plane_Manufacturer Varchar
ModelName Varchar
F_ArrivalTime Date
F_DepartureTime Date
Destination Varchar
6. Payment
ATTRIBUTES DATATYPE
Payment_ID Int
ModeOfPayment Int
StatusOfPayment Int
7. Other Staff
ATTRIBUTES DATATYPE
E_No. Int
Role Varchar

Conceptual Diagram
Figure 4: Conceptual Diagram

Task No. 3 Logical Modelling
Data Normalisation
UNF 1NF 2NF 3NF ENTITIES
Employee Table E_No.(PK) E_No.(PK) E_No.(PK) Employee
Airline Table E_Name E_Name E_Name
Passenger Table E_Salary E_Salary E_Salary
Pilots Table E_Address E_Address E_Address
Planes Table F_No. F_No. F_No.(FK)
Payment Table Branch_No. Branch_No. Branch_No.(FK)
Other Staff Table Role
TypeOfPlane E_No.(PK) Other Staff
E_No.(PK) Role
Role
E_No.(PK) Pilots
TypeOfPlane
E_No.(PK)
TypeOfPlane
Branch_ID(PK) Airline
Origin
TotalNumberOfFl
ights
TotalBookings
TotalCancellation
s
Branch_ID(PK) Branch_ID(PK)
Origin Origin
TotalNumberOfF TotalNumberOfFli

lights ghts
F_ArrivalTime TotalBookings
F_DepartureTim
e
TotalCancellations F_No (PK) Planes
Destination F_No (PK) Plane_Manufactu
rer
TotalBookings Plane_Manufactur
er
ModelName
TotalCancellatio
ns
ModelName F_ArrivalTime
Plane_Manufactu
rer
F_ArrivalTime F_DepartureTime
ModelName F_DepartureTime Destination
Destination
P_ID(PK)
Branch_ID P_ID(PK)
Payment_ID Branch_ID P_ID(PK) Passenger
P_Name Payment_ID Branch_ID(FK)
P_Contact P_Name Payment_ID(FK)
P_Email P_Contact P_Name
ModeOfPayment P_Contact
StatusOfPayment Payment_ID
ModeOfPayment
StatusOfPayment Payment_ID Payment
ModeOfPayment
StatusOfPayment