Modeling 3 entities with relationships
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 ?
Answer
Here is an example,
Conceptual (using NORMA)
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
