Data Modelling & SQL Language Report - Database Design for Jet Airline

This report details the design and implementation of a relational database for Jet Airline, covering various aspects of data modeling and SQL. It begins with an understanding of database systems, comparing file-based systems with database management systems, and explaining the characteristics of hierarchical, network, and relational models. The report then proceeds to conceptual, logical, and physical modeling stages. The conceptual model includes entity identification and an entity-relationship diagram (ERD). Logical modeling focuses on normalization, explaining the different normal forms and their application to the airline data. The report provides relational data models for various entities like passengers, flights, and employees. Finally, the physical modeling section includes SQL code for creating and populating the database tables, ensuring the database is fully functional to meet the airline's requirements.

Running head: DATA MODELLING & SQL LANGUAGE
Data Modelling & SQL Language
Name of the Student
Name of the University
Author’s note:

1DATA MODELLING & SQL LANGUAGE
Table of Contents
Task 1: Understanding of Database.................................................................................................2
Comparison between File based System and Database System..................................................2
Characteristics of Hierarchical, Network and Relational Models:..............................................3
Hierarchical Model..................................................................................................................3
Network Model........................................................................................................................4
Relational Model.....................................................................................................................5
Top-down and Bottom-up Approaches:......................................................................................6
Task 2: Conceptual Modelling.........................................................................................................7
Task 3: Logical Modelling...............................................................................................................8
Task 4: Physical Modelling...........................................................................................................14
Create Database:........................................................................................................................14
Populate Database:.....................................................................................................................19
Bibliography:.................................................................................................................................21

2DATA MODELLING & SQL LANGUAGE
Task 1: Understanding of Database
Comparison between File based System and Database System
File based System Database Management System
It is a general, easy usage system that store all
the required files that requires less security
and constraints (Poddar, Boelter and Popa
2016).
Database Management System is mainly used
for used when the security parameters are
high.
In file based system, there are more chances
of data redundancy
In data management system, data redundancy
is found to be minimum.
Inconsistent of data is found to be minimum
to more
Data inconsistency is found to be less.
Centralization is very much tough to get. Centralization is easy to achieve.
To get an access of the physical address, user
need to get access to the required data.
User does not have any kind of idea with
respect to physical address.
Security is considered to be minimum Security is considered to be maximum.
File based system aims to store unstructured
data.
DBMS aims to store structure data.
Advantages of File based System
Saving the files and downloading them in file system is much simpler: User just need to
click as ‘Save As’ which will help them out (Scotti et al., 2016). Downloading can be easily done
by making address of the URL of save file location.
Migration of data is very much easy process: User just need to copy-paste the given
folder to desired location along with writing the permission provided in the destination.
Disadvantages of Files Based System
Loosely Packed: There are no kind of operation with respect to ACID (Atomicity,
Consistency, Isolation and Durability) with respect to mapping that highlights no kind of
guarantee (Gahi, Guennoun & El-Khatib, 2015). Scenario can easily arise when the given file is
deleted from their location in manual way.

3DATA MODELLING & SQL LANGUAGE
Low Security: User need to write permission as the given files are stored in the folder. It
is completely prone so safety issues and troubles like hacking.
File based System has certain number of limitation
i. Separation and Isolation of give Data
ii. Data Duplication.
iii. Dependency of data
Advantages of Database System
i. ACID consistency that is mainly inclusive of rollback and update of files which
stored outside the given database.
ii. Given files will be synchronized within the database and cannot be easily
orphaned that gives them tracking of their transaction.
Disadvantages of Database System
i. Memory is considered to be ineffective and all the required data goes to the RAM
(Luo et al., 2018). In RDBMS, a proper track of each of data page is maintained.
ii. Backup of data is found to be more heavy and hefty.
Limitation of Database System
More Costly: Creation and Managing of Database is considered to be bit costly in nature.
High cost software and hardware are needed in the database.
High Complexity: DBMS is considered to be bit complex which requires creating,
editing and modifying the database.
Characteristics of Various Models:
Hierarchical Model
It mainly organizes the given data into a tree- structure so that each of the record is
completely send to the single Parent or root. After that, sibling records are mainly sorted in a
proper order (Rao, 2017). This particular order is mainly used for storage of the given database.
The given model is needed for highlighting various relationship with real world. In this model,
there is a child note which come up with a single parent note. The overall efficiency of the model
is completely described in some of the real-world relationship like recipes (Kraska et al., 2019).

