Database Management Systems

This report analyzes the different types of database models and produces a design for a relational database management system. It also develops a fully functional system using SQL source language and tests its functionality and performance. Tools available in the system to monitor and optimize system performance and manage security and authorization are also discussed.

Database Management Systems
Tutor: Koushik Modak
Student: Alexandrina Crigan
Student LON :19003058
1

Contents
INTRODUCTION...........................................................................................................................3
P1 Compare and contrast the different types of database models...............................................3
P2 Produce a design for a relational database management system to meet client requirements
.....................................................................................................................................................6
P3 Develop a fully functional system which meets client and system requirements, using SQL
source language...........................................................................................................................9
P4 Test system for its functionality and performance...............................................................12
P5 Tools available in system to monitor and optimise system performance and examine audit
logs.............................................................................................................................................14
P6 Tools available in system to manage security and authorisation.........................................15
CONCLUSION..............................................................................................................................16
REFERENCES..............................................................................................................................17
2

INTRODUCTION
Database is collection of data or information from different sources, organised into
structured forms. In this report, it has been chosen the scenario of database system of M&S
enterprise, which is basically stored or collected large amount of information. The scenario is
related the employment where consideration of departments, employees, manager, salaries and
titles tables. This will help for establishing a strong relationship between entities and also shows
as attributes of every entities in database development.
This report will analyse the system and user requirement for M&S, involves the
functionality of database system. It will discuss the compare and contrast between the different
types of database models such as network, relational, hierarchical and object oriented. It will
produce the effective design or relational DB system to meet the specific need or requirement for
clients. Moreover, fully functional system which meets client, system requirements. However,
perform the testing to check the overall performance and functionality of database system.
P1 Compare and contrast the different types of database models.
Relational Model Network Model Hierarchical
Model
Object oriented
Definition The relational
model is basically
represents how
data or
information is
stored in the
relational
databases. A
relational database
can be stored or
collected large
Network model is
based on the
database model
that conceived as
flexible way to
represents objects
and their
relationships
(Bulysheva,
Bulyshev, and
Kataev, 2017).
A hierarchical
model is basically
represents the data
or information in
the form of tree-
like structure.
There is single
parents for each
records. In order to
maintain the
specific sequence
Object oriented
model is based
on the
approach that
can represent
the model of an
application and
use at begins of
software life
cycle.
3

amount of data in
the form of
relations table
(Bulysheva,
Bulyshev, and
Kataev, 2017).
Another way, it
can be defined as
collection of
records connected
to one to another
through links.
and always keeps
sibling the nodes
into a proper
recoded manner.
History of
development
Relational model
was developed in
1970 as Codd
developed the
model. In 1981, it
was completely
established as
revolutionized
data management.
The network
model was
originally
developed by
Charles Bachman.
In 1969, it has
been conducted
the conference on
the basis of data
systems
Consortium and
developed as
network model.
Hierarchical model
was developed by
IBM in 1960s.
Basically, it can
use in early stage
of mainframe
DBMS. It is the
best way to
represent the
flexibility because
relationship is
confined to
different
relationship
(Bulysheva,
Bulyshev, and
Kataev, 2017).
OOS model is
based on the
programming
language and
developed by
1966, while
launching an
early
application of
Oops.
Features The features of
relational model is
that represent
The feature of
network model is
to create schemas,
It should consist of
different
components such
There are
different
features of
4

unique name
which would be
separated from
other relations.
Removing
duplication in
data.
viewed as graph
in which object
types are nodes,
as sender, receiver
and carrier. In this
way, it is directly
established the
relationship
between one node
to another.
object oriented
model such as
relationships,
abstractions,
encapsulations
etc.
Applications Application of
relational model is
to store or record
large collection of
information
regarding college
management
system.
Application of
network model is
to store or record
large collection of
information
regarding college
management
system.
Application of
hierarchical model
is to store or record
large collection of
information
regarding college
management
system and also
representing the
information in
hierarchical forms.
Object oriented
model is
basically used
to application
where
designing a
software,
including
programming
codes.
Advantage The advantage of
relational model is
their simplicity,
structural
independence,
easy to perform
query capability,
scalability etc.
Network model is
become easier to
handle the
coordination
between entire
system processes.
It provide the
high level of
flexibility.
The advantage of
hierarchical model
is represents as
clear chain of
command.
In order to
maintain or clearer
the path of
advancement.
It helps to
utilize the
redundancy
and re0usable
the code in
multiple times.
5

