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
Entity-relationship modeling
entities, relationships, attributes and cardinality, planned before any table exists
Core 5 parts · about 3 hours
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
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
Functional dependencies
what a functional dependency actually is, and Armstrong's axioms in outline
Common 5 parts · about 3 hours
5
Normal forms
1NF through BCNF, and exactly what anomaly each one rules out
Core 5 parts · about 4 hours
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
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
When to denormalize
read-heavy trade-offs, duplicating data on purpose, and keeping the duplicate consistent
Common 5 parts · about 3 hours
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