QAC020C155A - Data Modelling & SQL Language: Database Development

This report focuses on data modeling and SQL language, covering essential aspects of database development. It includes an ER diagram, database loading, data insertion, and SQL queries for various tasks such as listing employees, showing salaries by department, and managing department staff. The report also delves into database testing, emphasizing its importance for functionality and stability, and detailing structural, functional, and non-functional testing methods. Specific tests on an employee database, including field size validation, null value checks, primary key constraints, and foreign key checks, are presented with SQL queries and results. The report concludes with database view creation and salary updates, providing a comprehensive overview of database management and testing.

Running head: DATA MODELLING & SQL LANGUAGE
Data Modelling & SQL Language
Name of the Student:
Name of the University:
Author note

1
DATA MODELLING & SQL LANGUAGE
Table of Contents
Task 1: Data Manipulation.........................................................................................................2
ER Diagram (StarUML).........................................................................................................2
Loading the database on the server:.......................................................................................2
Inserting data into the database..............................................................................................3
Structured Query Language...................................................................................................3
Task 2: Database Testing...........................................................................................................9
Importance of Database Testing.............................................................................................9
Testing Process on the Employee Database.........................................................................10
Bibliography.............................................................................................................................15

2
DATA MODELLING & SQL LANGUAGE
Task 1: Data Manipulation
ER Diagram (StarUML)

3
DATA MODELLING & SQL LANGUAGE
Loading the database on the server:
Inserting data into the database
Structured Query Language
Write an SQL statement to list all employees with their full names, hire date and title
Query 1
SELECT titles.title, CONCAT(`first_name` ,' ', `last_name`) As 'full name',`hire_date`
FROM employees INNER JOIN titles ON employees.emp_no = titles.emp_no;

4
DATA MODELLING & SQL LANGUAGE
Write an SQL statement to show the salary of all employees and their department name.
Query 2
SELECT employees.emp_no, CONCAT(employees.`first_name` ,' ', employees.`last_name`)
As 'full name',salaries.salary, departments.dept_name
FROM employees
INNER JOIN salaries ON employees.emp_no = salaries.emp_no
INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
INNER JOIN departments ON dept_emp.dept_no = departments.dept_no;
Write an SQL statement to show the full names and genders of HR department staff.
Query 3

5
DATA MODELLING & SQL LANGUAGE
SELECT `gender`,CONCAT(`first_name` ,' ', `last_name`) As 'full name',
departments.dept_name FROM employees JOIN dept_emp ON dept_emp.emp_no =
employees.emp_no JOIN departments ON dept_emp.dept_no = departments.dept_no
WHERE departments.dept_name = "Human Resources";
Write an SQL statement to show the all departments’ name and their departments’
managers.
Query 4
SELECT departments.dept_name, CONCAT(employees.`first_name` ,' ',
employees.`last_name`) As 'full name'
FROM departments
INNER JOIN dept_manager ON dept_manager.dept_no = departments.dept_no
INNER JOIN employees ON employees.emp_no = dept_manager.emp_no;
Write an SQL statement to show a list of department managers who were hired after 1986

6
DATA MODELLING & SQL LANGUAGE
Query 5
SELECT departments.dept_name, CONCAT(employees.`first_name` ,' ',
employees.`last_name`) As 'full name' FROM departments INNER JOIN dept_manager ON
dept_manager.dept_no = departments.dept_no INNER JOIN employees ON
employees.emp_no = dept_manager.emp_no WHERE dept_manager.from_date < '1986-00-
00';
Write an SQL statement to change any employee’s title. Assume the employee has just
phoned in with his/her last name.
Query 6
It has been assumed that employee who has just phoned in with the emp_no 10005.
Hence the query has been structured for the specified employee.
SELECT title FROM `titles` WHERE emp_no = 10005;
UPDATE title SET title = "New Category" WHERE emp_no = 10005;

