请设计深圳大学教学管理系统（LMS）的核心数据表结构，包括课程、用户（学生、教师）、选课关系等，需考虑数据一致性、扩展性及未来功能（如在线作业、考试）的兼容性。
深圳大学中纺集团难度：中等
答案
1) 【一句话结论】
采用关系型数据库设计，通过规范化表结构（第三范式）保证数据一致性，核心表包括课程、用户（学生/教师）、选课、作业、考试等，通过外键关联，支持在线作业、考试等扩展功能。
2) 【原理/概念讲解】
关系型数据库设计遵循范式（如第三范式），消除冗余，确保数据一致性。核心表通过外键约束关联，例如选课表的外键（user_id、course_id）引用用户表和课程表的主键，避免数据孤立。
- 课程表：存储课程基本信息（如ID、名称、教师、学分），教师ID作为外键关联用户表。
- 用户表：区分学生/教师角色（role字段），密码加密存储。
- 选课表：关联学生和课程，唯一约束（user_id+course_id）确保一个学生不能重复选同一门课。
类比：课程表像图书馆的书籍目录（每本书有唯一编号、书名、作者），用户表像读者和馆员信息（每个用户有唯一ID、姓名、角色），选课表是借书记录（记录读者借了哪本书，时间）。
3) 【对比与适用场景】
| 表名 | 字段（示例） | 约束/说明 |
|---|---|---|
| course | course_id (PK), course_name, teacher_id (FK), credit, start_date, end_date | 主键，外键关联teacher表，日期格式统一（如YYYY-MM-DD） |
| user | user_id (PK), username, role, email, password | role字段区分学生/教师（如student/teacher），密码加密存储 |
| enrollment | enrollment_id (PK), user_id (FK), course_id (FK), enrollment_date | 外键关联user和course，唯一约束（一个学生不能重复选同一门课） |
| assignment | assignment_id (PK), course_id (FK), title, description, due_date, status | 外键关联course，status如“待提交”“已提交”“已批改” |
| exam | exam_id (PK), course_id (FK), title, date, duration, type | 外键关联course，type区分考试类型（如笔试/机试），日期格式统一 |
4) 【示例】
伪代码表结构：
-- 课程表
CREATE TABLE course (
course_id INT PRIMARY KEY,
course_name VARCHAR(100) NOT NULL,
teacher_id INT NOT NULL,
credit INT NOT NULL,
start_date DATE,
end_date DATE,
FOREIGN KEY (teacher_id) REFERENCES user(user_id)
);
-- 用户表
CREATE TABLE user (
user_id INT PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
role ENUM('student', 'teacher') NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL -- 加密存储
);
-- 选课表
CREATE TABLE enrollment (
enrollment_id INT PRIMARY KEY,
user_id INT NOT NULL,
course_id INT NOT NULL,
enrollment_date DATE,
FOREIGN KEY (user_id) REFERENCES user(user_id),
FOREIGN KEY (course_id) REFERENCES course(course_id),
UNIQUE (user_id, course_id) -- 确保唯一选课
);
-- 作业表
CREATE TABLE assignment (
assignment_id INT PRIMARY KEY,
course_id INT NOT NULL,
teacher_id INT NOT NULL,
title VARCHAR(100) NOT NULL,
description TEXT,
due_date DATE NOT NULL,
status ENUM('pending', 'submitted', 'graded') DEFAULT 'pending',
FOREIGN KEY (course_id) REFERENCES course(course_id),
FOREIGN KEY (teacher_id) REFERENCES user(user_id)
);
-- 考试表
CREATE TABLE exam (
exam_id INT PRIMARY KEY,
course_id INT NOT NULL,
teacher_id INT NOT NULL,
title VARCHAR(100) NOT NULL,
exam_date DATE NOT NULL,
duration INT NOT NULL, -- 分钟
type ENUM('written', 'online') NOT NULL,
FOREIGN KEY (course_id) REFERENCES course(course_id),
FOREIGN KEY (teacher_id) REFERENCES user(user_id)
);
5) 【面试口播版答案】
面试官您好，设计深圳大学教学管理系统（LMS）的核心数据表结构，我会采用关系型数据库，通过规范化设计保证数据一致性，核心表包括课程、用户（学生/教师）、选课、作业、考试等，通过外键关联。具体来说，课程表存储课程基本信息（如ID、名称、教师、学分），用户表区分学生和教师角色，选课表关联学生和课程，确保一个学生不能重复选同一门课。作业表和考试表分别支持在线作业和考试功能，通过外键关联课程和教师。外键约束（如选课表的外键）保证数据一致性，字段设计考虑扩展性，比如角色字段支持未来新增角色，日期字段统一格式便于查询。这样设计既满足当前选课需求，又能兼容未来在线作业、考试等扩展功能。
6) 【追问清单】
- 问：如何保证选课数据的一致性？
答：通过选课表的外键约束（user_id和course_id）以及唯一约束（user_id和course_id的组合），确保一个学生不能重复选同一门课，同时外键关联用户和课程表的主键，避免数据孤立。 - 问：如何处理在线作业的提交和批改？
答：作业表通过status字段（如pending、submitted、graded）跟踪状态，教师可以更新状态，系统自动计算成绩，外键关联课程和教师，确保作业归属正确。 - 问：如何支持未来新增角色（如管理员）？
答：用户表中的role字段使用枚举类型（如student/teacher/admin），未来新增角色只需修改枚举值，无需修改表结构，保持扩展性。 - 问：数据迁移时如何处理历史数据？
答：设计时考虑主键自增，字段类型选择可扩展的（如VARCHAR、DATE），迁移时使用数据库迁移工具（如Flyway），确保历史数据完整迁移。 - 问：如何优化查询性能？
答：为高频查询字段（如选课表中的user_id和course_id）创建索引，课程表中的teacher_id创建索引（教师查询课程），作业表中的due_date创建索引（按截止日期查询作业）。
7) 【常见坑/雷区】
- 忽略外键约束导致数据不一致：比如选课表没有外键关联用户表，可能导致学生选课后用户信息丢失。
- 角色表设计不当：将角色字段放在课程表或选课表中，导致数据冗余，应单独设计用户表中的role字段。
- 字段类型选择错误：日期字段使用字符串类型，导致查询和比较效率低，应使用DATE类型。
- 未考虑事务处理：选课操作涉及多个表（用户表、课程表、选课表），需要事务保证原子性，否则可能导致部分更新。
- 扩展性设计不足：字段长度固定（如用户名最多50字符），未来用户名变长需要修改表结构，应使用可变长度字段（如VARCHAR(100)）。