0

Let say we have two entities TEACHER and COURSE. There are two relationships between them. A TEACHER can teaches a COURSE, and also can learn a COURSE, however, the TEACHER cannot teach and learn a COURSE at the same time.

Is it possible to ilustrate this constraint in the relational model? How?

Thank you!

1 Answer 1

0

Short Answer

No. This can only be enforced through transactional logic. If you want to note it on the ER diagram, you can show some brief text below the necessary entity describing the constraint. E.g. Check student is not teacher. You can do this with other constraints with cardinalities or constraints that cannot be described solely through the diagram.

Long answer

I'm going to borrow this example from PerformanceDBA's IDEF1X Introduction to illustrate:

enter image description here

The relationship between Teacher and CourseOffering enforces that a Course has 1 Teacher.

If you tried to define something like a CourseRole so a Person could only have one Role within a class (so you couldn't have someone both as a student and a teacher), you'd have to check that:

  1. There's only one Person assigned to a Teacher role

  2. That Person was also a Teacher

  3. That when the CourseOffering was created, one row must be inserted into the CourseRole for the Teacher, otherwise you have a CourseOffering without a Teacher.

Which is way more work than checking:

IF StudentId = 
  (
    SELECT
      TeacherId
    FROM 
      CourseOffering 
    WHERE 
      CourseCode = @CourseCode
        AND Year = @Year 
        AND Semester = @Semester
  )
  THEN error out/rollback/etc.

Granted, there are situations in the real world where the relationship between a Course and a Teacher is M:M, there are 0:M TAs/student graders/etc. that would require a different model and different constraints.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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