Data Modelling and SQL Language

This document discusses data modelling and SQL language. It covers topics such as file-based systems, database systems, hierarchical, network, and relational database models, normalization, and DDL queries. It also includes solved assignments, essays, dissertations, and more.

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

1DATA MODELLING AND SQL LANGUAGE
Task 1
Comparison of the File based system and database system
Advantages and Disadvantages of File based system
Easy Backup: in case of the file based data storage system it is possible have faster
back-up of the stored data in the computer-based information systems. There are numerous
functionalities available for Computer based information systems that are helpful in the
accomplishing this purpose. Moreover it is also possible to develop applications that are
tailored for specific purposes.
Compactness: In file based systems it is also possible to store required data in a
compact way so that the memory or disk usage can be minimized.
Retrieval and editing of information: Computer-based file systems are very
helpful in the providing different data retrieval techniques or editing techniques which are
easy and efficient for managing the data stored in file format.
Disadvantages of file based system
In case of the file based system following are the most important disadvantages,
Redundancy of data: in case of file based system it may happen that the users
possibly store the same data/information multiple times which will lead to duplicated storage
and additional storage requirements and hence leads to the memory wastage.
Inconsistent Data: As the file based systems are prone to data redundancy thus it may
lead to the data that are not in a consistent state.
Accessibility issues to the Data: Accessibility to the data is not that much convenient
when data is stored inside a file processing system.

2DATA MODELLING AND SQL LANGUAGE
Advantages of the Database system
Compared to the traditional file based system of data storage following are the
advantages that encourages the users to use DBMS system rather than the File based system.
Consistency and Integrity of Data: As it is found that the main reason behind the data
inconsistency is data redundancy thus use of the normalization while storing data in the
DBMS takes care and resolves the data redundancy issue. In the same manner the data
inconsistency issue is also resolved through the maintenance of the ACID property of any
database.
Data Security: In database system it is possible to apply access constraints depending
on the authorization level in database systems. In this way, it can be ensured that only the
authorized users can access to some specific section of data. Different users may have
different level of access rights there making the data secured in such way that the stored data
can be secured from different issues data leaks or misuse of data.
Disadvantages of Database system
Compared to the file based system different DBMS implementation projects cost
much higher than the file system
Complexity of the implementation and usability: DBMS are complex in nature and
new users may feel hard to determine the usability and functionality.
Performance of the databases: Most of the DBMS are generic in nature which
makes this solutions suitable for different applications. However in certain scenarios this
generic nature adversely affects the performance for the users.

3DATA MODELLING AND SQL LANGUAGE
Characteristics of the of Hierarchical, network and relational
database models
Hierarchical model
For hierarchical model, the data is stored in tree like structures where the parent child
relationship is maintained. In other words it can be stated that required data is saved in top
down or bottom up approach. Data stored in the database is represented in the form of parent-
child relationship. In this kind of database system parent can have multiple children, whereas
the children can have only one parent.
Network models
In case of the network database model, it allows the child to have multiple parents
while storing data in the databases. In this way, it helps the users in order to address the need
to model and support very complex relationships between the different database entities such
as the many-to-many relationships among the different related tables. In the Network based
model, the tables /entities are managed through the use of a graph that can be accessed for
data retrieval through the use of the several paths connecting to the parent and child nodes.
Relational database
The Relational database model is the most popular model that is used in the industry
or users. This database model is normalizes data in the forms of row (records) and columns
(Attributes) of the tables inside the database. In case of the Relational model the data is
stored in predetermined structures and manipulated through the use of the SQL or the
structured query language.

4DATA MODELLING AND SQL LANGUAGE
Application of different database models
The Hierarchical database model is helpful in assuring the very high performance
as well as high availability of the databases for the applications. Thus this kind of database
model is used in sectors such as telecommunication and banking.
The network based database model is useful in case of the organization that has
multiple branches/department at different places and may require access to different type of
data while restricting the access through the authorization levels. In this scenarios the
Network model is applicable. The network based database model contains collection of data
or records which are connected each other through the relationship links. Records in the
database developed with respect to the entity E-R model. Each of the records in the tables is a
collection of attributes. The record contains single data value. The relationships or the links
are relation between exact two records.
The relational database model are helpful in the management of the large volume of
data with multiple complex relations thus this model is utilized in large scale systems such as
the airline reservation system of banking application that requires storage of multiple
systems.

