考试数据库设计

Question

我正在为一次考试设计数据库，我被困住了。我不知道怎么做。

以下是有关资料：

• 学生回答10个问题。
• 每个问题有三个选项(答案)，学生选择一个。
• 只有一个答案是正确的，另外两个是错误的。
• 学生只能参加一次考试。他们不能再试了。
• 不会有其他考试了，这是唯一的考试。

我需要数据库设计方面的帮助。

到目前为止，我自己尝试过的是：

表学生

ID bigint (primary key, identity)
Name nvarchar(MAX)

表格问题

ID bigint (primary key, identity)
TextOfTheQuestion nvarchar(MAX)

表回答

ID bigint (primary key, identity)
TextOfTheAnswer nvarchar(MAX)
QuestionID bigint (foreign key to Questions.ID)
isCorrectAnswer bit

表StudentChoices

StudentID bigint (primary key, foreign key to Students.ID)
AnswerID bigint (primary key, foreign key to Answers.ID)

这是我为学习而设计的个人设计。我正在努力学习实体框架+ C#。

Answer 1

在bigint和nvarchar上容易(最大)

Table Student:

ID int (primary key, identity)
Name nvarchar(800)

Table Question:

ID smallint (primary key, identity)
TextOfTheQuestion nvarchar(800)
CorrectAns tinyint FK (to AnswerNum.Num)  
tested and can make a composite FK to Answer  QuestionID, AnsNum   
I was not sure could do this but it would mean you have to create the question  
Then populate Answer  
Then come back and edit question for CorrectAns so may not be the way you want to go

Table Answer:

QuestionID smalling PK FK (to Question.ID) 
AnswerNum tinyint   PK FK (to AnswerNum.Num)
TextOfTheAnswer nvarchar(800)


Table StudentChoice:

StudentID int       (primary key, foreign key to Student.ID)
QuestionID smallint (primary key)
AnswerNum tinyint 
(QuestionID, AnswerNum) REFERENCES Answer (QuestionID, AnswerNum)

Table AnswerNum:

Num tinyint PK 
-- just values 1, 2, 3
-- this way you can change number of questions in the future 

你就是这样得分的

当主查询发出干净时，它是良好的db设计的标志。

  select Student.Name, count(*) as score 
  from student
  left join StudentChoice
         on Student.ID = StudentChoice.StudentID 
  left join Question
         on Question.QuestionID = StudentChoice.QuestionID 
        and Question.CorrectAns = StudentChoice.AnswerNum   
            -- CorrectAns in Question makes this a whole lot easier  
  group by Student.Name 

Answer 2

我想你已经把它固定在了你的初始模式上。

但是，由于我们处理的是MCQ场景，所以您不需要“答案”表--答案可以包含在问题中(以及两个不正确的答案--参见下面)。这将大大简化问题。我建议的另一个较小的更改是在试题表中有一个Question_ID字段作为PRIMARY KEY。

下面我准备了一个模式--恐怕是MySQL，我不经常使用，也没有一个实例需要处理。“翻译”不应该太难。

几点意见。

与其在每个表中使用" ID“作为字段名，我建议您使用"Table_Name_ID”--它可以使您的SQL更清晰，也有助于调试--如果您收到一条消息，上面写着“.带有ID的插入.”--您不知道哪个表的ID是消息引用的消息，而如果明确地命名它们，错误消息就变得有意义。

CREATE TABLE Student (Student_ID int, Name VARCHAR(25));
CREATE TABLE Question (Question_ID int, Name VARCHAR(25), Question_Text 
                       VARCHAR(64000), Answer1 VARCHAR(4096), 
                       Answer2 VARCHAR(4096), Answer3 VARCHAR(4096),
                       Correct_Answer TINYINT);
CREATE TABLE Student_Response (Student_ID int, Question_ID int,
                               Student_Answer TINYINT);

