Project: Design and Tune a Learning Platform DB

Project brief

Design a normalized schema for students, courses, lessons, enrollments, and payments. Then create transactions for enrollment checkout and optimize reporting queries for monthly active students and top courses.

Project tasks

• Draw ER diagram with cardinality and keys.
• Apply 3NF and justify denormalized fields, if any.
• Implement transaction-safe enrollment flow.
• Benchmark report query before and after indexing.
• Document locking assumptions and failure recovery behavior.

SELECT
  DATE_TRUNC('month', e.enrolled_at) AS month_start,
  COUNT(DISTINCT e.student_id) AS active_students
FROM enrollments e
GROUP BY DATE_TRUNC('month', e.enrolled_at)
ORDER BY month_start;