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