Data Modeling and SQL Language for Airlines Project

This project report delves into the core concepts of database management systems (DBMS) with a focus on data modeling and SQL language. It begins by differentiating between file-based systems and DBMS, exploring the properties of network, relational, and hierarchical data models. The report then examines the top-down and bottom-up approaches to relational DBMS design, emphasizing the importance of conceptual and logical modeling. Conceptual modeling identifies entities such as passengers, employees, flights, and planes, while logical modeling involves normalization and the creation of an entity-relationship diagram (ERD). The project further explores physical modeling, including SQL commands for table creation, insertion, and modification. The report concludes with a comprehensive overview of the airline database design, providing a practical application of data modeling principles.

DATA MODELLING & SQL LANGUAGE

Table of content
INTRODUCTION...........................................................................................................................1
TASK 1............................................................................................................................................1
Database management system.........................................................................................................1
Differentiate between the file and database management system...............................................1
Properties Network, Relational and Hierarchical data models....................................................2
Bottom up and Top-down approach of relational DBMS design................................................3
TASK 2: CONCEPTUAL MODELLING.......................................................................................4
Possible entities...........................................................................................................................4
Conceptual data model for the New Airline database.................................................................4
TASK 3............................................................................................................................................5
LOGICAL MODELLING...............................................................................................................5
Normalization Method.................................................................................................................5
Entity relationship diagram..........................................................................................................7
TASK 4: PHYSICAL MODELLING...........................................................................................13
SQL Commands and database tables.........................................................................................13
CONCLUSION..............................................................................................................................16
REFERENCES..............................................................................................................................17

INTRODUCTION
A DBMS is a process that can gather and collect the information to store, retrieve and
update in the database system. It is used MySQL tool and platform to interface with the
information about database for storage and retrieval. This report will discuss about the
differentiation between the file base system and database. Furthermore, it will identify the
characteristics of data models. This documentation will discuss about the top down and bottom
up approach in relational database to examine the entire processing of DBMS. It is understanding
the entity relational model for establishing the relation between different entities. It also contains
the conceptual modelling to identify the entities and create the conceptual model. Moreover, this
report will identify the structure query statement that can use for designing table and columns in
the system.
TASK 1
Database management system
Differentiate between the file and database management system.
Database can be defined as a set of structure of data which is used to collect information
in a specified manner so that it can easily be accessed, updated, modified and retrieved. It makes
evaluation of information easy (Egea and Dania, 2019). There are various kinds of database
managed systems that are used to store information and manage different databases. Database
systems makes is much easier for the end user to update, create, read, delete data in a specified
manner such that other information stored within the database is not hampered. As per the case
study Jet Airlines wants to create dynamic relational DBMS in order to maintain the requirement
of their growing business.
Fine management system can be defined as an application which is used to store, arrange
as well as access information or files from storage locations. Each file within this file
management system manages as well as defines their own data or information (Ghali and
AbuNaser, 2019). There are various kinds of limitations because I this each and every user need
to maintain and create their own files which are run through a specific program. Due to which it
becomes difficult for the user to maintain, update, create or evaluate the information.
1

Properties Network, Relational and Hierarchical data models
Relational:
It is the most common model that help for sorting data into different tables and establish
relationship with one or more tables. It is mainly containing different rows or columns. Every
column has a contain the unique attributes (Odarchenko, Hassan and Zaman, 2019). This model
is mainly accounts for different types of relationship between tables such as one to one, one to
many and many to many etc.
Characteristics:
 Relational model is represented in the form of tables that contain specific values within
rows.
 It also supports the inapplicable and missing information in proper manner.
 It must be supports the logical and physical data dependence.
 Relational data model is also supports the independence of distribution the overall
management system.
Hierarchical
Hierarchical Model can organize information in the form of tree structure so that each
record has a contain the single parents. It is sorting in particular order and also used as physical
order for sorting the DBMS.
Characteristics:
 It is based on the structured data model and structured in the tree.
 It provides the navigation path to access the information through tree like structure.
 It is mainly used for the logical pointers.
Network
It is mainly builds on the hierarchical model that can build many to many relationships. It
is also used the mathematical concept to create an effective network system and also constructed
with set of related records.
Characteristics:
 It can be supports the many to many relationships.
 All the entities are shown in the form of connected network with each other.
 It always maintains high performance.
 Network model is powerful than the other models.
