Database System for Jet Airlines

This document provides an understanding of database systems and their advantages and disadvantages. It also covers conceptual, logical, and physical modeling for Jet Airlines. The document includes entity relationship diagrams and relational data models.

Running head: DATABASE SYSTEM FOR JET AIRLINES
Database System for Jet Airlines
Name of the Student
Name of the University
Author’s note:

1DATABASE SYSTEM FOR JET AIRLINES
Table of Contents
Task 1: Understanding of Database.................................................................................................2
1.1Database System VS File Based System:...............................................................................2
1.1.1 Database System:............................................................................................................2
1.1.2 File Based System:.........................................................................................................2
1.2 Hierarchical, Network and Relational Models:.....................................................................3
1.3 Top-down and Bottom-up Approaches:................................................................................3
Task 2: Conceptual Modelling.........................................................................................................4
Task 3: Logical Modelling...............................................................................................................6
Task 4: Physical Modelling...........................................................................................................13
Create Database:........................................................................................................................13
Create Table:..............................................................................................................................13
Bibliography:.................................................................................................................................19

2DATABASE SYSTEM FOR JET AIRLINES
Task 1: Understanding of Database
1.1Database System VS File Based System:
1.1.1 Database System:
The database system is considered as the set of database management system and
databases. The database system is for storing, fetching, updating and deleting data. Database
systems are consisted of database software that allows the user to perform all the intended tasks.
Database system handles both the operations and the data.
Advantages: The database system is effective enough to control the data redundancy
through its entities. There is minimum inconsistency in the database systems. If any change is
made in the database, all the interrelated data will be changed. The quality of service is also
better in the database systems.
Disadvantages: The complexity of the database is a huge disadvantage. The execution of
functions are complex than file based systems. The cost of the software and hardware makes it
difficult to implement.
Limitations: Without having prior knowledge of database systems, the user cannot store
or retrieve data from a database system.
1.1.2 File Based System:
The may be thought as a collection of computer programs which is capable of carrying
out the programming services regarding the intentions of end user for accessing information.
Each program has its own file which is maintained by that program itself.
Advantages: The traditional file based systems are easy to access. The files remains in
the central location physically, therefore establishing the connection is very easy. The file
systems are very easy to understand.
Disadvantages: The file systems are outdated and most modern framework and
technologies do not support it. The file systems are very prone to cyber-attacks. As there is no
proper security checks in the file based system other than server, the cyber-attacks can be done
very easily.

3DATABASE SYSTEM FOR JET AIRLINES
Limitations: Current frameworks does not support the file based systems.
1.2 Hierarchical, Network and Relational Models:
Hierarchical Model: In terms of the hierarchical data model, a structure similar to the
tree is implemented. The parent’s node has many children and these children can act like nodes.
Only the one-to-many relations are available within hierarchical data model.
Network Model: Network data model is developed using objects and relationships among
the objects. The network data model is a very scalable approach toward database
implementation. The schema in the network data model is represented through a data
visualization where the acrs are relationship and objects are nodes.
Relational Model: The set of relations within a database is presented through the
relational model. The relations are the tables that holds the raw data. Each relation is connected
to other relation or relations through relationships. Relations has constraint like primary key,
foreign key, default, check and many more. Most modern day medium sized database are
relations. These databases focuses on the interrelationship among the relations.
1.3 Top-down and Bottom-up Approaches:
Top-Down: Top-Down is popular for generating new databases. The database
development starts at logical level where the logical database is created. The information about
the data is collected and structured. All the information collected at the logical and conceptual
level is applied to the physical database implementation level.
Advantages of Top-Down
i. High-level view for all items
ii. Data change can be monitored efficiently
Drawbacks of Top- Down:
i. Takes a lot of time to create the database
ii. Each process should be done only in sequential manner

4DATABASE SYSTEM FOR JET AIRLINES
Bottom-Up: The bottom-up approach is most popular for database migration. In the
bottom-up approach one physical database is migrated to another database environment. The
migration can happen between different database management systems.
Advantages of Bottom- Up:
i. Developing new database from existing one effective and efficient
ii. Can be done several time easily
Drawbacks of Bottom-Up:
i. The developers do not get high level view of the data
Task 2: Conceptual Modelling
Entity Identification: The database entities of Jet Airline are as following.
i. Aircraft
ii. Bookings
iii. Cancelled
iv. EmployeeJob
v. Employees
vi. Flights
vii. Passengers
viii. Payments
Database ERD:

5DATABASE SYSTEM FOR JET AIRLINES
Figure 4: Conceptual Data Model
(Source: Created by Author)

