DBMS Notes
HubUnit 1 — Introduction to DBMS & Entity-Relationship ModelAdvantages and Disadvantages of DBMSAggregation — Database Management SystemsCharacteristics of DBMSData IndependenceData ModelsDatabase Users and AdministratorsDBMS LanguagesER DiagramsEntity-Relationship (ER) ModelIntroduction to DBMSKeys in DBMSRelationships and CardinalitySchema and InstanceSpecialization and GeneralizationDBMS Unit 1 Summary - Database FundamentalsThree-Level Architecture (ANSI/SPARC Architecture)Weak Entity SetUnit 2 — Relational Model & SQLConstraints in SQLCursorsDCL and TCL CommandsDDL CommandsDML CommandsDomain Relational Calculus (DRC) - Complete TutorialFunctions and Stored ProceduresJoins in SQLNested Queries (Subqueries)Relational AlgebraRelational Calculus in Database Management SystemsRelational ModelSQL IntroductionDBMS Unit 2 Summary - SQL and Relational ModelTriggers in SQL — Database Management SystemsTuple Relational Calculus (TRC)Views in SQL — Database Management SystemsArmstrong\Boyce-Codd Normal Form (BCNF) — Database Management SystemsClosure of AttributesDependency PreservationFifth Normal Form (5NF)First Normal Form (1NF) - Database NormalizationFourth Normal Form (4NF)Functional DependencyLossless DecompositionMultivalued DependencyNormal Forms — OverviewNormalization — IntroductionSecond Normal Form (2NF) — Database Management SystemsUnit 3 Summary — NormalizationThird Normal Form (3NF) — Database Management SystemsUnit 4 — Transaction Management & Concurrency ControlACID PropertiesCheckpointsConcurrency Control & SerializabilityDeadlock in DBMSLock-Based Protocols & Two-Phase LockingLog-Based RecoveryRecovery SystemSerializabilityShadow PagingUnit 4 Summary — Transaction Management & Concurrency ControlTimestamp-Based ProtocolTransaction ManagementTransaction StatesTwo-Phase Locking (2PL)Unit 5 — Storage & IndexingB+ TreeB-TreeCost EstimationDynamic HashingFile OrganizationHashing TechniquesIndexing — IntroductionMulti-Level IndexingQuery Optimization — Database Management SystemsQuery ProcessingSingle-Level IndexingStatic HashingUnit 5 Summary — Storage & IndexingUnit 6 — Advanced Topics in DBMSCloud DatabaseData MiningData WarehouseDatabase SecurityDistributed DatabaseDistributed Query ProcessingDistributed DBMS Architecture, Fragmentation & ReplicationMongoDB — IntroductionNoSQL DatabaseReplication in Distributed DatabasesUnit 6 Summary — Advanced Topics in DBMSViva Voce — DBMSImportant Viva Questions — DBMSInterview Questions — DBMSViva Answers — DBMSDBMS Multiple Choice Questions (MCQs)DBMS Lab Programs
DCL commands control access and permissions to database objects. They manage security by defining who can do what.
DCL — Data Control Language
DCL commands control access and permissions to database objects. They manage security by defining who can do what.
REVOKE
Removes previously granted privileges from a user.
sql example
-- Syntax
REVOKE privilege_list ON object FROM user;
-- Revoke a specific privilege
REVOKE INSERT ON Employee FROM 'app_user'@'localhost';
-- Revoke all privileges
REVOKE ALL PRIVILEGES ON university.* FROM 'admin'@'localhost';
-- Revoke grant option only
REVOKE GRANT OPTION FOR SELECT ON Employee FROM 'senior_user'@'localhost';Privilege Types
| Privilege | Description |
|---|---|
| SELECT | Read data from a table/view |
| INSERT | Add new rows |
| UPDATE | Modify existing rows |
| DELETE | Remove rows |
| CREATE | Create tables, indexes, views |
| DROP | Drop tables, databases |
| ALTER | Modify table structure |
| INDEX | Create/drop indexes |
| EXECUTE | Run stored procedures/functions |
| ALL PRIVILEGES | All of the above |
View Current Privileges
sql example
-- Show privileges for a user
SHOW GRANTS FOR 'hr_user'@'localhost';
-- Show current user's grants
SHOW GRANTS;TCL — Transaction Control Language
TCL commands manage transactions — groups of SQL statements that should execute as a single unit.
COMMIT
Permanently saves all changes made during the current transaction.
sql example
START TRANSACTION;
UPDATE Accounts SET balance = balance - 5000 WHERE account_id = 'A001';
UPDATE Accounts SET balance = balance + 5000 WHERE account_id = 'A002';
COMMIT; -- Changes are now permanent on diskROLLBACK
Undoes all changes made since the transaction began (or since the last SAVEPOINT).
sql example
START TRANSACTION;
DELETE FROM Employee WHERE dept_id = 2;
-- Realized it was a mistake!
ROLLBACK; -- All deletions undone
-- Nothing was changedSAVEPOINT
Creates a named point within a transaction to which you can partially roll back.
sql example
START TRANSACTION;
INSERT INTO Orders VALUES (1001, 'Alice', 500.00);
SAVEPOINT after_alice;
INSERT INTO Orders VALUES (1002, 'Bob', 300.00);
SAVEPOINT after_bob;
INSERT INTO Orders VALUES (1003, 'Carol', 750.00);
-- Undo Carol's order but keep Alice's and Bob's
ROLLBACK TO SAVEPOINT after_bob;
COMMIT; -- Only Alice's and Bob's orders are savedSET TRANSACTION
Configures the isolation level and mode of the current transaction.
sql example
-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Set read-only transaction
SET TRANSACTION READ ONLY;Transaction Isolation Levels
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|---|---|---|---|
| READ UNCOMMITTED | Possible | Possible | Possible |
| READ COMMITTED | Prevented | Possible | Possible |
| REPEATABLE READ | Prevented | Prevented | Possible |
| SERIALIZABLE | Prevented | Prevented | Prevented |
AUTOCOMMIT
By default, most SQL databases run in autocommit mode — each statement is its own transaction and is immediately committed.
sql example
-- Disable autocommit (MySQL)
SET AUTOCOMMIT = 0;
-- Now all statements are in a manual transaction
UPDATE Employee SET salary = 80000 WHERE emp_id = 101;
-- Not committed yet!
COMMIT; -- Now it's committed
-- Re-enable autocommit
SET AUTOCOMMIT = 1;TCL Flow Diagram
START TRANSACTION
|
▼
Execute DML
(INSERT/UPDATE/DELETE)
|
┌────┴────┐
│ │
COMMIT ROLLBACK
│ │
Changes Changes
Permanent Undone
DCL vs. TCL Summary
| GRANT | Give permissions to users |
| REVOKE | Take permissions away |
| COMMIT | Save transaction permanently |
| ROLLBACK | Undo entire transaction |
| SAVEPOINT | Mark a point inside transaction |
Continue the lesson path
Study next in this module
01Unit 2CursorsA cursor is a database object that allows row-by-row processing of query results inside a stored procedure or PL/SQL block. While SQL naturally operates on...02Unit 2DDL CommandsData Definition Language (DDL) commands are used to define, modify, and remove database objects such as tables, indexes, views, and schemas. DDL commands...03Unit 2Constraints in SQLConstraints are rules applied to table columns that restrict the type of data that can be inserted or updated. They enforce data integrity at the database...04Unit 2DML CommandsData Manipulation Language (DML) commands are used to manipulate the data stored in database tables. DML operates on the instance (rows/data), not the schema.