Database design for an exam

Question

I am designing database for an exam, and I am stuck. I do not know how to do it.

Here is the relevant information:

• Student answers 10 questions.
• Each question has 3 options (answers) and student chooses one.
• Only one answer is correct, other 2 are wrong.
• Students can take this exam only once. They can not try again.
• There will be no other exams, this is the only one.

I need help with database design.

What I have tried so far on my own:

Table Students

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

Table Questions

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

Table Answers

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

Table StudentChoices

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

This is my personal design for learning purposes. I am trying to learn Entity framework + C# on my own.

Answer 1

Easy on the bigint and nvarchar(MAX)

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 

This is how you score
When the primary query comes out clean it is sign of a good db design

  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

I think you've pretty much nailed it on your initial schema.

However, since we're dealing with an MCQ scenario, you don't require an "Answers" table - the answer can be included in the question (along with the two incorrect answers - see below). This will greatly simplify matters. Another smaller change that I would recommend is having a Question_ID field as the PRIMARY KEY in the Question table.

I've prepared a schema below - it's MySQL I'm afraid, I don't use Microsoft SQL Server very often and don't have an instance to hand. It shouldn't be too hard to "translate".

A few comments.

Instead of using just "ID" as a field name in every table, I recommend that you use "Table_Name_ID" - it makes your SQL clearer and also helps with debugging - if you receive a message saying something like "... error with ID on insert..." - you don't know to which table's ID is the message referring, whereas if you name them explicitly, the error message becomes meaningful.

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);

If you have the questions formulated such that they are marked 'A', 'B' or 'C', you will need to modify TINYINT to CHAR(1).

You'll notice that I use relatively long and (hopefully) easy to understand names for my FOREIGN KEYs &c. There are two reasons for this:

• Programmers don't have to go digging through documentation to find out what an entity is - it is self-explanatory,

• Absolutely essential for debugging - which is where the vast majority of applications spend most of their time. Getting a message that "... constraint SYS00003433 has been violated..." isn't exactly helpful. Oracle is great for giving unnamed constraints its own "special" system generated names.

You'll also notice that I use singular table names. I recommend that you do the same - that way you never have to think about "Hmmm... is it student or students?" Either way, pick a standard and stick to it.

If you Google "database design best practices", you'll get any number of sites - take the time and trouble to read a few of them and see what the consensus is - if lots of people think it's a good idea, it probably is!

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);

This unique constraint is to eliminate the possibility of two answers to the same question.

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

Even though you specifically rule it out, it should be relatively easy to add an exam (same exam, different times) table or possibly an "attempt" table for that and then have an exam table for different exams - i.e. databases101, databases201.

Maybe you should consider a date or even a datetime field associated with attempt - take the same exam in the morning and afternoon?

At the end of the day, one could design till the cows come home. Avoid compulsive designing disorder - by that I mean choose (in advance) a point at which you'll be satisfied with the functionality of your system and stop there. I know how easy it is to fall into the trap of continually adding "bits and bobs" and getting no real work done.

Answer 3

"Designing database" exam task solution most probably should show how a student can express domain business rules by means of DB data integrity tools (PK,FK).

Your solution covers most of them. Missing rules are:
(1) Only one Answer is solution to a Question. Bit flag doesn't help without triggers or extra app code.
(2) A Student can choose only one Answer to a Question.

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)

I added SolutionID, this way Answer has exactly one solution.

Table Answers:

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

To meet (2) StudentChoices' PK must be (StudentID,QuestionID) which means Student answered the Question and his Answer was ... .

Table StudentChoices:

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

Now a problem arises, AnswerID and QuestionID must not contradict each other, i.e. AnswerID must belong to Question referenced by QuestionID. We can achieve it by declaring FK at StudentChoices:

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

To be able to target Answers(QuestionsID, ID) in the above FK, this pair of attributes must be declared Unique at Answers:

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

It's absolutly save to declare such Unique because every superset of PK is unique. Using the same unique we also ensure that SolutionID belongs to the Answer to exactly this Question by replacing FK at Questions with

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

Answer 4

As I review the structure you have now I would change the identity columns from BigInt to Int. I suggest you review how many Students, Questions, Answers and Choices you expect and select data types with appropriate sized ranges.

I would also replace nvarchar(max) with a more reasonable sized field. As there are limitations with functions on nvarchar(max). I'd only use it as a last resort.

As for the table structure itself I would makes changes to the Student choices table.

1. I would add an Identity column as a primary key to uniquely identify a single row. This makes life easier for applications.

2. I would add a Question Id column. So that you can see Student A, for Question B selected Answer C.

3. Optional: If you added a unique index on Student ID & Question id, Students could only select 1 answer per question.

