Database Management Systems Report: RDBMS, Modeling, and Normalization

This report delves into advanced database management systems (DBMS), focusing on relational database management systems (RDBMS) and normalization techniques for enhancing efficiency and reliability. It explores the evaluation of RDBMS, different database modeling languages such as SPARQL, UML, and XML, and compares various DBMS platforms, including open-source and vendor-specific options. The report applies data modeling techniques to refine logical data requirements, incorporating normalization and utilizing UML notation for documentation. Furthermore, it covers the design and construction of database structures, data extraction methods, the application of table and field-level security, system testing for functionality and performance, and the evaluation of security risks. The report is based on a case study of MaxRos Group and their multi-player gaming platforms and provides a comprehensive overview of database management principles and practices.

Advanced Database Management
Systems

Table of Contents
Introduction......................................................................................................................................1
LO 1.................................................................................................................................................1
1.1 Evaluate ways in which RDBMS & normalisation renders efficient as well as reliable
databases.................................................................................................................................1
1.2. Compare different database modelling languages...........................................................3
1.3. Evaluation of various DBMS with respect to open source & vendor specific platforms.4
LO2..................................................................................................................................................5
2.1. Apply data modelling techniques for refining logical data requirements along with
normalisation..........................................................................................................................5
2.2 Usage of UML notation for documentation of logical data needs...................................8
LO3..................................................................................................................................................9
3.1 Design as well as building of database structure..............................................................9
3.2 Extraction of data from tables........................................................................................11
3.3 Apply table & field-level security to database...............................................................11
3.4 Testing of a system for functionality as well as performance........................................12
3.5 Evaluation of security risks to database.........................................................................12
Conclusion.....................................................................................................................................13
References......................................................................................................................................14

Introduction
Database denotes the aggregation of related data which illustrates crucial aspects related
with real world scenario. Basically, it involves generic data for end-user in a defined as well as
global view. Database is utilised by firms for storage, management as well as retrieval of
information (Aponso and et. Al, 2017). This serves as a interface among application programs or
end users and database for ensuring that data is being organised consistently and can be accessed
whenever it is being required by individuals. It is responsible for providing programmers as well
as users with an systematic method for creation, retrieval, updation and management of data. The
advance database management system involves stream processing systems, advanced analytics
along with memory data processing (Bradford, 2015).
This report is based on MaxRos Group within London and it deals within multi-player
gaming platforms. They render their technologies as well as platform capabilities around
extended portfolio of brands as well as businesses for delivering gaming experiences within a
global scale. They render their customers with real time strategies and role playing games
through MMO (Massively multiplayer online). A report has been prepared for Emma Wilson

within task 1 that contains ways in which database models & normalisation renders efficient and
reliable databases along with comparison in between database modelling languages and DBMS
available with respect to open source as well as vendor specific platforms. This also comprises of
data modelling techniques for refining logical data needs and normalise along with this UML is
being utilised for their documentation.
LO 1
1.1 Evaluate ways in which RDBMS & normalisation renders efficient as well as reliable
databases.
The aggregation of programs along with capabilities which enables IT teams and others
for creation, update, administration as well as interaction with database is referred to relational
database management system. This utilises structure which allows identification as well as
accessing data with respect to other piece of data that is being organised in tables (Connolly and
Begg, 2015). It comprises of database that is liable for storing data within structured format
through the usage of rows & columns. For having accurate as well as efficient transactions
within RDBMS ACID can be utilised. Transactions denotes a smaller unit of program which
comprises of low levels of tasks. It has been mentioned below:
 Atomicity: This denotes that entire transaction occurs at same time or it do not takes
place at all. There is no partial transaction, it is executed towards its completion or do
not occurs. For this, two operations exist, they are, abort (alterations cannot be seen
within a database) and commit (modifications can be seen).
 Consistency: It denotes that constraints related with integrity must be maintained before
as well as after transaction takes place. This denotes that if database is in consistent state
before transactions have been carried out then even after it the state will remain same
(Coronel and Morris, 2016).
 Isolation: It ensures that MaxRos Group can have various transactions simultaneously
without any kind of inconsistencies which prevails within state of database. Transactions
must take place independently without any kind of interventions. Alterations which will
take place should not be seen by others until it occurs in the memory. Basically,
transactions will be carried out in such a way that it looks like it is only transaction
which is taking place.

 Durability: It shows that if transaction has been occurred, then the updates along with
