489 lines
16 KiB
PL/PgSQL
489 lines
16 KiB
PL/PgSQL
-- 高中数学知识图谱数据库架构
|
||
-- 基于三层架构设计：Knowledge（知识层）、Method（方法层）、Problem（题目层）
|
||
|
||
-- 创建数据库（如果需要）
|
||
-- CREATE DATABASE math_knowledge_graph;
|
||
-- \c math_knowledge_graph;
|
||
|
||
-- 启用必要的扩展
|
||
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
||
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
|
||
|
||
-- ===========================================
|
||
-- 1. 元数据表（教材信息）
|
||
-- ===========================================
|
||
|
||
-- 教材表
|
||
CREATE TABLE textbooks (
|
||
id SERIAL PRIMARY KEY,
|
||
name VARCHAR(100) NOT NULL,
|
||
publisher VARCHAR(50) DEFAULT '人教版',
|
||
version VARCHAR(20) DEFAULT 'A版',
|
||
volume_type VARCHAR(20) CHECK (volume_type IN ('必修', '选择性必修')),
|
||
volume_number INTEGER,
|
||
description TEXT,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 章节表
|
||
CREATE TABLE chapters (
|
||
id SERIAL PRIMARY KEY,
|
||
textbook_id INTEGER REFERENCES textbooks(id) ON DELETE CASCADE,
|
||
chapter_number INTEGER NOT NULL,
|
||
chapter_name VARCHAR(100) NOT NULL,
|
||
chapter_order INTEGER NOT NULL,
|
||
description TEXT,
|
||
page_range VARCHAR(20),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE(textbook_id, chapter_number)
|
||
);
|
||
|
||
-- 小节表
|
||
CREATE TABLE sections (
|
||
id SERIAL PRIMARY KEY,
|
||
chapter_id INTEGER REFERENCES chapters(id) ON DELETE CASCADE,
|
||
section_number VARCHAR(10) NOT NULL,
|
||
section_name VARCHAR(100) NOT NULL,
|
||
section_order INTEGER NOT NULL,
|
||
page_start INTEGER,
|
||
page_end INTEGER,
|
||
description TEXT,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE(chapter_id, section_number)
|
||
);
|
||
|
||
-- ===========================================
|
||
-- 2. 知识层（Knowledge Layer）
|
||
-- ===========================================
|
||
|
||
-- 知识点主表
|
||
CREATE TABLE knowledge_points (
|
||
id SERIAL PRIMARY KEY,
|
||
code VARCHAR(20) UNIQUE NOT NULL, -- 如 K1-1-1-01
|
||
name VARCHAR(100) NOT NULL,
|
||
level VARCHAR(10) CHECK (level IN ('二级', '三级')),
|
||
type VARCHAR(20) CHECK (type IN ('概念/定义', '定理/性质', '公式')),
|
||
importance VARCHAR(10) CHECK (importance IN ('核心', '重要', '基础')),
|
||
section_id INTEGER REFERENCES sections(id),
|
||
|
||
-- 核心内容（JSON格式存储复杂结构）
|
||
core_content JSONB,
|
||
|
||
-- 原理说明
|
||
principle_explanation JSONB,
|
||
|
||
-- 适用条件
|
||
application_conditions JSONB,
|
||
|
||
-- 关联内容
|
||
related_content JSONB,
|
||
|
||
-- 考查方式
|
||
examination_methods TEXT[],
|
||
|
||
-- 教材位置
|
||
textbook_location VARCHAR(100),
|
||
|
||
-- 创建和更新时间
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 知识点关系表
|
||
CREATE TABLE knowledge_relations (
|
||
id SERIAL PRIMARY KEY,
|
||
source_knowledge_id INTEGER REFERENCES knowledge_points(id) ON DELETE CASCADE,
|
||
target_knowledge_id INTEGER REFERENCES knowledge_points(id) ON DELETE CASCADE,
|
||
relation_type VARCHAR(20) CHECK (relation_type IN ('前置知识', '包含关系', '关联内容')),
|
||
strength FLOAT DEFAULT 1.0 CHECK (strength >= 0 AND strength <= 1),
|
||
description TEXT,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE(source_knowledge_id, target_knowledge_id, relation_type)
|
||
);
|
||
|
||
-- ===========================================
|
||
-- 3. 方法层（Method Layer）
|
||
-- ===========================================
|
||
|
||
-- 解题方法主表
|
||
CREATE TABLE methods (
|
||
id SERIAL PRIMARY KEY,
|
||
code VARCHAR(20) UNIQUE NOT NULL, -- 如 M1-1-1-01
|
||
name VARCHAR(100) NOT NULL,
|
||
type VARCHAR(20) CHECK (type IN ('解题方法', '计算技巧', '证明方法')),
|
||
difficulty_level INTEGER CHECK (difficulty_level >= 1 AND difficulty_level <= 5),
|
||
section_id INTEGER REFERENCES sections(id),
|
||
|
||
-- 适用场景
|
||
application_scenarios JSONB,
|
||
|
||
-- 数学思想
|
||
mathematical_thoughts TEXT[],
|
||
|
||
-- 解题策略
|
||
problem_solving_strategy TEXT,
|
||
|
||
-- 教材位置
|
||
textbook_location VARCHAR(100),
|
||
|
||
-- 描述
|
||
description TEXT,
|
||
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 方法步骤表
|
||
CREATE TABLE method_steps (
|
||
id SERIAL PRIMARY KEY,
|
||
method_id INTEGER REFERENCES methods(id) ON DELETE CASCADE,
|
||
step_number INTEGER NOT NULL,
|
||
step_description TEXT NOT NULL,
|
||
key_points TEXT[],
|
||
common_mistakes TEXT[],
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE(method_id, step_number)
|
||
);
|
||
|
||
-- 方法常见错误表
|
||
CREATE TABLE method_common_errors (
|
||
id SERIAL PRIMARY KEY,
|
||
method_id INTEGER REFERENCES methods(id) ON DELETE CASCADE,
|
||
error_description TEXT NOT NULL,
|
||
cause_analysis TEXT,
|
||
correct_approach TEXT,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 方法支撑知识点表
|
||
CREATE TABLE method_support_knowledge (
|
||
id SERIAL PRIMARY KEY,
|
||
method_id INTEGER REFERENCES methods(id) ON DELETE CASCADE,
|
||
knowledge_id INTEGER REFERENCES knowledge_points(id) ON DELETE CASCADE,
|
||
support_type VARCHAR(20) CHECK (support_type IN ('直接支撑', '间接支撑')),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE(method_id, knowledge_id)
|
||
);
|
||
|
||
-- ===========================================
|
||
-- 4. 题目层（Problem Layer）
|
||
-- ===========================================
|
||
|
||
-- 题目主表
|
||
CREATE TABLE problems (
|
||
id SERIAL PRIMARY KEY,
|
||
code VARCHAR(20) UNIQUE NOT NULL, -- 如 T1-1-1-E01
|
||
title TEXT,
|
||
problem_type VARCHAR(20) CHECK (problem_type IN ('例题', '练习题', '习题', '思考题')),
|
||
|
||
-- 来源信息
|
||
source_info JSONB,
|
||
|
||
-- 题目内容
|
||
stem TEXT, -- 题干
|
||
full_content TEXT, -- 完整题目
|
||
images_info JSONB, -- 图片信息
|
||
|
||
-- 题型分类
|
||
problem_category JSONB,
|
||
|
||
-- 难度评估
|
||
difficulty_info JSONB,
|
||
|
||
-- 教材位置
|
||
textbook_location VARCHAR(100),
|
||
|
||
-- 小题数量
|
||
subquestion_count INTEGER DEFAULT 0,
|
||
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 小题表
|
||
CREATE TABLE problem_subquestions (
|
||
id SERIAL PRIMARY KEY,
|
||
problem_id INTEGER REFERENCES problems(id) ON DELETE CASCADE,
|
||
subquestion_number VARCHAR(10) NOT NULL, -- 如 (1), (2)
|
||
content TEXT,
|
||
difficulty_level INTEGER CHECK (difficulty_level >= 1 AND difficulty_level <= 5),
|
||
solution TEXT,
|
||
answer TEXT,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE(problem_id, subquestion_number)
|
||
);
|
||
|
||
-- 题目知识点标注表
|
||
CREATE TABLE problem_knowledge_tags (
|
||
id SERIAL PRIMARY KEY,
|
||
problem_id INTEGER REFERENCES problems(id) ON DELETE CASCADE,
|
||
subquestion_id INTEGER REFERENCES problem_subquestions(id) ON DELETE CASCADE,
|
||
knowledge_id INTEGER REFERENCES knowledge_points(id) ON DELETE CASCADE,
|
||
tag_type VARCHAR(20) CHECK (tag_type IN ('主要考查', '辅助涉及')),
|
||
weight FLOAT DEFAULT 1.0 CHECK (weight > 0),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE(problem_id, subquestion_id, knowledge_id, tag_type)
|
||
);
|
||
|
||
-- 题目方法标注表
|
||
CREATE TABLE problem_method_tags (
|
||
id SERIAL PRIMARY KEY,
|
||
problem_id INTEGER REFERENCES problems(id) ON DELETE CASCADE,
|
||
subquestion_id INTEGER REFERENCES problem_subquestions(id) ON DELETE CASCADE,
|
||
method_id INTEGER REFERENCES methods(id) ON DELETE CASCADE,
|
||
usage_frequency VARCHAR(20) CHECK (usage_frequency IN ('主要方法', '辅助方法')),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE(problem_id, subquestion_id, method_id)
|
||
);
|
||
|
||
-- ===========================================
|
||
-- 5. 学习分析和扩展表
|
||
-- ===========================================
|
||
|
||
-- 学习路径表（为个性化推荐预留）
|
||
CREATE TABLE learning_paths (
|
||
id SERIAL PRIMARY KEY,
|
||
name VARCHAR(100) NOT NULL,
|
||
description TEXT,
|
||
knowledge_sequence JSONB,
|
||
difficulty_progression JSONB,
|
||
estimated_hours INTEGER,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 难度统计表
|
||
CREATE TABLE difficulty_stats (
|
||
id SERIAL PRIMARY KEY,
|
||
stat_type VARCHAR(20) NOT NULL,
|
||
reference_type VARCHAR(20) NOT NULL, -- knowledge, method, problem
|
||
reference_id INTEGER NOT NULL,
|
||
avg_difficulty FLOAT,
|
||
difficulty_distribution JSONB,
|
||
sample_size INTEGER,
|
||
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE(stat_type, reference_type, reference_id)
|
||
);
|
||
|
||
-- ===========================================
|
||
-- 6. 全文搜索支持
|
||
-- ===========================================
|
||
|
||
-- 创建全文搜索配置（中文支持）
|
||
CREATE TEXT SEARCH CONFIGURATION chinese (COPY = simple);
|
||
|
||
-- 搜索索引更新函数
|
||
CREATE OR REPLACE FUNCTION update_search_vectors()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
-- 更新知识点的搜索向量
|
||
IF TG_TABLE_NAME = 'knowledge_points' THEN
|
||
UPDATE knowledge_points
|
||
SET search_vector =
|
||
setweight(to_tsvector('chinese', COALESCE(name, '')), 'A') ||
|
||
setweight(to_tsvector('chinese', COALESCE(core_content::text, '')), 'B') ||
|
||
setweight(to_tsvector('chinese', COALESCE(principle_explanation::text, '')), 'C')
|
||
WHERE id = NEW.id;
|
||
END IF;
|
||
|
||
-- 更新方法的搜索向量
|
||
IF TG_TABLE_NAME = 'methods' THEN
|
||
UPDATE methods
|
||
SET search_vector =
|
||
setweight(to_tsvector('chinese', COALESCE(name, '')), 'A') ||
|
||
setweight(to_tsvector('chinese', COALESCE(problem_solving_strategy, '')), 'B') ||
|
||
setweight(to_tsvector('chinese', COALESCE(description, '')), 'C')
|
||
WHERE id = NEW.id;
|
||
END IF;
|
||
|
||
-- 更新题目的搜索向量
|
||
IF TG_TABLE_NAME = 'problems' THEN
|
||
UPDATE problems
|
||
SET search_vector =
|
||
setweight(to_tsvector('chinese', COALESCE(title, '')), 'A') ||
|
||
setweight(to_tsvector('chinese', COALESCE(stem, '')), 'B') ||
|
||
setweight(to_tsvector('chinese', COALESCE(full_content, '')), 'C')
|
||
WHERE id = NEW.id;
|
||
END IF;
|
||
|
||
RETURN NEW;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 为主要表添加搜索向量字段
|
||
ALTER TABLE knowledge_points ADD COLUMN search_vector tsvector;
|
||
ALTER TABLE methods ADD COLUMN search_vector tsvector;
|
||
ALTER TABLE problems ADD COLUMN search_vector tsvector;
|
||
|
||
-- 创建触发器
|
||
CREATE TRIGGER update_knowledge_search_vector
|
||
AFTER INSERT OR UPDATE ON knowledge_points
|
||
FOR EACH ROW EXECUTE FUNCTION update_search_vectors();
|
||
|
||
CREATE TRIGGER update_method_search_vector
|
||
AFTER INSERT OR UPDATE ON methods
|
||
FOR EACH ROW EXECUTE FUNCTION update_search_vectors();
|
||
|
||
CREATE TRIGGER update_problem_search_vector
|
||
AFTER INSERT OR UPDATE ON problems
|
||
FOR EACH ROW EXECUTE FUNCTION update_search_vectors();
|
||
|
||
-- ===========================================
|
||
-- 7. 索引创建
|
||
-- ===========================================
|
||
|
||
-- 基础索引
|
||
CREATE INDEX idx_knowledge_points_code ON knowledge_points(code);
|
||
CREATE INDEX idx_knowledge_points_level ON knowledge_points(level);
|
||
CREATE INDEX idx_knowledge_points_type ON knowledge_points(type);
|
||
CREATE INDEX idx_knowledge_points_importance ON knowledge_points(importance);
|
||
CREATE INDEX idx_knowledge_points_section ON knowledge_points(section_id);
|
||
|
||
CREATE INDEX idx_methods_code ON methods(code);
|
||
CREATE INDEX idx_methods_type ON methods(type);
|
||
CREATE INDEX idx_methods_difficulty ON methods(difficulty_level);
|
||
CREATE INDEX idx_methods_section ON methods(section_id);
|
||
|
||
CREATE INDEX idx_problems_code ON problems(code);
|
||
CREATE INDEX idx_problems_type ON problems(problem_type);
|
||
CREATE INDEX idx_problems_difficulty ON problems((difficulty_info->>'整体难度')::int);
|
||
|
||
-- JSON字段索引
|
||
CREATE INDEX idx_knowledge_points_content ON knowledge_points USING GIN(core_content);
|
||
CREATE INDEX idx_methods_scenarios ON methods USING GIN(application_scenarios);
|
||
CREATE INDEX idx_problems_source ON problems USING GIN(source_info);
|
||
|
||
-- 搜索向量索引
|
||
CREATE INDEX idx_knowledge_points_search ON knowledge_points USING GIN(search_vector);
|
||
CREATE INDEX idx_methods_search ON methods USING GIN(search_vector);
|
||
CREATE INDEX idx_problems_search ON problems USING GIN(search_vector);
|
||
|
||
-- 关系表索引
|
||
CREATE INDEX idx_knowledge_relations_source ON knowledge_relations(source_knowledge_id);
|
||
CREATE INDEX idx_knowledge_relations_target ON knowledge_relations(target_knowledge_id);
|
||
CREATE INDEX idx_knowledge_relations_type ON knowledge_relations(relation_type);
|
||
|
||
CREATE INDEX idx_problem_knowledge_tags_problem ON problem_knowledge_tags(problem_id);
|
||
CREATE INDEX idx_problem_knowledge_tags_knowledge ON problem_knowledge_tags(knowledge_id);
|
||
CREATE INDEX idx_problem_method_tags_problem ON problem_method_tags(problem_id);
|
||
CREATE INDEX idx_problem_method_tags_method ON problem_method_tags(method_id);
|
||
|
||
-- ===========================================
|
||
-- 8. 约束和触发器
|
||
-- ===========================================
|
||
|
||
-- 更新时间触发器
|
||
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
NEW.updated_at = CURRENT_TIMESTAMP;
|
||
RETURN NEW;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 为需要的表添加更新时间触发器
|
||
CREATE TRIGGER update_knowledge_points_updated_at
|
||
BEFORE UPDATE ON knowledge_points
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
CREATE TRIGGER update_methods_updated_at
|
||
BEFORE UPDATE ON methods
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
CREATE TRIGGER update_problems_updated_at
|
||
BEFORE UPDATE ON problems
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
-- 检查约束
|
||
ALTER TABLE knowledge_points ADD CONSTRAINT chk_knowledge_code_format
|
||
CHECK (code ~ '^K\d+-\d+-\d+-\d+$');
|
||
|
||
ALTER TABLE methods ADD CONSTRAINT chk_method_code_format
|
||
CHECK (code ~ '^M\d+-\d+-\d+-\d+$');
|
||
|
||
ALTER TABLE problems ADD CONSTRAINT chk_problem_code_format
|
||
CHECK (code ~ '^T\d+-\d+-\d+-[A-Z]\d+$');
|
||
|
||
-- ===========================================
|
||
-- 9. 初始数据插入
|
||
-- ===========================================
|
||
|
||
-- 插入教材信息示例
|
||
INSERT INTO textbooks (name, volume_type, volume_number, description) VALUES
|
||
('高中数学必修第一册', '必修', 1, '人教版高中数学必修第一册'),
|
||
('高中数学必修第二册', '必修', 2, '人教版高中数学必修第二册'),
|
||
('高中数学选择性必修第一册', '选择性必修', 1, '人教版高中数学选择性必修第一册'),
|
||
('高中数学选择性必修第二册', '选择性必修', 2, '人教版高中数学选择性必修第二册'),
|
||
('高中数学选择性必修第三册', '选择性必修', 3, '人教版高中数学选择性必修第三册');
|
||
|
||
-- ===========================================
|
||
-- 10. 视图创建
|
||
-- ===========================================
|
||
|
||
-- 知识点详细信息视图
|
||
CREATE VIEW knowledge_detail_view AS
|
||
SELECT
|
||
kp.id,
|
||
kp.code,
|
||
kp.name,
|
||
kp.level,
|
||
kp.type,
|
||
kp.importance,
|
||
s.section_name,
|
||
c.chapter_name,
|
||
t.name as textbook_name,
|
||
kp.core_content,
|
||
kp.principle_explanation,
|
||
kp.examination_methods,
|
||
kp.textbook_location,
|
||
kp.created_at
|
||
FROM knowledge_points kp
|
||
LEFT JOIN sections s ON kp.section_id = s.id
|
||
LEFT JOIN chapters c ON s.chapter_id = c.id
|
||
LEFT JOIN textbooks t ON c.textbook_id = t.id;
|
||
|
||
-- 题目详细信息视图
|
||
CREATE VIEW problem_detail_view AS
|
||
SELECT
|
||
p.id,
|
||
p.code,
|
||
p.problem_type,
|
||
p.title,
|
||
p.stem,
|
||
p.full_content,
|
||
p.difficulty_info,
|
||
s.section_name,
|
||
c.chapter_name,
|
||
t.name as textbook_name,
|
||
p.textbook_location,
|
||
p.subquestion_count,
|
||
p.created_at
|
||
FROM problems p
|
||
LEFT JOIN knowledge_points kp ON p.section_id = kp.section_id
|
||
LEFT JOIN sections s ON kp.section_id = s.id
|
||
LEFT JOIN chapters c ON s.chapter_id = c.id
|
||
LEFT JOIN textbooks t ON c.textbook_id = t.id;
|
||
|
||
-- 方法详细信息视图
|
||
CREATE VIEW method_detail_view AS
|
||
SELECT
|
||
m.id,
|
||
m.code,
|
||
m.name,
|
||
m.type,
|
||
m.difficulty_level,
|
||
m.mathematical_thoughts,
|
||
m.problem_solving_strategy,
|
||
s.section_name,
|
||
c.chapter_name,
|
||
t.name as textbook_name,
|
||
m.textbook_location,
|
||
m.created_at
|
||
FROM methods m
|
||
LEFT JOIN sections s ON m.section_id = s.id
|
||
LEFT JOIN chapters c ON s.chapter_id = c.id
|
||
LEFT JOIN textbooks t ON c.textbook_id = t.id;
|
||
|
||
COMMENT ON DATABASE math_knowledge_graph IS '高中数学知识图谱数据库'; |