Network model is
becoming cost
efficiency.
Disadvantage Sometimes, it is
represented the
complexity as
large amount of
data grows and
relations between
piece of data
which become
more complicated.
Disadvantage of
network model is
becoming more
complicated than
other hierarchical
model
It is a complex
which require
physical
representation of
database.
Lack of structure
independence
Many too many
relationship is not
supported.
This is
unfamiliar and
shortage of
experienced
programmers.
Show as low
dependency
when dealing
with large data
or information.
Table: 1
6

P2 Produce a design for a relational database management system to meet client requirements
Relationship Diagram
Figure 1 Entity Relationship Diagram
Entity relationship diargam- ER model is based on the entity relationship model, which is
mainly define as high level data model. This can be used to represent the importance of data
elements and relationship for specified system. usually, A database developer can use the
conceptual design for database but it needed to represent the actual performance or workflow.
This can be identified the graphical representation through ER model. It is one of the simples
method to design view of data or information in proper manner.
Entities- customer, employees, supplier, products etc. these are different entities in which
represents the specific attributes. In order to establish a relationship between them. At some
point, it is also expressing the importace or significant of ER model to define primary or foreign
key into each tables.
7

Data Validation technique or method-
In the database develoment, A developer is mainly focused on the define an appropriate
input data on the basis of need or requirement. This will help for identifying any kind of errors
when user can input invalid data or information (Bulysheva, Bulyshev, and Kataev, 2017). In
this way, it is important to use data validation technique to check availability of information in
proper manner.
By using data validation, it has been identified the restriction on the specific data type.
Developer can handle the current data formation and also fix or setup data type. Usually, it must
have follows the different data processing. In case, if specific rules are fixed at the time of
8

database table creation and define attributes in proper manner. It cannot be defined as numerical
value when user will not able to mislead system.
Database system needs to be fixed suitable restriction property, which applicable in the
development of database. M&S is type of online business which is always trying to empty the
attribute whereas they will show as error message. This technique is useful for handle the data
mis-match situation. In context of security issues, it is important to use as sequential process to
check the accuracy of data or information within database tables.
9

System Requirements-
In database, it is an essential for representing the actual need or requirement for system.
therefore, M&S has been used the new database system while update the all information ans stort
the load of data processing.
Within hardware system, it must require to install the large amount of capacity because it is
helping to resolve issue or problem regarding loads on the system. Sometimes, it is also
preventing to increase the efficiency or performance.
User Requriements-
A new database system is becoming reliable so that M&S can use themselves to record
all information about the employees, departments. In this way, it become easier for organization
to access information of employees within RDBMS. Sometimes, it can be updated or maintained
according to the demand. Database system should become compatible with different operating
system. it means that can easily fulfill all sort of restriction in database.
10

11

Validate User interface-
12

Whenever, it has been produced the database in which requires for evaluating the significant
procedures of development. In M&S, database developer has been created an appropriate
documentation in order to define the specific need or requirement for new database system.
that’s why, database developer is basically focused on the logical data model which is mainly
defined the actual flow of data or information. Documentation should consist of both hardware
as well as software requirements. In this way, it become easier to implement suitable tasks,
represents as more clarity about the information of consumers, employee and products. In
accordance with the need of database so that follows the testing procedures. This can help for
identifying any kind of errors during task executions in database system (Bulysheva, Bulyshev,
and Kataev, 2017). Usually, developer has made up suiable plan which are becoming similar to
the real time. In order to improve the efficiency, performance and accuracy in the database
system.
On the other hand, the effectivness of database can be measured through testing in which
check the availability of data. Sometimes, database transaction related activities performed in
order to check the consistency, isolation, durability as well as atomocity of database system.
P3 Develop a fully functional system which meets client and system requirements, using SQL
source language
Structure Query Language-
SQL standards for structure query language which is mainly used for storing or collecting
large amount of data or information in Relational database management system. By using SQL,
there are different tasks perform in data operations within RDBMS (Bulysheva, Bulyshev, and
Kataev, 2017). Usually, SQL can be defined in the different ways to manipulate large amount of
data or stored in RDBMS. Structure query language can be divided into different commands
such as DDL, DML, DCL
 Data Definition Language (DDL) - this type of command is basically used to change the
structure of database tables such as creation, altering table, deleting and modifying etc.
13