2

Bottom up and Top-down approach of relational DBMS design
It is emphasis on the high level of identification of different type of entities, relations and
attributes. It is mainly identifying the enterprises rules that help to use the appropriate database
object within domain and develop the conceptual model. This type approach can be utilising the
relational database models (Sharma and Gadia, 2019). When developing the database system so
that developer will start the process by modelling and planning strategy in proper manner. This
approach has an advantage when it provides the proper visibility for further changes. It is
consuming more time for collection of data because it requires for communication towards the
end users.
Bottom up approach is mainly concern on the low level elements of information like
functional dependency and attributes. Afterwards, it works towards the logical model of the
database system. Initially, it can start with the user requirement to collect all information and
then create proper documentation in proper manner (Abu Ghali and AbuNaser, 2019).
Normalization method can be used in the bottom up approach to find the similarities among the
database system for Airline company that would start by modelling of airline firm that contain
information about the employees, passenger, flight details.
3

TASK 2: CONCEPTUAL MODELLING
Possible entities
According to scenario, it found that there are different types of entities such as
 Passenger
 Employee
 Flight
 Plane
Conceptual data model for the New Airline database
Figure 1 Conceptual based data model
4

TASK 3
LOGICAL MODELLING
Normalization Method
Normalization is basically process of structuring the relation database system in proper manner
(Madhikerrni and Främling, 2019). It is a series of different technique that called normal form
and also minimize the data redundancy to improve the data integrity. It can be divided into three
different groups:
First Normal Form
A relation must be 1 NF when it contains the atomic values. It cannot hold the multiple
values in the table attributes. It only consists of single value attributes.
Second Normal Form
A 2NF must construct the 1NF because different type of non-key attributes is fully
functional dependent on unique or primary key.
Third Normal Form
It doesn’t contain any type of transitive partial dependency and also useful for reducing
the data duplication. In this way, it can easily achieve the data integrity. It doesn’t have any
5

transitive dependency in the prime attributes. It has been established the relation of third normal
form.
UNF 1NF 2NF 3NF Entities
Passenger
Information
Passenger_id
(PK)
Passenger_id
(PK)
Passenger_id
(PK)
Passenger
Passenger_name Passenger_id Flight_no Flight
Passenger
Candidate key Emp_name Flight_no Passenger_id Passenger
Flight
Non-prime
attributes
Emp_name Flight_no depend
on the
emp_email
because all
details are stored
on email.
Passenger_email
Passenger-name
Passenger_age
Passenger_phone
no.
Flight
Passenger
Table: 2
6

Entity relationship diagram
ER diagram is also known as ER model. It is basically type of structural diagram and useful for
database design. It is containing different group of entities and attributes to create relationship
between different column (Rabe and et.al., 2019). It is based on the higher level of conceptual
model that applicable on the real world objects. It can be analysed to identify the specific need or
requirement of data that relevant to the Airline Database. In this way, it can design in effective
manner.
Figure 2 ER Diagram
7

Data Dictionary
Entities Attributes
Passenger  Passenger_id (PK)
 Passenger_age
 Passenger_name
 Passenger_phone no
 Passenger_email
Employee  Emp_id (PK)
 Emp_age
 Emp_salary
 Emp_name
 Emp_phone no
 Qualification
 Flight_no (FK)
 Passenger_id (FK)
8

Plane  Plane_model (PK)
 Plane manufacture
 Plane_type
Flight  Flight_no (PK)
 Flight origin
 Departure time
 Flight_destination
 Arrival time
Table :1
In Passenger table:
Passenger_id is a primary key in the table that store the unique record in the rows and
also been created relationship with employee table. It can be shown as a foreign key in the
employee column.
Primary key- Passenger_id
Foreign key- Flight_no
In Flight table:
Flight_no is a primary key in the table that store the unique record in the table and also
develop the relationship with another plane database table. It is also shown as a foreign key in
the plane and also create relationship between them.
Primary key- Flight_no
In employee table:
Emp_id is a primary in the table which consists of unique records and does not contain
repetition. This table is stored the information plane and flight because these tables are used
primary key like a foreign key in the employee. Afterwards, it can be created the relationship
between one or more database tables.
9

Primary key- emp_id
Foreign key- plane model, flight_ no
Database tables
Employee Database table
10