4

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.

6
+50

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 
  • 'Answers' table - Unable to create relationship 'FK_Answers_Questions'. Introducing FOREIGN KEY constraint 'FK_Answers_Questions' on table 'Answers' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. This type of error (cycles or multiple cascade paths...) pops out every time I try to make foreign key. I am using designer (Database diagrams option in the Treeview on the left) to make them. I have marked all foreign keys with CASCADE for delete + update. – AlwaysLearningNewStuff Jun 30 '16 at 17:59
  • Then I highly suspect you have the FK in Question pointing to Answer and not AnswerNum. This is a case of you cannot really have DRI as there are some possible circular dependencies but I still think this is a proper design. Is it the best possible design - maybe? With this case I don't think you should cascade. – paparazzo Jun 30 '16 at 20:10
  • If you look at the answer from serg then you can have cleaner DRI relations but you introduce a loser relationship of question to answer and put a unique on top of an already PK. I don't disagree with the answer Serg but this is is not straight forward problem. The answer from Serg does not restrict the number of questions to 3. This is how I would approach it. – paparazzo Jun 30 '16 at 20:37
  • I have really tried to make your solution work but failed. I had to remove CorrectAns from Questions table and create new table CorrectAnswers, with QuestionID as primary key and AnswerNum where QuestionID + AnswerNum create composite FK that references (Answers.QuestionID, Answers.AnswerNum). This way only one answer can be a solution, and the error earlier mentioned is bypassed. I like your solution better, but on SQL Server 2012 it just wont work... After bounty period expires I will accept this solution and award the bounty (unless something better comes up, which I doubt). – AlwaysLearningNewStuff Jul 2 '16 at 11:32
  • Don't know what to say. Tested this myself and it worked for me on SQL Server 2012. It took me like 10 minutes to build the 5 tables and relationships. – paparazzo Jul 2 '16 at 12:15
5

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.

2

"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)
1

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?

  • Will this database be used for many different exams? (consider exam id) Or could a student sit the same exam twice? There is only one exam, and each student takes it only once. I apologize for the confusion. Thanks for answering. – AlwaysLearningNewStuff Jun 29 '16 at 10:43
1

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 :-).

  • Yes, I could use numerous answers of yours as a starting point, I agree with that. The data model in the OP was a result of the discussion between me and one of my friends. Long story short, it was an interview question. I found the question interesting and gave it a try. All of a sudden, lots of ideas came to my mind and what seemed to be simple task turned into interesting challenge. I nearly managed to make solution from user Paparazzi work, but am working on improving certain parts. EF is not the point of this question, I would like to find the best possible solution for this task. – AlwaysLearningNewStuff Jul 1 '16 at 23:48
1

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?
  • Or are 10 questions randomly choosen from a larger set of questions Damn, I forgot to mention that... Good catch. I am sorry if it seems that I have neglected your answer, that is not true. I am close to creating decent design, and am preparing to update OP. Thank you for answering. – AlwaysLearningNewStuff Jul 1 '16 at 14:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.