Design a Database Schema for a College Management System

Problem Design a normalized relational database schema for a college management system covering students, courses, faculty, enrollment and attendance, and justify the normalization choices.

Functional requirements

• Register students and faculty; assign faculty and students to departments.
• Define courses, offer them per semester, and track prerequisites.
• Enroll a student into a course offering; record grade and semester.
• Record per-session attendance for each enrolled student.
• Generate a student transcript and per-course grade rosters.
• Support course capacity limits and enrollment windows.

Non-functional requirements

• ~30,000 students, ~2,000 faculty, ~4,000 course offerings per semester.
• ~500 concurrent users during registration week; peak ~2,000 enrollment writes/min for a 3-hour window, ~5 QPS average the rest of the term.
• Attendance rows dominate volume: 30,000 students x 5 courses x 60 sessions ≈ 9M rows/semester, ~90M rows over a 10-year retention horizon.
• Transcript generation p95 < 300 ms; enrollment write p95 < 200 ms.
• Grades must never be lost: durable writes, point-in-time recovery.

Key components

• Core entities: Student, Faculty, Department, Course, CourseOffering (course + term + section + instructor), Enrollment (student x offering, with grade/status), AttendanceSession, AttendanceRecord.
• Enrollment is the join table carrying its own attributes (grade, status, enrolled_at) — the classic reason a many-to-many cannot be modelled with foreign keys alone.
• Indexes: (student_id, term) on Enrollment for transcripts, (offering_id) for rosters, composite (offering_id, session_date) on attendance.
• Read replicas or a denormalized transcript/reporting table refreshed nightly for registrar reporting.
• Partition AttendanceRecord by term to keep the hot partition small.

Deep dives / trade-offs

• Normal forms: walk 1NF → 2NF → 3NF concretely. Show the update anomaly (department head stored on every student row), the insert anomaly (cannot add a course with no students), and the delete anomaly (dropping the last enrollment erases the course).
• Normalized vs denormalized: 3NF optimizes writes and integrity but transcripts need 4-5 joins. Options are a materialized view, a nightly-built reporting table, or storing a computed GPA column — each trading staleness for read latency.
• Grade history: a mutable grade column loses the audit trail. An append-only grade_events table plus a current-grade projection is the safer design; discuss when the extra complexity is warranted.
• Capacity enforcement under concurrency: a naive count-then-insert races during registration; use a unique constraint plus a transactional seat counter or SELECT ... FOR UPDATE on the offering row.