CREATE TABLE user ( user_id VARCHAR(36) PRIMARY KEY COMMENT 'UUID主键', username VARCHAR(50) UNIQUE NOT NULL COMMENT '用户名（学号/工号）', password VARCHAR(100) NOT NULL COMMENT '加密存储', role ENUM('student','teacher','admin') NOT NULL COMMENT '角色类型', interest_tags JSON COMMENT '兴趣标签（如["编程","生物"]）[4](@ref)', ability_level JSON COMMENT '能力维度评分（如{"数学":85,"逻辑":78}）[6](@ref)', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE learning_resource ( resource_id VARCHAR(36) PRIMARY KEY, title VARCHAR(200) NOT NULL, type ENUM('video','document','quiz','project') NOT NULL COMMENT '资源类型', difficulty TINYINT CHECK (difficulty BETWEEN 1 AND 5) COMMENT '难度等级（1-5星）', subject_id VARCHAR(36) NOT NULL COMMENT '关联学科ID', prerequisite JSON COMMENT '前置知识点（如["向量运算","导数"]）[3](@ref)', related_subjects JSON COMMENT '跨学科关联（如["生物信息学","数据科学"]）[3](@ref)', duration INT COMMENT '预估学习时长（分钟）', FOREIGN KEY (subject_id) REFERENCES subject(subject_id) ); CREATE TABLE learning_path ( path_id VARCHAR(36) PRIMARY KEY, user_id VARCHAR(36) NOT NULL, current_step INT DEFAULT 1 COMMENT '当前学习阶段', status ENUM('active','completed','paused') DEFAULT 'active', generated_by ENUM('genetic_algorithm','RL') NOT NULL COMMENT '生成算法类型[1](@ref)', version INT DEFAULT 1 COMMENT '路径版本号', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES user(user_id) ); -- 路径历史版本表（支持动态调整追溯） CREATE TABLE learning_path_history ( history_id VARCHAR(36) PRIMARY KEY, path_id VARCHAR(36) NOT NULL, path_structure JSON NOT NULL COMMENT '资源序列与依赖关系', adjustment_reason VARCHAR(500) COMMENT '如"知识点掌握度低于阈值"[6](@ref)', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (path_id) REFERENCES learning_path(path_id) ); CREATE TABLE learning_progress ( progress_id VARCHAR(36) PRIMARY KEY, user_id VARCHAR(36) NOT NULL, resource_id VARCHAR(36) NOT NULL, completion_rate DECIMAL(5,2) CHECK (completion_rate BETWEEN 0 AND 100), mastery_score DECIMAL(3,2) COMMENT '知识点掌握度（0-1）[6](@ref)', last_accessed TIMESTAMP DEFAULT CURRENT_TIMESTAMP, assessment_result JSON COMMENT '评估指标（如{"正确率":85,"耗时":120s}）[8](@ref)', FOREIGN KEY (user_id) REFERENCES user(user_id), FOREIGN KEY (resource_id) REFERENCES learning_resource(resource_id) ); -- 知识点掌握度动态更新表（支持学习分析） CREATE TABLE knowledge_mastery ( user_id VARCHAR(36), knowledge_node VARCHAR(50) COMMENT '知识点唯一标识', p_current DECIMAL(3,2) COMMENT '当前掌握度[6](@ref)', updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (user_id, knowledge_node) ); CREATE TABLE subject_relation ( relation_id VARCHAR(36) PRIMARY KEY, source_subject VARCHAR(50) NOT NULL COMMENT '如"计算机科学"', target_subject VARCHAR(50) NOT NULL COMMENT '如"分子生物学"', relation_type ENUM('prerequisite','complementary','parallel') NOT NULL, weight DECIMAL(3,2) COMMENT '关联强度（基于PageRank算法）[3](@ref)' ); CREATE TABLE student_profile ( profile_id VARCHAR(36) PRIMARY KEY, user_id VARCHAR(36) UNIQUE NOT NULL, learning_style ENUM('visual','auditory','kinesthetic') COMMENT '学习风格[7](@ref)', cognitive_level TINYINT COMMENT '认知复杂度（1-5级）[3](@ref)', error_pattern JSON COMMENT '高频错误模式（FP-Growth挖掘结果）[6](@ref)', FOREIGN KEY (user_id) REFERENCES user(user_id) ); CREATE TABLE report ( report_id VARCHAR(36) PRIMARY KEY, user_id VARCHAR(36) NOT NULL, metrics JSON COMMENT '如{"学习时长":1200,"知识点覆盖率":78%}', comparison_data JSON COMMENT '同群体对比分位数[7](@ref)', generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES user(user_id) );