🚀AdvancedDatabase Design
Database Normalization (1NF, 2NF, 3NF)
Design schemas that eliminate data redundancy and anomalies through normalization.
Overview
Normalization organizes a database schema into a series of normal forms that reduce data redundancy and improve data integrity. 1NF: atomic values, no repeating groups. 2NF: no partial dependencies (non-key columns depend on the full PK). 3NF: no transitive dependencies (non-key columns don't depend on other non-key columns).
Normalization prevents update anomalies (changing one fact requires multiple row updates), insert anomalies (can't record data without other data), and delete anomalies (deleting data unintentionally removes other facts).
Key Points
- •1NF: atomic values, each column has a single value, no repeating groups
- •2NF: no partial dependencies every non-key column depends on the whole PK
- •3NF: no transitive dependencies non-key columns depend only on the PK
- •Normalization reduces redundancy; denormalization trades redundancy for query speed
- •BCNF: every determinant is a candidate key (stricter than 3NF)
Examples
Normalizing an unnormalized table
-- Unnormalized (violates 1NF/2NF):
-- orders: order_id, customer_name, customer_email, product_names (list!)
-- 1NF: atomic values, no lists
CREATE TABLE orders (order_id INT, customer_id INT, product_id INT, quantity INT);
-- 2NF: separate customer data (no partial dependency on order_id)
CREATE TABLE customers (customer_id SERIAL PRIMARY KEY, name TEXT, email TEXT UNIQUE);
-- 3NF: product_price depends on product_id, not order_id (transitive)
CREATE TABLE products (product_id SERIAL PRIMARY KEY, name TEXT, price NUMERIC);
CREATE TABLE order_items (order_id INT, product_id INT, quantity INT, PRIMARY KEY (order_id, product_id));Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4