数据库作业

第一章

1.6 什么是数据模型？数据模型的基本要素有哪些？为什么需要数据模型？

（1）. 数据模型定义 数据模型是对现实世界数据特征的抽象，用来描述数据、组织数据和对数据进行操作，是数据库中用来对现实世界进行抽象的工具，是数据库系统的核心和基础。 （2）. 三大基本要素 数据结构：描述数据库的组成对象以及对象之间的联系，是对系统静态特性的描述。 数据操作：指对数据库中各种对象允许执行的操作集合，包含查询、增删改，是系统动态特性描述。 完整性约束条件：一组完整性规则，限定数据及数据间联系，保证数据正确、有效、相容。 （3）. 需要数据模型的原因 现实世界信息复杂，计算机无法直接识别，数据模型完成现实世界→信息世界→机器世界的逐级抽象转换； 统一规范数据的组织、存储与操作方式，方便用户、开发人员、计算机理解数据； 作为数据库设计依据，支撑数据库存储、查询、维护等全部管理功能； 屏蔽底层硬件存储细节，实现数据独立，降低数据管理复杂度。

1.7 为什么数据模型分为概念、逻辑、物理三类？分别解释三类模型

一、划分原因 现实世界到计算机存储需要多层抽象，不同阶段面向不同使用者、解决不同问题： 概念层面向业务人员，描述业务逻辑； 逻辑层面向开发人员，描述数据库逻辑结构； 物理层面向 DBA / 底层存储，描述磁盘存储细节； 分层拆分降低建模复杂度，实现数据独立性，各司其职互不干扰。 二、三类模型解释 概念模型（信息模型） 面向现实世界、业务需求，独立于数据库与硬件，用于数据库需求分析，描述实体、属性、实体间联系，常用 E-R 图表示。不关心存储，只描述业务信息。 逻辑模型 面向数据库实现，介于信息世界与机器世界之间，包含关系、层次、网状模型。描述数据库逻辑结构（表、字段、关系、约束），是程序员设计表结构的依据，屏蔽磁盘物理细节。 物理模型 面向计算机底层硬件，描述数据在磁盘上的存储结构、存取路径、索引、分区、块大小等物理存储细节，和操作系统、存储设备强相关，供 DBA 优化性能使用。

1.10 为什么 DBMS 要对数据抽象？分为哪几级抽象？

1. 抽象的原因 屏蔽底层复杂的硬件、存储、操作系统细节，让用户不用关心数据怎么存在磁盘； 分层隔离，实现逻辑独立性和物理独立性； 区分不同用户视角：普通用户只看视图，开发看全局逻辑，管理员看物理存储； 简化数据操作，上层用户只操作抽象数据，底层存储改动不影响上层程序。
2. 三级抽象（对应三级模式） 物理级抽象（内模式）：数据物理存储结构； 逻辑级抽象（模式 / 概念模式）：全局逻辑数据结构； 视图级抽象（外模式）：局部用户视图。

1.11 解释数据库三级模式、两层映像；为什么需要三级模式 + 两层映像

一、三级模式 外模式（子模式 / 用户模式） 数据库用户能看见、使用的局部数据视图，是模式子集，面向应用程序，一个数据库可多个外模式。 模式（概念模式） 数据库全局逻辑结构，描述所有实体、属性、关系、约束，唯一，是全体数据逻辑视图。 内模式（存储模式） 数据库物理存储描述，记录存储结构、索引、磁盘块分配，唯一，面向底层存储。 二、两层映像 外模式 / 模式映像 定义外模式与模式之间对应关系，每个外模式对应一个该映像。 作用：保证逻辑独立性—— 模式全局逻辑修改，外模式无需改动，应用程序不受影响。 模式 / 内模式映像 唯一，定义全局逻辑结构与物理存储结构对应关系。 作用：保证物理独立性—— 物理存储结构调整，模式不用修改，上层应用不受影响。 三、为什么需要三级模式两层映像 核心目标是实现数据独立性： 三层模式划分多用户视角，隔离普通用户、开发、管理员，权限与视图分离，简化使用； 两层映像作为中间映射，解耦上层逻辑和底层物理存储； 物理存储优化、全局表结构调整时，不用修改上层业务程序，大幅降低维护成本； 数据安全隔离：不同用户仅能访问自身外模式，实现访问控制。

1.12 三级模式与三层数据模型的联系与区别

一、联系 二者都是数据分层抽象思想，一一对应： 概念模型 ↔ 模式（概念模式）； 逻辑模型 ↔ 模式、外模式（全局 + 局部逻辑）； 物理模型 ↔ 内模式； 都服务数据库设计全流程：需求阶段用概念模型，设计库表用逻辑模型，存储优化用物理模型；三级模式是 DBMS 运行时的数据分层视图。 二、区别 定义角度不同 三层数据模型：数据库设计阶段的建模工具，是建模方法； 三级模式：DBMS 运行阶段的数据体系结构，是数据库系统内置的分层架构。 使用对象不同 概念模型：业务分析师、需求人员；逻辑模型：开发工程师；物理模型：DBA； 外模式：应用程序员 / 终端用户；模式：数据库设计人员；内模式：DBA。 作用不同 三层模型：用于从现实世界逐步设计出数据库结构； 三级模式：数据库运行时，提供多视图隔离，依靠两层映像保障数据独立性。 数量规则不同 三层模型是设计流程的三个阶段，一套库对应一套概念、逻辑、物理模型； 三级模式：模式、内模式各一个，外模式可以有多个。

1.14 DBMS 的主要组成部分、主要功能