4DATA MODELLING & SQL LANGUAGE
The given data is organized in a tree-like structure that comes up one-to- many relation between
various type of data. The below given diagram describes the hierarchical model:
Fig 1: Hierarchical Model
(Source: Wang, Zhou & Chen, 2017)
Network Model
This particular model is completely based on hierarchical model which allow many- to-
many relationship that exist in between linked control. It merely implies on multiple number of
parent record (Sarwat et al., 2017). This particular model is based on collection of records. Each
of them comprises of single owner or even parent record where one of member is child record.
The record comprises of member or child in given set that allow the model to provide some of
the complex relationship (Poddar, Boelter and Popa 2016). This particular model was very much
popular in the era of 70s. It is mainly defined as conference of data system languages.

5DATA MODELLING & SQL LANGUAGE
Fig 2: Example of Network Model
(Source: Scotti et al., 2016)
Relational Model
It is considered to be most common kind of model which is needed for sorting data into
the required table which is known to be relation. Each of the given term comprises of columns
and rows (Gahi, Guennoun & El-Khatib, 2015). Each of the given column comprises of
attributes and entity in given question like zip code and birth date. All the given domains in a
together way is defined as a domain. In this, Attribute or even combination of attribute is
considered to be the primary key. It can be easily referred into the other table which is defined as
a foreign key. Each of the row is defined as tuple which is all about specific details of the entity
like employee in particular (Luo et al., 2018). The given module accounts for various kind of
relation that exists in between the given tables. There are various kind of relations like one to
one, one to many and many to many.

6DATA MODELLING & SQL LANGUAGE
Fig 3: Example of relational Model
(Source: Rao, 2017)
Top-down and Bottom-up Approaches:
Top-Down: It is mainly highlighted in research and planning stage which leads to the
database development (Kraska et al., 2019). It is merely used for creating database that comes up
with a view of high level for the given database that has known requirements.
Benefits of Top-Down
i. High level over-view of all the components
ii. Complete visibility that can affect the kind of changes on database and relation.
iii. Highlights the requirements without any kind of implementation.
Drawbacks of Top- Down:
i. It is a timely way in comparison to bottom-up approach.
ii. Proper communication is needed by both designers and end-user of the given
database.

7DATA MODELLING & SQL LANGUAGE
Bottom-Up: This particular approach mainly begins with the point of interest and rises up
to the next (Sarwat et al., 2017). It merely comes up later with down the given line. In this,
designer will merely access the interface of the database followed by analysing tables and
relationship views (Wang, Zhou & Chen, 2017). Designer need to work in opposite direction so
that they can figure out which information can be store in the database.
Advantages of Bottom- Up:
i. Can easily get something for working and its overall operation.
ii. There are many modules which required to be designed in the beginning phase.
Drawbacks of Bottom-Up:
i. Does not have a high level view that can bring proper potential affect.
ii. There are more chances of time spent as the given requirement needs to be fleshed
more.
Top down approach will be used for the design of database of the airplane.
Task 2: Conceptual Modelling
Entity Identification: The list of entities are as following.
i. Passengers
ii. Flights
iii. Employees
iv. Aircraft
v. Payments
vi. Bookings
Database ERD:

8DATA MODELLING & SQL LANGUAGE
Figure 4: Entity Relationship Diagram
(Source: Created by Author)
Task 3: Logical Modelling
Normalization: In order to organize data with the database, the normalization process is
followed. The purpose of normalization is eliminating the anomalies in the database. The
normalization process is followed by create
on of database and relationships in the database. Normalization has five normal forms. In in real
world databases, a database is generally normalized up to third normal form.

9DATA MODELLING & SQL LANGUAGE
The first normal form describes that the attributes of the tables has to be atomic. This
means, no multi valued attributes are allowed within the database tables. The following table
represent the passenger and flight table.
passenger
ID
name address phoneNum
ber
booking
ID
bookingD
ate
flightI
D
origin destinati
on
1 Linco
ln
Holtho
use
Road
8280 9723 1 1/ 2/2020 1 Austra
lia
UK
2 1/ 2/2020 1 Austra
lia
UK
3 1/ 9/2020 2 UK Australi
a
As per the above table, it can be said that bookingID, bookingDate, flightID, origin and
destination. The following table represents the first normal form as for each row of booking and
flight, the passenger details are recorded. All the attributes are atomic in the following table.
passenger
ID
name address phoneNum
ber
booking
ID
bookingD
ate
flightI
D
origin destinati
on
1 Linco
ln
Holtho
use
Road
8280 9723 1 1/ 2/2020 1 Austra
lia
UK
1 Linco
ln
Holtho
use
Road
8280 9723 2 1/ 2/2020 1 Austra
lia
UK
1 Linco
ln
Holtho
use
Road
8280 9723 3 1/ 9/2020 2 UK Australi
a
The second normal form describes that the database entities must be free from partial
dependencies. The partial dependency exists in an entity when a non-key attributes are dependent