DDL commands are used as auto-committed, it means that can easily save any kind of
changes permanently in the relational database.
 Data Manipulation Language (DML) - this type command is basically used for
manipulating the large amount of stored data in table. Usually, it involves insert, update,
merge, delete etc.
Database tables-
Customer table
Employee table
Order table
14

Product table
Supplier table
15

Steps to generate database-
16

17

18

19

20

SQL queries-
SELECT Supplier.[supplier_id], Supplier.[supplier_name], Supplier.[supplier_PhoneNo],
Supplier.[supplier_email]
FROM Supplier;
SELECT employees.[employee_id], employees.[employee_name], employees.
[employee_PhoneNo], employees.[employee_email], employees.[customer_id]
FROM employees;
SELECT order.[order_id], order.[order_date], order.[order_status]
FROM [order];
SELECT Product.[product_id], Product.[product_name], Product.[product_price], Product.
[product_quantity]
FROM Product;
SELECT Supplier.[supplier_id], Supplier.[supplier_name], Supplier.[supplier_PhoneNo],
Supplier.[supplier_email]
FROM Supplier;
Whenever, it has been designed the database system which is possible for user to identify the
different categories of data records. Usually, it consists of different information whereas
employees of M&S to access information at any time. Each and every records are contained the
information such as ID, name, email etc. these are consider the essential aspects to establish a
relationship between one or more database tables. For instance, it is possible to examine the user
to access full information of any kind of employees.
21

Design View of Database tables-
22

23

In additional, an authorized person such as Database administrator provide the privilege of other
users such as employees. Where they can access necessary information and do some major
modification, delete or update data. At the time of database development, Developer is basically
used as normalization process which allows for user to avoid any kind of duplication. On the
basis of evaluation, it has been represented the effectiveness of different parameters such as data
integrity, validation and other kind of testing procedures. M&S has ensured that database system
is basically accessible and always support for different platform such as mobile phone, desktop
24

version. As per organized the feedback and detailed about the users which may require to interact
or communicate with different database system.
Suggested the better improvement when it can be developed database for M&S-
M&S has been tried to improve the effectiveness of database system as follows:-
In future, it is an essential aspect for database that must need to ensure that uses appropriate
memory space, disk space and usage a latest version of hardware system.
Database administrator in M&S is always focused on the monitor or track the actual
memory level, because a database has been required large amount of space so that it can easily
store, collect large information. In this way, it has been suggested to use an appropriate database
management system which is always support for executing the different tasks.
25

Database will be monitored the entire activities of system in order to manage or control the
regulating process of any authorized person. In context of security aspect, it is consider as
essential part to monitor the different activities in detailed manner. On the other hand, it is also
noticed the security breach that will be notice at time of any loss. Therefore, it can easily
prevented or handled in proper manner.
Storing the large information about the consumers, who want to purchase items from M&S
but it is an essential needs to be accessed the database system. Company will provide the
facilities for both employees and consumers. This type of approach is supporting to prevent the
duplication as well as fragmentation. As per the implementation of consumer view, M&S can be
merged with large amount of dataset, match with all requirement for consumers and organize
data into structured forms.
P4 Test system for its functionality and performance
It is necessary to tests the data base so that it becomes easy to find out its functionality
and performance (Jacyna, Gołębiowski and Wasiak, 2017). This enable in finding out
effectiveness of each layer of database. Besides that, testing helps in finding out errors in
database so that it can be resolved easily. The outcomes obtained from is in terms of data
security, integrity, performance, speed of processing of data, etc.
26

However, there are some key points which has to be taken into consideration for testing
functionality and performance of data base. They are defined as below :
 The testing of database is done with help of primary and foreign keys. With that it will
enable in analysing its uniqueness. For example- two employee having same salary or log
in id.
 Here, in testing all constraints of database will be tested and with some default values in
it.
 Along with it, the effectiveness of database should not alter with change in testing
environment. This means that one type of test outcomes must not modify other type of
testing process for database.
Test data is defined as the data which will be used for testing. The organization must use real
time and valid data for testing to get an actual analysis of database performance (Pavych, and
Pavych, 2019). Database testing will not only utilize the existing data of organisation for testing
but it will also generate great data volume of test data for enhancing the effectiveness of
27

