Relational Modeling & Normalization — SQL Course

What you'll learn

Translate business concepts into entities and relationships, identify dependencies, and normalize designs without losing useful context. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply Entities and relationships to a realistic data question
Apply Cardinality to a realistic data question
Apply Functional dependencies to a realistic data question
Apply 1NF 2NF 3NF to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
Functional dependency	One attribute value determines another	Use dependencies to find the correct table grain and keys
Third normal form	Non-key facts depend on the key, whole key, and nothing but the key	Normalize update-sensitive operational data unless a measured tradeoff justifies otherwise
Junction table	A relation representing a many-to-many association	Give it both foreign keys and constraints for relationship-specific facts

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the normalized relational model question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Model authorship as a relationship

The junction key prevents duplicate authorship while position belongs to the relationship, not either entity.

book_authors.sql

CREATE TABLE publishing.book_authors (
  book_id bigint NOT NULL REFERENCES publishing.books(id),
  author_id bigint NOT NULL REFERENCES publishing.authors(id),
  author_position smallint NOT NULL CHECK (author_position > 0),
  PRIMARY KEY (book_id, author_id),
  UNIQUE (book_id, author_position)
);

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

Repeating author_1, author_2, and author_3 columns creates artificial limits and makes searching, constraints, and updates harder.

Independent workshop

Build a review-ready normalized relational model lab against the course commerce dataset.

Your finished workshop must include:

Entities and relationships
Cardinality
Functional dependencies
1NF 2NF 3NF
Junction tables
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

Functional dependency: Use dependencies to find the correct table grain and keys
Third normal form: Normalize update-sensitive operational data unless a measured tradeoff justifies otherwise
Junction table: Give it both foreign keys and constraints for relationship-specific facts

Quick check

1. Which rule best applies to Functional dependency?

2. Which rule best applies to Third normal form?

3. Which rule best applies to Junction table?

Next: INNER JOIN & Matching Relationships