Click here to Skip to main content
15,867,834 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, I'm working on some small university project and I'm stuck with modeling entities and relationships between them.
I have department, course, class, lesson and teacher.
Department has more courses, course has more classes and class has more lessons.
One lesson belongs to one class, class belongs to one course and course belongs to one department.
Teacher can work in more departments and department can have more teachers assigned to it. Course can have many teachers, but only ones that are from the same department that course belongs and teacher can be assigned to many courses. Class also has many teachers, but teacher can only be from the same course that class belongs to and lesson can be taught from one teacher, but that teacher must be assign to the class that lesson belongs to.
I have trouble with making those relationships. So if anyone has any advice on how to do it, I'll be really grateful.

What I have tried:

I tried making bridge table between department and teacher and then using that table to make another bridge table with course and using that table to make bridge table with class, but that just seems messy and I don't know if I actually get anything good from it. I have so many many-to-many relationships and I don't know what to do with them. In one case I ended up with having 7 attributes in lesson of which 6 were IDs from other tables. I also don't know how to make those restrictions that teacher can teach on courses only if he is assigned to department on which the course is. I was thinking to make identifying relationship between course an department and use that ID in bridge table to make check constraint saying that department ID from bridge table between teacher and department must be the same as department ID from course, but I have to make that same thing for class and lesson and that also seems impossible to work with. I have very little knowledge of how database actually works and any advice with be appreciated.
Posted
Updated 21-Jan-19 5:45am

1 solution

Indeed the task is not that easy from my point of view, therefore take care with my solution. No warranty :-)

First I divided (tried to) your textual description in logical parts:
a.) Department has more courses                          [done]
b.) course has more classes                              [done]
c.) class has more lessons.                              [done]

d.) One lesson belongs to one class,                     [done]
e.) class belongs to one course                          [done]
f.) and course belongs to one department.                [done]

g.) Teacher can work in more departments                 [done]
h.) and department can have more teachers assigned to it.[done]

i.) Course can have many teachers, but only ones that    [done]
are from the same department that course belongs
and teacher can be assigned to many courses.

k.) Class also has many teachers,                        [done]
but teacher can only be from the same course
that class belongs to

l.) and lesson can be taught from one teacher,           [done]
but that teacher must be assign to the
class that lesson belongs to.

From this I startet to define the tables and cross checked with the list above:
Department
   DptId

Teacher
   TeacherId

Dpts_Teacher
   DptId       // g.)
   TeacherId   // g.)

Course
   CourseId
   DptId       // a.) f.) Fgn Key Department(DptId).
Course_Teacher
   CourseId
   DptId       // i.)  Fgn Key Dpts_Teacher(DptId,TeacherId)
   TeacherId   // i.)

Class
   ClassId
   CourseId    // b.) e.) Fgn Key Course.CourseId.
Class_Teacher
   ClassId
   CourseId    // k.) Fgn Key Course_Teacher(CourseId, TeacherId)
   TeacherId   // k.)

Lesson
   LessonId
   ClassId     // c.) d.) Fgn Key Class(ClassId)
   Teacher_Id  // l.) Fgn Key Class_Teacher(ClassId, Teacher_Id)

Again, check it very carefully. I hope it is not too cryptic and helps a little bit.


[Edit]
A trial to explain a possible confusion:
Have a look to
a.) Department has more courses
On a first glance it suggest (at least for me) as a potential M:N

But then
f.) and course belongs to one department.
does clearly define departement:course is 1:N
 
Share this answer
 
v3
Comments
behemoth16 21-Jan-19 16:01pm    
Thanks for replying. I tried to do as you said, but that still doesn't give me constraints I am looking for. For example, in table Class_Teacher I could easily add ClassID which doesn't belong to the course with CourseID from that table, because I don't seem to check that at all. I tried making courseID dependent of deptID, classID dependent on courseID and so on and then it would look like this:
Class_Teacher
ClassId //fgn key from course
CourseId // fgn key from course// k.) Fgn Key Course_Teacher(CourseId, TeacherId)
TeacherId
And I overlapped CourseID, meaning, I checked if courseId from course and courseID from course_teacher are the same. That works, but because I have four levels: dept, course, class and lesson, I get a lot of keys in all tables. It gets impossible to search for something. For example, my Lesson would have deptID, courseID, ClassId, TeacherId, also table Class_Teacher would have deptId, classID, courseId, teacherID and so on. One row in class_teacher would be like: 1, 1, 1, 1 if it's integer and that would be the situation in all tables.
[no name] 22-Jan-19 10:27am    
I see. I generally did not mentioned the 'basic' foreign keys e.g. Class_Teacher to Class Fgn Key Class(ClassId, CourseId). From my point of view this should solve the problem. How you think about this?

Btw. same for Course_Teacher/Course etc.
[no name] 22-Jan-19 12:02pm    
Sorry, I don't have time to check/test iit carefully. I hope this should work (keep in mind, made with MSSQL not Oracle);

CREATE TABLE Department
(
DptId INTEGER,
CONSTRAINT PK_Department PRIMARY KEY (DptId)
);

CREATE TABLE Teacher
(
TeacherId INTEGER,
CONSTRAINT PK_Teacher PRIMARY KEY (TeacherId)
);

CREATE TABLE Dpts_Teacher
(
DptId INTEGER,
TeacherId INTEGER,
CONSTRAINT PK_Dpts_Teacher PRIMARY KEY (DptId, TeacherId),
FOREIGN KEY (DptId) REFERENCES Department(DptId),
FOREIGN KEY (TeacherId) REFERENCES Teacher(TeacherId)
);

CREATE TABLE Course
(
CourseId INTEGER,
DptId INTEGER,
CONSTRAINT PK_Course PRIMARY KEY (CourseId),
FOREIGN KEY (DptId) REFERENCES Department(DptId)
);

CREATE TABLE Course_Teacher
(
CourseId INTEGER,
TeacherId INTEGER,
CONSTRAINT PK_Course_Teacher PRIMARY KEY (CourseId, TeacherId),
FOREIGN KEY (CourseId) REFERENCES Course(CourseId),
FOREIGN KEY (TeacherId) REFERENCES Teacher(TeacherId)
);

CREATE TABLE Class
(
ClassId INTEGER,
CourseId INTEGER,
CONSTRAINT PK_Class PRIMARY KEY (ClassId),
CONSTRAINT UQ_ClassId_CourseId UNIQUE(ClassId, CourseId),
FOREIGN KEY (CourseId) REFERENCES Course(CourseId)
);

CREATE TABLE Class_Teacher
(
ClassId INTEGER,
TeacherId INTEGER,
CourseId INTEGER,
CONSTRAINT PK_Class_Teacher PRIMARY KEY (ClassId, TeacherId),
FOREIGN KEY (ClassId, CourseId) REFERENCES Class(ClassId, CourseId),
FOREIGN KEY (CourseId, TeacherId) REFERENCES Course_Teacher(CourseId, TeacherId)

);

CREATE TABLE Lesson
(
LessonId INTEGER,
ClassId INTEGER,
TeacherId INTEGER,
CONSTRAINT PK_Lesson PRIMARY KEY (LessonId),
FOREIGN KEY (ClassId) REFERENCES Class(ClassId),
FOREIGN KEY (ClassId, TeacherId) REFERENCES Class_Teacher(ClassId, TeacherId)
);

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900