Data Modelling SQL Language Tasks 2022

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

1DATA MODELLING & SQL LANGUAGE
Task 1
Comparison of the File based system and database system for storing data
Advantages of File based system
Compactness of file system: In file systems it is possible to store data in compact
format. In addition to that, File System is easy-to-use in order to store general files that
require lesser amount of security.
Minimal investment and Easy to use: The usage of File based system requires
minimal investment to setup. As there is no investment in developing the software as file
system allows the users to transfer files from one computer to another without the
requirement of any specialist.
Disadvantages of File based system
Accessibility issue: In case of File System, data is stored in the form of different
isolated files on the computers that had their own properties as well as physical location on
hard drive. The user have to manually find and goes to the location of the stored file to get
and access the data which is time consuming.
Lack of consistency and integrity: Due to the isolated storage and integrity check of
the stored data, redundancy is more in the file system and this cannot be controlled easily. In
addition to that data consistency is also not met in the file based system leading to the loss of
resources.
Advantages of the Database system

2DATA MODELLING & SQL LANGUAGE
Security of data: Database management system can ensure the high security of the
data through the use of the constraints in the database.
Centralization: On the contrast of the File based system the centralisation of the data
can be achieved in Database Management System which can help in availability of data.
Well defined and structured data: The Database Management System are capable
of storing data in a structured way with the use of the well-defined constraints on tables and
interrelation among them.
Disadvantages of Database system
Higher Cost: Required Cost in order to set up a DBMS according to the required
functions which incurs recurring annual maintenance cost to maintain the developed system.
Failure and loss data: Different kind failures such as the corrupted database due to
power failure/crashed system may lead to loss of huge amount of important data.
Characteristics of the of Hierarchical, network and relational database models
Characteristics of Hierarchical model
In the hierarchical model, data is stored in tree like structures where the parent kid
relationship is kept up. As it were it very well may be expressed that required information is
spared in top down or bottom up methodology. The data kept in the database is spoken to as
parent-kid relationship. In this database framework parent can have various kids, while the
child data node can have just one parent.
Application of Hierarchical model
The Hierarchical database model is utilized in the scenarios where very high
performance and high availability of the database is required for the front end applications.
As an example telecommunication and banking sectors/industries use this database model.

3DATA MODELLING & SQL LANGUAGE
Characteristics of Network models
In case of the network database model numerous records can be connected to a
single owner file of the database. This model can be represented as an upside down tree
structure where the branches are the data connected to the owner table, which is the base of
the tree. The various links which this model permits the system to be flexible to meet the
organizational needs through the database. Also, the relationship that the data has in the
system database model is characterized by m:m (many to many) relationship since one owner
document can be connected to numerous other records and the other way around.
Application of Network models
Network based database model is used by the organizations where multiple
branches/department need to access and manipulate the data at the same point of time while
maintaining the ACID property. This type of database models also helpful in providing
access to different type of data and restricting the accessibility by using different
authorization levels.
In network based database model the data rows or records are connected with each
other using relationship among the tables. Every record in the tables are collection of
multiple attributes. In a completely normalized database tables the records contains single
data value. Relationships among the tables are helpful in establishing connection between
two records of two tables.
Characteristics of Relational database

4DATA MODELLING & SQL LANGUAGE
Relational database model is the most popular model among all the database models.
In this model data tables are normalized in order to get manage the records using the columns
(Attributes) and rows in the database. Data or the records are is stored in table like structures
and different manipulations (create, read, delete and update) using the SQL (structured query
language.)
Application of Relational database
Relational database model is capable of handling large amount of data and millions
of records along with their complex relationships. Therefore this model is thus this model is
used in the large applications like the banking application or the airline reservation systems
that stores huge amount of data.
Bottom up and top down approach
Top down approach
In case of designing a database, the top-down approach is used by the developers in
order to start from the generalization of the different required tables/relations and afterwards
moves to specific attributes for the complete database model. Database designer initializes
the process with the general or rough idea about what is required. In the next stage, database
designer queries and specifies from the end-user to determine what is expected to be stored in
the database.
Bottom up approach
In e Bottom-up approach, databases designer starts with specific details about the
different and then proceed with the general perspective for the requirement from the
database. In order to get started with the bottom up approach, the database designer or the
analyst have to inspect developed interfaces for the proposed application that will be

