9

Say we have three entities in our environment, Teacher, Student and Course.

  • Every teacher has (teaches) 1 or more Courses and each Course is offered by 0 or more teachers
  • Every Student has taken 1 or more Courses and each Course is taken by 0 or more Students
  • Every Teacher has 0 or more Students and each Student has 1 or more Teachers

In this relationships, each relationship could be inferred from the two others, for example to know which Students are being taught by Teacher T1, go through the relationship between Teacher and Course to see what Courses are being taught by the teacher T1, and then go through the relationship between Course and Student to see which Students has taken these Courses. These Students are the ones that are being taught by teacher T1.

So we don't need an explicit relationship between Student and Teacher, because "Conceptually" this relationship exists.

Finally the question is:

In Conceptual Design is it necessary to show all the three relationships?

And as an extra information, how would it be in Logical Design (designing database tables and relationships), should the relationship remain as an inferred relationship or should be explicitly defined ?

Ashkan
  • 3,322
  • 4
  • 36
  • 47
  • 2
    Something you might want to think about. It's not true that each course is taken by many students. It *is* true that each *taught* course is taken by many students. (Students can't take a course that doesn't have a teacher.) – Mike Sherrill 'Cat Recall' Oct 26 '12 at 10:43
  • Actually it depends how we define the relationship between entities. We could say that no course can exist without being taught by any Teacher! But in reality, there may be such a course, so I accept what you said. – Ashkan Oct 26 '12 at 11:49

1 Answers1

7

Here is an example,

Conceptual (using NORMA)

enter image description here

Teacher teaches Course

  • It is possible that some Teacher teaches more than one Course and that for some Course, more than one Teacher teaches that Course.
  • In each population of Teacher teaches Course, each Teacher, Course combination occurs at most once.

Student takes Course

  • It is possible that some Student takes more than one Course and that for some Course, more than one Student takes that Course.
  • In each population of Student takes Course, each Student, Course combination occurs at most once.

Teacher tutors Student on Course

  • For each Student and Course, at most one Teacher tutors that Student on that Course.

Constraints

  • For each Teacher and Course, that Teacher tutors some Student on that Course if and only if that Teacher teaches that Course.

  • For each Student and Course, some Teacher tutors that Student on that Course if and only if that Student takes that Course.

Logical

enter image description here

Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71
  • +1 for the great detail. Your conceptual design example is a little complicated, I haven't seen this kind of models (apparently because I'm new to database design!). Is it considered a ternary relationship? Anyway... As the answer for my first question, we need to **explicitly show the relationship between all 3 entities**. The logical design is smart, how is it any different from having just a StudentCourseTeacher table for the relationship? – Ashkan Oct 26 '12 at 16:02
  • 2
    If you had only one `StudentCourseTeacher` table, then you could claim (insert a record) that a student is being tutored in math by a biology teacher (who does not teach math) and FK would not help. See verbalization of constraints on conceptual level. – Damir Sudarevic Oct 26 '12 at 16:08
  • .. or that a student is tutored in biology although that student does not take biology .... – Damir Sudarevic Oct 26 '12 at 16:10
  • Yes exactly. I will do a search on that. Does this have a performance overhead? – Ashkan Oct 26 '12 at 16:14
  • Normalization is about data integrity, performance overhead is meaningless in this context. – Damir Sudarevic Oct 26 '12 at 16:19