CSE Roadmap

Level 3 of 13 · about 3 weeks

Data modeling and design

Designing a schema before any table exists: entity-relationship modeling, mapping diagrams to tables, functional dependencies, normal forms through 5NF, decomposition, when to denormalize on purpose, and modeling data whose history matters.

Modules

Core: everyone needs it. Common: most jobs use it. Specialist: optional depth, safe to skip on a first read.

  1. 1

    Entity-relationship modeling

    entities, relationships, attributes and cardinality, planned before any table exists

    Core 5 parts · about 3 hours

  2. 2

    Specialization, generalization and aggregation

    ISA hierarchies, pulling shared attributes up, and treating a relationship as a higher-level entity

    Common 5 parts · about 3 hours

  3. 3

    From ER diagram to relational schema

    mapping entities and relationships to tables and foreign keys, with fixed rules for each cardinality

    Core 5 parts · about 3 hours

  4. 4

    Functional dependencies

    what a functional dependency actually is, and Armstrong's axioms in outline

    Common 5 parts · about 3 hours

  5. 5

    Normal forms

    1NF through BCNF, and exactly what anomaly each one rules out

    Core 5 parts · about 4 hours

  6. 6

    Fourth and fifth normal form

    multivalued dependencies and join dependencies, for the rare cases BCNF still doesn't catch

    Specialist 4 parts · about 3 hours

  7. 7

    Decomposition

    splitting a table to remove an anomaly, and making sure the split can be joined back losslessly

    Common 5 parts · about 3 hours

  8. 8

    When to denormalize

    read-heavy trade-offs, duplicating data on purpose, and keeping the duplicate consistent

    Common 5 parts · about 3 hours

  9. 9

    Modeling temporal and versioned data

    valid time, transaction time, and slowly changing dimensions, for data whose history matters

    Specialist 5 parts · about 3 hours