Exam Management System Database Design

Exam Management System Database Design

Introduction

Information technology plays a significant role in the teaching and learning process of teachers and students, respectively. IT offers a more efficient and convenient way for teachers and students to learn and assess learning’s. The capstone project, “Exam Management System,” is designed to allow electronic management of all the information about the exam questions, courses and subjects, and teachers and students. The said project is an all-in-one platform for student exam management.

The researchers aimed to develop an online platform for exam management. The proposed project will allow the administrator to facilitate the overall exam management from teachers, students, and examination details. The system will be designed to enable teachers to manage their exam details electronically and the conduct of the exam. They can also electronically process the result of the exam. For students, they can electronically view the list of their exams and take it online. They will also have easy access to their exam result. The proposed project will digitally transform the exam management process, which is more manageable, convenient, and efficient.

It is a database used by educational institutions to manage and track their exams, which is known as the Exam Management System (EMS). In the database, information on exams is stored, including information about the exam’s name, when it was given, who took it, and how well he or she performed on it. In addition to tracking students’ progress throughout their academic careers, the database can be utilized to ensure that tests are administered at the appropriate time and in the most efficient manner. It is also used to track student grades, which can be used to determine whether or not a student has completed the prerequisites for a degree program based on the information included inside it. Exam Management Systems are a vital tool for educational institutions, and they should be well-maintained and conserved in order to be used by future generations of students.

Database Tables

Let’s start by making the tables and columns. For a complete tutorial, please watch the video.

tbl_course – this table will hold the details of the courses in the system.

• course_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• course_name – the name of the course
• course_description – course description or additional information about the course