5DATA MODELLING & SQL LANGUAGE
connected with the database. After this the analyst backtracks through the system analysis
process so that it can determine the required data types, fields that needs to be stored.
Task 2
Possible entities of the database
Flight_Details
Airline
Employeetable
Booking_status
Passengertable
Paymenttable

6DATA MODELLING & SQL LANGUAGE

7DATA MODELLING & SQL LANGUAGE
Task 3
The process of normalization for database
The Normalization process is important in database management in order to reduce
data redundancies in a database. With the help of the Normalization it is possible to optimally
design a proposed database excluding the redundant data in order to use the available
memory in an efficient way. Without this process, there are certain anomalies that can be
faced by the users while using the developed database.
Update anomaly: If data or records in the tables are scattered in different tables and
are not linked to each other using specific relationships then this can lead to memory
exhaustion issue. As an example it can be stated that when a user updates one row having its
multiple copies are stored in multiple tables, then it may happen that some of the tables the
row is updated and get updated properly while few other tables are not updated with the
latest value and left with previous values. This non-updated instances or tables lefts the
database in an inconsistent state.
Deletion anomaly: When a user try’s to delete a record from a table in a specific
database, but some other tables still holds the value and was not undeleted because of
unawareness, then the linked data may lead to the inconsistent with the other tables.
Insert anomalies: Users try to insert data in a table/record that does not exist at all
inside the database tables.
For this project, the normalization of the passenger table is depicted and described
below;
The first normal form of the table is;

8DATA MODELLING & SQL LANGUAGE
Passenger_ID, name, Gender, contact, address, booking id, country
2nd normal form
Passenger _id, name, contact, booking_id
Passenger _id, booking_id, address, country;
3rd Normal form
User_id, booking_id, country;
Passenger_id, name, contact;
Passenger_id, address ,country;
Modified ERD after normalization
Relationships among the tables
1. The passenger_id in booking table can be used as foreign key to the Passenger
table.
2. Booking_id in the payment table links the tables Booking and payment
Task 4
DDL Queries

9DATA MODELLING & SQL LANGUAGE
CREATE TABLE Passengertable(PassengerID INT AUTO_INCREMENT, FirstNam
e VARCHAR(30), lastName VARCHAR(30), age INT, email VARCHAR(50), PRIMARY
KEY(PassengerID))
CREATE TABLE Airline ( ID INT AUTO_INCREMENT, name VARCHAR(50), co
untry VARCHAR(50), PRIMARY KEY(ID) )
CREATE TABLE Flight ( ID INT AUTO_INCREMENT, airlineID INT, departTime
TIME , departDate DATE , arrivalTime TIME , arrivalDate DATE, PRIMARY KEY(ID), F
OREIGN KEY(ID) REFERENCES airline(ID ) ON DELETE CASCADE )
CREATE TABLE Booking_status( ID INT AUTO_INCREMENT, flightID INT, Des
t VARCHAR(100), seatNumber VARCHAR(100), PassengerID INT, Price INT, PRIMARY
KEY(ID), FOREIGN KEY(flightID) REFERENCES flight(ID) ON DELETE CASCADE, F
OREIGN KEY(PassengerID) REFERENCESpassengertable(PassengerID) ON DELETE CA
SCADE)
CREATE TABLE emlpoyee( ID INT, Name VARCHAR(100), Gender
VARCHAR(20), Address VARCHAR(75), Contact VARCHAR(15), PRIMARY KEY(ID) )
CREATE TABLE Paymenttable ( TransactionID INT, Name VARCHAR(50), Booki
ngID INT, PaymentAmount INT, PaymentDate DATE, PRIMARY KEY(TransactionID), FO
REIGN KEY(TransactionID) REFERENCES booking_status(ID) )

10DATA MODELLING & SQL LANGUAGE
Bibliography
Taneja, D., Bhamidipati, P.V., Byragani, B.Y., Nadimpalli, S. and Lull, J., EMC Corp, 2016.
System and method for collecting and normalizing entitlement data within an enterprise. U.S.
Patent 9,286,595.
Connolly, T. and Begg, C., 2015. Database systems. Pearson Education UK.
Harding, D., ImageWare Systems Inc, 2016. Systems and methods for biometric data
management using relational database management systems (RDBMS). U.S. Patent
9,268,904.
Martin, C.B., Poyatos, J.M., Sierra, M.R. and Pérez, A.S., Actuate Corp, 2017. Performing
data mining operations within a columnar database management system. U.S. Patent
9,798,783.