软考高级-系统分析师-案例分析-数据库设计

数据库基础

数据库设计过程

• 需求分析：通过调查研究，了解用户的数据和处理要求，并按一定格式整理形成需求说明书。
• 概念设计：在需求分析阶段产生的需求说明书的基础上，按照特定的方法将它们抽象为一个不依赖于任何数据库管理系统的数据模型，即概念模型。
• 逻辑设计：将概念模型转化为某个特定的数据库管理系统上的逻辑模型。设计逻辑结构时，首先为概念模型选定一个合适的逻辑模型（通常是关系模型），然后将其转化为由特定 DBMS 支持的逻辑模型，最后对逻辑模型进行优化。
• 物理设计：对给定的逻辑模型选取一个最适合应用环境的物理结构，所谓数据库的物理结构，主要是指数据库在物理设备上的存储结构和存取方法。

规范化理论-范式

逐步优化以解决：插入异常、更新异常、删除异常、数据冗余

• 1NF 属性值都是不可分的原子值
  第一范式（1NF）：在关系模式R中，当且仅当所有域中只包含原子值，即每个属性都是不可再分的数据项，则称关系模式R是第一范式。

• 2NF 消除非主属性对候选键的部分依赖，满足1NF，如果候选码只有单个属性，必然满足2NF
  第二范式（2NF）：当且仅当实体E是第一范式（1NF），且每一个非主属性完全依赖候选键（不存在部分依赖）时，则称实体E是第二范式。
  
  学分对课程号候选键有部分依赖，所以不满足2NF。

• 3NF 消除非主属性对候选键的传递依赖，满足1NF，如果没有非主属性，必然满足3NF
  第三范式（3NF）：当且仅当实体E是第二范式（2NF），且E中没有非主属性传递依赖于候选码时，则称实体E是第三范式。
  
  学生关系S（学号，姓名，系号，系名，系位置）。从各属性之间的联系可以判断出S的函数依赖有学号→(姓名，系号，系名，系位置)，系号→(系名，系位置)。显然，学号为候选键。在函数依赖中有学号→系号→系名与学号→系号→系位置，这便是传递函数依赖。由于系名与系位置为非键属性，同时传递依赖于候选键，因此，关系模式S不满足3NF。若要使S满足3NF，需要将其拆分为S1(学号，姓名，系号)和S2(系号，系名，系位置)。

• BCNF 消除主属性对候选键的部分和传递依赖
  所有的函数依赖左边部分都必须是候选键中的一个。

部分函数依赖和传递函数依赖

范式级别提升带来了什么负面影响？

• 表太细，关联不方便（反规范化）

数据库事务

事务的4个属性（4个特性）:

• 原子性
  事务是数据库的逻辑工作单位，事务的原子性保证事务包含的一组更新操作是原子不可分的，也就是说，这些操作是一个整体，不能部分的完成。

• 一致性
  一致性是指使数据库从一个一致性状态变到另一个一致性状态。
  例如，在转账的操作中，各账户金额必须平衡。一致性与原子性是密切相关的，一致性在逻辑上不是独立的，它由事务的隔离性来表示。

• 隔离性
  隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。它要求即使有多个事务并发执行，但看上去每个事务按串行调度执行一样。这一性质也称为可串行性，也就是说，系统允许的任何交错操作调度等价于一个串行调度。

• 持久性
  持久性也称为永久性，是指事务一旦提交，改变就是永久性的，无论发生何种故障，都不应该对其有任何影响。

并发控制 - 数据不一致问题

• 丢失修改
  事务A与事务B从数据库中读入同一数据并修改，事务B的提交结果破坏了事务A提交的结果，导致事务A的修改被丢失。
  解决方法：对A加X锁（写锁），那么B只能在A解锁后读。

• 不可重复读
  不可重复读是指事务A读取数据后，事务B执行了更新操作，事务A使用的仍是更新前的值，造成了数据不一致性。
  解决方法：A加X锁（写锁）后，B对同一数据加X锁（写锁），这样A的锁解开后，才可以调用B

• 读“脏”数据
  事务A修改某一数据，并将其写回磁盘，事务B读取同一数据后，事务A由于某种原因被撤消，这时事务A已修改过的数据恢复原值，事务B读到的数据就与数据库中的数据不一致，是不正确的数据，称为“脏数据”。
  解决方法：修改时加排他锁，直到事务提交后才释放，读取时加共享锁，读取完释放。A读取数据时加上共享锁后，不允许任务事务操作该数据，只能读取。

并发控制 - 封锁协议

• 处理并发控制的主要方法是采用封锁技术，主要有X封锁（写锁）和S封锁（读锁）。

• 排他型封锁（X封锁）又叫写锁、独占锁：对于T1如果对A加了写锁，其他事务不能对A加任何锁。只允许事务T读取和修改数据A，其他事务要等事务T解除X封锁以后，才能对数据A实现任何类型的封锁。可见，X封锁只允许一个事务独锁某个数据，具有排他性。

• 共享型封锁（S封锁）又叫读锁：对于T1如果对A加了读锁，其他事务只能在A上加读锁，不能加写锁，事务T1自己可以升级S锁至X锁。允许事务T读取数据A，但不能修改数据A，在所有S封锁解除之前，决不允许任何事务对数据A实现X封锁。

• 一级封锁协议：事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放。可防止丢失修改，并保证事务T是可恢复的，但不能保证可重复读和不读“脏数据”。

• 二级封锁协议：一级封锁协议加上事务T在读取数据R之前先对其加S锁，读完后即可释放S锁。可防止丢失修改、读“脏数据”，但不能保证可重复读。

• 三级封锁协议。一级封锁协议加上事务T在读取数据R之前先对其加S锁，直到事务结束才释放。可防止丢失修改、读“脏数据”、防止数据重复读。

• 两段锁协议：对这些事务的任何并发调度策略都是可串行化的可能发生死锁。

案例题

某航空公司要开发一个订票信息处理系统，以方便各个代理商销售机票。开发小组经过设计，给出该系统的部分关系模式如下：航班（航班编号，航空公司，起飞地，起飞时间，目的地，到达时间，剩余票数，票价）代理商（代理商编号，代理商名称，客服电话，地址，负责人）机票代理（代理商编号，航班编号，票价）旅客（身份证号，姓名，性别，出生日期，电话）购票（购票单号，身份证号，航班编号，搭乘日期，购票金额）在提供给用户的界面上，其核心功能是当用户查询某航班时，将该航班所有的代理商信息及其优惠票价信息，返回给用户，方便用户购买价格优惠的机票。在实现过程中发现，要实现此功能，需要在代理商和机票代理两个关系模式上进行连接操作，性能很差。为此开发小组将机票代理关系模式进行了扩充，结果为：机票代理（代理商编号，航班编号，代理商名称，客服电话，票价）这样，用户在查找信息时只需对机票代理关系模式进行查询即可，提高了查询效率。

【问题 1】
机票代理关系模式的修改，满足了用户对代理商机票价格查询的需求，提高了查询效率。但这种修改导致机票代理关系模式不满足 3NF，会带来存储异常的问题。
1）请具体说明其问题，并举例说明。
2）这种存储异常会造成数据不一致，请给出解决该存储异常的方案。

1）不满足 3NF 会产生函数的传递依赖，造成数据冗余和修改异常等问题。
① 数据冗余，一个代理商会代理多家航班的机票销售业务，在机票代理模式中，该代
理商的代理商名称，客服电话就会被存储多次，造成数据的冗余。
② 修改异常，当需要修改该代理商的名称或客服电话时，就要修改所有相应元组中的
名称或客服电话，否则就会出现客服电话值不一致的现象，产生