Data Modelling & SQL Language : Assignment

Data Modelling & SQL Language: Part 2
1

Contents
INTRODUCTION...........................................................................................................................3
TASK 1............................................................................................................................................3
Structure Query Language...............................................................................................................3
Describe the two DDL statement within an appropriate example and syntax applicable in
DBMS..........................................................................................................................................3
Discuss about the Data control language statement within the appropriate example and syntax.
.....................................................................................................................................................4
TASK 2............................................................................................................................................6
Data Manipulation...........................................................................................................................6
TASK 3..........................................................................................................................................10
Discuss about the database testing.............................................................................................10
why database testing is an essential...........................................................................................10
Describe the testing template and screenshots of testing process on the employee database. . .11
CONCLUSION..............................................................................................................................14
REFERENCES..............................................................................................................................18
2

INTRODUCTION
Database management system is defined as a collection of data that enables it’s the user to
access the database, manipulate data and also representing in effective manner. It useful for
controlling and managing the overall processing of database system.
This report will discuss about the structure query language which help to design tables,
columns in the system. It can be used the two different type of SQL statement and contain
specific syntax for generating appropriate results. Furthermore, it will describe the database
testing and how it is important for data management.
TASK 1
Structure Query Language
Describe the two DDL statement within an appropriate example and syntax applicable in DBMS.
DDL stands for data definition language that contain the multiple SQL command that will
define the data schema (Egea and Dania, 2019). It is mainly deals with the proper explanation of
data schema to create table and modify the overall structure in the form of objects.
For Example-
 Create command useful for creating database tables and its objects.
 Alter is mainly applicable to alter the structure in the database system.
 Drop can delete the objects from its database system.
Alter command
3

Create command
Discuss about the Data control language statement within the appropriate example and syntax.
A data manipulation is based on the programming language that can use for modifying,
deleting and adding the data in the database system (GoliMalekabadi, Sargolzaei, Javan and
Akbari, 2016). It can be permitting the user for manipulate the information in DBMS which
includes insert the data into the tables, existing data and also modifying the records.
For Example-
 Select command can use to retrieve the records from DBMS table and perform the
different operations.
 Delete command can use to eliminate multiple record from DBMS table as per the
specified conditions.
 Update SQL command can be modified data in the different records and apply an
appropriate syntax.
4

Data control language
It is based on the programming language that can applicable in the database system to
easily access the information and store in proper manner. It is the component of SQL to perform
the different operations (Jukic, Vrbsky and Nestorov,
2016). It is privilege that may revoke and grant from the developer and implementing the
different operations by using DML and DDL. It may always involve different statement such as
delete, usage, select, update, insert and execute etc.
DCL command is mainly used to enforce the DBMS security in the different users in the
environment. There are two types of DCL SQL command: Revoke and Grant. The user is only
providing the privilege to another objects.
On the other hand, Grant is another command that must be used to give privilege for
access the objects in DBMS.
Grant command:
SQL GRANT command is basically used for access or privileges on the database objects
to the users.
Syntax
GRANT Privilege_name
On object_name
To {user_name / public / role name}
[With Grant option];
 Privilege name is used for providing the privilege granted to users where it can execute
all operations.
 Object_name is considered the database object such as stored, table, sequence and view
etc.
 User_name is a person those who are accessing operations.
 Public is mainly used for providing access rights of users.
For Example-
If Admin wanted to grant insert, update, select and update privileges on the database table
such as employee to user name James. Use Grant statement:
5

If admin will use ALL keywords that indicate that they wish to grant permission of select, insert,
update, delete and references. For example-
If admin wanted to grant only select access on employee table and users where they could have
grant privilege to public.
Revoke Command:
The Revoke command is basically used to remove user access privileges and rights in
database objects.
Syntax
Revoke Privilege_name
On object_name
From {user name/ public role name}
 Privilege name is used for providing the privilege granted to users where it can execute
all operations.
 Object_name is considered the database object such as stored, table, sequence and view
etc.
 User_name is a person those who are accessing operations.
 Public is mainly used for providing access rights of users.
For Example-
If admin wanted to revoke delete privileges on employee table from user name is
Anderson so that it can use command such as:
If revoke all permissions of users related select, insert, delete and references.
6