10DATA MODELLING & SQL LANGUAGE
on another non-key attribute. In the above entity, the booking details are dependent on the
bookingId. That is why a new booking table is introduced which has primary key passengerID as
foreign key.
passengerID name address phoneNumber
1 Lincoln Holthouse
Road
8280 9723
bookingI
D
passengerID bookingDate flightI
D
origin destination
1 1 1/ 2/2020 1 Australia UK
2 1 1/ 2/2020 1 Australia UK
3 1 1/ 9/2020 2 UK Australia
The third normal form is achieved by eliminating transitive dependency. The flightID and
bookingID has transitive dependency among them. For making the entity in third normal form, a
new flight table is introduced.
bookingI
D
passengerID bookingDate flightID
1 1 1/ 2/2020 1
2 1 1/ 2/2020 1
3 1 1/ 9/2020 2
flightID origin destination
1 Australia UK
2 UK Australia
Therefore, the selected part of the database will have the following entities if normalized
up to third normal form.

11DATA MODELLING & SQL LANGUAGE
passengerID name address phoneNumber
1 Lincoln Holthouse
Road
8280 9723
bookingI
D
passengerID bookingDate flightID
1 1 1/ 2/2020 1
2 1 1/ 2/2020 1
3 1 1/ 9/2020 2
flightID origin destination
1 Australia UK
2 UK Australia

12DATA MODELLING & SQL LANGUAGE
Entity Relationship Diagram:
Figure 5: Normalized Entity Relationship Diagram
(Source: Created by Author)

13DATA MODELLING & SQL LANGUAGE
Relational Data Model:
Passenger
passengerID passName addresss phoneNumber
1 Jake Szabados 53 Jones Street [phone]
2 Joshua Grahamslaw 28 Wigley Street [phone]
Employees
employeeID empName address empSalary
1 Oscar Tepper 89 Buoro Street 2320.00
2 Matthew Newdegate 22 Sale-Heyfield
Road
1480.00
Aircraft
aircraftID modelName manufacturer
1 Boing 747 Blue Box
2 Airbus A320-200 Airbus Corporate Jets
Flights
flightNumber aircraftID origin destination departureTim
e
arrivalTim
e
1 2 Melbourne New York 10:00:00 18:30:00
2 1 New York Melbourne 10:00:00 21:15:00
Responsibility
flightNumber employeeID responsibility
1 1 Main Pilot
2 2 Main Pilot

