Database Design

The database uses 5 normalized tables with enforced referential integrity through foreign keys and cascading operations. Schema design prioritizes data integrity over premature optimization: every relationship is explicit, every constraint is enforced at the DB level, not just in application code.

Schema

-- Core academic data
students    (id, user_id FK, student_id, first_name, last_name, email, major, gpa, enrollment_date)
courses     (id, course_code UNIQUE, course_name, credits, description)
enrollments (id, student_id FK, course_id FK, semester, grade, grade_points)
            -- UNIQUE constraint on (student_id, course_id, semester)
            -- ON DELETE CASCADE from both students and courses

-- Authentication & authorization
users       (id, username UNIQUE, password_hash, role ENUM, is_active, last_login)
staff       (id, user_id FK, first_name, last_name, email, department, title)

Key design decisions

enrollments as a junction table implements a many-to-many relationship between students and courses, with semester as an additional discriminator. The same student can take CS602 in Spring 2025 and again in Fall 2026: these are two separate enrollment records with their own grades.

UNIQUE (student_id, course_id, semester) prevents duplicate enrollments at the database level. The application code can't accidentally double-enroll a student even if a network blip causes a duplicate POST: MySQL rejects it.

ON DELETE CASCADE ensures data integrity. Deleting a student automatically removes their enrollments. No orphaned rows pointing to non-existent students. No manual cleanup logic in the DAO layer.

grade_points stored alongside grade trades a tiny bit of redundancy for fast GPA computation. The letter grade (A, B+, C-, etc.) is what staff and students see; the numeric grade_points (4.0, 3.33, 1.67, etc.) is what the GPA query uses. The DAO computes GPA as a weighted average:

SELECT SUM(grade_points * c.credits) / SUM(c.credits) AS gpa
FROM enrollments e
JOIN courses c ON e.course_id = c.id
WHERE e.student_id = ?
  AND e.grade IS NOT NULL;

One JOIN, one aggregation, one row returned. Fast even with thousands of enrollments.

Auth is separate from academic data. Credentials live in users. Student records live in students, linked via users.id → students.user_id. This means:

• Students can change passwords without touching academic records
• Admins exist in users without polluting the students table
• The auth layer can evolve (OAuth, SSO, MFA) without schema migrations to academic tables

Student IDs are deterministic, not random. Format: [first initial][last initial][3-digit sequence]. Robert Jean Pierre becomes rj001. Another student with the same initials becomes rj002. The UserDAO.generateStudentId() method queries the highest existing sequence with matching initials and increments. This is human-readable and collision-free.

What's not in the schema (deliberately)

No enrollments.grade_letter AND enrollments.grade_numeric cached separately. Just grade (string) and grade_points (decimal). The conversion is a fixed lookup, not a calculation worth caching twice.

No users.email column. Email lives on students and staff: the role-specific tables. The users table is purely for authentication. This avoids the question of "which email is the right one if both have entries."

No soft deletes. When a record is deleted, it's gone, and cascading deletes clean up dependent rows. This is a teaching/demo system: preserving deleted records would add complexity without clear benefit. In a production version handling FERPA-protected data, soft deletes with audit logging would be the right call.