ACID Properties, Concurrency Control, & Advanced Database Architecture

This chapter covers advanced database systems concepts, focusing on recovery logging, database hashing, procedural database objects, advanced SQL constructs (window functions, CTEs, set operations), and structured database design workflows used in system design interviews.

1. Crash Recovery: Undo and Redo Logging

When database transactions fail or the system crashes unexpectedly, the Database Management System (DBMS) relies on transaction logs to restore consistency. This mechanism revolves around two primary operations: Undo and Redo.

Undo Logging: If a transaction fails or aborts before committing, the system reverses its changes using old values stored in the log to restore the database to its pre-transaction state.
Redo Logging: If a transaction committed successfully but its changes were only stored in volatile buffer memory and not yet flushed to non-volatile disk storage before a crash, the system reapplies (redoes) the changes from the log.
Undo/Redo Logging: Modern database engines combine both strategies into an Undo/Redo logging architecture to handle arbitrary failure scenarios during active and committed transaction lifecycles.

2. Database Hashing and Bucket Indexing

Hashing provides rapid O(1) average-time complexity lookups for indexed data by mapping search keys directly to specific physical storage locations using a hash function.

Real-Life Analogy

Real-Life Analogy: Imagine sorting incoming mail into postal boxes based on the last digit of the recipient's street number. If you receive a letter for house number 104, you divide or apply a hash function (e.g., modulo operation) to direct it straight to bucket 4, bypassing the need to search through boxes 1, 2, and 3.

Hashing Workflow Example

Suppose you need to store 100 records using a simple hash function based on modulo arithmetic:

Structure & Concept Flow

Key (Record ID) -> Hash Function: key % 4 -> Bucket Location (0, 1, 2, or 3)

If key = 25, 25 % 4 = 1 (Stored in Bucket 1).
If key = 100, 100 % 4 = 0 (Stored in Bucket 0).

During a search query, the database engine passes the target key directly through the hash function to jump straight to the correct bucket, avoiding full table scans.

3. Stored Procedures, Functions, and Triggers

Databases allow developers to encapsulate business logic inside server-side procedural objects. Each construct serves a distinct purpose and execution model:

Procedural Object	Return Value	Invocation Strategy	Primary Use Case
Stored Procedure	Optional parameters / status codes	Explicitly called via application code (`CALL procedure_name`)	Complex multi-step transactional workflows and batch processing
Function	Strictly returns a single value	Used directly inside SQL expressions (`SELECT func()`)	Computed values, data transformations, and deterministic calculations
Trigger	None (does not return values)	Implicitly executed by the database engine in response to events (`BEFORE`/`AFTER` `INSERT`, `UPDATE`, `DELETE`)	Automated auditing, cascading updates, and data validation

4. Advanced SQL: Window Functions

Window functions perform calculations across a specific set of table rows (a "window") related to the current row, without collapsing the result set into a single summary group like the GROUP BY clause does.

Common Window Functions

ROW_NUMBER(): Assigns a unique sequential integer to rows within a partition.
RANK(): Assigns ranks to rows, leaving gaps in ranking when there are ties.
DENSE_RANK(): Assigns ranks without gaps in ranking when ties occur.
LAG() / LEAD(): Accesses data from a previous or subsequent row relative to the current row without requiring self-joins.

Practical Example: Calculating Running Totals

SQL

SELECT 
    order_id,
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders;

5. Common Table Expressions (CTEs) and Set Operations

Common Table Expressions (CTEs)

A CTE (WITH clause) defines a temporary named result set within the scope of a single SQL statement. It improves readability and eliminates the need for deeply nested subqueries.

SQL

WITH HighValueCustomers AS (
    SELECT customer_id, SUM(order_total) AS total_spent
    FROM orders
    GROUP BY customer_id
    HAVING SUM(order_total) > 10000
)
SELECT * FROM HighValueCustomers WHERE total_spent > 50000;

Set Operations: `UNION` vs. `UNION ALL`

UNION: Combines result sets from multiple SELECT queries and automatically removes duplicate rows (requires sorting/hashing overhead).
UNION ALL: Combines result sets and retains all rows, including duplicates. It is significantly faster because it avoids the duplicate-elimination step.

Subquery Filtering: `EXISTS` vs. `IN`

IN: Passes a list of values to check against. Efficient for small static lists, but can degrade in performance with large subquery datasets.
EXISTS: Evaluates whether a subquery returns any rows, returning a boolean (TRUE or FALSE). It short-circuits evaluation as soon as a matching row is found, making it more efficient for correlating large tables.

6. Structured Database Design Workflow

Designing a robust database for scalable applications (such as an e-commerce platform) follows a strict architectural pipeline:

Structure & Concept Flow

1. Requirement Analysis

2. Conceptual Design (Entity-Relationship Diagrams / ERDs)

3. Logical Design (Tables, Schemas, Normalization up to 3NF/BCNF)

4. Physical Design (Partitioning, Storage Engines, Indexing Strategies)

5. Implementation (DDL Scripts execution)

E-Commerce Schema Blueprint Example

When designing core entities for an online retailer like Amazon or Flipkart, tables are structured with clear foreign key relationships:

Customer Table: Stores user authentication and profile data (customer_id [PK], name, email, phone_number, created_at).
Product Table: Stores item catalogs (product_id [PK], name, description, price).
Order Table: Captures transaction headers (order_id [PK], customer_id [FK], order_date, status).
Order Items Table: Line items linking orders to specific products (order_item_id [PK], order_id [FK], product_id [FK], quantity).
Address Table: Stores shipping and billing locations (address_id [PK], customer_id [FK], street, city, postal_code).

Summary and Key Takeaways

Undo logs reverse uncommitted or failed transactions using prior values, while redo logs reapply committed changes from buffer memory after a system crash.
Hashing maps search keys directly to storage buckets using a hash function (e.g., modulo arithmetic) to achieve efficient $O(1)$ lookups.
Stored procedures handle complex business logic with optional return parameters, functions compute and return single values inside SQL expressions, and triggers fire implicitly in response to data modification events.
Window functions (ROW_NUMBER, RANK, SUM() OVER) perform row-level calculations across related partitions without collapsing rows into a single summary group.
CTEs (WITH clause) simplify complex queries by creating temporary, readable named result sets.
UNION ALL outperforms UNION by skipping duplicate removal overhead.
EXISTS evaluates subqueries efficiently using short-circuit boolean checks compared to large IN lists.
Database design follows a structured progression from requirements gathering and conceptual ERDs to logical normalization and physical DDL implementation.