Как создать простую реляционную базу данных "учитель-предмет-ученик-партия"?

Условия

  1. Много учителей по одному предметному отношению, то есть одному предмету, могут учить более одного учителя, но один учитель может преподавать только один предмет

  2. Отношение многих студентов ко многим предметам, то есть многие студенты могут изучать общий предмет, и один студент может изучать много предметов.

  3. У многих учеников много партий отношений.

  4. Две партии не могут быть проведены одновременно, поэтому необходимо уникальное поле даты в таблице партий.

  5. Много партий одного предметного отношения, то есть только один предмет преподается за партию. Но в других партиях тот же предмет, который преподается в предыдущей партии, может быть повторен.

  6. Последнее условие ставит меня перед проблемой: это много партий для одного учителя, то есть учитель может преподавать больше, чем в одной партии, и только один учитель для каждой партии.

Мой первоначальный подход был как ниже

teacher table
-----------------------
id(PK)   name   email   subject_id(FK to subject.id)



subject table
-----------------------
id(PK)   name   description



student table
----------------------
id(PK)   name   location



batch table
----------------------
id(PK)   venue   teacher_id(FK to teacher.id)   date(unique)



student-batch table
-----------------------
id(PK)   batch_id(FK to batch.id)   student_id(FK to student.id)

И нужно получить информацию, скажем, идентификатор партии, имя учителя, имя ученика на определенный день.

Но проблема возникает при назначении учителя для партий, в каждой партии преподается только один конкретный предмет, а также один учитель не может преподавать более одного предмета.

Источник

1 ответ

Решение

Вам не хватает таблицы, связывающей предметы и студентов (по пункту 2):

// student [student_id] takes subject [subject_id]
takes(student_id, subject_id)

Обратите внимание, что с каждой базовой таблицей связан шаблон выписки для выписок о бизнес-ситуации, параметризованный именами столбцов - его (характеристическим) предикатом. Строки, которые делают предикат истинным, попадают в таблицу. Обратите внимание, что определение таблицы выглядит как сокращение для предиката.

// teacher [id] named [name] with email [email] teaches subject [subject_id]
teacher(id, name, email, subject_id)

// subject [id] named [name] is [description]
subject(id, name, description)

// student [id] named [name] lives at [location])
student(id, name, location)

// batch [id] at venue [venue] was taught by teacher [teacher_id] on date [date]
batch(id, venue, teacher_id, date)

// student-batch [id] reports student [student_id] being in batch [batch_id]
student-batch(id, student_id, batch_id)
// CHECK student [student_id] takes the subject that is taught by the teacher of batch [batch_id]

Но проблема возникает при назначении учителя для партий, в каждой партии преподается только один конкретный предмет, а также один учитель не может преподавать более одного предмета.

Поскольку вы, кажется, озадачены этим, я выведу его с точки зрения того, как мы можем обосновать таблицы, ограничения и дизайн запросов. Один из способов выразить ограничение, которое вы хотите, кажется, прокомментированный CHECK выше.

Чтобы выразить любую таблицу, ограничение или запрос в SQL, мы сначала выбираем его предикат. Затем мы можем преобразовать предикат в стенографию. Затем мы можем преобразовать сокращение в SQL.

Сказуемое:

student [student_id] takes the subject that is taught by the teacher of batch [batch_id]

Использование предикатов базовой таблицы:

FOR SOME k.*, t.*, b.* (
    student_id = k.student_id AND batch_id = b.bid
AND student [k.student_id] takes subject [k.subject_id]
AND teacher [t.id] named [t.name] with email [t.email] teaches subject [t.subject_id] 
AND batch [b.id] at venue [b.venue] was taught by teacher [b.teacher_id] on date [b.date]
AND [k.subject_id] = [t.subject_id]
AND [t.id] = [b.teacher_id])

Использование сокращений:

FOR SOME k.*, t.*, b.* (
    student_id = k.student_id AND batch_id = b.bid
AND takes(k.student_id, k.subject_id)
AND teacher(t.id, t.name, t.email, t.subject_id)
AND batch(b.id, b.venue, b.teacher_id, date)
AND k.subject_id = t.subject_id
AND t.id = b.teacher_id)

В FROM каждый (возможно, неявный) псевдоним представляет таблицу, подобную заданному имени базовой таблицы и / или подзапросу, но каждый столбец имеет свое значение и предикат, переименованный в псевдоним.колонна

