LOGO 第 6 章 数据库规范化设计 孙焘. 重点： 三个范式的基本概念 1 概念模型 2 物理模型 3 多 framePowerDesigner 工具的使用页面设计 4.

Presentation on theme: "LOGO 第 6 章 数据库规范化设计 孙焘. 重点： 三个范式的基本概念 1 概念模型 2 物理模型 3 多 framePowerDesigner 工具的使用页面设计 4."— Presentation transcript:

1 LOGO 第 6 章 数据库规范化设计 孙焘

2 重点： 三个范式的基本概念 1 概念模型 2 物理模型 3 多 framePowerDesigner 工具的使用页面设计 4

3 难点： 范式概念的理解 1 “ 强制 ” 关系概念的理解 2 概念模型和物理模型的区别 3 多对多模型的转换 4

4 6.1 E-R 模型

5  E-R 图也即实体 - 联系图 (Entity Relationship Diagram) ，提供了表示实体 型、属性和联系的方法，用来描述现实世界的概 念模型。  E-R 方法 : 是 “ 实体 - 联系方法 ” （ Entity- Relationship Approach ）的简称。它是描 述现实世界概念结构模型的有效方法。  构成 E-R 图的基本要素是实体型、属性和联系

6 实体型 (Entity) ：  具有相同属性的实体具有相同的特 征和性质，用实体名及其属性名集合 来抽象和刻画同类实体 ; 在 E-R 图中用 矩形表示，矩形框内写明实体名

7 属性 (Attribute) ：  实体所具有的某一特性，一个实体 可由若干个属性来刻画。在 E-R 图中 用椭圆形表示，并用无向边将其与相 应的实体连接起来；比如学生的姓名 、学号、性别、都是属性。如果是多 值属性的话，再椭圆形外面再套实线 椭圆。

8 联系 (Relationship) ：  信息世界中反映实体内部或实体之 间的联系。实体内部的联系通常是指 组成实体的各属性之间的联系；实体 之间的联系通常是指不同实体集之间 的联系。在 E-R 图中用菱形表示，菱 形框内写明联系名，并用无向边分别 与有关实体连接起来，同时在无向边 旁标上联系的类型

9 联系的类型  1 对 1 ：在两个实体连线方向写 1 ；  1 对多：在 1 的一方写 1 ，多的一方写 N ；  多对多：则是在两个实体连线方向各写 N,M

10 6.2 键 (Key) ：  ①超键：可以唯一标识一个实体的属性或者属性组  ②候选键：可以唯一标识一个实体的具有最小属性 数目的超键。  ③主键 (PK) ：被选中作为标示实体的候选键。如学 生表里学号是主键，选课表里学号和课号是联合主键。  ④备用键：没有被选中作为实体的候选键。  ⑤外键 (FK) ：实体 A 中的一个属性组，与另一个实 体 B 的候选键相对应，则称该属性组为 A 的外键。如学生 表里的院系代码与院系表的主键对应，院系代码是学生表 的外键。

11 6.3 概念模型：  数据库概念模型是对真实世界中问题域内的 事物的描述，不是对软件设计的描述。概念的描 述包括：记号、内涵、外延，其中记号和内涵（ 视图）是其最具实际意义的。  概念模型不依赖于具体的计算机系统，他是 纯粹反映信息需求的概念结构。  建模是在需求分析结果的基础上展开，常常 要对数据进行抽象处理。常用的数据抽象方法是 ‘ 聚集 ’ 和 ‘ 概括 ’ 。

12 概念模型的建模过程：  运用概念目录列表或名词性短语找出问 题领域中的后选概念  绘制概念到概念模型图中  为概念添加关联关系  为概念添加属性

13 设计局部概念模型  ① 确定局部概念模型的范围  ② 定义实体  ③ 定义联系  ④ 确定属性  ⑤ 逐一画出所有的局部 ER 图，并附以 相应的说明文件

14 设计全局概念模型  ① 确定公共实体类型  ② 合并局部 ER 图  ③ 消除不一致因素  ④ 优化全局 ER 图  ⑤ 画出全局 ER 图，并附以相应的说明文件。

15 6.4 物理模型：  把概念模型转化到数据世界

16 6.5 范式：  另外一种泛化的数据库设计的方法，叫做规 范化  规范化是一种用来产生表的集合的技术，这 些表具有符合要求的属性，并能支持用户或公司 的需求，规范化通常作为对表结构的一系列测试 来决定它是否满足或者符合 给定范式。存在几种 范式形式，但是最常用的是第一范式，第二范式 和第三范式。所有这些范式都是基于在表中的列 之间的关系的。

