Normal Forms Quick Revision

Why Normalize a Relational Design?

Normalization uses keys and functional dependencies to place each fact in an appropriate relation. It reduces:

• update anomaly: one address must be changed in many rows;
• insert anomaly: a product cannot be recorded until an order exists;
• delete anomaly: deleting the last order accidentally removes customer/product facts.

Start from business rules, not only sample rows. A decomposition should be lossless so joins reconstruct the intended facts; preserving important dependencies is also desirable.

Normalization improves integrity, but indexes, transactions, authorization and performance testing are separate responsibilities.

Candidate Keys and Functional Dependencies

A functional dependency X → Y means each valid X value determines exactly one Y value under the business rules. Important terms:

For an order line relation with key (order_id,product_id):

(order_id,product_id) → quantity,sale_price
product_id → product_name,current_price
order_id → order_date,customer_id
customer_id → customer_name,customer_city

These rules—not the mere presence of an ID—tell us where facts belong.

First Normal Form (1NF)

A 1NF table represents one value per row/column intersection and avoids repeating groups such as product1, product2, product3 or comma-separated product IDs.

CREATE TABLE order_items_1nf (
 order_id BIGINT NOT NULL,
 product_id BIGINT NOT NULL,
 quantity INT NOT NULL,
 sale_price DECIMAL(10,2) NOT NULL,
 PRIMARY KEY(order_id,product_id),
 CHECK(quantity > 0)
) ENGINE=InnoDB;

Each product occurrence becomes a row. “Atomic” is domain-dependent: a postal address may be one value for display, but split fields are appropriate when street/city must be validated or queried independently.

Second Normal Form (2NF)

A relation is in 2NF when it is in 1NF and every non-prime attribute is fully dependent on each candidate key—there is no dependency on only part of a composite key.

In order_line(order_id,product_id,product_name,current_price,quantity), product name/current price depend only on product_id, not the whole composite key. Split them:

products(product_id PK, product_name, current_price)
order_items(order_id PK/FK, product_id PK/FK,
            quantity, sale_price)

sale_price can correctly remain on the order item because the agreed price may vary by order. With only single-attribute candidate keys, a 1NF relation cannot have a partial-key dependency and is automatically 2NF.

Third Normal Form (3NF)

A common teaching test is: the relation is in 2NF and non-key facts do not depend on the key through another non-key fact. In an orders table:

order_id → order_date,customer_id
customer_id → customer_name,customer_city

Customer name/city depend transitively on order ID through customer ID. Store customer facts once:

customers(customer_id PK,customer_name,customer_city)
orders(order_id PK,order_date,customer_id FK)

Formal 3NF: for every nontrivial functional dependency X → A, X is a superkey or A is prime. This formal rule matters when a relation has multiple candidate keys.

Historical order shipping address may intentionally be an order fact; business meaning decides the dependency.

Boyce–Codd Normal Form (BCNF)

BCNF is stricter: for every nontrivial dependency X → Y, X must be a superkey.

Suppose student_subject_teacher(student_id,subject,teacher) has rules:

(student_id,subject) → teacher
teacher → subject

Candidate keys are (student_id,subject) and (student_id,teacher). The relation can satisfy 3NF because subject is prime, but violates BCNF because teacher determines subject and teacher alone is not a superkey.

teacher_subject(teacher PK,subject)
student_teacher(student_id,teacher,
                PRIMARY KEY(student_id,teacher))

Always check lossless join and dependency preservation; a BCNF decomposition can sometimes make a dependency harder to enforce.

Build the Normalized MySQL Order Schema

CREATE TABLE customers (
 customer_id BIGINT PRIMARY KEY,
 customer_name VARCHAR(100) NOT NULL,
 customer_city VARCHAR(80) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE products (
 product_id BIGINT PRIMARY KEY,
 product_name VARCHAR(120) NOT NULL,
 current_price DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE orders (
 order_id BIGINT PRIMARY KEY,customer_id BIGINT NOT NULL,
 order_date DATE NOT NULL,
 FOREIGN KEY(customer_id) REFERENCES customers(customer_id)
) ENGINE=InnoDB;
CREATE TABLE order_items (
 order_id BIGINT NOT NULL,product_id BIGINT NOT NULL,
 quantity INT NOT NULL,sale_price DECIMAL(10,2) NOT NULL,
 PRIMARY KEY(order_id,product_id),
 FOREIGN KEY(order_id) REFERENCES orders(order_id),
 FOREIGN KEY(product_id) REFERENCES products(product_id),
 CHECK(quantity > 0)
) ENGINE=InnoDB;

The composite key prevents duplicate product lines per order; foreign keys protect relationships. Add secondary indexes from measured query plans.

Review a Design and Denormalize Deliberately

1. Write entities, facts and business rules.
2. List candidate keys and functional dependencies.
3. Remove repeating groups (1NF).
4. Remove partial dependencies (2NF).
5. Remove transitive/non-key determinants (3NF).
6. Test every determinant for BCNF.
7. Prove lossless joins and review dependency preservation.
8. Add PK, UNIQUE, FK, NOT NULL and CHECK constraints.
9. Load representative data and test CRUD/concurrency.
10. Optimize measured queries with indexes first.

Denormalize only for a measured requirement: document the duplicate fact's owner, refresh mechanism, transaction boundary, reconciliation and recovery. Prefer a view, generated report, cache or summary table before duplicating source facts in operational tables.

Review primary and foreign keys and indexes.

References

• E. F. Codd: A Relational Model of Data
• MySQL 8.4: Foreign-Key Constraints
• MySQL 8.4: Multiple-Column Indexes

Normalization principles are grounded in the relational model; MySQL examples and constraint behavior were checked against the official manual.