OnlineExamsDatabase

Question

OnlineExamsDatabase

GuptaGoli

SSC Rookie

Points: 48
More actions
June 25, 2008 at 8:00 pm

#96049

Could you please send me idea about online examination database design

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

The first thing to do is define what you want it to do.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

This isn't a place for people to do work for you. Show some effort, try to figure out what you need and then ask questions. We'll try to help, but we're not here to do your job for you.

GuptaGoli SSC Rookie Points: 48 More actions · Answer 3

Hi,

I have Degined DB Model but i am thinking how can handle random questions generation and respected answers from thier correct answers.

I've Created table like tblQuestions it hold 250 questions and then trying to generate random questions and answers accordingly.

Could you please someone help me to share idea about holding this information correctly .

Thank you

Gupta

GSquared SSC Guru Points: 260824 More actions · Answer 4

I bet you created a table with columns like "Question1", "Answer1", "Question2", "Answer2". Something like:

create table Exams (

ExamID int identity primary key,

Question1 varchar(100),

Answer1 int,

Question2 varchar(100),

Answer2 int,

... and so on through ...

Question250 varchar(100),

Answer250 int)

If so, you'd be better off with something like:

create table Questions (

QuestionID int identity,

Question varchar(100) primary key nonclustered,

Answer int)

go

create unique clustered index CID_Questions on dbo.Questions(QuestionID)

go

create table Exams (

ExamID int identity primary key,

ExamName varchar(100))

go

create table Exams_Questions (

ExamID int not null references dbo.Exams (ExamID),

QuestionID int not null references dbo.Questions(QuestionID))

go

create trigger Exams_Delete on dbo.Exams

after delete

as

delete Exams_Questions

from dbo.Exams_Questions

inner join deleted

on Exams_Questions.ExamID = deleted.ExamID

go

create trigger Questions_Delete on dbo.Questions

after delete

as

delete Questions

from dbo.Questions

inner join deleted

on Questions.QuestionID = deleted.QuestionID

go

With a structure like that, you can assign as many or few questions to an exam as you like. You can even re-use good questions without having to type them in twice. Plus, checking answers given by exam-takers becomes an easy matter of a simple join to the Questions table, compared to a count from the same table.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 5

If you add a NEWID() column to your select and then order by it, you'll get some pseudo random results.

GuptaGoli SSC Rookie Points: 48 More actions · Answer 6

Hi GSquared,

Thank you for idea. it is very usefull for me.

Gupta

GuptaGoli SSC Rookie Points: 48 More actions · Answer 7

Hi All,

I have question for Generate Random Questions for User Based on Examtype,only 20 questions for Examination. I have Table like

DS_V_UserDetails

UserID

Fullname

Firstname

Lastname

DS_V_ExamType

ExamTypeCode

ExamType

Date

DS_V_Questions

QuestionID

Examtypecode (FK to Examtype)

Question

DS_V_Answer

AnsID(PK)

ChoiceID

QuestionID(FK to Question table)

Answer

CorrectAnswer

DS_V_Choice

ChoiceID(PK)

QuestionID (FK Question table)

Choice

DS_V_Exams

ExamID (PK)

QuestionID (FK to Exam_Questions)

ExamTypecode

Question

ChoiceID

UseriD

DS_V_Exams_Questions

ExamID

QuestionID (FK to Questions Table)

DS_V_MappingUserExams

MapID (PK)

QuestionID(FK to Questions)

UserID(FK to Userdetails)

DS_V_Results

ResultID(PK)

ExamID (FK TO Exams)

QuestionID

UserID

I need to be create 20 random questions from questions table then stored into exams table. what is the best way to do this one. Conditions is it is depends on User selected type of exam we have four exam types example car, boat, airplane types.

Thank you

G

GSquared SSC Guru Points: 260824 More actions · Answer 8

You can use "Select Top 20 ... Order By NewID()" (with the columns and tables in there where I have the elipses). That will give you a reasonably random set of questions.

If you include a Where clause that limits it to the type of test you want, it should do exactly what you need.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

GuptaGoli (6/25/2008)
Could you please send me idea about online examination database design

Have a word with Vasaharshit, he's the expert on this question:

http://www.sqlservercentral.com/Forums/Topic532276-373-1.aspx

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden