mysql - 如何设计一个简单的教师-科目-学生批处理关系数据库？

Question

条件是

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)

并且需要获取特定日期的批处理 ID、教师姓名、学生姓名等信息。

但是在分配教师到批处理时会出现问题，每个批处理只能教授一门特定科​​目，而且一名教师不能教授多个科目。

Answer 1

您缺少关联科目和学生的表格(每点 2):

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

请注意，每个基表都有一个关联的语句模板，用于有关业务情况的语句，并通过列名称进行参数化 - 它的(特征)谓词。使谓词为 true 的行进入表中。请注意，表定义看起来像是谓词的简写。

// 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]

But the problem is occuring when assigning teacher to batches, in each batch only one particular subject is being taught, and also one teacher can not teach more than one subject.

既然你似乎对此感到困惑，我将根据如何推理表、约束和查询设计来推导它。表达您想要的约束的一种方法似乎是上面注释的 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 中，每个(可能是隐式的)别名表示一个表，类似于给定的基表名称和/或子查询，但其值和谓词中的每一列均重命名为别名。列.

我们通过在 SQL 中 JOIN 谓词表来获取满足两个谓词 AND 的行。如果我们想要满足条件 AND 的行，那么我们在 SQL 中使用 ON 或 WHERE。

SELECT 子句返回的行中，对于点列的某些值，返回的(未点列)列等于满足 FROM 谓词的点列的函数。

SQL:用表替换语句，用 JOIN 或 ON 或 WHERE 替换语句，用 SELECT 替换外部 FOR SOME & THERE EXISTS:

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 的行表是它们表的 UNION。对于 AND NOT，我们使用 EXCEPT(又名 MINUS)(或 LEFT JOIN 惯用语)。 FOR SOME 或 THERE EXISTS 无法在 SQL 中查询所有列，但如果我们想知道是否有满足谓词的行，那么我们可以在带有该谓词的子查询周围使用 EXISTS。

假设我们想要约束一个基表，以便每一行都满足某些列上的谓词。即，对于所有列，如果它们满足基本谓词，那么它们满足查询谓词。即 FOR ALL 列 IF 它们形成的行位于基数中，那么它位于查询中。所以我们在 SQL 中要求 NOT EXISTS(从基本查询中选择列)。或者对于数据库中的每一行，我们需要在 SQL 中存在 EXISTS(query)。

在标准 SQL 中，您可以 CREATE ASSERTION CHECK(NOT EXISTS (SELECT Student_id, batch_id FROM Student-batch EXCEPT query)) 或在 CREATE TABLE Student-batch 中，您可以 CHECK(EXISTS(query))。不幸的是，MySQL 或大多数 DBMS 不支持这些。如果您在批处理后插入到学生批处理，那么您可以在触发器上要求存在(查询)。或者您可以添加某些列和复合 FK(外键)约束。

And need to get the information lets say batch's id, teacher's name, students' name for a particular day.

现在我们正在编写一个查询。我们想要的行位于:

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