Data Modelling & SQL Language Report - Database Development, 2018/19

This report, submitted as part of a Data Modelling & SQL Language module, addresses key aspects of database development. The report begins with an exploration of Structured Query Language (SQL), including the UPDATE and DELETE statements, along with the GRANT and REVOKE commands, providing syntax and examples for each. The core of the report focuses on data manipulation, presenting and explaining ten SQL statements designed to query and modify data within a relational database. These statements cover various operations such as joining tables, filtering data based on specific criteria, updating data, and deleting records. Finally, the report delves into database testing, discussing the importance of testing and providing an overview of various testing methods, including field size validation, handling of null values, check constraints, and referential integrity. The bibliography provides a list of relevant sources. The report is a comprehensive overview of the key concepts in database management systems, providing practical examples of SQL queries and highlighting the importance of thorough database testing.

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

1
DATA MODELLING & SQL LANGUAGE
Table of Contents
Task 1: Structure Query Language (SQL)......................................................................................2
Answer to Question 1:.................................................................................................................2
Answer to Question 2:.................................................................................................................3
Task 2: Data Manipulation..............................................................................................................5
SQL Statement 1:.........................................................................................................................5
SQL Statement 2:.........................................................................................................................6
SQL Statement 3:.........................................................................................................................6
SQL Statement 4:.........................................................................................................................7
SQL Statement 5:.........................................................................................................................8
SQL Statement 6:.........................................................................................................................8
SQL Statement 7:.........................................................................................................................9
SQL Statement 8:.........................................................................................................................9
SQL Statement 9:.......................................................................................................................10
SQL Statement 10:.....................................................................................................................10
Task 3: Database Testing...............................................................................................................11
Answer to Question 1:...............................................................................................................11
Answer to Question 2:...............................................................................................................12
Bibliography..................................................................................................................................14

2
DATA MODELLING & SQL LANGUAGE
Task 1: Structure Query Language (SQL)
Answer to Question 1:
The UPDATE statement is basically utilized for updating either the data or rather row within a
particular table.
The syntax of the command of UPDATE is written below:
Update table-name set column-name = value where any condition;
Example
Let us imagine that there is a table which has been named as employee
Department_No Department_Name Employee_No Employee_Name
5 IT E1 Harry
10 IT E2 Tom
15 Finance E6 Kate
20 Management E8 David
25 Finance E10 Alice
For updating this table the query which can be utilized:
Update employee set Employee_No = E4 where Department_No = 15
This query will be producing the result:
Department_No Department_Name Employee_No Employee_Name
5 IT E1 Harry
10 IT E2 Tom

3
DATA MODELLING & SQL LANGUAGE
15 Finance E4 Kate
20 Management E8 David
25 Finance E10 Alice
The DELETE statement is utilized for the deletion of row from the entire table within the
database
Syntax:
DELETE from table-name
Example:
Let us consider the same table named employee which has been presented above.
DELETE from employee where Department_No = 20
This query will be producing the result:
Department_No Department_Name Employee_No Employee_Name
5 IT E1 Harry
10 IT E2 Tom
15 Finance E6 Kate
25 Finance E10 Alice

4
DATA MODELLING & SQL LANGUAGE
Answer to Question 2:
The administrator of database actually defines the specific command of GRANT in the SQL for
providing with the particular access to all the various users of the entire database.
Syntax:
GRANT privilege_name
ON object_name
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];
Example:
GRANT SELECT ON employee to user1;
This specific command will be actually granting the specific permission SELECT on the table of
employee to the user1. The option of WITH GRANT must be utilized very much carefully due to
the fact like for an example if GRANT SELECT privilege on the table of employee to the user1
will be utilizing the option of WITH GRANT, then it can be GRANT SELECT privilege upon
the specific employee table towards some other user like user 2 and so on.
The REVOKE command will be actually removing all the rights of the user access or rather
privileges to all the various objects of the database.
Syntax:
REVOKE privilege_name
ON object_name

5
DATA MODELLING & SQL LANGUAGE
FROM {user_name |PUBLIC |role_name}
Example:
REVOKE SELECT ON employee FROM user 1;
This particular command will actually REVOKE a SELECT privilege on the particular table of
employee from the user 1. Whenever anyone will REVOKE SELECT privilege upon the
particular table from a specific user, the user will not at all be able to SELECT data from that
specific table anymore. However, if any user will be receiving the privileges of SELECT on that
specific table from about more than one single user then he/she can be actually able to SELECT
from that specific table till each and everyone who will be granting the permission actually
revokes it. Anyone cannot be able to REVOKE privileges if they have not been granted initially.
Task 2: Data Manipulation
SQL Statement 1:
select CONCAT (first_name, ' ' , last_name) as fullname, employees.gender, salaries.salary from
employees Inner Join salaries on employees.emp_no = salaries.emp_no;

6
DATA MODELLING & SQL LANGUAGE
SQL Statement 2:
SELECT titles.title, departments.dept_name From titles join dept_emp ON titles.emp_no =
dept_emp.emp_no join departments ON departments.dept_no = dept_emp.dept_no

7
DATA MODELLING & SQL LANGUAGE
SQL Statement 3:
select CONCAT (first_name, ' ' , last_name) as fullname, employees.gender From employees
join dept_emp ON employees.emp_no = dept_emp.emp_no join departments ON
departments.dept_no = dept_emp.dept_no WHERE dept_emp.dept_no = 'd004'
SQL Statement 4:
SELECT departments.dept_name, CONCAT (first_name, ' ' , last_name) as fullname From
employees join dept_emp ON employees.emp_no = dept_emp.emp_no join departments ON
departments.dept_no = dept_emp.dept_no;

