Data Modeling and SQL Language: A Comprehensive Guide

Desklib provides past papers and solved assignments for students. This report covers SQL, database design, and testing.

DATA MODELLING & SQL LANGUAGE
TABLE OF CONTENT
LIST OF FIGURES....................................................................................................................1
INTRODUCTION......................................................................................................................2
TASK 1: Structure Query Language (SQL)...............................................................................2
DDL (DATA DEFINITION LANGUAGE)..........................................................................3
CREATE STATEMENT...................................................................................................3
ALTER STATEMENTS....................................................................................................4
DCL (DATA CONTROL LANGUAGE)..............................................................................5
TASK 2: Data Manipulation......................................................................................................6
Create a SQL statement to list all employees with their full names, gender and salary....6
Create a SQL statement to show the title of all employees and their department name....7
Create a SQL statement to show the full names and gender who belong to department
number 'd004'.....................................................................................................................7
Create a SQL statement to show the all departments and their department’s managers.. .8
Create a SQL statement to show a list of department’s managers who were hired after
1986....................................................................................................................................8
Create a SQL statement to change any employee’s date of birth. Assume the employee
has just phoned in with his/her last name...........................................................................9
Create a SQL statement to delete employee’s record who belongs to department 'd004'
and ID is 10003..................................................................................................................9
Create a database view to list the full names of all employees, their departments and
their salaries.....................................................................................................................10
Create a database view to list all departments and their department’s managers, who
were hired between 1980 and 1990..................................................................................10
Create a SQL statement to increase salaries of all employees up to 10% who are working
from 1990.........................................................................................................................11
TASK 3: Database Testing.......................................................................................................12
TESTING TEMPLATE.......................................................................................................13
CONCLUSION........................................................................................................................14
REFERENCES.........................................................................................................................14

LIST OF FIGURES
Figure 1:SQL statement to list all employees with their full names, gender and salary............7
Figure 2: SQL statement to show the title of all employees and their department name..........7
Figure 3: SQL statement to show the full names and gender who belong to department
number 'd004'.............................................................................................................................8
Figure 4: SQL statement to show the all departments and their department’s managers..........8
Figure 5: SQL statement to show a list of department’s managers who were hired after 1986.9
Figure 6: SQL statement to change any employee’s date of birth. Assume the employee has
just phoned in with his/her last name.........................................................................................9
Figure 7: SQL statement to delete employee’s record who belongs to department 'd004' and
ID is 10003...............................................................................................................................10
Figure 8: Database view to list the full names of all employees, their departments and their
salaries......................................................................................................................................10
Figure 9: Database view to list all departments and their department’s managers, who were
hired between 1980 and 1990..................................................................................................11
Figure 10: SQL statement to increase salaries of all employees up to 10% who are working
from 1990.................................................................................................................................11
INTRODUCTION
Databases are an important part of our lives so, understanding them in depth will benefit our
lives. In this assignment, we have discussed two languages in SQL that are – DDL and DCL.
We also created a database and run some queries. We discussed more on database testing and
why it’s important.

TASK 1: Structure Query Language (SQL)
DDL (DATA DEFINITION LANGUAGE)
DDL is the language which can be used to define and modify data and its structure with the
help of various commands. It creates & modifies database structural objects like tables,
indexes, schemas etc.
CREATE STATEMENT
 CREATE DATABASE
 CREATE TABLE
This is a DDL predefined statement which is used for the database as well as table
creation. It does component/object creation in the relational database management
system. With the execution of the CREATE DATABASE statement, a database is created
and now the tables can be created. With the execution of the CREATE TABLE statement,
a table is created and now the data can be inserted. To use the CREATE TABLE
statement the user must have privileges to create a table in its own database.
CREATE DATABASE <database name>;
<database name> is the name of the database.
The above statement represents the CREATE DATABASE statement format. It’s simple
to execute and just enter a database name to create it.
CREATE TABLE <table name> (Column_1 datatype column_constraints, Column_2 datatype
column_constraints…);
<table name> is the name of the table.
Column_1 is the first column.
The datatype is what type of value one wants to store in a column.
Column_constraints are the rules which are checked by further statements used in the table
operations.

