假设你负责设计一个校园科研管理系统，需要支持跨院系、跨学科的项目申报、经费管理和成果展示。请设计数据库模型，并说明如何处理数据的一致性和权限控制。

深圳大学上海交运难度：困难

答案

1) 【一句话结论】
以项目为核心构建关系型数据库模型，通过多对多关系表实现跨院系、跨学科管理，结合ACID事务保障数据一致性，采用RBAC模型实现细粒度权限控制，并考虑性能优化（如索引、缓存）和业务流程（如经费审批）。

2) 【原理/概念讲解】
老师会解释：关系型数据库通过表结构存储数据，主键与外键保证数据关联。跨学科项目需多对多关系，需中间表（如项目-学科关联表）支持一个项目属于多个学科（类比：项目表是“项目库”，院系、学科是“分类标签”，中间表是“标签关联”）。事务（ACID）确保数据操作原子性，避免不一致（类比：调整经费时，整笔操作要么全成功要么全失败，不能只改部分）。RBAC按角色分配权限，简化管理（类比：用户职责，教师管项目、学生看成果）。此外，为提升性能，可对常用查询字段（如项目ID、院系ID）添加索引，用Redis缓存常用项目信息；经费审批通过事务管理审批流程，确保审批步骤原子性。

3) 【对比与适用场景】

模型/机制	定义	特性	使用场景	注意点
项目-学科多对多关系（中间表）	项目表与学科表通过中间表关联，支持一个项目属于多个学科	支持跨学科，数据关联灵活	交叉学科项目（如人工智能与数学）	需额外维护中间表，增加查询复杂度（如查询某学科项目需JOIN多表）
单表关联（外键直接关联）	项目表直接用外键关联学科表	简单，但一个项目只能属于一个学科	单学科项目（如计算机学院纯计算机项目）	无法处理跨学科需求
事务类型（ACID vs 乐观锁）	ACID事务：确保操作原子性、一致性、隔离性、持久性；乐观锁：通过版本号检查冲突	ACID保证强一致性，开销大；乐观锁适用于低冲突场景，性能高	经费调整（高冲突）、项目状态变更（低冲突）	ACID在高并发下可能阻塞，乐观锁可能因冲突回滚
权限模型（RBAC vs ABAC）	RBAC：用户属于角色，角色有权限；ABAC：权限基于用户属性、资源属性、环境属性	RBAC简单易管理，适合角色固定场景；ABAC灵活动态	校园用户（教师、学生、管理员）	RBAC角色更新需同步所有用户，ABAC实现复杂

4) 【示例】
数据库表结构（伪代码）：

-- 项目表（核心）
CREATE TABLE project (
    project_id INT PRIMARY KEY,
    name VARCHAR(100),
    start_date DATE,
    end_date DATE,
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES user(user_id)
);