7
DATA MODELLING & SQL LANGUAGE
SELECT title FROM `titles` WHERE emp_no = 10005;
Write anSQL statement to delete employee’s record who belongs to department 'd004' and
ID 10003.
Query 7
DELETE FROM employees WHERE emp_no = 10003;
Create a database view to list full names of all employees, their department managers and
salaries
Query 8
CREATE VIEW employeesManagement
AS SELECT CONCAT(employees.`first_name` ,' ', employees.`last_name`) As 'full name',
dept_manager.emp_no As 'manager', salaries.salary
FROM employees
INNER JOIN dept_manager ON employees.emp_no = dept_manager.emp_no
INNER JOIN salaries ON employees.emp_no = salaries.emp_no;

8
DATA MODELLING & SQL LANGUAGE
Create a database view to list all departments and their department managers, who were
hired between 1980 and 1990
Query 9
CREATE
VIEW `departmentManagement`
AS SELECT departments.dept_name, CONCAT(employees.`first_name` ,' ',
employees.`last_name`) As 'full name' FROM departments INNER JOIN dept_manager ON
dept_manager.dept_no = departments.dept_no INNER JOIN employees ON
employees.emp_no = dept_manager.emp_no WHERE dept_manager.from_date > '1980-00-
00' AND dept_manager.from_date < '1990-00-00';
Write an SQL statement to increase salaries of all employees up to 10% who are working
in marketing department

9
DATA MODELLING & SQL LANGUAGE
Query 10
UPDATE
salaries
INNER JOIN employees ON employees.emp_no = salaries.emp_no
INNER JOIN dept_emp.emp_no = employees.emp_no
INNER JOIN departments ON dept_emp.dept_no = departments.dept_no
SET
salaries.salary = (salaries.salary*1.1)
WHERE
departments.dept_name = "Marketing";
Task 2: Database Testing
Importance of Database Testing
Database Testing is very important in order the check the functionalities of the
database. The designer would be sure about design of the database and also the designer
would be able to develop the database if there is a further requirement of the database

10
DATA MODELLING & SQL LANGUAGE
needing improvements. With database testing, the data base designer can check the overall
health and stability of databases, stored as master data as well as procedures and business
logic to ensure quality performance and continuous contribution to key business processes.
 Diagnosis of specific database on server
 Industry standard benchmarks testing of databases
 Managing and governing database resources and their utilization
There are three type of the database testing and discussions of which has been
included in this report.
 Structural database testing
 Functional testing
 Non-functional testing
The structural testing method for the database has been used for the testing and
verification of the tables and the columns that have been used in the database. The testing
involves tests which are done on the components of the database are not available to the users
for changing it. In addition to this the functional data testing process involves the process of
testing which is done from the point of view of an end user. The functional testing process
involves further processes which is white box testing and black box testing. The functionality
testing is one concern of the Black box testing process of the database, whereas the White
box testing basically deals with the structure of the database. Non-functional Testing process
involves the testing procedures of performing the load testing, stress testing, checking
minimum system requirements so that the minimum requirements of the system can be
checked very easily so that it is seen that the business requirements of the organization which
implements the database are met.
For example, a Banking application is considered. The application is used for the
transactions by the users. The applications store the details of the transactions made by the
users and the correct information is displayed to the user. In case of an error incorrect data
will be displayed to the users who are using the system. The system should also make sure

11
DATA MODELLING & SQL LANGUAGE
that the information in the system are not lost due to any type of error in the system. The
authorization of the system is also done with the help of a database and hence the access
control feature is to be described efficiently in the database. For ensuring each and every
criterion described it is important the database testing process is done and ensure that data
testing and data validation processes are performed efficiently.
Testing Process on the Employee Database
The testing process involves the four types of test that are listed below:
Field size validation
The field size validations are used for the constrains of the field size which have been
used for the development of the tables in the database. The department table has been used in
this instance for the discussions of the validation in the report. The dept_no is defined as a
char(4), meaning the data cannot be longer than eight bytes.
The following query is run in the database:
INSERT INTO `departments` (`dept_no`, `dept_name`) VALUES ('@123', 'New');
The query is executed efficiently in the server.
Not Null values
For checking the null values in the database the employees field has been considered.
At first the query provided below has been described.
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`,
`hire_date`) VALUES ('10015', '', 'Steve', 'Rogers', 'F', '2018-04-17');