Мы получаем строки, удовлетворяющие AND двух предикатов, путем соединения таблиц предикатов в SQL. Если нам нужны строки, удовлетворяющие условию AND, тогда мы используем ON или WHERE в SQL.

Предложение SELECT возвращает строки, в которых значения FOR SOME столбцов с точками возвращаемые столбцы (без точек) равны функциям столбцов с точками, которые удовлетворяют предикату FROM.

SQL: Замените операторы их таблицами, И с помощью JOIN, ON или WHERE, и внешним FOR SOME & THERE EXISTS, используя SELECT:

SELECT t.student_id AS student_id, b.bid AS batch_id
FROM takes k JOIN teacher t JOIN batch b
WHERE k.subject_id = t.subject_id
AND t.id = b.teacher_id
AND student_id = t.student_id
AND batch_id = b.id

Таблица строк, удовлетворяющая OR двух предикатов, является объединением их таблиц. Для AND NOT мы используем EXCEPT (он же MINUS) (или идиома LEFT JOIN). НЕОБХОДИМО ДЛЯ НЕКОТОРЫХ ИЛИ СУЩЕСТВУЮЩИХ по всем столбцам в SQL нельзя запросить, но если мы хотим узнать, есть ли строки, удовлетворяющие предикату, то мы можем использовать EXISTS вокруг подзапроса с этим предикатом.

Предположим, мы хотим ограничить базовую таблицу, чтобы каждая строка удовлетворяла предикату в некоторых столбцах. Т.е. ДЛЯ ВСЕХ столбцов, ЕСЛИ они удовлетворяют базовому предикату, ТОГДА они удовлетворяют предикату запроса. Т.е. ДЛЯ ВСЕХ столбцов, ЕСЛИ строка, которую они формируют, находится в базе, ТОГДА в запросе. Таким образом, мы требуем в SQL, что НЕ СУЩЕСТВУЕТ (ВЫБЕРИТЕ столбцы ОТ базового ИСКЛЮЧЕНИЯ запроса). Или для каждой строки в базе данных мы требуем в SQL, что EXISTS (запрос).

В стандартном SQL вы можете создать CREATE ASSERTION CHECK(НЕ СУЩЕСТВУЕТ (ВЫБРАТЬ student_id, batch_id ОТ студенческого пакета EXCEPT запрос)) или в CREATE TABLE студенческом пакете вы можете ПРОВЕРИТЬ (EXISTS(запрос)). К сожалению, они не поддерживаются MySQL или большинством СУБД. Если вы ВСТАВЛЯЕТЕ в студенческую партию за партией, то при срабатывании вы можете требовать, чтобы EXISTS (запрос). Или вы можете добавить определенные столбцы и составные ограничения FK (внешний ключ).

И нужно получить информацию, скажем, идентификатор партии, имя учителя, имя ученика на определенный день.

Сейчас мы пишем запрос. Мы хотим строки где:

FOR k.*, t.*, b.*, s.*, sb.* (
    batch = b.id AND teacher = t.name AND student = s.name
AND takes(k.student_id, k.subject_id)
AND teacher(t.id, t.name, t.email, t.subject_id)
AND batch(b.id, b.venue, b.teacher_id, b.date)
AND student(s.id, s.name, s.location)
AND student-batch(sb.id, sb.student_id, sb.batch_id)
AND k.subject_id = t.subject_id
AND t.id = b.teacher_id
AND s.id = k.student_id
AND sb.student_id = k.student_id
AND sb.batch_id = b.id
AND @date = b.date)

Это похоже на предикат ограничения с различными возвращаемыми столбцами и добавленными строками. SQL так же прямо переведен. Мы добавляем соединение со студентом, чтобы получить имена студентов. Мы добавляем соединение со студентом-партией, потому что ограничение не справляется с этим; контексты, использующие запрос ограничения, проверяют, есть ли в нем подклассы student-batch (student_id, batch_id).

SELECT b.id AS batch, t.name AS teacher, s.name AS student
FROM takes k JOIN teacher t JOIN batch b JOIN student s JOIN student-batch sb
WHERE ... AND @date = date

Вы можете попробовать версию ON:

SELECT b.id AS Batch, t.name AS Teacher, s.name AS Student
FROM takes k
JOIN teacher t ON k.subject_id = t.subject_id
JOIN batch b ON t.id = b.teacher_id
JOIN ...
WHERE @date = b.date
Источник
Другие вопросы по тегам