alterations are stored within the database and are written within disk as well as they
persist in case failure of system takes place (Farooqi, 2019). These updates become
lasting or permanent and are always stored within non-volatile memory. This means that
impact that has been created by transactions can never be lost.
The ACID properties within database illustrates the consistency along with correctness of
database in such a way that each group acts as a single unit.
The process of reorganising data within database by which two crucial needs can be
addressed, they are: there is no redundancy of data and data dependencies are logical is referred
to as normalisation (Harrison and Lock, 2017). It involves process associated within structuring
of relational database with respect to series of normal forms for elimination of data redundancies
as well as improvisation within data integrity. MaxRos Group can have data in organised form
by the usage of normalisation in which attributes as well as relations within database will be
depicted for ensuring dependencies for enforcing database integrity. Database normalisation
depicts refinement process that takes place after data objects have been identified along with
relationship and defining tables for the same. With respect to reliability as well as efficiency,
there have to be minimised search as well as provides better data integrity as well as minimise
risks of mistakes. Through normalisation MaxRos Group can logically group data and enforce
referential integrity (relationship among data in joined tables).
1.2. Compare different database modelling languages.
The process related with creation of data model for storing data within database is
referred to as data modelling. It comprises of conceptual representation of data objects,
association among data objects along with rules (Huang, Xiao and Cao, 2017). This aids within
visual representation of data which will enforce regulatory compliances, government policies and
business rules. This also ensures consistency with respect to naming conventions, semantics,
default values along with security so that quality of data can be managed. The different
modelling languages have been illustrated below:
 SPARQL: It is a semantic query language that is designed for database as well as it
assists within retrieval as well as manipulation of data that is present within resource
description framework. It is liable for allowing query that comprises of optional,
disjunctions, conjunctions and triple patterns (Król, Madeyski and Nguyen 2016). This

is a declarative query language which is responsible for carrying out data definition as
well as manipulation operations. SPARQL endpoint will provides MaxRos Group with
wide range of benefits like declarative data interaction which involves definition &
manipulation which can be integrated with HTTP, through this there will be enhanced
flexibility with respect to data queries. It will support wide range of query solution
documents which comprises of RDF-XML, RDF-N-Triples, CSV, JSON and various
others.
 UML (Unified Modelling Language): Standard language that is liable for specification,
visualisation, construction as well as documentation of artifacts of software systems is
referred to as UML. It can be utilised by MaxRos Group to have blueprints of software
along with this, there will be reduction within cost for developing diagrams by the usage
of supporting tools (Malik and Patel, 2016). UML tools will minimise the development
tool. This provides an appropriate architect for software engineers as well as developers
through the usage of tools associated with implementation, designing as well as analysis
of software based systems along with modelling of processes. It enables object visual
modelling tool interoperability for enabling meaningful transactions of information
related with agreement, tools related with semantics and notations are required.
 XML: Extensible Markup Language illustrates set of rules that are associated with
encoding of documents in a specified format which is in both machine and human
readable format. The emphasis of XML is within simplification, usability as well as
generality across internet (Mohammed and Mohammed, 2016). This involves textual
data format and renders support through Unicode for diverse human languages. It can be
utilised by MaxRos Group as this separates data from HTML through this emphasis can
be made on usage of HTML/CSS for layout as well as display. This will also simplify
data sharing along with transport and also simplifies the platform change with enhanced
data availability.
1.3. Evaluation of various DBMS with respect to open source & vendor specific platforms.
The software which are available to firm free of cost and be accessed easily is referred to
as open source. MaxRos Group can make use of any of the database. Few of them are MySQL
(relational database & management system), PostgreSQL (object relational database and
management system), Apache Cassandra (NoSQL DBMS) and there are many others and can be

utilised as per their requirements each provides different features. An instance can be taken of
PostgreSQL is mostly used and preferred by different firms (Muntjir, 2016). There asset is
enhanced efficiency of central algorithm through which database is illustrated as advance and
I/O processes do not becomes bottleneck Along with this, it is flexible and can be written on
server side languages. But there are certain limitations also, they are not efficient with respect to
larger datasets, some features such as clustering & parallelization needs the plugins from third-
party.
Vendor specific DBMS denotes that firms need to pay for software's they are making use
for storing their information. There are wide range of databases which are paid. MaxRos Group
can make use of Microsoft SQL server makes use of cloud-based & local servers and this can be
utilised for set up to work simultaneously (Obe and Hsu, 2017). They will provide certain
vantages: it is stable & fast, visualisations can be accessed within mobile devices, can work on
all Microsoft products and performance levels can be tracked. Along with this, there are certain
limitations also: it will be very costly for MaxRos Group, resources can be misused and
integration issues can arise while importing files.
LO2
2.1. Apply data modelling techniques for refining logical data requirements along with
normalisation.
Representation of data structures within a table of database of a firm and acts as a crucial
asset within expressing requirements of business is referred to as data modelling. This assists
within implementation as well as designing of database. Basically, it illustrates data that is
contained within the database (such as courses, subjects, lectures, etc.), relationship among data
items and constraints on the data. Logical data modelling refers to process that is liable for
representation of data architecture as well as its organisation in a graphical manner without it
physical execution that is involved within storage of data. Logical database can access as well as
identify files which are stored within a system (Pei and et. al, 2018). Normalisation refers to
process of reducing redundancy along with improvisation of data integrity. Basically, it is liable
for simplifying the design of database for attaining optimal structure that comprises of atomic
elements. An example has been illustrated below to explain the concept of normalisation along
with logical data requirements.

