Exam Management System Database and Java Code

CREATE DATABASE exam; USE exam;

CREATE TABLE students ( student_id INT PRIMARY KEY, name VARCHAR(50), password VARCHAR(50) );

CREATE TABLE courses ( course_id INT PRIMARY KEY, course_name VARCHAR(100) );

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) );

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) );

CREATE TABLE teachers ( teacher_id INT PRIMARY KEY, name VARCHAR(50), password VARCHAR(50) );

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) );

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON exam.* TO 'new_user'@'localhost';

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);

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:
            // 查看问题页面逻辑
            break;
        case 2:
            // 添加问题页面逻辑
            break;
        case 3:
            // 删除问题页面逻辑
            break;
        case 4:
            // 更新问题页面逻辑
            break;
        case 5:
            System.out.println('返回主页面');
            break;
        default:
            System.out.println('无效的选项！');
            break;
    }
}

}