14DATA MODELLING & SQL LANGUAGE
Bookings
bookingID flightNumbe
r
passengerI
D
cost bookingTim
e
coPassengerQua
n
1 2 1 120.00 10:00:00 1
2 1 2 180.00 11:00:00 2
3 1 1 120.00 10:05:00 1
Payments
payID bookingID payTime method
1 1 11:00:00 Credit Card
2 3 11:00:00 Credit Card
3 2 11:30:00 PayPal
Cancelled
bookingID cancelationTim
e
cancelCharge reason
2 18:00:00 30.00 Higher cost of travel
Completed
bookingID feedback customerRating
1 Good hosting service and
good quality food
4.5
Task 4: Physical Modelling
Create Database:
CREATE TABLE aircraft (
aircraftID int(11) NOT NULL,

15DATA MODELLING & SQL LANGUAGE
modelName varchar(200) NOT NULL,
manufacturer varchar(200) NOT NULL
);
CREATE TABLE bookings (
bookingID int(11) NOT NULL,
flightNumber int(11) NOT NULL,
passengerID int(11) NOT NULL,
cost decimal(12,2) NOT NULL,
bookingTime time NOT NULL,
coPassengerQuan int(11) NOT NULL
);
CREATE TABLE cancelled (
bookingID int(11) NOT NULL,
cancelationTime time NOT NULL,
cancelCharge decimal(12,2) NOT NULL,
reason varchar(200) NOT NULL
);
CREATE TABLE completed (
bookingID int(11) NOT NULL,
feedback varchar(200) NOT NULL,
customerRating decimal(2,1) NOT NULL
);
CREATE TABLE employees (

16DATA MODELLING & SQL LANGUAGE
employeeID int(11) NOT NULL,
empName varchar(200) NOT NULL,
address varchar(200) NOT NULL,
empSalary decimal(12,2) NOT NULL
);
CREATE TABLE flights (
flightNumber int(11) NOT NULL,
aircraftID int(11) NOT NULL,
origin varchar(20) NOT NULL,
destination varchar(20) NOT NULL,
departureTime time NOT NULL,
arrivalTime time NOT NULL
);
CREATE TABLE passengers (
passengerID int(11) NOT NULL,
passName varchar(200) NOT NULL,
addresss varchar(200) NOT NULL,
phoneNumber varchar(15) NOT NULL
);
CREATE TABLE payments (
payID int(11) NOT NULL,
bookingID int(11) NOT NULL,
payTime time NOT NULL,

17DATA MODELLING & SQL LANGUAGE
method varchar(20) NOT NULL
);
CREATE TABLE responsibility (
flightNumber int(11) NOT NULL,
employeeID int(11) NOT NULL,
responsibility varchar(20) NOT NULL
);
ALTER TABLE aircraft
ADD PRIMARY KEY (aircraftID);
ALTER TABLE bookings
ADD PRIMARY KEY (bookingID),
ADD KEY flightNumber (flightNumber),
ADD KEY passengerID (passengerID);
ALTER TABLE cancelled
ADD PRIMARY KEY (bookingID);
ALTER TABLE completed
ADD PRIMARY KEY (bookingID);
ALTER TABLE employees
ADD PRIMARY KEY (employeeID);
ALTER TABLE flights
ADD PRIMARY KEY (flightNumber),
ADD KEY aircraftID (aircraftID);
ALTER TABLE passengers

18DATA MODELLING & SQL LANGUAGE
ADD PRIMARY KEY (passengerID);
ALTER TABLE payments
ADD PRIMARY KEY (payID),
ADD KEY bookingID (bookingID);
ALTER TABLE responsibility
ADD PRIMARY KEY (flightNumber,employeeID),
ADD KEY employeeID (employeeID);
ALTER TABLE bookings
ADD CONSTRAINT bookings_ibfk_1 FOREIGN KEY (flightNumber)
REFERENCES flights (flightNumber),
ADD CONSTRAINT bookings_ibfk_2 FOREIGN KEY (passengerID) REFERENCES
passengers (passengerID);
ALTER TABLE cancelled
ADD CONSTRAINT cancelled_ibfk_1 FOREIGN KEY (bookingID) REFERENCES
bookings (bookingID);
ALTER TABLE completed
ADD CONSTRAINT completed_ibfk_1 FOREIGN KEY (bookingID) REFERENCES
bookings (bookingID);
ALTER TABLE flights
ADD CONSTRAINT flights_ibfk_1 FOREIGN KEY (aircraftID) REFERENCES
aircraft (aircraftID);
ALTER TABLE payments
ADD CONSTRAINT payments_ibfk_1 FOREIGN KEY (bookingID) REFERENCES
bookings (bookingID);

19DATA MODELLING & SQL LANGUAGE
ALTER TABLE responsibility
ADD CONSTRAINT responsibility_ibfk_1 FOREIGN KEY (flightNumber)
REFERENCES flights (flightNumber),
ADD CONSTRAINT responsibility_ibfk_2 FOREIGN KEY (employeeID)
REFERENCES employees (employeeID);
COMMIT;
Populate Database:
INSERT INTO `aircraft` (`aircraftID`, `modelName`, `manufacturer`) VALUES
(1, 'Boing 747', 'Blue Box'),
(2, 'Airbus A320-200', 'Airbus Corporate Jets');
INSERT INTO `bookings` (`bookingID`, `flightNumber`, `passengerID`, `cost`,
`bookingTime`, `coPassengerQuan`) VALUES
(1, 1, 1, '120.00', '10:00:00', 1),
(2, 1, 2, '180.00', '11:00:00', 2),
(3, 2, 1, '120.00', '10:05:00', 1);
INSERT INTO `cancelled` (`bookingID`, `cancelationTime`, `cancelCharge`, `reason`)
VALUES
(2, '18:00:00', '30.00', 'Higher cost of travel');
INSERT INTO `completed` (`bookingID`, `feedback`, `customerRating`) VALUES
(1, 'Good hosting service and good quality food', '4.5');
INSERT INTO `employees` (`employeeID`, `empName`, `address`, `empSalary`)
VALUES
(1, 'Oscar Tepper', '89 Buoro Street', '2320.00'),
(2, 'Matthew Newdegate', '22 Sale-Heyfield Road', '1480.00');