5DATA MODELLING AND SQL LANGUAGE
Bottom up and top down approach for database design
Top-down approach for designing database starts from the generalization and then
moves to specific. Thus the designer starts with general idea of what is required for the
proposed system. After this the designer queries end-user about the data they require to store
in the database.
The analyst or the designer then collaborates with users in order to decide what type
of data will be in the database. Through the use of this approach it requires analyst to have
detailed understanding of proposed system.
On the other hand top-down approach also have shortcomings. In some scenarios,
top-down design leads to unsatisfactory results for the proposed system.
On the Bottom-up approach, the starts with specific details and moves to the general
perspective. In order to begin with bottom-up design of the design of the database. The
system analyst needs to inspect the interfaces for the proposed system. The analyst/database
designer works backwards through system analysis in order to determine the data types and
fields have to be stored.
Task 2
Airport
Airlinename
Employee

6DATA MODELLING AND SQL LANGUAGE
Booking_status
Flight
Flight_Details
Passenger
Payment

7DATA MODELLING AND SQL LANGUAGE

8DATA MODELLING AND SQL LANGUAGE
Task 3
1. Normalization
The normalization process that theoretical basis for the designing and structuring
databases for any real life scenario. The objective of normalization process is to reduce data
redundancy in storing databases. In this way it is possible to avoid certain insertion, update,
deletion anomalies. This is desirable in case of any data model for any database design.
The update anomalies in the database makes the data modification in the database
operations much more time and storage consuming as well as error prone. If an user wants to
insert, update, or delete a record then the DBMS application or the application invoker have
to complete other extra works.
Such as for the booking table or payment method following is the normalization
method;
The un-normalized table of user table would look like the following;
The first normal form of the table is;
user_ID, name, user_phone, user_state, usr_country, booking id
2nd normal form
user_id, name, user_phn, booking_id
user_id, booking_id, user_state, usr_country;
3rd Normal form
User_id, booking_id, user_country;
user_id, name, user_phn;

9DATA MODELLING AND SQL LANGUAGE
user_id, name, user_phn, user_state, usr_country;

10DATA MODELLING AND SQL LANGUAGE
2 ERD after normalization

11DATA MODELLING AND SQL LANGUAGE

12DATA MODELLING AND SQL LANGUAGE
3. Logical relations
The passengerid in the booking table is the foreign key to the Passenger table.
Bookingid in the payment table establishes the relation between the tables Booking
and payment.
Task 4
DDL queries
CREATE TABLE Usertbl(userID INT AUTO_INCREMENT, Name VARCHAR(45),
lastName VARCHAR(45), email VARCHAR(45), age INT, PRIMARY KEY(userID) )
CREATE TABLE Airline ( airlineID INT AUTO_INCREMENT, name
VARCHAR(30), code VARCHAR(20), country VARCHAR(30), PRIMARY
KEY(airlineID) )
SET SQL_MODE='ALLOW_INVALID_DATES'
CREATE TABLE Flight ( flightID INT AUTO_INCREMENT, departureTime TIME
DEFAULT '00:00:00', departureDate DATE DEFAULT '0000-00-00', arrivalTime
TIME DEFAULT '00:00:00', arrivalDate DATE DEFAULT '0000-00-00', airlineID
INT, updatedAT TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP, PRIMARY

13DATA MODELLING AND SQL LANGUAGE
KEY(flightID), FOREIGN KEY(airlineID) REFERENCES airline(airlineID ) ON
DELETE CASCADE )
CREATE TABLE Booking ( BookingID INT AUTO_INCREMENT, flightID INT,
seatNum VARCHAR(30), class VARCHAR(30), userID INT, Destination
VARCHAR(30), Price INT, updatedAT TIMESTAMP NOT NULL ON UPDATE
CURRENT_TIMESTAMP, PRIMARY KEY(BookingID), FOREIGN KEY(flightID) REFERENCES
Flight(flightID) ON DELETE CASCADE, FOREIGN KEY(userID) REFERENCES
usertbl(userID) ON DELETE CASCADE )
CREATE TABLE emlpoyee ( StaffID INT, Name VARCHAR(50), Gender
VARCHAR(10), Address VARCHAR(75), Contact VARCHAR(15), PRIMARY KEY(StaffID)
)
CREATE TABLE Payment
( PaymentID INT, Name VARCHAR(50),
BookingID INT,
PaymentAmount INT,
PassengerName VARCHAR(15),
PaymentDate DATE,
PRIMARY KEY(PaymentID),
FOREIGN KEY(BookingID) REFERENCES booking(BookingD )
)
Data Insertion SQL’s
INSERT INTO `airline` (`airlineID`, `name`, `code`, `country`) VALUES
('1', 'NewAirline', 'NA012544', 'UK'), ('2', 'NewAirline', 'NA114422',
'UK'), ('3', 'NewAirline', 'NA123654', 'Australia'), ('4', 'NewAirline',
'NA0011', 'UK'), ('5', 'NewAirline', 'NA0114423', 'UK')

