> ## Documentation Index
> Fetch the complete documentation index at: https://fastapi2day.codewithsiva.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# 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     |

<Accordion title="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.
</Accordion>

### 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.

<Accordion title="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.
</Accordion>

### 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 |

<Accordion title="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.
</Accordion>

## 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:

```sql theme={null}
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.

<Accordion title="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.
</Accordion>

## 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.

```sql theme={null}
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`.

```sql theme={null}
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.

```text theme={null}
Ranks with ties: 1, 2, 2, 4  ← rank 3 is skipped
```

```sql theme={null}
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.

```text theme={null}
Ranks with ties: 1, 2, 2, 3  ← no gap after the tie
```

```sql theme={null}
SELECT
    employee_name,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employee;
```

<Tip>
  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.
</Tip>

### PARTITION BY

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

```sql theme={null}
-- 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` |

<Accordion title="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.
</Accordion>

## 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.