421 lines
19 KiB
SQL
421 lines
19 KiB
SQL
-- =============================================
|
|
-- EduNexus Pro - MySQL数据库建表脚本
|
|
-- 基于Model.ts生成
|
|
-- 版本: 1.0
|
|
-- 日期: 2025-11-25
|
|
-- =============================================
|
|
|
|
-- 设置字符集和时区
|
|
SET NAMES utf8mb4;
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
SET time_zone = '+08:00';
|
|
|
|
-- =============================================
|
|
-- 1. 身份与权限模块
|
|
-- =============================================
|
|
|
|
-- 用户表
|
|
DROP TABLE IF EXISTS `application_users`;
|
|
CREATE TABLE `application_users` (
|
|
`id` VARCHAR(36) PRIMARY KEY COMMENT '用户ID (UUID)',
|
|
`real_name` VARCHAR(50) NOT NULL COMMENT '真实姓名',
|
|
`student_id` VARCHAR(20) NULL COMMENT '学号（学生）',
|
|
`avatar_url` VARCHAR(500) NULL COMMENT '头像URL',
|
|
`gender` ENUM('Male', 'Female') NOT NULL DEFAULT 'Male' COMMENT '性别',
|
|
`current_school_id` VARCHAR(36) NULL COMMENT '当前所属学校ID',
|
|
`account_status` ENUM('Active', 'Suspended', 'Graduated') NOT NULL DEFAULT 'Active' COMMENT '账号状态',
|
|
`email` VARCHAR(100) NULL COMMENT '邮箱',
|
|
`phone` VARCHAR(20) NULL COMMENT '手机号',
|
|
`bio` TEXT NULL COMMENT '个人简介',
|
|
`password_hash` VARCHAR(255) NOT NULL COMMENT '密码Hash',
|
|
|
|
-- 审计字段
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`created_by` VARCHAR(36) NOT NULL COMMENT '创建人ID',
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
`updated_by` VARCHAR(36) NOT NULL COMMENT '更新人ID',
|
|
`is_deleted` BOOLEAN NOT NULL DEFAULT FALSE COMMENT '软删除标记',
|
|
|
|
INDEX `idx_student_id` (`student_id`),
|
|
INDEX `idx_email` (`email`),
|
|
INDEX `idx_phone` (`phone`),
|
|
INDEX `idx_school` (`current_school_id`),
|
|
INDEX `idx_created_at` (`created_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';
|
|
|
|
-- =============================================
|
|
-- 2. 组织架构模块
|
|
-- =============================================
|
|
|
|
-- 学校表
|
|
DROP TABLE IF EXISTS `schools`;
|
|
CREATE TABLE `schools` (
|
|
`id` VARCHAR(36) PRIMARY KEY,
|
|
`name` VARCHAR(100) NOT NULL COMMENT '学校名称',
|
|
`region_code` VARCHAR(20) NOT NULL COMMENT '地区编码',
|
|
`address` VARCHAR(200) NULL COMMENT '学校地址',
|
|
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`created_by` VARCHAR(36) NOT NULL,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`updated_by` VARCHAR(36) NOT NULL,
|
|
`is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
INDEX `idx_region` (`region_code`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='学校表';
|
|
|
|
-- 年级表
|
|
DROP TABLE IF EXISTS `grades`;
|
|
CREATE TABLE `grades` (
|
|
`id` VARCHAR(36) PRIMARY KEY,
|
|
`school_id` VARCHAR(36) NOT NULL COMMENT '所属学校ID',
|
|
`name` VARCHAR(50) NOT NULL COMMENT '年级名称',
|
|
`sort_order` INT NOT NULL COMMENT '排序序号',
|
|
`enrollment_year` INT NOT NULL COMMENT '入学年份',
|
|
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`created_by` VARCHAR(36) NOT NULL,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`updated_by` VARCHAR(36) NOT NULL,
|
|
`is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
FOREIGN KEY (`school_id`) REFERENCES `schools`(`id`) ON DELETE RESTRICT,
|
|
INDEX `idx_school_id` (`school_id`),
|
|
INDEX `idx_enrollment_year` (`enrollment_year`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='年级表';
|
|
|
|
-- 班级表
|
|
DROP TABLE IF EXISTS `classes`;
|
|
CREATE TABLE `classes` (
|
|
`id` VARCHAR(36) PRIMARY KEY,
|
|
`grade_id` VARCHAR(36) NOT NULL COMMENT '所属年级ID',
|
|
`name` VARCHAR(50) NOT NULL COMMENT '班级名称',
|
|
`invite_code` VARCHAR(10) NOT NULL UNIQUE COMMENT '邀请码',
|
|
`head_teacher_id` VARCHAR(36) NULL COMMENT '班主任ID',
|
|
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`created_by` VARCHAR(36) NOT NULL,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`updated_by` VARCHAR(36) NOT NULL,
|
|
`is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
FOREIGN KEY (`grade_id`) REFERENCES `grades`(`id`) ON DELETE RESTRICT,
|
|
FOREIGN KEY (`head_teacher_id`) REFERENCES `application_users`(`id`) ON DELETE SET NULL,
|
|
UNIQUE INDEX `idx_invite_code` (`invite_code`),
|
|
INDEX `idx_grade_id` (`grade_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='班级表';
|
|
|
|
-- 班级成员表
|
|
DROP TABLE IF EXISTS `class_members`;
|
|
CREATE TABLE `class_members` (
|
|
`id` VARCHAR(36) PRIMARY KEY,
|
|
`class_id` VARCHAR(36) NOT NULL COMMENT '班级ID',
|
|
`user_id` VARCHAR(36) NOT NULL COMMENT '用户ID',
|
|
`role_in_class` ENUM('Student', 'Monitor', 'Committee', 'Teacher') NOT NULL DEFAULT 'Student' COMMENT '班级角色',
|
|
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`created_by` VARCHAR(36) NOT NULL,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`updated_by` VARCHAR(36) NOT NULL,
|
|
`is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
FOREIGN KEY (`class_id`) REFERENCES `classes`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`user_id`) REFERENCES `application_users`(`id`) ON DELETE CASCADE,
|
|
UNIQUE INDEX `idx_class_user` (`class_id`, `user_id`),
|
|
INDEX `idx_user_id` (`user_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='班级成员表';
|
|
|
|
-- =============================================
|
|
-- 3. 教材与知识图谱模块
|
|
-- =============================================
|
|
|
|
-- 学科表
|
|
DROP TABLE IF EXISTS `subjects`;
|
|
CREATE TABLE `subjects` (
|
|
`id` VARCHAR(36) PRIMARY KEY,
|
|
`name` VARCHAR(50) NOT NULL COMMENT '学科名称',
|
|
`code` VARCHAR(20) NOT NULL UNIQUE COMMENT '学科代码',
|
|
`icon` VARCHAR(50) NULL COMMENT '图标',
|
|
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`created_by` VARCHAR(36) NOT NULL,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`updated_by` VARCHAR(36) NOT NULL,
|
|
`is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
INDEX `idx_code` (`code`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='学科表';
|
|
|
|
-- 教材表
|
|
DROP TABLE IF EXISTS `textbooks`;
|
|
CREATE TABLE `textbooks` (
|
|
`id` VARCHAR(36) PRIMARY KEY,
|
|
`subject_id` VARCHAR(36) NOT NULL COMMENT '所属学科ID',
|
|
`name` VARCHAR(100) NOT NULL COMMENT '教材名称',
|
|
`publisher` VARCHAR(100) NOT NULL COMMENT '出版社',
|
|
`version_year` VARCHAR(20) NOT NULL COMMENT '版本年份',
|
|
`cover_url` VARCHAR(500) NULL COMMENT '封面URL',
|
|
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`created_by` VARCHAR(36) NOT NULL,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`updated_by` VARCHAR(36) NOT NULL,
|
|
`is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
FOREIGN KEY (`subject_id`) REFERENCES `subjects`(`id`) ON DELETE RESTRICT,
|
|
INDEX `idx_subject_id` (`subject_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='教材表';
|
|
|
|
-- 教材单元表
|
|
DROP TABLE IF EXISTS `textbook_units`;
|
|
CREATE TABLE `textbook_units` (
|
|
`id` VARCHAR(36) PRIMARY KEY,
|
|
`textbook_id` VARCHAR(36) NOT NULL COMMENT '所属教材ID',
|
|
`name` VARCHAR(100) NOT NULL COMMENT '单元名称',
|
|
`sort_order` INT NOT NULL COMMENT '排序序号',
|
|
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`created_by` VARCHAR(36) NOT NULL,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`updated_by` VARCHAR(36) NOT NULL,
|
|
`is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
FOREIGN KEY (`textbook_id`) REFERENCES `textbooks`(`id`) ON DELETE CASCADE,
|
|
INDEX `idx_textbook_id` (`textbook_id`),
|
|
INDEX `idx_sort_order` (`sort_order`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='教材单元表';
|
|
|
|
-- 教材课程表
|
|
DROP TABLE IF EXISTS `textbook_lessons`;
|
|
CREATE TABLE `textbook_lessons` (
|
|
`id` VARCHAR(36) PRIMARY KEY,
|
|
`unit_id` VARCHAR(36) NOT NULL COMMENT '所属单元ID',
|
|
`name` VARCHAR(100) NOT NULL COMMENT '课名称',
|
|
`sort_order` INT NOT NULL COMMENT '排序序号',
|
|
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`created_by` VARCHAR(36) NOT NULL,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`updated_by` VARCHAR(36) NOT NULL,
|
|
`is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
FOREIGN KEY (`unit_id`) REFERENCES `textbook_units`(`id`) ON DELETE CASCADE,
|
|
INDEX `idx_unit_id` (`unit_id`),
|
|
INDEX `idx_sort_order` (`sort_order`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='教材课程表';
|
|
|
|
-- 知识点表
|
|
DROP TABLE IF EXISTS `knowledge_points`;
|
|
CREATE TABLE `knowledge_points` (
|
|
`id` VARCHAR(36) PRIMARY KEY,
|
|
`lesson_id` VARCHAR(36) NOT NULL COMMENT '挂载课节ID',
|
|
`parent_knowledge_point_id` VARCHAR(36) NULL COMMENT '父知识点ID',
|
|
`name` VARCHAR(200) NOT NULL COMMENT '知识点名称',
|
|
`difficulty` INT NOT NULL COMMENT '难度系数(1-5)',
|
|
`description` TEXT NULL COMMENT '描述/口诀',
|
|
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`created_by` VARCHAR(36) NOT NULL,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`updated_by` VARCHAR(36) NOT NULL,
|
|
`is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
FOREIGN KEY (`lesson_id`) REFERENCES `textbook_lessons`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`parent_knowledge_point_id`) REFERENCES `knowledge_points`(`id`) ON DELETE CASCADE,
|
|
INDEX `idx_lesson_id` (`lesson_id`),
|
|
INDEX `idx_parent_id` (`parent_knowledge_point_id`),
|
|
INDEX `idx_difficulty` (`difficulty`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='知识点表';
|
|
|
|
-- =============================================
|
|
-- 4. 题库资源模块
|
|
-- =============================================
|
|
|
|
-- 题目表
|
|
DROP TABLE IF EXISTS `questions`;
|
|
CREATE TABLE `questions` (
|
|
`id` VARCHAR(36) PRIMARY KEY,
|
|
`subject_id` VARCHAR(36) NOT NULL COMMENT '所属学科ID',
|
|
`content` TEXT NOT NULL COMMENT '题干内容(HTML)',
|
|
`options_config` JSON NULL COMMENT '选项配置(JSON)',
|
|
`question_type` ENUM('SingleChoice', 'MultipleChoice', 'TrueFalse', 'FillBlank', 'Subjective') NOT NULL COMMENT '题目类型',
|
|
`answer` TEXT NOT NULL COMMENT '参考答案',
|
|
`explanation` TEXT NULL COMMENT '答案解析',
|
|
`difficulty` INT NOT NULL DEFAULT 3 COMMENT '难度(1-5)',
|
|
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`created_by` VARCHAR(36) NOT NULL,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`updated_by` VARCHAR(36) NOT NULL,
|
|
`is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
FOREIGN KEY (`subject_id`) REFERENCES `subjects`(`id`) ON DELETE RESTRICT,
|
|
INDEX `idx_subject_id` (`subject_id`),
|
|
INDEX `idx_question_type` (`question_type`),
|
|
INDEX `idx_difficulty` (`difficulty`),
|
|
FULLTEXT INDEX `ft_content` (`content`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='题目表';
|
|
|
|
-- 题目-知识点关联表
|
|
DROP TABLE IF EXISTS `question_knowledge`;
|
|
CREATE TABLE `question_knowledge` (
|
|
`id` VARCHAR(36) PRIMARY KEY,
|
|
`question_id` VARCHAR(36) NOT NULL COMMENT '题目ID',
|
|
`knowledge_point_id` VARCHAR(36) NOT NULL COMMENT '知识点ID',
|
|
`weight` INT NOT NULL DEFAULT 100 COMMENT '考察权重(0-100)',
|
|
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`created_by` VARCHAR(36) NOT NULL,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`updated_by` VARCHAR(36) NOT NULL,
|
|
`is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
FOREIGN KEY (`question_id`) REFERENCES `questions`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`knowledge_point_id`) REFERENCES `knowledge_points`(`id`) ON DELETE CASCADE,
|
|
UNIQUE INDEX `idx_question_knowledge` (`question_id`, `knowledge_point_id`),
|
|
INDEX `idx_knowledge_id` (`knowledge_point_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='题目知识点关联表';
|
|
|
|
-- =============================================
|
|
-- 5. 试卷工程模块
|
|
-- =============================================
|
|
|
|
-- 试卷表
|
|
DROP TABLE IF EXISTS `exams`;
|
|
CREATE TABLE `exams` (
|
|
`id` VARCHAR(36) PRIMARY KEY,
|
|
`subject_id` VARCHAR(36) NOT NULL COMMENT '所属学科ID',
|
|
`title` VARCHAR(200) NOT NULL COMMENT '试卷标题',
|
|
`total_score` DECIMAL(5,1) NOT NULL DEFAULT 0 COMMENT '总分',
|
|
`suggested_duration` INT NOT NULL COMMENT '建议时长(分钟)',
|
|
`status` ENUM('Draft', 'Published') NOT NULL DEFAULT 'Draft' COMMENT '状态',
|
|
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`created_by` VARCHAR(36) NOT NULL,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`updated_by` VARCHAR(36) NOT NULL,
|
|
`is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
FOREIGN KEY (`subject_id`) REFERENCES `subjects`(`id`) ON DELETE RESTRICT,
|
|
INDEX `idx_subject_id` (`subject_id`),
|
|
INDEX `idx_status` (`status`),
|
|
INDEX `idx_created_by` (`created_by`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='试卷表';
|
|
|
|
-- 试卷节点表(树形结构)
|
|
DROP TABLE IF EXISTS `exam_nodes`;
|
|
CREATE TABLE `exam_nodes` (
|
|
`id` VARCHAR(36) PRIMARY KEY,
|
|
`exam_id` VARCHAR(36) NOT NULL COMMENT '所属试卷ID',
|
|
`parent_node_id` VARCHAR(36) NULL COMMENT '父节点ID',
|
|
`node_type` ENUM('Group', 'Question') NOT NULL COMMENT '节点类型',
|
|
`question_id` VARCHAR(36) NULL COMMENT '题目ID(Question节点)',
|
|
`title` VARCHAR(200) NULL COMMENT '标题(Group节点)',
|
|
`description` TEXT NULL COMMENT '描述(Group节点)',
|
|
`score` DECIMAL(5,1) NOT NULL COMMENT '分数',
|
|
`sort_order` INT NOT NULL COMMENT '排序序号',
|
|
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`created_by` VARCHAR(36) NOT NULL,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`updated_by` VARCHAR(36) NOT NULL,
|
|
`is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
FOREIGN KEY (`exam_id`) REFERENCES `exams`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`parent_node_id`) REFERENCES `exam_nodes`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`question_id`) REFERENCES `questions`(`id`) ON DELETE RESTRICT,
|
|
INDEX `idx_exam_id` (`exam_id`),
|
|
INDEX `idx_parent_node_id` (`parent_node_id`),
|
|
INDEX `idx_sort_order` (`sort_order`),
|
|
INDEX `idx_question_id` (`question_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='试卷节点表';
|
|
|
|
-- =============================================
|
|
-- 6. 教学执行模块
|
|
-- =============================================
|
|
|
|
-- 作业表
|
|
DROP TABLE IF EXISTS `assignments`;
|
|
CREATE TABLE `assignments` (
|
|
`id` VARCHAR(36) PRIMARY KEY,
|
|
`exam_id` VARCHAR(36) NOT NULL COMMENT '关联试卷ID',
|
|
`class_id` VARCHAR(36) NOT NULL COMMENT '目标班级ID',
|
|
`title` VARCHAR(200) NOT NULL COMMENT '作业标题',
|
|
`start_time` DATETIME NOT NULL COMMENT '开始时间',
|
|
`end_time` DATETIME NOT NULL COMMENT '截止时间',
|
|
`allow_late_submission` BOOLEAN NOT NULL DEFAULT FALSE COMMENT '允许迟交',
|
|
`auto_score_enabled` BOOLEAN NOT NULL DEFAULT TRUE COMMENT '自动判分',
|
|
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`created_by` VARCHAR(36) NOT NULL,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`updated_by` VARCHAR(36) NOT NULL,
|
|
`is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
FOREIGN KEY (`exam_id`) REFERENCES `exams`(`id`) ON DELETE RESTRICT,
|
|
FOREIGN KEY (`class_id`) REFERENCES `classes`(`id`) ON DELETE RESTRICT,
|
|
INDEX `idx_exam_id` (`exam_id`),
|
|
INDEX `idx_class_id` (`class_id`),
|
|
INDEX `idx_start_time` (`start_time`),
|
|
INDEX `idx_end_time` (`end_time`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='作业表';
|
|
|
|
-- 学生提交表
|
|
DROP TABLE IF EXISTS `student_submissions`;
|
|
CREATE TABLE `student_submissions` (
|
|
`id` VARCHAR(36) PRIMARY KEY,
|
|
`assignment_id` VARCHAR(36) NOT NULL COMMENT '作业ID',
|
|
`student_id` VARCHAR(36) NOT NULL COMMENT '学生ID',
|
|
`submission_status` ENUM('Pending', 'Submitted', 'Grading', 'Graded') NOT NULL DEFAULT 'Pending' COMMENT '提交状态',
|
|
`submit_time` DATETIME NULL COMMENT '提交时间',
|
|
`time_spent_seconds` INT NULL COMMENT '耗时(秒)',
|
|
`total_score` DECIMAL(5,1) NULL COMMENT '总得分',
|
|
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`created_by` VARCHAR(36) NOT NULL,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`updated_by` VARCHAR(36) NOT NULL,
|
|
`is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
FOREIGN KEY (`assignment_id`) REFERENCES `assignments`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`student_id`) REFERENCES `application_users`(`id`) ON DELETE CASCADE,
|
|
UNIQUE INDEX `idx_assignment_student` (`assignment_id`, `student_id`),
|
|
INDEX `idx_student_id` (`student_id`),
|
|
INDEX `idx_submit_time` (`submit_time`),
|
|
INDEX `idx_status` (`submission_status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='学生提交表';
|
|
|
|
-- 答题详情表
|
|
DROP TABLE IF EXISTS `submission_details`;
|
|
CREATE TABLE `submission_details` (
|
|
`id` VARCHAR(36) PRIMARY KEY,
|
|
`submission_id` VARCHAR(36) NOT NULL COMMENT '提交ID',
|
|
`exam_node_id` VARCHAR(36) NOT NULL COMMENT '试卷节点ID',
|
|
`student_answer` TEXT NULL COMMENT '学生答案',
|
|
`grading_data` JSON NULL COMMENT '批改数据(Canvas JSON)',
|
|
`score` DECIMAL(5,1) NULL COMMENT '得分',
|
|
`judgement` ENUM('Correct', 'Incorrect', 'Partial') NULL COMMENT '判题结果',
|
|
`teacher_comment` TEXT NULL COMMENT '老师评语',
|
|
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`created_by` VARCHAR(36) NOT NULL,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`updated_by` VARCHAR(36) NOT NULL,
|
|
`is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
FOREIGN KEY (`submission_id`) REFERENCES `student_submissions`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`exam_node_id`) REFERENCES `exam_nodes`(`id`) ON DELETE RESTRICT,
|
|
UNIQUE INDEX `idx_submission_node` (`submission_id`, `exam_node_id`),
|
|
INDEX `idx_node_id` (`exam_node_id`),
|
|
INDEX `idx_judgement` (`judgement`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='答题详情表';
|
|
|
|
-- =============================================
|
|
-- 完成
|
|
-- =============================================
|
|
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
|
|
-- 显示创建的所有表
|
|
SHOW TABLES;
|