14DATA MODELLING AND SQL LANGUAGE
INSERT INTO `usertbl` (`userID`, `Name`, `lastName`, `email`, `age`)
VALUES ('1', 'Arelan', 'dorothy', '[e-mail]', '25'), ('2', 'Tina
', 'Ramirez ', '[e-mail]', '25'), ('3', 'Carol ', 'Moreno',
'[e-mail]', '26'), ('4', 'Mark ', 'Hansen ',
'[e-mail] ', '44'), ('5', 'Alice ', 'Nguyen ',
'[e-mail] ', '30')
INSERT INTO `flight` (`flightID`, `departureTime`, `departureDate`,
`arrivalTime`, `arrivalDate`, `airlineID`, `updatedAT`) VALUES ('2',
'13:08:00', '2019-07-10', '20:14:00', '2019-07-23', '2', '2019-07-17
03:08:00'), ('3', '21:14:00', '2019-07-24', '20:08:00', '2019-07-29', '5',
'2019-07-08 04:06:00'), ('5', '00:00:00.000000', '2019-07-25',
'00:00:00.000000', '2019-07-03', '4', '2019-07-07 04:17:00')
INSERT INTO `booking` (`BookingID`, `flightID`, `seatNum`, `class`, `userID`,
`Destination`, `Price`, `updatedAT`) VALUES ('1', '5', '33', 'Business', '2', 'CCU', '2500',
'2019-07-21 03:15:09'); INSERT INTO `booking` (`BookingID`, `flightID`, `seatNum`,
`class`, `userID`, `Destination`, `Price`, `updatedAT`) VALUES ('3', '1', '14', 'economy', '2',
'CA', '5400', '2019-07-16 04:11:11'), ('4', '3', '55', 'business', '2', 'CA', '8900', '2019-07-16
04:10:11');
INSERT INTO `payment` (`PaymentID`, `BookingID`, `PaymentAmount`,
`PassengerName`, `PaymentDate`) VALUES ('1', '2', '5000', 'Allen', '2019-07-16'), ('2', '3',
'8500', 'Josh', '2019-07-10'), ('3', '4', '7800', 'Chris', '2019-07-19');

15DATA MODELLING AND SQL LANGUAGE
Bibliography
Al-Barak, M. and Bahsoon, R., 2016, October. Database design debts through examining
schema evolution. In 2016 IEEE 8th International Workshop on Managing Technical Debt
(MTD) (pp. 17-23). IEEE.
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.
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.
Tomoda, A., Isoda, Y. and Ushijima, K., Hitachi Ltd, 2016. Database management system
and method. U.S. Patent Application 14/894,042.