Database Management Systems and Applications

This assignment delves into the world of database management systems (DBMS), examining various types of databases employed by organizations based on size and nature. It discusses how DBMS facilitate systematic data storage, retrieval, updating, and creation, enhancing employee efficiency in handling data. The report also touches upon the impact of constraints within databases, highlighting their role in ensuring data integrity.

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

Plane-ID Stock-ID Manufacturer
1 1 GETA
2 4 TERA
3 6 META
Normalising it in 2NF as under :
Plane-ID Stock-ID
1 1
2 4
3 6
Stock-ID Manufacturer
1 GETA
4 TERA
6 META
Third normal form (3NF)
A table is considered to be in 3NF when it is already present in 2NF. Also, the transitive
functional dependency on any super key present must be excluded. There are some rules that are
to be followed in 3NF normalisation and it involves that the table should be in 2NF to be in 3NF.
Also, there must not be any transitive dependency. These rules have been followed in the table
below.
For normalising a table in 3NF, it is important that it should be in 2NF.
11

Plane-ID Stock-ID Plane type Price
1 1 Single engine piston $78.33
2 4 Business Jet $65.14
3 2 Tilt-rotors $45.32
4 3 Helicopter $78.65
So, after normalising in 3NF, the table will be represented as under :
Plane-ID Stock-ID Price
1 1 $78.33
2 4 $65.14
3 2 $45.32
4 3 $78.65
Stock-ID Plane type
1 Single engine piston
4 Business Jet
2 Tilt-rotors
3 Helicopter
2. Enhanced ERD involving all normalised entities
Enhanced entity relationship diagrams (EERD) are specialised helps in providing the
relationship of all the entities that are associated within the database of the New Airlines. EERD
diagrams helps in modelling the database more effectively and appropriate. The ERD has been
represented as below :
12

This is the specialised enhanced entity relationship diagram which is involving all the normalised
entities that are necessary to be involved in the database in New Airlines.
3. Mapping of the ER model designed
On the basis of the enhanced relationship diagram made, the initial relational schema can
be made as under :
Passengers
13

Passengers(pname, paddress, pflightinformation, pidentification number)
Flights
14

Flights(Forigin, Fflightnumber, Farrivaltime, Fdestination, Fdeparturetime)
Employees
15

Employees(Equalification, Edetailsofflying)
Aircrafts
16

Aircrafts(Amodelnumber, Amanufacturer, Aprice, Aplaneid)
Departure
Departure(Ddepaturetime, Ddeparturedate)
TASK 4
1. Producing a script using appropriate SQL commands
Passenger
create table Passenger (
Name char (20) NOT NULL,
address varchar (20) NOT NULL,
FlightInformation varchar (30) NOT NULL,
IdentificationNumber double (20) UNIQUE
);
17

Flight
Create table Flight (
origin varchar (20),
destination varchar (20),
arrivaltime double (20),
FlightNo varchar (25) UNIQUE,
departuretime double (20)
);
Employee
Create table Employee (
qualification varchar (20) NOT NULL,
DetailsOfPlane varchar (20) NOT NULL
);
Aircraft
Create table AirCrafts (
ModelNo varchar (20) UNIQUE,
18

Manufacturer varchar (20) NOT NULL,
Price float (25) NOT NULL,
PlaneId varchar (20) UNIQUE
);
Departure
Create table Departure (
Deptime double (30) NOT NULL,
Depdate varchar (20) NOT NULL
);
2. Populating the tables
1. Passengers
create table Passenger (
Name char (20) NOT NULL,
address varchar (20) NOT NULL,
FlightInformation varchar (30) NOT NULL,
IdentificationNumber double (20) UNIQUE
);
INSERT INTO Passenger (Name, address, FlightInformation,IdentificationNumber)
VALUES ('SHAWN', '12Wallstreet', '14E', 'OP894');
select * from Passenger
19

2. Flight
Create table Flight (
origin varchar (20),
destination varchar (20),
arrivaltime double (20),
FlightNo varchar (25) UNIQUE,
departuretime double (20)
);
Insert into Flight (origin, destination, arrivaltime, FlightNo, departuretime)
Values ('UK', 'Zimbawe', 6:20, '34ED', 7:30);
select * from Flight
3. Employees
Employee
Create table Employee (
qualification varchar (20) NOT NULL,
DetailsOfPlane varchar (20) NOT NULL
);
Insert into Employee (qualification, DetailsOfPlane)
Values ('MES','BE23');
select * from Employee
4. Aircrafts
Aircraft
Create table AirCrafts (
ModelNo varchar (20) UNIQUE,
Manufacturer varchar (20) NOT NULL,
Price float (25) NOT NULL,
20

PlaneId varchar (20) UNIQUE
);
Insert into AirCrafts (ModelNo, Manufacturer, Price, PlaneId)
Values ('24RF', 'DELPI', '2500$', '56FRT');
select * from AirCrafts
5. Departure
Departure
Create table Departure (
Deptime double (30) NOT NULL,
Depdate varchar (20) NOT NULL
);
Insert into Departure (Deptime, Depdate)
Values (2:30, 11March);
select * from Departure
Project 2
TASK 1
Structured query language
1) Data manipulation language (DML) statements
DML statements – These statements deal with manipulation of data that is presented in database.
It generally includes most of SQL statements. They help in managing data in database. They are
not auto committed. It is used to insert, retrieve and modify contents of database. Also, it helps in
retrieving data from database. The examples of DML statements are SELECT, WRITE, INPUT,
etc.
a) Update- It is used to change or update existing data in a table
Syntax - UPDATE
[ WITH <common_table_expression> [...n] ]
UPDATE
[ TOP ( expression ) [ PERCENT ] ]
21

