Data Modelling & SQL Language

This report discusses data manipulation, ER diagrams, SQL language, and database testing. It covers topics such as field size validation, primary and foreign keys, and the importance of database testing. The report also includes SQL queries for various tasks. The subject is Data Modelling & SQL Language and the college/university is not mentioned.

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');

12
DATA MODELLING & SQL LANGUAGE
The data was inserted in the table however the data of the field was truncated. The
data can be displayed with a select query.
SELECT * FROM `employees`;
The next query used on the system for checking the null value fields is displayed
below:
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`,
`hire_date`) VALUES ('10018', '1994-11-23', '', 'Rogers', 'F', '2018-04-17');

13
DATA MODELLING & SQL LANGUAGE
The data was inserted successfully into the table. However, an empty field was left in the
table.
Primary key
The primary key is that specially selected single candidate key (among all other
candidate keys of this entity) which is to be used to uniquely identify the current entity in
relations. When implemented as table per entity, the primary key of the current entity
becomes a foreign key in another entity table, identifying the relationship between the
entities. The primary key helps in indexing the tables and also removes the chance of delicacy
in the table in the database. For the illustration of the constrain an example has been used and
the departments table has been used for the illustration in the report:
INSERT INTO `departments` (`dept_no`, `dept_name`) VALUES ('d049', 'New
Department');
The above designed query was used in the table for inserting and the data was inserted
successfully.
It can be seen that the row is inserted successfully into the database as there are no
data having value d049 in the table. Hence for checking the table further a query has been
used.
INSERT INTO `departments` (`dept_no`, `dept_name`) VALUES ('d004', 'New Test');

14
DATA MODELLING & SQL LANGUAGE
An error message is displayed in the system. The system is preventing the user from entering
a data which is a duplicate one for the database. Hence the primary key constrains are
maintained in the database.
Foreign Key
The foreign keys are the candidate keys which are used in the database for the
development of the references across different tables in the database. The foreign key checks
are also very checking process for any type of database. The foreign key also helps in
fetching the data from the database very efficiently. Hence, a foreign key testing has been
done on the database to check if all the designed keys are functioning properly on the
database. For the illustration the dept_emp table has been used in this report. The first used
for inspection of the table is provided below:
INSERT INTO `dept_emp` (`emp_no`, `dept_no`, `from_date`, `to_date`) VALUES ('1019',
'd005', '1987-02-14', '9999-00-00');
The data was inserted successfully in the table
Hence another test query was used:
INSERT INTO `dept_emp` (`emp_no`, `dept_no`, `from_date`, `to_date`) VALUES ('109',
'd023', '1987-02-14', '9999-00-00');

15
DATA MODELLING & SQL LANGUAGE
It can be seen that an error is faced asking the user for a valid foreign key.

16
DATA MODELLING & SQL LANGUAGE
Bibliography
Broy, M., Kirstan, S., Krcmar, H. and Schätz, B., 2014. What is the benefit of a model-based
design of embedded software systems in the car industry?. In Software Design and
Development: Concepts, Methodologies, Tools, and Applications (pp. 310-334). IGI Global.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
du Plessis, H. and Van Niekerk, A., 2014. A new GISc framework and competency set for
curricula development at South African universities. South African Journal of Geomatics,
3(1), pp.1-12.
El-Halees, A.M. and Kehail, E.O., 2017. Integrate Database Design Techniques with Agile
Applications.
Mahajan, S., Abrol, P. and Lehana, P.K., 2016. Design and Development of 3D Insect
Modeling, Identification, and Dynamic Database Updation. International Journal of
Scientific and Technical Advancements, 2(2), pp.43-46.
Mallikarjun Sharada, S., Bligaard, T., Luntz, A.C., Kroes, G.J. and Nørskov, J.K., 2017.
SBH10: A Benchmark Database of Barrier Heights on Transition Metal Surfaces. The
Journal of Physical Chemistry C, 121(36), pp.19807-19815.
Mitrovic, A. and Suraweera, P., 2016. Teaching database design with constraint-based tutors.
International Journal of Artificial Intelligence in Education, 26(1), pp.448-456.
Mohankumar, M. and Kumar, M.A., 2016. Green Database Design Model in Software
Development Life Cycle Phase. Indian Journal of Science and Technology, 9(30).

17
DATA MODELLING & SQL LANGUAGE
Nascimento, L.M.E., Ferreira, A.C.M. and Gonzalez, S.A., 2018, March. Design and
development of a geo-referenced database to radionuclides in food. In Journal of Physics:
Conference Series (Vol. 975, No. 1, p. 012045). IOP Publishing.
Parks, R.F. and Hall, C., 2016. Front-End and Back-End Database Design and Development:
Scholar’s Academy Case Study. Information Systems Education Journal, 14(2), p.58.
Stonebraker, M., Deng, D. and Brodie, M.L., 2017. Application-Database Co-Evolution: A
New Design and Development Paradigm. New England Database Day, pp.1-3.
Youseff, G.A. and Ibrahim, R.E., 2016. Design and development of web-based database for
managing NARSS projects. International Journal of Information Technology and
Management Information System, 7(2), pp.27-35.