数据库PPT课后习题 - 第四章

PPT 课后习题

4.1.1 完整的数据库设计方案包含哪些内容？

​ 数据库应用架构、数据库内部结构、数据应用访问方式。

1. 数据库应用架构

• 单用户结构
• 集中式结构
• C/S 结构
• 分布式结构

2. 数据库内部结构

• CDM（概念数据模型）：描述业务实体、属性和它们之间的关系，独立于任何特定数据库管理系统。
• LDM（逻辑数据模型）：定义数据的逻辑结构和关系，不涉及具体的数据库实现细节。
• PDM（物理数据模型）：确定数据在特定数据库管理系统中的实现方式，包括存储结构和索引策略。

3. 数据库应用访问方式

• 基于 本地接口 连接访问
• 基于 标准接口 连接访问
• 基于 数据访问层框架 连接访问

也可以分为：需求分析、E-R模型、概念结构设计、逻辑结构设计、物理结构设计

4.1.2 数据库应用架构与数据库内部结构分别是指什么？

• 数据库应用架构：定义了数据库系统如何被应用程序使用和访问，包括：数据库和应用的交互方式、数据访问接口、传输协议等。

  ​ 关注的是数据库系统在应用程序中的角色和位置。

• 数据库内部结构：指数据库系统内部的组织和实现方式，包括：数据存储结构、索引结构、查询优化、事务管理等。

  ​ 关注的是数据库系统内部的工作原理和机制。

4.2 前置知识

4.2.1 E-R模型

• 定义：Entity-Relationship Model，“实体-联系模型”。用于设计系统 CDM，LDM 。

• 基本元素：实体、属性、标识符、联系。

  • 联系度数：一个联系关联的实体的数目。

4.2.2 实体-联系类型

• (1:1) , (1:N) , (M:N)

• 实体联系的实例数量称为 基数。上图 b) 的 1 为最小基数，N 为最大基数。

• 除了数量对应关系，有时候还需要进一步可以如下图反映 实体参与关系的必要性：用 可选 、强制 表示。

4.2.3 鸟足版本的表示

• 示例如下：

4.2.4 实体继承联系

• 用于表示实体之间的相似性关系。其中，具有公共属性的称为 父实体，具有特殊属性的称为 子实体。

• 两种维度的分法：

  • 互斥性继承联系、非互斥性继承联系：

• 完整继承、非完整继承：

• ${\bigcup }_1^n$ 子实体 = 父实体 $\leftrightarrow$ 完整继承联系。

​ PS: 注意符号怎么画。

4.2.5 强实体、弱实体

• 弱实体依赖强实体。弱实体的存在必须以强实体的存在为前提。

• 弱实体又分为：标识符依赖弱实体、非标识符依赖弱实体。

  • 如果 弱实体的标识符 包含 所依赖实体的标识符，那么该弱实体是 标识符依赖弱实体（下图左）pk + fk
  • 部分弱实体有自己独立的标识符，称为 非标识符依赖弱实体（下图右）仅 fk

4.2 习题

4.2.1 E-R模型与 CDM 、PDM之间是什么关系？

​ E-R 模型是数据库设计的一种概念模型，用于描述实体、属性、关系。通常用于数据库设计阶段，帮助设计师理解业务需求。

• 与CDM：CDM描述业务实体、属性和它们之间的关系，独立于任何特定数据库管理系统。

  ​ E-R 模型可以被视为 CDM 的一种实现，因为它提供了一种图形化的方式来表示实体、属性和它们之间的关系，有助于捕捉业务需求和数据之间的关系。

• 与PDM：定义数据的逻辑结构和关系，不涉及具体的数据库实现细节。

  ​ E-R 模型通常不涉及具体的数据库实现细节，因此与 PDM 是相对独立的。

4.2.2 针对如下班级活动管理 E-R 模型图，如何理解实体联系的多重性及强制性？如何理解强弱实体？如何理解标识符依赖实体和非标识符依赖弱实体？

• 实体联系的多重性：

  1. 班级与学生：一般来说，一个班级可以有多名学生，但一个学生只属于一个班级。这是一对多的关系（1:N）。
  2. 班级与活动发布表：一个班级可以发布多个活动，但每个活动发布表只属于一个班级。这也是一对多的关系（1:N）。
  3. 活动与活动发布表：一个活动对应多个活动发布表，但每个活动发布表只属于一个活动。这同样是一对多的关系（1:N）。
  4. 活动发布表与活动报名表：一个活动发布表可以有多个活动报名表，而一个活动报名表只属于一个活动发布表。这是一对多的关系（1:N）。

• 参与性： 所有的关系都是强制参与的，例如每个学生必须属于一个班级，每个班级必须至少有一名学生。

• 强弱实体

  1. 强实体：班级，活动，活动发布表，学生

  2. 弱实体：学生，活动发布表，活动报名表

  其中活动发布表、学生既是 强实体 也是 弱实体。