一、DBMS 主要组成部分 数据定义语言（DDL）及其编译程序：定义库、表、视图、索引； 数据操纵语言（DML）及其编译 / 解释程序：实现增删改查； 数据库运行控制管理模块：并发控制、事务管理、权限安全、完整性检查； 数据库组织、存储、管理模块：缓冲区、文件、索引管理； 数据库建立与维护程序：备份、恢复、导入导出、性能监控； 数据通信接口：支持应用程序、网络多端访问数据库。 二、DBMS 核心功能 数据定义：使用 DDL 定义数据库、表、视图、约束、索引； 数据操纵：提供 DML 实现查询、插入、更新、删除数据； 数据库运行管理（核心） 事务管理与并发控制； 数据完整性约束校验； 安全访问控制、用户权限管理； 故障恢复； 数据存储与组织管理：管理磁盘文件、内存缓冲区、索引，优化存取效率； 数据库维护：备份恢复、数据导入导出、性能分析、重组优化； 数据交互接口：对接应用程序、编程语言、网络客户端。

第二章

2.1

(1) 域、笛卡尔积、关系、元组和属性 定义 域：一组具有相同数据类型的值的集合，是属性的取值范围。 笛卡尔积：给定若干域，将各域取值进行全部组合生成的元组集合。 关系：笛卡尔积中取有限子集，形成一张二维表。 元组：关系中的一行，对应笛卡尔积里一条取值组合。 属性：关系中的一列，每一列对应一个域，列名即为属性名。 联系与区别 联系：域是基础；域构造笛卡尔积；笛卡尔积筛选后得到关系；关系由元组（行）、属性（列）构成。 区别：域是取值范围；笛卡尔积是所有可能组合；关系是有效二维数据表；元组代表单条记录；属性代表记录的字段。 (2) 关系模式、关系数据库模式、关系数据库 定义 关系模式：对单个关系（单张表）的结构描述，格式 R(U,D,DOM,F) ，定义属性、数据类型、约束。 关系数据库模式：一个数据库内所有关系模式的集合，是整套数据表的结构定义。 关系数据库：某一时刻，关系数据库模式对应的所有关系实例（表里真实存储的数据）。 联系与区别 联系：关系模式是单表结构模板；数据库模式是全部表模板集合；关系数据库是模板对应的实际数据。 区别：模式是静态结构定义；数据库是动态数据内容；关系模式仅描述一张表，数据库模式描述整套库表。 (3) 超码、候选码、主码、外码 定义 超码：可以唯一标识关系内每一条元组的属性集合，允许包含多余属性。 候选码：最小超码，去掉集合内任意一个属性，就无法唯一标识元组。 主码：人为从候选码中选定、作为表唯一标识的候选码，一张表仅一个主码。 外码：本表的一组属性，取值匹配另一张表的主码，用于建立两张表之间关联。 联系与区别 联系：候选码一定属于超码，主码从候选码中选出；外码依靠其他表主码实现表间关联。 区别：超码可存在冗余属性；候选码无冗余；主码是选定的唯一主键；外码不唯一标识本表元组，用于表关联。 (4) 为什么需要空值 NULL 字段对应的值未知、暂时未录入； 该属性不适用于当前元组（如未选课学生无成绩）； 区分 “数值为 0 / 无” 和 “信息未知”，保证数据语义准确。

2.3 关系模型的数据完整性约束有哪些

实体完整性：主码全部属性不能取空值，主码值唯一，保证每条记录可区分。 参照完整性：外码取值要么为空，要么等于被参照表主码已存在的值，保证表间关联合法。 用户定义完整性：用户根据业务自定义约束规则，如成绩区间 0~100、性别仅允许男 / 女。

2.4 关系代数的主要操作有哪些

1. 传统集合运算（两关系结构完全一致） 并运算∪、差运算−、交运算∩、笛卡尔积×
2. 专门关系运算（面向二维表） 选择σ（筛选行）、投影Π （筛选列）、连接⋈、除÷
3. 拓展连接运算 等值连接、自然连接、左外连接、右外连接、全外连接

2.6 等值连接与自然连接的区别与联系

联系 二者都属于内连接，通过属性相等条件匹配两张表元组； 运算底层都是先做笛卡尔积，再筛选满足相等条件的元组。 区别 匹配条件：等值连接可任意两属性相等，属性名可不同；自然连接仅匹配同名、同类型属性。 重复列处理：等值连接保留两张表全部字段，匹配属性重复出现；自然连接自动删除重复同名字段，仅保留一列。 书写形式：等值连接必须显式书写相等判断条件；自然连接无需写条件，自动匹配同名列。

2.8

基础数据表结构 Student(studentNo,studentName,birthday,nation,className,gender) Score(studentNo,courseNo,score) Course(courseNo,courseName,teacherNo,year,term,credit,priorCourse) （1）查找籍贯为 “上海” 的全体学生 σ(birthday=’ 上海 ‘)(Student) （2）查找 2005 年元旦以后出生的全体男同学 σ(birthday>‘2005-01-01’ ∧ gender=’ 男 ‘)(Student) （3）查找信息学院非汉族同学的学号、姓名、性别及民族 Π(studentNo,studentName,gender,nation)(σ(nation≠’ 汉 ’ ∧ className like ’ 信息学院 %’)(Student)) （4）查找 2022—2023 学年第二学期 (22232) 开设课程的课程号、课程名和学分 Π(courseNo,courseName,credit)(σ(year=‘2022-2023’ ∧ term=‘22232’)(Course)) （5）查找选修了 “操作系统” 的学生学号、成绩及姓名 Π(studentNo,studentName,score)(Student ⋈ Score ⋈ σ(courseName=’ 操作系统 ‘)(Course)) （6）查找班级名称为 “会计学 21 (3) 班” 的学生在 2021—2022 学年第一学期 (21221) 选课情况，显示学生姓名、课程号、课程名和成绩 Π(studentName,Course.courseNo,courseName,score)(σ(className=’ 会计学 21 (3) 班 ‘)(Student) ⋈ Score ⋈ σ(year=‘2021-2022’ ∧ term=‘21221’)(Course)) （7）查找至少选修了一门其直接先修课号为 CS012 的课程的学生学号和姓名 Π(studentNo,studentName)(Student ⋈ Score ⋈ Π(courseNo)(σ(priorCourse=‘CS012’)(Course))) （8）查找选修了 2022—2023 学年第一学期 (22231) 开设的全部课程的学生学号和姓名 Π(studentNo,studentName)( (Π(studentNo,courseNo)(Score) ÷ Π(courseNo)(σ(year=‘2022-2023’ ∧ term=‘22231’)(Course))) ⋈ Student ) （9）查找至少选修了学号为 2103010 的学生所选课程的学生学号和姓名 Π(studentNo,studentName)( (Π(studentNo,courseNo)(Score) ÷ Π(courseNo)(σ(studentNo=‘2103010’)(Score))) ⋈ Student )

