I am facing difficulties establishing constraints in the following situation. This is a hypothetical scenario for practicing DB designing.

Imagine, a rule in a School states that the Courses there will be taken in some fixed Rooms. E.g: Math Course can only be taken in Room 101, 102 and 110. Now, a Course can have many Sections. So, I need to make sure that any Section of the Math Course is taken only in the specified Rooms.

NOTE: A particular Section will be taken in only one room. That is, Section 1 of the Math course will be taken in Room 101 only

Now, I make a Course-Room relation by taking the PK of both Course and Room. The Section relation is created by taking the PK of Course and adding the Section Number with it.

For storing the Rooms of the Sections, a relation Section-Room is created. The Section-Room table should only contain rows that conform to the Course-Room relation. How can I create a constraint from this relation to the Course-Room relation?

A sample ERD is created here: https://dbdiagram.io/d/5e4433459e76504e0ef15e24

The Schema as DBML is pasted below as a backup.

// Store info about a Course
Table course {
  course_pk int [pk] 
  course_code varchar(13) [not null, unique]
  course_name varchar(200) [not null]

Table room {
  room_pk int [pk]
  room_no int

Table section {
  section_pk int [pk]
  course_id int
  section int
Ref: section.course_id > course.course_pk

Table course_room {
  course_room_pk int [pk]
  course_id int
  room_id int
Ref: course_room.course_id > course.course_pk
Ref: course_room.room_id > room.room_pk
  • I didn't get why you assert sections must be directly related to rooms? Courses are related to Rooms, Sections are related to Courses. That a Section is related to a Room is now implied indirectly; it doesn't need to be expressed? – Caius Jard Feb 12 at 19:06
  • Then how to know in which room class of a particular Section will be held? – Jahirul Islam Feb 12 at 19:07
  • Where is that restriction stated in the question? – Caius Jard Feb 12 at 19:08
  • I'll update it. Thanks. I assumed as Section and Course are real world entities, this restriction is implied – Jahirul Islam Feb 12 at 19:09
  • The restriction is implied. Many Section has 1 Course. 1 Course has Many Rooms. Thus, for any given section we can find out what rooms it may occur in. It was never stated that a section occurs in a particular room – Caius Jard Feb 12 at 19:10
  • 1 course has many rooms
  • 1 room has many courses

  • 1 course has many sections

  • 1 section has 1 course (? Or can the same section appear in multiple courses?)

If a section always occurs in a particular room for a particular course, then it depends on the course-room entity that decomposes the many:many relationship between courses and rooms. This thus establishes the rule that

  • 1 section has 1 course-room, that is in the list of rooms that the course takes place in
  • To answer your question, let's assume that one section won't appear in multiple courses. Though section here is simply an integer – Jahirul Islam Feb 12 at 19:26
  • I can infer the 1 section has 1 course-room part. But I can't figure out how to restrict the possible room values of that section based on its coure-room. I'd need to store the course's pk in section-room table to establish a foreign key on course-room – Jahirul Islam Feb 12 at 19:30
  • 1
    @JahirulIslam, the trick is that the Section table does not contain a room_id foreign key relation into the Room table, but a course_room_id foreign key relation into the Cource_room table, with the additional constraint that the course_id's must match (possibly not expressed in the dababase itself). – Bart van Ingen Schenau Feb 13 at 9:10
  • So, the Section-Room relation would be something like (section_id, course_room_id) ? – Jahirul Islam Feb 13 at 9:34
  • Yes: a section doesn't link to a room, it links to a course-room (which links to a room). If you want to know what single room a section is in you get to it via course-room – Caius Jard Feb 13 at 12:08

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.