behemoth16 Ответов: 1

Как смоделировать сущности и отношения базы данных в этом примере?

Привет, я работаю над каким-то небольшим университетским проектом и застрял с моделированием сущностей и отношений между ними.
У меня есть кафедра, курс, класс, урок и учитель.
На кафедре больше курсов, на курсе больше классов, а в классе больше уроков.
Один урок принадлежит одному классу, класс принадлежит одному курсу, а курс - одному отделу.
Учитель может работать в большем количестве отделов, и отдел может иметь больше учителей, назначенных ему. Курс может иметь много учителей, но только те, которые из того же отдела, что и курс, и учитель могут быть назначены на многие курсы. Класс также имеет много учителей, но учитель может быть только из того же курса, к которому принадлежит класс, и урок может быть преподан от одного учителя, но этот учитель должен быть назначен классу, к которому принадлежит урок.
У меня есть проблемы с этими отношениями. Так что если у кого-нибудь есть какой-нибудь совет, как это сделать, я буду очень благодарен.

Что я уже пробовал:

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.

1 Ответов

Рейтинг:
2

0x01AA

Действительно, задача не так проста с моей точки зрения, поэтому позаботьтесь о моем решении. Никакая гарантированность :-)

Сначала я разделил (попытался) ваше текстовое описание на логические части:

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.

Из этого я начинаю определять таблицы и сверяюсь с приведенным выше списком:
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)

Опять же, проверьте это очень тщательно. Я надеюсь, что это не слишком загадочно и немного помогает.


[Редактировать]
Испытание для объяснения возможной путаницы:
Взгляните на это
а) на кафедре есть еще курсы
На первый взгляд он предполагает (по крайней мере для меня) как потенциальный M:N

Зато
f.) и курс принадлежит к одному отделу.
четко определяет отдел:курс-1:N

behemoth16

Спасибо, что ответили. Я пытался сделать так, как вы сказали, но это все еще не дает мне ограничений, которые я ищу. Например, в таблице Class_Teacher я мог бы легко добавить ClassID, который не принадлежит курсу, с CourseID из этой таблицы, потому что я, кажется, вообще не проверяю это. Я попытался сделать столбцами courseid зависит от deptID, идентификатор класса зависит от идентификатор_курса и т. д. И тогда это будет выглядеть так:
классный руководитель
ClassId //fgn ключ от курса
Идентификатор_курса ключ // ФГН от поля// к.) ФГН Ключ Course_Teacher(Идентификатор_курса, TeacherId)
TeacherId
И я перекрывающимися столбцами courseid, смысл, я проверил, если идентификатор_курса от курса и столбцами courseid от course_teacher такие же. Это работает, но поскольку у меня есть четыре уровня: отдел, курс, класс и урок, я получаю много ключей во всех таблицах. Становится невозможным что-то искать. Например, Урок бы deptID, столбцами courseid, идентификатор класса, TeacherId, также Class_Teacher стол бы deptId, идентификатор класса, столбцами courseid, teacherID и так далее. Одна строка в class_teacher будет выглядеть так: 1, 1, 1, 1, если это целое число, и это будет ситуация во всех таблицах.

0x01AA

Я вижу. Я вообще не упоминал "основные" внешние ключи, например Class_Teacher to Class Fgn Key Class(ClassId, CourseId) С моей точки зрения, это должно решить проблему. Как вы думаете об этом?

Кстати, то же самое для Course_Teacher/Course и т. д.

0x01AA

Извините, у меня нет времени тщательно проверять/тестировать iit. Я надеюсь, что это должно сработать (имейте в виду, что сделано с MSSQL, а не с 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)
);