---
title: 'Normal Forms'
source: 'https://academia.sh/en/courses/relational-theory/normal-forms'
course: 'Data Modeling and Relational Theory'
language: en
updated: '2026-08-23T07:00:48+00:00'
license: 'CC BY-SA 4.0'
---

# Normal Forms

The three types of update anomaly; the definitions of first, second, and third normal form; and query output showing which anomaly each step removes.

The previous lesson named the source of repetition: functional dependencies whose left
side is not a key. This lesson's question is: in what order are these dependencies
removed, and what concrete problem does each step eliminate?

Normalization's criterion is not aesthetic. The criterion is **update anomalies**:
situations, caused by a relation's structure, that leave data inconsistent. There are
three kinds.

- **Insertion anomaly**: recording one fact requires also knowing an unrelated fact. A
  branch that has never lent out a book cannot be written into a table that stores loan
  records.
- **Deletion anomaly**: deleting one fact makes a second fact disappear too. When a
  book's only loan record is deleted, the book itself drops out of the records.
- **Modification anomaly**: because the same fact is written in multiple rows, a change
  must be applied to all of them; skipping one leaves the data self-contradictory.

Normal forms are definitions that eliminate, step by step, the structures that produce
these three anomalies.

## First Normal Form

**First normal form** requires a single, indivisible value at every row–column
intersection. No lists, nested structures, or repeating column groups are allowed.

The cost of this rule is concrete. When phone numbers are stored comma-separated in a
single text column, searching for one number reduces to string matching, and string
matching produces false hits:

```sh
sqlite3 :memory: <<'SQL'
.headers on
.mode box
CREATE TABLE member_flat (member_no INTEGER PRIMARY KEY, name TEXT, phones TEXT);
INSERT INTO member_flat VALUES (41, 'Alice Kane',   '0312-555-0101,0312-555-0102'),
                           (52, 'Marcus Reyes',    '0312-555-0110'),
                           (63, 'Sylvia Renner', '0312-555-011');

CREATE TABLE member_phone (member_no INTEGER, phone TEXT, PRIMARY KEY (member_no, phone));
INSERT INTO member_phone VALUES (41, '0312-555-0101'), (41, '0312-555-0102'),
                               (52, '0312-555-0110'), (63, '0312-555-011');

SELECT 'list in one column' AS format, COUNT(*) AS found
  FROM member_flat WHERE phones LIKE '%0312-555-011%'
UNION ALL
SELECT 'separate relation', COUNT(*)
  FROM member_phone WHERE phone = '0312-555-011';
SQL
```

```
┌────────────────────┬───────┐
│       format       │ found │
├────────────────────┼───────┤
│ list in one column │ 2     │
│ separate relation  │ 1     │
└────────────────────┴───────┘
```

The number being searched for was `0312-555-011`, and it had a single owner. The string
search also pulled in the record `0312-555-0110`, which contains that number as a prefix.
When numbers are held in a separate relation, comparison becomes equality and the false
match disappears; in addition, a constraint can be written on the number, its uniqueness
can be checked, and a single number can be deleted.

The same rule applies to the "repeating column group" form. Columns `phone_1`, `phone_2`,
`phone_3` are nothing but a list written out horizontally; a fourth number requires a
schema change.

## Second Normal Form

**Second normal form** defines a relation that satisfies first normal form and in which
**no non-key attribute is dependent on a proper subset of a candidate key**. In short, no
partial dependency exists. The definition only has meaning for relations with a composite
key; a single-column key has no proper subset.

The relation holding the quantity of each book per branch has the key
`(isbn, branch_code)`. The book's title depends only on the ISBN, and the branch's name
depends only on the branch code — both are partial dependencies:

```sh
sqlite3 :memory: <<'SQL'
.headers on
.mode box
CREATE TABLE stock_flat (
  isbn         TEXT    NOT NULL,
  branch_code  TEXT    NOT NULL,
  quantity     INTEGER NOT NULL,
  book_title   TEXT    NOT NULL,
  branch_name  TEXT    NOT NULL,
  PRIMARY KEY (isbn, branch_code)
);
INSERT INTO stock_flat VALUES
  ('975-01', 'CEN', 3, 'Lost Time',        'Central'),
  ('975-01', 'BHC', 1, 'Lost Time',        'Bahcelievler'),
  ('975-02', 'CEN', 2, 'Sea Lighthouses',  'Central'),
  ('975-02', 'BHC', 4, 'Sea Lighthouses',  'Bahcelievler'),
  ('975-03', 'BHC', 1, 'Silent Garden',    'Bahcelievler');

UPDATE stock_flat SET branch_name = 'Bahcelievler Branch'
 WHERE branch_code = 'BHC' AND isbn = '975-01';

SELECT branch_code, COUNT(*) AS rows, COUNT(DISTINCT branch_name) AS distinct_names
FROM stock_flat GROUP BY branch_code;
SQL
```

