设计一个简单的教师 - 学科 - 学生 - 批次关系数据库的方式是什么？ [英] What is the way of designing a simple teacher-subject-student-batch relational database?

问题描述

条件是

1. 许多教师对一个科目关系，即一个科目可以由多个教师教授，




2. 许多学生与许多学科关系，也就是说许多学生可以选择一个共同的主题，一个学生可以选择多个学科。

一次，所以在批处理表中需要一个唯一的日期字段。



3. 许多批次到一个主题关系，




最后一个条件是给我一个问题，那就是很多批次到一个教师关系，那
是一个教师可以教多个批次和每个批次只有一个教师。

我的初始方法如下

 教师表
 ----------------------- 
 id（PK）name email subject_id（FK to subject.id）
 
 
 
主题表
 ----------------------- 
 id（PK）名称描述
 
 
 
学生表
 ------------------- --- 
 id（PK）名称位置
 
 
 
批处理表
 --------------- ------- 
 id（PK）venue teacher_id（FK to teacher.id）date（unique）
 
 
 
学生批处理表
 ----------------------- 
 id（PK）batch_id（FK to batch.id）student_id（FK to student.id）
  

并且需要得到的信息让我们说批次的id，老师的名字，学生的名字为特定的一天。

但是，当分配教师到批次时，问题就出现了，在每个批次中，只有一个特定的主题正在教授，并且一个教师不能教多于一个主题。 / p>

解决方案

您缺少一个关联主题和学生的表格（第2点）：

  // student [student_id] take subject [subject_id] 
 takes（student_id，subject_id）
  

请注意，每个基表都有一个关于应用程序情况的关联语句。使语句为真的行将在表中。注意表定义看起来像语句的缩写。

  //教师[id]电子邮件]教学主题[subject_id] 
教师（id，姓名，电子邮件，subject_id）
 
 // subject [id] named [name] is [description] 
 subject ，名称，描述）
 
 //学生[id]命名为[name]住在[位置]）
学生（id，名称，位置）
 
 / / batch [id]在地点[地点]由教师[teacher_id]在日期[date]教授
 batch（id，venue，teacher_id，date）
 
 // student-batch [ id] reports student [student_id] in batch [batch_id] 
 student-batch（id，student_id，batch_id）
 CONSTRAINT？ -  student [student_id]采用批次教师所教授的科目[batch_id] 
  

你似乎很难猜到这一点，我将如何推导表，约束和查询设计。

要在SQL中表达任何表，约束或查询首先决定其声明。然后我们可以将语句转换为速记。然后我们可以将速记转换为SQL。

表保存使其语句为真的行。因此，我们通过在SQL中写入表名来获得满足基表语句的行。

我们通过JOINing语句的表来获得满足两个语句的AND的行SQL。如果我们想要满足条件的AND的行，那么我们在SQL中使用ON或WHERE。对于OR，我们使用UNION。对于AND NOT，我们使用EXCEPT / MINUS（或LEFT JOIN成语）。

如果我们想要行满足语句FOR SOME的列值，其中THERE EXISTS一些值列（但我们不想要的值），那么我们使用SELECT删除SQL中的列。我们可以在我们的语句中使用FOR，我们保留的列。对于所有列的一些或者所有列不能在SQL中写入，但是如果我们想知道是否有行满足语句，那么在SQL中，我们使用语句的SELECT作为条件的EXISTS。

  // student [student_id]采用批次教师教授的科目[batch_id] 
为某些姓名，电子邮件，地点，日期，takes.student_id，...（
 student [takes.student_id]需要主题[takes.subject_id] 
和教师[teacher.id]用电子邮件命名[name] .subject_id] 
和在[场所]地点的批次[batch.id]由日期[date]的老师[batch.teacher_id]教授
 AND [takes.subject_id] = [teacher.subject_id] 
 AND [teacher.id] = [batch.teacher_id] 
 AND [student_id] = [takes.student_id] 
 AND [batch_id] = [batch.id]）
  

