University Database Management System Report: SQL and Testing

This report provides a comprehensive overview of Database Management Systems (DBMS), focusing on the practical application of Structured Query Language (SQL). The report begins with an introduction to Data Manipulation Language (DML), detailing selection, insertion, modification, and deletion operations. It then explores Data Control Language (DCL), specifically the GRANT and REVOKE commands used for managing database access permissions. The core of the report involves executing and analyzing various SQL queries for data manipulation, including joins, updates, and deletions. Additionally, the report delves into database testing, outlining field size validation, null value handling, check constraints, and referential integrity. The report concludes with a bibliography of relevant resources, demonstrating a thorough understanding of the subject matter and the practical aspects of DBMS implementation.

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.................................................................................................................9
3.1 Database Testing Definition and Importance:.......................................................................9
3.2 Testing Process:.....................................................................................................................9
Bibliography:.................................................................................................................................12

2DATABASE MANAGEMENT SYSTEM
Task 1: Structure Query Language
1.1 Data Manipulation Language:
In order to manipulate, record and access data from a database, the users use the data
manipulation language. Selection, insertion, modification and deletion are the core of data
manipulation language in database. The primary aim of the data manipulation language can be
referred to implementation of easy and effective interaction between the system and user. The
data manipulation language can be utilized in the database SQL. The SQL can be used for
manipulating the relational databases. Two kind of DML exists in database such as non-
procedural and procedural. In case of non-procedural, the database admin mentions what type of
data is required. On the other hand, the user defines the method of accessing the data along with
the data to be accessed in procedural data manipulation language.
For making the database applicable to an organizational objective, the database must
have values within it. In order to populate the database with necessary values, the user uses the
insert command. The insert command allows the user to input a row into the database based on
the structure of the database. Few columns of the database can be empty in a row if columns are
defined as nullable but the primary key will always have a value. Syntax for the insert query is
INSERT [INTO] TableName [(ColumnName, ... n)] VALUES ({DEFAULT I NULL I
expression}[, ... n]). The order of the column name and values are respective to each other. If
orders mismatch then database will either store wrong data in column or show an error. If the any
attribute has a value set as default then entering null values would result into saving the default
value instead of null for that column.

3DATABASE MANAGEMENT SYSTEM
The select column is used for fetching data from the database entities or views. The select
column is capable of retrieving data from more than one column. This is possible because of the
referential integrity constraint that make use of foreign keys. The user can also define a condition
for pulling data out of a database tables.
Update: The update query is used by the users for modifying the existing value in single
or multiple columns in a database table. The general form of update command is as following.
UPDATE table_name
SET column_name1 = value1,
column_name2 = value2,
...
column_nameN = valueN,
[WHERE condition]
Taken as an example, an employee has moved from one location to another. This data
needs to be updated in the database so that organization has complete and current data regarding
each employee. Suppose the id of the employee is 1. The query for updating the address of
employee column is as following.
UPDATE employee set emp_address = '95 Capper Street, BINJOUR QLD 4625' where emp_id
= 1;

4DATABASE MANAGEMENT SYSTEM
Select * from employee where emp_id = 1;
From the above screen shot it can eb stated that the database table employee has updated
address for the employee who has emp_id of 1.
Delete: The delete statement is capable of deleting entire data set of a table or a specific
row. In order to delete a specific row, where condition is defined in the delete statement. The
syntax for the delete statement is as following.
DELETE FROM table_name [WHERE condition];
Taken as an example, employee with the id ‘1’ is not working in the organization any
more. In order to reflect this into the database, the row with emp_id ‘1’ in the employee table
will be deleted. The delete query as state below.
DELETE FROM employee WHERE emp_id = 1;
As it can be seen, the row with emp_id ‘1’ has been deleted.
1.2 Data Control Language:
The data control language can be referred to the subset of structured query language or
SQL. The data control language is used for allowing the administrator of database configuring
the relational database security access. The DCL complements the data definition language and