```
┌─────────────┬──────┬────────────────┐
│ branch_code │ rows │ distinct_names │
├─────────────┼──────┼────────────────┤
│ BHC         │ 3    │ 2              │
│ CEN         │ 2    │ 1              │
└─────────────┴──────┴────────────────┘
```

The update changing the branch name was applied to a single row, and the branch is now on
record under two different names. The engine cannot prevent this, because the rule "the
same branch code carries the same name" is not written in the schema — nor can it be, since
the left side of the dependency is not a key.

Decomposition makes the left side of the partial dependency the key of its own relation:
the book information moves to the `book` relation, the branch information to the `branch`
relation, and `stock` holds only the key together with the `quantity` column, which is
fully dependent on it.

```sh
sqlite3 :memory: <<'SQL'
.headers on
.mode box
PRAGMA foreign_keys = ON;
CREATE TABLE book   (isbn TEXT PRIMARY KEY NOT NULL, title TEXT NOT NULL);
CREATE TABLE branch (branch_code TEXT PRIMARY KEY NOT NULL, name TEXT NOT NULL);
CREATE TABLE stock  (
  isbn        TEXT    NOT NULL REFERENCES book (isbn),
  branch_code TEXT    NOT NULL REFERENCES branch (branch_code),
  quantity    INTEGER NOT NULL,
  PRIMARY KEY (isbn, branch_code)
);
INSERT INTO book VALUES ('975-01', 'Lost Time'), ('975-02', 'Sea Lighthouses'),
                         ('975-03', 'Silent Garden');
INSERT INTO branch VALUES ('CEN', 'Central'), ('BHC', 'Bahcelievler'), ('KDK', 'Kadikoy');
INSERT INTO stock  VALUES ('975-01', 'CEN', 3), ('975-01', 'BHC', 1), ('975-02', 'CEN', 2),
                         ('975-02', 'BHC', 4), ('975-03', 'BHC', 1);

UPDATE branch SET name = 'Bahcelievler Branch' WHERE branch_code = 'BHC';

SELECT changes() AS updated_rows,
       (SELECT COUNT(*) FROM branch WHERE branch_code = 'KDK') AS branch_without_stock;
SQL
```

```
┌──────────────┬──────────────────────┐
│ updated_rows │ branch_without_stock │
├──────────────┼──────────────────────┤
│ 1            │ 1                    │
└──────────────┴──────────────────────┘
```

Because the branch name is now held in a single row, one update suffices and inconsistency
cannot arise. The same decomposition also removes the insertion anomaly: the Kadikoy
branch, which has no books at all, is on record — in the flat table this row could not have
been written, because the ISBN part of the key would have been left empty. The name of the
function that returns the number of changed rows varies by engine.

## Third Normal Form

**Third normal form** defines a relation that satisfies second normal form and in which
**no non-key attribute is dependent on another non-key attribute**. In short, no
transitive dependency exists.

In the loan record the key is a single column, so no partial dependency exists; there is,
however, the chain `loan_no → member_no → member_name, member_email`. The member
information does not depend on the loan directly, but through the member number:

```sh
sqlite3 :memory: <<'SQL'
.headers on
.mode box
CREATE TABLE loan_flat (
  loan_no      INTEGER PRIMARY KEY,
  member_no    INTEGER NOT NULL,
  member_name  TEXT    NOT NULL,
  member_email TEXT    NOT NULL,
  isbn         TEXT    NOT NULL,
  book_title   TEXT    NOT NULL,
  checkout_date TEXT   NOT NULL
);
INSERT INTO loan_flat VALUES
  (1001, 41, 'Alice Kane',  'alice@example.test', '975-01', 'Lost Time',       '2025-03-02'),
  (1002, 52, 'Marcus Reyes', 'marco@example.test', '975-01', 'Lost Time',       '2025-03-04'),
  (1003, 41, 'Alice Kane',  'alice@example.test', '975-02', 'Sea Lighthouses', '2025-03-05'),
  (1004, 41, 'Alice Kane',  'alice@example.test', '975-01', 'Lost Time',       '2025-03-20'),
  (1005, 52, 'Marcus Reyes', 'marco@example.test', '975-03', 'Silent Garden',   '2025-03-22');

UPDATE loan_flat SET member_email = 'alice.kane@example.test' WHERE loan_no = 1001;
DELETE FROM loan_flat WHERE loan_no = 1005;

SELECT (SELECT COUNT(DISTINCT member_email) FROM loan_flat WHERE member_no = 41) AS member41_email,
       (SELECT COUNT(*) FROM loan_flat WHERE isbn = '975-03')                    AS book975_03;
SQL
```