17 第一范式 (1NF)  如果关系模式 R 的每个关系 r 的属性都 是不可分的数据项，那么就称 R 是第一范式 的模式。简单的说，每一个属性都是原子 项，不可分割。 1NF 是关系模式应具备的 最起码的条件，如果数据库设计不能满足 第一范式，就不称为关系型数据库。关系 数据库设计研究的关系规范化是在 1NF 之 上进行的。

18 要点：  任何给定行的列必须是只包含一个值；  表中的每一行必须有相同数量的列；  表中的每一行必须是唯一的即是不相同 的；

19 学号姓名性别联系方式 09001 张三 MEmail:zs@163.com, Phone:88888888 09002 李四 MEmail:ls@163.com, Phone:77777777 学号姓名性别电子邮件电话 09001 张三 Mzs@163.com88888888 09002 李四 Mls@163.com77777777 ↓

20 第二范式 (2NF)  如果关系模式 R 是 1NF ，且每个非主属性完 全函数依赖于任意一个候选键，那么就称 R 是第 二范式。简单的说，第二范式要满足以下的条件 ：首先要满足第一范式，其次每个非主属性要完 全函数依赖于任意一个候选键。也就是说，每个 非主属性是由整个主键函数决定的，而不能由主 键的一部分来决定。

21 要点  必须满足第一范式；  表中的所有非主键必须依赖一整个主 键；

22 列名含义类型长度允许空键 Sno 学号 char8NP Cno 课号 char8NP Cname 课程名称 varchar50Y Ccredit 学分 intY CTeacher 教师 varchar10Y Grade 成绩 intY 选课表 2(CS2) 字段列表

23 表 CS2 中，学号和课号是关键字，而属性课程名称等只取决于课号， 这就不满足第二范式。并将带来以下问题：  (1) 数据冗余：同一门课程由 n 个学生选修，课程名称等就重复 n-1 次。  (2) 更新异常：若调整了某门课程的学分，数据表中所有该门课程 的 “ 学分 ” 值都要更新，否则会出现同一门课程不同学分的情况。  (3) 插入异常：假设要开设一门新的课程，暂时还没有人选修。这 样，由于还没有 “ 学生 ” 关键字，课程名称也无法记录入数据库。  (4) 删除异常：假设一批学生已经完成课程的选修，这些选修记录 就应该从数据库表中删除。但与此同时，课程信息也被删除了。

24 第三范式 (3NF)  如果关系模式 R 是 2NF ，且关系模式 R （ U ， F ）中的所有非主属性对任何候选关键字都不存在 传递依赖，则称关系 R 是属于第三范式。所谓传 递函数依赖，指的是如果存在 "A → B → C" 的 决定关系，则 C 传递函数依赖于 A 。 简单的说， 第三范式要满足以下的条件：首先要满足第二范 式，其次非主属性之间不存在函数依赖。由于满 足了第二范式，表示每个非主属性都函数依赖于 主键。如果非主属性之间存在了函数依赖，就会 存在传递依赖，这样就不满足第三范式。

25 要点：  1 ）必须满足第二范式；  2 ）表中的所有非主键必须相互独 立，即没有传递性依赖

26 列名含义类型长度允许空键 Cno 课程编号 char8NP Cname 课程名称 varchar50Y Ccredit 学分 intY Cteacher 教师 varchar10Y 课程表 Course 加上职称字段。这样，职称依赖于教师，教师又依赖 这同样会存在数据冗余、更新异常、插入异常和删除异常的情况

27 鲍依斯 - 科得范式（ BCNF ）：  在第三范式的基础上，数据库表中 如果不存在任何字段对任一候选关键 字段的传递函数依赖则符合第三范式 。

28 假设仓库管理关系表为 StorehouseManage( 仓库 ID, 存储物品 ID, 管理员 ID, 数量 ) ，且有一个管理员只在一个仓库工作；一个仓 库可以存储多种物品。这个数据库表中存在如下决定关系： ( 仓库 ID, 存储物品 ID) → ( 管理员 ID, 数量 ) ( 管理员 ID, 存储物品 ID) → ( 仓库 ID, 数量 ) 所以， ( 仓库 ID, 存储物品 ID) 和 ( 管理员 ID, 存储物品 ID) 都是 StorehouseManage 的候选关键字，表中的唯一非关键字段为数 量，它是符合第三范式的。但是，由于存在如下决定关系： ( 仓库 ID) → ( 管理员 ID) 管理员 ID) → ( 仓库 ID)

