Data Modelling and SQL Language: Database Design Project

This project delves into the core concepts of data modeling and SQL language, encompassing two distinct projects. Project 1 focuses on understanding database systems, including DBMS, file-based systems, and different data models like hierarchical, network, and relational models. It explores the top-down approach for database design and applies it to a case study of a New Airlines, involving conceptual and logical modeling, and normalization. Project 2 centers on SQL, with a focus on data manipulation and database testing. The project demonstrates the importance of database testing and provides screenshots of the testing process on an employee database. The assignment covers various aspects of database design, including entities, relationships, and the application of normalization techniques to ensure data integrity. The project uses examples to illustrate the practical application of database concepts in a real-world scenario, providing a comprehensive understanding of data modeling and SQL.

Data Modelling and SQL
Language

TABLE OF CONTENTS
INTRODUCTION:..........................................................................................................................1
Project 1...........................................................................................................................................1
TASK 1 ...........................................................................................................................................1
Understanding of Database.....................................................................................................1
TASK 2 : CONCEPTUAL MODELLING......................................................................................7
1. Possible Entities..................................................................................................................7
2. Designing a conceptual data model for the database of New Airlines...............................8
TASK 3 : LOGICAL MODELLING...............................................................................................9
1. Normalisation.....................................................................................................................9
2. Enhanced ERD involving all normalised entities.............................................................12
3. Mapping of the ER model designed.................................................................................12
Project 2.........................................................................................................................................15
TASK 1..........................................................................................................................................15
Structured query language....................................................................................................15
TASK 2 : DATA MANIPULATION............................................................................................16
TASK 3 : DATABASE TESTING................................................................................................17
1. Database testing and its importance.................................................................................17
2. Screenshot of the testing process on Employee database.................................................18
CONCLUSION:.............................................................................................................................20
REFERENCES:.............................................................................................................................21

INTRODUCTION:
It is the responsibility of organisations to maintain the database of their customers. It is
maintaining all the records of each customer. This is useful in retaining clients and improving
services. Moreover, a database is kept to store data in systematic way. Therefore, companies are
able to deal with customers at right time (Storey, and Song, , 2017) Also, database provide
security to data from been corrupt. In this case data can again be retrieved from database.
Besides database is very helpful in fulfilling the demands of customers. By tracking the past data
analyses can be made. Data model defines the logical structure of database. It shows that how
data will flow in Database management system (DBMS). Data model is of two types that is
entity relationship and relational model. Moreover, Structured query language (SQL) statements
made arranges the data in specific manner. This report will show how database is maintained in
enterprise with different systems.
Project 1
TASK 1
Understanding of Database
1) A database management system is a software that manages data in systematic way. It means
that data can be retrieve, updated and created with the help of database. It makes it easier for
employees to read, write, delete, etc. data in quick time. Database serves as an interface between
user and application program (Jukic, Vrbsky,. and Nestorov,, 2016). A DBMS manages three
things:- data, database engine and database schema. The advantages of DBMS are:-
 Controlling redundancy- A database avoids redundancy of data. It means that multiple
data can not be stored in it. This results in wastage of storage space.
 Integrity can be enforced- It means that data stored in database is always accurate. This
is done by maintaining some integrity data constrains in the database.
 Providing backup and recovery- In any case of hardware or software failures the data
can be lost so a database always provides a backup and recovery of data.
Disadvantages:-
Cost- The cost of installing and maintaining a database software is very high. Also , it requires
some additional hardware components to be installed.
1

Complexity- It becomes very complex to use a database software.
File based database systems is collection of application programs that access information of each
program or file. All files are grouped together based on their categories. Each file must be having
related information (Bello, and et..al., 2017). Its limitation was duplication of data and
incompatibility of files. But on other hand its the advantage of file based database system are:-
 Back up-It provides back up of data stored in files.
 Editing- data can be edited using specific editing software
 Sharing- It is easy to share files among multiple users at same time.
Disadvantages:-
 Data inconsistency- It is possible that data may not be in consistent state.
 Data redundancy- A file based system may store same information again. It may lead to
data redundancy.
 Limited data sharing- As data is stores in different files it allows limited sharing of data
between users or departments.
Example – A payroll system is an example of top down approach in which entire details of
person is entered. It contains a pay period object that shows per day pay and working hours of
employees is entered in it.
2) A hierarchical data model organises data into a tree form structure. It means each record is
having a single parent. It is very useful in describing relationships. It is having one to many
relationships nodes with each other.
2

Characteristics of model – Its main feature is the tree like structure provides flexibility to
company. For example- if database is made for team and its members then it becomes easy to
define parent child relationship.
 One to many- in this one parent can consist of many children. It becomes easy to work.
 Navigation- The model path is limited by predetermined structures. To access a record
query moves in downward direction. It is easy when the location of data is known.
 Logical parent pointers- In this new database have to be set up for many to many
relationships.
Network model- A network model is built on hierarchical model where relationships are tied
with many to many nodes (Balan, and et..al, 2016). It is structured in such a way that sets are
related to each other. Each set consists of one parent record and one child. A record can be a
member or child in multiple sets.
Characteristics of model- It is a very simple and easily understandable model. Also, it is very
easy to design and implement.
Ease of data access- It is very easy to access the data in this model. It is much flexible than
hierarchical model.
3
Illustration 1: Hierarchical Data model

