Module 4
Question 1 : Demonstrate transaction states & additional operations.
Transaction States in DBMS
A transaction is a sequence of operations performed as a single logical unit of work. It
must satisfy ACID properties (Atomicity, Consistency, Isolation, Durability). A transaction
passes through the following states:
1. Active
● Transaction has started.
● Performing read/write operations.
2. Partially Committed
● Final operation completed.
● Waiting for all effects to be saved to the database.
3. Committed
● All changes made are permanently saved.
● Transaction is successful.
4. Failed
● Error or failure occurred before completion.
● Transaction cannot proceed.
5. Aborted
● Changes rolled back (undone).
● Transaction may restart or be terminated.
State Transition Flow (Text Diagram):
[Active] [Partially Committed] [Committed]
!
[Failed] ---------------------- [Aborted]
Additional Operations on Transactions
1. BEGIN / START TRANSACTION
o Marks the beginning of the transaction.
2. READ(X)
o Reads the value of data item X.
3. WRITE(X)
o Writes a new value to data item X.
4. COMMIT
o Confirms all operations; changes become permanent.
5. ROLLBACK
o Undoes all operations done after the transaction started.
Example:
BEGIN;
READ(A);
A = A + 100;
WRITE(A);
COMMIT;
If any error occurs before COMMIT, the system issues a ROLLBACK to restore the original
state.
Question 2 : Demonstrate working of Assertion & Triggers in database? Explain with an
example.
1. Assertion
Definition:
An Assertion is a database constraint that ensures a condition always holds true in the
database.
Purpose:
Used to enforce global integrity constraints that involve multiple tables or complex logic
beyond standard constraints.
Syntax (SQL):
CREATE ASSERTION assertion_name
CHECK (condition);
Example:
Ensure no employee earns more than their manager:
CREATE ASSERTION salary_check
CHECK (
NOT EXISTS (
SELECT * FROM Employee E, Employee M
WHERE [Link] = [Link] AND [Link] > [Link]
)
);
Working:
● Automatically checked after every relevant update.
● If the condition is violated, the update fails.
● Ensures data consistency across related tables.
2. Trigger
Definition:
A Trigger is a procedure that is automatically executed when a specified event occurs on
a table (like INSERT, UPDATE, or DELETE).
Purpose:
Used for enforcing rules, auditing, or automatic updates based on data changes.
Syntax (MySQL-style example):
CREATE TRIGGER log_update
AFTER UPDATE ON Employee
FOR EACH ROW
BEGIN
INSERT INTO LogTable(EmpID, Action, Time)
VALUES ([Link], 'Updated', NOW());
END;
Example:
Log all updates to the Employee table into a LogTable.
Working:
● Trigger fires automatically after an update.
● It inserts a log entry with the employee's ID, action type, and timestamp.
● Can be set to execute before or after operations, for each row or statement.
Difference Between Assertion and Trigger
Feature Assertion Trigger
Enforce complex conditions Automate actions on specific table
Purpose (constraints) events
Checked Specific events (INSERT, UPDATE,
On Every relevant update (globally) DELETE)
Type Declarative Procedural (contains logic)
Question 3 : Demonstrate the System Log in database transaction.
Here is a point-wise, exam-ready explanation of the System Log in database
transactions, suitable for a 6–8 mark VTU question:
System Log in Database Transaction
Definition:
A System Log is a sequential record maintained by the DBMS to keep track of all
transaction-related operations, especially for recovery and consistency in case of
failures.
Purpose of System Log
● To recover the database after crash or failure.
● To maintain ACID properties, especially Atomicity and Durability.
● To undo or redo transactions based on commit status.
Log Contents
A typical log includes:
1. Transaction ID – Unique identifier of the transaction.
2. Operation type – READ, WRITE, COMMIT, ABORT.
3. Data item affected – e.g., A, B, Salary.
4. Old value – Before change (used for undo).
5. New value – After change (used for redo).
6. Timestamps – For order of operations.
Example Log Entries
Assume Transaction T1 updates value of A from 100 to 150:
[START T1]
[WRITE T1, A, 100, 150]
[COMMIT T1]
System Log Operations
● UNDO: If a transaction fails or aborts, changes are undone using the old values
from the log.
● REDO: If a committed transaction’s changes were not saved due to crash, they
are redone using new values.
Write-Ahead Logging (WAL) Protocol
● Log records are written to disk before actual data is written.
● Ensures recovery is possible even if crash occurs before data reaches the
database.
Types of Recovery Using Logs
Actio
Scenario n
Committed RED
transaction O
UND
Aborted transaction O
In-progress (no UND
commit) O
Summary
● System log is critical for crash recovery.
● Maintains history of all read/write/commit/abort actions.
● Enables DBMS to rollback or replay transactions safely.
Let me know if you need a flowchart of UNDO/REDO or handwritten-style notes.
