I am in the early stages of writing a program in VB 2005 that deals with taking a test. I need to create an ERD for the project that deals with taking tests and storing the results. All users, admins, instructors, tests, questions, answers, and results will be stored in a database (either SQL Server or MS Access). The "business rules" for this venture are as follows:
- Tests will be multiple-choice, true/false format (a mixture of both).
- The user will enter their ID and password before taking the test (the student ID and password should be authenticated against values stored in a database).
- The test will display a series of at least ten (10) questions.
- There can be from two (True/False) to four (A, B, C & D) answers per question.
- When the test is complete, the answers should be stored in a table with the users ID and the time and date they took the test.
- The questions and answers are stored in a table(s).
- At least three tests will be available that the student may take.
- Students may take a test only once.
- Admins and instructors may also go into the database and change questions or maintain the database.
- Results include the answer to each question for each student for each test, student ID and timestamp.
My main confusion is how to set up the tables with all of the answers and incorrect answers (especially having either multiple-choice or true/false). I figure I will have 10 correct answers per test (at a minimum of 3 tests) and 20 - 30 incorrect answers per test. I will then have to figure out how to store the incorrect questions with the correct results (and the overall results) in the database also.
Any opinions on how I can set this up would be really helpful and appreciated!