Phase 3Beginner4 of 18 in Database Design

Normalization, Denormalization and Access Patterns

Functional dependencies and attribute closure, 1NF through BCNF, 4NF and 5NF, lossless decomposition, deliberate denormalization with counters and summary tables, and access-pattern-first physical design.

Normalization removes redundancy so each fact lives in one place and can't contradict itself. Denormalization adds redundancy back on purpose, for reads you can measure. Access patterns tell you which one each table needs.

0/5 · 0%

5 topics ~41 min 6 code blocks & diagrams

Start with the first topic

3.1

Functional Dependencies and Attribute Closure

A functional dependency X → Y means rows that agree on X must agree on Y. Dependencies are the business rules behind normalization: computing the closure of an attribute set tells you what it determines, which finds candidate keys and exposes partial and transitive dependencies.

7 min 1 code practice

3.2

1NF, 2NF, 3NF and BCNF

1NF: atomic values, no repeating groups. 2NF: no partial dependencies on a composite key. 3NF: no transitive dependencies from the key to non-key attributes. BCNF: every determinant is a candidate key. Each step removes a kind of redundancy and the update, insert and delete anomalies it causes.

9 min 2 code practice

3.3

4NF, 5NF, Lossless Decomposition and Dependency Preservation

4NF removes independent multivalued facts stored in one table (a person's skills and languages); 5NF removes join dependencies that can only be reconstructed from three or more projections. Every decomposition must be lossless (joining back gives exactly the original) and ideally dependency-preserving (rules still checkable per table).

8 min 1 code practice

3.4

Denormalization: Counters, Summaries and Snapshots

Denormalization stores data redundantly to make specific reads cheaper: duplicated columns, precomputed counters, summary tables, materialized views and snapshots. Every copy needs an owner and a mechanism (same transaction, trigger, CDC, or periodic rebuild) that keeps it correct, and a stated tolerance for staleness.

9 min 1 code practice

3.5

Access-Pattern-First Design

Before creating physical tables and indexes, list every important read and write with its frequency, filters, sort order, pagination, expected latency, and consistency requirement. That inventory drives table shape, indexes, denormalization and the choice of store, and it's the backbone of NoSQL modeling where you can't join later.