Member Avatar for alkeshtech

Hi guys,
I am creating a university database, where student can take sections and courses have prerequisites. I have to make sure that when user takes a section, he has met all the prerequisites for that course he is signing up for. And, when a course is deleted from the database, i have to make sure that the course to be deleted doesn't have any prerequisites.

here is my schema
Student(studentid, firstname, lastname, major, gpa)
course(courseid, title, credit)
requires(courseid, prerequisiteid)

I am not sure how do this in MYSQL. Can you guys get me started in the right direction?

Thanks,
Alkesh.

  • 3 Contributors
  • 4 Replies
  • 101 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by alkeshtech

Dani AI

Generated

Two things to enforce: (A) prevent deleting a course that is used in prerequisite relationships, and (B) prevent a student from registering for a course until all its prerequisites are completed. As noted, enforce referential integrity in the database so deletes/updates can’t silently break the prereq graph. As noted, you need a table that records completed/passed courses to check against.

To check prerequisites at registration time (application or DB), find any prerequisite rows that the student has not satisfied. Example query (replace table/column names to match your schema):

-- returns any missing prereq ids for :student_id and :course_id
SELECT p.prereq_id
FROM prereq_links p
WHERE p.course_id = :course_id
  AND NOT EXISTS (
    SELECT 1 FROM completed_courses c
    WHERE c.student_id = :student_id
      AND c.course_id = p.prereq_id
      AND c.passed = 1
  );

If this returns rows, deny registration and report which prerequisites are missing. Run this in the app before INSERT, or inside a DB routine.

For a DB-level safety net, add foreign keys on the prereq table that reference the courses table with restrictive deletes (do not use CASCADE). Example:

ALTER TABLE prereq_links
  ADD CONSTRAINT fk_course FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE RESTRICT,
  ADD CONSTRAINT fk_prereq FOREIGN KEY (prereq_id)  REFERENCES courses(id) ON DELETE RESTRICT;

If you prefer DB enforcement on insert, a BEFORE INSERT trigger can run the same NOT EXISTS check and SIGNAL an error when prerequisites are missing (see CREATE TRIGGER and SIGNAL in MySQL docs). For multi-level (transitive) prerequisites, compute the prerequisite closure with a recursive CTE (MySQL 8+) or maintain a materialized closure table for performance. See MySQL docs on InnoDB foreign keys, triggers, and WITH RECURSIVE for details: , CREATE TRIGGER, WITH RECURSIVE.

Caveats: triggers add complexity and harder-to-debug errors; application-level checks give clearer UX. Choose both (app check + DB constraints) for best safety.

Recommended Answers

All 4 Replies

Member Avatar for urtrivedi

create relation in requires table. both column will take value from course table. do not allow casacade update or delete to protect related data from deleting.

Member Avatar for alkeshtech

Yep that will work, but how do I check that a student has met all prerequisites when he wants to take a course?

Member Avatar for d5e5

Where in your schema do you record the fact that the student is taking, or has taken some courses? I think you need a table that has a one-many relation associating each student to courses that student is taking or has taken.

StudentCourses (studentid, courseid, date_completed)

Member Avatar for alkeshtech

Yes, I have that table.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.