2.9

新增数据表 Teacher (teacherNo,teacherName) 补充 Course 字段：classNo,time,location （1）查找 2022 级蒙古族学生信息：学号、姓名、性别、班级 Π(studentNo,studentName,gender,className)(σ(nation=’ 蒙古族 ’ ∧ className like ‘2022 级 %’)(Student)) （2）查找 “C 语言程序设计” 课程的教学班号、上课时间、上课地点 Π(classNo,time,location)(σ(courseName=‘C 语言程序设计 ‘)(Course)) （3）查找以 “计算机概论” 为先修课的课程号、课程名、课程学分 Π(courseNo,courseName,credit)(σ(priorCourse=’ 计算机概论 ‘)(Course)) （4）查找李勇老师 2022—2023 学年第二学期 (22232) 开设课程号、课程名、学分 Π(courseNo,courseName,credit)(σ(teacherName=’ 李勇 ’ ∧ year=‘2022-2023’ ∧ term=‘22232’)(Teacher ⋈ Course)) （5）查找信息学院学生选课情况：学生姓名、课程号、课程名、教学班号、成绩、任课教师姓名 Π(studentName,Course.courseNo,courseName,classNo,score,teacherName)(σ(className like ’ 信息学院 %’)(Student) ⋈ Score ⋈ Course ⋈ Teacher)

第三章

3.1 查询 1991 年出生的读者的姓名、工作单位和身份证号

SELECT readerName, workUnit, identitycard FROM Reader WHERE YEAR(identitycard) = 1991;

3.2 查询图书名称中含有 “数据库” 的图书的详细信息

SELECT * FROM Book WHERE bookName LIKE ‘%数据库%’;

3.3 查询 2019—2020 年入库的图书编号、出版时间、入库时间和图书名称，并按入库时间的降序排列输出

SELECT bookNo, publishingDate, shopDate, bookName FROM Book WHERE YEAR(shopDate) BETWEEN 2019 AND 2020 ORDER BY shopDate DESC;

3.4 查询读者喻自强借阅的图书编号、图书名称、借阅日期和归还日期

SELECT b.bookNo, b.bookName, br.borrowDate, br.returnDate FROM Reader r JOIN Borrow br ON r.readerNo = br.readerNo JOIN Book b ON br.bookNo = b.bookNo WHERE r.readerName = ‘喻自强’;

3.5 查询借阅了清华大学出版社出版的图书的读者编号、姓名、图书名称、借阅日期和归还日期

SELECT r.readerNo, r.readerName, b.bookName, br.borrowDate, br.returnDate FROM Reader r JOIN Borrow br ON r.readerNo = br.readerNo JOIN Book b ON br.bookNo = b.bookNo JOIN Publisher p ON b.publisherNo = p.publisherNo WHERE p.publisherName = ‘清华大学出版社’;

第四章

4.1 术语简要解释

实体：现实世界中客观存在、可以相互区分的事物，例如职工、商品、客户。 实体集：同一类型实体的集合，全体职工构成职工实体集。 属性：实体具有的特征，职工的职工号、姓名都是属性。 域：属性的取值范围，如性别域只能取 “男、女”。 联系：实体之间的关联关系，如客户购买商品。 联系集：同类联系的全部集合，所有 “客户 - 购买 - 商品” 构成购买联系集。 多联系：三元及以上实体间的联系，供应商、商品、仓库三者的供应入库属于多联系。 角色：同一实体在联系中承担的不同身份，如职工既是员工又是部门管理者，管理者是角色。 映射基数：联系两端实体的数量对应关系，分为一对一 (1:1)、一对多 (1:N)、多对多 (M:N)。 超码：能唯一标识实体集中每个实体的属性集，可包含多余属性。 候选码：最小超码，去掉任意一个属性就无法唯一标识实体。 主码：人为选定的一个候选码，作为实体唯一标识。 多值联系：同一对实体之间存在多条联系记录，同一个商品多次存入同一仓库。 多值联系集：由全部多值联系构成的集合。 依赖约束：弱实体必须依赖强实体才能存在，弱实体主码包含所依赖强实体主码。 参与约束：分为全部参与、部分参与；全部参与表示实体集中每个实体都必须参与该联系，部分参与表示可有可无。 弱实体集：自身无独立候选码，必须依赖另一个强实体集才能唯一标识的实体，如贷款明细。 类层次：存在继承关系的实体集层次，如人员分为职工、客户，职工又分为医生、销售员。 聚合：将一个联系整体当作一个实体，再和其他实体建立新联系。

4.4 销售公司数据库设计

