Database Management System Project: Jet Airlines Database Design

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

Contents
Introduction......................................................................................................................................3
Task 1...............................................................................................................................................4
(A)................................................................................................................................................4
(B)................................................................................................................................................5
(C)................................................................................................................................................7
Task 2...............................................................................................................................................8
(A)................................................................................................................................................8
(B)..............................................................................................................................................11
Task 3.............................................................................................................................................12
(A)..............................................................................................................................................12
(B)..............................................................................................................................................15
(C)..............................................................................................................................................16
Task 4.............................................................................................................................................18
(A)..............................................................................................................................................18
(B)..............................................................................................................................................20
Conclusion.....................................................................................................................................23
References......................................................................................................................................24
List of Figures
Figure 1: Conceptual diagram.......................................................................................................11
Figure 2: Entity Relationship.........................................................................................................15
Figure 3: UML diagram.................................................................................................................16
Figure 4: PhpMyAdmin design.....................................................................................................17
Figure 5: Table Staff......................................................................................................................18
Figure 6: Table PaymentDetails....................................................................................................18
Figure 7: GroundEmployee...........................................................................................................18
Figure 8: Table Flights...................................................................................................................19
Figure 9: Table Customers.............................................................................................................19
Figure 10: Table AirStaff...............................................................................................................19
Figure 11: AirlineCompany...........................................................................................................19

Figure 12: Foreign Keys................................................................................................................20
Figure 13: Staff Data.....................................................................................................................20
Figure 14: PaymentDetails Data....................................................................................................20
Figure 15: GroundEmployee Data.................................................................................................21
Figure 16: Flights Data..................................................................................................................21
Figure 17: Customers Data............................................................................................................21
Figure 18: AirStaff Data................................................................................................................22
Figure 19: AirlineCompany Data..................................................................................................22

Introduction
This assignment is based on the database management that is carried out by the help of
phpMyAdmin and the case scenario that is provided here is about an international company of
Airline named Jet Airlines. The use of entity relationship diagram and the conceptual diagram is
given in order to explain the overall scenario as well as the entities and relationship among the
entities that are present for the given scenario. The whole assessment has many tasks that need to
be completed for the overall completion of the assessment and the task include various different
discussions on diverse topics and they are given in the file.
In the database that needs to create here is developed as on the position of the junior developer in
the company that is named as the Jet Airline which is an international company for the airline.
Some discussions are made regarding the database and all the tasks are included in this report.

Task 1
(A)
Comparison of database systems and file-based systems
Database Systems File-Based System
 Provide proper security of the data.  It does not provide proper data
security.
 In this system, the user is not aware of
the physical address where the data is
stored.
 User is aware of the physical address
and user locates the physical address
of the files to access data in the File
Management System.
 This system stores the structured data
that contain a constraint which is well
defined and have interrelations.
 This system stores the unstructured
data in the form of isolated files as
well as the entities.
 Centralization can be achieved easily
here in this system.
 It is hard to achieve centralization in
this system (Yang et al., 2016).
File based Systems Database Management Systems
Advantages  Retrieving the data is easy in
this system.
 Sharing data with other systems
is easy here.
 Restoration of the data is easily
performed.
 Provide high security to
that data which is stored.
 Retrieving as well as
storing procedure is easy.
 Maintenance is easy
(Gogolla et al., 2018).
Disadvantages  Data present is inconsistent.
 Not strong security of data.
 Redundancy can be seen.
 Data present is complex
and needs high skills to
understand.

 Maintenance is easy but
costly.
Limitations  Data that is present is not
secured.
 A major issue of redundant
data.
 Data is not consistent.
 The limited size of the
fields that are present in
the database.
 Complex database.
 Expensive than any other
database.
(B)
Definition Characterist
ics
Example image
Hierarchi
cal
Database
Model
It is also
referred to
as the
inverted
tree in
which the
node which
is present at
the topmost
part is
known as
the root and
the
branches
emerged
from this
branch.
1. Data
retrieval is
easy here.
2. The data
insertion is
an easy task
here.
3. Data
deletion is
also simpler
(Iqbal and
Bajwa,
2016).
Network This is used 1. Easier and

Database
Model
for
improvisati
on of the
hierarchical
model and
this helps
in the
relationship
representati
on that is
present
between
the objects.
The
relations
among the
items are
direct
(Yang and
Cao, 2016).
simpler to
understand.
2.
Integration of
data can be
done in this
model.
3. Accessing
the data is
easy (Shao et
al., 2015).
Relational
database
Model
This model
is more
optimized
and
efficient as
the
problem
like
complexity
which is
1. Easiest of
all models.
2. Least
complex.
3. Retrieval
of
information
and data is
easy/

higher can
be
eliminated
using a
relational
database
model.
Also, the
problems
regarding
the
inflexibility
are
removed.
(C)
Top-Down Approach
This is the first and the most important approach that is also known as the ‘Design by analyses’.
Here, Breaking down of the design is shown in order to show the completion of the design and
meeting its requirements.
Strengths:
 Easy and quick decisions are taken.
 Reliability is really high.
 Accessing is really fast whereas implementation is also done in very minimum time.
The quality of the project is also assured(Deelman et al., 2015).
Weakness:

 A lot of skills along with the knowledge is required here.
 Participation is quite high.
Bottom-Up Approach
This is the first and the most important approach that is also known as the ‘Design by syntheses’.
This is used after the collection of the data is done along with all the requirements being fulfilled.
Strengths:
 No expert, as well as skills, are required.
 Accurate and proper results are obtained.
Weakness:
 Without the knowledge of the experts and fewer skills needed, the quality is
compromised.
 The budget for this approach is quite high.
Because of the efficiency as well as the effectiveness, the top-down approach is used here in
this development design.
Task 2
(A)
AirlineCompany
Attributes Data type
AirlineBranchId integer
ToatlBookings integer
ToatalCancellations integer
TotalFlights integer

AirStaff
Attributes Data type
Staff_ID integer
FlightType varchar
Customers
Attributes Data type
C_ID integer
P_Id integer
AirlinebranchID integer
C_Name varchar
C_Contact integer
C_Email varchar
Flights
Attributes Data type
Flight_Id int
Flight_Arrivaltime int
Flight_Departuretime int
Flight_Destination varchar
Flight_Model varchar

GroundEmployee
Attributes Data type
Staff_ID integer
Staff_Designation varchar
Staff_Income integer
PaymentDetails
Attributes Data type
P_Id integer
ModeofPayment varchar
StatusofPayment varchar
Staff
Attributes Data type
Staff_ID integer
Staff_Name varchar
AirlineBranchID integer
Flight_Id integer
Staff_Address varchar
Staff_Email varchar
Staff_Salary integer

(B)
Conceptual Diagram
Figure 1: Conceptual diagram

Task 3
(A)
Normalization Table
UNF 1NF 2NF 3NF Entities
Staff Staff_ID(PK) Staff_ID(PK) Staff_ID(PK) Staff
Customers Staff_Name Staff_Name Staff_Name
PaymentDetails AirlineBranchID AirlineBranchID AirlineBranchI
D(FK)
Flights Flight_Id Flight_Id Flight_Id(FK)
AirStaff Staff_Address Staff_Address Staff_Address
AirlineCompany Staff_Email Staff_Email Staff_Email
GroundEmployees Staff_Salary Staff_Salary Staff_Salary
Staff_ID(PK) Staff_ID(PK) AirStaff
FlightType FlightType
Staff_ID(PK) Staff_ID(PK) GroundE
mployee
Staff_Designation Staff_Designati
on
Staff_Income Staff_Income
AirlineBranchId(P AirlineBranchI AirlineCo