4

Considering this ER diagram

ER diagram

we have Students who are admitted to participate in an Exam, and each Exam can be split up into multiple Runs (for example, to split up large groups across multiple rooms or to have two Runs for the same Exam in direct succession). Is it possible to ensure (via database constraints) that Students participate only in Runs that belong to Exams they are admitted to?

I couldn't find a way on my own and also don't know how to phrase this for an internet search.

Jasper
  • 3,939
  • 1
  • 18
  • 35
  • This is a *similar* [question](http://stackoverflow.com/questions/2538786/how-to-abort-insert-operation-in-mysql-trigger) that has information on how to abort an insert if data is not what you want. You could implement something like that, but check to be sure that a 'participant' value contains an exam that is linked to the student. – AdamMc331 Dec 16 '14 at 17:05
  • Thanks! I think I'll be OK with managing this relation on the application side. – Jasper Dec 18 '14 at 12:50

1 Answers1

0

You have these tables and columns: exam: id, name student: id, name run: id, exam_id (foreign key to exam.id), when (timestamp), room

You need a new intersection table to keep track of what exam is being taken by which student: int_exam_to_student: exam_id, student_id - both foreign keys

Now, you can query this to determine what runs a student is allowed to be in: select run.* from run join int_exam_to_student i on (run.exam_id = i.exam_id) where i.student_id = 123;

anupam.singhal
  • 101
  • 3