29 即存在关键字段决定关键字段的情况，所以其不符合 BCNF 范式。它会 出现如下异常情况： (1) 删除异常： 当仓库被清空后，所有 " 存储物品 ID" 和 " 数量 " 信息被删除的同时， " 仓 库 ID" 和 " 管理员 ID" 信息也被删除了。 (2) 插入异常： 当仓库没有存储任何物品时，无法给仓库分配管理员。 (3) 更新异常： 如果仓库换了管理员，则表中所有行的管理员 ID 都要修改。

30 把仓库管理关系表分解为二个关系表： 仓库管理： StorehouseManage( 仓库 ID, 管 理员 ID) ； 仓库： Storehouse( 仓库 ID, 存储物品 ID, 数 量 ) 。 这样的数据库表是符合 BCNF 范式的，消除了删 除异常、插入异常和更新异常。

31  数据库设计中还有第四、第五范式 ，在一般的设计中能达到 BC 范式，或 者第三范式即可，在一些应用中，达 到第二范式即可。

32 6.6 SQL 依赖关系  SQL 依赖关系是在 SQL 表达式中使用的按名称引 用，可使一个实体依赖于另一个实体。在其定义中引用了 另一个实体并且该定义存储在系统目录中的实体称为 “ 引 用实体 ” 。被另一个实体引用的实体称为 “ 被引用的实体 ” 。 数据库引擎跟踪两种类型的依赖关系。

33 绑定到架构的依赖关系  绑定到架构的依赖关系是一种两个实体之间 的关系，只要引用实体存在，这种关系就可以防 止被引用的实体被删除或更改。绑定到架构的依 赖关系是在使用 WITH SCHEMABINDING 子句创建视图或用户定义函数时创建的。绑定到 架构的依赖关系也可以于表在 CHECK 或 DEFAULT 约束或计算列定义中引用另一个实体 （例如 Transact-SQL 用户定义函数、用户定 义类型或 XML 架构集合）时创建。使用由两部 分 (schema_name.object_name) 组成的 名称指定对象不能称为绑定到架构的引用。

34 非绑定到架构的依赖关系  非绑定到架构的依赖关系也是一种 两个实体之间的关系，但这种关系并 不防止被引用的实体被删除或修改。

35  图中包含两个实体：过程 X 和过程 Y 。过程 X 包含一个 对过程 Y 按名称引用的 SQL 表达式。过程 X 称为 “ 引用实体 ” ，而过程 Y 称为 “ 被引用的实体 ” 。由于过程 X 依赖于过程 Y ， 因此当过程 Y 不存在时，过程 X 将失败，并出现运行时错误。 但是，当过程 X 不存在时，过程 Y 也不会失败。

36  下面的示例说明存储过程 X 如何依赖于存储过程 Y 。  USE tempdb; GO CREATE PROCEDURE dbo.Y AS SELECT * FROM sys.objects GO CREATE PROCEDURE dbo.X as EXEC dbo.Y; GO

37  若要查看 X 对 Y 的依赖关系，请运行以下查询。  SELECT * FROM sys.sql_expression_dependencies WHERE referencing_id = OBJECT_ID('X') AND referenced_id = OBJECT_ID('Y') AND referenced_schema_name = 'dbo' AND referenced_entity_name = 'Y' AND referenced_database_name IS NULL AND referenced_server_name IS NULL; GO

38 6.7 多对多关系  将多对多关系映射到数据库关系图中  多对多关系使您得以将一个表中的每一行与 另一个表中的多行相关，或者将后者中的每一行 与前者中的多行相关。例如，可创建 authors 表与 titles 表之间的多对多关系，以将每位作者 与他或她的所有书籍匹配并且将每本书与其所有 作者匹配。从上述任何一个表创建一对多关系都 会错误地表示每本书只能有一位作者或者每位作 者只能编写一本书。

39  表与表之间的多对多关系通过联接表存 储于数据库中。联接表包含要相关的两个 表的主键列。然后，分别创建从每个表的 主键列到联接表中的匹配列之间的关系。 在 pubs 数据库中， titleauthor 表是联 接表。

40  多对多关系至少需要 3 个表，我们把一 个表叫做主表，一个叫做关系表，另外一 个叫做字典表或者副表  按照数据库的增删查改操作，多对多关 系的查找都可以用 inner join 或者 select * from 主表 where id in (select 主 表 id from 关系表 )

41 LOGO