QAC020C155A: Database Development & Testing Report with SQL Queries

This report provides a comprehensive overview of database development and testing, focusing on SQL language implementation. It includes SQL statements for listing employees, displaying salaries with department names, and showing HR department staff. The report also covers database testing aspects such as data mapping verification, ACID property validation, and business rule verification. Practical examples of field size validation, handling NULL values, primary key constraints, and foreign key constraints are demonstrated using SQL queries on an employee database. Screenshots illustrating database constraints are also included. Referencing and bibliography are provided to support the information presented in the report.

1
Submission Coversheet (All Programmes)
Student ID Number(Do not include student
name as anonymous
marking is implemented)
COR17454166
Programme Title BSc Computing Technologies (Day)
Module Title Data Modelling & SQL Language
Module Code
(listed on
Moodle and in LTAFP) QAC020C155A
Module Convener Sharjeel Aslam
Coursework Title Course 2
Academic Declaration:Students are reminded that the electronic copy of their essay may be checked, at
any point during their degree, with Turnitin or other plagiarism detection software
for plagiarised material.
Word Count 1667 Date
Submitted 19/12/2018
COR17454166

2
Contents
Database Development Report....................................................................................3
Database Testing..........................................................................................................8
Testing on Employee Database...................................................................................9
Referencing and Bibliography....................................................................................13
COR17454166

3
Database Development Report
Please note there are some employees without title that are not displayed in the
table.
There are some employees without department and salary. However they are not
displayed here in this table.
COR17454166

4
There is only one employee in the department Human resources.
The name of all departments are displayed along with the name (Microsoft, 2018a).
Year function has been used here (Refsnes Data, 2018a).
COR17454166

5
UPDATE titles SET title ='Admin' from employees full join titles on
employees.emp_no = titles.emp_no where last_name = 'Bamford';
Select * from titles inner join employees on titles.emp_no = employees.emp_no;
Information regarding update and join statement has displayed using he SQL queries
update (Tech on the Net, 2018a) and (Ben Nadel, 2018).
DELETE from dept_emp where EXISTS (select * from employees, departments
where dept_emp.emp_no = employees.emp_no and departments.dept_no =
dept_emp.dept_no and departments.dept_no = 'd004' and employees.emp_no =
'10003');
COR17454166

6
select * from employees, departments, dept_emp where dept_emp.emp_no =
employees.emp_no and departments.dept_no = dept_emp.dept_no and
departments.dept_no = 'd004' and employees.emp_no = '10003';
10003 has been successfully removed from the tables.
SELECT * FROM `emp_details`
There are some employees who are not working in any department and has not
assigned with any manager. The salary of listed managers are displayed.
COR17454166

7
SELECT * FROM `dept_managers`
UPDATE salaries, employees, dept_emp
SET salary = salary * 1.1
WHERE employees.emp_no = salaries.emp_no and employees.emp_no =
dept_emp.emp_no and dept_emp.dept_no = 'd001';
SELECT * FROM `salaries`, employees WHERE employees.emp_no =
salaries.emp_no
COR17454166

8
From this it can be understood that there is no employee from department marketing
(Microsoft, 2018b).
Database Testing
Database testing plays a very crucial part of software testing. Database
testing is important for ensuring that all the values associated with an application has
retrieved or stored into the database accurately. It is important to ensure that the
tester is having the knowledge regarding SQL and the techniques used in SQL (Test
Triangle, 2012).
The reason behind the wide use of database testing is it offers different IT
companies with effective solution, this are explained below:
For verifying data mapping
Mainly all the application contains 3 layers that are UI, business and
database. UI helps in capturing the interactions, the business layer helps in
processing this interaction and finally all the data are mapped and saved in the
database. This type of interaction with database helps in manipulating a lot of data
and hence it is needed to be scrutinized. The front end interaction taking place within
the system and this triggers the functions such as create, update, delete or read
action for the data stored within the database. This interactions are further compared
to idea behaviour that are mentioned within the requirement document. Moreover
verifying data mapping helps by decreasing the need for intense manual testing.
Validation of ACID property:
COR17454166
U
I
BUISNESS
DATABASE

9
Database testing helps us to ensure that every database is being developed
by following the ACID properties.
 Atomicity: this helps in providing software assurance as it depends on the rule
“all or nothing”.
 Consistency: all transaction needs to be consistence and only valid
information needs to be stored.
 Isolation: multiple transaction does not affect any single transaction
 Durability: after the transaction has occurred the data cannot be manipulated
by any external factors such as power outage or system crashes.
Verifies business rules
It is important for every organization to ensure that the database interaction is
performed by business rules (HelpingTesters, 2018).
Testing on Employee DatabaseField size validation with an example
INSERT INTO `departments` (`dept_no`, `dept_name`) VALUES ('d000000002',
'test1');
The above query is used for testing. The test results are provided below:
COR17454166
ATOMACITY CONSISTENCY
ISOLATION DURABILITY

10
It is observed that the data in field is truncated.
Output:
SELECT * FROM `departments`
Not Null values with an example
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`,
`gender`, `hire_date`) VALUES ('123', '', '', '', '', '');
The above query is used for testing. The test results are provided below:
COR17454166

11
Output
SELECT * FROM `employees`
Primary Key with example
COR17454166

12
Result is that duplicate entry into database is not allowed.Foreign Key with example
INSERT INTO `salaries` (`emp_no`, `salary`, `from_date`, `to_date`) VALUES
('1007', '200', '2017-08-22', '2018-07-16');
The above query is used for testing. The test results are provided below:
Screenshots in employee database with contraints
COR17454166