mysql - Improve database design for online exam -


i working on multiple choice online test project here have designed database store result looking more optimized way.

requirements:

  1. every question have 4 options.
  2. only 1 option can selected , needs stored in database.

my design:

tables:

students
stud_id, name, email

tests
test_id, testname, duration

questions
que_id, question, opt1, opt2, opt3, opt4, answer, test_id

answers
stud_id, que_id, answer

by way answers can stored increase number of records every question solved student new record added in answers table.

e.g. 1 test consists 100 questions , 1000 students take test, every student there 100 records each question , 1000 students 100k records.

is there better way number of records less.

initial response

understanding data

you have done work. far data concerned, design correct, incomplete. there 2 errors:

  1. opt1…opt4 repeating group, breaks 2nf. must placed in separate table.

    • further, there seems no option name or descriptor, strange (what paint on page, next each radio button?)

    • if ever add fifth option, catered for; if have questions less 4 options, catered for.

    • conversely, have fixed set of columns, , if there such changes in future, have change both database , existing code. , code horrendous (extra processing instead of direct selects)

  2. your answers table has no integrity. stands, answers can recorded against question student not asked, or test student did not sit. prevention of type of error ordinary fare in relational database, , not possible in record filing system.

    • in these dark days of it, common trend. people focus on data values; imagine values in spreadsheet form, , go directly implementing object contain values. instead of understanding data , means.

    • answers(stud_id, que_id, answer) has no meaning, no integrity, unless context of student_test asserted.

  3. the third item not error, because did not give requirement. however, seems me question can used in more 1 test. way have set up, such questions duplicated (the whole point of database normalise it, such there no duplication).

    • of course, consequence associative table, test_question.

questions

by way answers can stored increase number of records every question solved student new record added in answers table.

yes. normal database.

is there better way number of records less.

for record filing system, yes. database, no. since have tagged question database-design, assume that want.

a database collection of facts, not of records related fields. facts real world, limited scope of database , app.

it important determine discrete facts need, because subordinate facts depend on higher-order facts. database design. , normalise data, progress, part of 1 , same exercise. normalisation has purpose of eliminating duplication, otherwise have update anomalies. , determine relational keys, progress, again part of 1 , same exercise. relational keys provide logical structure of relational database, ie. logical integrity.

e.g. 1 test consists 100 questions , 1000 students take test, every student there 100 records each question , 1000 students 100k records.

yes. expressed in isam record-processing terms. in database terms, cannot around fact database stores:

  • facts 100 questions

  • facts 1,000 students

  • facts 1,000 students times 100 choices made

you need head around 2 things: large number of discrete facts; , use of compound keys. both essential relational databases. if either of missing, or implement them reluctance, not have integrity, power, or speed of relational database, have pre-1970's isam record filing system.

further, sql platforms, , degree nonsql platforms such mynonsql, heavily optimised processing sets of data (not record-by-record); heavy i/o , caching; etc. if implement structures required high concurrency, obtain more performance.

implementation

as far implementation concerned, , particularly since concerned performance, there errors. restatement be, implementation should not attempted until data understood , modelled correctly.

the problem across board, have added surrogate (there no such thing "surrogate key", surrogate, physical record id). far in modelling exercise; hasn't progressed enough; model not stable, add surrogates.

  • surrogates additional column plus underlying index. consumes resources, , has cost on inserts , deletes.

  • surrogates do not provide row uniqueness, demanded in relational database.

  • the relational model demands keys made data. relational keys provide row uniqueness.

  • a surrogate isn't made data. therefore not relational key, , not provide of qualities of one.

  • if surrogate used, not replace key, in addition key. why evaluate need surrogates after, not before, modelling data. implementation concern, not modelling one.

solution

rather going , forth, let me provide proposal, , can discuss it.

  • student test data model (page 1 only, following progression).

  • if not used notation, please advised every little tick, notch, , mark, solid vs dashed lines, square vs round corners, means specific. refer idef1x notation.

  • for test , question. have left id columns in, note better off short, meaningful codes.

  • student_id valid because both name , email large migrate child tables.

  • please check verb phrases carefully, comprise set of predicates. remainder of predicates can determined directly model. if not clear, please ask.

  • see if can determine collection of facts, , each fact discrete precisely because other facts depend on it; not collection of records fields related.

your answers table has no integrity. stands, answers can recorded against question student not asked, or test student did not sit. prevention of type of error ordinary fare in relational database, , not possible in record filing system.

  • that prevented. answers table, named student_response, has integrity. student registered test in student_test, , student_responses constrained student_test.