The above statement represents the CREATE TABLE statement format. Datatypes are
important while creation of a column in a table. Constraints are optionally defined rules
which are checked during any query action such as UPDATE, INSERT etc. on the table
and to abort on its violation by raising an error.
CREATE DATABASE students;
The above statement creates a database named students and now this database is ready to
get some tables created in it for further data storage.
CREATE TABLE student (student_id INT, student_name CHAR, class CHAR, mobile
VARCHAR);
Above statement creates a table named the student. In the above table student_id field is
of INT datatype i.e. only integer values will be stored in the column. Another column is
named as student_name & class fields are of string datatypes & mobile column field is of
varchar datatype. To restrict NULL values getting into a particular column, the column
can hold a default value at the time of table creation.
ALTER STATEMENTS
 ADD
 DROP
 MODIFY
This statement is used for altering operations on an existing table like drop, delete, add &
modify columns. It can also be used to add various other constraints on a table which is
already present in the database.
ADD
This is used to add columns into a table which already there. It is helpful because there
are times where one needs to add more information so there is no need to create the full
database or table again, just using ADD will solve the problem.
ALTER TABLE <table name> ADD (Column 1 datatype, Column 2 datatype…);

ALTER TABLE student ADD (city CHAR);
The above statement will add a column named a city in the existing table named the
student.
DROP
This is used to drop or delete a particular column from an existing table. Whenever a
column is not needed anymore then DROP can be used to delete it from the table as well
as the database.
ALTER TABLE <table name> DROP COLUMN <column name>;
ALTER TABLE student DROP COLUMN city;
The above statement will drop/delete the column named city from the table student.
MODIFY
This is used to change or modify columns in a table that already exists. The modification
can be used on multiple columns in a table in one go.
ALTER TABLE <table name> MODIFY <column name> <column type>;
ALTER TABLE student MODIFY mobile INT;
The above statement will modify the column named mobile, present in the student table,
& will change its datatype to the INT datatype.
DCL (DATA CONTROL LANGUAGE)
 GRANT
 REVOKE

It is a language that is used in a database to control user access. Its commands are used for
security purposes. With the help of DCL, users can be allowed or restricted for accessing data
present in the database.
GRANT
This statement is used to give privileges to the user to access the database. It also
specifies the user to complete particular tasks. It includes many permissions which
can be given to a particular user like granting the user the permission to create any
table or drop any table.
GRANT <list of privileges> ON <table name> TO <list of users>;
The above statement is a format of the GRANT query used in the database.
GRANT ALL ON student TO XYZ;
The above statement gives permission to modify and view the data in the student table
to the XYZ user.
REVOKE
This statement is used when one wants to cancel or remove the formerly denied or
granted permissions. This statement will take away the privileges which were given to
the user with the GRANT statement. In simple terms, it takes user permissions back.
REVOKE <list of privileges> ON <table name> FROM <user name>;
The above statement is the format of the REVOKE statement used in the database.
REVOKE UPDATE ON student FROM XYZ;
The above statement will take back the update privileges from the XYZ user.

TASK 2: Data Manipulation
Create a SQL statement to list all employees with their full names, gender, and salary.
select concat(e.first_name,' ',e.last_name) as full_name,e.gender, s.salary from employees e left
join salaries s on e.emp_no=s.emp_no;
Figure 1:SQL statement to list all employees with their full names, gender and salary.
Create a SQL statement to show the title of all employees and their department name.
select t.title,d.dept_name from titles t, departments d, dept_emp de,employees e where
t.emp_no=e.emp_no and e.emp_no=de.emp_no and de.dept_no=d.dept_no;
Figure 2: SQL statement to show the title of all employees and their department name.

