Advanced Database Systems Lab Manual
Based on SQL Server Management Studio (SSMS)
Preface
This lab manual is designed to accompany the Advanced Database Systems course, covering
Chapters 3 through 6. Each lab session provides hands-on exercises to reinforce theoretical
concepts including transaction management, concurrency control, backup and recovery, and
database security.
Prerequisites: SQL Server Management Studio (SSMS) installed with a local or remote SQL
Server instance.
Lab 1: Transaction Management in SQL Server
Objectives
Understand transaction boundaries using BEGIN, COMMIT, and ROLLBACK
Implement SAVEPOINT and partial rollbacks
Observe transaction states and outcomes
Analyze ACID properties through practical examples
Duration: 2 hours
1.1 Creating Sample Database
sql
-- Create a test database
CREATE DATABASE BankDB;
GO
USE BankDB;
GO
-- Create Accounts table
CREATE TABLE Accounts (
AccountID INT PRIMARY KEY,
AccountHolder VARCHAR(50),
Balance DECIMAL(10,2),
AccountType VARCHAR(20)
Prep by Wuletawu.I
);
-- Insert sample data
INSERT INTO Accounts VALUES
(1, 'Alice Johnson', 5000.00, 'Savings'),
(2, 'Bob Smith', 3000.00, 'Checking'),
(3, 'Carol Davis', 10000.00, 'Savings'),
(4, 'David Wilson', 2000.00, 'Checking');
-- Verify data
SELECT * FROM Accounts;
1.2 Basic Transaction: COMMIT and ROLLBACK
Exercise 1: Successful Transaction (COMMIT)
sql
-- Begin transaction
BEGIN TRANSACTION;
-- Display initial balances
SELECT AccountID, Balance FROM Accounts WHERE AccountID IN (1,2);
-- Transfer money: Alice to Bob
UPDATE Accounts SET Balance = Balance - 500 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 500 WHERE AccountID = 2;
-- Verify intermediate state
SELECT AccountID, Balance FROM Accounts WHERE AccountID IN (1,2);
-- Commit the transaction
COMMIT;
-- Final state after commit
SELECT AccountID, Balance FROM Accounts WHERE AccountID IN (1,2);
Expected Output:
Alice: 5000 → 4500
Bob: 3000 → 3500
Prep by Wuletawu.I
Exercise 2: Failed Transaction (ROLLBACK)
sql
-- Begin transaction
BEGIN TRANSACTION;
-- Display initial balances
SELECT AccountID, Balance FROM Accounts WHERE AccountID IN (1,2);
-- Attempt transfer with insufficient funds
UPDATE Accounts SET Balance = Balance - 6000 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 6000 WHERE AccountID = 2;
-- Check balances after update (will show negative!)
SELECT AccountID, Balance FROM Accounts WHERE AccountID IN (1,2);
-- Rollback to undo changes
ROLLBACK;
-- Verify no changes were made
SELECT AccountID, Balance FROM Accounts WHERE AccountID IN (1,2);
Observation: Both balances return to original values (4500 and 3500).
Exercise 3: Transaction with Error Handling
sql
-- Using TRY-CATCH for transaction management
BEGIN TRY
BEGIN TRANSACTION;
-- Check sufficient balance
DECLARE @Balance DECIMAL(10,2);
SELECT @Balance = Balance FROM Accounts WHERE AccountID = 1;
IF @Balance >= 1000
BEGIN
Prep by Wuletawu.I
UPDATE Accounts SET Balance = Balance - 1000 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 1000 WHERE AccountID = 2;
COMMIT;
PRINT 'Transaction committed successfully';
END
ELSE
BEGIN
ROLLBACK;
PRINT 'Transaction rolled back - Insufficient funds';
END
END TRY
BEGIN CATCH
ROLLBACK;
PRINT 'Transaction rolled back due to error: ' + ERROR_MESSAGE();
END CATCH;
-- Verify result
SELECT AccountID, Balance FROM Accounts WHERE AccountID IN (1,2);
1.3 SAVEPOINT and Partial Rollback
Exercise 4: Using SAVEPOINT
sql
-- Reset data
UPDATE Accounts SET Balance = 5000 WHERE AccountID = 1;
UPDATE Accounts SET Balance = 3000 WHERE AccountID = 2;
-- Transaction with savepoints
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 200 WHERE AccountID = 1;
SAVE TRANSACTION SavePoint1;
Prep by Wuletawu.I
UPDATE Accounts SET Balance = Balance + 200 WHERE AccountID = 2;
SAVE TRANSACTION SavePoint2;
UPDATE Accounts SET Balance = Balance - 50 WHERE AccountID = 1; -- Fee
-- View current state
SELECT AccountID, Balance FROM Accounts WHERE AccountID IN (1,2);
-- Rollback to SavePoint2 (undoes the fee)
ROLLBACK TRANSACTION SavePoint2;
SELECT AccountID, Balance FROM Accounts WHERE AccountID IN (1,2);
-- Commit remaining changes
COMMIT;
-- Final result
SELECT AccountID, Balance FROM Accounts WHERE AccountID IN (1,2);
1.4 Transaction Isolation Levels
Exercise 5: Demonstrating Isolation Levels
sql
-- Create a second table for this exercise
CREATE TABLE TestIsolation (
ID INT PRIMARY KEY,
Value INT
);
INSERT INTO TestIsolation VALUES (1, 100);
-- Session 1 (Run first)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;
SELECT * FROM TestIsolation;
-- Do not commit yet
-- Session 2 (Run second in a new query window)
Prep by Wuletawu.I
BEGIN TRANSACTION;
UPDATE TestIsolation SET Value = 200 WHERE ID = 1;
-- Do not commit
-- Back to Session 1: Run again
SELECT * FROM TestIsolation; -- Will show 200 (dirty read!)
-- Clean up
ROLLBACK; -- In Session 2
COMMIT; -- In Session 1
Exercise 6: Comparing Isolation Levels
sql
-- Create table for comparison
CREATE TABLE IsolationDemo (
ID INT IDENTITY(1,1) PRIMARY KEY,
Data VARCHAR(50),
Version INT DEFAULT 1
);
INSERT INTO IsolationDemo (Data) VALUES ('Record A'), ('Record B');
-- Function to check current isolation level
SELECT name, is_read_committed_snapshot_on
FROM [Link]
WHERE name = DB_NAME();
-- Set different isolation levels
-- READ COMMITTED (default)
Prep by Wuletawu.I
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Check current setting
DBCC USEROPTIONS;
1.5 Lab Tasks
Task 1: Write a transaction that transfers money between three accounts ensuring all updates
succeed or none are applied.
sql
-- Your solution here
Task 2: Create a transaction with multiple savepoints demonstrating partial rollback for a multi-
step banking operation (deposit, withdrawal, fee deduction).
sql
-- Your solution here
Task 3: Write a stored procedure that performs a funds transfer with proper error handling and
transaction management.
sql
-- Your solution here
Task 4: Demonstrate the difference between READ COMMITTED and REPEATABLE READ
isolation levels using two concurrent sessions.
sql
Prep by Wuletawu.I
-- Your solution here
Prep by Wuletawu.I