Practice & Assessment
Test your understanding of From Schema to Production
Multiple Choice Questions
6A migration file V3__add_status_index.sql has already been deployed to production. You realize it has a bug. What should you do?
In the expand-and-contract migration pattern, why is the new column added as nullable in Step 1?
Which database user should be used in application connection strings for a production web app?
Where should the most durable and PR-reviewed schema documentation live?
What is the correct sequence for a zero-downtime migration that renames a column?
Which tool is specifically designed to perform online schema changes on large MySQL tables without blocking reads and writes?
Coding Challenges
1Write a Zero-Downtime Migration to Add a New Column
An existing table has 50 million rows: CREATE TABLE user_events (event_id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, event_type VARCHAR(50), created_at TIMESTAMP). You need to add a non-nullable session_id UUID column. Write: (1) Step 1 migration SQL — add session_id as nullable; (2) the batch backfill SQL that sets session_id = UUID() for rows where session_id IS NULL in batches of 5000; (3) Step 3 migration SQL — make session_id NOT NULL after backfill. Add a comment explaining why each step is separate. Time estimate: 20 minutes.
Mini Project
Production-Ready Schema for a Job Board Platform
Design and deliver the complete production schema for a job board platform (companies post jobs, candidates apply). Deliverables: (1) ER diagram (dbdiagram.io DBML or image); (2) all CREATE TABLE DDL with correct types, NOT NULL, UNIQUE, CHECK, and FK constraints; (3) soft deletes on jobs and applications; (4) an audit table for application status changes; (5) four numbered Flyway-style migration files (V1 through V4) building up the schema incrementally; (6) GRANT statements for three roles: app_user, report_user, migration_user; (7) inline SQL comments on every non-obvious column. Minimum 6 tables required.