6DATABASE SYSTEM FOR JET AIRLINES
Task 3: Logical Modelling
Normalize Jet Airline Database up to 3rd Normal Form: The database normalization
process is referred to as the method of data management in a database. The normalization
process is used for decomposing the database entities or tables. The decomposition allows the
database to reduce data redundancy. It also helps the database to prevent malfunction or data
corruption during data insertion, update or delete.
First normal form: After collecting all the data, the developers put all the data in same
table. This table has multivalued attributes and inconsistent data. For converting the database
table into first normal form, all the multivalued attributes are to be converted into atomic valued
attributes. All the values stored in the database should be of same data type such as if decimal is
used for cost then all the values in the cost column must be of decimal data type.
passenger
ID
passeng
erName
booki
ngID
bookin
gDate
flightID origi
n
cancella
tionDat
e
aircraft
ID
modelNa
me
1001 Luke
Leitch
101 August
14,
2019
1001 New
jerse
y
August
15, 2019
101 Airbus
A380-80
0
102 August
15,
2016
1002 Perth NULL 102 Boeing
747-8
103 August
16,
2013
1003 Cair
ns
NULL 103 Boeing
747-400
As the above table has multivalued attribute for the booking related information for each
customer, every multivalued columns are converted into atomic valued column. The following
table is created to show that the all the multivalued attributes are converted into atomic values.
passenger
ID
passeng
erName
booki
ngID
bookin
gDate
flightID origi
n
cancella
tionDat
aircraft
ID
modelNa
me

7DATABASE SYSTEM FOR JET AIRLINES
e
1001 Luke
Leitch
101 August
14,
2019
1001 New
jerse
y
August
15, 2018
101 Airbus
A380-80
0
1001 Luke
Leitch
102 August
15,
2016
1002 Perth NULL 102 Boeing
747-8
1001 Luke
Leitch
103 August
16,
2013
1003 Cair
ns
NULL 103 Boeing
747-400
Second Normal Form: Second normal deals with the partial dependencies within the
database. The partial dependency exists in a table when a non-key columns are dependent on
another non-key attribute. Taken as an example, booking date and cancel date depends on the
booking id. Same way the origin depends on the flight number. Therefore, two new tables are
created named as booking and flight to exterminate the partial dependency.
passengerID passengerName
1001 Luke Leitch
However, to connect the passenger table with the booking table, referential integrity
constraint is used. The referential constraint allows the database to utilize the key attribute of one
relation as a foreign-key attribute in another table. For connecting the passenger table with the
booking table, the passenger table primary key is used as foreign key in booking table. As the
flight table will also be created, flight number will also be utilized as foreign-key attribute within
the booking relation.
bookingID passengerID bookingDate flightID cancellationDate
101 1001 August 14, 2019 1001 August 15, 2018
102 1001 August 15, 2016 1002 NULL
103 1001 August 16, 2013 1003 NULL

8DATABASE SYSTEM FOR JET AIRLINES
The flight table will be as following.
flightID origin aircraftID modelName
1001 New jersey 101 Airbus A380-800
1002 Perth 102 Boeing 747-8
1003 Cairns 103 Boeing 747-400
Third Normal From: The third normal form is responsible for handling transitive
dependency. The aircraft id, flight number and model name has transitive dependency among
those. Booking id, flight number and origin has transitive dependency. The final tables look like
as following.
flightID origin aircraftID
1001 New jersey 101
1002 Perth 102
1003 Cairns 103
aircraftID modelName
101 Airbus A380-800
102 Boeing 747-8
103 Boeing 747-400
bookingID passengerID bookingDate flightID cancellationDate
101 1001 August 14, 2019 1001 August 15, 2018
102 1001 August 15, 2016 1002 NULL
103 1001 August 16, 2013 1003 NULL
passengerID passengerName
1001 Luke Leitch

9DATABASE SYSTEM FOR JET AIRLINES

10DATABASE SYSTEM FOR JET AIRLINES
Entity Relationship Diagram:
Figure 5: Normalized Entity Relationship Diagram
(Source: Created by Author)
Relational Data Model:
Passenger
passengerID passengerName addresss phoneNumber
1001 Luke Leitch 43 Settlement Road
COONGULLA VIC
3860
[phone]
1002 Max Moriarty 63 Mandible Street [phone]

11DATABASE SYSTEM FOR JET AIRLINES
GUNPOWDER QLD
4825
1003 Hugo Wilkin 10 Main Street
BOOLGUN SA 5330
[phone]
1004 Ashton Ferguson 60 Boughtman Street
COATESVILLE VIC
3165
[phone]
1005 Jonathan Quong 98 Barnett Street
TULLAH TAS 7321
[phone]
Employees
empID empName address salary
1001 Zac Forrest 3 Weemala Avenue
COMOBELLA NSW
2820
2150.00
1002 Aaron Dwyer 2 Adavale Road
GOULBURN NSW
2580
1780.00
1003 James Macandie 59 Walters Street
KOONDA VIC 3669
3500.00
1004 Sam Ida 34 Cofton Close
CLOUDS CREEK
NSW 2453
2560.00
1005 Joel Wormald 74 Sunnyside Road
BOOKPURNONG
SA 5333
2700.00
Aircraft
aircraftID modelName manufacturer
1001 Airbus A380-800 Airbus Corporate Jets

