Advanced Database Management Systems

This report discusses the ways in which RDBMS & normalisation renders efficient and reliable databases. It also compares different database modelling languages and evaluates various DBMS with respect to open source & vendor specific platforms. The report includes data modelling techniques for refining logical data requirements and normalisation.

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.

(Source: Woolworths Group Limited, 2019)
The above table comprises of entities like date, open, high, low, close, adj. close and
volume. Each is an independent identity and shows the share vales on respective date. Like on
6th November 2019, the opening was 37.95 and it was also same for high. Though there exist an
repetitive anomaly but as each entity represent the different values, it will be important to include
each.
Illustration 2: Woolworths Database

3.2 Extraction of data from tables.
The process in which data is crawled and analysed for retrieving specified information
from database in an specified format is referred to as data extraction. An instance can be taken
with respect to Woolworths is illustrated below:
The basic command that can be utilised for having a data from specified database is
illustrated below:
SELECT * FROM Woolworths_database;
Similarly for having or extracting the information from specified field can be taken as:
SELECT * FROM “Woolworths_database” WHERE High >38
This will provide the output of all the values present within the entity or field High where
is lies greater than 38.
Here, Woolworths_database is the name of database which comprises of different entities
and each have different data types depending upon the kind of values the specified field will
contain. For an instance, entity High is having the decimal values which denotes the data type
that can be utilised is “Decimal” and to store the values in a date entity, “Date” datatype can be
utilised. The reason for this that, each type has their own characteristic and if wrong type is
assigned then error will occur while entering the data within the table. Therefore, development
team of Woolworths need to be clear with fields they are being using.
3.3 Apply table & field-level security to database.
Power BI (business analytics service that can be utilised for providing interactive
business intelligence capabilities and visualisation interface for creating reports) can be utilised
for table level securities within the DB. Through this, restrictions can be applied with respect to
providing access to the users or employees of Woolworths. Through this roles can be viewed and
specified field can be selected on the basis of options that are being provided and accordingly
Illustration 3: Extraction of Data

access can be provided each individual. The reason for this is that it is not necessary that sales
department of Woolworths must have access to the data of finance department.
Field level security provides the control access to individualistic fields in an object. This
can be applied to records for which user possess access and this is done aggregation of both
record and role based security. The rule have to be defined for field level security and with
respect to this development team of Woolworths need to take into consideration all the aspects
that are necessary for accessing for specified fields. Basically, in this use permission sets &
profiles are being utilised for specifying fields and objects to which users can have access.
3.4 Testing of a system for functionality as well as performance.
The process of checking tables, schema, triggers and many others of database which are
under test is referred to as database testing. Performance testing is liable to test the behaviour of
product when it is under load. This load can be evaluated with respect to number of users that
can be handled by application suppose website of an organisation, amount of data which can be
processed, etc. Functional testing is responsible to test the functions of applications. For an
instance, the customers of Woolworths can carry out online shopping, for this they can login
into the system and they will be navigated to their profile. There will be different options like
user can change their contact number but before this, it has to be verified. It is the simple
function which is being provided by the system. For this, different tools can be utilised like
functional and performance testing or load testing depending upon the requirements of the
system.
3.5 Evaluation of security risks to database.
There are wide range of threats which prevails with respect to the database. They
involves excessive privileges (like when sales department have privilege to database of finance
division of Woolworths then they might modify the details) , legitimate privilege abuse, database
injection attacks, storage media exposure, malware, unmanaged sensitive data, exploitation of
vulnerable database and many others. This denotes that firms need to opt for security solutions
and defensive matrix can provide to apt results for protecting database of Woolworths. This
includes assessment of database vulnerabilities, identification of compromised endpoints along
with classification of sensitive data. In addition to this, database access activities are also
monitored for detection of data leakage, transactions and probable system attacks. The defensive
matrix is also liable for blocking malicious web requests and archival of external data.

Furthermore, database can be stored in an encrypted form to ensure that it is secured and any
unwanted user do not get access to this.
Conclusion
From above, it can be concluded that database management system (DBMS) refers to
technology that is being utilised for storage as well as retrieval of users data with higher
efficiency and security measures. RDBMS (Relational DBMS) refers to segregation of programs
along with capabilities which will enable IT teams within creation, updation, administration as
well as interaction. DBMS makes use of ACID properties to ensure that information or data
which is present within database is atomic, consistence, isolate as well as durable. This is carried
out within transactions that are conducted for manipulation of data within the database. There are
different types of software's that is being utilised by firms for ensuring that data is stored in an
effectual manner. They can be either open source or vendor specific as per the requirements of
the organisation or features they are looking forward for.

References
Books & Journals
Aponso, G.C. and et. al, 2017. Database optimization using genetic algorithms for distributed
databases. Int J Comput, 24(1), pp.23-27.
Bradford, M., 2015. Modern ERP: select, implement, and use today's advanced business
systems. Lulu. Com.
Connolly, T. and Begg, C., 2015. Database systems. Pearson Education UK.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Farooqi, N., 2019. Tackling Approach for Transferring Database to Knowledge Base via
Practical Algorithm. Life Science Journal, 16(6).
Harrison, F. and Lock, D., 2017. Advanced project management: a structured approach.
Routledge.
Huang, Z., Xiao, X. and Cao, X. eds., 2017. Databases Theory and Applications: 28th
Australasian Database Conference, ADC 2017, Brisbane, QLD, Australia, September
25–28, 2017, Proceedings (Vol. 10538). Springer.
Król, D., Madeyski, L. and Nguyen, N.T. eds., 2016. Recent developments in intelligent
information and database systems(Vol. 642). Springer.
Malik, M. and Patel, T., 2016. Database securityattacks and control methods. International
Journal of Information, 6(1/2), pp.175-183.
Mohammed, A.K. and Mohammed, S., 2016. EMPLOY DATABASE TECHNOLOGY
(ENVIRONMENTALLY FRIENDLY) IN E-PAYMENT SYSTEM AT APPLYING
ON THE SOCIAL SECURITY DIRECTORATE/DUHOK. International Journal of
Advances in Engineering & Technology, 9(6), p.592.
Muntjir, M., 2016. Novice Evaluation and Comparative Survey on Database Management
System, Data Warehousing and Data Mining. International Journal of Computer
Applications, 136(10), pp.39-45.
Obe, R.O. and Hsu, L.S., 2017. PostgreSQL: Up and Running: a Practical Guide to the
Advanced Open Source Database. " O'Reilly Media, Inc.".
Pei, J., Manolopoulos, Y., Sadiq, S. and Li, J. eds., 2018. Database Systems for Advanced
Applications: 23rd International Conference, DASFAA 2018, Gold Coast, QLD,
Australia, May 21-24, 2018, Proceedings (Vol. 10828). Springer.
Prabhjot, P. and Sharma, N., 2017. Overview of the Database Management
System. International Journal of Advanced Research in Computer Science, 8(4).
Singh, K., 2017. Data warehousing in libraries for managing database. International Journal of
Advanced Research in IT and Engineering, 6(3), pp.9-13.