Learning Platform Database Layer

1. Entity relationships and schema design

Scenario: you are building the real database layer behind a learning platform, covering learners, courses, modules, lessons, enrollments, lesson-level progress, and notes/bookmarks. Model every entity and relationship (including cardinality and optionality) in a short design document before writing DDL, explicitly noting which relationships are one-to-many and which (if any) are many-to-many and need a junction table.

• A design document lists every entity, its key attributes, and every relationship's cardinality and optionality
• At least one one-to-many relationship (e.g. course to lessons) and one many-to-many-via-junction relationship (e.g. learner to course, via enrollment) are both correctly identified
• The design is normalized to at least Third Normal Form, with any deliberate denormalization explicitly justified in the document

2. Schema implementation, constraints, and seed data

Implement the full schema as ordered migration files (learner, course, module, lesson, enrollment, lesson_progress, note — created in valid dependency order), with appropriate PostgreSQL types, PRIMARY KEY/NOT NULL/UNIQUE/CHECK constraints, and REFERENCES with explicit ON DELETE behavior for every foreign key. Seed the schema with realistic sample data (at least 3 learners, 3 courses with modules and lessons, and a mix of enrollment/progress states) in an order that respects every foreign key.

• All tables are created via ordered migration files, applying cleanly with no foreign key errors
• Every foreign key has an explicit, deliberately-chosen ON DELETE behavior (not left to the default without consideration)
• Seed data includes realistic, varied enrollment and lesson-progress states across at least 3 learners and 3 courses

3. Reporting queries

Write at least four reporting queries against the schema: (1) each learner's overall completion percentage across all their enrollments, using a join and aggregation; (2) the most-recently-active learners, using a subquery or CTE; (3) each course's popularity ranked using a window function (RANK() or ROW_NUMBER()); (4) a query using a CTE to avoid repeating a non-trivial intermediate calculation used more than once in the same query.

• All four reporting queries run successfully and return sensible, verifiable results against the seed data
• At least one query uses a window function (PARTITION BY and/or ORDER BY inside OVER)
• At least one query uses a CTE, with a comment explaining why a CTE was chosen over a plain subquery for that specific case

4. Indexes, transactions, and roles

Add at least two indexes justified by the reporting queries from milestone 3 (verify each one changes a query's EXPLAIN output from a Seq Scan to an Index Scan). Write a genuine multi-statement transaction (e.g. enrolling a learner AND creating their initial lesson_progress rows together) that correctly rolls back entirely if any part fails. Create at least two roles with different, deliberately narrow privilege sets (e.g. a read-only reporting role, and an application role limited to exactly the tables/actions it needs).

• At least two indexes exist, each with a documented EXPLAIN before/after showing a Seq Scan to Index Scan change
• A multi-statement transaction exists and is verified to roll back completely on a simulated failure partway through
• At least two roles exist with genuinely different, narrowly-scoped privilege sets, verified by attempting (and having rejected) an out-of-scope operation as each role

5. Verification and operational checklist

Write a set of verification queries confirming the schema's constraints actually work (attempting and observing the rejection of: a duplicate enrollment, a negative value where a CHECK forbids it, an orphaned foreign key reference). Complete a real backup/recovery checklist for this schema, following this course's operational-safety lesson — with specific, concrete answers, not placeholders.

• At least 3 verification queries exist, each demonstrating a specific constraint correctly rejecting invalid data
• The backup/recovery checklist has specific, concrete answers for backup frequency, a tested restore procedure, acceptable data-loss window, and recovery time expectation
• Every migration file applies cleanly, in order, to a completely fresh database