Assignment 7-A

Safe Data Operations

Build a banking transaction system that demonstrates ACID compliance. You'll implement transactions with proper commit/rollback handling, enforce data integrity with constraints, and handle concurrent access safely.

5-6 hours
Advanced
150 Points
What You'll Practice
  • BEGIN, COMMIT, ROLLBACK
  • Savepoints for partial rollback
  • Data integrity constraints
  • Isolation level concepts
  • Error handling in transactions
Contents
01

Assignment Overview

In this assignment, you will build a Banking System Database that handles money transfers, account operations, and audit logging - all with proper transaction safety and data integrity constraints.

Skills Applied: This assignment tests your understanding of Constraints (Topic 7.1), ACID Transactions (Topic 7.2), and Locking (Topic 7.3) from Module 7.
Constraints

PK, FK, CHECK, UNIQUE, NOT NULL

Transactions

BEGIN, COMMIT, ROLLBACK, SAVEPOINT

Concurrency

Isolation levels, locking

02

The Scenario

SecureBank Database

You're building the core database for SecureBank. The bank requires bulletproof transaction handling where money transfers either complete fully or not at all.

"If a transfer fails halfway through, we cannot have money disappear. Every transaction must be atomic, consistent, isolated, and durable."

03

Implementation Tasks

1
Schema with Constraints (5 tasks)
  • Accounts table with balance CHECK (>= 0)
  • Transactions table with FK to accounts
  • UNIQUE constraint on account numbers
  • NOT NULL constraints on critical fields
  • Audit log table for tracking changes
2
Transaction Scripts (5 tasks)
  • Money transfer between accounts (atomic)
  • Transaction with savepoint for partial rollback
  • Multi-step operation with error handling
  • Demonstrate COMMIT success scenario
  • Demonstrate ROLLBACK failure scenario
3
Integrity Verification (5 tasks)
  • Test constraint violation handling
  • Verify referential integrity on delete
  • Demonstrate isolation level behavior
  • Create audit trigger for balance changes
  • Write verification queries for data consistency
04

Submission Guidelines

GitHub Repository
  • Create a repository named sql-assignment-7-transactions
  • Include schema.sql with constrained tables
  • Include transactions.sql with transaction scripts
  • Include test-cases.sql with verification tests
  • Document each ACID property demonstration
05

Grading Rubric

Criteria Points
Schema with constraints (5 × 10 pts) 50
Transaction scripts (5 × 10 pts) 50
Integrity verification (5 × 10 pts) 50
Total 150