Data Modelling & SQL Language

This document provides an in-depth understanding of data modelling and SQL language. It covers topics such as database systems vs file based systems, hierarchical, network and relational models, top-down and bottom-up approaches. It also explores conceptual, logical and physical modelling.

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
1.1Database System VS File Based System:...............................................................................2
1.2 Hierarchical, Network and Relational Models:.....................................................................3
1.3 Top-down and Bottom-up Approaches:................................................................................4
Task 2: Conceptual Modelling.........................................................................................................5
Task 3: Logical Modelling...............................................................................................................6
Task 4: Physical Modelling...........................................................................................................13
Create Database:........................................................................................................................13
Bibliography:.................................................................................................................................18

2DATA MODELLING & SQL LANGUAGE
Task 1: Understanding of Database
1.1Database System VS File Based System:
Database System: The database management system is a set of data. In terms of database
management system, there is no need to write procedures. The database management system
offers an abstract view of data. This allows the database management system to offer the user
access to data without providing insight of it. There is also a crash recovery mechanism in
database management system which is safeguards the data from system failure. There is also
very good protection mechanism in database management system. The storing and retrieval of
data is done in organized way in database management system (Arulraj 2017). There is also
locking approaches in database management system which handles the concurrent access of data.
The main advantage of database system is synchronization. The data will be
synchronized within the database so that every transaction data can be handled properly. The
disadvantage of database management system is ineffective memory usage. The consistent usage
of RAM can make the system slow. The biggest limitation of DBMS is cost. The database
system are costly than file based system.
File Based System: The file system is also a collection of data, however, which asks the
user to write various procedures for handling the data. The representation and storage of data is
provided by the file based system. File system does not include any approach for protecting the
data from system failure as it does not have any crash mechanism. This can be a huge issue as all
the data can be lost.
The first advantage of file based system is saving the data. The data can be saved by just
one click. The data migration in file based system is also very easy (Arpaci-Dusseau, Arpaci-
Dusseau and Venkataramani 2018). The disadvantage is that there is no complex ideas like
ACID property in file based system. The main limitation of this system is data duplication.
Advantages of DBMS over File Based System: The database management system is
capable of managing data redundancy. The redundancy is the concept of data repetition which
creates multiple copies of same data. There is no approach in the file based system to manage the
redundancy of data. The sharing of data is very complex in file based system. However, as the

3DATA MODELLING & SQL LANGUAGE
database system has centralized approach, the sharing of data is very easy. In order to perform a
search operation, different programs needs to be developed. The database management system
provides a very easy approach toward searching by providing inbuilt searching option.
1.2 Hierarchical, Network and Relational Models:
Hierarchical Model: A tree like structure is used in the hierarchical database model for
each record that has one parent and many children. The hierarchical model supports only one-to-
many relationship between parent and child nodes.
Taken as an example, the students can be direct children of the root node. Each course
can be children of student node (Ogle et al. 2016). The above model describes that one student
can have many courses. This means that a children can also have many children and in that case
that particular child node will function as parent node.
Network Model: The network data model is designed for representing the objects and the
relationship among the objects flexibly. The schema in the network model is represented as the
graph. The objects are interpreted as nodes and relationships are viewed as arcs. The concept
behind the network model is very simple and implementation of network model is also effortless
(Gile and Handcock 2015). The network model is capable of handling many-to-many and one-to-

4DATA MODELLING & SQL LANGUAGE
one relationships. The access to the data stored in the network model is also very easy. However,
there are many drawbacks to the network model. In network model, the every node has to be
maintained with the assistance of pointers. The maintenance of pointers are very complex to
track and manage. The essential operations like insert, update and delete becomes extremely
complex. If the network model structure is changed then the application is also changed.
Relational Model: The data and relationships among the data is visualized as a collection
of inter-connected tables in the relational model. The tables are consisted of rows and columns.
The row represent the records in the table whereas the columns represent the attributes. There is
a key Integrity in the relational model which represent the primary or composite primary key in
the table. The tables refers to the entity in the database model. Each entity must have its own
attributes to be marked as primary key (Avgustinov et al. 2016). In some cases, the entities uses
attributes of other tables as references which may also be used as primary key.
1.3 Top-down and Bottom-up Approaches:
Top-Down: The top-down approach included appropriate planning and analysis in every
stage, which in turn leads to the successful development of the desired database. The to-down
approach is not meant for developing the low-level representation of entire database where all
the requirements are known (Huang et al. 2017).
Advantages of Top-Down
i. All components have a high-level view
ii. The alterations in the data and relationships can be easily tracked
iii. The requirements are visible before the implementation
Drawbacks of Top- Down:
i. This approach takes a lot of time to be completed, in comparison with bottom-up
approach
ii. There should be continuous and un-interrupted communication between the
database developer and end-user
Bottom-Up: The bottom-up approach is most suitable for a specific section of a big
organization or a small organization. The complexity of entity, relationship and attribute
management is less than that of top-down approach (Hu and Janowicz 2016). In this approach,

