0

Let's say I have a relation: CourseRegistrations with composite primary key: (course_id, sem_id, student_id)

I have another relation: CourseEvaluations with composite primary key: (course_id, sem_id, section, type, serial) .The CourseEvaluations relation will contain records of the different evaluations done in a course.

Now I want to make a third relation: CourseStudentEvaluations with two composite foreign keys from both the above tables. Both the above tables' primary keys contain course_id and sem_id. How do I enforce that they are equal in a row/record so that I can ensure only those students' evaluations are entered against only those courses in which they're registered.

The CourseStudentEvaluations relation will contain records of all students' performance in each evaluation entered in CourseEvaluations.

  • In order to get a good answer, you might try and give us the DDL and some sample DML for the tables? See the articles in my profile on how to ask questions here. p.s. welcome to the forum! :-) – Vérace Apr 14 at 15:07
0

If your database supports it, you can use a table level CHECK constraint to compare two columns to make sure that they're equal. You could have a single CHECK constraint that confirms the course_id and sem_id pointing to each parent are the same.

Alternatively, you could just have one course_id and one sem_id in your new table and use these columns in both of the foreign keys (one to each parent).

I think the second approach is a little cleaner, but if you're using some kind of ORM that has trouble understanding a child table column pointing at two different parents then you may not find the second approach to be practical.

  • Understood. Thanks a bunch. I'm using Oracle. One more thing, CourseRegistrations also contains a 'section' column, not as the part of a primary key but just a standard column. Is there any way to double check that 'section' of the CourseRegistration matches the 'section' in StudentCourseEvaluations coming from the composite primary key of CourseEvaluations? I'm guessing Triggers? – blackviper Apr 14 at 14:28
  • @blackviper if you aren't going to include a column in a candidate key then you can't really enforce anything about it declaratively. You could use code (e.g. triggers) for this. – Joel Brown Apr 14 at 15:02
1

I think your issue is a modeling one, as you skipped a few relations along the way. You can enforce your data consistency exclusively with the schema, no need for code enforcement with triggers or otherwise. Also, why use surrogate keys for such a model? Try using the natural keys which in your case are short, stable, and familiar, and see hot it simplifies your queries. Here is my take:

Courses:

Course PK <-- This is the name of the course, the one you use in the 'real world'

Semesters:

Semester PK <-- Summer, Spring, Winter, Fall or whatever you use to distinguish them

Course_Schedules:

Course REF Courses,

Semester REF Semesters,

Year ( in case not all courses are offered in the same semesters every year),

PK (Course, Semester, Year)

Students:

Student PK <-- Whatever you use to distinguish students in the real world

Course_Registrations:

Course,

Semester,

Year,

Student,

FK (Course, Semester, Year) REF Course_Schedules <-- Only 'real' scheduled courses,

PK (Course, Semester, Year, Student) <-- Student may repeat the course in different years

Course_Evaluations:

I think you are missing part of the key here, in the model you showed every course can have only one evaluation... Who evaluates it? add to PK as needed

Course,

Semester,

Year,

-- other properties --

PK (Course, Semester, Year, {missing attribute?}),

FK (Course, Semester, Year) REF Course Schedules

Course_Student_Evaluation:

Course,

Semester,

Year,

Student,

PK (Course, Semester, Year, Student),

FK (Course, Semester, Year) REF Course_Schedules

Now you can see that since both Course_Evaluations and Course_Student_Evaluations have DRI to Course_Schedules, it enforces everything you asked for, and more..

HTH

  • This is exactly what I'm doing. I just skipped out the rest of the model and manipulated the details a little to keep the question short and to the point. With your design, the problem that I faced was that in Course_Student_Evaluation, I can map a Course, Semester, Year from Course_Schedules to any Student even if he isn't registered. Joel Brown above gave the idea to add an extra FK FK(Course, Semester, Year, Student) REF Course_Registrations. – blackviper Apr 14 at 16:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.