8
DATA MODELLING & SQL LANGUAGE
SQL Statement 5:
SELECT departments.dept_name, CONCAT (first_name, ' ' , last_name) as fullname From
employees join dept_manager ON employees.emp_no = dept_manager.emp_no join departments
ON departments.dept_no = dept_manager.dept_no
WHERE dept_manager.from_date > ('1990-1-1');

9
DATA MODELLING & SQL LANGUAGE
SQL Statement 6:
Write an SQL statement to change any employee’s date of birth. Assume the employee has just
phoned in with his/her last name.
UPDATE employees
SET birth_date='1953-09-04'
WHERE last_name='Facello'
SQL Statement 7:
UPDATE salaries
SET salary = salary * 0.55
WHERE from_date > '1997-01-01'
SQL Statement 8:
Write an SQL statement to delete employee’s record who belongs to department 'd004' and ID is
10003.
DELETE employees

10
DATA MODELLING & SQL LANGUAGE
FROM employees
LEFT JOIN
dept_emp ON employees.emp_no = dept_emp.emp_no
WHERE dept_no = 'd004' && employees.emp_no = '10003'
SQL Statement 9:
select CONCAT (first_name, ' ' , last_name) as fullname, departments.dept_name, salaries.salary
From employees join dept_emp ON employees.emp_no = dept_emp.emp_no join departments
ON departments.dept_no = dept_emp.dept_no JOIN salaries ON salaries.emp_no =
dept_emp.emp_no

11
DATA MODELLING & SQL LANGUAGE
SQL Statement 10:
SELECT employees.first_name, departments.dept_name From employees join dept_emp ON
employees.emp_no = dept_emp.emp_no join departments ON departments.dept_no =
dept_emp.dept_no WHERE
employees.emp_no = dept_emp.emp_no AND
employees.hire_date BETWEEN '1990-1-1' AND '2000-1-1'

12
DATA MODELLING & SQL LANGUAGE
Task 3: Database Testing
Answer to Question 1:
Database testing is known to be actually comprising of a procedure which will be
layered, involving the layer of user interface, business and the data access. It will be involving
the database itself as well. The particular layer of UI is known to actually dealing with that of the
designing of the interface of the database, while the layer of business will be involving all the
various databases supporting all the different strategies of the business.
The testing of database is considered to be very much essential aspect of the testing of
software. A testing of database is considered to be very much essential for ensuring that all the
several values which a particular application will be either retrieving or rather storing within the
database are exactly accurate or not. For testing a database, it is really very much essential that
the tester must possess a knowledge of sound of SQL. In all the various companies of IT as well
as all the several companies of testing, testing of database cannot at all be conducted until and
unless the tester will be able to properly understand the entire application fully. The tester must
be knowing which of the transactions will be actually making what specific kind of alterations
within the database and after that only he/she can be going ahead with that of the testing.
Generally, all the various are provided with a separate database with a lot of dummy data for the
conduction of the all the several checks.
Answer to Question 2:
Field Size Validation

13
DATA MODELLING & SQL LANGUAGE
The filed size of each of the rows in the employees table is valid and for the emp_no integer (11),
birth_date date, first_name varchar (14), last_name varchar (16), gender, (M/F), hire_date date is
taken that is sufficient for meeting the current and future needs of the database
Null Values
The table is create such that no NULL values can be inputted into it since the NULL values can
generate error during joining the table and show inappropriate result for a query. The above
screenshot shows the NULL column and here it is set to no for all the attributes.

14
DATA MODELLING & SQL LANGUAGE
Check Constraints
The constraints are checked from the designer option and selecting the database for analyzing the
primary and the foreign key used for joining the tables.
Referential Integrity
For checking the referential integrity of the tables created in the employees database operations
option of my sql is selected and the structure of the table is analyzed for identification of the
status of each of the tables under the database.

15
DATA MODELLING & SQL LANGUAGE
Bibliography
Atzeni, Paolo. "Data Modelling in the NoSQL world: A contradiction?." In Proceedings of the
17th International Conference on Computer Systems and Technologies 2016, pp. 1-4. ACM,
2016.
Bhaiya, Lalit Kumar P. "STATISTICAL MODELLING FOR SQL INJECTION
ATTACK." Journal of Global Research in Computer Science 9, no. 4 (2018): 01-06.
Bilal, Muhammad, Lukumon O. Oyedele, Kamran Munir, Saheed O. Ajayi, Olugbenga O.
Akinade, Hakeem A. Owolabi, and Hafiz A. Alaka. "The application of web of data technologies
in building materials information modelling for construction waste analytics." Sustainable
materials and technologies 11 (2017): 28-37.
Byrne, B., David Nelson, and Renuga Jayakumar. "Big Data Technology-Can We Abandon the
Teaching of Normalisation?." (2017): 510-517.
Costa, Eduarda, Carlos Costa, and Maribel Yasmina Santos. "Efficient Big Data modelling and
organization for Hadoop hive-based data warehouses." In European, Mediterranean, and Middle Eastern
Conference on Information Systems, pp. 3-16. Springer, Cham, 2017.
Jukic, N., Vrbsky, S. and Nestorov, S., 2016. Database systems: Introduction to databases and
data warehouses. Prospect Press.
Ramesh, Dharavath, Ashay Sinha, and Suraj Singh. "Data modelling for discrete time series data
using Cassandra and MongoDB." In 2016 3rd International Conference on Recent Advances in
Information Technology (RAIT), pp. 598-601. IEEE, 2016.

16
DATA MODELLING & SQL LANGUAGE
Sharma, Sugam, Ritu Shandilya, Srikanta Patnaik, and Ashok Mahapatra. "Leading NoSQL
models for handling Big Data: a brief review." International Journal of Business Information
Systems 22, no. 1 (2016): 1-25.