Consider a video library that is liable for maintaining a database of movies that has been
sold or rented. The information without normalisation is shown beneath:
Full Name Physical Address Videos rented Salutation Category
Sophie Willson First Street Plot No 4 Clash of the Titans
Pirates of the Caribbean
Ms. Action, action
James Phil 4th Street 35 Daddy's Little Girls
Forgetting Sarah Marshal
Mr. Romance,
romance
James Phil 5th Avenue Clash of the Titans Mr. Action
The above table shows that videos rented comprises of multiple values. Now
normalisation can be carried out. It has been illustrated beneath:
First normal form (1NF) rules: Here, this involves that there must be single value within each
table cell and each record will be unique.
Full Name Physical Address Videos rented Salutation
Sophie Willson First Street Plot No 4 Clash of the Titans Ms.
Sophie Willson First Street Plot No 4 Pirates of the Caribbean Ms.
James Phil 4th Street 35 Daddy's Little Girls Mr.
James Phil 4th Street 35 Forgetting Sarah Marshal Mr.
James Phil 5th Avenue Clash of the Titans Mr.
Second normal form rules (2NF) rules: It is necessary for being within 1NF and there must be
single column primary key.
Membership ID Full Name Physical Address Salutation
1 Sophie Willson First Street Plot No 4 Ms.
2 James Phil 4th Street 35 Mr.

3 James Phil 5th Avenue Mr.
Table 1
Membership ID Videos rented
1 Clash of the Titans
1 Pirates of the Caribbean
2 Daddy's Little Girls
2 Forgetting Sarah Marshal
3 Clash of the Titans
Table 2
Two tables have been each contains different information but Membership_ID is same. It
is primary key for table 1 which will provide relation between different tables. In case of table 2,
Membership_ID will provide foreign key.
Third Normal Form (3NF) rules: It is necessary that database entries must be in 2NF and do
not possess any kind of transitive functional dependencies. For this, tables will be again divided
Membership ID Full Name Physical Address Salutation_Id
1 Sophie Willson First Street Plot No 4 2
2 James Phil 4th Street 35 1
3 James Phil 5th Avenue 2
Table 1
Membership ID Videos rented
1 Clash of the Titans
1 Pirates of the Caribbean
2 Daddy's Little Girls
2 Forgetting Sarah Marshal

3 Clash of the Titans
Table 2
Salutation_Id Salutation
1 Mr.
2 Ms.
3 Mrs.
4 Dr.
Table 3
A new table has been formulated 'Salutations' which denotes that there are no functional
dependencies. In table 3 salutation_Id is a primary key and within table 1, it is acting as a foreign
key to salutation table.
Further, decomposition is not required as it is within higher normalisation forms only. In
case of critical databases further steps are required (Prabhjot and Sharma, 2017). But still they
are illustrated below:
Boyce-Codd Normal Form (BCNF): There can be the anomalies like a table can possess many
candidate keys and is also in 3rd NF. In such cases BCNF can be utilised.
Fourth Normal Form (4NF) rules: A database is within a 4NF, if no table have more than two
multivalued as well as independent data which describes the specified entity (Singh, 2017).
Fifth Normal Form (5NF) rules: If database is within 4NF and cannot be further decomposed
within other tables without losing any kind of data, then it will be in 5NF.
When the database is in a normalised form, then the logical data is in a refined form.
Logical data is defined as a process for representation of data architecture within a graphical way
without taking into consideration physical execution.

2.2 Usage of UML notation for documentation of logical data needs.
Data modelling refers to the process of creation of data model for information that has to
be stored within the database. It basically illustrates data objects, relation among them and
certain rules are applied to them (Aponso and et. al, 2017). This can be carried out through ER
models and UML notations. The general purpose modelling language that is being utilised for
specification, visualisation, construction and documentation of artifacts related with software
intensive systems is referred to as unified modelling language. This makes information secured,
robust and scalable. It illustrates graphical notation for creating visual models of specified
systems. Standard UML diagrams provides a entire design which needs exact execution of
software or a system and it is carried via utilisation of Computer Aided Software Engineering
Tools. This denotes that while graphical illustration all the requirements must be acknowledged.
Logical data model is liable for addressing the needs of business. Generally, it denotes
how the system can be executed with respect to DBMS. It will enable technical department of
MaxRos Group to map data structures & rules. Through the utilisation of Unified Modelling
Language the structure of data elements as well as relationship can be illustrated in an
Illustration 1: Logical Data Diagram

appropriate manner (Bradford, M., 2015). The logical data modelling needs standard ER models
which involves distinct information system.
LO3.
3.1 Design as well as building of database structure.
The aggregation of record type (includes research objects or entities which must be
captured for an instance person) and field type (it comprises of attributes or properties which are
liable for describing the record types like age,height, gender, etc.) definitions which includes
database is referred to as database structure. Basically, they define data or information which can
be stored within any kind of record of that type. Database design refers to identification of what
data needs to be stored and how these elements are interrelated. This concept of designing as
well as building of database can be understood by taking an instance. Woolworths is an
Australian supermarket which is largest company by a revenue. The initial step while building a
database is requirement analysis, its designing and implementation.
Database designing: It comprises of logical as well as physical model. Both are different
and must be taken into consideration by development team of Woolworths. Logical model is
related with development of database model depending upon needs. It is made in paper and liable
for altering logical schema. The physical model actually implements logical model of database
by taking into account DBMS. This can be understood by Woolworths instance, the database is
made using Excel.