速记：

  / student [student_id]采用批次教师所教授的科目[batch_id] 
 For Some ...（
 takes（takes.student_id，takes.subject_id）
和teacher 
和batch（batch.id，venue，batch.teacher_id，date）
 AND takes.subject_id = teacher.subject_id 
和老师。 id = batch.teacher_id 
 AND student_id = takes.student_id 
和batch_id = batch.id）
  

SQL：通过表格替换语句，通过JOIN或ON或WHERE替换语句，通过SELECT替换某些或其他存在的EXISTS：

  // student [student_id]采用由batch [batch_id]的教师教授的主题
 SELECT * 
 FROM需要JOIN教师JOIN批处理
 WHERE .subject_id = teacher.subject_id 
 AND teacher.id = batch.teacher_id 
 AND student_id = takes.student_id 
和batch_id = batch.id 
  

对于约束，我们需要一个条件（而不是表），没有满足该语句的行：

  //是否有EXISTS student_id，batch_id（
 student [student_id]需要由batch [batch_id]的老师教授的主题）
 EXISTS ... that ...）
  

不幸的是，这不是一个约束， mySQL或大多数DBMS。

编辑：

现在我们在写一个查询。我们需要的行在哪里：

 对于batch.id，teacher.name，student.name（
需要
和student（teacher.id，teacher.name，email，teacher.subject_id）
和batch（batch.id，venue，batch.teacher_id，date）
 AND student（student.id，student.name，location）
和student-batch（student-batch.id，student-batch.student_id，student-batch.batch_id）
 AND takes.subject_id = teacher .subject_id 
 AND teacher.id = batch.teacher_id 
 AND student.id = takes.student_id 
和student-batch.student_id = takes.student_id 
和student-batch.batch_id = batch.id 
 AND @date = date）
  

对于FOR SOME实际返回一些列，一些其他行被添加和一些模糊的名称被点缀。 SQL直接翻译。注意，现在我们必须加入学生批处理，因为约束假设我们处理学生批次中的一行（id，student_id，batch_id）。

  SELECT batch.id AS batch，teacher.name AS Teacher，student.name AS student 
 FROM takes JOIN teacher JOIN batch JOIN student JOIN student-batch 
 WHERE ... AND @date = date 
  

您可以尝试ON版本：

 选择batch.id AS批次，teacher.name AS教师，student.name AS学生
 FROM从
 JOIN教师ON takes.subject_id = teacher.subject_id 
 JOIN批处理开启teacher.id = batch.teacher_id 
 JOIN ... 
 WHERE @date = date 
  

Conditions are

1. Many teachers to one subject relationship , that is one subject can be taught by more than one teachers, but one teacher can only teach one subject

2. Many students to many subjects relationship, that is many students can take a common subject and one student can take many subjects.

3. Many students to many batches relationship.

4. Two batches can not be held in one time , so need a unique date field in batch table.

5. Many batches to one subject relationship, that is only one subject is being taught per batch. But in other batches same subject which is taught in previous batch, can be repeated.

6. The last condition is giving me problem, that is many batches to one teacher relationship , that is a teacher can teach in more than one batches and only one teacher for each batch.

My initial approach was as below

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)

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

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.

解决方案

You are missing a table associating subjects and students (per point 2):

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

Notice that each base table has an associated statement about the application situation. The rows that make the statement true go in the table. Notice that the table definition looks like a shorthand for the statement.

// 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)
CONSTRAINT ?? -- student [student_id] takes the subject that is taught by the teacher of batch [batch_id]

Since you seem stumped about this I will derive it in terms of how we can reason for table, constraint and query design.

To express any table, constraint or query in SQL we first decide on its statement. Then we can convert the statement to shorthand. Then we can convert the shorthand to SQL.

A table holds the rows that make its statement true. So we get the rows satisfying a base table statement by writing the table name in SQL.

