如何在数据库中存储OR关系？

Question

考虑像经济学这样的专业。假设一个学生需要参加“EC101”或“EC102”，但不一定要同时参加这两门课程才能毕业。目前，我有两个这样的表：

课程: CourseID、CourseName

专业: MajorID、MajorName、RequiredCourseID

在我的数据库设计中，有时一个专业的需求是:选择课程A或课程B，但不一定两者都修，我如何才能满足这一需求？

Answer 1

专业有要求，而要求可以通过课程来满足。通过这种方式，专业也可以共享共同的需求。

major  requirementId
-----------------------
econ       1
econ       2
artOrSmth  2


requirementId     coursename
------------------------------
    1              econ101
    1              econ102
    2              math101

Answer 2

模式设计

您需要重构您的表，并引入一些额外的表：

ReqCount

• RequiredCourseOptions：
• 课程: CourseID、CourseName
• Majors: MajorID、ReqId
• Requirements: ReqId、CourseID

样本数据

从问题看：

经济学(EC001)：假设一个学生需要参加“EC101”或“EC102”，但不必同时参加这两门课程才能从该专业毕业。

其他要求：

此外，主修EC001的学生必须选修所有三门课程EC200，EC201，EC202。

政治经济学(EC002)：与经济学一样，学生需要选修“EC101”或“EC102”，但不必同时选修两门课。此外，主修EC002的学生必须选修EC200、EC201、EC202这三门课程中的任何一门。(可能还有这里没有讨论的其他课程。)

课程

Course ID    Name
EC 101       Economics 101
EC 102       Economics 102
EC 200       Economics 200
EC 201       Economics 201
EC 202       Economics 202

专业

Major ID     Name
EC001        Economics
EC002        Political Economy

MajorRequirements

MajorID      ReqID
EC001        R01
EC001        R02
EC002        R01
EC002        R03

要求

ReqID         ReqCount
R01           1
R02           3
R03           2

RequiredCourseOptions

ReqID         CourseID
1             EC 101
1             EC 102
2             EC 200
2             EC 201
2             EC 202
3             EC 200
3             EC 201
3             EC 202

释义

主修经济学(EC001)的人必须满足其专业的所有要求，这意味着必须满足MajorRequirements R01和R02。要满足R01要求，学生必须从可用选项中选择1门必修课程。所需的课程选项是EC 101和EC 102；任何一个都足够了。要达到R02，学生必须从可选课程中选修3门必修课；有3门课程(EC200、EC201、EC202)，因此学生必须选修所有这三门课程。

同样，主修政治经济学(EC002)的人必须满足其专业的所有要求，这意味着必须满足MajorRequirments R01和R03。如前所述，要满足R01要求，学生必须从可选课程(EC 101或EC 102)中选择1门必修课。要达到R03要求，学生必须从可选课程中选择两门必修课；有三门课程(EC200、EC201、EC202)，并且学生必须至少选修了三门课程中的两门。

显然，这可以用来要求任何集合中的任何N个课程。如果主修M需要特定的课程C，则MajorRequirements表包含主修M的ReqID R，ReqCount为1，RequiredCourseOptions记录R和C。R02所需的三门课程可以拆分为三个单独的要求，每个要求具有不同的ReqID值和ReqCount 1。然而，我想展示使用R03的3个课程中的2个的灵活性，对称性表明使用ReqCount 3的ReqID R02在某些方面更好。

挑选有资格毕业的学生

哪些学生有资格毕业？

假设有一个学生表，其中包含列StudentID、Name和MajorID (以及其他列，如出生日期、入学日期等)，外加另一个表StudentPassedCourses，其中包含列StudentID和CourseID (以及通过日期和通过等级等)。只有当学生通过课程后，条目才会显示在StudentPassedCourses中。

那么，有资格毕业的学生就是那些满足了各自专业要求的学生。

让我们使用TDQD-驱动查询设计一步一步地构建查询。

Q1:专业毕业的要求数量

SELECT MajorID, COUNT(ReqID) AS CountReqs
  FROM MajorRequirements
 GROUP BY MajorID

Q2:学生要求通过的次数

SELECT s.StudentID, m.ReqID, COUNT(*) AS PassCount
  FROM StudentPassedCourses AS p
  JOIN Students             AS s ON p.StudentID = s.StudentID
  JOIN MajorRequirements    AS m ON s.MajorID = m.MajorID
 GROUP BY s.StudentID, m.ReqID

(这是一个相当大的步骤；可能需要将其分解为单独的步骤。)

Q3:满足给定要求的学生

该列表列出了学生is和要求is，其中给定ReqID的学生通过计数至少是该专业所需的通过计数。

SELECT p.StudentID, p.ReqID
  FROM (SELECT s.StudentID, m.ReqID, COUNT(*) AS PassCount  -- Q2
          FROM StudentPassedCourses AS p
          JOIN Students             AS s ON p.StudentID = s.StudentID
          JOIN MajorRequirements    AS m ON s.MajorID = m.MajorID
         GROUP BY s.StudentID, m.ReqID
       ) AS p
  JOIN MajorRequirements AS m ON p.ReqID = m.ReqID
 WHERE p.PassCount >= m.ReqCount

Q4:每个学生的汇总需求计数

SELECT r.StudentID, COUNT(*) AS ReqsPassed
  FROM (SELECT p.StudentID, p.ReqID  -- Q3
          FROM (SELECT s.StudentID, m.ReqID, COUNT(*) AS PassCount  -- Q2
                  FROM StudentPassedCourses AS p
                  JOIN Students             AS s ON p.StudentID = s.StudentID
                  JOIN MajorRequirements    AS m ON s.MajorID = m.MajorID
                 GROUP BY s.StudentID, m.ReqID
               ) AS p
          JOIN MajorRequirements AS m ON p.ReqID = m.ReqID
         WHERE p.PassCount >= m.ReqCount
       ) AS r
 GROUP BY r.StudentID

Q5:通过专业考试的学生

SELECT s.StudentID, s.Name, s.MajorID, m.Name Major
  FROM Students AS s
  JOIN Majors   AS m ON m.MajorID = s.MajorID
  JOIN (SELECT r.StudentID, COUNT(*) AS ReqsPassed  -- Q4
          FROM (SELECT p.StudentID, p.ReqID  -- Q3
                  FROM (SELECT s.StudentID, m.ReqID, COUNT(*) AS PassCount  -- Q2
                          FROM StudentPassedCourses AS p
                          JOIN Students             AS s ON p.StudentID = s.StudentID
                          JOIN MajorRequirements    AS m ON s.MajorID = m.MajorID
                         GROUP BY s.StudentID, m.ReqID
                       ) AS p
                  JOIN MajorRequirements AS m ON p.ReqID = m.ReqID
                 WHERE p.PassCount >= m.ReqCount
               ) AS r
         GROUP BY r.StudentID
       )        AS c ON c.StudentID = s.StudentID
  JOIN (SELECT MajorID, COUNT(ReqID) AS CountReqs    -- Q1
          FROM MajorRequirements
         GROUP BY MajorID
       )        AS r ON r.MajorID = s.MajorID
 WHERE c.ReqsPassed >= r.CountReqs

警告:未测试的SQL！