(1) 实体集及属性 ① 职工（职工实体集，强实体） 属性：职工号（主码）、姓名、性别、电话、住址 ② 供货商（供货商实体集，强实体） 属性：制造商编号（主码）、制造商名称、联系电话、通信地址 ③ 商品（商品实体集，强实体） 属性：商品编号（主码）、商品名称、型号、计量单位、进货单价、库存数量、销售单价 ④ 客户（客户实体集，强实体） 属性：客户编号（主码）、客户名称、联系电话、通信地址 ⑤ 供应（M:N 联系转弱实体：供货记录） 属性：制造商编号、商品编号、供货单价；联合主码 (制造商编号，商品编号) ⑥ 购买（M:N 联系转弱实体：订单） 属性：客户编号、商品编号、购买数量、成交单价；联合主码 (客户编号，商品编号) (2) E-R 模型说明（映射基数） 供货商 — 供应 — 商品 映射基数：供货商 (1,N)，商品 (M,1)，多对多 M:N；联系属性：供货单价。 业务：一个供货商供应多种商品，一种商品可由多个供货商供货。 客户 — 购买 — 商品 映射基数：客户 (1,N)，商品 (M,1)，多对多 M:N；联系属性：购买数量、成交单价。 业务：一个客户购买多种商品，一种商品销售给多个客户。 实体：职工（仅基础信息，不参与核心购销联系）。 (3) 转换为关系模式（标注主码 PK、外码 FK） 职工 (职工号，姓名，性别，电话，住址) PK：职工号 供货商 (制造商编号，制造商名称，联系电话，通信地址) PK：制造商编号 商品 (商品编号，商品名称，型号，计量单位，进货单价，库存数量，销售单价) PK：商品编号 供货记录 (制造商编号，商品编号，供货单价) PK：(制造商编号，商品编号) FK：制造商编号 → 供货商 (制造商编号) FK：商品编号 → 商品 (商品编号) 订单 (客户编号，商品编号，购买数量，成交单价) PK：(客户编号，商品编号) FK：客户编号 → 客户 (客户编号) FK：商品编号 → 商品 (商品编号) 客户 (客户编号，客户名称，联系电话，通信地址) PK：客户编号

4.8 医院门诊开方、缴费开票数据库设计

(1) 实体集及属性 ① 职工（医生 / 开票人员，强实体） 属性：职工号 (PK)、姓名、性别、电话、住址 ② 药品（强实体） 属性：药品号 (PK)、药品名、计量单位、进货单价、库存数量、销售单价 ③ 病人（强实体） 属性：病人号 (PK)、姓名、性别、出生日期、联系电话 ④ 处方（强实体，医生开具） 属性：处方号 (PK)、开具日期、处方金额、职工号 (开具医生 FK)、病人号 (FK) ⑤ 处方明细（弱实体，依赖处方） 属性：处方号 (FK)、药品号 (FK)、药品数量、单价、单项金额、每日用药次数 PK：(处方号，药品号) ⑥ 发票（缴费票据，强实体） 属性：发票号 (PK)、开票日期、业务摘要、发票金额、职工号 (开票职工 FK) ⑦ 缴费明细（弱实体，关联处方与发票） 属性：发票号 (FK)、处方号 (FK)、本次缴费金额 PK：(发票号，处方号) (2) 局部 E-R 模型 映射基数与联系 职工 — 开具 — 处方 一对多 (1:N)：一名医生可开多张处方，一张处方仅由一名医生开具；全部参与约束。 病人 — 持有 — 处方 一对多 (1:N)：一个病人有多张处方，一张处方仅属于一位病人；全部参与。 处方 — 包含 — 药品（处方明细） 多对多 M:N，转化弱实体处方明细；联系属性：数量、单价、金额、用药次数。 职工 — 开具 — 发票 一对多 (1:N)：一名开票职工开多张发票，一张发票仅一个开票人。 处方 — 缴费生成 — 发票（缴费明细） 多对多 M:N，转化弱实体缴费明细；业务：一张处方可分多次缴费（多张发票），一张发票可包含多个处方缴费记录。 (3) 转换关系模式（PK 主码，FK 外码） 职工 (职工号，姓名，性别，电话，住址) PK：职工号 药品 (药品号，药品名，计量单位，进货单价，库存数量，销售单价) PK：药品号 病人 (病人号，姓名，性别，出生日期，联系电话) PK：病人号 处方 (处方号，开具日期，处方金额，职工号，病人号) PK：处方号 FK：职工号 → 职工 (职工号) FK：病人号 → 病人 (病人号) 处方明细 (处方号，药品号，药品数量，单价，单项金额，每日用药次数) PK：(处方号，药品号) FK：处方号 → 处方 (处方号) FK：药品号 → 药品 (药品号) 发票 (发票号，开票日期，业务摘要，发票金额，职工号) PK：发票号 FK：职工号 → 职工 (职工号) 缴费明细 (发票号，处方号，本次缴费金额) PK：(发票号，处方号) FK：发票号 → 发票 (发票号) FK：处方号 → 处方 (处方号)

第五章

5.1 数据冗余引发的问题 + 异常实例

1. 数据冗余的危害 同一数据在多条元组中重复存储，浪费存储空间，还会引发三类操作异常。
2. 三类异常实例（以学生选课表：S (学号，姓名，系名，课程号，成绩) 为例） 插入异常 新增一个还未选课的新生，因无课程号，主码 (学号，课程号) 为空，无法插入该学生基础信息。 删除异常 某系最后一名学生退学，删除该生选课记录时，连带把该系的系名信息全部删除，丢失系数据。 更新异常 某学生转系，若该生选了 3 门课，需要修改 3 条记录的系名字段；若漏改某一条，会出现同一学生对应两个系名的数据不一致。

5.2 术语完整解释

函数依赖 设R(U)为属性集U上的关系模式，X,Y⊆U。若对于R中任意两条元组，X上属性值相等则Y上属性值必然相等，称X→Y，即X函数决定Y。 平凡 / 非平凡函数依赖 平凡：X→Y且Y⊆X（如AB→A），必然成立，无业务意义； 非平凡：X→Y且Y⊈X（如学号→姓名），具备实际业务语义。 完全函数依赖、部分函数依赖 设X→Y，X′是X的任意真子集： 完全：任意X′都不满足X′→Y，必须X全部属性才能决定Y； 部分：存在某一个X′满足X′→Y，仅X部分属性即可决定Y。 传递函数依赖 满足X→Y、Y↛X、Y→Z，则X传递决定Z，记传递。 函数依赖集闭包F+ 由函数依赖集F，通过自反、增广、传递三条公理推导出的全部函数依赖的集合。 属性集闭包XF+ 给定属性集X，由F能推导出的所有属性构成的集合。 无损连接分解 将R分解为多个子模式，对R任意实例，分解后子模式自然连接可以还原出原关系，不会产生多余虚假元组。 保持依赖分解 分解后所有子模式上的函数依赖投影合并，等价于原F，所有约束不会丢失。 1NF 关系中每个属性的值都是不可再分的原子值，不允许嵌套表、多值单元格。 2NF 满足 1NF，消除所有非主属性对候选码的部分函数依赖。 3NF 满足 2NF，消除所有非主属性对候选码的传递函数依赖；即不存在X→Y,Y→Z，Z是非主属性。 BCNF（巴斯 - 科德范式） 满足 3NF，消除主属性对候选码的部分 / 传递依赖；任意非平凡函数依赖X→Y，X一定是超码。