Create SQL Statement – the statement below is used to create the tbl_course, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_course` (
`course_id` int(11) NOT NULL,
`course_name` varchar(30) NOT NULL,
`course_description` varchar(300) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_subject – the subject’s information will be held in this table.

• subject_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• subject_code – unique code given for a specific subject
• subject_name – the name of the subject
• subject_description – subject description or additional information about the subject

Create SQL Statement – the statement below is used to create the tbl_subject, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_subject` (
`subject_id` int(11) NOT NULL,
`subject_code` varchar(15) NOT NULL,
`subject_name` varchar(30) NOT NULL,
`subject_description` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_teacher – this table will store the information of the teachers in the system.

• teacher_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• teacher_id_number – this is unique id number of the teachers and is system generated
• first_name – the first name of the teacher
• middle_name- the middle name of the teacher
• last_name – the last name of the teacher
• course_id – this is a foreign key that points out to the course under the teacher
• email_address – the email address of the teacher
• profile_picture- this will hold the profile picture of the teacher in the system
• username – the desired username of the teacher for his/her account
• password – the desired password of the teacher, combined with the username to login
• account_status – (0)active,(1)inactive
• user_id – this is a foreign key that points out to the user

Create SQL Statement – the statement below is used to create the tbl_teacher, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_teacher` (
`teacher_id` int(11) NOT NULL,
`teacher_id_number` varchar(15) NOT NULL,
`first_name` varchar(30) NOT NULL,
`middle_name` varchar(30) NOT NULL,
`last_name` varchar(30) NOT NULL,
`course_id` int(11) NOT NULL,
`email_address` varchar(50) NOT NULL,
`profile_picture` longblob NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`account_status` int(1) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_student – this table will store the information of the students in the system.

• student_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• student_id_number – unique student id number generated by the system
• first_name – the first name of the student
• middle_name – the middle name of the student
• last_name – the last name of the student
• course_id – this is a foreign key that points out to the course of the student
• email_address – the email address of the student
• profile_picture – this will hold the profile picture of the student
• username – the desired username of the student
• password – the desired password of the student combined with the username to log in
• account_status – (0)active,(1)inactive
• user_id -this is a foreign key that points out to the user.

Create SQL Statement – the statement below is used to create the tbl_student, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_student` (
`student_id` int(11) NOT NULL,
`student_id_number` varchar(15) NOT NULL,
`first_name` varchar(30) NOT NULL,
`middle_name` varchar(30) NOT NULL,
`last_name` varchar(30) NOT NULL,
`course_id` int(11) NOT NULL,
`email_address` varchar(50) NOT NULL,
`profile_picture` longblob NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`account_status` int(1) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_teacher_subject – the details of the subjects under a specific teacher is stored in this table.

• teacher_subject_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• course_id- this is a foreign key that points out to the course under the teacher
• subject_id – this is a foreign key that points out to the subjects under the teacher
• teacher_id – this is a foreign key that points out to the teacher

Create SQL Statement – the statement below is used to create the tbl_teacher_subject, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_teacher_subject` (
`teacher_subject_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`subject_id` int(11) NOT NULL,
`teacher_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_exam – this table will store the details of the exams in the system.

• exam_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• teacher_id – this is a foreign key that points out to the teacher
• exam_code – a unique code given for an exam
• exam_name – the name of the exam
• Instruction – general instruction for the exam
• date – the date the exam will be given
• start_time – the time when the exam will start
• end_time – the time when the exam will end
• timer_in_minutes – timer for the exam to be taken

Create SQL Statement – the statement below is used to create the tbl_exam, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_exam` (
`exam_id` int(11) NOT NULL,
`teacher_id` int(11) NOT NULL,
`exam_code` varchar(15) NOT NULL,
`exam_name` varchar(15) NOT NULL,
`instruction` text NOT NULL,
`date` date NOT NULL,
`time` time NOT NULL,
`start_time` time NOT NULL,
`end_time` time NOT NULL,
`timer_in_minutes` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_question_bank – this table stores the information of the questions given for the exam .

• question_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• question – the details of the question
• choice_1 – the first choice for an answer
• choice_2 – the second choice for an answer
• choice_3 – the third choice for an answer
• choice_4 – the fourth choice for an answer
• correct_answer – the correct answer for the question
• points – the points for the correct answer

Create SQL Statement – the statement below is used to create the tbl_question_bank, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_question_bank` (
`question_id` int(11) NOT NULL,
`question` varchar(100) NOT NULL,
`choice_1` varchar(30) NOT NULL,
`choice_2` varchar(30) NOT NULL,
`choice_3` varchar(30) NOT NULL,
`choice_4` varchar(30) NOT NULL,
`correct_answer` varchar(30) NOT NULL,
`points` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_assign_question_to_exam – this table will store the information of the questions assigned for an exam.

• assign_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• exam_id – this is a foreign key that points out to the exam
• question_id – this is a foreign key that points out to the question

Create SQL Statement – the statement below is used to create the tbl_assign_question_to_exam, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_assign_question_to_exam` (
`assign_id` int(11) NOT NULL,
`exam_id` int(11) NOT NULL,
`question_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_assign_exam_to_student – this table will store the information of the exams assigned to students.

• student_exam_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• exam_id – this is a foreign key that points out to the exam assigned for the student
• student_id – this is a foreign key that points out to the student.

Create SQL Statement – the statement below is used to create the tbl_assign_exam_to_student, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_assign_exam_to_student` (
`student_exam_id` int(11) NOT NULL,
`exam_id` int(11) NOT NULL,
`student_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_exam_result – this table will hold the exam result information of the students.

• result_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• student_id – this is a foreign key that points out to the student
• exam_id – this is a foreign key that points out to the exam
• score – the accumulated score for the exam
• status – (0)passed, (1)failed

Create SQL Statement – the statement below is used to create the tbl_exam_result, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_exam_result` (
`result_id` int(11) NOT NULL,
`student_id` int(11) NOT NULL,
`exam_id` int(11) NOT NULL,
`score` int(3) NOT NULL,
`status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_score_card – the score card information will be stored in this table.

• question_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• your_answer – the answers of the students
• correct_answer- the correct answers among the given answers by the student
• points – the points gained by the student

Create SQL Statement – the statement below is used to create the tbl_score_card, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_score_card` (
`question_id` int(11) NOT NULL,
`your_answer` varchar(30) NOT NULL,
`correct_answer` varchar(30) NOT NULL,
`points` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_user – this table will store the information of the system’s users.

• user_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• complete_name – the complete name of the user.
• avatar – this will hold the profile photo of the user
• username – the preferred username of the user
• password- the preferred password of the user
• contact – the contact details of the user preferably mobile number
• email – the email address of the user.
• status – (0)active, (1)inactive

Create SQL Statement – the statement below is used to create the tbl_user, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_user` (
`user_id` int(11) NOT NULL,
`complete_name` varchar(100) NOT NULL,
`avatar` longblob NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`contact` varchar(15) NOT NULL,
`email` varchar(50) NOT NULL,
`status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Video Tutorial

Summary

In order to ensure that the database is designed in a manner that fulfils the needs of the company, database planning is essential. Database planning also assists in ensuring that the database is correctly optimized for performance by identifying bottlenecks. Additionally, database design can aid in the identification of possible security issues and the mitigation of such risks before they become a serious problem. Finally, database planning can assist in ensuring that a database is appropriately managed and updated, as previously stated. An business may discover that its database is becoming out dated and difficult to use if it does not conduct proper database planning. Briefly stated, database planning is critical for guaranteeing the success of an organization’s data management initiatives.

Please watch the video tutorial on how to prepare and create the different tables of the database of Exam Management System.

You may visit our Facebook page for more information, inquiries, and comments. Please subscribe also to our YouTube Channel to receive  free capstone projects resources and computer programming tutorials.

Hire our team to do the project.

Related Topics and Articles:

Exam Management System Free Bootstrap Source code

Online Exam and Learning Management System

Online Entrance Exam with Course Recommendation System

Mobile Based Exam Schedule Notification App Capstone Project

Exam Paper Repository Management System