Normal Forms

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:

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:

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.

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:

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',  '[email protected]', '975-01', 'Lost Time',       '2025-03-02'),
  (1002, 52, 'Marcus Reyes', '[email protected]', '975-01', 'Lost Time',       '2025-03-04'),
  (1003, 41, 'Alice Kane',  '[email protected]', '975-02', 'Sea Lighthouses', '2025-03-05'),
  (1004, 41, 'Alice Kane',  '[email protected]', '975-01', 'Lost Time',       '2025-03-20'),
  (1005, 52, 'Marcus Reyes', '[email protected]', '975-03', 'Silent Garden',   '2025-03-22');

UPDATE loan_flat SET member_email = '[email protected]' 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:

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', '[email protected]'),
                          (52, 'Marcus Reyes', '[email protected]');
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 = '[email protected]' 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.