5.3 术语解释：无关属性、左无关、右无关、正则覆盖

无关属性 在函数依赖X→Y中，X或Y内可删除、删除后不改变F+的属性。 左无关属性 X→Y中，属性A∈X，满足(X−{A})F+​⊇Y，去掉A后依赖依然成立，A是左无关属性。 右无关属性 X→Y中，属性A∈Y，满足XF+​⊇(Y−{A})，去掉A后依赖依然成立，A是右无关属性。 正则覆盖Fc 满足两个条件的最简依赖集： ① 任意依赖无左、右无关属性； ② 不存在两条完全相同的函数依赖。

5.4

图 5-19 关系实例，求所有非平凡最简函数依赖 实例元组： 表格

A

B

C

D

E

1

2

3

4

5

1

4

3

4

5

1

2

4

4

1

2

4

5

5

2 最简非平凡函数依赖： A→D：A 相同则 D 一定相同 AC→B：A、C 共同确定 B AC→E：A、C 共同确定 E D无其他决定，B、C、E 无单独决定关系

5.7

R(A,B,C,D,E),F={A→BC, CD→E, B→D, E→A} (1) 求A+、B+ A+： A→BC → 得 B、C；B→D → 得 D；CD→E → 得 E A+={A,B,C,D,E} B+： B→D；无其他推导，B+={B,D} (2) 求全部候选码 逐个计算属性闭包： A+=ABCDE → A 是候选码 E+：E→A→BC→D → E+=ABCDE → E 是候选码 CD+：CD→E→A→BC → CD+=ABCDE → CD 是候选码 候选码：、、

5.8

证明分解R1(ABC),R2(ADE)是无损连接分解 无损连接判定定理： 分解R1(U1),R2(U2)无损的充要条件：U1∩U2→U1​−U2 或 U1∩U2→U2​−U1 交集：U1∩U2={A} U2​−U1={D,E} 由F：A+=ABCDE，A→DE，满足A→U2​−U1 因此该分解为无损连接分解。

5.10 R(A,B,C,D)，三组依赖分别求解

(1) F1={C→D, C→A, B→C} ① 候选码：B B→C→D,A，B+=ABCD ② 范式判断： B→C，、，存在非主属性传递依赖，最高2NF，不满足 3NF ③ BCNF 分解： R1(B,C), R2(C,A), R3(C,D) (2) F2={ABC→D, D→A} ① 候选码：ABC ABC+=ABCD；无其他属性闭包覆盖全集 ② 范式判断： 存在主属性 A 依赖非超码 D，仅1NF ③ BCNF 分解： R1(D,A), R2(B,C,D) (3) F3={A→B, BC→D} ① 候选码：AC AC→B→D，AC+=ABCD ② 范式判断： A→B，B 是主属性，但无部分 / 传递非主属性依赖，满足3NF；不满足 BCNF（A→B左部 A 不是超码） ③ BCNF 分解： R1(A,B), R2(A,C,D)

5.11 R(A,B,C,D,E,G)，两组依赖求解

(1) F1={A→BDE, B→AE, AC→G, BC→AD} ① 候选码：AC AC→G；A→BDE，AC+=ABCDEG ② 3NF 判断： 所有依赖左部均包含候选码 / 候选码子集，无传递、部分非主属性依赖，满足 3NF，无需分解。 (2) F2={A→CDG, G→A, AE→C, EG→BD} ① 候选码：、 AE+：AE→C，A→CDG，G→A，EG→BD → 全集 EG+：EG→BD，G→A→CD → 全集 ② 3NF 判断：无传递 / 部分非主属性依赖，满足 3NF，无需分解。

第七章

7.2 BookDB 图书管理数据库 SQL

(1) 将 “经济类” 图书的单价提高 10% UPDATE Book b JOIN BookClass c ON b.classNo = c.classNo SET b.price = b.price * 1.10 WHERE c.className = ‘经济类’; (2) 将入库数量最多的图书单价下调 5% UPDATE Book SET price = price * 0.95 WHERE shopNum = (SELECT MAX(shopNum) FROM Book); (3) 删除读者 “张小娟” 的借书记录 DELETE br FROM Borrow br JOIN Reader r ON br.readerNo = r.readerNo WHERE r.readerName = ‘张小娟’; (4) 创建视图 v_reader_60：在借图书总价 60 元以上读者（未归还 = returnDate IS NULL） CREATE VIEW v_reader_60 AS SELECT r.readerNo, r.readerName, SUM(b.price) AS totalPrice FROM Reader r JOIN Borrow br ON r.readerNo = br.readerNo JOIN Book b ON br.bookNo = b.bookNo WHERE br.returnDate IS NULL GROUP BY r.readerNo, r.readerName HAVING SUM(b.price) > 60; (5) 创建视图 v_reader_25_35：年龄 25~35 岁读者借书信息 sql CREATE VIEW v_reader_25_35 AS SELECT r.readerNo, r.readerName, TIMESTAMPDIFF(YEAR,STR_TO_DATE(SUBSTRING(r.identitycard,7,8),’%Y%m%d’),CURDATE()) AS age, r.workUnit, b.bookName, br.borrowDate FROM Reader r JOIN Borrow br ON r.readerNo = br.readerNo JOIN Book b ON br.bookNo = b.bookNo WHERE TIMESTAMPDIFF(YEAR,STR_TO_DATE(SUBSTRING(r.identitycard,7,8),’%Y%m%d’),CURDATE()) BETWEEN 25 AND 35; (6) 创建视图 v_tsinghua_computer：清华出版社 2019-2020 计算机类图书 sql CREATE VIEW v_tsinghua_computer AS SELECT b.* FROM Book b JOIN Publisher p ON b.publisherNo = p.publisherNo JOIN BookClass c ON b.classNo = c.classNo WHERE p.publisherName = ‘清华大学出版社’ AND c.className = ‘计算机类’ AND YEAR(b.publishingDate) BETWEEN 2019 AND 2020; (8) 在 Reader 表按 workUnit 建立索引 readerUnitIdx CREATE INDEX readerUnitIdx ON Reader(workUnit);