We get the rows satisfying the AND of two statements by JOINing the statements' tables in SQL. If we want rows satisfying the AND of a condition then we use ON or WHERE in SQL. For OR we use UNION. For AND NOT we use EXCEPT/MINUS (or a LEFT JOIN idiom).

If we want rows satisfying a statement FOR SOME values of columns ie where THERE EXISTS some values for columns (but we don't want the value) then we use SELECT to drop the columns in SQL. We could just use FOR in our statement re the columns that we keep. FOR SOME or THERE EXISTS over all columns can't be written in SQL but if we want to know whether there are rows satisfying a statement then in SQL then we use EXISTS around the statement's SELECT as a condition.

// student [student_id] takes the subject that is taught by the teacher of batch [batch_id]
FOR SOME name, email, venue, date, takes.student_id, ... (
    student [takes.student_id] takes subject [takes.subject_id]
AND teacher [teacher.id] named [name] with email [email] teaches subject [teacher.subject_id] 
AND batch [batch.id] at venue [venue] was taught by teacher [batch.teacher_id] on date [date]
AND [takes.subject_id] = [teacher.subject_id]
AND [teacher.id] = [batch.teacher_id]
AND [student_id] = [takes.student_id]
AND [batch_id] = [batch.id])

Shorthand:

// student [student_id] takes the subject that is taught by the teacher of batch [batch_id]
FOR SOME ... (
    takes(takes.student_id,takes.subject_id)
AND teacher(teacher.id,name,email,teacher.subject_id)
AND batch(batch.id,venue,batch.teacher_id,date)
AND takes.subject_id = teacher.subject_id
AND teacher.id = batch.teacher_id
AND student_id = takes.student_id
AND batch_id = batch.id)

SQL: Replace statements by their tables, AND by JOIN or ON or WHERE, FOR SOME or THERE EXISTS by SELECT, and where wanted EXISTS:

// student [student_id] takes the subject that is taught by the teacher of batch [batch_id]
SELECT *
FROM takes JOIN teacher JOIN batch
WHERE takes.subject_id = teacher.subject_id
AND teacher.id = batch.teacher_id
AND student_id = takes.student_id
AND batch_id = batch.id

For a constraint we want a condition (not a table) that there are no rows satisfying that statement:

 // whether THERE EXISTS student_id,batch_id (
     student [student_id] takes the subject that is taught by the teacher of batch [batch_id])
 EXISTS (...that SELECT...)

Unfortunately this is not a constraint that you can easily check in mySQL or most DBMSs.

EDIT:

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

Now we are writing a query. We want rows where:

FOR batch.id, teacher.name, student.name (
    takes(takes.student_id,takes.subject_id)
AND teacher(teacher.id,teacher.name,email,teacher.subject_id)
AND batch(batch.id,venue,batch.teacher_id,date)
AND student(student.id,student.name,location)
AND student-batch(student-batch.id,student-batch.student_id,student-batch.batch_id)
AND takes.subject_id = teacher.subject_id
AND teacher.id = batch.teacher_id
AND student.id = takes.student_id
AND student-batch.student_id = takes.student_id
AND student-batch.batch_id = batch.id
AND @date = date)

This is almost like the constraint except for the FOR SOME actually returning some columns, some other lines being added and some ambiguous names being dotted. The SQL is just as directly translated. Notice that now we have to join with student-batch because the constraint was assuming we were dealing with a row (id,student_id,batch_id) from student-batch.

SELECT batch.id AS Batch, teacher.name AS Teacher, student.name AS Student
FROM takes JOIN teacher JOIN batch JOIN student JOIN student-batch
WHERE ... AND @date = date

You could try an ON version:

SELECT batch.id AS Batch, teacher.name AS Teacher, student.name AS Student
FROM takes
JOIN teacher ON takes.subject_id = teacher.subject_id
JOIN batch ON teacher.id = batch.teacher_id
JOIN ...
WHERE @date = date

这篇关于设计一个简单的教师 - 学科 - 学生 - 批次关系数据库的方式是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！