please comment/discuss.

response comments

i add additional table subject (subject_id, subject_name) , add subject_id in question table fk okay?

yes, means. has consequences. advice make sure properly, across board:

  1. as explained, not use surrogates (record ids) unless absolutely have to. short codes better identifiers, both users , developers.

    • if more info on problems related id columns, read this answer.

  2. subject important. context in (a) question exists, , (b) test exists. did exist independent items (page 1 of dm), subordinate subject. addition substantially improves data integrity.

  3. the fact of student registration , fact of student sitting test, discrete , separate facts.

  4. gratefully, eliminated 2 surrogates question_id , test_id. short codes such char(2) easier , more meaningful.

  5. note improvement in table names, improved clarity.

  6. i have updated student test data model (page 2 only, following progression).

  7. however, exposes (that why model data, paper cheap, many drafts normal). if evaluate predicates (readily visible in data model, detailed in idef1x notation document):

       each subject_test taken 0-to-n student_tests    each student_test [a taking of] 1 subject_test    each student took 0-to-n student_tests    each student_test taken 1 student

    those predicates not accurate. student can sit test in subject. given new subject table, think want students registered subjects, , therefore student_test constrained subjects student registered for.

    • if information on important relational concept of predicates, , how used both understand , verify model, visit this answer, scroll down until find predicate section, , read carefully.

  8. i have updated student test data model (page 3). have more integrity, such student_test constrained subjects student registered for. relevant predicates are:

       each student registered 0-to-n student_subjects    each student_subject registration of 1 student    each subject attracted 0-to-n student_subjects    each student_subject attraction of 1 subject     each subject_test taken 0-to-n student_tests    each student_test [a taking of] 1 subject_test    each student_subject took 0-to-n student_tests    each student_test taken 1 student_subjects

  9. now data model appears complete.

    • context in database.

    • the data hierarchies plainly visible in compounding of keys.

    • notice relational keys, in child tables, provide relational integrity parent tables, every higher level (parent, grandparent) in hierarchy.

    • in case not obvious, notice power of relational joins. cannot record filing systems have id fields in every file. eg:

      • join student_response directly subject on subject_code, without having navigate 2 levels in-between

      • join student_response directly student on student_id, without having navigate 2 levels in-between


Comments

Post a Comment

Popular posts from this blog

php - failed to open stream: HTTP request failed! HTTP/1.0 400 Bad Request -

this code i'm using find latitude , longitude of place: function coords($address){ echo $url="http://maps.googleapis.com/maps/api/geocode/json?address=".$address; $json = file_get_contents(urlencode($url)); $data = json_decode($json, true); print_r($data['results'][0]['geometry']['location']['lat']); print_r($data['results'][0]['geometry']['location']['lng']); } however returns warning: failed open stream: http request failed! http/1.0 400 bad request if use above code in simple procedure works fine not in function. note: have tried curl_init()... method produced same result? instead of encoding whole url encode address part. following code work fine $add='jamshoro phase 2'; $url="http://maps.googleapis.com/maps/api/geocode/json?address=".urlencode($add); $json = file_get_contents($url); $data = json_decode($json, true); print_r($data['r...
Read more

command line - Use qwinsta in PowerShell ISE -

i trying use qwinsta , rwinsta powershell code. when try run error "the term 'qwinsta' not recognized name of cmdlet, function, script file, or operable program." need able remotely on several machines without having add .dll's or modules other machines. tried terminal services powershell module, need able put on remote machines , not able that. within powershell ise how run qwinsta? not looking parse information gather it. following code have tried (which had found on site also): function get-tssessions { param( $computername = "localhost" ) qwinsta /server:$computername | #parse output foreach-object { $_.trim() -replace "\s+","," } | #convert objects convertfrom-csv } get-tssessions -computername "localhost" | ft -autosize best have running powershell ise (x86). when run command qwinsta there same error you. of course assuming have 64-bit os. th...
Read more

java - Incorrect order of records in M-M relationship in hibernate -

Image
i have 4 records in bridge table in mysql database. using @manytomany in hibernate, accurate order of employees incorrect incorrect of projects. following required material figure out doing mistake. this source. @entity @table(name="project") public class projectbean { @id @generatedvalue @column(name="project_id") private int projectid; @column(name="title") private string projecttitle; @manytomany(mappedby="projects") private collection<employeebean> employees; public projectbean() { employees = new arraylist<employeebean>(); } //getters & setters } @entity @table(name="employee") public class employeebean { @id @generatedvalue @column(name="employee_id") private int employee_id; @column(name="first_name") private string ...
Read more