QAC020C155A Data Modeling & SQL Language Database Development Report

This report provides a comprehensive overview of data modeling and SQL language, focusing on database development and testing. It includes SQL queries for retrieving and manipulating data from an employee database, covering tasks such as selecting employee information, filtering data based on hire dates and department, updating employee titles, and deleting records. The report also addresses the importance of database testing, including field size validation, handling null values, and primary key constraints, with examples from the employee database. The report references various academic sources to support its findings and methodologies. Desklib provides a platform for students to access similar solved assignments and past papers for further learning.

Data Modelling & SQL Language
Task 1
1. Employees with their full names, hire date and title.
SELECT employees.first_name, employees.last_name, employees.hire_date, titles.title
/*selecting the data from table*/
FROM employees, titles /*querying data tables*/
WHERE employees.emp_no=titles.emp_no /*relating table employee and title using as the
foreign key in titles and its the primary key in employees*/
ORDER BY employees.emp_no /*format of ordering displaying data*/ (Galindo et al., 2011)
2. Salary of all employees and their department name.
SELECT salaries.salary, departments.dept_name /*data query*/
FROM salaries, departments /*db tables*/
Sample output

3. Full names and genders of HR department staff.
SELECT employees.first_name, employees.last_name, employees.gender FROM employees,
departments
WHERE dept_name IN ('Human Resources');

4. All departments’ name and their departments’ managers.
SELECT departments.dept_name, dept_manager.emp_no, dept_manager. from_date,
dept_manager.to_date
FROM departments, dept_manager
WHERE departments.dept_no=dept_manager.dept_no
ORDER BY departments.dept_no;

5. Department managers who were hired after 1986
SELECT employees.first_name, employees.last_name, employees.hire_date
FROM employees
WHERE hire_date > '1986-12-31' /*greater than date provided do it will start at 1987*/
6. SQL statement to change any employee’s title where the employee has just phoned in
with his/her last name.
UPDATE titles /*table name to be updates*/
SET title = 'New Name' /*set name to update*/
WHERE emp_no=10002; /*row to make changes to*/ (Mishra, Koudas and Zuzarte, 2010)

7. Delete employee’s record who belongs to department 'd004' and ID 10003.
DELETE FROM dept_emp
WHERE dept_no='d004' AND emp_no=10003

8. View to list full names of all employees, their department managers and salaries.
CREATE VIEW NewDBView AS
SELECT employees.first_name, employees.last_name, departments.dept_name,
dept_manager.dept_no, salaries.salary
FROM employees, departments, dept_manager, salaries WHERE
employees.emp_no=salaries.emp_no AND
dept_manager.dept_no=departments.dept_no;
Sample output

9. View to list all departments and their department managers, who were hired between
1980 and 1990.
CREATE VIEW V2 AS
SELECT employees.first_name, employees.last_name, employees.hire_date,
departments.dept_name FROM employees, departments
WHERE hire_date BETWEEN '1980-01-01' AND '1990-01-01'; (Kaminsky, Arena, and
Myers, 2015)
Sample output

10. Increase salaries of all employees up to 10% who are working in marketing
department
UPDATE salaries
SET salary=(salary+(salary*0.1)) (Ganski and Wong, 2009)
Task 2.
1. Importance of database testing.
Database testing involves testing of the stored data in the database. For the process to occur,
some knowledge that is in depth is needed for the specified application and an approach that is
pre-planned in order to test the data. It is important at a time the application contains data
storage that is persistent, centralized data control, data redundancy control, integrity and
consistency control, comprises of support for multiple user, practice of sharing of data,
documentation of data, autonomy of data, data access control as well as when a client requires
backup, security and data recovery (Chan and Cheung, 2009).
Database testing has a number of importance which include the following:
 It eases the calls complexity to the backend of the database. This is achieved by the
developers when they increase the use of procedures that are stored as well as view.
 The robustness of data is increased when the backend testing is performed.

 The components of the database are tested regularly to ensure that no incorrect or
harmful data is stored in the database. This may occur as the data stored in the database
is brought by from a number of applications.
 It is also important to carry out testing as the Views and Stored procedures comprises of
vital tasks like the sales data and insertion of details of the customers such as their
contact information and the names. Therefore, it’s essential to carry out testing to ensure
the security of the data.
 The errors which can’t detected in the testing of the frontend are discovered while testing
the database.
 Database testing enables testing of functions that may not be tested by frond end
application.
For example, when a tester updates, searches and saves among other operations, one must
ensure that the operations are accurate and they have performed actions on the respective
tables and that the records are updated. For example, in our employees’ database, we will
delete a record and check if it is updated.
In the example below, we generate a query to delete the employee 10001;
After deleting, we run the table again to check if it’s updated;

The table is displayed with the deleted row missing.
Another reason why database is essential is so as to ensure that the ACID properties which
are the Atomicity, Consistency, Isolation and durability are maintained (Tuya et al., 2012).
2. Employees database testing
i. Field size validation
In the employees table in the employees’ database, the size of the first_name is 14
characters. We are going to test the first_name “Duangkaew” which has 9 characters. If we
add more characters to the name which are more than 14, will see how it responds.

When I add the characters up to 20 and then save it, it only saves up to the set 14 characters
only (Klein, 2014).
ii. Not null values
If we look at the employees table once again, all the attributes are set to not null.
When we try to insert some data and then some of them are null, the insertion fails.

iii. Primary key
When validating the primary key, if the tester inserts a value that is duplicate to the primary
key in the column of the primary key, a sqlexception is thrown. In the employees table, the
primary key is the emp_no.
When the same value in the emp_no is inserted, an error occurs (Sallakonda, Bear and
Ayyamperumal, 2011).