Case study · MSU Mankato · CIS 440

Campus Registration & Academic Tracking System

A 12-table MySQL database for university enrollment, grading, and advising, with the business rules enforced inside the database itself: triggers, stored procedures, functions, views, and role-based access.

Course: CIS 440, Advanced Database Systems (Group 6)
My part: Schema design, triggers, stored procedures, access control
Tools: MySQL
Focus: Data integrity, business rules in SQL, least-privilege roles

The problem

University academic data tends to be spread across systems or handled by hand, which makes it hard to keep consistent. The requirements document called for one central database supporting three groups of users: students registering for sections, instructors managing classes and recording grades, and advisors tracking progress toward a major.

What the requirements asked for

Storage. Students, instructors, advisors, departments, majors, courses, sections, semesters, enrollments, grades, buildings, and classrooms.
Processing. Block enrollment when a section is full, block duplicate enrollment, timestamp grades automatically, and assign instructors to sections.
Reporting. Transcripts with GPA, enrollment levels per section, instructor schedules, and student progress.
Non-functional. Referential integrity throughout, Third Normal Form, and safe use by multiple users at once.

Schema

12Tables

3Triggers

2 + 2Functions and stored procedures

5Roles

The core of the model is Section, one offering of a course in a semester, taught by an instructor in a classroom. Students connect to sections through Enrollment, and each enrollment can receive at most one Grade. Unique constraints back the rules: one enrollment per student per section, and one grade per enrollment. Click the diagram to enlarge it.

The database is seeded with sample data: 8 students, 4 instructors, 8 courses, and 12 sections across five semesters from Fall 2023 to Spring 2026, with 24 enrollments and 20 grades.

Business rules in the database

Rules live in the database rather than in application code, so no client can bypass them. For example, enrollment capacity is checked by a trigger on Enrollment, which means a direct INSERT is blocked the same way a call to the registration procedure is.

Object	Type	What it does
`fn_available_seats`	Function	Section capacity minus active enrollments
`fn_calculate_gpa`	Function	Credit-weighted GPA across a student's graded courses, 0.00 if none
`trg_check_capacity`	Trigger	Rejects an enrollment when the section is full
`trg_grade_timestamp_*`	Triggers (2)	Stamps `recorded_at` on every grade insert and update
`sp_register_student`	Procedure	Rejects duplicates and full sections with clear errors, then enrolls the student
`sp_assign_instructor`	Procedure	Validates the section and instructor exist, then assigns
`vw_student_transcript`	View	Courses, grades, and cumulative GPA per student
`vw_enrollment_summary`	View	Per-section enrollment, seats left, instructor, and location

CREATE TRIGGER trg_check_capacity
BEFORE INSERT ON Enrollment
FOR EACH ROW
BEGIN
    DECLARE v_available INT;
    SET v_available = fn_available_seats(NEW.section_id);

    IF v_available <= 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Enrollment failed: section has reached maximum capacity.';
    END IF;
END

Capacity counts only Active enrollments, so completed enrollments free their seats.

Access control

Each role gets only what its job needs, matching the security section of the requirements.

Role	Access
DBA	All privileges on the database
Developer	Read and write on all tables, plus execute on every function and procedure
Academic admin	Manage sections, courses, semesters, classrooms, and buildings; read access elsewhere
Instructor	Read sections, enrollments, students, and the enrollment summary; read and write grades
Advisor	Read-only on students, enrollments, grades, and majors, plus the transcript view and GPA function

What I'd improve

Concurrent registration. The capacity check reads, then inserts, without locking. Two students taking the last seat at the same moment could both pass. Locking the section row (SELECT ... FOR UPDATE) inside the procedure would close that gap.
Prerequisites. They are stored as free text on Course, so nothing enforces them. A self-referencing prerequisite table would make them checkable at enrollment.
Enrollment summary view. It inner-joins instructor and classroom, which are nullable on Section, so a section with no instructor yet would drop out of the report. A left join fixes it.

MySQL Schema Design Triggers Stored Procedures Views RBAC

← All projects Get in touch →