Database Design and Normalization to Third Normal Form
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.
Learning Objectives
- Identify entities, attributes, and relationships in a domain.
- Normalize a flat table to third normal form (3NF).
- Draw an ERD showing keys and cardinalities.
- Translate the model into DDL with primary and foreign keys.
- Index foreign keys to keep joins fast.
Environment & Prerequisites
Prerequisites
- A relational DBMS (
PostgreSQL 16) and a sample dataset - An ERD tool or notation for modeling (draw.io, dbdiagram, or pen and paper)
- Known query patterns to inform indexing choices
- Assumed skill: basic SQL DDL (CREATE TABLE)
Step-by-Step Execution
1. Decompose into normalized tables
CREATE TABLE customer (id SERIAL PRIMARY KEY, email TEXT UNIQUE NOT NULL);Expected output:
CREATE TABLEWhat this teaches: Splitting customer data out of orders removes duplication: each customer exists once, so an email change touches one row instead of thousands. SERIAL is PostgreSQL shorthand for an auto-incrementing integer; on new schemas prefer id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, the SQL standard spelling that avoids sequence ownership quirks.
2. Model the relationship
CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT REFERENCES customer(id));Expected output:
CREATE TABLEWhat this teaches: The foreign key makes the relationship structural, not conventional: the database itself rejects orders pointing at nonexistent customers.
3. Index the foreign key
CREATE INDEX idx_orders_customer ON orders(customer_id);CREATE TABLE
CREATE INDEXExpected output:
CREATE INDEXWhat this teaches: Joins probe the foreign key column for every outer row; without the index each probe is a scan, with it each probe is a seek.
4. Prove the foreign key rejects orphans
INSERT INTO orders (customer_id) VALUES (9999);Expected output:
ERROR: insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL: Key (customer_id)=(9999) is not present in table "customer".What this teaches: The schema now enforces the relationship structurally. The error names the exact constraint (orders_customer_id_fkey), which is the message your application error handling should expect and translate.
How It Works
How It Works
Normalization removes redundancy by giving each fact one home. First normal form bans repeating groups, second bans partial dependencies on composite keys, and third bans transitive dependencies; the practical effect is that updates happen in exactly one place.
An ERD is the design before the DDL. Entities become tables, attributes become columns, and relationships become foreign keys with cardinality (one-to-many here). Drawing it first surfaces modeling mistakes when they are still cheap to fix.
Foreign keys are integrity plus documentation: they enforce referential rules the application would otherwise have to remember, and they tell every future reader exactly how tables connect. Indexing them keeps that integrity from costing join performance.
customer data repeated"] --> N1["1NF: atomic values"] N1 --> N2["2NF: no partial
dependencies"] N2 --> N3["3NF: customer table
+ FK reference"] N3 --> IX["index the FK"]
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.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| cannot decide 2NF vs 3NF | the definitions blur on simple schemas | ask: does this column depend on the whole key (2NF) and on nothing but the key (3NF) |
| over-normalization slows reads | every query joins six tables for one screen | normalize for integrity, then denormalize deliberately with documented views or caches |
| circular foreign keys block inserts | A references B and B references A | insert with one FK deferred, or restructure so the cycle is not load-bearing |
| ERD cardinality backwards | one-to-many drawn the wrong way | read it aloud: one customer has many orders; the FK sits on the many side |
| duplicate data crept back in | application wrote denormalized copies for speed | centralize writes through one path or use triggers/materialized views |
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.
Key Takeaways
- Normalize to 3NF so each fact lives in exactly one place.
- Draw the ERD before writing DDL; design mistakes are cheapest on paper.
- Foreign keys enforce relationships the app would otherwise have to remember.
- Index foreign keys: integrity should not cost join performance.
- Denormalize deliberately and document it, never by accident.
Practice Challenges
Challenge 1: Normalize a spreadsheet
Goal: Take a flat CSV of orders with repeated customer columns and produce a 3NF schema plus migration INSERTs.
Hint: Create customer first, then orders referencing customer(id); use INSERT ... SELECT to migrate.
Challenge 2: ERD review
Goal: Draw the ERD for a library (books, members, loans) and defend every cardinality choice in one sentence each.
Hint: Loans is the classic many-to-many resolver between books and members.