Advanced SQL: Normalization, ACID, and Window Functions

Understand database normalization (1NF–3NF), ACID transaction properties, and powerful window functions like RANK and ROW_NUMBER.

Once you’re comfortable writing SELECT queries and JOINs, the next step is understanding the principles that guide good database design — and the advanced features that make complex analytical queries possible without losing individual row detail. This chapter introduces three essential topics: database normalization to keep your schema clean and consistent, ACID properties to guarantee reliable transactions, and window functions to rank, number, and partition data without collapsing rows the way GROUP BY does.

Database Normalization

Normalization is the process of organizing a database schema to reduce data redundancy and improve data consistency. A normalized database stores each piece of information in exactly one place, which means updates only need to happen in one location — dramatically reducing the chance of inconsistencies.

Benefits of normalization:

• Eliminates duplicate data
• Improves data consistency
• Reduces storage space
• Simplifies UPDATE and DELETE operations
• Easier long-term maintenance

First Normal Form (1NF)

A table is in 1NF if:

• Every column contains a single, atomic (indivisible) value
• There are no repeating groups of columns
• Every row is unique (has a primary key)

Not in 1NF — the Subjects column holds multiple values in one cell:

Student	Subjects
Rahul	Python, SQL

In 1NF — each cell holds exactly one value:

Student	Subject
Rahul	Python
Rahul	SQL

Practice: Why is the first table not in 1NF?

The Subjects column stores multiple values (Python, SQL) in a single cell. 1NF requires that every column contain only a single, atomic value — you cannot have lists or comma-separated values in a column.

Second Normal Form (2NF)

A table is in 2NF if:

• It is already in 1NF
• Every non-key column depends on the entire primary key (not just part of it)

This mainly applies to tables with composite primary keys (a primary key made from two or more columns). If a non-key column depends only on one part of the composite key, it violates 2NF and should be moved to a separate table.

Practice: When is 2NF mainly applicable?

2NF is relevant when a table has a composite primary key — a key made up of two or more columns. If a non-key attribute depends on only one column of that composite key (a partial dependency), the table is not in 2NF.

Third Normal Form (3NF)

A table is in 3NF if:

• It is already in 2NF
• No non-key column depends on another non-key column (no transitive dependencies)

Violates 3NF — the Manager column depends on Department, not directly on the primary key Employee ID:

Employee	Department	Manager
Rahul	Engineering	Priya
Kiran	Engineering	Priya

In 3NF — move manager information to the Department table:

Department table:

Department	Manager
Engineering	Priya

Employee table:

Employee	Department
Rahul	Engineering
Kiran	Engineering

Practice: What problem does 3NF solve?

3NF removes transitive dependencies, where one non-key column indirectly depends on the primary key through another non-key column. By eliminating these, you ensure each non-key attribute describes only the primary key entity — nothing else.

ACID Properties

When a database executes a transaction (a sequence of operations treated as a single unit), it must guarantee four properties collectively known as ACID. These properties ensure that your data stays reliable and consistent even in the face of errors, crashes, or concurrent access.

Property	Description
Atomicity	The entire transaction either succeeds completely or is rolled back entirely — there is no partial success.
Consistency	A transaction moves the database from one valid state to another, never leaving it in a corrupt or incomplete state.
Isolation	Concurrent transactions do not interfere with each other. Each transaction sees a consistent snapshot of the data.
Durability	Once a transaction is committed, the changes are permanently saved — even if the system crashes immediately after.

Real-World Example: Bank Transfer

Consider transferring ₹500 from Account A to Account B:

BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 500 WHERE account_id = 'B';

COMMIT;

• Atomicity: If the second UPDATE fails, the first UPDATE is also rolled back. You never end up with ₹500 deducted but not deposited.
• Consistency: Total money across all accounts remains the same before and after.
• Isolation: Another transaction reading Account A’s balance during the transfer sees either the original amount or the final amount — never a partial state.
• Durability: Once committed, the transfer survives a power outage or server restart.

Practice: Which property ensures committed data is permanently saved?

Durability — once a transaction is committed, the data is permanently stored on disk. A system crash after the commit cannot undo it.

Window Functions

A window function performs a calculation across a set of rows that are related to the current row — without collapsing those rows into a single group the way GROUP BY does. Every row remains visible in the output; the window function simply adds an extra calculated column alongside it.

function_name() OVER (
    PARTITION BY column   -- optional: divide rows into groups
    ORDER BY column       -- defines the ordering within each window
)

ROW_NUMBER()

Assigns a unique sequential integer to every row within a partition, starting from 1. No two rows ever share the same ROW_NUMBER.

SELECT
    employee_name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employee;

Result:

employee_name	salary	row_num
Ajay	80000	1
Kiran	72000	2
Rahul	65000	3
Anitha	55000	4
Sneha	50000	5

RANK()

Assigns the same rank to rows with equal values, then skips the next rank to account for the tie.

Ranks with ties: 1, 2, 2, 4  ← rank 3 is skipped

SELECT
    employee_name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rank
FROM employee;

DENSE_RANK()

Also assigns the same rank to ties, but does not skip the next rank.

Ranks with ties: 1, 2, 2, 3  ← no gap after the tie

SELECT
    employee_name,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employee;

Use RANK() when you want to reflect actual competition positions (e.g., two second-place finishers mean no third place). Use DENSE_RANK() when you want continuous ranking regardless of ties.

PARTITION BY

PARTITION BY divides the rows into separate groups before applying the window function — similar to GROUP BY, but without collapsing the rows.

-- Rank employees by salary within each department
SELECT
    employee_name,
    department_id,
    salary,
    ROW_NUMBER() OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
    ) AS dept_rank
FROM employee;

This gives every employee a rank within their own department, resetting to 1 at the start of each new department group.

GROUP BY vs. Window Functions

GROUP BY	Window Functions
Collapses rows into one row per group	Keeps every original row
Returns one result per group	Returns a value per row
Cannot see individual row values after grouping	Individual row values remain accessible
Uses aggregate functions like SUM(), COUNT()	Uses OVER() clause with ORDER BY / PARTITION BY

Practice: Main advantage of window functions over GROUP BY

A window function performs calculations while keeping every row in the result set. GROUP BY collapses multiple rows into a single summary row, making individual record details unavailable. Window functions let you simultaneously see per-row details and a calculated aggregate (like rank or running total) in the same query.

Summary

This chapter covered three advanced areas of SQL:

Database Normalization

• 1NF — atomic column values, no repeating groups
• 2NF — no partial dependencies on a composite key
• 3NF — no transitive dependencies between non-key columns

ACID Properties

• Atomicity — all-or-nothing transactions
• Consistency — always valid state transitions
• Isolation — concurrent transactions don’t interfere
• Durability — committed data survives failures

Window Functions

• ROW_NUMBER() — unique sequential numbering
• RANK() — same rank for ties, skips next rank
• DENSE_RANK() — same rank for ties, no gaps
• PARTITION BY — apply window per group while keeping all rows

These concepts complete the SQL fundamentals covered in this course and prepare you for more advanced topics such as indexes, views, stored procedures, and query optimization.