```
┌────────────────┬────────────┐
│ member41_email │ book975_03 │
├────────────────┼────────────┤
│ 2              │ 0          │
└────────────────┴────────────┘
```

Two anomalies appear at once. An email address corrected on one row left the same member
appearing to have two different addresses. The book whose only loan record was deleted
disappeared from the database entirely — its title had been written only in that row.

Decomposition makes the middle of the transitive chain the key of its own relation:

```sh
sqlite3 :memory: <<'SQL'
.headers on
.mode box
PRAGMA foreign_keys = ON;
CREATE TABLE member (member_no INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL);
CREATE TABLE book   (isbn TEXT PRIMARY KEY NOT NULL, title TEXT NOT NULL);
CREATE TABLE loan (
  loan_no       INTEGER PRIMARY KEY,
  member_no     INTEGER NOT NULL REFERENCES member (member_no),
  isbn          TEXT    NOT NULL REFERENCES book (isbn),
  checkout_date TEXT    NOT NULL
);
INSERT INTO member VALUES (41, 'Alice Kane', 'alice@example.test'),
                          (52, 'Marcus Reyes', 'marco@example.test');
INSERT INTO book VALUES ('975-01', 'Lost Time'), ('975-02', 'Sea Lighthouses'),
                         ('975-03', 'Silent Garden');
INSERT INTO loan VALUES (1001, 41, '975-01', '2025-03-02'), (1002, 52, '975-01', '2025-03-04'),
                         (1003, 41, '975-02', '2025-03-05'), (1004, 41, '975-01', '2025-03-20'),
                         (1005, 52, '975-03', '2025-03-22');

UPDATE member SET email = 'alice.kane@example.test' WHERE member_no = 41;
DELETE FROM loan WHERE loan_no = 1005;

SELECT (SELECT COUNT(DISTINCT email) FROM member WHERE member_no = 41) AS member41_email,
       (SELECT COUNT(*) FROM book WHERE isbn = '975-03')               AS book975_03;
SQL
```

```
┌────────────────┬────────────┐
│ member41_email │ book975_03 │
├────────────────┼────────────┤
│ 1              │ 1          │
└────────────────┴────────────┘
```

The same two statements were run; the result changed. Because the email is held in one
place only, a single address remains; because the book record is independent of the loan
record, it was unaffected by the deletion. Neither was achieved by how the query was
written, but by the structure of the schema.

## The Two Conditions for Decomposition

Splitting a relation is not always safe. Two conditions are required.

**Lossless join**: when the split relations are joined back together, the original rows
must be recovered exactly, with no extra rows created. The condition is that the common
attribute set must be a superkey of at least one of the parts. All the decompositions
above satisfy this, because the split was always made along the left side of a
dependency.

**Dependency preservation**: the original dependencies must be checkable separately on
the parts. A dependency that is not preserved can only be tested by joining the parts back
together — that is, it cannot be written as a constraint.

Decompositions up to third normal form can satisfy both at once. The next lesson's topic
is the case where these two conditions come apart.

## Summary

- Normalization's criterion is the elimination of insertion, deletion, and modification
  anomalies.
- First normal form requires a single indivisible value in every cell; a column holding a
  list reduces search to string matching and produces false matches.
- Second normal form removes partial dependency, third normal form removes transitive
  dependency; both split along the left side of a dependency.
- In a normalized schema, a fact is written in exactly one place; an update applies to a
  single row, and inconsistency cannot arise.
- A decomposition must satisfy lossless join and dependency preservation; the join is
  lossless when the common attribute set is a superkey of one of the parts.

## Next Step

Third normal form focuses on non-key attributes. But what if the right side of a
dependency is part of a key? By definition that attribute is not a non-key attribute, and
third normal form is not considered violated — yet the repetition continues. The next
lesson closes this gap by defining Boyce–Codd normal form, shows a case where lossless
join and dependency preservation cannot be satisfied together, and moves on to fourth
normal form through multivalued dependency.