如果您的问题被表述成标记为“A”、“B”或“C”，则需要将TINYINT修改为CHAR(1)。

您会注意到，我使用的FOREIGN KEYs &c的名称相对较长，并且(希望)容易理解。

• 程序员不需要翻阅文档就能发现实体是什么--这是不言而喻的，
• 调试是绝对必要的--这是绝大多数应用程序花费大部分时间的地方。收到一条消息：“.约束SYS00003433已被违反.”一点帮助都没有。Oracle很适合为未命名的约束提供自己的“特殊”系统生成的名称。

您还会注意到，我使用了单数表名。我建议你也这样做--这样你就不用去想“嗯……是学生还是学生？”无论哪种方式，选择一个标准并坚持它。

如果你在谷歌上搜索"数据库设计最佳实践"，你会得到很多网站--花点时间和精力去阅读其中的几个，看看他们的共识是什么--如果很多人认为这是个好主意，那可能就是了！

ALTER TABLE Student_Response 
  ADD CONSTRAINT fk_sr_student FOREIGN KEY (Student_ID) 
    REFERENCES Student (Student_ID);
ALTER TABLE Student_Response 
  ADD CONSTRAINT fk_sr_question FOREIGN KEY (Question_ID) 
    REFERENCES Question (Question_ID);

这一独特的限制是消除对同一问题有两个答案的可能性。

ALTER TABLE Student_Response 
  ADD CONSTRAINT uq_sr_student_question 
    UNIQUE (Student_ID, Question_ID);

尽管你明确排除了这个可能性，但相对来说，添加一个考试(相同的考试，不同的时间)表，或者可能添加一个“尝试”表，然后为不同的考试(即databases101，databases201 )准备一个考试表，应该是相对容易的。

也许你应该考虑一个日期，甚至一个与尝试相关的日期-在上午和下午参加同样的考试？

在一天结束时，人们可以设计，直到母牛回家。避免使用强迫设计障碍 --我的意思是选择(预先)一个让您对系统的功能满意的点，然后就此结束。我知道掉进陷阱是多么容易，不断增加“比特和鲍勃”，而没有得到真正的工作。

Answer 3

“设计数据库”考试任务解决方案很可能应该向学生展示如何使用DB数据完整性工具(PK、FK)来表达域业务规则。

你的解决方案涵盖了其中的大部分。缺少的规则是：

(1)只有一个答案是问题的解决办法。没有触发器或额外的应用程序代码，位标志没有帮助。

(2)学生只能选择一个问题的答案。

Table Students:

ID int (primary key, identity)
Name nvarchar(200)

Table Questions:

ID int (primary key, identity)
TextOfTheQuestion nvarchar(4000)
SolutionID int
foreign key (SolutionID) to Answers(ID)

我添加了SolutionID，这样答案就有了一个解决方案。

Table Answers:

ID int (primary key, identity)
QuestionID int (foreign key to Questions.ID)
TextOfTheAnswer nvarchar(4000)

要满足(2) StudentChoices的PK必须是(StudentID，QuestionID)，这意味着学生回答了问题，他的回答是……。

Table StudentChoices:

StudentID int (primary key, foreign key to Students.ID)
QuestionID int (primary key)
AnswerID int 

现在出现了一个问题，AnswerID和QuestionID不能相互矛盾，即AnswerID必须属于QuestionID引用的问题。我们可以通过在StudentChoices上声明FK来实现这一点：

foreign key (QuestionID, AnswerID) to Answers(QuestionsID, ID)

为了能够在上面的FK中指定答案(QuestionsID，ID)，必须在答案处声明这对属性是唯一的：

unique (QuestionID,ID) -- target of FK from Questions, StudentChoices

声明这样的唯一是绝对保存的，因为PK的每个超集都是唯一的。使用相同的唯一，我们还确保SolutionID完全属于这个问题的答案，方法是用

foreign key (ID,SolutionID) to Answers(QuestionsID, ID)