Tutorial 3.2

Normalization, Transactions & Advanced Design

Chapter 7 · Data Management
~2.5 hours Advanced Normalization · ACID · Indexing

Overview

A well‑designed database is essential for data integrity and performance. This tutorial explores the principles of normalization to eliminate redundancy, the strategic use of indexing to speed up queries, and the transaction model that guarantees data consistency even under concurrent access.

Why this matters: Poorly normalized databases lead to update anomalies and bloated storage. Without proper transactions, applications can easily corrupt data. Mastering these concepts distinguishes a junior from a senior engineer.

1. Normalization (1NF – 3NF / BCNF)

Normalization is the process of organising data to reduce redundancy and improve integrity.

1NF (First Normal Form)

Each column contains atomic (indivisible) values, and each row is unique.

2NF (Second Normal Form)

Must be in 1NF, and every non‑prime attribute is fully functionally dependent on the whole primary key (no partial dependencies).

3NF (Third Normal Form)

Must be in 2NF, and no transitive dependency exists (non‑prime attributes depend only on the primary key, not on other non‑prime attributes).

BCNF (Boyce‑Codd Normal Form)

A stricter version of 3NF where every determinant is a candidate key.

-- Example of unnormalised table (repeating groups):
OrderID | Customer | Products
1 | Alice | Apple, Banana

-- 1NF: Separate rows for each product
OrderID | Customer | Product
1 | Alice | Apple
1 | Alice | Banana

2. Denormalization

Denormalization is the intentional addition of redundant data to improve read performance (fewer joins). It is a trade‑off: we sacrifice write performance and storage space for faster queries. Often used in data warehouses or high‑traffic read‑heavy applications.

3. Indexing Strategies

An index is a data structure (usually a B‑Tree or Hash table) that improves the speed of data retrieval operations at the cost of additional writes and storage.

  • B‑Tree Index: Default for most RDBMS. Good for equality and range queries.
  • Hash Index: Extremely fast for exact matches, but does not support range queries.
  • Composite Index: Index on multiple columns. Order matters (left‑most prefix).
  • Covering Index: Includes all columns needed for a query, avoiding table access.
Rule of thumb: Index columns that appear in WHERE, JOIN, ORDER BY, and GROUP BY clauses. Avoid over‑indexing on tables with frequent writes.

4. ACID & Transactions

A transaction is a sequence of operations performed as a single logical unit of work. ACID guarantees:

  • Atomicity: All operations succeed or none are applied (all‑or‑nothing).
  • Consistency: The database remains in a valid state before and after the transaction.
  • Isolation: Concurrent transactions do not interfere with each other.
  • Durability: Once committed, changes persist even after a system failure.
-- Transaction example (pseudocode / SQL)
BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

5. Isolation Levels

Isolation levels define the degree to which transaction operations are visible to others.

  • Read Uncommitted: Dirty reads possible. Lowest isolation.
  • Read Committed: Only committed data is read. Prevents dirty reads.
  • Repeatable Read: Ensures that if a row is read twice, the data is the same. Prevents non‑repeatable reads.
  • Serializable: Highest level. Transactions execute as if they were serial. Prevents phantom reads.

Choosing the right isolation level is a balance between consistency and performance.

Quiz

Question 1

Which normal form requires that every non‑key attribute is fully functionally dependent on the entire primary key (no partial dependency)?

  • 1NF
  • 2NF
  • 3NF
  • BCNF
Show answer
B. 2NF (Second Normal Form).

Question 2

In the ACID acronym, what does the 'D' stand for?

  • Durability
  • Distribution
  • Dependency
  • Data
Show answer
A. Durability.

Question 3

Which isolation level prevents dirty reads but allows non‑repeatable reads?

  • Read Uncommitted
  • Read Committed
  • Repeatable Read
  • Serializable
Show answer
B. Read Committed.

Exercises

Exercise 1

Normalise the following table to 3NF:

Orders (OrderID, CustomerName, CustomerCity, ProductID, ProductName, Quantity, Price)

Hint: Identify the dependencies.

Sample answer

1NF: Already in 1NF (atomic values, PK = OrderID + ProductID).

2NF: Remove partial dependencies. ProductName depends on ProductID (not the whole PK), CustomerName/City depend on OrderID (not the whole PK).

3NF: Remove transitive dependencies. CustomerCity depends on CustomerName (if we keep it) – but we split it.

Final Schema:

  • Orders (OrderID, CustomerID)
  • Customers (CustomerID, CustomerName, CustomerCity)
  • Products (ProductID, ProductName, Price)
  • Order_Items (OrderID, ProductID, Quantity)

Exercise 2

Explain the trade‑off between adding an index on a frequently used column and the overhead it introduces.

Sample answer

Advantage: Speeds up SELECT queries that filter, sort, or join on that column.

Disadvantage: Slows down INSERT, UPDATE, and DELETE operations because the index must be updated along with the table data. Also consumes additional disk space.

Decision: Index columns that are heavily used in read queries, but avoid over‑indexing on tables with high write throughput.

Homework

Homework 1

Describe a scenario where you would deliberately denormalise a database. What are the specific benefits and risks in that scenario? (200–300 words)

Sample answer

Scenario: A real‑time analytics dashboard for an e‑commerce site that displays daily sales totals and top‑selling products.

Denormalisation: Store pre‑computed daily aggregates in a separate table (daily_sales_summary) instead of calculating them on‑the‑fly from the transaction logs.

Benefits: Dashboard queries become extremely fast (single table read). Reduces load on the transactional database.

Risks: Data redundancy (the summary is derived from raw data). Must implement an ETL process to keep the summary up‑to‑date. Risk of inconsistency if updates fail.

Mini‑Project

E‑Commerce Checkout Transaction

Given a normalized database for an online store (products, inventory, orders, order_items, customers), write a transaction block that:

  1. Checks if the requested quantity of a product is available.
  2. Deducts the quantity from the inventory.
  3. Inserts the order and order items.
  4. Rolls back if the quantity is insufficient.

Use pseudocode or SQL (PostgreSQL/MySQL syntax).

Sample transaction (PostgreSQL style)
BEGIN;

-- Check stock (lock row for update)
SELECT stock INTO current_stock FROM products WHERE id = product_id FOR UPDATE;

IF current_stock >= quantity THEN
  UPDATE products SET stock = stock - quantity WHERE id = product_id;
  INSERT INTO orders (customer_id, order_date) VALUES (cust_id, NOW()) RETURNING id INTO order_id;
  INSERT INTO order_items (order_id, product_id, quantity) VALUES (order_id, product_id, quantity);
  COMMIT;
ELSE
  ROLLBACK;
  RAISE EXCEPTION 'Insufficient stock';
END IF;

Tutorial Summary

You explored advanced relational database design: normalization to 3NF, strategic denormalisation, indexing for performance, and the vital role of ACID transactions. You also learned about isolation levels and how they trade off consistency for concurrency. The exercises and mini‑project gave you practical experience in designing robust, reliable data layers.

Key takeaway: Database design is an engineering discipline of trade‑offs. Normalize for integrity, index for speed, and use transactions to protect your data’s integrity in all circumstances.