sql-server - SQL数据库学生时间表系统

Question

我正在制作一个系统，该系统使用 SQL Server Management Studio 显示学生时间表并将其链接到 Visual Studio 程序(这与我的问题无关，只是添加了上下文)，但是我的数据库表存在重大问题此刻，我需要一些帮助。

我的项目的基本背景是有3个表，Lesson，Student和Subject。这些表中填充了小型测试样本数据集。

类(class)表:

LessonID    DayOfWk PeriodValue SubjectID   Room    blockLessonIsIn
-------------------------------------------------------------------
1           Mon     2           1           G8      2
2           Mon     3           1           G8      2
3           Mon     4           1           G8      2
4           Mon     5           2           N5      1
5           Tue     1           3           SF5     4
6           Tue     3           2           N7      1
7           Wed     1           3           SF5     4
8           Wed     2           1           H9B     2
9           Wed     5           1           G8      2
10          Thu     1           3           SF4     4
11          Thu     3           2           N7      1
12          Thu     5           3           SF5     4
13          Fri     1           3           SF5     4
14          Fri     2           1           G8      2
15          Fri     3           1           H9B     2
16          Fri     4           2           SP2     1
17          Mon     1           5           H1      1
18          Tue     5           5           H1      1
19          Thu     3           5           H1      1
20          Fri     4           5           H1      1
21          Wed     4           4           S1      3
22          Fri     5           4           S1      3
23          Tue     1           2           N1      4
24          Tue     2           2           N1      4
25          Wed     1           2           N1      4
26          Thu     1           2           N1      4
27          Thu     4           2           N1      4 

主题表:

SubjectID   Title
---------------------------------
1           Computing
2           Maths
3           Economics
4           Physics
5           Geography

学生表:

UserID  Forname Surname     SchlYear    InOrOut Block1  Block2  Block3  Block4  Pword
----------------------------------------------------------------------------------

1       Jake    Richardson  13          1       2       1       NULL    3       password
2       Russell Penn        13          1       5       1       NULL    2       russpass
3       Xander  Sheppard    13          1       2       1       4       NULL    xander
4       Dan     Bostock     13          1       2       1       4       NULL    pass

这些表通过以下方式相互链接:

• Lesson 表 -> Subject 表通过 SubjectID 作为外键
• Subject 表 -> Student 表通过 Block1、Block2、Block3和 Block4，每个 block 中都有一个 SubjectID 作为外键，每个 block 都与一个主题相关，每个学生最多可以选择 4 个可能的主题

我的目标是查询表格，以便在查询运行一天和期间值时可以找到一个奇异值，并输入个人 userID，从而找到个人的类(class)在那天和期间。

我目前的查询如下，如果试图找到 Jake Richardson 在星期二第 1 期的类(class):

SELECT 
    lesson.SubjectID
FROM 
    lesson_tbl AS lesson
LEFT OUTER JOIN 
    student_tbl AS subject1 ON lesson.SubjectID = subject1.Block1
LEFT OUTER JOIN 
    student_tbl AS subject2 ON lesson.SubjectID = subject2.Block2
LEFT OUTER JOIN 
    student_tbl AS subject3 ON lesson.SubjectID = subject3.Block3
LEFT OUTER JOIN 
    student_tbl AS subject4 ON lesson.SubjectID = subject4.Block4
WHERE 
    lesson.DayOfWk = 'Mon' 
    AND lesson.PeriodValue = '5'  
    AND ((subject1.UserID = '1' AND lesson.blockLessonIsIn = '1') OR 
         (subject2.UserID = '1' AND lesson.blockLessonIsIn = '2') OR 
         (subject3.UserID = '1' AND lesson.blockLessonIsIn = '3') OR 
         (subject4.UserID = '1' AND lesson.blockLessonIsIn = '4'));

这将成功返回值 2，即 Maths。

但是，例如，如果我们要在星期二第 1 期搜索 Russell Penn 的值:

SELECT 
    lesson.SubjectID