• 标识符依赖弱实体与非标识符依赖弱实体

  1. 标识符依赖弱实体：活动发布表，活动报名表
  2. 非标识符依赖弱实体：学生（学生班级不固定的话，否则是标识符依赖弱实体）

4.3 前置知识

4.3.1 CDM/LDM 到 PDM 转换原理

也就是 E-R 模型图到关系模型的转换

• 原理：

  1. （实体，实体属性，实体标识符） $\to$ （关系表，关系表中的列，关系表的PK或FK）
  2. 实体之间的联系 $\to$ 关系表之间的参照完整性约束

• 弱实体的转换：（标识符 pk + fk ；非标识符 fk ）

• 联系的转换

  • 1:1 ：任意实体的主键作为另实体的外键
    

  • 1:N ：父实体的主键作为子实体的外键

• M:N：需要创建一个新的表，分别参照原有实体对应的关系表。
  

• 继承的转换：主表的主键作为子表的 pk + fk

• 递归的转换：

  • 1:N：标识符转换为主键，还要转换为外键。

  

  • M:N：增加一个关联表，对原关系表建立两个参照完整性约束。
    

4.3 习题

4.3.1 针对一个复杂业务系统，采用什么策略设计该系统的CDM？

1. 理解业务需求： 深入了解业务流程、功能和数据需求。
2. 识别实体和属性： 确定系统中的核心实体和属性。
3. 确定实体关系： 确定实体之间的关系，包括一对一、一对多、多对多等。
4. 使用ER模型或UML建模： 使用可视化工具表示实体、属性和关系。
5. 规范化数据模型： 通过规范化确保数据模型的简洁性和一致性。
6. 考虑完整性和安全性： 设定适当的完整性约束和安全措施。
7. 验证和调整： 验证模型是否满足业务需求，并进行调整优化。
8. 持续迭代和维护： 随着业务的发展，持续更新和维护数据模型。

需求分析 $\to$ 实体及属性 $\to$ 实体关系 $\to$ E-R模型 $\to$ 规范化 $\to$ 完整性和安全性 $\to$ 调整优化 $\to$ 迭代更新

4.3.2 在如下示例的 LDM/PDM 数据模型转换设计中，如何处理实体继承联系？

• 管理人员和业务人员实体 非互斥继承 员工实体。
• 需要把员工实体的标识符 员工编号 作为 管理人员实体 和 业务人员实体 的 外键。

4.3.3 针对以下图书销售系统 CDM，如何进一步设计出合理的 LDM，PDM？为提高查询性能，如何设计表索引？为满足业务需求，如何设计参照完整性约束规则？

• LDM和PDM 略

• 表索引设计：

  1. 主键索引：对于每个表的主键字段，如 author 表的 作者身份证号、book 表的 图书ISBN 等，应创建主键索引以确保唯一性和快速检索。
  2. 外键索引：对于外键字段，如 book 表的 图书ISBN、作者身份证号、background user 表的 用户编号 等，应创建外键索引以加速关联查询操作。
  3. 经常查询的字段索引：针对经常用于查询条件或连接条件的字段，如 book 表的 图书名称、图书定价 等，可以创建相应的索引以提高查询性能。
  4. 联合索引：对于经常一起使用的字段组合，可以创建联合索引以加速多条件查询，如 inventory 表的 (图书编号, 书店编号) 组合索引。
  5. 唯一约束索引：对于需要保证唯一性的字段，如 discount 表的 折扣编号、background user 表的 用户账号 等，应创建唯一约束索引以加速唯一性检查。

• 参照完整性约束规则设计：

  1. 外键约束：已经定义了外键关系，如 销售 表的 图书ISBN 和 作者身份证号 分别参照 book 表和 author 表的主键，确保了数据的参照完整性。
  2. 级联操作：在删除或更新父表记录时自动处理相关子表记录时，可以考虑使用级联操作，如 on delete cascade 或 on update cascade。
  3. 限制操作：限制删除或更新父表记录时对子表记录的影响，可以使用 on delete restrict 或 on update restrict 等限制操作。
  4. 默认值约束：根据业务规则，可以对某些字段设置默认值约束以确保数据的完整性，如 background user 表的 c_type 字段可以设置默认值。
  5. 检查约束：根据业务规则，可以使用检查约束限制字段值的范围或格式，以确保数据的有效性，如电话号码字段的格式检查等。

4.4 前置知识

为什么要要求数据库规范数据？

• 减少冗余数据
• 合理设计数据库结构以实现对数据的高效访问
• 避免在增删改的时候出现数据不一致、数据不完整的问题（数据访问操作异常）

4.4.1 函数依赖