developed database system. The credible and reliable test data increases the efficiency otherwise
the designed database. However, there are different type of testing available in which can be
used. But it depends on type and design of database. The types of described as below :
Black box testing:
This functional testing test the functionality of incoming and outgoing data of designed
database. It ensures that functionality is not compromised in design. It does not give detailed
description of the programs which are required to be tested. This testing is performed at the
initial stages of database development and is less costly. The most common black box testing
techniques are equivalence partitioning, boundary value analysis and error guessing techniques.
In equivalence partitioning input values of system are divided into groups and output is tested. In
boundary value analysis database is tested by feeding extreme values to the database so that data
validity can be tested. On the other hand in error guess technique error prone segments are
detected at priority and are tested (Sai, and Kumar, 2020).
28

White box testing:
In contrary to the black box testing, white box testing requires the detailed knowledge of
internal structure and code of the database. It tests the syntax and can be accomplished with
programming knowledge only. This database testing utilizes flowcharts and data-flow diagrams.
This type of testing prohibits the communication between different modules. White box testing
uses triggers and supporting logical concepts for testing. It validates database integrity and
schema.
Non functional testing: In these testing procedures the various parameter such as load, stress,
record fetching time, response time are tested. In this testing various transactions are performed
to test the response time of the designed database. It also analyses the system failure by creating
situations in which system gets failed. This testing approach helps organisation to determine the
risk factors in prior to actual system failure.
Hence these are various test which can be applied to test the performance of database and it’s
functionality. So on basis of it improvements can be made to enhance the efficiency of database.
29

Test Data Type-
30

31

P5 Tools available in system to monitor and optimise system performance and examine audit
logs
It has been evaluated that there are different types of tools available to monitor and
optimise system performance. Besides, there are core administration task which are followed in
it. They are data extraction, loading, security, data base back up and recovery. For that there are
certain tools and software uses in it. Furthermore, data base activity is traced to determine its
efficiency (Talukder, Roy, and Al Mahmud, 2021). The monitoring of performance ensure that
data base is working on set standards. Other than this, audit logs is done to track use of resources
and recording of action of data base. Hence, tools available are explained as
32

Solarwinds data base performance – The tool is useful for to monitor data base performance. In
this there is use of metric. The concept used in it is response time analysis. It enable in finding
out difference in metric of data base.
PRTG network monitor- if offers relevant SQL monitoring functionality in it by which
one stop monitoring is done. So, it monitor entire IT infra, traffic, cloud, up time, etc of data base
by which it is easy to analyse its effectiveness. The tools contains default prescribed settings so it
does not configure it. By that it becomes easy to optimise speed of data base processing.
Manage engine application manager- the tool is useful in monitoring server performance.
generally it is used for relational database, oracle, MS SQL, etc. therefore, it optimise
dependencies of other applications on database and its integration with server.
Apart from monitoring and optimizing tools of database management system, there are
various tools available two analyze audit logs. This enables in tracking of resources utilisation.
Thus, the tool are as follows
Site24/7- It is a SAAS tool which helps in Collecting consolidating logs etc in database.
Here logs are collected from where varied sources such as server, Application network devices,
etc. It also helps in troubleshooting and searching of data by which efficiency is evaluated. In
this alert is sent through SMS emails etc. (Bulysheva, Bulyshev, and Kataev, 2017).
33

McAfee tool- As name depict it is A tool which is authorized buy McAfee. The tool stores
use and analyze data by displaying it its original content. Hence it is easy to evaluate the use of
resources within database and tracking the efficiency of it.
Therefore these are the tools which helps in optimizing of database efficiency and tracking
of use of resources in it. Furthermore, proper monitoring is done to determine working and
processing of data in database. It is essential to do auditing of logs so that resources is utilized in
proper way end changes are made according to it within database.
34

P6 Tools available in system to manage security and authorisation
Along with tools to monitor and optimise system performance there are certain tools
available to manage security and authorisation as well (Yang, 2020). It helps in maintaining
security of data base in effective way. Security is core component in data base so that only
authorised person is able to access it. Moreover, quality of data is maintained as well. Hence,
tools for security are as follows
MSSQL data mask – this is most common security tool. There occurs mistake of live data
in database. Thus, this tools is used to mask data for development, testing, etc. So, data is
categorised and then protected. The categories are classified, sensitive and commercial.
Nmap- It is open source tool which is used in security auditing. The database
administrator use this to manage tasks like host service, schedule, etc. It uses IP packets to find
35

