实验0-关系规范化设计理论和方法.doc

资源描述

关系规范化设计理论和方法一．关系规范化设计理论函数依赖定义：设关系模式R（U，F），U是属性全集，F是U上的函数依赖集，X和Y 是U的子集。如果对于R（U）的任意一个可能的关系 r ，对于X的每一个具体值，Y都有唯一具体的值与之对应，则称 X函数决定 Y，或Y函数依赖于X，记为 X→Y。我们称X为决定因素，Y为依赖因素。当Y不函数依赖于X时，记作：XY。当X→Y且Y→X时，则记作：XY。范式在关系数据库的规范化过程中，为不同程度的规范化要求设立的不同的标准或准则称为范式（Normal Form）。满足最低要求的叫第一范式,简称1NF。在第一范式中满足进一步要求的为第二范式(2NF),其余以此类推。R为第几范式就可以写成R∈xNF（x表示某范式名）。一个低一级范式的关系模式，通过模式分解可以转换为若干个高一级范式的关系模式的集合，这种转换过程就叫规范化。第一范式定义：如果关系模式R所有的属性均为简单属性，即每个属性都是不可再分的，则称R属于第一范式，简称1NF，记作R∈1NF。第一范式（First Normal Form）是最基本的规范化形式，即关系中每个属性都是不可再分的简单项。第二范式定义：如果关系模式R∈1NF，R（U，F）中的所有非主属性都完全函数依赖于任意一个候选关键字，则称关系R 是属于第二范式（Second Normal Form），简称2NF，记作R∈2NF。从定义可知，满足第二范式的关系模式R中，不可能有某非主属性对某候选关键字存在部分函数依赖。第三范式定义: 如果关系模式R∈2NF，R（U，F）中所有非主属性对任何候选关键字都不存在传递函数依赖，则称R是属于第三范式（Third Normal Form），简称3NF，记作 R∈3NF。 3NF只限制了非主属性对码的依赖关系，而没有限制主属性对码的依赖关系。如果发生了这种依赖，仍有可能存在数据冗余、插入异常、删除异常和修改异常。这时，则需对3NF进一步规范化，消除主属性对码的依赖关系，向更高一级的范式BCNF转换。 BC范式定义：如果关系模式R∈1NF，且在所有的函数依赖 X→Y（Y不包含于X）中，决定因素X都包含了R的一个候选码，则称R属于BC范式（Boyce-Codd Normal Form）,记作 R∈BCNF。由BCNF的定义可以得到以下结论，一个满足BCNF的关系模式有：（1）所有非主属性对每一个候选码都是完全函数依赖。（2）所有的主属性对每一个不包含它的候选码都是完全函数依赖。（3）没有任何一组属性完全函数依赖于非码。二．关系规范化方法应用举例某机械厂有多个车间；每个车间有多名工人；每个车间生产多种产品和零件；一种产品由多个零件组成；产品和零件均存入仓库中，该厂有多个仓库。以下是该机械厂的详细情况表格：原始数据表格R0 职工号姓名性别工种车间号车间主任车间产品号产品价格产品存仓仓库主任组成产品的零件号零件重量零件制造车间零件存仓 0157 张三男钳工 03 李瑞 T-25 890.0 02 顾太原 A257 8.0 01 02 02 B236 12.0 02 01 C224 7.0 01 03 03 0165 李大朋女车工 02 王娜 T-47 4580.0 03 赵选发 A257 8.0 01 02 02 B548 5.0 02 01 C224 7.0 01 03 03 0184 王五男铣工 01 张雄伟 B-02 1954.0 02 顾太原 A257 8.0 01 02 02 B236 12.0 02 01 S-58 1800.0 01 陈自成 B548 5.0 02 01 C224 7.0 01 03 03 0244 刘小刚女车工 02 王娜 T-47 4580.0 03 赵选发 A257 8.0 01 02 02 B548 5.0 02 01 C224 7.0 01 03 03 这张表格察看起来非常方便，但它不符合1NF，存在以下问题：某些单元格存储的数据多于一个，在数据库中将无法实现这种存储格式；另外，这种表格使某些操作也无法完成。为了使R0∈1NF，对R0做纵向扩展，从而可有以下关系R1：某机械厂信息管理关系R1 职工号姓名性别工种车间号车间主任车间产品号产品价格产品存仓仓库主任组成产品的零件号零件重量零件制造车间零件存仓 0157 张三男钳工 03 李瑞 T-25 890.0 02 顾太原 A257 8.0 01 02 0157 张三男钳工 03 李瑞 T-25 890.0 02 顾太原 A257 8.0 02 02 0157 张三男钳工 03 李瑞 T-25 890.0 02 顾太原 B236 12.0 02 01 0157 张三男钳工 03 李瑞 T-25 890.0 02 顾太原 C224 7.0 01 03 0157 张三男钳工 03 李瑞 T-25 890.0 02 顾太原 C224 7.0 03 03 0165 李大朋女车工 02 王娜 T-47 4580.0 03 赵选发 A257 8.0 01 02 0165 李大朋女车工 02 王娜 T-47 4580.0 03 赵选发 A257 8.0 02 02 0165 李大朋女车工 02 王娜 T-47 4580.0 03 赵选发 B548 5.0 02 01 0165 李大朋女车工 02 王娜 T-47 4580.0 03 赵选发 C224 7.0 01 03 0165 李大朋女车工 02 王娜 T-47 4580.0 03 赵选发 C224 7.0 03 03 0184 王五男铣工 01 张雄伟 B-02 1954.0 02 顾太原 A257 8.0 01 02 0184 王五男铣工 01 张雄伟 B-02 1954.0 02 顾太原 A257 8.0 02 02 0184 王五男铣工 01 张雄伟 B-02 1954.0 02 顾太原 B236 12.0 02 01 0184 王五男铣工 01 张雄伟 S-58 1800.0 01 陈自成 B548 5.0 02 01 0184 王五男铣工 01 张雄伟 S-58 1800.0 01 陈自成 C224 7.0 01 03 0184 王五男铣工 01 张雄伟 S-58 1800.0 01 陈自成 C224 7.0 03 03 0244 刘小刚女车工 02 王娜 T-47 4580.0 03 赵选发 A257 8.0 01 02 0244 刘小刚女车工 02 王娜 T-47 4580.0 03 赵选发 A257 8.0 02 02 0244 刘小刚女车工 02 王娜 T-47 4580.0 03 赵选发 B548 5.0 02 01 0244 刘小刚女车工 02 王娜 T-47 4580.0 03 赵选发 C224 7.0 01 03 0244 刘小刚女车工 02 王娜 T-47 4580.0 03 赵选发 C224 7.0 03 03 现在，R1∈1NF 。 R1的一个候选键是：（职工号+产品号+零件号）。由于R1存在以下函数依赖：职工号→姓名，职工号→性别，职工号→工种，职工号→车间号，职工号→车间主任；产品号→价格；产品号→仓库号；产品号→仓库主任零件号→重量。在以上9个函数依赖中，依赖因素都是非主属性，而决定因素都是候选键的一部分。换句话说，R1中存在非主属性对候选键的部分函数依赖，故R1不满足2NF。可明显看出，R1存在大量数据冗余。必须通过模式分解将R1关系模式转换为若干个高一级范式的关系模式的集合——模式规范化处理。为了使关系模式R1向高一级范式转换，可将R1分解（做垂直分割，并保留相关的联系）为R2、R3、R4三个关系： R2 职工号姓名性别工种车间号车间主任车间产品号 0157 张三男钳工 03 李瑞 T-25 0157 张三男钳工 03 李瑞 T-25 0157 张三男钳工 03 李瑞 T-25 0157 张三男钳工 03 李瑞 T-25 0157 张三男钳工 03 李瑞 T-25 0165 李大朋女车工 02 王娜 T-47 0165 李大朋女车工 02 王娜 T-47 0165 李大朋女车工 02 王娜 T-47 0165 李大朋女车工 02 王娜 T-47 0165 李大朋女车工 02 王娜 T-47 0184 王五男铣工 01 张雄伟 B-02 0184 王五男铣工 01 张雄伟 B-02 0184 王五男铣工 01 张雄伟 B-02 0184 王五男铣工 01 张雄伟 S-58 0184 王五男铣工 01 张雄伟 S-58 0184 王五男铣工 01 张雄伟 S-58 0244 刘小刚女车工 02 王娜 T-47 0244 刘小刚女车工 02 王娜 T-47 0244 刘小刚女车工 02 王娜 T-47 0244 刘小刚女车工 02 王娜 T-47 0244 刘小刚女车工 02 王娜 T-47 R3 R4 车间产品号产品价格产品存仓仓库主任组成产品的零件号零件号零件重量零件制造车间零件存仓 T-25 890.0 02 顾太原 A257 A257 8.0 01 02 T-25 890.0 02 顾太原 A257 A257 8.0 02 02 T-25 890.0 02 顾太原 B236 B236 12.0 02 01 T-25 890.0 02 顾太原 C224 C224 7.0 01 03 T-25 890.0 02 顾太原 C224 C224 7.0 03 03 T-47 4580.0 03 赵选发 A257 A257 8.0 01 02 T-47 4580.0 03 赵选发 A257 A257 8.0 02 02 T-47 4580.0 03 赵选发 B548 B548 5.0 02 01 T-47 4580.0 03 赵选发 C224 C224 7.0 01 03 T-47 4580.0 03 赵选发 C224 C224 7.0 03 03 B-02 1954.0 02 顾太原 A257 A257 8.0 01 02 B-02 1954.0 02 顾太原 A257 A257 8.0 02 02 B-02 1954.0 02 顾太原 B236 B236 12.0 02 01 S-58 1800.0 01 陈自成 B548 B548 5.0 02 01 S-58 1800.0 01 陈自成 C224 C224 7.0 01 03 S-58 1800.0 01 陈自成 C224 C224 7.0 03 03 T-47 4580.0 03 赵选发 A257 A257 8.0 01 02 T-47 4580.0 03 赵选发 A257 A257 8.0 02 02 T-47 4580.0 03 赵选发 B548 B548 5.0 02 01 T-47 4580.0 03 赵选发 C224 C224 7.0 01 03 T-47 4580.0 03 赵选发 C224 C224 7.0 03 03 删除以上三个关系中的重复元组后，三个关系模式分别为： R2 职工号姓名性别工种车间号车间主任产品号 0157 张三男钳工 03 李瑞 T-25 0165 李大朋女车工 02 王娜 T-47 0184 王五男铣工 01 张雄伟 B-02 0184 王五男铣工 01 张雄伟 S-58 0244 刘小刚女车工 02 王娜 T-47 R3 R4 产品号产品价格产品存仓仓库主任组成产品的零件号零件号零件重量零件制造车间零件存仓 T-25 890.0 02 顾太原 A257 A257 8.0 01 02 T-25 890.0 02 顾太原 B236 A257 8.0 02 02 T-25 890.0 02 顾太原 C224 B236 12.0 02 01 T-47 4580.0 03 赵选发 A257 C224 7.0 01 03 T-47 4580.0 03 赵选发 B548 C224 7.0 03 03 T-47 4580.0 03 赵选发 C224 B548 5.0 02 01 B-02 1954.0 02 顾太原 A257 B-02 1954.0 02 顾太原 B236 S-58 1800.0 01 陈自成 B548 S-58 1800.0 01 陈自成 C224 R2的候选键是（职工号+产品号）。 R2存在以下函数依赖：职工号→姓名，职工号→性别，职工号→工种，职工号→车间号，职工号→车间主任；由于R2存在部分函数依赖，故R2不满足2NF。将R2分解为R5和生产表： R2 职工号姓名性别工种车间号车间主任产品号 0157 张三男钳工 03 李瑞 T-25 0165 李大朋女车工 02 王娜 T-47 0184 王五男铣工 01 张雄伟 B-02 0184 王五男铣工 01 张雄伟 S-58 0244 刘小刚女车工 02 王娜 T-47 R5 生产表职工号姓名性别工种车间号车间主任车间号产品号 0157 张三男钳工 03 李瑞 03 T-25 0165 李大朋女车工 02 王娜 02 T-47 0184 王五男铣工 01 张雄伟 01 B-02 0244 刘小刚女车工 02 王娜 01 S-58 显然，生产表∈2NF，且生产表∈3NF。生产表反映的是车间与产品之间的联系——车间生产产品。 R5满足2NF。 R5的候选键是“职工号”。故有：职工号→车间号，职工号→车间主任；但也存在：车间号→车间主任。即有：职工号→车间号，车间号→车间主任，即“车间主任”传递依赖于候选关键字“职工号”，即R5不满足3NF。将R5分解为工人表和车间表：工人表车间表职工号姓名性别工种车间号车间号车间主任 0157 张三男钳工 03 03 李瑞 0165 李大朋女车工 02 02 王娜 0184 王五男铣工 01 01 张雄伟 0244 刘小刚女车工 02 至此，R2被分解成了3个满足3NF的关系模式（工人表、车间表、生产表）。 R3的候选键是（产品号+零件号）。 R3存在以下函数依赖：产品号→产品价格，产品号→仓库号，产品号→仓库主任；故R3不满足2NF。将R3分解为R6和装配表： R3 产品号产品价格仓库号仓库主任零件号 T-25 890.0 02 顾太原 A257 T-25 890.0 02 顾太原 B236 T-25 890.0 02 顾太原 C224 T-47 4580.0 03 赵选发 A257 T-47 4580.0 03 赵选发 B548 T-47 4580.0 03 赵选发 C224 B-02 1954.0 02 顾太原 A257 B-02 1954.0 02 顾太原 B236 S-58 1800.0 01 陈自成 B548 S-58 1800.0 01 陈自成 C224 R6 装配表产品号产品价格产品存仓仓库主任产品号零件号 T-25 890.0 02 顾太原 T-25 A257 T-47 4580.0 03 赵选发 T-25 B236 B-02 1954.0 02 顾太原 T-25 C224 S-58 1800.0 01 陈自成 T-47 A257 T-47 B548 T-47 C224 B-02 A257 B-02 B236 S-58 B548 S-58 C224 显然，装配表∈2NF，且装配表∈3NF。装配表反映的是零件与产品之间的联系——零件装配成产品。 R6满足2NF，但不满足3NF，故再将R6分解为产品表和仓库表：产品表仓库表产品号产品价格仓库号仓库号仓库主任 T-25 890.0 02 02 顾太原 T-47 4580.0 03 03 赵选发 B-02 1954.0 02 01 陈自成 S-58 1800.0 01 至此，R3被分解成了3个满足3NF的关系模式（产品表、仓库表、装配表）。 R4的候选关键字是：（零件号+车间号），存在部分函数依赖，故将R4分解为零件表和制造表： R4 零件号零件重量车间号仓库号 A257 8.0 01 02 A257 8.0 02 02 B236 12.0 02 01 C224 7.0 01 03 C224 7.0 03 03 B548 5.0 02 01 零件表制造表零件号零件重量仓库号零件号车间号 A257 8.0 02 A257 01 B236 12.0 01 A257 02 C224 7.0 03 B236 02 B548 5.0 01 C224 01 C224 03 B548 02 显然，零件表∈3NF，制造表∈3NF。综合以上规范化结果，有以下8个关系：工人表职工号姓名性别工种车间号 0157 张三男钳工 03 0165 李大朋女车工 02 0184 王五男铣工 01 0244 刘小刚女车工 02 车间表车间号车间主任 03 李瑞 02 王娜 01 张雄伟产品表产品号产品价格仓库号 T-25 890.0 02 T-47 4580.0 03 B-02 1954.0 02 S-58 1800.0 01 仓库表仓库号仓库主任 02 顾太原 03 赵选发 01 陈自成生产表产品号车间号 T-25 03 T-47 02 B-02 01 S-58 01 零件表零件号零件重量仓库号 A257 8.0 02 B236 12.0 01 C224 7.0 03 B548 5.0 01 装配表产品号零件号 T-25 A257 T-25 B236 T-25 C224 T-47 A257 T-47 B548 T-47 C224 B-02 A257 B-02 B236 S-58 B548 S-58 C224 制造表零件号车间号 A257 01 A257 02 B236 02 C224 01 C224 03 B548 02 其中，生产表可合并到产品表中。则最终规范化结果为以下7个关系模式：工人表职工号姓名性别工种车间号 0157 张三男钳工 03 0165 李大朋女车工 02 0184 王五男铣工 01 0244 刘小刚女车工 02 车间表车间号车间主任 03 李瑞 02 王娜 01 张雄伟产品表产品号产品价格仓库号车间号 T-25 890.0 02 03 T-47 4580.0 03 02 B-02 1954.0 02 01 S-58 1800.0 01 01 仓库表仓库号仓库主任 02 顾太原 03 赵选发 01 陈自成零件表零件号零件重量仓库号 A257 8.0 02 B236 12.0 01 C224 7.0 03 B548 5.0 01 装配表产品号零件号 T-25 A257 T-25 B236 T-25 C224 T-47 A257 T-47 B548 T-47 C224 B-02 A257 B-02 B236 S-58 B548 S-58 C224 制造表零件号车间号 A257 01 A257 02 B236 02 C224 01 C224 03 B548 02 这7个关系模式均满足3NF，且满足BCNF。能否由以上的7个关系模式，得到“看起来更方便、清楚”的R1呢？实际上，按以下关系在数据库中建立7个表（并输入相应的数据，数据总个数为：2x6+5x4+3x4+2x10+2x3+4x4+2x3=92。）在查询分析器中，输入以下SQL语句： SELECT dbo.工人.职工号, dbo.工人.姓名, dbo.工人.性别, dbo.工人.工种, dbo.车间.车间号, dbo.车间.车间主任, dbo.产品.产品号 AS 车间产品号, dbo.产品.价格 AS 产品价格, dbo.仓库.仓库号 AS 产品存仓, dbo.仓库.仓库主任, dbo.装配.零件号 AS 组成产品的零件号, dbo.零件.重量 AS 零件重量, dbo.制造.车间号 AS 零件制造车间, dbo.零件.仓库号 AS 零件存仓 FROM dbo.装配 INNER JOIN dbo.零件 ON dbo.装配.零件号 = dbo.零件.零件号 INNER JOIN dbo.产品 ON dbo.装配.产品号 = dbo.产品.产品号 INNER JOIN dbo.仓库 ON dbo.产品.仓库号 = dbo.仓库.仓库号 INNER JOIN dbo.车间 ON dbo.产品.车间号 = dbo.车间.车间号 INNER JOIN dbo.工人 ON dbo.车间.车间号 = dbo.工人.车间号 INNER JOIN dbo.制造 ON dbo.零件.零件号 = dbo.制造.零件号 ORDER BY dbo.工人.职工号即可得R1（数据总个数为：14x21=294）。

展开阅读全文