• 定义：在关系数据库中，一个属性（或属性集合）的取值能够唯一确定另一个属性（或属性集合）的取值，就称这个属性（或属性集合）对另一个属性（或属性集合）存在函数依赖。
• 类型：

  1. 完全函数依赖：一个属性（或属性组合）恰好完全依赖于关系中的另一个属性组合，而不依赖于这个属性组合的任何一个子集

     • eg：在一个学生表中，学生的学号和课程编号（复合主键）能够唯一确定学生的姓名。

  2. 部分函数依赖：一个属性（或属性组合）部分依赖于关系中的另一个属性组合，并且可以通过去掉其中一个或多个属性来消除这种依赖，那么就称这个依赖是部分函数依赖

     • eg：在一个订单表中，订单号和产品编号确定了产品名称（复合主键），但是产品名称也可能只依赖于订单号。

  3. 属性函数依赖：属性函数依赖指的是一个属性依赖于关系中的其他属性。这种依赖关系可以是完全函数依赖或部分函数依赖。换句话说，一个属性的取值取决于其他属性的取值。

     • eg：在一个学生信息表中，学生的年龄依赖于学生的出生日期。

  4. 多值函数依赖：一个属性组合对另一个属性组合存在依赖关系，但这种依赖关系不是通过单个属性确定的，而是通过多个属性值的组合来确定。

     • eg：在一个员工项目表中，员工编号和项目编号组成复合主键，一个员工参与了多个项目，而一个项目也可能由多个员工参与。

4.4.2 关系规范化范式

• 一个表可以有多个候选键，但是只有一个候选键可以选为主键
• 决定因子是能够确定其他属性的属性（不限于主键、候选键）或属性组合

• 第一范式（1NF）：关系表中的属性都是原子值，不可再分。

• 第二范式（2NF）：满足1NF，且消除了 部分函数依赖（非主属性不能依赖复合主键的真子集）。

• 第三范式（3NF）：满足2NF，且消除了 传递函数依赖（非主属性不能依赖其他非主属性or其组合）

• 巴斯-科德范式（BCNF）：满足3NF，完全依赖候选键而不能是其真子集。

  注：2NF要求不能依赖复合主键的真子集，3NF要求不能依赖其他非主属性组合，BCNF要求只能完全依赖候选键

• 第四范式（4NF）：满足BCNF，且消除了 多值函数依赖。

• 举例：

  ​ 学生（学号，姓名，课程号，成绩，系名，住址，联系方式)

  1. 1NF：联系方式可细分为 电话和邮件

     学生（学号，姓名，课程号，成绩，系名，住址，电话，邮箱）

  2. 2NF：主键为（学号，课程号）有 学号 $\to$ 电话，学号 $\to$ 邮箱

     学生（学号，姓名，系名，住址，电话，邮箱）

     课程成绩（学号，课程号，成绩）

  3. 3NF：学号 $\to$ 系名，系名 $\to$ 住址，故学号 $\to$ 住址，即住址到学号存在传递函数依赖（因为没有直接依赖）

     学生（学号，姓名，系编号，电话，邮箱）

     系信息（系编号，系名，住址，办公电话）

     课程成绩（学号，课程号，成绩）

  4. BCNF：以上三个关系的所有函数的依赖因子都是候选键

  5. 4NF：一个系的学生住址可能有多个，办公电话也可能有多个，故系信息可能存在多值函数依赖

     学生（学号，姓名，系编号，电话，邮箱）

     系信息（系编号，系名）

     电话目录（办公电话，系编号）

     学生住址（住址编号，住址名称，系编号）

     课程成绩（学号，课程号，成绩）

4.4.3 逆规范化处理

• 规范化减少了数据冗余，保证了数据完整性；

• 但是规范化过高也会导致数据库 关联查询性能 低。

• 逆规范化：适当降低规范化约束，允许适当的数据冗余以获取数据访问性能。
  • 方法：增加冗余列 or 派生列；多表合并。

4.4 习题

如下工资表在进行数据访问时会出现哪些异常问题？请设计出满足4NF范式的工资信息关系表。

• 异常：

  1. 数据冗余：多个部门共用相同的办公电话和工作地点信息时，这些信息在每条记录中都会重复出现。
  2. 插入异常：如果需要插入一个新部门但暂时没有员工，就无法记录该部门的办公电话和工作地点。
  3. 删除异常：如果删除某个部门的最后一名员工记录，该部门的办公电话和工作地点信息会丢失。
  4. 更新异常：如果某个部门的办公电话或者工作地点变化，需要更新所有相关记录，否则数据不一致。

• 1NF：满足

• 2NF：姓名、职称、入职年限、所在部门、办公电话、工作地点只依赖工号。存在部分函数依赖。

  工资表（年月，工号，月薪）

  员工表（工号，姓名，职称，入职年限，所在部门，办公电话，工作地点）

