第第 6 讲讲 设计数据完整性设计数据完整性 一、一、 关系数据库设计要点关系数据库设计要点 1 关系数据库设计概述关系数据库设计概述 2 规划关系数据库规划关系数据库 3 设计数据完整性概述设计数据完整性概述二、二、数据库表的规范化数据库表的规范化 1 概论概论 2 规范化的必要性规范化的必要性 3 到第一范式的转换到第一范式的转换 4 到第二范式的转换到第二范式的转换 5 到第三范式的转换到第三范式的转换 本本讲讲参考参考书书目目： 1 美美 Rob, P. Coonel, C. 数据数据库库系系统设统设计计、实现实现与管理（第与管理（第5版）版）. 陈陈立立军军 等等译译. 电电子工子工业业出版社，出版社，2004.3 2 美美 Ramakrishnan, R. Gehrke, J. 数据数据库库管理系管理系统统原理与原理与设计设计（第（第3版）版）.周周立立柱柱 等等译译. 北北京京：清清华华大大学学出出版版社社，2004.3 1 关系数据库设计概述关系数据库设计概述 DBMS 从产生直至发展到现在从产生直至发展到现在, 出现了多出现了多种类型种类型. 按其数据模型来分，主要有层次数据按其数据模型来分，主要有层次数据库、网状数据库和关系数据库库、网状数据库和关系数据库. 前两者在六七前两者在六七十年代较为流行，然而他们表示数据之间的联十年代较为流行，然而他们表示数据之间的联系太过于复杂，现在已经很少使用，只是在讲系太过于复杂，现在已经很少使用，只是在讲解数据库的时候才有所提及解数据库的时候才有所提及. 关系数据库以关系的数学理论为基础，是关系数据库以关系的数学理论为基础，是数据库技术的一项重大突破数据库技术的一项重大突破. 一、一、 关系数据库设计要点关系数据库设计要点 关系数据库简单严谨，二十多年来有了长关系数据库简单严谨，二十多年来有了长足发展，现在已经成为事实上的标准，当今几足发展，现在已经成为事实上的标准，当今几乎所有的数据库产品都是基于关系数据库的乎所有的数据库产品都是基于关系数据库的. 数据库设计是数据库应用的一个关键因素数据库设计是数据库应用的一个关键因素,设计结构合理、功能齐全的数据库对于提高数设计结构合理、功能齐全的数据库对于提高数据库应用程序的开发效率和程序的性能都是非据库应用程序的开发效率和程序的性能都是非常重要的常重要的. 数据库设计是指对于一个给定的应用环境数据库设计是指对于一个给定的应用环境,构造最优的数据模式，建立数据库，使其能够构造最优的数据模式，建立数据库，使其能够有效地存储数据记录，并能满足各种应用需求有效地存储数据记录，并能满足各种应用需求. 对于一个数据密集型应用来说，数据库设对于一个数据密集型应用来说，数据库设计是它的一个核心部分，但对于大型的软件系计是它的一个核心部分，但对于大型的软件系统设计来说统设计来说, 它只是其中一个部分它只是其中一个部分. 数据库设计的过程可以分为数据库设计的过程可以分为 6 步步. (1) 需求分析需求分析 (2) 概念数据库设计概念数据库设计 (3) 逻辑数据库设计逻辑数据库设计 (4) 模式的细化模式的细化 (5) 物理数据库设计物理数据库设计 (6) 应用与安全设计应用与安全设计 2 规划关系数据库规划关系数据库 进行数据库规划是所有数据库编程的第一步进行数据库规划是所有数据库编程的第一步,也是最重要的一步也是最重要的一步. 首先，列出客户所关心的所有数据和建立这首先，列出客户所关心的所有数据和建立这个数据库的主要目的个数据库的主要目的. 这可以通过收集各种相关这可以通过收集各种相关的数据报表和表格来完成的数据报表和表格来完成. 需求分析是数据库设需求分析是数据库设计的第一阶段，不断的调查与研究，了解组织机计的第一阶段，不断的调查与研究，了解组织机构的情况，了解部门的业务流程等系统需求，对构的情况，了解部门的业务流程等系统需求，对于设计好数据库是非常重要的于设计好数据库是非常重要的. 然后，数据表规划是整个数据库设计中技巧然后，数据表规划是整个数据库设计中技巧性最强的一个步骤性最强的一个步骤. 数据表要满足第三范式数据表要满足第三范式. 规划数据库规划数据库有以下三条有以下三条基本原则基本原则： 数据库中的记录个数应该反映现实世界数据库中的记录个数应该反映现实世界对象的个数对象的个数. 如果一个对象的实例在现实世界中如果一个对象的实例在现实世界中存在，则在数据库中有且只有一条记录存在存在，则在数据库中有且只有一条记录存在. 每行记录的字段应该表达现实世界对象每行记录的字段应该表达现实世界对象的属性的属性. 现实世界对象之间的关系应该反映在数现实世界对象之间的关系应该反映在数据库记录之间的关系上据库记录之间的关系上. 3 设计数据完整性概述设计数据完整性概述 关系数据库的目的是建立现实世界的模型关系数据库的目的是建立现实世界的模型.基于这一点基于这一点, 用来实施数据完整性的规则和方法用来实施数据完整性的规则和方法无论对于理论和数据库开发的实践都很重要无论对于理论和数据库开发的实践都很重要. 数据完整性是指存储在数据库中的数据的正数据完整性是指存储在数据库中的数据的正确性和相容性确性和相容性. 设计数据完整性的目的是为了防止数据库中设计数据完整性的目的是为了防止数据库中存在不符合语义的数据存在不符合语义的数据, 防止错误信息的输入和防止错误信息的输入和输出输出. 数据完整性可以分数据完整性可以分为为四四类类： 实实体完整性体完整性 每个实体都保持惟一性每个实体都保持惟一性. 值值域完整性域完整性 如何限制向表中输入值的范围如何限制向表中输入值的范围. 引用完整性引用完整性(参照完整性参照完整性) 处理数据时维护表之间数据的一致性处理数据时维护表之间数据的一致性. 用用户户定定义义的完整性的完整性 体体现实际现实际运用的运用的业务规则业务规则. 实实体完整性体完整性 每一个实体都必须拥有一个主键或者其他的惟一标识列每一个实体都必须拥有一个主键或者其他的惟一标识列. 要求要求：所有的主：所有的主键项键项都是唯一的，并且主都是唯一的，并且主键键的任何部分的任何部分都不能都不能为为空空. 目的目的：保：保证证每个每个实实体有惟一的体有惟一的标识标识，确保外，确保外键值键值可以正可以正确地引用主确地引用主键值键值. 例子例子：发货单发货单没有重复的号没有重复的号码码，号，号码码也不能也不能为为空空. 参照完整性参照完整性 要求要求：外：外键键可能是空可能是空-只要它不是它自己所在的表的主只要它不是它自己所在的表的主键键的一部分的一部分-每个非每个非null外外键值键值必必须须参照已参照已经经存在的主存在的主键值键值. 目的目的：不能有无效的：不能有无效的项项. 例子例子：客：客户户可以没有可以没有给给定的代理商号定的代理商号码码，但是不能有无，但是不能有无效的代理商号效的代理商号码码. SQL Server提供的用来实施数据完整性的提供的用来实施数据完整性的途径主要是途径主要是: 约束约束 ( constraint ) 标识列标识列 ( identity column) 默认值默认值 ( default ) 规则规则 ( rule ) 触发器触发器 ( trigger ) 数据类型数据类型 ( data type ) 索引索引 ( index ) 存储过程存储过程 ( stored procedure ) 约束完整性约束完整性 约束完整性是指数据的正确性和完备性约束完整性是指数据的正确性和完备性. 在用在用INSERT、DELETE、UPDATE 语句修改数据库语句修改数据库内容时，数据的完整性可能会遭到破坏，内容时，数据的完整性可能会遭到破坏， 有如下有如下几种情况：几种情况： - 无效的数据被添加到数据库中，如某订无效的数据被添加到数据库中，如某订单单所指的产品不存在所指的产品不存在. - 对数据库的修改不一致，对数据库的修改不一致， 如为某产品增加如为某产品增加了订单，但却没有调整产品的库存信息了订单，但却没有调整产品的库存信息. - 将存在的数据修改为无效的数据将存在的数据修改为无效的数据. 在在 SQL Server 中实现数据完整性的方法之中实现数据完整性的方法之一就是一就是约束约束. 使用这种方法简单且不容易出错，使用这种方法简单且不容易出错，它把完整性的要求定义在表和列上它把完整性的要求定义在表和列上. 所谓约束，是所谓约束，是 SQL Server 强制实行的应用强制实行的应用规则，它能够限制用户存放到表中的数据的格式规则，它能够限制用户存放到表中的数据的格式和范围和范围. 约束是在约束是在CREATE TABLE、ALTER TABLE语句中，通过语句中，通过 CONSTRAINT 和和 DEFAULT实现实现. 1 概论概论 好的数据库设计需要好的表结构好的数据库设计需要好的表结构. 本节我们本节我们将学习如何评价和设计好的数据库表，控制数据将学习如何评价和设计好的数据库表，控制数据冗余，从而避免数据异常，这就是冗余，从而避免数据异常，这就是规范化规范化. 为了识别和鉴赏好的表结构的特性，考察一为了识别和鉴赏好的表结构的特性，考察一个不好的表结构是很有用的个不好的表结构是很有用的. 利用这种方法，我利用这种方法，我们将学会如何设计好的表结构，如何修改已有的们将学会如何设计好的表结构，如何修改已有的不好的表结构不好的表结构.二、数据库表的规范化二、数据库表的规范化 通过规范化不仅可以消除数据异常，而且适通过规范化不仅可以消除数据异常，而且适当规范化的表结构实际上要比没有规范化的来得当规范化的表结构实际上要比没有规范化的来得简单，它还能反映出一个组织的实际运行情况简单，它还能反映出一个组织的实际运行情况. 拥有好的关系数据库软件，并不足以避免拥有好的关系数据库软件，并不足以避免数据冗余数据冗余. 即使在一个好的数据库设计中即使在一个好的数据库设计中, 也有也有可能生成不好的表结构可能生成不好的表结构. 那么，如何识别不好的表结构，如何创建那么，如何识别不好的表结构，如何创建好的表结构呢？两个问题的答案都是基于规范好的表结构呢？两个问题的答案都是基于规范化的化的. 规范化是给实体分配属性的过程规范化是给实体分配属性的过程. 它能它能够减少但不能够消除数据冗余；相反，它通过够减少但不能够消除数据冗余；相反，它通过生成有控制的冗余来连接数据库表生成有控制的冗余来连接数据库表. 规范化通过一系列称为范式的阶段来完成规范化通过一系列称为范式的阶段来完成.最前面的三个阶段分别叫做第一范式最前面的三个阶段分别叫做第一范式 (1NF)、第二范式第二范式 (2NF)和第三范式和第三范式 (3NF). 从结构化从结构化的的观点来看，观点来看，2NF 比比 1NF 好，好，3NF 比比 2NF 好好.对于大多数的事物数据库设计，我们只要能规对于大多数的事物数据库设计，我们只要能规范到范到 3NF 就已经足够了就已经足够了. 虽然规范化是数据库设计的一个重要组成虽然规范化是数据库设计的一个重要组成部分，但是并不是规范化级别越高越好部分，但是并不是规范化级别越高越好. 通常通常,范式级别越高，产生制定输出所要求的连接就范式级别越高，产生制定输出所要求的连接就越多，系统对终端用户请求的响应就越慢越多，系统对终端用户请求的响应就越慢. 我们考察某建筑公司的一个简单数据库行我们考察某建筑公司的一个简单数据库行为为. 该公司管理几个建筑项目，每个项目都有该公司管理几个建筑项目，每个项目都有它自己的项目号、项目名、员工等属性，每个它自己的项目号、项目名、员工等属性，每个员工有员工号、姓名和职位级别等属性员工有员工号、姓名和职位级别等属性. 公司根据每个合同需要花费的小时数来向公司根据每个合同需要花费的小时数来向客户收费客户收费. 每小时的报酬由员工的职位决定每小时的报酬由员工的职位决定. 该应用周期性地生成信息报表该应用周期性地生成信息报表. 由这个报由这个报表对应地生成一个表表对应地生成一个表. 2 规范化的必要性规范化的必要性 这个表的结构不符合完整性的要求，它也这个表的结构不符合完整性的要求，它也没有很好的处理数据没有很好的处理数据. 项目号（项目号（PROJ_NUM）很明显是希望）很明显是希望作为一个主码，或者至少是主码的一部分，但作为一个主码，或者至少是主码的一部分，但是它包含空值是它包含空值. 表的数据输入引起数据的不一致表的数据输入引起数据的不一致. 如，如，Elect.Engineer 可能写成可能写成Elect.Eng,El.Eng,EE. 该表中有数据冗余，会产生异常：该表中有数据冗余，会产生异常： 更新异常更新异常: 如修改如修改105号的号的 JOB_CLASS. 插入异常，删除异常插入异常，删除异常. 当把某员工分配到某项目时当把某员工分配到某项目时,重复输入数据重复输入数据. 消除消除组组重复重复 在作为主码的列中加入合适的项即可在作为主码的列中加入合适的项即可. 主主码应码应能恰当地、惟一地确定任何属性能恰当地、惟一地确定任何属性值值 取主取主码为码为 PROJ_NUM 和和 EMP_NUM 的的组组合合. 这样这样得到了符合第一范式的表得到了符合第一范式的表. 3 到第一范式的转换到第一范式的转换PROJ_NUM PROJ_NAME EMP_NUM EMP_NAME JOB_CLASS CHG_HOUR HOUR部分依赖部分依赖部分依赖部分依赖传递依赖传递依赖 术语术语 1NF 描述了如下的表格式：描述了如下的表格式： 定定义义了所有的了所有的码码属性属性. 表中没有重复表中没有重复组组. 所有的属性都依所有的属性都依赖赖于主于主码码. 所有的关系表都属于所有的关系表都属于 1NF. 上图所示的上图所示的第第一范式的表结构的问题是它包含部分依赖一范式的表结构的问题是它包含部分依赖, 也也就是就是说说，基于部分主，基于部分主码码的依的依赖赖. 虽虽然有然有时为时为了提高性能需要使用部分依了提高性能需要使用部分依赖赖,但要但要谨谨慎使用慎使用. 因因为这样为这样的表仍有数据冗余的表仍有数据冗余(由由每行每行记录记录都要求有数据副本而都要求有数据副本而产产生生), 重复重复输输入入使效率很低，也会助使效率很低，也会助长长数据异常的数据异常的产产生生. 一个表属于一个表属于第二范式第二范式是指：是指： 它属于它属于 1NF . 它不包含部分依它不包含部分依赖赖，即，没有属性只依，即，没有属性只依赖赖于主于主码码的一部分的一部分. 到第二范式的到第二范式的转换转换是，将原表分是，将原表分为为三个表三个表: PROJCT( PROJ_NUM, PROJ_NAME ) EMPLOYEE( EMP_NUM, EMP_NAME,JOB_CLASS, CHG_HOUR ) ASSIGN( PROJ_NUM, EMP_NUM, ASSIGN_HOUR)4 到第二范式的转换到第二范式的转换 将三个表再分出一个表，消除将三个表再分出一个表，消除传递传递依依赖赖: EMPLOYEE( EMP_NUM, EMP_NAME,JOB_CLASS ) JOB( JOB_CLASS, CHG_HOUR ) 表属于表属于第三范式第三范式是指：是指： 它属于它属于 2NF . 它不包含它不包含传递传递依依赖赖. 消除了表消除了表结结构中的部分依构中的部分依赖赖和和传递传递依依赖赖后后,还还要集中改善数据要集中改善数据库库提供信息和增提供信息和增强强关系特性关系特性. 5 到第三范式的转换到第三范式的转换 练习练习 写出写出 T-SQL 语句建立下表存放学生语句建立下表存放学生的信息的信息. 表名为：表名为：student, 含有列：含有列：name(char,10位位)、id(char, 7位位)、sex(char,2位位)、birthday(datetime, 8位位) 、 class(char, 10位位) birth_place(varchar, 50位位)，设置学号（，设置学号（id）为主键约束并带有聚集索引为主键约束并带有聚集索引.