DCL AND TCL COMMANDS

Submitted in fulfillment of requirements

of micro-project

Database Management System

By

“Aadie Gupta”
“Darshana Malusare”
“Gaurangi Madankar”

ROLL NO: - 55
56
57

ENROLLMENT NO: - 2209640226

2209640227
2209640228

SUBJECT INCHARGE
Mrs. Komal Jadhav

Computer Engineering Department

Academic Year 2023-2024

 CERTIFICATE
This is to certify that the microproject

“ALL DCL AND TCL COMMANDS”

is done by

“Aadie Gupta”
“Darshana Malusare”
“Gaurangi Madankar”

is submitted for

“Database Management System”

for
the diploma in Computer Engineering to the

Maharashtra State Board of Technology Education, Mumbai

(Autonomous) (ISO-9001-2008) (ISO/IEC 27001:2013)

___________ ____________
Subject In charge Head of Department

(Mrs. Komal Jadhav ) (Mrs. Smita Kuldiwar)

 ALL DCL AND TCL COMMANDS

Submitted in fulfillment of requirements

of micro-project

Database Management System

By

PROCESS AND INDIVIDUAL TOTAL

ROLL NAME ENROLLMENT PRODUCT PRESENTATION/ (10 marks)
NO NO ASSESMENT (6 WORK (4 marks)
marks)

55 AADIE 2209640226
GUPTA

56 DARSHANA 2209640227
MALUSARE

57 GAURANGI 2209640228
MADANKAR

SUBJECT INCHARGE
Mrs. Komal Jadhav

Computer Engineering Department

Academic Year 2023-2024

 COMPUTER ENGINEERING DEPARTMENT
VISION AND MISSION OF THE PROGRAMME

VISION

To contribute to society through excellence in scientific & knowledgeable based

education of computer science professionals.

MISSION

M1: To transform students into technically components, socially responsible & ethical
computer science professionals.

M2: To promote a creative teaching-learning process that will strive for academic
excellence in the field of computer engineering.

M3: To enhance the technical expertise of students through workshop & industry-
institute interaction.
 COMPUTER ENGINEERING DEPARTMENT
PROGRAMME OUTCOMES
PO1: Basic and Discipline specific knowledge: Apply knowledge of basic
mathematics, science and engineering fundamentals and engineering specialization to
solve the engineering problems.

PO2: Problem analysis: Identify and analyse well-defined engineering problems

using codified standard methods.

PO3: Design/ Development of solutions: Design solutions for well-defined technical

problems and assist with the design of systems components or processes to meet
specified needs.

PO4: Engineering Tools, Experimentation and Testing: Apply modern engineering

tools and appropriate technique to conduct standard tests and measurements.

PO5: Engineering practices for society, sustainability and environment: Apply

appropriate technology in context of society, sustainability, environment and ethical
practices

PO6: Project Management: Use engineering management principles individually, as

a team member or a leader to manage projects and effectively communicate about
well-defined engineering activities.

PO7: Life-long learning: Ability to analyse individual needs and engage in updating
in the context of technological changes.
 COMPUTER ENGINEERING DEPARTMENT
PROGRAMME EDUCATIONAL OBJECTIVES

PEO1: Provide socially responsible, environment friendly solutions to

Computer engineering related broad-based problems adapting professional
ethics.

PEO2: Adapt state-of-the-art Computer engineering broad-based technologies

to work in multidisciplinary work environments.

PEO3: Solve broad-based problems individually and as a team member

communicating effectively in the world of work.

PROGRAMME SPECIFIC OUTCOMES

PSO1: Computer Software and Hardware Usage: Use state-of-the-art
technologies for operation and application of computer software and hardware.

PSO2: Computer Engineering Maintenance: Maintain computer engineering

related software and hardware systems.

DCL & TCL Commands

Aim
Explain All DCL & TCL commands over any table schema.

Course Outcomes
Study of all DCL & TCL commands and operations over any table schema using SQL
command.

Proposed Methodology
 First, Search the topic for which you want to make a project, and then propose it to
the Subject In charge.

 After finalizing the topic, start gathering the information about your project.

 Recheck the program with the subject in charge.

 Now, it’s time to make a report of your Selected Project.

Action Plan
Sr. Detail of activity Plan Start Date Plan Finish Date
No.

1. Searching of Topic 25-09-2023 27-09-2023

2. Gathering Information 28-09-2023 30-09-2023

3. Report Making 11-10-2023 14-10-2023

Subject In-Charge
(Mrs. Komal Jadhav)

DCL & TCL Commands

Rationale

Studying all TCL and DCL commands provides a comprehensive skill set that is essential for
effective and secure database management. It empowers individuals to navigate the
complexities of administration—ensuring both security and performance.

Databases are often used by more than one person at a time, and their data must be protected
from conflicting modifications. The TCL commands provide mechanisms to handle
concurrent transactions so that conflicts do not occur and the integrity of the database is
maintained.

TCL and DCL commands are integral to optimizing database performance. Efficient
transaction management and user access control contribute to streamlined database
operations. By studying these commands comprehensively, you empower yourself to
implement practices that enhance overall system performance.

Knowledge of TCL and DCL commands is highly sought after in the IT industry. Employers
value professionals who possess the skills to manage databases securely, maintain data
integrity, and optimize performance. By studying these commands, you position yourself as a
valuable asset in the job market.

Literature

The data control language commands are related to the security of database. Data Control
Language perform tasks of assigning privileges, so users can access certain objects in
database.

