All tracks

Database architecture

6 modules, 12 hours. Work through them in order: each module lists the ones it needs first.

1. d1 Keys and constraints

   • Browser
   • PostgreSQL with PostGIS
   • 2 hours

   Prerequisites: Window functions (f4)

   Skills: Entities; natural and surrogate keys; PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK; generated columns

   • What does a foreign key check, and what doesn't it?
   • Natural or surrogate key for a species?
   • What does a CHECK refuse?

2. d2 Normalization

   • Browser
   • 2 hours

   Prerequisites: Keys and constraints (d1)

   Skills: 1NF to 3NF from nested JSON; functional dependencies; when to denormalize

   • Is a license a fact about a record or about a dataset?
   • What goes wrong when a dataset's title is stored on every record?

3. d3 Indexes and query plans

   • PostgreSQL with PostGIS
   • 2 hours

   Prerequisites: Keys and constraints (d1)

   Skills: B-tree, GiST and BRIN indexes; EXPLAIN and EXPLAIN ANALYZE; planner statistics

   • Why is this a sequential scan?
   • When does an index help a filter, a join or a spatial test?
   • What does the planner believe about a table?

4. d4 Transactions

   • PostgreSQL with PostGIS
   • 2 hours

   Prerequisites: Indexes and query plans (d3)

   Skills: ACID, MVCC, isolation levels, locks, savepoints, ON CONFLICT

   • How do I re-run a failed load safely?
   • What does another session see while a load runs?

5. d5 Roles and privileges

   • PostgreSQL with PostGIS
   • 1.5 hours

   Prerequisites: Transactions (d4)

   Skills: Schemas, roles, GRANT and REVOKE, default privileges, least privilege, a taste of row-level security

   • How do I give a reporting tool read-only access to one schema and nothing else?
   • What do default privileges change?

6. d6 Star schemas

   • PostgreSQL with PostGIS
   • Browser
   • 2.5 hours

   Prerequisites: Normalization (d2), Transactions (d4)

   Skills: Grain, facts, dimensions, conformed dimensions, unknown members, slowly changing dimensions (types 1 and 2); star vs snowflake; export

   • What is one record fact's grain?
   • Which dimensions does it share with habitat area per country?
   • Where does a record with no country go?