A complete student portal database schema designed for universities and colleges. Includes student profiles, course registration, assignment management with file submissions, grade tracking, and a campus announcement system. Suitable for building student information systems, university portals, or academic management platforms.
The enrollment is what a final grade belongs to. grades keys on enrollment_id rather than on the student and course separately, so retaking a course produces a second enrollment with its own grade — and a transcript can show both attempts instead of one overwriting the other.
course_prerequisites is a self-join on courses with a min_grade. That single column is what turns a prerequisite from a checkbox into a rule: it is not enough to have taken the course, you must have passed it at a stated level, and the check runs against the grade row of a previous enrollment.
Assignments carry weight_pct alongside max_score, which means the course's grading scheme lives in the data rather than in a spreadsheet. A submission scores against max_score and contributes according to its weight, so a final mark is computable from rows instead of from someone's arithmetic.
Converting a letter to points is a formula, and the formula changes — institutions revise their scales and apply the new one only going forward. Storing gpa_points freezes the conversion used at the time, so a transcript from four years ago still computes the GPA the student was actually awarded. Deriving it would silently restate every past result the day the scale is edited.
It can be recomputed from grades and gpa_points at any time, and it is kept on the row because a portal shows it on every screen. That makes it a cache with the usual obligation: it must be recalculated when a grade is corrected, and it is never the figure a dispute is settled with — the grade rows are.
Comparing submitted_at to the assignment's due_date gives the answer, and it gives the wrong answer as soon as an extension is granted. A stored flag records the decision that was actually made about this submission, including when a professor chose to accept it. The timestamps remain for evidence; the flag is the judgement.
A list of required course ids would be simpler and could not express the common case — you need this course with at least a C. min_grade on the link makes the rule complete. What it still cannot express is alternatives, where either of two courses qualifies; that needs a group concept, and this schema deliberately stops short of it.