5DATA MODELLING & SQL LANGUAGE
the attributes are defined and then entities are developed. After the previous two parts are
completed, the database creates the conceptual schema.
Advantages of Bottom- Up:
i. Creating a working system is much easier and faster
ii. Various modules are designed and developed in the early phases which supports
later development
Drawbacks of Bottom-Up:
i. This approach does not provide high-level view of the data
ii. Big and complex databases which are industry standard are not be developed
using this approach
Task 2: Conceptual Modelling
Entity Identification: The database entities of Jet Airline are as following.
i. Passengers
ii. Flights
iii. Employees
iv. EmployeeJob
v. Aircraft
vi. Payments
vii. Bookings
viii. Cancelled
Database ERD:

6DATA MODELLING & SQL LANGUAGE
Figure 4: Conceptual Data Model
(Source: Created by Author)
Task 3: Logical Modelling
Normalize Jet Airline Database up to 3rd Normal Form: Normalization is the way of
data organization within the database. This process is used for minimizing the data redundancy
from a relation or a collection of relations. The normalization allows the database developer to
divide the big tables into smaller ones and linking those tables using relational integrity. There
are total five normal forms in normalization, however, in this assignment only first three normal
forms are considered.

7DATA MODELLING & SQL LANGUAGE
First normal form: The following table shows the passenger, booking, flight,
cancellation and aircraft details in brief. All the attributes of these relations has not been used.
However, the following table has enough information to show the normalization process in short.
The table has multivalued attributes as for each passenger, there is multiple values in booking
relation.
passenger
_id
pass_na
me
book_
id
book_d
ate
flight_nu
mber
origi
n
cancel_d
ate
aircraft
_id
model_na
me
101 Darcy
Backha
us
1001 July 14,
2018
101 New
York
July 15,
2018
1 Airbus
A380-80
0
1002 July 15,
2018
102 Adel
aide
1 2 Boeing
747-8
1003 July 16,
2018
103 Sydn
ey
2 1 Boeing
747-400
The following table is created to convert the multivalued attributes into atomic attributes.
The following table create data redundancy for passenger relation so that multivalued attributes
into atomic attributes.
passenger
_id
pass_na
me
book_
id
book_d
ate
flight_nu
mber
origi
n
cancel_d
ate
aircraft
_id
model_na
me
101 Darcy
Backha
us
1001 July 14,
2018
101 New
York
July 15,
2018
1 Airbus
A380-80
0
101 Darcy
Backha
us
1002 July 15,
2018
102 Adel
aide
2 Boeing
747-8
101 Darcy
Backha
us
1003 July 16,
2018
103 Sydn
ey
1 Boeing
747-400

8DATA MODELLING & SQL LANGUAGE
Second Normal Form: partial dependency occurs in a relation when an attribute is
dependent on the non-primary key of the same relation. Suppose relation Passenger has
passenger_id, pass_name, book_id and book_date. The book_date is also dependent on the
book_id which is not the primary key of the table. Therefore, a new relation called booking is
created to store the information of booking.
passenger_id pass_name
101 Darcy Backhaus
The model_name is also dependent on the aircraft_id which is also not a primary key of
the relation. Therefore, aircraft relation is also created to make the database relations in second
normal form.
aircraft_id model_name
1 Airbus A380-800
2 Boeing 747-8
The booking relation then have the following attributes.
book_id book_date flight_number origin cancel_date cancel_fine
1001 July 14, 2018 101 New York July 15,
2018
$50.00
1002 July 15, 2018 102 Adelaide
1003 July 16, 2018 103 Sydney
Third Normal From: The transitive dependency exists in a relation if a functional
dependency is formed by two functional dependencies. In the following relation, cancel_date is
dependent on book_id but book_id is not dependent on cancel_date. However, the fine is
dependent on the cancel_date. Therefore, there is transitive dependency is present between
book_id and cancel_date. A new table cancellation is created to remove transitive dependency.
The new tables are as following.