FROM 
    lesson_tbl AS lesson
LEFT OUTER JOIN 
    student_tbl AS subject1 ON lesson.SubjectID = subject1.Block1
LEFT OUTER JOIN 
    student_tbl AS subject2 ON lesson.SubjectID = subject2.Block2
LEFT OUTER JOIN 
    student_tbl AS subject3 ON lesson.SubjectID = subject3.Block3
LEFT OUTER JOIN 
    student_tbl AS subject4 ON lesson.SubjectID = subject4.Block4
WHERE 
    lesson.DayOfWk = 'Tue' 
    AND lesson.PeriodValue = '1'  
    AND ((subject1.UserID = '2' AND lesson.blockLessonIsIn = '1') OR 
         (subject2.UserID = '2' AND lesson.blockLessonIsIn = '2') OR 
         (subject3.UserID = '2' AND lesson.blockLessonIsIn = '3') OR 
         (subject4.UserID = '2' AND lesson.blockLessonIsIn = '4'));

那么返回值是 2 的 3 个值，即 3 个数学值，而它应该只返回 1 个数学值，因为在我添加的阻塞约束内星期二在第 1 期只有 1 个数学值。

我知道数学跨越 block 1 和 block 4，但这是想法，blockLessonIsIn 旨在纠正这样一个事实，即它应该只选择与数学所在的特定 block 相关的类(class)。

我的数据库设计是否存在严重错误，或者是我愚蠢地错过了一个明显的错误。数据库将被塑造到我的程序中，该程序已经创建以传递诸如 DayOfWk 和 PeriodValue 之类的值，所以这只是唯一阻碍我的事情，但它让我完全不知道在哪里选择了数学的 3 个值。

请注意，有些列是时间表不需要的，因此如果它们没有被引用(即类(class)表中的房间)，请忽略它们。

如果您对我的程序还有任何疑问或我可能忘记提及的任何事情，或者需要我更详细地说明我试图用某些代码行实现的目标，请务必提问，因为这是主要的我程序中的绊脚石。

感谢您提供的任何帮助!

Answer 1

我认为您的类(class)表中可能有一些数据损坏。在阅读了您的问题以及您希望能够做什么之后，我认为 Lesson 中的每一对 (Day, Period) 应该是独一无二的，这对我来说似乎是合理的。例如，如果一个学生在 Wednesday Period 1，那么我们应该可以明确地说他在 Math。然而，情况似乎并非如此。如果我们运行以下查询，

select dayofwk, periodValue, COUNT(*)
    from lesson_tbl
    group by dayofwk, periodValue
    having COUNT(*) > 1

我们得到如下结果

+ ------- + ----------- + ----- +
| dayofwk | periodValue | count |
+ ------- + ----------- + ----- +
| thu     | 1           | 2     |
| tue     | 1           | 2     |
| wed     | 1           | 2     |
| thu     | 3           | 2     |
| fri     | 4           | 2     |
+ ------- + ----------- + ----- +

这告诉我们这些(日、期间)组合不是唯一的。如果学生在星期三第 1 期，那么我们无法确定他是在数学还是经济学。

从这里开始做什么

(1) 如果你不相信一个(day, period)组合在课表中应该是唯一的，那么你想要的是不可能的。您将无法找到(用户、日期、期间)的“一个奇异值”。

(2) 如果您认为 (day,period) 组合在 Lesson 表中必须是唯一的，则修复损坏的数据。然后构建以下查询

declare @dayQuery varchar(3) = 'tue'
declare @PeriodQuery int = 1
declare @userQuery int = 1

select *
    from lesson_tbl L
    inner join student_tbl U
        on case L.blockLessonIsIn when 1 then U.Block1
                                  when 2 then U.Block2
                                  when 3 then U.Block3
                                  when 4 then U.Block4
           end = L.subjectID
    where L.dayofwk = @dayQuery
        and L.periodValue = @PeriodQuery
        and U.userID = @userQuery