Database Management System

This document provides a comprehensive guide on Database Management System. It covers topics such as structure query language, data manipulation, and database testing. Learn about DML, DCL, and more.

Running head: DATABASE MANAGEMENT SYSTEM
Database Management System
Name of the Student
Name of the University
Author’s Note:

1DATABASE MANAGEMENT SYSTEM
Table of Contents
Task 1: Structure Query Language.................................................................................................2
1.1 Data Manipulation Language:...............................................................................................2
1.2 Data Control Language:.........................................................................................................4
Task 2: Data Manipulation..............................................................................................................6
Task 3: Database Testing...............................................................................................................10
3.1 Database Testing Definition and Importance:.....................................................................10
3.2 Testing Process:...................................................................................................................11
Bibliography:.................................................................................................................................13

2DATABASE MANAGEMENT SYSTEM
Task 1: Structure Query Language
1.1 Data Manipulation Language:
The DML or data manipulation language is a computer language that offers the user way
to write data manipulation commands within a database management system. The data
manipulation includes the data insertion within the database relations, fetching the existing data
from the database, eliminating data from the relations and updating the stored data. The basic
idea behind the DML is same for all the relational databases. The data manipulation language
can be of two types such as procedural programming and declarative programming. In the
procedural programming, the user specifies the desired data and the process of retrieving it.
However, in the declarative programming, the user will mention the required data. The
commands for data manipulation are select, inert, update and delete. The select, update and
delete commands specifies which data to be manipulated and form where we can access the data.
The insert command mentions which data to be inserted and into which position. From and
where are associated with the select and delete commands. The form specifies a single relation
within the database and the attributes mentioned in the delete or select command should be
existing within that specific relation. Where is for implementing condition within the query. The
where clause restricts the commands to specific value or values within the relation mentioned in
the form syntax. Taken as an example, select * from student where student.city = ‘Sydney’. This
query searches all the records in the student relation based on the condition that student must be
living in Sydney.
Update: Update command uses the Set and Where clauses to manipulate the data within a
database. However, there are some restrictions regarding data update. One of the most important

3DATABASE MANAGEMENT SYSTEM
restriction is that primary key of a relation cannot be updated. Another important update is that
foreign key of a relation cannot be updated to some value which is not present as primary key of
referenced relation. Update command syntax is as following.
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
One important thing to remember is running the query without mentioning the where
clause will result into updating all the values in that attribute. The example of update command
is as following.
Update employees first_name = 'Jhon' Where emp_no = 10001;
Delete: This command is used for deleting the present record within the database. The
form of writing the delete query in relational database is
DELETE FROM table_name WHERE condition.
It is mandatory to specify the where condition in the delete command. If the where
condition is not mentioned then few database management system may delete all records within
the database. Taken as an example as following.
Delete from employees Where emp_no = 10001;

4DATABASE MANAGEMENT SYSTEM
1.2 Data Control Language:
The data control language or DCL may be referred as a computer specific language
which is capable of utilizing database privilege controls. These database privileges are essential
regarding execution of operations within the database. The examples of the operations are
creating sequences, tables or views. This is a part of SQL or Structured Query Language. Two
kinds of privileges exists within a database management system such as system privileges and
object privileges.
The system privileges are associated with some special actions on objects such as
synonyms, tables, views, groups, indexes, PL/SQL procedures, PL/SQL functions and more.
Only the user or an instance administrator has the authority to granting system privileges. The
object privileges, on the other hand, is used for performing specific actions on objects such as
replication schemes, synonyms, views, tables, cache groups and more. The object privileges
cannot be revoked. The object privileges can be grant by the object owner. There are two data
control language or DCL commands in a SQL database, grant command and revoke command.
Grant: In order to offer privileges or access of the database system privileges to the user,
the grant command is used. The grant command can be used for getting access to the database
privileges. The syntax of the grant command is somewhat similar to the SQL queries. The syntax
of grant command is as following.
GRANT privilege_name
ON object_name
TO {user_name I PUBLIC I role_name}
[WITH GRANT OPTION];

5DATABASE MANAGEMENT SYSTEM
At the time of creating user in the SQL, the system must wait for appropriate privileges
or permissions. An example of granting session creating privileges is GRANT CREATE
SESSION TO username. Another grant privilege is allowing all the permissions to a specific
user. The sysdba is a collection of privileges and holds all the permissions within it. Therefore,
by providing all the privileges to a specific user, sysdba can be granted to a user. The example of
this command is GRANT sysdba TO username.
Revoke: The revoke command is used for cancelling the previously granted or denied
permissions. The main purpose of the revoke command is withdrawing command regarding
privilege allowance. The syntax of grant command is as following.
REVOKE<privilege list>
ON <relation name or view name>
From <user name>
The example of the revoke command is as following.
REVOKE UPDATE
ON worker
FROM MNO;

6DATABASE MANAGEMENT SYSTEM
Task 2: Data Manipulation
Query 1:
Query 2:
Query 3:

7DATABASE MANAGEMENT SYSTEM
Query 4:
Query 5:

8DATABASE MANAGEMENT SYSTEM
Query 6:
Query 7:
Query 8:
Query 9:

