How to use MySQL to create a table structure suitable for online examination systems?

How to use MySQL to create a table structure suitable for online examination systems?

When designing a database for an online exam system, we need to create some tables to store exam-related information, such as exam questions, answers, scores, etc. In MySQL, we can achieve this by defining the table structure.

First, we need to create a table to store exam questions. It is assumed that each question has a unique question ID, question type, content, options and correct answers, etc. We can use the following code to create the table:

CREATE TABLE IF NOT EXISTS `exam_questions` (
  `question_id` INT(11) NOT NULL AUTO_INCREMENT,
  `question_type` VARCHAR(255) NOT NULL,
  `question_text` TEXT NOT NULL,
  `option_a` VARCHAR(255) NOT NULL,
  `option_b` VARCHAR(255) NOT NULL,
  `option_c` VARCHAR(255) NOT NULL,
  `option_d` VARCHAR(255) NOT NULL,
  `correct_answer` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`question_id`)
) ENGINE=InnoDB;

Next, we need to create a table to store candidates’ answers. Assume that each candidate has a unique ID, which corresponds to the ID of the exam question, and the answer is represented by a string. We can use the following code to create the table:

CREATE TABLE IF NOT EXISTS `exam_answers` (
  `student_id` INT(11) NOT NULL,
  `question_id` INT(11) NOT NULL,
  `answer` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`student_id`, `question_id`),
  FOREIGN KEY (`question_id`) REFERENCES `exam_questions` (`question_id`)
) ENGINE=InnoDB;

Next, we need to create a table for storing examinee scores. Assume that each candidate has a unique ID, and the test score is represented by a floating point number. We can use the following code to create the table:

CREATE TABLE IF NOT EXISTS `exam_scores` (
  `student_id` INT(11) NOT NULL,
  `score` FLOAT NOT NULL,
  PRIMARY KEY (`student_id`),
  FOREIGN KEY (`student_id`) REFERENCES `exam_answers` (`student_id`)
) ENGINE=InnoDB;

Finally, we need to create a table for storing exam records. Assume that each candidate has a unique ID, and the exam start and end time and duration are represented by timestamps respectively. We can use the following code to create the table:

CREATE TABLE IF NOT EXISTS `exam_records` (
  `student_id` INT(11) NOT NULL,
  `start_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `end_time` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  `duration` INT(11) NOT NULL,
  PRIMARY KEY (`student_id`),
  FOREIGN KEY (`student_id`) REFERENCES `exam_scores` (`student_id`)
) ENGINE=InnoDB;

With the above code, we have successfully created a table structure suitable for the online examination system. Of course, in actual development, we may need to adjust and expand the table structure according to specific needs. I hope this article will help you understand how to use MySQL to create a table structure suitable for online examination systems.

The above is the detailed content of How to use MySQL to create a table structure suitable for online examination systems?. For more information, please follow other related articles on the PHP Chinese website!