Other things to think about: Will this database be used for many different exams? (consider exam id) Or could a student sit the same exam twice? How would you handle that?

Answer 5

Rather than address the specific data model aspect of the question (there are already five answers that do a good job of that), I will address the stated goal of this question (after all, context is important).

This is my personal design for learning purposes. I am trying to learn Entity framework + C# on my own.

Now, I do understand that there could very well be other factors and details that have not been shared that make pursuing this specific data model the most appropriate course of action. However, I only know what has been shared (the statement above), so with that in mind:

IF you are doing this model just to learn Entity Framework (EF), C#, and possibly MVC / ASP.NET / etc, then you might be better served by considering one of the following approaches:

1. Given that you have already asked several data modeling questions, using the actual data model that those questions pertain to might make it easier to focus on learning the .NET stuff. The data model in this particular question is a contrived situation and so it hasn't been tested with real data and real users; it lacks certain complexities that rise to the surface as a system starts getting used. So you could spend quite a bit of time re-thinking various aspects of this model after its initial conception when you should instead be focusing on how to use Entity Framework to interact with the real-world business rules that you are hopefully already familiar / comfortable with.

2. If you really do need / want a system to manage students taking an exam, there is no need to reinvent the wheel when many such systems already exist and can be studied. There is an entire class of software called Learning Management Systems (LMSs) and at least one of them is Open Source and is pretty popular, so they have already gone through the initial growing pains of finding little things that didn't work. And you certainly don't need the full data model as it handles much more than you need to learn EF, so just take the parts that you need to start building a .NET app around it.

   The system I am thinking of is called Moodle and can be found on GitHub at:
   https://github.com/moodle/moodle/blob/master/lib/db/install.xml
   That link goes directly to their data model. It is actually a description of the data model that they then render into one of 6 or so different RDBMS's dialects. But it is easy enough to read that you can easily see the tables, indexes, relationships, etc. You can even install Moodle so that it creates the SQL Server version of that model. Their code is all PHP so you might need to download that separately, but they do have a binary for Windows (be sure to read the "Which version do I choose?" section on the left).

Again, I understand that you might have one or more reasons for needing to stick with the data model that you are asking about in this Question. So, these are just points to consider maybe for the future if not now, or perhaps for others searching on "exam / testing data models" that might be unaware of Open Source options such as Moodle.

BUT, regardless of which specific path you choose, you will definitely need to also learn more about performance tuning, the pros and cons of the various data types, etc since you are using an ORM (i.e. Entity Framework). Be sure to look up "plan cache bloat", and keep in mind that EF does indeed have the option, for each object and action, to call a Stored Procedure instead of dynamically generating the SQL :-).

Answer 6

As others mentioned you should use reasonable datatypes, e.g. no BIGINT for students/question (there will never be more than 2 billion students), probably no NVARCHAR(MAX) for questions/answers. You should also add NOT NULL constraints when possible.

A clean logical data model would result in this:

Table Student

StudentID int (primary key, system assigned?)
-- of course there will be an official registration number 
-- issued by the univerity which is probably not based on a sequence
Name nvarchar(200) not null
-- of course there will be lots of other columns like last_name/first_name/birthdate/...

Table Question

QuestionID smallint (primary key, system assigned?) 
TextOfTheQuestion nvarchar(4000) not null

CorrectAnswer tinyint not null check (CorrectAnswer between 1 and 3)
-- for a 100% clean model you might:
-- drop the check, remove the NOT NULL 
-- and do a foreign key to Answers(QuestionID, AnswerID) instead
-- But then you got a circular reference and this is hard to load

Table Answer

QuestionID int (foreign key to Questions(QuestionID))
AnswerID tinyint check (AnswerID between 1 and 3)
TextOfTheAnswer not null nvarchar(4000)
Primary key (QuestionID, AnswerID) 

Table StudentChoice

StudentID int (foreign key to Students.StudentID)
QuestionID smallint (foreign key to Answers.AnswerID)
AnswerID tinyint -- NULLable as student might not have answered question (yet)?
Primary key (StudentID, QuestionID)

Then a query to get the (correct) answered questions will look like this:

select s.Name, 
   count(sc.AnswerID) as AnsweredQuestions
   count(q.AnswerID) as CorrectAnswers
from student as s
left join StudentChoice as sc
       on s.StudentID = sc.StudentID 
left join Question as q
       on q.QuestionID = sc.QuestionID 
      and q.CorrectAns = sc.AnswerId
group by Student.Name 

Of course this might get more and more complicated, e.g. regarding the actual questions (in ascending order of complexity):

• Are there exactly 10 questions?
• Or are there multiple sets of 10 questions?
• Or are 10 questions randomly chosen from a larger set of questions?