7.3 ScoreDB 学生成绩数据库存储过程

(1) 输入课程号，返回选课人数、平均分 DELIMITER // CREATE PROCEDURE proc_course_stat(IN in_courseNo CHAR(10), OUT out_count INT, OUT out_avg DECIMAL(5,2)) BEGIN SELECT COUNT(DISTINCT studentNo), AVG(score) INTO out_count, out_avg FROM Score WHERE courseNo = in_courseNo; END // DELIMITER ; – 调用示例 CALL proc_course_stat(‘C001’, @cnt, @avg); SELECT @cnt AS 选课人数, @avg AS 平均分; (2) 删除重复选课，仅保留每学生每门课最高分记录 思路：分组取最高分，删除分数低于最高分的重复行 DELIMITER // CREATE PROCEDURE proc_del_dup_score() BEGIN DELETE s1 FROM Score s1 JOIN Score s2 WHERE s1.studentNo = s2.studentNo AND s1.courseNo = s2.courseNo AND s1.score < s2.score; END // DELIMITER ; – 调用 CALL proc_del_dup_score(); (3) 无聚合函数，统计各学院选课人数、平均分，按学院升序输出 思路：使用变量累加计数、总分，替代 COUNT/AVG DELIMITER // CREATE PROCEDURE proc_class_stat_no_agg() BEGIN DECLARE cur_class VARCHAR(50); DECLARE cur_stu CHAR(10); DECLARE cur_sc DECIMAL(5,2); DECLARE done INT DEFAULT 0; DECLARE class_cur CURSOR FOR SELECT DISTINCT className FROM Student ORDER BY className; DECLARE stu_cur CURSOR FOR SELECT s.studentNo, sc.score FROM Student s JOIN Score sc ON s.studentNo=sc.studentNo WHERE s.className = cur_class; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

OPEN class_cur;
class_loop: LOOP
    FETCH class_cur INTO cur_class;
    IF done=1 THEN LEAVE class_loop; END IF;

    SET @stu_cnt = 0;
    SET @sum_score = 0;
    SET done = 0;
    OPEN stu_cur;
    stu_loop: LOOP
        FETCH stu_cur INTO cur_stu, cur_sc;
        IF done=1 THEN LEAVE stu_loop; END IF;
        SET @stu_cnt = @stu_cnt + 1;
        SET @sum_score = @sum_score + cur_sc;
    END LOOP stu_loop;
    CLOSE stu_cur;

    SELECT cur_class AS 学院名称, @stu_cnt AS 选课人数, ROUND(@sum_score/@stu_cnt,2) AS 平均分;
END LOOP class_loop;
CLOSE class_cur;

END // DELIMITER ; – 调用 CALL proc_class_stat_no_agg(); (4) 无聚合函数，按格式逐门课程输出学生明细、选课人数、平均分 DELIMITER // CREATE PROCEDURE proc_course_print_no_agg() BEGIN DECLARE cur_cno CHAR(10); DECLARE cur_cname VARCHAR(40); DECLARE cur_stu CHAR(10); DECLARE cur_sname VARCHAR(20); DECLARE cur_sc DECIMAL(5,2); DECLARE done INT DEFAULT 0; DECLARE course_cur CURSOR FOR SELECT DISTINCT c.courseNo, c.courseName FROM Course c JOIN Score sc ON c.courseNo=sc.courseNo; DECLARE stu_cur CURSOR FOR SELECT s.studentNo, s.studentName, sc.score FROM Student s JOIN Score sc ON s.studentNo=sc.studentNo WHERE sc.courseNo = cur_cno; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

OPEN course_cur;
course_loop: LOOP
    FETCH course_cur INTO cur_cno, cur_cname;
    IF done=1 THEN LEAVE course_loop; END IF;

    -- 打印课程标题
    SELECT CONCAT('课程名 ', cur_cname) AS output;
    SELECT '学号        姓名        成绩' AS output;

    SET @stu_cnt = 0;
    SET @sum_sc = 0;
    SET done = 0;
    OPEN stu_cur;
    stu_loop: LOOP
        FETCH stu_cur INTO cur_stu, cur_sname, cur_sc;
        IF done=1 THEN LEAVE stu_loop; END IF;
        SET @stu_cnt = @stu_cnt + 1;
        SET @sum_sc = @sum_sc + cur_sc;
        SELECT CONCAT(cur_stu, '    ', cur_sname, '    ', cur_sc) AS output;
    END LOOP stu_loop;
    CLOSE stu_cur;

    -- 统计汇总
    SELECT CONCAT('选课人数：', @stu_cnt) AS output;
    SELECT CONCAT('平均分：', ROUND(@sum_sc/@stu_cnt,2)) AS output;
    SELECT '----------------------------------------' AS output;
END LOOP course_loop;
CLOSE course_cur;

END // DELIMITER ; – 调用 CALL proc_course_print_no_agg();

第八章

8.10

1. 顺序索引、散列索引、主索引、辅助索引、稠密索引、稀疏索引定义 顺序索引：基于有序排序的搜索码建立的索引，记录按搜索码有序存储，支持范围查询。 散列索引：利用散列函数将搜索码映射到存储桶，直接定位存储位置，适合等值查询，不擅长范围查询。 主索引：建立在有序主码上的索引，索引项与数据块一一对应，数据文件按搜索码物理有序。 辅助索引（二级索引）：建立在非排序字段上的索引，搜索码无序，每条索引项指向对应记录。 稠密索引：数据文件中每一条记录都对应一条索引项，每条记录都有索引条目。 稀疏索引：仅为每个数据块建立一条索引项，一个块只存一条索引，索引数量更少。
2. 稠密索引与稀疏索引区别 索引条目数量：稠密索引每条记录一条索引；稀疏索引每个数据块一条索引。 查找效率：稠密索引可直接定位记录，查找更快；稀疏索引找到块后需在块内顺序查找。 存储开销：稠密索引占用存储空间更大；稀疏索引空间开销小。 更新代价：稠密索引插入 / 删除需维护大量索引项；稀疏索引修改索引操作更少。

