University Database Development Report: Data Manipulation and Testing

This report presents a comprehensive analysis of SQL database development, covering both data manipulation and database testing aspects. Task 1 focuses on data manipulation, providing SQL queries to list employee details, salaries, department information, and to modify and delete employee records. It includes examples using SQL's `CONCAT_WS`, `INNER JOIN`, `UPDATE`, and `DELETE` statements, as well as the creation of database views. Task 2 delves into database testing, emphasizing its importance and providing examples of field size validation, not null values, primary key, and foreign key constraints, with corresponding SQL queries and screenshots to illustrate the concepts. The report references key SQL concepts and database testing principles, providing a practical understanding of database management systems. The assignment demonstrates the student's ability to use manipulation and querying tools including SQL and test and document relational database systems.

Documentation
Task 1: Data Manipulation:
1. Write an SQL statement to list all employees with their full names, hire date and title
Query:
Select CONCAT_WS(" ",first_name,last_name) as full_name, hire_date, title from employees
inner join titles on titles.emp_no=employees.emp_no;
Output:
Picture 1

2. Write an SQL statement to show the salary of all employees and their department name.
Query:
select salaries.emp_no, salary, dept_name from (dept_emp inner join departments on
departments.dept_no=dept_emp.dept_no) inner join salaries on
salaries.emp_no=dept_emp.emp_no;
Output:
Picture 2

3. Write an SQL statement to show the full names and genders of HR department staff.
Query:
Select CONCAT_WS(" ",first_name,last_name) as full_name, gender from (employees inner
join dept_emp on dept_emp.emp_no=employees.emp_no) inner join departments on
departments.dept_no=dept_emp.dept_no where dept_name like 'Human Resources'
Output:
Picture 3

4. Write an SQL statement to show the all departments’ name and their departments’ managers.
Query:
Select dept_name, CONCAT_WS(" ",first_name,last_name) as full_name from
(employees inner join dept_manager on dept_manager.emp_no=employees.emp_no)
inner join departments on departments.dept_no=dept_manager.dept_no;
Output:
Picture 4

5. Write an SQL statement to show a list of department managers who were hired after 1986
Query:
Select dept_name, CONCAT_WS(" ",first_name,last_name) as full_name from
(employees inner join dept_manager on dept_manager.emp_no=employees.emp_no)
inner join departments on departments.dept_no=dept_manager.dept_no where
year(hire_date)>1986;
Output:
Picture 5

6. Write an SQL statement to change any employee’s title. Assume the employee has just phoned in
with his/her last name.
Query:
update employees inner join titles on titles.emp_no=employees.emp_no set title="Engineer"
where last_name LIKE "Kalloufi"
Output:
Before Update:
Picture 6

Update:
Picture 7
After Update:
Picture 8

7. Write an SQL statement to delete employee’s record who belongs to department 'd004'
and ID 10003.
Query:
delete employees from employees inner join dept_emp on
employees.emp_no=dept_emp.emp_no
where employees.emp_no=10003 and dept_emp.dept_no LIKE 'd004';
Output:
Before Delete:
Picture 9

Delete:
Picture 10
After Delete:

Picture 11
8. Create a database view to list full names of all employees, their department managers and
salaries.
Query:
create view EmployeeManagerList as
SELECT employee_name, manager_name, salary FROM ( ( SELECT employees.emp_no,
CONCAT_WS(" ", first_name, last_name) AS employee_name, salary, dept_no FROM
( ( employees INNER JOIN salaries ON employees.emp_no = salaries.emp_no ) INNER JOIN
dept_emp ON dept_emp.emp_no = employees.emp_no ) ) AS employeeList INNER
JOIN( SELECT dept_manager.dept_no, CONCAT_WS(" ", first_name, last_name) AS
manager_name FROM employees INNER JOIN dept_manager ON dept_manager.emp_no =
employees.emp_no ) AS managerList ON managerList.dept_no = employeeList.dept_no )
Output:
Picture 12

Picture 13

9. Create a database view to list all departments and their department managers, who were
hired between 1980 and 1990.
Query:
create view ManagerList as
Select dept_name, CONCAT_WS(" ",first_name,last_name) as full_name from (employees inner
join dept_manager on dept_manager.emp_no=employees.emp_no) inner join departments on
departments.dept_no=dept_manager.dept_no where year(hire_date) between 1980 and 1990;
Output:
Picture 14

Picture 15

10. Write an SQL statement to increase salaries of all employees up to 10% who are working
in marketing department
Query:
UPDATE employees inner join dept_emp on dept_emp.emp_no=employees.emp_no inner join
departments on departments.dept_no=dept_emp.dept_no inner join salaries on
employees.emp_no=salaries.emp_no set salary = 1.1*salary where dept_name like 'Marketing'
Output:
Picture 16

