LOGO

第 6 章 数据库规范化设计

孙焘

重点：

三个范式的基本概念1

概念模型2

物理模型3

多 framePowerDesigner工具的使用页面设计4

难点：

范式概念的理解1

“强制”关系概念的理解2

概念模型和物理模型的区别3

多对多模型的转换4

6.1 E-R 模型

E-R 图也即实体 - 联系图 (Entity Relationship Diagram) ，提供了表示实体型、属性和联系的方法，用来描述现实世界的概念模型。

E-R 方法 : 是“实体 - 联系方法”（ Entity-Relationship Approach ）的简称。它是描述现实世界概念结构模型的有效方法。

构成 E-R 图的基本要素是实体型、属性和联系

实体型 (Entity) ：

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

属性 (Attribute) ：

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

联系 (Relationship) ：

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

联系的类型

1 对 1 ：在两个实体连线方向写 1 ；

1 对多：在 1 的一方写 1 ，多的一方写 N ；

多对多：则是在两个实体连线方向各写 N,M

6.2 键 (Key) ：

①超键：可以唯一标识一个实体的属性或者属性组

② 候选键：可以唯一标识一个实体的具有最小属性数目的超键。

③ 主键 (PK) ：被选中作为标示实体的候选键。如学生表里学号是主键，选课表里学号和课号是联合主键。

④ 备用键：没有被选中作为实体的候选键。⑤ 外键 (FK) ：实体 A 中的一个属性组，与另一

个实体 B 的候选键相对应，则称该属性组为 A 的外键。如学生表里的院系代码与院系表的主键对应，院系代码是学生表的外键。

6.3 概念模型：数据库概念模型是对真实世界中问题域内

的事物的描述，不是对软件设计的描述。概念的描述包括：记号、内涵、外延，其中记号和内涵（视图）是其最具实际意义的。

概念模型不依赖于具体的计算机系统，他是纯粹反映信息需求的概念结构。

建模是在需求分析结果的基础上展开，常常要对数据进行抽象处理。常用的数据抽象方法是‘聚集’和‘概括’。

概念模型的建模过程：

运用概念目录列表或名词性短语找出问题领域中的后选概念

绘制概念到概念模型图中 为概念添加关联关系 为概念添加属性

设计局部概念模型

① 确定局部概念模型的范围 ② 定义实体 ③ 定义联系 ④ 确定属性 ⑤ 逐一画出所有的局部 ER 图，并

附以相应的说明文件

设计全局概念模型

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

6.4 物理模型：

把概念模型转化到数据世界

6.5 范式：

另外一种泛化的数据库设计的方法，叫做规范化

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

第一范式 (1NF)

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

要点：

任何给定行的列必须是只包含一个值；

表中的每一行必须有相同数量的列；

表中的每一行必须是唯一的即是不相同的；

学号 姓名 性别 联系方式

09001 张三 M Email:zs@163.com, Phone:88888888

09002 李四 M Email:ls@163.com, Phone:77777777

学号 姓名 性别 电子邮件 电话09001 张三 M zs@163.com 88888888

09002 李四 M ls@163.com 77777777

↓

第二范式 (2NF)

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

要点

必须满足第一范式；

表中的所有非主键必须依赖一整个主键；

列名 含义 类型 长度 允许空 键Sno 学号 char 8 N P

Cno 课号 char 8 N P

Cname 课程名称 varchar 50 Y

Ccredit 学分 int Y

CTeacher 教师 varchar 10 Y

Grade 成绩 int Y

选课表 2(CS2)字段列表

表 CS2 中，学号和课号是关键字，而属性课程名称等只取决于课号，这就不满足第二范式。并将带来以下问题：

(1) 数据冗余：同一门课程由 n 个学生选修，课程名称等就重复n-1次。

(2)更新异常：若调整了某门课程的学分，数据表中所有该门课程的“学分”值都要更新，否则会出现同一门课程不同学分的情况。

(3) 插入异常：假设要开设一门新的课程，暂时还没有人选修。这样，由于还没有“学生”关键字，课程名称也无法记录入数据库。

(4) 删除异常：假设一批学生已经完成课程的选修，这些选修记录就应该从数据库表中删除。但与此同时，课程信息也被删除了。

第三范式 (3NF)

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

要点：

1 ）必须满足第二范式；

2 ）表中的所有非主键必须相互独立，即没有传递性依赖

列名 含义 类型 长度 允许空 键Cno 课程编号 char 8 N P

Cname 课程名称 varchar 50 Y

Ccredit 学分 int Y

Cteacher 教师 varchar 10 Y

课程表 Course加上职称字段。这样，职称依赖于教师，教师又依赖这同样会存在数据冗余、更新异常、插入异常和删除异常的情况

鲍依斯 -科得范式（ BCNF ）：

在第三范式的基础上，数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖则符合第三范式。

假设仓库管理关系表为 StorehouseManage(仓库 ID, 存储物品 ID, 管理员 ID, 数量 ) ，且有一个管理员只在一个仓库工作；一个仓库可以存储多种物品。这个数据库表中存在如下决定关系：

(仓库 ID, 存储物品 ID) →(管理员 ID, 数量 ) (管理员 ID, 存储物品 ID) → (仓库 ID, 数量 )

所以， (仓库 ID, 存储物品 ID) 和 (管理员 ID, 存储物品 ID) 都是 StorehouseManage 的候选关键字，表中的唯一非关键字段为数量，它是符合第三范式的。但是，由于存在如下决定关系：

(仓库 ID) → (管理员 ID) 管理员 ID) → (仓库 ID)

即存在关键字段决定关键字段的情况，所以其不符合 BCNF 范式。它会出现如下异常情况：

(1) 删除异常：

当仓库被清空后，所有 "存储物品 ID" 和 "数量 "信息被删除的同时， "仓库 ID" 和 "管理员 ID" 信息也被删除了。

(2) 插入异常：

当仓库没有存储任何物品时，无法给仓库分配管理员。

(3) 更新异常：

如果仓库换了管理员，则表中所有行的管理员 ID 都要修改。

把仓库管理关系表分解为二个关系表：

仓库管理： StorehouseManage(仓库 ID, 管理员 ID) ；

仓库： Storehouse(仓库 ID, 存储物品 ID, 数量 ) 。

这样的数据库表是符合 BCNF 范式的，消除了删除异常、插入异常和更新异常。

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

6.6 SQL 依赖关系

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

绑定到架构的依赖关系

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

非绑定到架构的依赖关系

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

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

下面的示例说明存储过程 X 如何依赖于存储过程 Y 。

USE tempdb;GOCREATE PROCEDURE dbo.Y ASSELECT * FROM sys.objectsGOCREATE PROCEDURE dbo.X as EXEC dbo.Y;GO

若要查看 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

6.7 多对多关系

将多对多关系映射到数据库关系图中

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

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

多对多关系至少需要 3 个表，我们把一个表叫做主表，一个叫做关系表，另外一个叫做字典表或者副表

按照数据库的增删查改操作，多对多关系的查找都可以用 inner join 或者select * from 主表 where id in (select 主表 id from 关系表 )

LOGO