out what host is available to be use in network, type of service being offered, etc. Nmap tool can
use by database developer in M&S to manage or handle the regular basis tasks. So that it would
require to use as appropriate Nmap tool which provide the better facilities and services.
Sometimes, if any kind of unauthorised access occurs, this tool can establish connection with
other network and share information within quickly manner.
36

Oracle auditing tools- the tool is developed by Oracle in which is used in security of data
base. Basically, it is a complete set of tool kit which contain password, query tools, TNS, etc. for
security of database. This can be used both in Windows and Linux (Abdullah, Rashid, and
Ahmad, 2018).
Oracle auditing tool or platform is mainly used in the different operating system whereas
company has been installed their business database system. at that time, auditing tool can
perform the different tasks to secure users accessibility. In case, if an authorised person to enter
password because they have chances to loss of data or information. so that auditing tool will help
for monitoring the current activities and perform suitable actions as per requirements.
37

DB defence- this is easy and affordable tool that can be used for security of data base
system. The tool allow data base administrator to encrypt data base. Besides that, tool help in
protecting it from unauthorised access, modifying data, etc. Thus, there are many other features
as well in that is encryption, protection, of SQL, etc. This type of database defence tool is one of
the most commonly used by developer in order to protect or secure sensitive information in the
database system.
this tool is reliable so that will check the all security parameters, usually applied on the
creation of development. Sometimes, Database administrator can be given the privilege of other
staff members to do certain necessary changes within system. This tool is not only protecting the
authorised access but it would generate as security alert to examine the current specific need for
database system protection. DB defence is the most commonly used tool to manage or protect the
entire database in M&S within proper manner.
SQLRECON- This tool perform active and passive scans in the database and the overall
network. It also combines all SQL Server and discovery into a single tool that is used to Find out
servers which does not exist in database. Therefore, this unique function helping protecting the
database security. It analyze all SQL queries that is processed on various servers.
Thus, these are some tools by which security of data base is managed. It also helps in
maintaining authorisation of data base and ensuring quality of data is maintained. The use of
security helps in protecting data and ensuring its smooth functioning (Liu, Lu and Wu, 2018)
38

CONCLUSION
From above discussion, it has concluded that database management system plays important
role in the organization to manage or control the entire business process in proper manner. In this
report, it has been analysed the system and user requirement for M&S, involves the functionality
of database system. As per analysis, it has been performed the compare and contrast between the
different types of database models such as network, relational, hierarchical and object oriented.
On the other hand, using SQL language is to produce the effective design or relational DB
system to meet the specific need or requirement for clients. Moreover, fully functional system
which meets client, system requirements.
39

REFERENCES
Book and Journals
Abdullah, A.A., Rashid, A.S. and Ahmad, M.E., 2018. A Student Feedback System for Teaching
Evaluation in Education Using Database Management System (DBMS); an Empirical
Study for Quality Assurance Unit, University of Sulaimani. journal of kirkuk University
For Administrative and Economic Sciences, 8(1).
Bulysheva, L., Bulyshev, A. and Kataev, M., 2017, October. Image database management
architecture: logical structure and indexing methods. In International Conference on
Research and Practical Issues of Enterprise Information Systems (pp. 34-42). Springer,
Cham.
Jacyna, M., Gołębiowski, P. and Wasiak, M., 2017. Efficacy of Data Security in Managing the
Database of SIMMAG 3D System. Procedia Engineering, 187, pp.526-531.
Liu, Z.H., Lu, J. and Wu, Z., 2018. Multi-model database management systems-a look forward.
In Heterogeneous Data Management, Polystores, and Analytics for Healthcare (pp. 16-
29). Springer, Cham.
Pavych, N. and Pavych, T., 2019. Method for Time Minimization of Api Requests Service from
Cyber-Physical System to Cloud Database Management System. Advances in Cyber-
Physical Systems, (2), pp.125-131.
Sai, G.K. and Kumar, K., 2020. Database Management of Blood Bank & its Availability to Users
through Mobile Terminal. Database, 7(05).
Talukder, S., Roy, S. and Al Mahmud, T., 2021 A Distributed Anti-Malware Database
Management System using BlockChain.
Yang, B., 2020. Research on Network Database Laboratory Instrument Management Information
System Based on Content Query. In IOP Conference Series: Materials Science and
Engineering (Vol. 740, No. 1, p. 012126). IOP Publishing.
40