ER diagram for online examination system
This ER diagram models an online examination system where students take timed multiple-choice exams and their answers are stored and scored. It suits students working on DBMS assignments and developers planning the database for a quiz or test platform.
Example: ER diagram for online examination system: click to open it in the editor and change it.
Entities and key attributes
The diagram has six tables. Each has an integer id primary key, and foreign keys link the tables together.
| Entity | Primary key | Other attributes |
|---|---|---|
| students | id | name, email |
| exams | id | title, duration, starts_at |
| questions | id | exam_id (FK), text, marks |
| options | id | question_id (FK), text, is_correct |
| attempts | id | exam_id (FK), student_id (FK), score, submitted_at |
| answers | id | attempt_id (FK), question_id (FK), option_id (FK) |
Relationships and cardinality
- exams 1 : N questions: an exam contains many questions, and each question belongs to one exam.
- questions 1 : N options: each question has several choices, and is_correct marks the right one.
- exams 1 : N attempts: many attempts can be made on one exam.
- students 1 : N attempts: a student can attempt many exams, or retake the same one.
- attempts 1 : N answers: one attempt stores one answer row per question answered.
- questions 1 : N answers and options 1 : N answers: each answer points to the question and the option the student chose.
attempts acts as the bridge between students and exams. That resolves their many-to-many relationship and also holds the score and submitted_at time.
How to adapt it for your project
- Add a teachers or admins table and link it to exams to record who created each exam.
- For written answers, add a text column to answers and allow option_id to be empty.
- Add started_at to attempts so you can check time against duration.
- Add a subjects or courses table to group exams.
Tip: Score an attempt by joining answers to options where is_correct is true, then adding up the marks of the matching questions. You can store the result in attempts.score so you don't have to recompute it.
Frequently asked questions
What are the entities in an ER diagram for an online examination system?
This diagram has six entities: students, exams, questions, options, attempts and answers. Each one uses an integer id as its primary key.
How is the many-to-many relationship between students and exams handled?
It goes through the attempts table, which holds both student_id and exam_id as foreign keys. Each row is one sitting of an exam by one student, together with its score and submission time.
How does the system know which answer is correct?
Each row in options has a boolean is_correct field. The answers table stores the option_id the student picked, so you compare it against the option marked correct.
Make your own ER diagram with AI →