GROUP 16

Data Control Languages And Transaction

GROUP MEMBERS

BYNADGE J JAKARASI R227543Y HDSC

TAKUDZWA J GUMBO R227544R HDSC
TADIWANASHE HUNGWE R229132F HDSC
TANYARADZWA MBANO R227537Q HDSC
SHINGIRIRAI MWARUNZIKA R229121B HDSC
Data Control Language (DCL)
• Data Control Language (DCL) is a subset of SQL
commands used to control access to data in a database.
• DCL is crucial for ensuring security and proper data
management, especially in multi-user database
environments.
• Data control language consist of statements that control
security and concurrent acces to table data.
• The primary DCL commands in SQL include Grant and
Revoke.
GRANT
• This command is used to give users access privileges to
the database.
• It is employed to grant a privilege to a user. GRANT
command allows specified users to perform specified
tasks.
• privilege names are
SELECT,UPDATE,DELETE,INSERT,ALTER,ALL
• Syntax

• GRANT privilege_name on objectname to user;

GRANT

EXAMPLE
• GRANT INSERT, SELECT on users to Tadiwa;
• Using the grant command, Tadiwa has been granted
permission on the “users” database objects, and he can
insert or query the “users” database.
REVOKE
• This command is used to remove previously granted
access privileges from a user.
• privilege names are
SELECT,UPDATE,DELETE,INSERT,ALTER,ALL

• Syntax

• REVOKE privilege_name on objectname from user;

REVOKE

• objectname is table name

• user is the name of the user whose privileges are
removing
Example:
• REVOKE INSERT, SELECT on users from Tadiwa;
• Thus, when we use this command, the permissions of
Tadiwa (like insert or query) on the “users” database
objects have been removed.
Application of DCL
• User Acces Management: DCL commands are used to
grant or revoke privileges to users or user roles within a
databas.
• Data Security: DCL commands are crucial in ensuring
data security by controlling access to sensitive or
confidential information.
• User Roles and Permissions: DCL commands are used to
define and manage user roles within a database.
• Database Auditing: DCL commands play a significant role
in auditing activities within a database.
Application of DCL
• Data Integrity and Consistency: DCL commands, such as
constraints defined through GRANT or REVOKE
statements, help enforce data integrity rules within a
database.
• Application Security: DCL commands are utilized to
secure database access for applications.
• By granting specific permissions to application users or
roles, organizations can ensure that applications can only
access the necessary data and perform authorized
operations.
Transaction Control Language
• Transaction Control Language (TCL) is a subset of SQL
commands used to manage transactions in a database.
• Transactions are important for maintaining the integrity
and consistency of data.
• They allow multiple database operations to be executed
as a single unit of work, which either entirely succeeds or
fails.
• The primary TCL commands in SQL include BEGIN
TRANSACTION, COMMIT, ROOLBACK, SAVEPOINT
and SET TRANSACTION.
BEGIN TRANSACTION
• This command is used to start a new transaction.
• It marks the point at which the data referenced in a
transaction is logically and physically consistent.
• Syntax: BEGIN TRANSACTION;
• In many SQL databases, a transaction starts implicitly
with any SQL statement that accesses or modifies data,
so explicit use of BEGIN TRANSACTION is not always
necessary.
COMMIT
• This command is used to permanently save all changes
made in the current transaction.
• Syntax: COMMIT;
• When you issue a COMMIT command, the database
system will ensure that all changes made during the
current transaction are saved to the database.
• BEGIN;
• SQL statements
• COMMIT;
ROLLBACK
• This command is used to undo changes that have been
made in the current transaction.
• It is also used with savepoint command to jump to a
savepoint in a transaction.
• Syntax: ROLLBACK;
• BEGIN;
• SQL statements
• ROLLBACK;
SAVEPOINT
• Savepoint command is used to temporarily save a
transaction so that you can rollback to that point
whenever necessary.
• It allows for partial rollbacks and more complex
transaction control.
• Syntax: SAVEPOINT savepoint_name;
• You can roll back to a savepoint using ROLLBACK TO
savepoint_name;
SAVEPOINT
• BEGIN;
• SQL statements
• SAVEPOINT my_savepoint;
• More SQL statements;
• ROLLBACK to my_savepoint;
• This example begins a transaction, performs SQL
statements and then rolls back the changes, restoring the
database to its previous state.
SET TRANSACTION
• This command is used to specify characteristics for the
transaction, such as isolation level.
• Syntax: SET TRANSACTION [characteristic];
• This is more advanced usage and may include settings
like isolation level which controls how transaction integrity
is maintained and how/when changes made by one
transaction are visible to other transactions.
EXAMPLE
• Consider thr following Table Student:
Name Marks

John 79

Jolly 65

Shuzan 70

UPDATE STUDENT

SET NAME = ‘Sherlock’

WHERE NAME = ‘Jolly’ ;

COMMIT;
ROLLBACK;
EXAMPLE
• Now after COMMIT:
Name Marks

John 79

Sherlock 65

Shuzan 70

• If commit was not perfomed then the changes made by

the update command can be rollback.
EXAMPLE
• Now if ROLLBACK is perfomed on the table:
rollback;

• After Rollback:
Name Marks

John 79

Jolly 65

Shuzan 70
EXAMPLE
• If save point is performed:
START TRANSACTION;

INSERT into STUDENT

VALUES ( ‘Jack’ , 95);

Commit;

UPDATE NAME
SET NAME = ‘Rossie’
WHERE marks = 70;

Savepoint A;

INSERT INTO STUDENT

VALUES (‘Zack’ , 76);

Savepoint B;
EXAMPLE

Savepoint C
INSERT INTO STUDENT
VALUES ( ‘Bruno’ , 85);

Savepoint C;

SELECT *
FROM STUDENT;
EXAMPLE
• The resulting table will be:
Name Marks

John 79

Jolly 65

Rossie 70

Jack 95

Zack 76

Bruno 85
EXAMPLE
• Now if we rollback to savepoint B:
Rollback to B;

• The resulting table will be:

Name Marks

John 79

Jolly 65

Rossie 70

Jack 95

Zack 76
EXAMPLE
• Now if we rollback to savepoint A:
Rollback to A;

• The resulting table will be:

Name Marks

John 79

Jolly 65

Rossie 70

Jack 95
Applications of TCL
• Committing Transactions: TCL statements can be used to
commit a transaction, which means to permanently save
the changes made during the transaction to the database.
• Rolling Back Transactions: TCL statements can be used
to roll back a transaction, which means to undo the
changes made during the transaction and restore the
database to its previos state.
• Setting Transaction Isolation Levels: TCL statements can
be used to set the transaction isolation level, which
determines the level of concurrency and consistency in
the database.
Applications of TCL
• Savepoints: TCL statements can be used to set
savepoints within a transaction, allowing for partial
rollback if needed.
• Managing Transactions in Stored Procedures: TCL
statements can be used in stored procedures to manage
transactions within the scope of te procedures.