20DATA MODELLING & SQL LANGUAGE
INSERT INTO `flights` (`flightNumber`, `aircraftID`, `origin`, `destination`,
`departureTime`, `arrivalTime`) VALUES
(1, 2, 'Melbourne', 'New York', '10:00:00', '18:30:00'),
(2, 1, 'New York', 'Melbourne', '10:00:00', '18:30:00');
INSERT INTO `passengers` (`passengerID`, `passName`, `addresss`, `phoneNumber`)
VALUES
(1, 'Jake Szabados', '53 Jones Street', '[phone]'),
(2, 'Joshua Grahamslaw', '28 Wigley Street', '[phone]');
INSERT INTO `payments` (`payID`, `bookingID`, `payTime`, `method`) VALUES
(1, 1, '11:00:00', 'Credit Card'),
(2, 3, '11:00:00', 'Credit Card'),
(3, 2, '11:30:00', 'PayPal');
INSERT INTO `responsibility` (`flightNumber`, `employeeID`, `responsibility`)
VALUES
(1, 1, 'Main Pilot'),
(2, 2, 'Main Pilot');

21DATA MODELLING & SQL LANGUAGE
Bibliography:
Arulraj, J., & Pavlo, A. (2017, May). How to build a non-volatile memory database management
system. In Proceedings of the 2017 ACM International Conference on Management of Data(pp.
1753-1758). ACM.
Elmasri, R., & Navathe, S. (2017). Fundamentals of database systems. Pearson.
Gahi, Y., Guennoun, M., & El-Khatib, K. (2015). A secure database system using homomorphic
encryption schemes. arXiv preprint arXiv:1512.03498.
Kraska, T., Alizadeh, M., Beutel, A., Chi, E. H., Ding, J., Kristo, A., ... & Nathan, V. (2019).
Sagedb: A learned database system.
Luo, S., Gao, Z., Gubanov, M., Perez, L. L., & Jermaine, C. (2018). Scalable linear algebra on a
relational database system. IEEE Transactions on Knowledge and Data Engineering.
Nidzwetzki, J. K., & Güting, R. H. (2016). DISTRIBUTED SECONDO: An extensible highly
available and scalable database management system. FernUniversität, Fakultät für Mathematik
und Informatik.
Nidzwetzki, J. K., & Güting, R. H. (2016). DISTRIBUTED SECONDO: An extensible highly
available and scalable database management system. FernUniversität, Fakultät für Mathematik
und Informatik.
Poddar, R., Boelter, T. and Popa, R.A., 2016. Arx: A strongly encrypted database system. IACR
Cryptology ePrint Archive, 2016, p.591.
Rao, J. (2017, November). Design of Bilingual Course Teaching of Database Principles.
In International Conference on Education Innovation and Social Science (ICEISS 2017). Atlantis
Press.
Sarwat, M., Moraffah, R., Mokbel, M. F., & Avery, J. L. (2017, April). Database system support
for personalized recommendation applications. In 2017 IEEE 33rd International Conference on
Data Engineering (ICDE) (pp. 1320-1331). IEEE.

22DATA MODELLING & SQL LANGUAGE
Scotti, A., Hannum, M., Ponomarenko, M., Hogea, D., Sikarwar, A., Khullar, M., ... & Deng, L.
(2016). Comdb2 bloomberg's highly available relational database system. Proceedings of the
VLDB Endowment, 9(13), 1377-1388.
Wang, Z., Zhou, D., & Chen, S. (2017). STEED: an analytical database system for tree-
structured data. Proceedings of the VLDB Endowment, 10(12), 1897-1900.
Yu, X., Xia, Y., Pavlo, A., Sanchez, D., Rudolph, L., & Devadas, S. (2018). Sundial:
harmonizing concurrency control and caching in a distributed OLTP database management
system. Proceedings of the VLDB Endowment, 11(10), 1289-1302.