Comprehensive Data Modeling and SQL Language Assignment: Part 1

This assignment provides a comprehensive overview of database concepts and SQL language. It begins by differentiating between file-based and database systems, followed by a discussion of hierarchical, network, and relational database models, including their characteristics and practical applications. The assignment then delves into database design approaches, contrasting top-down and bottom-up strategies. Conceptual modeling is explored through the identification of entities and the construction of a conceptual model using Crow's Foot notation. Logical modeling is addressed by explaining the principles of normalization (UNF, 1NF, 2NF, and 3NF) with examples and constructing an ERD. Finally, the assignment covers physical modeling by producing SQL scripts to create database tables in MySQL, including constraints, and populating the tables with data. The document showcases a complete understanding of the database design process from conceptualization to implementation.

Data Modeling & SQL Language: Part 1
1

Contents
INTRODUCTION...........................................................................................................................3
TASK 1............................................................................................................................................3
Understanding the database concepts..............................................................................................3
Differentiate between the database system and file bases system...............................................3
Table: 2........................................................................................................................................4
Discuss about the characteristics of hierarchical, network and relational database models and
also represent the practical application........................................................................................5
Describe top down and bottom up approaches to the relational database design and also
identify the strength or weakness of each approaches.................................................................6
TASK 2............................................................................................................................................6
Conceptual Modelling.....................................................................................................................6
Identify all necessary entities that help to find the problem in DBMS.......................................6
Construct the conceptual model of New Airline database and representing with the help of
Crow’s Foot notation. It shows all participation constraints and cardinality..............................7
TASK 3............................................................................................................................................7
Logical Modelling...........................................................................................................................7
Principles of Normalization process by using specific example and also demonstrate the
process with proper explanations from UNF, 2NF, 1NF and 3NF..............................................7
Construct the ERD with normalized entities that contain useful attributes...............................10
Mapping the ER model with the set of logical relation into the other relational data model....12
TASK 4..........................................................................................................................................14
Physical Modelling........................................................................................................................14
Produce a script by using appropriate SQL command to create database table by using MySQL
and also represent the constraints in the different table level....................................................14
Populate the database table........................................................................................................18
CONCLUSION..............................................................................................................................21
REFERENCES..............................................................................................................................22
2

INTRODUCTION
A database management system is a type of software that support for managing and creating.
It provides the systematic approach to design table and also performed different operations such
as retrieve, manage, update and create. This report will discuss about the conceptual frame of
database system to differentiate between the file based and database system. It will describe the
characteristics of hierarchical, relational and network model. Furthermore, this assignment will
discuss about the approaches that applicable during database design. Afterwards, it will discuss
about the normalization process that demonstrate in the different forms of database system.
TASK 1
Understanding the database concepts
Differentiate between the database system and file bases system.
File Management system Database Management system
 It is easier to store the file in the system
and requires less constraints or security.
 It has required the high security
constraints (Batra, 2018).
 Data Redundancy is higher in the File
system.
 It is less in the Database management
system.
 It is very hard to get the centralisation
in the file management system (Kolev
and et.al., 2016).
 It can be achieved in the centralisation
of database system.
 The security is low in the File
management system.
 The security is very high in Database
management system.
 File can be stored in the unstructured
ways where data is isolated.
 DBMS stores the data in structured
form that will define the interrelation
and constraints.
Table:1
3

Describe about the advantage or disadvantages.
Advantage Disadvantage
File Management System  Multiple user access
the data.
 Cost and time saving.
 Maintaining and
controlling the over the
files (Jukic, Vrbsky
and Nestorov, 2016).
 Lack of Storage and
standards
 Lack of transactions.
 Increases the data
Redundancy
Database Management System  Database system can
easily access the data
or information.
 It also preserved the
reliability of data and
ensure the ACID
properties.
It is expensive because
database requires the large
space for disk storage.
It has needed the extra cost for
maintain hardware system.
Table: 2
4

Discuss about the characteristics of hierarchical, network and relational database models and also
represent the practical application.
Hierarchical Model:
Hierarchical model is a tree like structure that can organize the data and information in
proper manner.
Characteristics of Hierarchical model
 It can be represented the data in the form of hierarchical tree.
 It has minimized the disk input as well as output in each device (Preidel, Daum and
Borrmann, 2017).
 This model will help for increasing the navigation process and easily access data on time.
Network Model
Network Model can be designed as flexible approach to represent the specific objects and
their relationship between them. It is a unique schema that can viewed as graph.
Characteristic of Network Model
 Each entity is connected as a form of network with each other.
 It supports the many to many relationships between them.
 It is maintaining the high performance and speed.