8.11 为什么需要多级索引？多级索引结构

1. 多级索引产生原因 当数据量巨大时，单层稀疏索引自身文件也会超出内存容量，无法一次性载入内存检索；将索引本身再分块、建立上层索引，形成多层结构，每次仅加载少量索引块到内存，减少磁盘 I/O，提升查询速度。
2. 多级索引整体结构 底层（叶级索引）：一级稀疏索引，对应原始数据块，索引项为「搜索码值 + 数据块地址」； 上层（非叶级）：多层稀疏索引，每一层为下一层索引块建立索引； 顶层（根节点）：最高一级索引，仅有一个索引块，检索入口。 检索时从根逐层向下，直到叶级索引，再访问数据块。

8.12 什么是 B⁺树索引？B⁺树索引优缺点

B⁺树索引定义 B⁺树是一种平衡多路搜索树，作为数据库主流多级顺序索引；所有记录数据指针仅存于叶节点，非叶节点只存索引键值与子块指针，天然实现多级稀疏索引，数据有序，支持等值、范围查询。 2. 优点 整棵树严格平衡，任意查询磁盘 I/O 次数稳定，性能波动小； 叶节点通过双向链表串联，高效支持区间、排序、全表扫描； 插入、删除仅局部调整节点分裂 / 合并，平衡维护代价低； 支持等值、范围、排序、模糊前缀多种查询。 3. 缺点 树节点存在空闲空间，有一定存储冗余； 等值单点查询性能弱于散列索引，散列仅一次 I/O，B⁺树需遍历树高多层节点； 频繁随机更新会触发节点分裂，带来少量磁盘写开销。

8.13 B⁺树根、非叶、叶节点结构相同，区别是什么？

1. 结构共性 三类节点存储结构格式一致：存储多个（键值，指针）二元组，节点有最大键值容量上限。
2. 核心区别 存储指针类型不同 非叶节点（含根节点）：指针全部指向子索引块，无原始数据记录指针；仅用于索引跳转。 叶节点：指针分为两类：①下一个叶节点的链表指针；②指向磁盘原始数据记录 / 数据块的指针。 键值作用不同 非叶节点键值：仅作为分界值，划分左右子树检索区间，不对应真实记录； 叶节点键值：完整包含所有搜索码，每条键值对应真实数据记录。 链表连接 只有叶节点带有双向链表指针，实现有序遍历；根、非叶节点无链表。 数据存储位置 全部真实记录地址仅保存在叶节点；根与非叶节点只存导航索引，不存储记录指针。

8.14 如何利用 B⁺树索引查找？B⁺树文件组织与 B⁺树索引区别

一、B⁺树索引查找步骤 等值查找 ① 从根节点进入，用待查键与节点内分界键对比，选择匹配区间的子节点指针； ② 逐层向下遍历非叶节点，直到抵达叶节点； ③ 在叶节点内顺序查找目标键，通过记录指针读取磁盘数据。 范围查找（区间查询） ① 先按等值查找找到区间下界对应的叶节点； ② 利用叶节点双向链表，向后遍历所有满足区间条件的叶节点，读取全部符合条件记录。 二、B⁺树文件组织 和 B⁺树索引区别 定义层级不同 B⁺树索引：仅索引结构，独立于数据文件，只存储键与指针，不存储原始数据；数据文件可堆文件、顺序文件。 B⁺树文件组织：完整存储方案，数据记录全部存放于 B⁺树叶节点中，索引与数据合为一体，整个文件就是一棵 B⁺树。 数据存放位置 B⁺树索引：原始数据独立存储在数据文件，叶节点仅存记录指针； B⁺树文件组织：叶节点直接保存完整数据记录，无外部数据文件。 适用场景 B⁺树索引：作为辅助索引附加在已有数据文件上； B⁺树文件组织：作为表的主存储结构，整张表以 B⁺树形式落地磁盘。 IO 流程差异 B⁺树索引：检索完叶节点后，还需要额外一次 IO 读取外部数据块； B⁺树文件组织：找到叶节点即读取完整数据，无需额外访问数据文件。

第九章

9.1

一、数据库安全性概念 数据库安全性是指保护数据库，防止未经授权的用户非法访问、修改、删除或破坏数据库中数据，避免数据泄露、篡改、丢失，保障数据合法、可控访问的技术与管理机制。 核心目标：区分合法 / 非法用户，控制用户可执行的操作，保障数据不被恶意窃取、破坏。 二、数据库安全保护措施及实现方式

1. 用户标识与鉴别（身份认证） 作用：验证访问者身份，确认是否为合法用户，是第一道安全屏障。 实现：账号 + 密码、生物识别、动态验证码、数字证书等；登录时校验身份，不通过则拒绝连接数据库。
2. 存取控制（自主 / 强制存取控制） 1）自主存取控制 DAC（主流，SQL 标准 GRANT/REVOKE） 作用：DBA 为用户分配权限，用户可自主将自身权限转授他人；区分用户能访问哪些表、执行增删改查 / 建表等操作。 实现：通过GRANT授予权限、REVOKE回收权限，建立用户 - 权限映射表。 2）强制存取控制 MAC（高安全场景：军工、政务） 作用：给数据、用户划分安全密级（绝密 / 机密 / 公开），仅用户密级≥数据密级才能访问。 实现：系统自动对比密级，不受用户自主授权干扰。
3. 视图机制 作用：为不同用户创建定制视图，隐藏底层敏感字段、无关数据，用户仅能访问视图可见内容。 实现：CREATE VIEW筛选数据，仅授予用户视图访问权限，不开放基表权限。
4. 审计机制 作用：记录所有数据库访问、修改操作日志，事后追溯非法操作，追责溯源。 实现：开启审计功能，自动保存登录、增删改、权限变更记录，存入审计日志表。
5. 数据加密 作用：防止数据脱库、磁盘被盗后明文泄露。 实现： 存储加密：磁盘文件整体加密； 传输加密：客户端与数据库通信 SSL/TLS 加密； 字段加密：身份证、手机号等敏感字段单独加密存储。
6. 触发器安全审计 作用：对关键表的增删改操作实时拦截、记录，限制非法修改。 实现：编写触发器，操作前校验操作者身份、操作范围，违规则回滚并写入审计记录。
7. 操作系统与网络安全辅助 作用：从底层阻断非法连接，防护 Web、服务器漏洞。 实现：防火墙限制数据库端口访问、操作系统账户权限隔离、Web 应用防 SQL 注入。