{ { table_alias | <object> | rowset_function_limited
[ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
}
table_variable
}
SET
{ column_name = { expression | DEFAULT | NULL }
{ udt_column_name.{ { property_name = expression
field_name = expression }
method_name (argument [ ,...n ] )
}
b) Delete- This statement is used for deleting one or more rows from a table.
Syntax- DELETE
[ TOP (expression) [PERCENT] ]
[FROM]
{{ table_alias
<object>
row set_function_limited
[ WITH (table_hint_limited [...n] ) ] }
}
22

2) Data control language (DCL) statements
DCL statements – It is control programming language that is similar to access and control data in
database. They are components of SQL language. They create roles, permissions, etc. to secure
database from unauthorised access. It only contains three commands that are GRANT, DENY
AND REVOKE.
a) Grant- It is used to provide access to any user for the database
Syntax – GRANT [privilege]
ON [object]
TO [user]
Example- GRANT ALL ON employee
To ABC;
[WITH GRANT OPTION]
b) Revoke- It is used to take back permission from user.
Syntax - REVOKE [privilege]
ON [object]
TO [user]
Example- REVOKE UPDATE
ON employee
FROM ABC;
TASK 2 : DATA MANIPULATION
1.
Create table Employee (
birthdate varchar (20),
23

firstname varchar (20),
fromdate varchar (25),
todate varchar (20),
);
SELECT first_name, last_name, gender, salary
FROM Employees
2.
Create table Employee (
birthdate varchar (20),
firstname varchar (20),
fromdate varchar (25),
todate varchar (20),
);
Select * from Employees
3.
Select name, gender
from Employees where department == d004.
4. Select dept, dept_manager
from Department
5. Select * from Department
where hire_date >= 1986.
6. Update Employees
SET birth_date = Value1
7. Delete * from Employee
24

where dept_no == d004
and ID == 10003.
8. Select emp_name, emp_dept, emp_salary
from Employees
9. Select * from Employees
where 1980<=hire_date>=1990
10. Select salary from Employees
where hire_date >=1990
TASK 3 : DATABASE TESTING
1. Database testing and its importance
Database testing is considered as a structured process which involves some layers such as
the data access and the business layer along with the user interface and the database itself. Every
factor has their own functionality. The use interface layer is associated with the interface
designing of the database whereas the business layer is responsible for supporting different
business strategies. It is a very important process because this checks whether the values that
have been retrieved in the database are accurate or not. The tester should have effciient
knowledge of SQL. Database testing is essential as the information and data present in the
database is a very important asset, so should be appropriate. Also, in RDBMS, the functionality
that is implemented is the mission critical business functionality. There are various approaches
that helps in checking the efficiency of data but they cannot be considered as efficient, that is
why there is a huge need of the database testing.
2. Screenshot of the testing process on Employee database
While creating the database for checking variosu factors, some screenshots are
represented as under :
25

Field size validation
This example can be considered as efficient enough for representing field size validation because
it involves a set of rules such as for the key Gender, it asks whether the person is male or female.
Similarly, in the case of date, it is optional whether one wants to put themselves or choose from
the calendar. So, these rules provide an efficient reflection of the field size validation.
Referential integrity
Referential integrity is considered as a concept of relational database which considers the
fact that the relationship between the table should remain consistent. Such as in the table,
dept_no is a primary key and dept_name is a foreign key. So, the dept_name agrees with the
dept_no. It means it is referenced by the foreign key which demonstrates the referential integrity.
26

Null value
It can be represented as an efficient example of null value because in this database, the
third row demonstrates a missing value, so this represents a null value. In the database itself, the
value behind this column will be stored as zero.
Check constraints
Check constraints are used to set some sort of limitation in the specific query or table. A
very common example of this can be considered as that if in the command, the date has been set
on a maximum value of >= 25/2/2017, in that case, the table will not receive any value after that.
So, it helps in checking the constraints in a database.
27

CONCLUSION:
From this report it is concluded that there are different databases used in organisation.
These database helps in storing, updating, retrieving data. Also, the type of database depends
upon the size and nature of organisation. 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
28

REFERENCES:
Books and Journals:
Balan, and et..al, 2016. Modeling and code generation for SQL-based data transformations.
U.S. Patent 9,372,671.
Bello, and et..al., 2017. A University Examination Web Application Based on Linear-Sequential
Life Cycle Model.
Freitas and et..al., 2015. Modelling solar potential in the urban environment: State-of-the-art
review. Renewable and Sustainable Energy Reviews.41. pp.915-931.
Jukic, N., Vrbsky, S. and Nestorov, S., 2016. Database systems: Introduction to databases and
data warehouses. Prospect Press.
Linoff, G.S., 2015. Data analysis using SQL and Excel. John Wiley & Sons.
Rajakumari, S.B. and Nalini, C., 2014. An efficient cost model for data storage with horizontal
layout in the cloud. Indian Journal of Science and Technology.7(3S). pp.45-46.
Rajakumari, S.B. and Nalini, C., 2014. An efficient data mining dataset preparation using
aggregation in relational database. Indian Journal of Science and Technology.7(S5). pp.44-
46.
Steinberg, A., Facebook Inc, 2014. Systems and methods for implementation of a structured
query language interface in a distributed database environment. U.S. Patent 8,832,556.
Storey, V.C. and Song, I.Y., 2017. Big data technologies and Management: What conceptual
modeling can do. Data & Knowledge Engineering.108. pp.50-67.
29