Database Normalization

This post was migrated from Tistory. You can find the original here.

Normalization

• Normalization is the process of decomposing tables to eliminate anomalies (insertion, deletion, update).

• With a normalized database structure, when you extend the schema by adding a new data type, you either don’t need to change the existing structure at all, or only need to change part of it.

• Since tables get decomposed, JOINs increase and performance can suffer. That’s why denormalization also exists.

---

First Normal Form (1NF)

• Ensure atomicity of attributes — decompose any domain that isn’t an atomic value.

Second Normal Form (2NF)

• Satisfy full functional dependency = remove partial functional dependency

• Full functional dependency: when the primary key is composed of two or more columns, no proper subset of the primary key should be a determinant.

Third Normal Form (3NF)

• Remove transitive functional dependency

ex) A->B (B depends on A)

In a table with columns A(PK), B, C,

if A->B, B->C, and A->C hold, then there’s a dependency between non-key columns.

BCNF

• Remove any determinant that is not a candidate key (i.e., remove functional dependencies whose determinant is not a candidate key).

• Determinant: in A->B, this refers to A.

KEY

• Candidate key: the minimal set of attributes that can uniquely identify each row (satisfies uniqueness and minimality)

• Primary key: one of the candidate keys

• Super key: satisfies uniqueness only