SQL – transaction statements control transactions in database access. This subset of SQL is
also called the Data Control Language for SQL, (SQL, DCL).

1. COMMIT Command:

 The COMMIT command is the transactional command used to save changes invoked
by a transaction to the database. The COMMIT Command saves all the transactions to
the database since the last COMMIT or ROLLBACK command.

 The syntax for COMMIT command is as follows:

COMMIT;

 Example: Consider CUSTOMERS table is having following records:

 Id Name Age Address Salary
1 Ramesh 36 Ahmedabad 2,000.00
2 Khilan 25 Delhi 1,500.00
3 Kaushik 23 Kota 2,000.00
4 Chaitali 25 Mumbai 6,500.00
5 Hardik 27 Bhopal 2125.00
6 Komal 22 MP 4,500.00
7 Muffy 24 Indore 10,000.00

As a result, two rows from the table would be deleted and SELECT statement would produce
following result:

2. SAVEPOINT Command:

 A SAVEPOINT is a point in a transaction when you can roll the transaction back to a
certain point without rolling back the entire transaction.

 The syntax for SAVEPOINT command is as follows:

SAVEPOINT SAVEPOINT_NAME;

 This command serves only in the creation of SAVEPOINT among transactional

statements. The ROLLBACK command is used to undo a group of transactions.

 The syntax for rolling back to a SAVEPOINT is as follows:

ROLLBACK TO SAVEPOINT_NAME;
  Following is an example where you plan to delete the three different records from the
CUSTOMERS table. You want to create a SAVEPOINT before each delete, so that
you can ROLLBACK to any SAVEPOINT at any time to return the appropriate data
to its original state:

Example: Consider CUSTOMERS Table is having following records:

Id Name Age Address Salary

1 Ramesh 36 Ahmedabad 2,000.00
2 Khilan 25 Delhi 1,500.00
3 Kaushik 23 Kota 2,000.00
4 Chaitali 25 Mumbai 6,500.00
5 Hardik 27 Bhopal 2125.00
6 Komal 22 MP 4,500.00
7 Muffy 24 Indore 10,000.00

Now here is the series of operations:

3. ROLLBACK Command:

 The ROLLBACK Command is the transactional command used to undo transactions

that have not already been saved to the database. The ROLLBACK command can
only be used to undo transactions since the last COMMIT or ROLLBACK command
was issued.

 The syntax for ROLLBACK command is as follows:

ROLLBACK;

Example: Consider CUSTOMERS table is having following records:

 Id Name Age Address Salary
1 Ramesh 36 Ahmedabad 2,000.00
2 Khilan 25 Delhi 1,500.00
3 Kaushik 23 Kota 2,000.00
4 Chaitali 25 Mumbai 6,500.00
5 Hardik 27 Bhopal 2125.00
6 Komal 22 MP 4,500.00
7 Muffy 24 Indore 10,000.00

As a result, delete operation would not impact the table and SELECT statement would
produce following result:

4. GRANT Command:

 The GRANT command is Data Control Language (DCL) command. The objects
created by one user are not accessible by another use unless the owner of those
objects gives such permissions to other users. These permissions can be given by
using GRANT statement. One user can grant permission to another user if he is the
owner of the object or has the permission to grant to other users.

 Syntax:

GRANT {object privileges} ON object name

To user name [with GRANT OPTION]

 The list of object privileges is as follows:

 ALTER: Allows the grantee to change the table definition with the ALTER
TABLE command.
DELETE: Allows the grantee to remove the records from the table with the
DELETE command.
INDEX: Allows the grantee to create an index on table with the CREATE
INDEX command.
INSERT: Allows the grantee to add records to the table with the INSERT
command.
SELECT: Allows the grantee to query the tables with SELECT command.
UPDATE: Allows the grantee to modify the records in tables with UPDATE
command.
WITH GRANT OPTION: It allows the grantee to grant object
privileges to other users

Examples:

5. REVOKE command:

 The REVOKE command is Data Control Language (DCL) command. The REVOKE
statement is used to deny the grant given on an object.
 Syntax:

REVOKE (object privileges)

ON object name
FROM user name;

 The list of object privileges is:

 ALTER: Allows the grantee to change the table definition with the ALTER TABLE
command.

DELETE: Allows the grantee to remove the records from the table with the DELETE
command.

INDEX: Allows the grantee to create an index on table with the CREATE INDEX
command.

INSERT: Allows the grantee to add records to the table with the INSERT command.

SELECT: Allows the grantee to query the tables with SELECT command.

UPDATE: Allows the grantee to modify the records in tables with UPDATE
command.

Examples:
Actual Methodology Followed

Topi Work Done Data Work Done By

c
1. Searching of topic (BFS Traversal Method) Darshana Malusare

2. Gathering Information (Rationale, Aim, Applications, Gaurangi Madankar /

etc.) Darshana Malusare

3. Report Making Finalization of report Gaurangi Madankar

Resources Required

Sr. No. Name of Resources Specification Qty. Remark

1. Computer Intel i3, 4GB RAM or 1 -

above

2. MS-Word Office 2007 or above 1 -

3. Reference Book DSU Nirali Publication 1 -

Skill Developed

1. Execute create users, grant. revoke, commit, savepoint and rollback command
queries.

2. Allocate and deallocate the system and object privileges.

Applications

Can be used in college and school level programs.

Subject In-charge
(Mrs. Komal Jadhav)