9DATABASE MANAGEMENT SYSTEM
Query 10:

10DATABASE MANAGEMENT SYSTEM
Task 3: Database Testing
3.1 Database Testing Definition and Importance:
Definition: The database testing is one of the most significant part of software testing.
Database testing is essential for making sure that the data a program is fetching or recording into
the database are accurate or wrong. For testing the database, it is essential that the tester must
have adequate amount of knowledge and expertise of database. The database testing is all about
checking database schema, triggers, relations, attributes, functions, views and all the other
database elements under specific circumstances and conditions. The database testing include
generating complex SQL queries for stress/load testing the database along with evaluating the
responsiveness of the database. The database testing involves consistency and integrity.
There are three types of database testing such as structural, functional and non-functional
testing. The structural database testing includes validation of elements that reside within the data
repository. These elements are utilized mainly for recording data and these elements are not
permitted to be directly altered by the database end user. The database server validation is also
extremely significant consideration in structural database testing. Trigger testing, schema testing,
relation and column testing, stored procedure testing and database server testing are the types of
structural testing. Verification of ACID properties is also an important factor in database testing.
The ACID stands for Atomicity, Consistency, Isolation and Durability of a database. This testing
is related to the relational database. This testing makes sure that the data within the database
remain consistent throughout its lifecycle.
Importance: The importance of database testing are as following.

11DATABASE MANAGEMENT SYSTEM
i. The database testing allows checking if attributes in the database are
corresponding with the fields in the user interface.
ii. The database testing makes sure that database transaction rules are always
followed
iii. The database testing allows checking CRUD operations in a database and assist in
maintaining database integrity
3.2 Testing Process:
Test 1: Field Size Validation - INSERT INTO `departments` VALUES
('d0011','Marketing');
In spite of entering ‘d0011’ the database catches only the ‘d001’ as the field size is set as
char(4).
Test 2: Null values - INSERT INTO `employees` VALUES (10020,'1953-09-
02','','Facello','M','1986-06-26');
The database accept null values which not the right way to implement the database.
Test 3: Check constraints – INSERT INTO `employees` VALUES (10020.20,'1953-09-
02','James','Facello','M','1986-06-26');

12DATABASE MANAGEMENT SYSTEM
If any float is tried to be inserted into the attribute defined as integer then database will
only accept the integer part.
Task 4: Referential integrity - INSERT INTO `dept_emp` VALUES (10001,'d010','1986-
06-26','9999-01-01')

13DATABASE MANAGEMENT SYSTEM
Bibliography:
Anderson, D., 2018. Modeling and Analysis of SQL Queries in PHP Systems.
Ansari, H., 2018. Performance Comparison of Two Database Management Systems MySQL vs
MongoDB.
Cao, Y., Fan, W., Wang, Y., Yuan, T., Li, Y. and Chen, L.Y., 2017, May. BEAS: bounded
evaluation of SQL queries. In Proceedings of the 2017 ACM International Conference on
Management of Data (pp. 1667-1670). ACM.
Challawala, S., Lakhatariya, J., Mehta, C. and Patel, K., 2017. MySQL 8 for Big Data: Effective
Data Processing with MySQL 8, Hadoop, NoSQL APIs, and Other Big Data Tools. Packt
Publishing Ltd.
Gyorodi, C., Gyorodi, R., Pecherle, G. and Olah, A., 2015, June. A comparative study:
MongoDB vs. MySQL. In 2015 13th International Conference on Engineering of Modern
Electric Systems (EMES) (pp. 1-6). IEEE.
Krogh, J.W., Krogh and Gennick, 2018. MySQL Connector/Python Revealed. Apress.
Kumar, L., Rajawat, S. and Joshi, K., 2015. Comparative analysis of nosql (mongodb) with
mysql database. International Journal of Modern Trends in Engineering and Research, 2(5),
pp.120-127.
Lindberg, T., 2018. A/B-testing for web design: A comparative study of response times between
MySQL and PostgreSQL: Implementation of a web based tool for design comparisons with
stored images.

14DATABASE MANAGEMENT SYSTEM
Litta, E., Passarotti, M. and Culy, C., 2016. Formatio formosa est. Building a Word Formation
Lexicon for Latin. In Proceedings of the third italian conference on computational linguistics
(clic–it 2016) (pp. 185-189).
Medeiros, I., Beatriz, M., Neves, N. and Correia, M., 2017, June. Demonstrating a Tool for
Injection Attack Prevention in MySQL. In 2017 47th Annual IEEE/IFIP International
Conference on Dependable Systems and Networks (DSN)(pp. 551-558). IEEE.
Sánchez-de-Madariaga, R., Muñoz, A., Castro, A.L., Moreno, O. and Pascual, M., 2018.
Executing Complexity-Increasing Queries in Relational (MySQL) and NoSQL (MongoDB and
EXist) Size-Growing ISO/EN 13606 Standardized EHR Databases. JoVE (Journal of Visualized
Experiments), (133), p.e57439.
Saraiva, J., Guimarães, M. and Belo, O., 2017. AN ECONOMIC ENERGY APPROACH FOR
QUERIES ON DATA CENTERS.