Create a SQL statement to show the full names and gender who belong to department
number 'd004'.
select concat(e.first_name,' ',e.last_name) as full_name,e.gender from employees e,dept_emp d
where e.emp_no=d.emp_no and d.dept_no = "d004";
Figure 3: SQL statement to show the full names and gender who belong to department number 'd004'.
Create a SQL statement to show all departments and their department’s managers.
select d.dept_name,concat(e.first_name,' ',e.last_name) from employees e, departments d,
dept_manager dm where dm.dept_no=d.dept_no and e.emp_no =dm.emp_no;
Figure 4: SQL statement to show all departments and their department’s managers.
Create a SQL statement to show a list of department’s managers who were hired after
1986.

select concat(e.first_name,' ',e.last_name) from employees e,dept_manager dm where e.emp_no =
dm.emp_no and YEAR(e.hire_date)>"1986";
Figure 5: SQL statement to show a list of department’s managers who were hired after 1986.
Create a SQL statement to change an employee’s date of birth. Assume the employee
has just phoned in with his/her last name.
update employees set birth_date="1952-09-02" where last_name="Facello";
Figure 6: SQL statement to change any employee’s date of birth. Assume the employee has just phoned in with his/her last
name.
Create a SQL statement to delete employee’s record who belongs to department 'd004'
and ID is 10003.
delete employees.* from employees,dept_emp where dept_emp.emp_no=employees.emp_no and
dept_emp.dept_no="d004" and employees.emp_no=10003;

Figure 7: SQL statement to delete employee’s record who belongs to department 'd004' and ID is 10003.
Create a database view to list the full names of all employees, their departments and
their salaries.
select concat(e.first_name,' ',e.last_name),d.dept_name,s.salary from employees e, departments d,
salaries s, dept_emp de where e.emp_no=s.emp_no and e.emp_no=de.emp_no and
de.dept_no=d.dept_no;
Figure 8: Database view to list the full names of all employees, their departments and their salaries.
Create a database view to list all departments and their department’s managers, who
were hired between 1980 and 1990.

select d.dept_name,concat(e.first_name,' ',e.last_name) from employees e, departments d,
dept_manager dm where dm.dept_no=d.dept_no and e.emp_no =dm.emp_no and
YEAR(e.hire_date)>1980 and YEAR(e.hire_date)<1990;
Figure 9: Database view to list all departments and their department’s managers, who were hired between 1980 and 1990.
Create a SQL statement to increase salaries of all employees up to 10% who are
working from 1990.
update salaries,employees set salary=salary*1.10 where YEAR(employees.hire_date)="1990";
Figure 10: SQL statement to increase salaries of all employees up to 10% who are working from 1990.

TASK 3: Database Testing
Databases consist of a collection of data files which are interconnected to each other.
Databases are heterogeneous in nature. Database Testing is a process which is layered. A
database system generally consists of 4 layers that are – UI layer (User Interface layer),
business layer, data access layer and the database itself. It is important to have testing of all
the different types of layers. The testing is a heterogeneous process. The database is one of
the most important parts of an application so, testing is an important process which needs
testers to expertise in monitoring tables, writing procedures and queries.
There are various testing methods such as –
 Validity of data
 Integrity of data
 Database performance
 Function, trigger and procedure tests
There are many points on which we can say that testing of databases is an important task.
MAPPING OF DATA
As we know that the data is fetched from the backend database to the frontend & vice versa,
so it's important to keep an eye on the following –
 Checking whether the column fields in the frontend forms are consistently
connected/mapped with the particular database table present in the backend.
 When an action occurs in the frontend side of the application, a Create, Retrieve,
Update & Delete action i.e. CRUD action gets started at the DB backend so the testers
should check whether the particular action is successful or not.
VALIDATIONS OF THE ACID PROPERTY
ACID stands for Atomicity, Consistency, Isolation, and Durability. Each & every database
should have these 4 properties.
Atomicity tells us about a transaction either it fails or passes. Even if one part of a transaction
fails the whole transaction will be considered as fail, this rule is known as the all-or-nothing
rule.