Normalization and Schema Quality

Normalization reduces redundancy and update anomalies by organizing data into well-formed tables. It keeps data accurate when inserts, updates, and deletes happen frequently.

Why normalization exists

Normalization reduces redundancy and update anomalies by organizing data into well-formed tables. It keeps data accurate when inserts, updates, and deletes happen frequently.

From 1NF to 3NF

1NF: atomic values, no repeating groups.
2NF: remove partial dependency on composite keys.
3NF: remove transitive dependencies.

Example decomposition

-- Unnormalized style (bad)
-- orders(order_id, customer_name, customer_city, product_name, product_price)

-- Better normalized design
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(100),
  customer_city VARCHAR(80)
);

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(100),
  product_price DECIMAL(10, 2)
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT REFERENCES customers(customer_id)
);