200 lines
9.0 KiB
Markdown
200 lines
9.0 KiB
Markdown
|
||
|
||
这是一个非常棒的想法。为了全面测试大语言模型（LLM）的 SQL 能力，我们需要一个既有业务逻辑深度，又包含大量“坑”的数据库设计。
|
||
|
||
我为你设计了一个 **“高校教务与捐赠管理系统”**。
|
||
|
||
这个场景足够复杂，包含了层级结构（学院-系）、多对多关系（学生-课程、教师-课程），以及容易混淆的财务和人员信息。
|
||
|
||
### 1. 数据库概览
|
||
|
||
这个数据库包含 6 张表，以下是它们的关系和陷阱说明：
|
||
|
||
* **Faculties (学院表)**: 顶级组织单位。
|
||
* **Departments (系/专业表)**: 隶属于学院，学生和教师归属于此。
|
||
* *关联*: `Faculties` 1:N `Departments`
|
||
* **Teachers (教师表)**: 包含入职信息。
|
||
* *关联*: `Departments` 1:N `Teachers`
|
||
* **Students (学生表)**: 包含学籍信息。
|
||
* *关联*: `Departments` 1:N `Students`
|
||
* *关联*: `Teachers` N:M (通过 Advisor 关系，这里简化为字段，实际复杂场景可用表)
|
||
* **Courses (课程表)**: 课程基础信息。
|
||
* **Course_Records (选课及成绩表)**: 核心多对多表。
|
||
* *关联*: `Students` N:M `Courses`
|
||
* *关联*: `Teachers` 1:N (一门课一个老师教，但老师教多门课)
|
||
|
||
---
|
||
|
||
### 2. 详细表结构设计 (SQL DDL)
|
||
|
||
你可以直接运行以下 SQL 创建表。
|
||
|
||
```sql
|
||
-- 1. 学院表
|
||
CREATE TABLE Faculties (
|
||
faculty_id INT PRIMARY KEY,
|
||
faculty_name VARCHAR(100),
|
||
dean_name VARCHAR(100) -- 陷阱1: 这里的名字是人名，不是学院名
|
||
);
|
||
|
||
-- 2. 系/专业表
|
||
CREATE TABLE Departments (
|
||
dept_id INT PRIMARY KEY,
|
||
dept_name VARCHAR(100),
|
||
faculty_id INT,
|
||
FOREIGN KEY (faculty_id) REFERENCES Faculties(faculty_id),
|
||
dept_code VARCHAR(10) UNIQUE -- 陷阱2: 类似ID但不是主键，容易搞混
|
||
);
|
||
|
||
-- 3. 教师表
|
||
CREATE TABLE Teachers (
|
||
teacher_id INT PRIMARY KEY,
|
||
teacher_name VARCHAR(100),
|
||
dept_id INT,
|
||
hire_date DATE,
|
||
title VARCHAR(50), -- 职称：Professor, Lecturer
|
||
salary DECIMAL(10, 2), -- 陷阱3: 薪资字段，可能被误用为学生的学费
|
||
FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
|
||
);
|
||
|
||
-- 4. 学生表
|
||
CREATE TABLE Students (
|
||
student_id INT PRIMARY KEY,
|
||
student_name VARCHAR(100),
|
||
dept_id INT,
|
||
enrollment_year INT, -- 陷阱4: 这里存的是年份(2023)，不是日期
|
||
admission_date DATE, -- 陷阱5: 这才是真正的日期
|
||
tuition_fee DECIMAL(10, 2), -- 陷阱6: 学费，容易和 teacher.salary 搞混
|
||
status VARCHAR(20), -- 状态: Active, Graduated, Suspended
|
||
advisor_id INT, -- 导师ID
|
||
total_credit INT DEFAULT 0, -- 陷阱7: 已修总学分，容易和课程的 credit 搞混
|
||
FOREIGN KEY (dept_id) REFERENCES Departments(dept_id),
|
||
FOREIGN KEY (advisor_id) REFERENCES Teachers(teacher_id)
|
||
);
|
||
|
||
-- 5. 课程表
|
||
CREATE TABLE Courses (
|
||
course_id INT PRIMARY KEY,
|
||
course_name VARCHAR(100),
|
||
credit INT, -- 学分
|
||
course_type VARCHAR(50), -- 类型: Core, Elective
|
||
dept_id INT, -- 开课系
|
||
FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
|
||
);
|
||
|
||
-- 6. 选课及成绩表 (多对多关系)
|
||
CREATE TABLE Course_Records (
|
||
record_id INT PRIMARY KEY,
|
||
student_id INT,
|
||
course_id INT,
|
||
teacher_id INT,
|
||
semester VARCHAR(20), -- 例如 "2023-Fall"
|
||
score DECIMAL(5, 2), -- 陷阱8: 卷面分数 (0-100)
|
||
grade_point DECIMAL(3, 2), -- 陷阱9: 绩点 (0.0-4.0)，含义相近易混
|
||
attendance_rate INT, -- 考勤率百分比
|
||
FOREIGN KEY (student_id) REFERENCES Students(student_id),
|
||
FOREIGN KEY (course_id) REFERENCES Courses(course_id),
|
||
FOREIGN KEY (teacher_id) REFERENCES Teachers(teacher_id)
|
||
);
|
||
```
|
||
|
||
---
|
||
|
||
### 3. 迷惑项与陷阱设计解析
|
||
|
||
在设计上述数据库时，我特意埋下了以下几类“坑”，用于测试 LLM 的语义理解能力：
|
||
|
||
1. **相似的字段名与含义**:
|
||
* `Students.enrollment_year` (年份, Integer) vs `Students.admission_date` (日期, Date)。
|
||
* *测试点*: 模型能否识别“年份”和“日期”的区别，在写 WHERE 条件时不会用错。
|
||
* `Course_Records.score` (考试分数) vs `Course_Records.grade_point` (绩点) vs `Courses.credit` (学分)。
|
||
* *测试点*: 当问“平均分”时，模型应该算 `score`；当问“总学分”时，应该累加 `Courses.credit`。模型经常会混淆“分”和“点”。
|
||
* `Teachers.salary` (工资) vs `Students.tuition_fee` (学费)。
|
||
* *测试点*: 两个字段都是钱，模型在写 JOIN 时如果只关注金额字段，很容易连错表。
|
||
|
||
2. **层级关系**:
|
||
* `Faculties` -> `Departments` -> `Students/Teachers`。
|
||
* *测试点*: 如果问题是“统计 **计算机学院** (Faculty) 的所有学生人数”，模型必须通过 `Departments` 表进行两跳连接 (`Students` -> `Departments` -> `Faculties`)，而不能直接连。
|
||
|
||
3. **聚合函数的干扰**:
|
||
* `Students.total_credit` (表中已存在的冗余字段) vs 动态计算 `SUM(Courses.credit)`。
|
||
* *测试点*: 严谨的 SQL 应该基于 `Course_Records` 和 `Courses` 动态计算，但如果模型偷懒，可能会直接读取 `Students.total_credit`。我们可以测试问“实际修读学分”看模型是读字段还是计算。
|
||
|
||
4. **多对多关系的中间表**:
|
||
* `Course_Records` 包含了 `student_id`, `course_id`, `teacher_id`。
|
||
* *测试点*: 问“谁教了张三？”，模型需要 JOIN `Students` -> `Course_Records` -> `Teachers`。如果模型误以为 `Students.advisor_id` 就是任课老师，就会写错。
|
||
|
||
5. **命名相似**:
|
||
* `dept_id` 与 `dept_code`。
|
||
* *测试点*: 如果在查询条件中给出了代码（如 'CS01'），模型必须知道是用 `dept_code` 而不是 `dept_id` 去匹配。
|
||
|
||
---
|
||
|
||
### 4. 插入测试数据
|
||
|
||
为了让测试更真实，这里插入几条基础数据：
|
||
|
||
```sql
|
||
-- Faculties
|
||
INSERT INTO Faculties VALUES (1, 'Engineering', 'Dr. Smith');
|
||
INSERT INTO Faculties VALUES (2, 'Arts', 'Dr. Joyce');
|
||
|
||
-- Departments
|
||
INSERT INTO Departments VALUES (101, 'Computer Science', 1, 'CS');
|
||
INSERT INTO Departments VALUES (102, 'Mechanical Eng', 1, 'ME');
|
||
INSERT INTO Departments VALUES (201, 'English Lit', 2, 'ENG');
|
||
|
||
-- Teachers
|
||
INSERT INTO Teachers VALUES (1, 'Prof. Alice', 101, '2020-01-01', 'Professor', 9000.00);
|
||
INSERT INTO Teachers VALUES (2, 'Dr. Bob', 101, '2021-06-01', 'Lecturer', 6000.00);
|
||
INSERT INTO Teachers VALUES (3, 'Prof. Carol', 201, '2019-09-01', 'Professor', 8500.00);
|
||
|
||
-- Students
|
||
-- 注意：张三和李四在同一个系，王五在不同系
|
||
INSERT INTO Students VALUES (1001, 'Zhang San', 101, 2022, '2022-09-01', 5000.00, 'Active', 1, 0);
|
||
INSERT INTO Students VALUES (1002, 'Li Si', 101, 2023, '2023-09-01', 5500.00, 'Active', 2, 0);
|
||
INSERT INTO Students VALUES (1003, 'Wang Wu', 201, 2022, '2022-09-01', 4500.00, 'Active', 3, 0);
|
||
|
||
-- Courses
|
||
INSERT INTO Courses VALUES (501, 'Database Systems', 4, 'Core', 101);
|
||
INSERT INTO Courses VALUES (502, 'Algorithms', 3, 'Core', 101);
|
||
INSERT INTO Courses VALUES (503, 'Shakespeare', 3, 'Elective', 201);
|
||
|
||
-- Course_Records (选课记录)
|
||
-- 张三选了数据库，老师是Alice，考了85分
|
||
INSERT INTO Course_Records VALUES (1, 1001, 501, 1, '2023-Fall', 85.0, 3.5, 90);
|
||
-- 张三选了算法，老师是Bob，考了92分
|
||
INSERT INTO Course_Records VALUES (2, 1001, 502, 2, '2023-Fall', 92.0, 4.0, 95);
|
||
-- 李四选了数据库，老师是Alice，考了78分
|
||
INSERT INTO Course_Records VALUES (3, 1002, 501, 1, '2023-Fall', 78.0, 2.7, 80);
|
||
-- 王五选了莎士比亚，老师是Carol
|
||
INSERT INTO Course_Records VALUES (4, 1003, 503, 3, '2023-Fall', 88.0, 3.7, 100);
|
||
```
|
||
|
||
---
|
||
|
||
### 5. 推荐的测试 Prompt 样例
|
||
|
||
你可以用下面这些问题去测试模型，观察它是否掉入陷阱：
|
||
|
||
1. **测试层级过滤与相似字段名**:
|
||
* *问题*: “查询 **Engineering 学院** (Faculty) 下所有学生的名字和他们的入学年份。”
|
||
* *陷阱*: 需要两跳 JOIN；必须选 `enrollment_year` 而不是 `admission_date`。
|
||
|
||
2. **测试多对多关系与字段混淆**:
|
||
* *问题*: “查询 ‘Database Systems’ 这门课的平均考试分数。”
|
||
* *陷阱*: 必须选 `Course_Records.score`，不能选 `grade_point`，也不能选 `Courses.credit`。
|
||
|
||
3. **测试金额字段区分**:
|
||
* *问题*: “列出所有学费高于 5000 的学生姓名以及他们所属系的名称。”
|
||
* *陷阱*: 必须用 `Students.tuition_fee`，如果模型写了 `salary` 就错了。
|
||
|
||
4. **测试聚合与 JOIN 顺序**:
|
||
* *问题*: “找出教过学生 ‘Zhang San’ 的所有老师的名字。”
|
||
* *陷阱*: 路径是 `Students` -> `Course_Records` -> `Teachers`。不能直接用 `Students.advisor_id`，因为那只是导师，不一定教过课。
|
||
|
||
5. **测试相似表名/代码**:
|
||
* *问题*: “查询系代码为 'CS' 的系里，职称是 'Professor' 的老师薪资总和。”
|
||
* *陷阱*: 条件是用 `dept_code = 'CS'` 而不是 `dept_id`。
|
||
|
||
这套设计应该能很好地帮你分辨出 LLM 在 Schema Linking（模式链接）和语义理解方面的真实水平。 |