RDBMS Design, SQL Implementation, Performance Monitoring & Security

This report provides a comprehensive analysis of database management systems, focusing on the M&S enterprise scenario. It begins by comparing and contrasting various database models, including relational, network, hierarchical, and object-oriented models, highlighting their features, advantages, and disadvantages. The report then details the design of a relational database management system tailored to meet client requirements, incorporating an entity-relationship diagram and data validation techniques. The implementation phase involves developing a fully functional system using SQL, covering data definition language (DDL) and data manipulation language (DML) commands. Finally, the report addresses system testing for functionality and performance, as well as tools for monitoring system performance, examining audit logs, and managing security and authorization. The goal is to provide a robust and secure database solution for M&S, ensuring data integrity and efficient access.

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