Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
creating question database

Answered creating question database

  • Tuesday, July 03, 2012 10:40 AM
     
     
    Sign In to Vote
    0
    i hav created a asp.net web application of online test.I want to store many questions along with 4 options into database...how to store questions??do i hav any other way instead of writting a insert statement??how to retrive these questions randomly?

    saswat

     

All Replies

  • Tuesday, July 03, 2012 10:50 AM
    Answerer
     
     
    Sign In to Vote
    0
    Table for questions, Table for Answers..Table for Person that takes question and put answers... It depends on your business requirements.

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/


     
  • Monday, July 09, 2012 4:57 AM
    Moderator
     
     
    Sign In to Vote
    0

    What do you mean: "instead of writting a insert statement??"  This is the db design forum, so real idea how asp.net works...

    As for the questions, you need to do the first step and that is to define your requirements? Do you just mean you want to have the definition of the questions in the database? Or the answers?  Just questions would probably work best in XML, but I am guessing you want to save answers?

    Randomly as in order of a list?  ORDER BY newid() is a typical way to do it.  If you want to retrieve them randomly one at a time, you would have to maintain the state of what you have shown them and either do SELECT TOP 1 .... ORDER BY newid(), or do the random number from the UI. 

    Database design is generally pretty easy one you know the problem you are trying to solve, and if you want to post your requirements in a paragraph or two, we can attempt to help you with a sketch of a design...

    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

     
  • Tuesday, July 10, 2012 5:21 PM
     
     
    Sign In to Vote
    0

    i want to store a lot of questions and retrive some of them randomly...the total no of questions retrived randomly is the total no of questions that user wants to take test on...how to store these questions in db!

    saswat

     
  • Wednesday, July 11, 2012 3:18 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Well, the absolutely simplest version would be something like this:

    Question (QuestionId (PK), QuestionText (AK), CorrectQuestionAnswerId (FK to QuestionAnswer))

    QuestionAnswer(QuestionAnswerId (PK), QuestionId (FK), AnswerLetter, AnswerText, AK(QuestionId, AnswerLetter)

    then

    select *
    from   question
                 join QuestionAnswer
                          on Question.QuestionId = QuetionAnswer.QuestionId
    order  by newid()

    Of course, this doesn't automate anyting other than just returning a number of question.

    add:

    --The person who is registered to take the test:
    TestTaker (TestTakerId (PK), TestTakerNumber (AK), NameEtc) --TestTakerNumber would be some id that the user would have

    --an attempt at taking the test
    TestTakerAttempt (TestTakerAttemptId (PK), TestTakerId, AttemptNumber, QuestionCount, AK(TestTakerId, AttemptNumber))

    --answers given to the test
    TestTakerAttemptAnswer (TestTakerAttemptId, QuestionId, GivenQuestionAnswerId (FK), PK(TestTakerAttemptId, QuestionId))

    Grading the test is then a join to the answers to see if they match..

    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.