设计一个数据库表结构，用于存储法学专业学生的选课信息、课程成绩、作业提交记录。请定义表结构（包括主键、外键、字段类型），并解释索引策略（如哪些字段需要建立索引以提高查询效率），以及如何处理成绩的更新与历史记录保留。
答案
1) 【一句话结论】
核心是设计包含学生、课程、选课、成绩、作业提交的表结构，通过外键关联确保数据一致性，用事务保证成绩更新原子性，历史记录通过版本号字段保留，索引聚焦高频查询字段（学号、课程ID、时间字段）。
2) 【原理/概念讲解】
数据库设计需遵循第三范式（消除冗余），主键唯一标识实体（如学号、课程ID），外键建立关联（学生表学号→选课表学号，课程表课程ID→选课表课程ID）。索引通过B树结构加速查询（如学号、课程ID是外键，需建主键索引；时间字段建普通索引）。事务（ACID）保证成绩更新时“原子性”（如先更新版本号再更新成绩，出错则回滚）。历史记录保留可通过版本号字段（如成绩表添加version字段，每次更新递增）或单独历史表（记录每次更新）。
3) 【对比与适用场景】
| 对比项 | 主键索引（如学号、课程ID） | 普通索引（如选课时间、作业提交时间） | 唯一索引（如成绩表的学号+课程ID） |
|---|---|---|---|
| 定义 | 唯一非空，用于唯一标识和关联 | 加速非唯一查询（如按时间范围检索） | 确保字段唯一（如成绩表中每个学生每门课只有一个当前成绩） |
| 特性 | 必须唯一，是外键的基础 | 可重复，仅加速查询 | 防止数据重复 |
| 使用场景 | 标识实体（学生、课程） | 高频时间范围查询（如查询某时间段选课） | 确保成绩唯一性（避免重复录入） |
| 注意点 | 不能为空 | 过多索引影响写入性能 | 避免对频繁更新的字段建唯一索引（如成绩） |
4) 【示例】
-- 学生表
CREATE TABLE students (
student_id VARCHAR(20) PRIMARY KEY,
name VARCHAR(50),
major VARCHAR(50)
);
-- 课程表
CREATE TABLE courses (
course_id VARCHAR(20) PRIMARY KEY,
course_name VARCHAR(100),
credit INT
);
-- 选课表
CREATE TABLE enrollments (
enrollment_id BIGINT AUTO_INCREMENT PRIMARY KEY,
student_id VARCHAR(20) NOT NULL,
course_id VARCHAR(20) NOT NULL,
enroll_time DATETIME,
status ENUM('enrolled','completed') DEFAULT 'enrolled',
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
-- 成绩表（带版本号）
CREATE TABLE grades (
grade_id BIGINT AUTO_INCREMENT PRIMARY KEY,
student_id VARCHAR(20) NOT NULL,
course_id VARCHAR(20) NOT NULL,
score DECIMAL(5,2),
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
version INT DEFAULT 1,
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id),
UNIQUE KEY (student_id, course_id) -- 确保当前成绩唯一
);
-- 作业表
CREATE TABLE assignments (
assignment_id BIGINT AUTO_INCREMENT PRIMARY KEY,
course_id VARCHAR(20) NOT NULL,
assignment_name VARCHAR(100),
due_date DATETIME,
FOREIGN KEY (course_id) REFERENCES courses(course_id),
INDEX idx_course_assignment (course_id) -- 加速按课程查询作业
);
-- 作业提交记录
CREATE TABLE assignment_submissions (
submission_id BIGINT AUTO_INCREMENT PRIMARY KEY,
student_id VARCHAR(20) NOT NULL,
assignment_id BIGINT NOT NULL,
submission_time DATETIME,
file_path VARCHAR(255),
status ENUM('submitted','reviewed') DEFAULT 'submitted',
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (assignment_id) REFERENCES assignments(assignment_id),
INDEX idx_student_assignment (student_id, assignment_id) -- 加速按学生和作业查询
);
5) 【面试口播版答案】
“面试官您好，针对法学专业学生的选课、成绩和作业提交，我设计三个核心表：学生表、课程表、选课表，以及成绩表和作业提交表。学生表用学号主键，课程表用课程ID主键，选课表通过学号和课程ID的外键关联两者，确保每个学生每门课的选课记录唯一。成绩表用学号+课程ID作为唯一键（主键的一部分），同时添加更新时间和版本号，当成绩更新时，版本号递增，这样既能保留历史记录，又能保证当前成绩的唯一性。作业提交表用课程ID和作业ID关联，通过外键约束保证数据一致性。索引方面，学号、课程ID是外键，也是查询高频字段，所以建立主键索引；选课时间、作业提交时间等时间字段建立普通索引，加速按时间范围查询；成绩表的学号+课程ID唯一键也建立唯一索引，防止重复成绩。成绩更新时，通过事务（BEGIN TRANSACTION...COMMIT）保证原子性，比如先更新版本号，再更新成绩，如果中间出错，事务回滚，避免数据不一致。历史记录保留方面，成绩表通过版本号字段记录每次更新，比如第一次成绩80分，版本1，更新为85分，版本2，这样查询历史成绩时，按版本号降序即可。作业提交记录中，提交文件路径用VARCHAR存储，如果文件较大，可以存文件名和存储路径，避免存储大文件影响性能。”
6) 【追问清单】
- 问题：外键约束如何处理级联更新或删除？
回答要点：设置ON UPDATE CASCADE（更新时级联更新）和ON DELETE RESTRICT（删除时限制，避免数据丢失）。 - 问题：历史记录如何查询？
回答要点：通过成绩表的version字段，按版本号降序查询，或单独建立成绩历史表记录每次更新。 - 问题：索引是否影响写入性能？
回答要点：主键索引是必须的，普通索引对写入影响较小，但过多索引会降低写入速度，需根据查询需求合理设计。 - 问题：作业提交记录中，如何处理大文件？
回答要点：存储文件名和路径，实际文件存到文件系统或对象存储（如OSS），数据库只存元数据。 - 问题：如果学生转专业，选课表中的专业字段如何处理？
回答要点：在学生表中维护专业信息，选课表通过学号关联学生表，转专业时更新学生表的专业字段，选课表无需修改。
7) 【常见坑/雷区】
- 忽略历史记录导致数据不可追溯（如只存储当前成绩，无法查询历史成绩）。
- 外键未设置约束导致数据不一致（如学生或课程被删除，选课表中的外键没有约束，导致数据错误）。
- 索引过多影响写入性能（如为每个字段都建索引，导致插入、更新、删除操作变慢）。
- 成绩更新未用事务导致数据不一致（如成绩更新时，先更新成绩，再更新版本号，中间出错，导致版本号不递增，数据不一致）。
- 作业提交记录未考虑文件存储路径，导致数据库存储大文件，影响性能。