Relational Model
This model is based on approach for managing and controlling data by using
programming language (Borodin, Kiselev and Porshnev, 2016). It can design for the fast storage
and retrieval of the large amount of data or information.
Characteristic of Relational model
 It is avoiding the data redundancy in the system.
 It should be maintained the data integrity.
 This model is standard and portable for ensure that data must be transferred between
RDBMS.
5

Describe top down and bottom up approaches to the relational database design and also identify
the strength or weakness of each approaches.
Top down and bottom up approach are two different strategies of information process that
can include scientific theories and management system.
A Top-down approach is generally useful for breaking down the system to gain into the
different compositional sub system (Christian, 2017). It is formulating and specifying but no
dealing with the level subsystem. It easily specified the black boxes which easier to data
manipulation and also break down into the small segments.
A bottom-up approach is dividing the piece of system that can increase the complexity
and making the original sub systems. This approach is analysing the individual risk in the
process at lower level. It always forward looking to control and maintain the process in proper
manner.
As per scenario, it can use the Bottom up approach for developing the new system at low
level. Developer can understand the overall functionality of system
TASK 2
Conceptual Modelling
Identify all necessary entities that help to find the problem in DBMS.
 Plane
 Flight
 Passenger
 Employee
These are different types of entities exists in the database system and also creating a relationship
between each other.
6

Construct the conceptual model of New Airline database and representing with the help of
Crow’s Foot notation. It shows all participation constraints and cardinality.
Figure 1: Conceptual Model
.
TASK 3
Logical Modelling
Principles of Normalization process by using specific example and also demonstrate the process
with proper explanations from UNF, 2NF, 1NF and 3NF.
Normalization:
It is a type of technique and method for designing database system which can organise the tables
appropriate manner (Traub and et.al., 2017). It also reduced dependency and redundancy of
information or data. It can be divided into smaller tables and also link between them by using
relationship. There are different types of Normal forms useful in the database system.
First Normal Form
A relation will be one normal form that contain the specific values. It does not hold the
multi values and sonly contains single attributes.
7

For Example-
Second Normal Form
2NF must be consisting the 1NF and all type of non attributes are functional dependent in
the primary key.
For Example-
8

Third Normal Form
A relation in 3rd form that exists in 2NF and doesn’t have any transitive partial dependency. It
helps to reduce the data duplications and achieve the data integrity (Bashir and Hoque, 2016). In
case, if the transitive dependency for the non-prime attributes so that it has to be created third
normal form.
 Flight_no is the candidate key and other attributes are non-prime attributes.
For Example-
9

Normalization
UNF 1NF 2NF 3NF Entities
Flight
Information
Flight_no (PK) Flight_no (PK) Flight_no (PK) Flight
Flight Flight Flight
Passenger
Information
Passenger_id
(PK)
Passenger_id
(PK)
Passenger_id
(PK)
Passenger
Employee
information
Emp_id
(PK)
Emp_id (PK) Emp_id (PK) Employee
Table : 3
Construct the ERD with normalized entities that contain useful attributes.
Figure 2 ERD Model
Entity relationship diagram shows as the relationship of different set of entity that can store
specific attributes in the database system. It is an integral part of software system that can utilise
the ER Model and produce high quality of design. It provides the communication medium to
build a relationship for sharing information between them.
Entities Attributes Relationship
10

Flight Flight_no (PK)
Flight_origin
Flight_destination
Depature_time
Arrival_time
Airline_no (PK)
One to many
Plane Model (PK)
Manufacture
Type
Flight_no (FK)
One to one
Airline Airline_no (PK)
Airline_size
One to many
Employee Emp_id (PK)
Emp_name
Emp_address
Emp_salary
Emp_phone no
Emp_age
Qualification
Flight_no (PK)
Passenger_id (PK)
One to one
Passenger Passenger_id (PK)
Passenger_name
Passenger_email
Passenger_phone no
Passenger_age
Flight_no (FK)
One to one
Table: 4
11

Mapping the ER model with the set of logical relation into the other relational data model.
Figure 3 Relational Data Model
Data dictionary:
Employee
Column Type Null Default Comments
emp_id (Primary
) int(10) No
emp_name varchar(20
) No
emp_address varchar(20
) No
emp_salary int(20) No
emp_age int(10) No
emp_phone no int(10) No
qualification varchar(10
) No
flight_no int(10) No
12