12DATABASE SYSTEM FOR JET AIRLINES
1002 Boeing 747-8 Boeing
1003 Boeing 747-400 Boeing
Flights
flightID aircraf
t
origin destinatio
n
flightDate departureTim
e
arrivalTim
e
1001 1001 New
jersey
Perth 12/01/2019 12:30:00 18:30:00
1002 1002 Perth Cairns 18/08/2018 12:45:00 21:15:00
1003 1003 Cairns Perth 27/02/2019 18:00:00 23:45:00
EmployeeJob
flight employee employeeJob
1001 1001 Pilot
1002 1002 Air Hostess
1003 1003 Pilot
1002 1004 Air Hostess
1001 1005 Pilot
Bookings
bookingI
D
fligh
t
passengerI
D
bookingChar
ge
bookingDa
te
bookingTi
me
passengerQu
an
1001 1001 1001 150.00 12/04/2019 11:58:00 1
1002 1002 1002 150.00 18/04/2019 17:59:00 3
1003 1003 1003 150.00 27/04/2019 08:27:00 1

13DATABASE SYSTEM FOR JET AIRLINES
1004 1004 1004 150.00 12/04/2019 11:58:00 2
1005 1005 1005 150.00 18/04/2019 17:59:00 1
Payments
paymentID booking paymentDat
e
paymentTime tax totalPaymen
t
1001 1001 12/04/2019 12:00:00 18% 177.00
1002 1002 18/04/2019 18:00:00 18% 177.00
1003 1003 27/04/2019 08:30:00 18% 177.00
1004 1004 12/04/2019 12:00:00 18% 177.00
1005 1005 18/04/2019 18:00:00 18% 177.00
Cancellation
booking_id cancellationDat
e
cancellationTim
e
fine reason Status
1002 12/04/2019 12:58:00 50.00 Other Refunded
1003 18/04/2019 18:59:00 50.00 Other Refunded
Task 4: Physical Modelling
Create Database:
CREATE DATABASE IF NOT EXISTS JetAirline DEFAULT CHARACTER SET
latin1 COLLATE latin1_swedish_ci;
USE JetAirline;
Create Table:
CREATE TABLE aircraft (
aircraftID int(11) NOT NULL PRIMARY KEY,

14DATABASE SYSTEM FOR JET AIRLINES
modelName varchar(150) NOT NULL,
manufacturer varchar(150) NOT NULL
);
CREATE TABLE employees (
empID int(11) NOT NULL PRIMARY KEY,
empName varchar(150) NOT NULL,
address varchar(150) NOT NULL,
salary decimal(12,2) NOT NULL
);
CREATE TABLE flights (
flightID int(11) NOT NULL PRIMARY KEY,
aircraftID int(11) NOT NULL,
origin varchar(30) NOT NULL,
destination varchar(30) NOT NULL,
flightDate DATE NOT NULL,
departureTime time NOT NULL,

15DATABASE SYSTEM FOR JET AIRLINES
arrivalTime time NOT NULL,
FOREIGN KEY (aircraftID) REFERENCES Aircraft(aircraftID)
);
CREATE TABLE EmployeeJob (
flight int(11) NOT NULL,
employee int(11) NOT NULL,
employeeJob varchar(20) NOT NULL,
CONSTRAINT PK_EmployeeJob PRIMARY KEY (flight,employee),
FOREIGN KEY (flight) REFERENCES Flights(flightID),
FOREIGN KEY (employee) REFERENCES Employees(empID)
);
CREATE TABLE passengers (
passengerID int(11) NOT NULL PRIMARY KEY,
passengerName varchar(150) NOT NULL,

16DATABASE SYSTEM FOR JET AIRLINES
addresss varchar(150) NOT NULL,
phoneNumber varchar(15) NOT NULL
);
CREATE TABLE bookings (
bookingID int(11) NOT NULL PRIMARY KEY,
flight int(11) NOT NULL,
passengerID int(11) NOT NULL,
bookingCharge decimal(12,2) NOT NULL,
bookingDate DATE NOT NULL,
bookingTime time NOT NULL,
passengerQuan int(11) NOT NULL,
FOREIGN KEY (flight) REFERENCES Flights(flightID),
FOREIGN KEY (passengerID) REFERENCES Passengers(passengerID)
);

17DATABASE SYSTEM FOR JET AIRLINES
CREATE TABLE Cancellation (
bookingID int(11) NOT NULL PRIMARY KEY,
cancellationDate DATE NOT NULL,
cancellationTime time NOT NULL,
fine decimal(12,2) NOT NULL,
reason varchar(150) NOT NULL,
status varchar(150) NOT NULL,
FOREIGN KEY (bookingId) REFERENCES bookings(bookingID)
);
CREATE TABLE payments (
paymentID int(11) NOT NULL PRIMARY KEY,
booking int(11) NOT NULL,
paymentDate DATE NOT NULL,
paymentTime time NOT NULL,
tax int(11) NOT NULL,
totalPayment decimal(12,2) NOT NULL,
FOREIGN KEY (booking) REFERENCES Bookings(bookingID)

18DATABASE SYSTEM FOR JET AIRLINES
);