Database Design and Normalization to Third Normal Form

IntermediateDatabases~4 hours
ERD3NFnormalizationindexes

I designed a normalized relational schema to third normal form, modeling entities and relationships with an ERD before writing DDL. Normalization eliminated update and deletion anomalies that a flat table would have introduced.

Objective & Context

Schema design decides data integrity for the life of an application. This lab decomposes a denormalized table through 1NF, 2NF, and 3NF, removing redundant and transitively dependent data, then adds indexes aligned to query patterns.

Environment & Prerequisites

  • A relational DBMS (PostgreSQL 16) and a sample dataset.
  • An ERD tool or notation for modeling.
  • Known query patterns to inform indexing.

Step-by-Step Execution

1. Decompose into normalized tables

CREATE TABLE customer (id SERIAL PRIMARY KEY, email TEXT UNIQUE NOT NULL);

2. Model the relationship

CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT REFERENCES customer(id));

3. Index the foreign key

CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE TABLE
CREATE INDEX

Validation & Testing

Attempt to introduce a redundant or anomalous update and confirm the normalized structure prevents it. Pass criteria: no repeating groups (1NF), full key dependency (2NF), no transitive dependencies (3NF), and FK-backed referential integrity.

Advanced: Troubleshooting
  • Over-normalization: excessive joins hurt read performance; denormalize selectively where justified.
  • Missing FK index: unindexed foreign keys slow joins and cascade operations.
  • Surrogate vs natural key: prefer stable surrogate keys for mutable identifiers.

Key Results

  • Normalized the schema to 3NF, eliminating update/deletion anomalies.
  • Modeled 4 entities with FK-enforced referential integrity.
  • Indexed foreign keys to keep joins performant.
  • Documented the ERD as the schema's source of truth.

Related Labs