9DATA MODELLING & SQL LANGUAGE
book_id book_date flight_number origin
1001 July 14, 2018 101 New York
1002 July 15, 2018 102 Adelaide
1003 July 16, 2018 103 Sydney
book_id cancel_date cancel_fine
1001 July 15, 2018 $50.00

10DATA MODELLING & SQL LANGUAGE
Entity Relationship Diagram:
Figure 5: Normalized Entity Relationship Diagram
(Source: Created by Author)
Relational Data Model:
Passenger

11DATA MODELLING & SQL LANGUAGE
passenger_id pass_name addresss phone_number
1001 Ryan Frencham 16 George Street,
MOUNT
BINDANGO QLD
4455
[phone]
1002 Callum Reginald 11 Cambridge Street,
FREEMANS
REACH NSW 2756
[phone]
1003 Brock Springthorpe 10 Kogil Street,
UPPER BINGARA
NSW 2404
[phone]
Employees
emp_id emp_name address emp_salary
1 Sean Rowe 36 Flinstone Drive,
INTERLAKEN TAS
7030
2000.00
2 Lilly Reade 51 Warren Avenue,
DOYALSON
NORTH NSW 2262
1000.00
3 Hugo Crombie 9 Bayview Close,
MOUNT
MURCHISON QLD
4715
2500.00
Aircraft
aircraft_id model_name manufacturer
1 Airbus A380-800 Airbus Corporate Jets
2 Boeing 747-8 Airbus Corporate Jets

12DATA MODELLING & SQL LANGUAGE
Flights
flight_numbe
r
aircraf
t
origin destinatio
n
flight_dat
e
departure_tim
e
arrival_tim
e
101 1 New
York
Adelaide 12/05/201
9
10:00:00 18:30:00
102 2 Adelaid
e
Sydney 18/05/201
9
10:00:00 21:15:00
103 3 Sydney Adelaide 27/05/201
9
10:00:00 19:45:00
EmployeeJob
flight employee emp_job
101 1 Pilot
102 1 Air Hostess
103 2 Pilot
Bookings
book_id flight passenger_i
d
book_cos
t
book_date book_time total_passenger
s
1001 101 1001 100.00 12/04/2019 11:58:00 1
1002 102 1002 100.00 18/04/2019 17:59:00 1
1003 103 1003 100.00 27/04/2019 08:27:00 1
Payments
pay_id booking pay_date pay_time tax pay_total
1001 1001 12/04/2019 12:00:00 5% 105.00
1002 1002 18/04/2019 18:00:00 5% 105.00
1003 1003 27/04/2019 08:30:00 5% 105.00