Data integrity- It does not allow a member to exits without a parent.
A relational database model sorts data into tables known as relations. A table is having column
and row, each column contains an attribute such as price, date of birth, etc. The collection of
attributes is called domain. In this table a specific attribute is chosen as primary key that can used
in another table as foreign key (Rajakumari, and Nalini, 2014). Each row is called as tuple that
includes data of specific entity.
Characteristics of model:-
Use of keys- In this each data row is given a unique key called primary key. With this data in
different tables can be linked. Also, primary key can be used to link two rows.
4
Illustration 2: Network model

Data redundancy- It avoids data redundancy as an username can only be inserted once at one
location. This avoids same data to be stored at multiple locations.
SQL- It uses SQL queries to perform the operations like storing , updating and deleting data.
SQL is relatively easy to understand and allows some advanced database operations.
3) Top down approach- It is designed in for big organisation where there are many departments.
In this the requirements are identified and accordingly database design is developed. Here, large
number of entities exists and relationship between them is identified (Linoff,, 2015). It is a very
complex database that contains a huge volume of data.
Strengths:-
 There is full visibility of what effect it can have with any changes implemented in it.
 There is less redundancy of data
 The requirements are specific
5
Illustration 3: Relational model

 A high level view of all components.
 It is very easy approach for big organisation to implement
Weakness:-
 It requires a lot of time to provide data from top to bottom
 It requires a lot of communication between designer and end user database.
Bottom up approach- It is the reverse of top down approach in which data is stored from
bottom. It moves upwards . In this firstly data elements are identified and then grouped together
in sets. These data elements are called attributes that are grouped to form entities.
6
Illustration 4: Top down approach
Illustration 5: Bottom up approach

Strengths-
 It can be quickly adjusted into the business operations.
 Modules are designed earlier that helps in easy designing and implementing of database.
Weakness-
 Change implemented affects the entire database to a great extent.
 It requires a lot of time to find out the requirements needed to be changed.
New Airline must use top down approach as it consists many departments. Also, it is an
international airline that operates globally (Rajakumari,. and Nalini, 2014). For example the
detail of customer will be accesses in quick time. It will give details such as address, contact
number, etc. of customer. In this data can be stored under different categories. With this it will be
easy for New airline to get access of data quickly.
TASK 2 : CONCEPTUAL MODELLING
1. Possible Entities
New Airlines is an international airline company, they want to develop a database so that
they can have an efficient set of operations that can also help in coping with the competitiveness
in the market place. There are various entities involved in the scenario and they are listed as
below :
Employees
The entity employee consists of all the factors that are in association with them such as
the details of all the employees working in the New Airlines, also, their qualification that what
kind of plane they can fly etc.
Plane ; Flights
Plane are considered as the second entity and the database must be including the model
number of each plane along with the manufacturer of the plane. The airlines also involves the
information about the flight number, destination, departure and the arrival time.
Passengers
7

Passengers are also considered as one of the important entity here because the flights and
the airline work because of them only. The database must be including the information of all the
passengers which will be including their name, address etc.
2. Designing a conceptual data model for the database of New Airlines
8

TASK 3 : LOGICAL MODELLING
1. Normalisation
Normalisation is considered as a process of maintaining all the information and data in
order to avoid some factors such as data redundancy, different anomalies such as insertion,
updation, deletion etc. When the database is not being normalised, there is a possibility that these
anomalies occur. So, normalisation of the database is very important. Same will be done in case
of the database that is being developed for New Airlines. The database has to be properly
normalised in order to avoid any anomaly. Various types of normalisation is discussed as under :
Although as per the requirement of the database of the New Airlines as it involves the
information and record of passengers, flights, departure, employees and air crafts, but taking an
example of the record of planes to determine the normalisation.
Un normalised form (UNF)
In case of New Airlines, if we have to produce the un normalised form, that is we have to
keep it in UNF form, we have to list all the attributes that are part of the entity. Also, it is
important to identify the main key and the attributes that are repeating.
Plane-Id Model Number Manufacturer
1 14589 GETA, YELA
2 78956 TERA
3 79654 KIOSK
This is a simple example of the table in UNF.
First normal form (1NF)
The first normal form is considered as an effective form because it excludes all the
repeating groups. There are some rules that are being applied in this normalisation and these are
that all the columns should have different names, the order in which data has been entered does
not matter. Therefore, the same has been applied here as well. But there are some cases where
repeating groups has to occur, so one of them is shifted to another table.
9

TABLE_PLANE
Plane-Id Model Number Manufacturer
1 14589 GETA, YELA
2 78956 TERA
3 79654 KIOSK
As the following table is in UNF and the record is repetitive, so for normalising it in 1NF,
shifting the other element in different table as below :
PLANE_ModelNumber
Plane-Id Model Number
1 14589
2 78956
3 79654
PLANE_Manufacturer
Plane-Id Manufacturer
1 GETA
1 YELA
2 TERA
3 KIOSK
Second normal form (2NF)
A table is considered in 2NF when the table is already in 1NF and also the attribute that is
not contained in any candidate key relies on the proper subset of any possible candidate key
within the table. It considers the basic rules that the table should already be in 1NF and there
must not be any sort of partial dependency as well. It has been achieved in the table below.
Taking an example of table that is already in 1NF, so that it can be easy to normalise in 2NF.
10