College Management Database

One academic truth, many useful views

A college must store students, their departments, offered courses, semesters, enrollments, marks and attendance. Faculty need course lists, students need transcripts, and coordinators need shortage and performance reports. Spreadsheets copy department and course names into many rows, so a spelling change creates conflicting versions.

Translate sentences into constraints

Marks may be NULL until evaluation, which differs from zero marks. Attendance defaults to zero only because the teaching workflow creates a record before sessions are counted. A production system should also record who changed marks and when.

Enrollment resolves a many-to-many relationship

A student takes many courses and a course has many students. enrollment becomes an associative entity and carries relationship facts: semester, marks and attendance. Putting marks inside student would require repeating columns; putting a comma-separated student list inside course would violate atomicity and make joins unreliable.

Keys

• Surrogate integer keys keep joins compact.
• Natural identifiers—roll number, course code and email—remain unique candidate keys.
• The enrollment composite key represents its real identity and blocks duplicates without application code.

Move each fact to the table it describes

An unnormalized row such as (roll_no, student_name, department_name, course1, marks1, course2, marks2) has repeating groups. First normal form creates one course registration per row. Second normal form removes attributes depending on only part of the registration key: student name belongs to student and course title belongs to course. Third normal form removes transitive facts: department head depends on department, not on student.

Ask questions at the correct grain

The transcript query begins at enrollment because one output row represents one registered course. Course performance groups by course and uses conditional aggregation for pass percentage. Attendance shortage filters individual enrollment facts before ordering.

weighted average = Σ(marks × credits) / Σ(credits)

The result view groups by student and semester. Every selected non-aggregate is functionally determined by those grouping keys. NULL marks should be handled according to published academic policy; silently converting “not evaluated” to zero can misrepresent performance.

Executable SQLite schema, seed data and reports

Loading source…

Run with sqlite3 :memory: < college-management.sql. The script activates foreign keys explicitly, creates constraints and indexes, inserts deterministic sample data, builds a view and executes four reports.

Follow one enrollment from rule to report

Try to break the rules deliberately

Index access paths, not every column

Primary and unique keys already create lookup structures. idx_student_department supports department rosters; idx_enrollment_course_sem supports course lists within a semester. Every index consumes storage and adds work to inserts and updates, so justify it with frequent filters, joins or orderings and verify with EXPLAIN QUERY PLAN.

Multi-table registration should run in one transaction. Concurrent systems also need isolation decisions: preventing two coordinators from overwriting marks may require optimistic version columns or database-specific locking. Backups, role-based permissions, encryption and an immutable audit log belong in production.

Check design judgment

Build further

1. Add faculty and course-offering tables so one course can have different teachers each semester.
2. Add grade rules without hard-coding one policy into old results.
3. Create department rank and attendance-shortage reports with window functions.
4. Add an append-only marks audit table and authorization model.
5. Compare query plans before and after a purposeful composite index.

A database is an integrity system, not a collection of tables

The design succeeds because each fact has one home, relationships are explicit, invalid states are constrained, reports respect their grain, and indexes answer demonstrated access needs. Application validation improves experience; database constraints protect every application.