Design a Database Schema for a College Management System

Requirements: Design a normalized database schema for a college management system. The interviewer probed pros/cons of normalization alongside the schema.

Design: Core entities typically include Students, Courses, Departments, Faculty, Enrollments (student-course join with grade/semester), and Attendance. Discuss normal forms (1NF/2NF/3NF), why splitting Students/Enrollments avoids update/insert/delete anomalies, and the tradeoff of denormalizing for read-heavy reporting queries (e.g. transcript generation) versus a fully normalized write-optimized schema.