-- 院系表
CREATE TABLE department (
    department_id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 学科表
CREATE TABLE discipline (
    discipline_id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 项目-学科多对多关联表
CREATE TABLE project_discipline (
    project_id INT,
    discipline_id INT,
    PRIMARY KEY (project_id, discipline_id),
    FOREIGN KEY (project_id) REFERENCES project(project_id),
    FOREIGN KEY (discipline_id) REFERENCES discipline(discipline_id)
);

-- 经费表（带版本号用于乐观锁）
CREATE TABLE fund (
    fund_id INT PRIMARY KEY,
    project_id INT,
    amount DECIMAL(10,2),
    period DATE,
    version INT, -- 乐观锁版本号
    FOREIGN KEY (project_id) REFERENCES project(project_id)
);

-- 成果表
CREATE TABLE achievement (
    achievement_id INT PRIMARY KEY,
    project_id INT,
    type VARCHAR(20), -- 论文、专利等
    content TEXT,
    FOREIGN KEY (project_id) REFERENCES project(project_id)
);

-- 用户表
CREATE TABLE user (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    role_id INT,
    FOREIGN KEY (role_id) REFERENCES role(role_id)
);

-- 角色表
CREATE TABLE role (
    role_id INT PRIMARY KEY,
    name VARCHAR(20) -- 教师、学生、管理员
);

-- 权限表
CREATE TABLE permission (
    permission_id INT PRIMARY KEY,
    role_id INT,
    action VARCHAR(50), -- 如"create_project"、"approve_fund"
    resource VARCHAR(50),
    FOREIGN KEY (role_id) REFERENCES role(role_id)
);

-- 经费审批表（业务流程）
CREATE TABLE fund_approval (
    approval_id INT PRIMARY KEY,
    fund_id INT,
    approver_id INT,
    status VARCHAR(20), -- 待审批、已批准、已拒绝
    approval_time TIMESTAMP,
    FOREIGN KEY (fund_id) REFERENCES fund(fund_id),
    FOREIGN KEY (approver_id) REFERENCES user(user_id)
);

事务示例（经费调整，带乐观锁检查）：

BEGIN TRANSACTION;

-- 检查版本号是否一致（当前版本为v1）
SELECT version FROM fund WHERE fund_id = 1 AND period = '2024-01-01' AND version = 1;
-- 如果版本一致，执行更新
UPDATE fund SET amount = amount - 100, version = version + 1 WHERE fund_id = 1 AND period = '2024-01-01' AND version = 1;

COMMIT;

经费审批事务（管理员审批经费）：

BEGIN TRANSACTION;

-- 更新经费状态（从待审批到已批准）
UPDATE fund SET status = 'approved' WHERE fund_id = 1 AND period = '2024-01-01';
-- 记录审批日志
INSERT INTO fund_approval (fund_id, approver_id, status, approval_time) 
VALUES (1, 1001, 'approved', NOW());

COMMIT;

5) 【面试口播版答案】
面试官您好，我设计校园科研管理系统数据库时，核心是以“项目”为中心构建模型，通过多对多关系表实现跨院系、跨学科管理。首先，表结构上，项目表关联院系和学科，中间表（项目-学科关联表）支持一个项目属于多个学科，比如“人工智能与数学交叉研究”项目同时属于计算机学院和数学学院。数据一致性方面，采用ACID事务确保经费调整等操作原子性，避免数据不一致；并发修改时，用乐观锁（经费表加版本号字段）检查冲突，不一致则回滚。权限控制用RBAC模型，角色分教师、学生、管理员，权限表定义操作权限（如教师能申报项目、审批经费，学生只能查看成果）。性能优化上，为常用查询字段（项目ID、院系ID）添加索引，用Redis缓存项目基本信息减少数据库压力。经费审批通过事务管理审批流程，确保审批步骤完整。整体设计兼顾跨学科管理、数据一致性和权限控制，且考虑了性能和业务流程。

6) 【追问清单】

如何处理跨院系跨学科的数据关联？
回答要点：通过项目表与学科表的多对多关系（中间表），记录项目与学科的关联，支持一个项目属于多个学科。
如何保证经费数据在多用户并发修改时的一致性？
回答要点：采用乐观锁机制（经费表加version字段），检查版本号是否一致，不一致则回滚，避免数据冲突。
如何处理临时授权（如临时让学生参与项目）？
回答要点：通过会话管理临时权限，或权限表中增加临时权限字段，到期自动取消。
数据库性能优化，比如大量项目数据时的查询优化？
回答要点：为常用查询字段添加索引，分页查询减少数据量，或使用Redis缓存常用项目信息。
经费审批流程如何通过数据库事务管理？
回答要点：通过事务管理审批步骤，确保审批记录和经费状态变更原子性，避免审批流程中断。

7) 【常见坑/雷区】

单表存储所有字段导致数据冗余，影响性能和更新效率（应拆分表，如经费表单独存储）。
权限控制只考虑角色而忽略动态权限，无法处理临时授权场景（需补充临时权限管理）。
事务处理不完整，导致数据不一致（如部分更新成功部分失败，应确保事务完整性）。
外键约束缺失，导致跨表关联数据不一致（如项目表和经费表关联失败，需严格外键约束）。
未考虑数据扩展性，比如新增字段时需要修改表结构，影响系统稳定性（应预留扩展字段或使用灵活的存储方案）。