Exam Management System Database Design and Implementation with Java
Exam Management System: Database Design and Java Implementation
This comprehensive guide details the design and implementation of an exam management system, focusing on the database schema and the accompanying Java code for various functionalities.
Database Schema
The database schema consists of several tables:
1. students Table:
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(50),
password VARCHAR(50)
);
2. courses Table:
CREATE TABLE courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(100)
);
3. questions Table:
CREATE TABLE questions (
question_id INT PRIMARY KEY,
course_id INT,
question_text VARCHAR(500),
option_a VARCHAR(100),
option_b VARCHAR(100),
option_c VARCHAR(100),
option_d VARCHAR(100),
correct_answer VARCHAR(1),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
4. exam_results Table:
CREATE TABLE exam_results (
student_id INT,
course_id INT,
score INT,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
5. teachers Table:
CREATE TABLE teachers (
teacher_id INT PRIMARY KEY,
name VARCHAR(50),
password VARCHAR(50)
);
6. teacher_courses Table:
CREATE TABLE teacher_courses (
teacher_id INT,
course_id INT,
PRIMARY KEY (teacher_id, course_id),
FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
Database Population
After creating the tables, we populate them with sample data:
INSERT INTO courses (course_id, course_name) VALUES (1, 'Mathematics');
INSERT INTO courses (course_id, course_name) VALUES (2, 'English');
INSERT INTO courses (course_id, course_name) VALUES (3, 'Science');
INSERT INTO questions (question_id, course_id, question_text, option_a, option_b, option_c, option_d, correct_answer) VALUES (1, 1, 'What is 2+2?', '3', '4', '5', '6', 'B');
INSERT INTO questions (question_id, course_id, question_text, option_a, option_b, option_c, option_d, correct_answer) VALUES (2, 1, 'What is the square root of 16?', '2', '4', '6', '8', 'B');
INSERT INTO questions (question_id, course_id, question_text, option_a, option_b, option_c, option_d, correct_answer) VALUES (3, 2, 'What is the opposite of 'good'?', 'Bad', 'Nice', 'Happy', 'Sad', 'A');
INSERT INTO questions (question_id, course_id, question_text, option_a, option_b, option_c, option_d, correct_answer) VALUES (4, 2, 'What is the past tense of 'eat'?', 'Eating', 'Ate', 'Eaten', 'Eats', 'B');
INSERT INTO questions (question_id, course_id, question_text, option_a, option_b, option_c, option_d, correct_answer) VALUES (5, 3, 'What is the boiling point of water?', '50°C', '75°C', '100°C', '125°C', 'C');
INSERT INTO questions (question_id, course_id, question_text, option_a, option_b, option_c, option_d, correct_answer) VALUES (6, 3, 'What is the largest organ in the human body?', 'Heart', 'Liver', 'Lungs', 'Skin', 'D');
INSERT INTO students (student_id, name, password) VALUES (101, 'John', 'John123');
INSERT INTO students (student_id, name, password) VALUES (102, 'Jane', 'Jane123');
INSERT INTO students (student_id, name, password) VALUES (103, 'Tom', 'Tom123');
INSERT INTO students (student_id, name, password) VALUES (104, 'Mary', 'Mary123');
INSERT INTO students (student_id, name, password) VALUES (105, 'Peter', 'Peter123');
INSERT INTO students (student_id, name, password) VALUES (106, 'Lucy', 'Lucy123');
INSERT INTO exam_results (student_id, course_id, score) VALUES (101, 1, 80);
INSERT INTO exam_results (student_id, course_id, score) VALUES (102, 1, 90);
INSERT INTO exam_results (student_id, course_id, score) VALUES (103, 2, 75);
INSERT INTO exam_results (student_id, course_id, score) VALUES (104, 2, 85);
INSERT INTO exam_results (student_id, course_id, score) VALUES (105, 3, 70);
INSERT INTO exam_results (student_id, course_id, score) VALUES (106, 3, 80);
INSERT INTO teachers (teacher_id, name, password) VALUES (2, 'John Doe', 'password123');
INSERT INTO teacher_courses (teacher_id, course_id) VALUES (1, 1);
Java Implementation
The Java code provides functionalities for student registration, login, taking exams, and teacher-specific operations. Here's a breakdown of the code:
package exam;
import java.util.Scanner;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class ExamManagementSystem {
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
System.out.println("Select Language:");
System.out.println("1. 中文");
System.out.println("2. English");
int langChoice = scanner.nextInt();
switch (langChoice) {
case 1:
System.out.println("欢迎使用考试管理系统！");
System.out.println("请选择您是学生还是老师：");
System.out.println("1. 学生");
System.out.println("2. 老师");
int choice = scanner.nextInt();
switch (choice) {
case 1:
System.out.println("请选择您要进行的操作：");
System.out.println("1. 学生注册");
System.out.println("2. 学生登录");
System.out.println("3. 返回主页面");
int studentChoice = scanner.nextInt();
switch (studentChoice) {
case 1:
studentRegistration();
break;
case 2:
studentLogin();
break;
case 3:
System.out.println("返回主页面");
break;
default:
System.out.println("无效的选项！");
break;
}
break;
case 2:
System.out.println("请输入姓名：");
String teacherName = scanner.nextLine();
System.out.println("请输入密码：");
String teacherPassword = scanner.nextLine();
System.out.println("是否登录？");
System.out.println("1. 是");
System.out.println("2. 不是");
int teacherLoginChoice = scanner.nextInt();
switch (teacherLoginChoice) {
case 1:
teacherLogin();
break;
case 2:
System.out.println("返回主页面");
break;
default:
System.out.println("无效的选项！");
break;
}
break;
default:
System.out.println("无效的选项！");
break;
}
break;
case 2:
System.out.println("Welcome to Exam Management System!");
System.out.println("Please choose if you are a student or a teacher:");
System.out.println("1. Student");
System.out.println("2. Teacher");
int Choice = scanner.nextInt();
switch (Choice) {
case 1:
System.out.println("Please choose your option:");
System.out.println("1. Student Registration");
System.out.println("2. Student Login");
System.out.println("3. Return to Main Menu");
int studentChoice = scanner.nextInt();
switch (studentChoice) {
case 1:
studentRegistration();
break;
case 2:
studentLogin();
break;
case 3:
System.out.println("Returning to Main Menu");
break;
default:
System.out.println("Invalid option!");
break;
}
break;
case 2:
System.out.println("Please enter your name:");
String teacherName = scanner.nextLine();
System.out.println("Please enter your password:");
String teacherPassword = scanner.nextLine();
System.out.println("Do you want to login?");
System.out.println("1. Yes");
System.out.println("2. No");
int teacherLoginChoice = scanner.nextInt();
switch (teacherLoginChoice) {
case 1:
teacherLogin();
break;
case 2:
System.out.println("Returning to Main Menu");
break;
default:
System.out.println("Invalid option!");
break;
}
break;
default:
System.out.println("Invalid option!");
break;
}
break;
default:
System.out.println("Invalid option!");
break;
}
}
// 学生注册
public static void studentRegistration() {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入学号：");
int studentId = scanner.nextInt();
System.out.println("请输入姓名：");
String name = scanner.nextLine();
System.out.println("请输入密码：");
String password = scanner.nextLine();
System.out.println("请再次输入密码：");
String confirmPassword = scanner.nextLine();
if (!password.equals(confirmPassword)) {
System.out.println("两次输入的密码不一致，请重新注册！");
return;
}
// 将学生信息插入到数据库中
try (Connection conn = DatabaseConnector.getConnection()) {
String sql = "INSERT INTO students (student_id, name, password) VALUES (?, ?, ?)";
PreparedStatement statement = conn.prepareStatement(sql);
statement.setInt(1, studentId);
statement.setString(2, name);
statement.setString(3, password);
statement.executeUpdate();
System.out.println("注册成功！");
} catch (SQLException e) {
e.printStackTrace();
System.out.println("注册失败，请重新尝试！");
}
}
// 学生登录
public static void studentLogin() {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入学号：");
int studentId = scanner.nextInt();
System.out.println("请输入密码：");
String password = scanner.next();
// 查询数据库验证学生登录信息
try (Connection conn = DatabaseConnector.getConnection()) {
String sql = "SELECT * FROM students WHERE student_id = ? AND password = ?";
PreparedStatement statement = conn.prepareStatement(sql);
statement.setInt(1, studentId);
statement.setString(2, password);
ResultSet result = statement.executeQuery();
if (result.next()) {
System.out.println("登录成功！");
System.out.println("请选择您要进行的操作：");
System.out.println("1. 开始考试");
System.out.println("2. 查看成绩");
System.out.println("3. 返回主页面");
int choice = scanner.nextInt();
switch (choice) {
case 1:
studentStartExam();
break;
case 2:
// 查看成绩页面逻辑
break;
case 3:
System.out.println("返回主页面");
break;
default:
System.out.println("无效的选项！");
break;
}
} else {
System.out.println("学号或密码错误，请重新登录！");
studentLogin(); // 返回登录页面
} } catch (SQLException e) {
e.printStackTrace();
System.out.println("登录失败，请重新尝试！");
}
}
// 学生开始考试页面
public static void studentStartExam() {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入您的学号：");
int studentId = scanner.nextInt();
System.out.println("请选择您要考试的学科：");
System.out.println("1. Mathematics");
System.out.println("2. English");
System.out.println("3. Science");
int subjectChoice = scanner.nextInt();
String subject = "";
int totalQuestions = 0;
switch (subjectChoice) {
case 1:
subject = "Mathematics";
totalQuestions = 10;
break;
case 2:
subject = "English";
totalQuestions = 10;
break;
case 3:
subject = "Science";
totalQuestions = 10;
break;
default:
System.out.println("无效的选项！");
return;
}
int score = 0;
for (int i = 1; i <= totalQuestions; i++) {
System.out.println("问题 " + i + "：");
// 从数据库中获取题目
try (Connection conn = DatabaseConnector.getConnection()) {
String sql = "SELECT * FROM questions WHERE course_id = ? ORDER BY RAND() LIMIT 1";
PreparedStatement statement = conn.prepareStatement(sql);
statement.setInt(1, subjectChoice);
ResultSet result = statement.executeQuery();
if (result.next()) {
String question = result.getString("question_text");
String optionA = result.getString("option_a");
String optionB = result.getString("option_b");
String optionC = result.getString("option_c");
String optionD = result.getString("option_d");
String answer = result.getString("correct_answer");
System.out.println(question);
System.out.println("A. " + optionA);
System.out.println("B. " + optionB);
System.out.println("C. " + optionC);
System.out.println("D. " + optionD);
System.out.println("请选择您的答案：");
String studentAnswer = scanner.next();
if (studentAnswer.equalsIgnoreCase(answer)) {
score += 10;
System.out.println("回答正确！");
} else {
System.out.println("回答错误！");
}
}
} catch (SQLException e) {
e.printStackTrace();
System.out.println("获取题目失败！");
}
if (i < totalQuestions) {
System.out.println("请选择您要进行的操作：");
System.out.println("1. 下一题");
System.out.println("2. 完成答题");
int answerChoice = scanner.nextInt();
if (answerChoice == 2) {
break;
}
}
}
System.out.println("您的得分是：" + score);
// 将学生的分数保存到数据库中
try (Connection conn = DatabaseConnector.getConnection()) {
String sql = "INSERT INTO exam_results (student_id, course_id, score) VALUES (?, ?, ?)";
PreparedStatement statement = conn.prepareStatement(sql);
statement.setInt(1, studentId);
statement.setInt(2, subjectChoice);
statement.setInt(3, score);
statement.executeUpdate();
System.out.println("分数已保存！");
} catch (SQLException e) {
e.printStackTrace();
System.out.println("保存分数失败！");
}
}
// 教师登录
public static void teacherLogin() {
Scanner scanner = new Scanner(System.in);
System.out.println("请选择您要进行的操作：");
System.out.println("1. 查看问题");
System.out.println("2. 添加问题");
System.out.println("3. 删除问题");
System.out.println("4. 更新问题");
System.out.println("5. 返回主页面");
int choice = scanner.nextInt();
switch (choice) {
case 1:
viewQuestions();
break;
case 2:
// 添加问题页面逻辑
break;
case 3:
// 删除问题页面逻辑
break;
case 4:
// 更新问题页面逻辑
break;
case 5:
System.out.println("返回主页面");
break;
default:
System.out.println("无效的选项！");
break;
}
}
public static void viewQuestions() {
Scanner scanner = new Scanner(System.in);
System.out.println("请选择要查看的课程：");
System.out.println("1. Mathematics");
System.out.println("2. English");
System.out.println("3. Science");
int courseChoice = scanner.nextInt();
int courseId = 0;
switch (courseChoice) {
case 1:
courseId = 1;
break;
case 2:
courseId = 2;
break;
case 3:
courseId = 3;
break;
default:
System.out.println("无效的选项！");
return;
}
try (Connection conn = DatabaseConnector.getConnection()) {
String sql = "SELECT * FROM questions WHERE course_id = ?";
PreparedStatement statement = conn.prepareStatement(sql);
statement.setInt(1, courseId);
ResultSet result = statement.executeQuery();
while (result.next()) {
int questionId = result.getInt("question_id");
String questionText = result.getString("question_text");
String optionA = result.getString("option_a");
String optionB = result.getString("option_b");
String optionC = result.getString("option_c");
String optionD = result.getString("option_d");
String correctAnswer = result.getString("correct_answer");
System.out.println("Question ID: " + questionId);
System.out.println("Question: " + questionText);
System.out.println("Option A: " + optionA);
System.out.println("Option B: " + optionB);
System.out.println("Option C: " + optionC);
System.out.println("Option D: " + optionD);
System.out.println("Correct Answer: " + correctAnswer);
System.out.println("----------------------");
}
} catch (SQLException e) {
e.printStackTrace();
System.out.println("Failed to retrieve questions!");
}
System.out.println("Do you want to go back to the Admin page now?");
System.out.println("1. Yes");
System.out.println("2. No");
int backChoice = scanner.nextInt();
if (backChoice == 1) {
teacherLogin();
} else {
System.out.println("Returning to Main Menu");
}
}
}
Note: You will need to create a separate DatabaseConnector class to handle the connection to your database. This class should include the necessary driver and connection details.
Conclusion
This article has provided a complete guide to building an exam management system, covering database design and Java implementation. You can expand this foundation by adding features like exam scheduling, result analysis, and more advanced teacher functionalities. This framework serves as a starting point for developing a robust and user-friendly exam management system.
原文地址: https://www.cveoy.top/t/topic/f0Jl 著作权归作者所有。请勿转载和采集!