Task 2:Database Testing[35%]
1. Discuss why database testing is important and give examples to illustrate your answer?
(15 marks)
Solution:
Database is defined as the collection of tables that are linked with one another. These tables store
information about the particular application for which it is developed. Database are used in
complex application like banking to store the account details of the customer, transactions
performed by them etc. The data stored in the database must be correct, so it must be tested for
correctness to ensure the proper functioning of the banking system.
Database testing is a software testing.
The database is tested such that
i) The data stored in the database is same as the one entered by the user
ii) The data displayed in the UI to the user is same as the stored data
Some of items tested while doing database testing are
i) Database schema is implemented correctly as specified in the design document
ii) Transaction are performed correctly, maintaining ACID properties
iii) Ensure that the triggers are executed properly on performing particular operation
iv) The procedures/ function calls are working correctly and ensure that function returns
the correct value
v) Check for the field constraints
vi) Table columns are inserted with the correct value from the front-end application
vii) Front end application correctly displays the table column value.
Thus, the database testing is classified broadly into 3 types:
1) Structural Testing
2) Functional Testing
3) Non-functional Testing
Structural testing involves testing the table, attributes, views, procedures/functions, trigger of the
database.
Functional testing involves testing the database from the external user point of view. Functional
testing includes both black box and white box testing.
Non-functional testing includes testing for load, stress, risk determination, optimization etc.
2. Provide the screen shots of the testing process on Employees database with the following
constraints
 field size validation with an example (5 marks)

 Not Null values with an example (5 marks)
 Primary Key with example (5 marks)
 Foreign Key with example (5 marks)
Solution:
Field size validation:
This constraint checks for the field size of the attribute before insert/update operation. If the
length of the data exceeds the field size the insert/update operation is not executed, and error is
thrown by the mysql.
For example:
In the Departments table, dept_no field has the field size of 4 characters. So, when the data of
size greater than 4 is inserted the operation is not executed.
Query:
Insert into departments values ('d1001','Work force');
Output:
Picture 17
Not Null Validation:
When an attribute of the table is mentioned as not null, insertion of the data with null values in
the field is forbidden with an error message.
For example:

In the Departments table the attribute dept_name is specified as NOT NULL. So when we try to
insert the data in the departments table with dept_name as null, an error message is displayed.
Query:
Insert into departments(dept_no) values ('d100');
Output:
Picture 18
Primary Key Validation:
Primary key is the attribute of the table which uniquely identifies each row in the table.
When we insert a data into the table, the primary key data should be unique i.e. same key value
must not exist already in the table. If primary key violates the operation is not denied with a error
message for primary key violation.
For example:
In the Departments table, dept_no is the primary key. The table already contains the department
with dept_no ‘d001’. So when a data is inserted again with dept_no ‘d001’ an error message is
displayed.
Query:
Insert into departments values ('d001','Work force');
Output:

Picture 19
Foreign Key Validation:
Foreign key is attribute in the one table which refers to the primary key in another table. When a
data is inserted into the foreign key attribute, the database first checks if the corresponding
primary key already exist in another table. If the primary key does not already exist, the
operation reports the error.
For example:
In dept_emp table, the dept_no attribute acts as a foreign key referring to the dept_no
attribute of the departments table. So, when a row is inserted in the dept_emp table, the data for
dept_no is checked against the dept_no field in the departments table. If the particular dept_no
does not exist an error message is thrown.
Query:
INSERT INTO `dept_emp` VALUES (10001,'d100','1986-09-26','9999-01-01');
Output:
In the above insert statement, the dept_no 'd100' does not exist in the department table.

Picture 20
References:
Laine Campbell, Charity Majors, "Database Reliability Engineering: Designing and Operating
Resilient Database", O'Reilly Publisher, 2017
Tutorial Point, 2018, Database Testing [Interactiv] Available at:
https://www.tutorialspoint.com/software_testing_dictionary/database_testing.htm [Access at
December 2018].
Tutorial Point, 2018, SQL - SELECT Query [Interactiv] Available at:
https://www.tutorialspoint.com/sql/sql-select-query.htm [Access at December 2018].
w3schools.com, 2018. SQL UPDATE Statement. [Interactiv] Available at:
https://www.w3schools.com/sql/sql_update.asp [Access at December 2018].
w3schools, 2018. SQL DELETE Statement. [Interactiv] Available at:
https://www.w3schools.com/sql/sql_delete.asp [Acces at December 2018].