New Airline Database: Data Modelling and SQL Language Report

This report provides a comprehensive overview of database systems, focusing on data modeling and SQL language. It begins by differentiating between database systems and file systems, highlighting their advantages and disadvantages. The report then explores various database models, including network, hierarchical, and relational models, emphasizing the relational model for its efficiency and security. It discusses both bottom-up and top-down approaches to relational database design, analyzing their strengths and weaknesses. A significant portion of the report is dedicated to the design of a New Airline database, including the identification of entities, attributes, and relationships through conceptual and physical modeling. The principles of normalization are explained with examples, covering UNF, 1NF, 2NF, and 3NF. Finally, the report concludes with SQL commands for creating database tables in MySQL, along with the application of constraints. The report also includes figures that show conceptual and relationship models and SQL commands.

Data Modelling & SQL Language
1

INTRODUCTION
A relational database is basically the set of data that can describe in the form of table which
can access in the different manner. It helps for recognise the database table effectively and
efficiently. The relational database model is based on the straight forward that can represent the
data in the form of tables. This report will discuss about the database system and also compare
with the file system. It will describe about the characteristics of network, relational and
hierarchical data model that applicable in the system for design purpose. Furthermore, this report
will discuss about the different types of approach that can use in the relational DBMS design and
also identify their strength and weakness. Moreover, it will design the conceptual and physical
modelling of New Airline database that help for identifying the entities, attributes and
relationship with the each other. It will discuss about the normalization process in DBMS that
can break the table into smaller formats. At last, it will Produce the SQL command to create the
database table by using the MySQL relational database.
TASK 1: Concept/ understand of database
Differentiate between the database system and file system. Discussion about the advantage and
disadvantage.
Database system File system
 Database system is organised the data
in the collection of large dataset that
can manage, access and update in
proper manner.
 This type of process is managing data
on the specific storage disk which
easily managed and accessed in
efficient manner.
 It always maintains the data and
information consistency.
 It has a more chances of high data
inconsistency.
 The structure of DBMS is complex.  The structure of file system is simple.
 It can easily share the data.  It is very hard to share data from one
place to another.
 It should be maintained the low
redundancy.
 It is higher redundancy.
Table: 1
Advantage and Disadvantage of File system and database system.
2

Relational database system File system
Advantage  It can share the data
from one platform to
another.
 It maintains the
duplication in the data.
 It is simple process for
easily handle the data
and information.
Disadvantage  Many relational
DBMS have limited
field length.
 It is very expensive to
maintain in the system.
 It is difficult to access
the data and
information.
 There is some problem
regarding security and
integrity.
Table : 2
Describe the characteristics of network, hierarchical and relational database model by using
practical approach.
In Database, Data model is based on the logical structure that can describe the overall
design which reflect on the entities, attributes and their relationship among constraints, data and
so on. There are different types of database model discuss as following:
Network data Model
This model is based on the network that can allows the different records and linked with
same type of files. It also seen as the form of tree where every branches are connected with each
other. The relationship between the network model define many to many because one file
connects with the many files.
3

Relational data Model
The relational model refers to the data manipulation and control the overall features that
require for managing the large number of information. It should provide the better way to
organize data in proper manner.
Hierarchical data Model
This model can have organised the data in the form of tree and formed the structure by
using child and parent’s relationship. An entities are either representing child or parents. This
model is maintaining the information in the form of records where they can identify the ranking
of entities. This type of model stores the information in logical manner.
As per scenario, developer must be used the Relational data model that help for develop or
design dynamic system. It is the most efficient that can maintain the security and privacy in
proper manner.
Discuss about the bottom-up, top-down approach that applicable in the relational database design
and also determine the weakness and strength.
Bottom-up approach
A bottom up approach is a process that useful for managing the complex system and also
making the sub system that easily emerge with the other. It mainly includes the design of the
fundamental part where they easily combined with the higher level and integrated with the sub
modules. Bottom up approach has performed the functions with the specific layer of functions.
Strength
 It useful for business to awareness about the product and handle the processing at early
stage.
 This approach is implementing on the password management of the large number of
users.
 There is possibility of less intrusion during the operations.
4

Weakness
 It might be change the structure of organization while immediately change will impact on
the processing.
Top-down approach
This approach will use for solving the complex structure into smaller ways. It easily
decomposed while understanding the different level of process. It starts with the bottom level
that refine the concrete level. This type of approach follows the step by step process for breaking
the large amount of data into simpler form. It must be controlled the approach in downward
directions.
Strength
 It only focused on the resources that help for managing the database application in proper
manner.
 It will have clear visibility of change in the system.
Weakness
 It should be providing the limited coverage in the starting phase.
 It will support the overall application but it will not realise an accurate benefits of the
solutions.
TASK 2: Conceptual Modelling
Determine the possible entities in the database design.
 Airline
 Employee
 Flight
 Passenger
 Plane
5

Design the conceptual data model of new airline database and identify all entities, attributes and
relationship between them.
Figure 1 Conceptual Model
6

TASK 3: Logical Modelling
Assess the principle of the normalization process that easily demonstrate with the help of
example.
Normalization:
It is a process that help for organising the data into the different table and also eliminate
the redundancy while increasing the integrity. On another way, it can be defined as the practical
approach to optimise the structure of table which accomplished as per the overall investigation of
different piece of information.
Show the explanation of UNF, 1NF, 2NF and 3NF.
TASK 4: Physical Modelling
Produce the SQL command to create the database table by using the MySQL relational database
and shown the constraints which applied on the column.
Figure 2 Relationship Model
7

SQL (Structural query language)
SQL stands for structured query language that can design and construct for managing the
relational database system.
Insert command:
8

9

Construct the database table by using the SQL statement.
CONCLUSION
As per discussion, it concluded that the database system is a type of software or application
that stores large number of data in the form of tabular format. It helps for recognise the database
table effectively and efficiently. The relational database model is based on the straight forward
that can represent the data in the form of tables.
It summarised that the database system which comparison over the file system and
understand about the characteristics of network, relational and hierarchical data model that
applicable in the system for design purpose. There are different types of approach that can use in
the relational DBMS design and also identify their strength and weakness. It is describing the
conceptual and physical modelling of New Airline database that help for identifying the entities,
attributes and relationship with the each other.
10

REFERENCES
Book and Journals
11