Why this decision matters
Bad database design isn't usually visible on day one. It shows up two years later when a simple business request takes three weeks of refactoring. Good design — a clean ERD, properly normalized tables, the right keys and constraints — pays dividends for the entire life of the system. This is the skill that turns business analysts into people who get pulled into architectural conversations.
By the end of this topic you'll be able to
Translate a business problem into an ERD with entities, attributes, and relationships; recognize and resolve violations of 1NF, 2NF, and 3NF; design an EER for a moderately complex domain; create a database from a SQL script in Oracle APEX; use a GenAI assistant to accelerate the design process without losing control of it.
Materials
Key concepts to know
- ERD — Entity-Relationship Diagram. Boxes (entities), lines (relationships), attributes (fields).
- Cardinality — 1-to-1, 1-to-many, many-to-many. Many-to-many almost always means a junction table.
- EER — Enhanced ER. Adds inheritance/specialization, aggregation, weak entities.
- Normalization — 1NF (atomic values) → 2NF (no partial dependencies) → 3NF (no transitive dependencies).
- Anomalies — insert, update, delete anomalies. The pain that normalization solves.
- Denormalization — sometimes you go back. For analytics, often. Know why.
Readings & class notes
- Database Design Principles — HandoutThe reference document for the entire design module.
- Database Design — Real-World Case StudyA worked end-to-end design with the trade-offs annotated.
- EERD — Example Diagram (PDF)Reference visual.
Normalization deep-dives
Two worked examples taking real-world artifacts (an invoice, a prescription record) from un-normalized through 3NF.
Designing with a GenAI assistant
How to use Claude / ChatGPT / Gemini as a design partner. The goal is acceleration without losing control — you must be able to defend every choice.
- AI Prompt Templates GuideVetted prompt templates for design, normalization review, schema critique.
Hands-on design exercises
- First National Bank — EER Design ExerciseDesign an EER for a multi-product bank.
- Planet Fitness — Database Design Document (Student)Build a full DDD for a real-world gym chain.
- Planet Fitness — DDD (Reference)
- Planet Fitness — Functional SpecThe "before" — what the business asked for.