13DATA MODELLING & SQL LANGUAGE
Cancellation
booking_id cancel_date cancel_time cancel_fine reason
1 12/04/2019 17:00:00 $50.00 Change of plan
Task 4: Physical Modelling
Create Database:
CREATE DATABASE IF NOT EXISTS `database1` DEFAULT CHARACTER SET
latin1 COLLATE latin1_swedish_ci;
USE `database1`;
CREATE TABLE `aircraft` (
`aircraft_id` int(11) NOT NULL PRIMARY KEY,
`model_name` varchar(150) NOT NULL,
`manufacturer` varchar(150) NOT NULL
);
CREATE TABLE `employees` (
`emp_id` int(11) NOT NULL PRIMARY KEY,
`emp_name` varchar(150) NOT NULL,
`address` varchar(150) NOT NULL,
`emp_salary` decimal(12,2) NOT NULL

14DATA MODELLING & SQL LANGUAGE
);
CREATE TABLE `flights` (
`flight_number` int(11) NOT NULL PRIMARY KEY,
`aircraft` int(11) NOT NULL,
`origin` varchar(30) NOT NULL,
`destination` varchar(30) NOT NULL,
`flight_date` DATE NOT NULL,
`departure_time` time NOT NULL,
`arrival_time` time NOT NULL,
FOREIGN KEY (aircraft) REFERENCES Aircraft(aircraft_id)
);
CREATE TABLE `EmployeeJob` (
`flight` int(11) NOT NULL,
`employee` int(11) NOT NULL,
`emp_job` varchar(20) NOT NULL,

15DATA MODELLING & SQL LANGUAGE
CONSTRAINT PK_EmployeeJob PRIMARY KEY (flight,employee),
FOREIGN KEY (flight) REFERENCES Flights(flight_number),
FOREIGN KEY (employee) REFERENCES Employees(emp_id)
);
CREATE TABLE `passengers` (
`passenger_id` int(11) NOT NULL PRIMARY KEY,
`pass_name` varchar(150) NOT NULL,
`addresss` varchar(150) NOT NULL,
`phone_number` varchar(15) NOT NULL
);
CREATE TABLE `bookings` (
`book_id` int(11) NOT NULL PRIMARY KEY,
`flight` int(11) NOT NULL,
`passenger_id` int(11) NOT NULL,
`book_cost` decimal(12,2) NOT NULL,
`book_date` DATE NOT NULL,
`book_time` time NOT NULL,

16DATA MODELLING & SQL LANGUAGE
`total_passengers` int(11) NOT NULL,
FOREIGN KEY (flight) REFERENCES Flights(flight_number),
FOREIGN KEY (passenger_id) REFERENCES Passengers(passenger_id)
);
CREATE TABLE `Cancellation` (
`booking_id` int(11) NOT NULL PRIMARY KEY,
`cancel_date` DATE NOT NULL,
`cancel_time` time NOT NULL,
`cancel_fine` decimal(12,2) NOT NULL,
`reason` varchar(150) NOT NULL,
FOREIGN KEY (booking_id) REFERENCES bookings(book_id)
);
CREATE TABLE `payments` (
`pay_id` int(11) NOT NULL PRIMARY KEY,

17DATA MODELLING & SQL LANGUAGE
`booking` int(11) NOT NULL,
`pay_date` DATE NOT NULL,
`pay_time` time NOT NULL,
`tax` int(11) NOT NULL,
`pay_total` decimal(12,2) NOT NULL,
FOREIGN KEY (booking) REFERENCES Bookings(book_id)
);

18DATA MODELLING & SQL LANGUAGE
Bibliography:
Arpaci-Dusseau, R.H., Arpaci-Dusseau, A. and Venkataramani, V., 2018. Cloud-native file
systems. In 10th {USENIX} Workshop on Hot Topics in Cloud Computing (HotCloud 18).
Arulraj, J., 2017. The Design and Implementation of a Non-Volatile Memory Database
Management System (Doctoral dissertation, Microsoft Research).
Avgustinov, P., De Moor, O., Jones, M.P. and Schäfer, M., 2016. QL: Object-oriented queries on
relational data. In 30th European Conference on Object-Oriented Programming (ECOOP 2016).
Schloss Dagstuhl-Leibniz-Zentrum fuer Informatik.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Gile, K.J. and Handcock, M.S., 2015. Network model‐assisted inference from respondent‐driven
sampling data. Journal of the Royal Statistical Society: Series A (Statistics in Society), 178(3),
pp.619-639.
Hu, Y. and Janowicz, K., 2016. Enriching top-down geo-ontologies using bottom-up knowledge
mined from linked data. Advancing Geographic Information Science: The Past and Next Twenty
Years, 183.
Huang, J., Mozafari, B., Schoenebeck, G. and Wenisch, T.F., 2017, May. A top-down approach
to achieving performance predictability in database systems. In Proceedings of the 2017 ACM
International Conference on Management of Data (pp. 745-758). ACM.
Lewis, K.A., Tzilivakis, J., Warner, D.J. and Green, A., 2016. An international database for
pesticide risk assessments and management. Human and Ecological Risk Assessment: An
International Journal, 22(4), pp.1050-1064.
Nidzwetzki, J.K. and 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.

19DATA MODELLING & SQL LANGUAGE
Ogle, K., Ryan, E., Dijkstra, F.A. and Pendall, E., 2016. Quantifying and reducing uncertainties
in estimated soil CO2 fluxes with hierarchical data‐model integration. Journal of Geophysical
Research: Biogeosciences, 121(12), pp.2935-2948.
Poerner, N. and Winkelmann, R., 2017. Interfacing the BAS Speech Science Web Services and
the EMU Speech Database Management System. Preface & Acknowledgements, p.141.
Yu, X., Xia, Y., Pavlo, A., Sanchez, D., Rudolph, L. and Devadas, S., 2018. Sundial:
harmonizing concurrency control and caching in a distributed OLTP database management
system. Proceedings of the VLDB Endowment, 11(10), pp.1289-1302.