9.5 数据库完整性概念 + DBMS 实现完整性约束的机制

一、数据库完整性概念 数据库完整性是指数据库中数据的正确性、有效性、相容性： 正确性：数据符合业务逻辑，如年龄不能为负数； 有效性：数据在规定取值范围内，如性别只能男 / 女； 相容性：表之间关联数据一致，外码必须引用已存在的主码。 完整性防止输入不合规、错误、矛盾的数据，区分于安全性（安全防非法访问，完整防错误数据）。 二、DBMS 实现数据完整性约束的手段

1. 四类静态完整性约束（定义表时声明，DBMS 自动校验） 实体完整性（主键约束 PRIMARY KEY） 每张表主码非空、唯一；插入 / 更新时 DBMS 自动校验主码重复、空值，违规拒绝操作。 参照完整性（外键约束 FOREIGN KEY） 外码要么为空，要么引用另一张表已存在的主码；删除 / 更新主码时可配置级联更新、级联删除、置空、拒绝操作，DBMS 自动校验关联一致性。 域完整性（列级约束） 包括数据类型、长度、非空 NOT NULL、唯一 UNIQUE、默认值 DEFAULT、检查 CHECK 约束；限定单个字段取值规则，字段写入时即时校验。 用户自定义完整性（元组 / 表级 CHECK 约束） 多字段联合约束，例如 “上海户籍学生年龄≥17”；整条记录插入 / 修改完成后，DBMS 校验多字段逻辑关系。
2. 动态完整性约束：触发器 TRIGGER 静态约束仅校验写入瞬间字段规则；触发器实现动态业务完整性： 在增 / 删 / 改操作前 / 后触发自定义逻辑； 可实现跨表联动（修改学生学号同步更新成绩表学号）、业务限额（借书数量不能超上限）、复杂多表校验； 违规时回滚事务，阻止非法数据写入。 DBMS 完整性约束统一执行流程 执行 INSERT/UPDATE/DELETE 操作； 先执行列级域约束（字段类型、非空、CHECK）； 再执行元组级表约束（多字段联合 CHECK）； 校验实体完整性（主键唯一、非空）； 校验参照完整性（外码关联合法性）； 触发对应触发器，执行复杂业务校验 / 联动更新； 任意一层校验失败，回滚整条事务，数据不写入数据库。

第十章

10.1 事务 ACID 特性及 DBMS 保障机制

一、事务四大 ACID 特性 原子性（Atomicity） 事务是不可分割的最小执行单元，事务内所有操作要么全部成功提交，要么全部回滚撤销，不存在部分执行完成的状态。 一致性（Consistency） 事务执行前后，数据库始终保持业务完整性约束的一致状态；事务执行过程可临时不一致，但提交 / 回滚后必须恢复合法一致。 隔离性（Isolation） 多个并发事务之间互相隔离，一个事务看不到其他事务未提交的中间数据，各事务感知不到彼此的并行执行。 持久性（Durability） 事务一旦成功提交，它对数据库的修改永久生效，后续系统崩溃、断电等故障不会丢失已提交的数据变更。 二、DBMS 分别如何保证四大特性 原子性：日志 + 回滚机制（Undo 日志） 事务执行前记录撤销日志，若事务中途失败，DBMS 读取 Undo 日志反向执行所有操作，撤销已完成的修改；正常提交则清空对应撤销日志，实现全部回滚或全部生效。 一致性：完整性约束 + 原子性 + 隔离性共同支撑 静态约束：主键、外键、CHECK、唯一约束自动校验； 动态约束：触发器校验业务规则； 依托原子性避免半完成事务破坏数据，依托隔离性避免并发脏数据导致不一致。 隔离性：并发控制（锁机制 / MVCC 多版本并发控制） 封锁方案：共享锁、排他锁、意向锁，读写互斥、写写互斥，限制事务访问未提交数据； MVCC：为数据生成多版本快照，读事务读取历史快照，不阻塞写事务，实现不同隔离级别（读未提交、读已提交、可重复读、串行化）。 持久性：重做日志（Redo 日志）+ 数据备份 事务提交时先把变更写入 Redo 日志再刷新磁盘数据；系统崩溃后，DBMS 扫描 Redo 日志重做已提交事务的修改；配合数据库备份、检查点机制，保障提交数据永久保存。

10.2 数据库为什么需要并发控制

1. 并发访问的业务需求 数据库是多用户共享系统，大量客户端同时读写数据库（如电商下单、学生同时查成绩、图书馆多人借书）。若强制串行排队执行，系统吞吐量极低、响应缓慢，无法支撑多用户同时访问，必须允许多事务并发执行提升性能。
2. 无并发控制会产生三类数据不一致问题 丢失更新 两个事务同时读取同一数据，各自基于初始值修改，后提交的事务覆盖先提交事务的修改，造成更新丢失。 读脏数据 事务 T1 修改数据未提交，事务 T2 读取了 T1 未提交的中间值；若 T1 回滚，T2 读取的数据就是无效脏数据。 不可重复读 同一事务 T1 内两次读取同一数据，中间 T2 修改并提交该数据，导致 T1 两次查询结果不一致。
3. 并发控制的核心作用