• 3NF：工号 $\to$ 所在部门，所在部门 $\to$ 办公电话、工作地点，存在传递函数依赖。

  工资表（年月，工号，月薪）

  员工表（工号，姓名，职称，入职年限，部门编号）

  部门表（部门ID，部门名称，办公电话，工作地点）

• BCNF：以上三个表的所有非主键属性都依赖的候选键。

• 4NF：一个部门可能有多个办公电话和工作地点，一个工作地点也可能有多个部门。可能存在多值函数依赖。

  工资表（年月，工号，月薪）

  员工表（工号，姓名，职称，入职年限，部门编号）

  部门表（部门ID，部门名称）

  电话表（办公电话，部门ID）

  部门-工作地点表（部门ID，地点ID） – 注：需要引入中间表处理多对多关系

  工作地点表（地点ID，工作地点）

4.5 习题

4.5.1 如何将数据库设计模型在不同DBMS系统上实现？

1. 设计数据库：
   • 需求分析、概念设计（E-R图）、逻辑设计（表结构）、物理设计（优化）。
2. 选择DBMS：
   • 确定目标DBMS，如MySQL、PostgreSQL、Oracle等。
3. 编写和调整SQL脚本：
   • 编写基础SQL脚本并调整数据类型和SQL语法以适应目标DBMS。
4. 使用工具：
   • 利用ORM工具（如Hibernate）和数据库迁移工具（如Liquibase、Flyway）简化跨DBMS实现。
5. 测试和优化：
   • 在目标DBMS上测试并优化数据库性能。

4.5.2 在执行SQL脚本程序创建数据库对象时，如何解决与数据库中原有对象的冲突问题？

1. 检查对象是否存在：
   • 在执行创建数据库对象之前，先查询系统表或信息模式，检查对象是否已存在。如果存在，则根据情况选择跳过创建或删除已有对象再重新创建。
2. 使用条件语句：
   • 在创建数据库对象的SQL语句中，使用条件语句（如IF EXISTS）判断对象是否已存在。如果存在，则跳过创建，否则执行创建操作。不同的DBMS可能有不同的条件语句支持。
3. 重命名对象：
   • 如果要创建的对象与数据库中已有对象冲突，可以尝试修改对象的名称，避免冲突。例如，在创建表时添加数字后缀或调整原有名称。
4. 备份和恢复：
   • 在执行创建数据库对象之前，先对数据库进行备份。如果创建过程中出现问题，可以通过恢复备份来回滚到之前的状态，避免数据丢失或不一致。
5. 手动处理冲突：
   • 遇到冲突问题时，手动处理是一种选择。例如，先删除冲突的对象，然后重新执行创建操作。这需要人工介入，适用于较为复杂或特殊情况。

4.5.3 针对电商系统的高并发访问、高性能存取、可扩展应用需求，在商品销售数据库设计与实现中，该如何来应对？请给出具体解决方案。

1. 数据库分区与分片

• 垂直分区：将不同类型的数据分割到不同的表中。例如，将用户信息和订单信息放在不同的表中。
• 水平分区：将同一表的数据按某种规则分割到不同的分区中，如按时间或地理区域分区。
• 数据库分片：将数据分布在多个数据库实例中，常用的分片策略有按用户ID、商品ID等。

2. 缓存机制

• 使用缓存系统：如Redis、Memcached缓存热点数据，减少数据库查询压力。
• 本地缓存：应用服务器内使用本地缓存减少对远程数据库的访问。

3. 索引优化

• 创建合适的索引：在高查询频率的列上创建索引，如商品ID、类别、价格等。
• 覆盖索引：使查询只使用索引中的数据，避免访问表数据。
• 分区索引：对于分区表，在每个分区上创建局部索引。

4. 读写分离

• 主从复制：将读操作分散到从数据库，主数据库处理写操作。
• 读写分离框架：使用中间件（如MySQL Proxy、HAProxy）实现读写分离。

5. 数据库连接池

• 连接池管理：使用连接池（如HikariCP、C3P0）来管理数据库连接，提高连接的复用性和效率。
• 配置优化：优化连接池参数（如最大连接数、最小空闲连接数）。

6. 分布式事务管理

• 两阶段提交（2PC）：在分布式环境中保证事务的一致性。
• 本地事务与补偿机制：尽量使用本地事务，使用补偿机制处理失败的操作。

7. 弹性扩展

• 容器化与编排：使用Docker和Kubernetes实现数据库服务的弹性扩展。
• 自动扩展：根据负载自动调整数据库实例数量。

8. 监控与调优

• 实时监控：使用监控工具（如Prometheus、Grafana）实时监控数据库性能。
• 定期调优：分析慢查询日志，定期进行数据库优化。

数据库分区与分片、缓存机制、索引优化、读写分离、连接池管理、分布式事务管理、弹性扩展以及监控与调优

学堂在线补充

NULL