5DATABASE MANAGEMENT SYSTEM
well as the data manipulation language. There are two kinds of DCL such as grant and revoke.
Grant is allowing permission to a user and revoke is for calling back the grant permission.
Grant: the grant statement is used by the database admins for adding new permission to a
user of the database. The syntax of grant is very simple and is as following.
GRANT [privilege]
ON [object]
TO [user]
[WITH GRANT OPTION]
The grant permission has three main parameters such as privilege, object and user.
Privilege refers to the SQL DDL or DML command that are used in the SQL. Following diagram
is an example of Grant command.
Revoke: This command is used for revoking the user access to the database which has
previously been granted by the administrator. The syntax of revoke command is as stated below.
REVOKE [GRANT OPTION FOR] [permission]
ON [object]
FROM [user]
[CASCADE]

6DATABASE MANAGEMENT SYSTEM
The parameters for the revoke statement are permission, object, user, grant option for and
cascade. Following diagram is an example of Revoke command.
Task 2: Data Manipulation
Query 1: Select Concat(first_name, last_name) as 'Full Name', gender, salary from
employees inner join salaries on employees.emp_no = salaries.emp_no;
Query 2: Select Concat(first_name, last_name) as 'Full Name', title, dept_name from
departments inner join dept_emp on departments.dept_no = dept_emp.dept_no inner join
employees on dept_emp.emp_no = employees.emp_no inner join titles on employees.emp_no =
titles.emp_no;

7DATABASE MANAGEMENT SYSTEM
Query 3: Select Concat(first_name, last_name) as 'Full Name', gender from departments
inner join dept_emp on departments.dept_no = dept_emp.dept_no inner join employees on
dept_emp.emp_no = employees.emp_no Where departments.dept_no = 'd004';
Query 4: Select Concat(first_name, last_name) as 'Full Name', dept_name, from_date,
to_date from departments inner join dept_manager on departments.dept_no =
dept_manager.dept_no inner join employees on dept_manager.emp_no = employees.emp_no;
Query 5: Select Concat(first_name, last_name) as 'Full Name', dept_name, from_date,
to_date from departments inner join dept_manager on departments.dept_no =
dept_manager.dept_no inner join employees on dept_manager.emp_no = employees.emp_no
where year(hire_date) >= 1990;
Query 6: Update employees Set employees.birth_date = '1959-12-09' Where
employees.last_name = 'Bamford';

8DATABASE MANAGEMENT SYSTEM
Query 7: Update salaries, employees Set salaries.emp_no = employees.emp_no, salary =
(salary*105.5/100) Where salaries.emp_no = employees.emp_no AND Year(hire_date) >= 1997;
Query 8: Delete departments, dept_manager, employees from departments inner join
dept_manager on departments.dept_no = dept_manager.dept_no inner join employees on
dept_manager.emp_no = employees.emp_no Where departments.dept_no = 'd004' AND
employees.emp_no = 10003;
Query 9: CREATE OR REPLACE VIEW `Employee Details` AS Select
Concat(first_name, last_name) as 'Full Name', dept_name, salary from departments inner join
dept_emp on departments.dept_no = dept_emp.dept_no inner join employees on
dept_emp.emp_no = employees.emp_no inner join salaries on employees.emp_no =
salaries.emp_no;

9DATABASE MANAGEMENT SYSTEM
Query 10: CREATE VIEW `Manager of Department` AS Select Concat(first_name,
last_name) as 'Full Name', dept_name from departments inner join dept_manager on
departments.dept_no = dept_manager.dept_no inner join employees on dept_manager.emp_no =
employees.emp_no Where Year(employees.hire_date) >= 1990 AND Year(employees.hire_date)
<= 2000;
Task 3: Database Testing
3.1 Database Testing Definition and Importance:
3.2 Testing Process:
Test 1: Field Size Validation - INSERT INTO `departments` VALUES
('d0011','Marketing');

10DATABASE MANAGEMENT SYSTEM
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');
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')

11DATABASE MANAGEMENT SYSTEM