数据库系统原理-6

数据库系统原理第六章关系数据理论黄穗副教授主讲暨南大学信息学院计算机系2/40【教学目标】

通过观察关系模式产生的四种典型问题了解规范化的必要性。掌握函数依赖的分析方法，掌握范式判断的标准和分析方法。掌握范式分解的基本步骤。

【教学重点】函数依赖分析的步骤和方法，各个范式的判断标准，范式分解方法。【教学难点】函数依赖分析，范式标准的掌握与运用。范式分解所产生的问题。3/40第一节、规范化问题【教学目标】了解规范化理论针对的主要问题了解关系模式存在的函数依赖及分析方法教学进度4/40引言

解决数据库的逻辑设计问题，即面对一个具体的应用需要，应当构造几个关系表？每个表中设置哪些属性？如何评价现有的关系模式？设计不当会造成哪些问题？关系数据理论的三大内容

数据依赖、范式分析、模式设计。数据依赖概念

关系中诸属性数据之间内在、固有的关联性，是语义的体现。分析数据依赖应从客观事物本身的规律和习惯着手，着重语义的分析与理解，不偏信部分数据。数据依赖中包括函数依赖和多值依赖。掌握函数依赖部分。函数依赖—象自变量与因变量一样的对应关系。例：公司与董事长（1-1），学号与姓名（1-m）。课程与老师（m-n），学号与选课（m-n）。5/40关系模式的四类问题

数据冗余、插入异常、删除异常、修改复杂。例1：S(sno,sname,ssex,course,grade)关系模式中各属性分别代表学号、姓名、性别、课程和成绩。

数据冗余以选课数目为基数，重复sno和sname；

插入异常若某学生尚未选课，但无法插入表中；

删除异常若需要删除某学生的成绩记录，则全失；

修改复杂若修改学生姓名，则要修改所有相关记录。恰当的关系模式设计应当是：

S(sno,sname,ssex)，C(sno,course,grade)。仍然有冗余。例2：教师/教室及其使用情况的数据定义如下关系模式：R(职工号，姓名，性别，年龄，职称，教室号，类型，座位数，使用时间)

6/40存在的问题？冗余？插入？修改？删除？应当划分成几个表？R1(职工号，姓名，性别，年龄，职称)R2(教室号，类型，座位数)R3(职工号，教室号，使用时间)(码）7/40第二节、规范化【教学目标】掌握函数依赖分析方法掌握范式判断标准掌握范式分解方法8/40根据函数依赖的定义，可以找出下面规律：①在一个关系模式中，如果属性X、Y有1：1联系，则存在函数依赖XY、YX，可记作XY。设X，Y为关系中的属性或属性组，它们的所有可能取值组成两个集合。如果对于X中的任一具体值Y中至多有一个值与之对应，称X，Y这两个属性之间是一对一联系。例：在图书关系中，借书证号是唯一的，如果读者没有重名的，姓名与借书证号两个属性之间是1：1联系。另外，一本书有若干副本，它们有相同的书名、作者、分类号等，但每本书有唯一的书编号。书名与书编号之间是1：m②如果属性X、Y是1：m的联系，则存在函数依赖YX，但XY。③如果属性X、Y是n：m的联系，则X与Y之间不存在任何函数依赖。9/40几个函数依赖的概念

函数依赖的记号：XY。非平凡的函数依赖、平凡的函数依赖、决定因素（自变量）、完全函数依赖、部分函数依赖、传递函数依赖（注意与直接依赖区别）。几个关键术语

码（主码、候选码）、外码、全码、主属性、非主属性。

K为R＜U，F＞中的属性或属性组合，若K

fU，则K为R的候选码，候选码多于一个时选定其中一个为主码，主码通常就是属性个数最少的那个候选码。10/40码（关键字）定义：在关系模式R（U）中，K是U中的属性或属性组。如果K—U，则称K为关系R（U）的一个候选关键字。R（U）中若有一个以上的候选关键字，则选定其中一个作为主关键字。如果K是属性组，可称为组合关键字，或合成关键字。包含在任意一个候选关键字中的属性，称为主属性。不包含在任何候选关键字中的属性称为非主属性。11/40候选码具有的两个性质：(1)标识的唯一性：对于R（U）中的每一个元组，K的值确定后，该元组就相应确定了。(2)无冗余性：当K是属性组的情况下，K的任何一部分都不能唯一标识该元组。这是定义中的完全函数依赖的意义。例：确定下述关系的函数依赖、码，主属性和非主属性

1、人员（员工号，姓名，性别，部门号，工资号）依赖：员工号姓名、性别部门号决定工作部门，工资号决定收入。码：员工号非属性：姓名、性别、部门号、工资号

2、学生（学号，姓名，性别，身份证号，班级号，宿舍）依赖：学号姓名、性别、身份证号、班级号、宿舍码：学号、身份证号（假如身份证号不重复）12/403、借书（借书证号，姓名，性别，书名，书号，分类号，出版社）依赖：借书证号姓名、性别书号书名、分类号、出版社码：（借书证号，书号）4、化验单（科室，姓名，化验项目，化验时间，结果，正常值）依赖：化验项目正常值（科室，姓名，化验项目，化验时间）结果码：（科室，姓名，化验项目，化验时间）5、供应（商户编号，商户名称，商户所在地，供应零件号，供应量）商户编号商户名称、商户所在地（商户编号，供应零件号）供应量码：（商户编号，供应零件号）6、送货（顾客名，顾客地址，商品名，供应商名，供应商地址）依赖：顾客地址顾客名码：（供应商名，供应商地址，商品名，顾客地址）顾客名顾客地址商品名供应商名供应商地址陈牛津中山一路122号405房电视机H23－T海尔中贸城702杜子腾陇西街33号之一102房冰箱BCD233H西门子和平街266号1203费红忠江城南路255号306房洗衣机JD690金羚金顺商贸城210913/40范式的关系

INF2NF3NFBCNF4NF5NF。通过模式分解可将低级范式转换为高级范式。分解后的模式与原来的模式存在三种关系：

A、分解具有无损连接；

B、分解保持函数依赖；

C、分解既要无损连接，又要保持函数依赖。1NF2NF3NF4NF、BCNF各级范式的关系14/40第一范式（INF）每一个分量必须是不可分的数据项。例1：科室（科室编号、科室名称、科室成员、科主任）是1NF吗？不是1NF，因为“科成员”属性设置不当（科成员包含科主任），为什么不认为“科主任”设置不当呢？若将关系模式改成：科室（科室编号、科室名称、科室成员）合适吗？例2：如何将下述数据规范成1NF？15/40第一种方法是重复存储职工号和姓名。第二种方法是保留职工号的关键字地位，把电话号码拆分成单位电话和住宅电话两个属性。第三种方法是保留职工号的关键字地位。维持原模式不变，但强制每个元组只能录入一个电话号码。以上三种选择，第一种最不可取，后两种选择可根据应用需要确定一种。16/40部分依赖的例子例:关系模式SLC(Sno,Sdept,Sloc,Cno,Grade)Sloc为学生住处，假设每个系的学生住在同一个地方。函数依赖包括：

(Sno,Cno)fGrade

Sno→Sdept(Sno,Cno)PSdept部分依赖

Sno→Sloc(Sno,Cno)PSloc部分依赖

Sdept→Sloc17/40SLC的码为(Sno,Cno)非主属性Sdept和Sloc部分函数依赖于码(Sno,Cno)SLC为第1范式。SnoCnoGradeSdeptSlocSLC关系18/40SLC不是一个好的关系模式(1)插入异常 假设Sno＝95102，Sdept＝IS，Sloc＝N的学生还未选课，因课程号是主属性，因此该学生的信息无法插入SLC。(2)删除异常假定某个学生本来只选修了3号课程这一门课。现在因身体不适，他连3号课程也不选修了。因课程号是主属性，此操作将导致该学生信息的整个元组都要删除。19/40SLC不是一个好的关系模式(3)数据冗余度大如果一个学生选修了10门课程，那么他的Sdept和Sloc值就要重复存储了10次。(4)修改复杂例如学生转系，在修改此学生元组的Sdept值的同时，还可能需要修改住处（Sloc）。如果这个学生选修了K门课，则必须无遗漏地修改K个元组中全部Sdept、Sloc信息。20/40原因

Sdept、Sloc部分函数依赖于码。解决方法

SLC分解为两个关系模式，以消除这些部分函数依赖

SC（Sno，Cno，Grade）

SL（Sno，Sdept，Sloc）21/402NF的定义

若关系模式R∈1NF，并且每一个非主属性都完全函数依赖于R的码，则R∈2NF。 前面的例子：

SLC(Sno,Sdept,Sloc,Cno,Grade)∈1NFSLC(Sno,Sdept,Sloc,Cno,Grade)∈2NFSC（Sno，Cno，Grade）至少∈2NFSL（Sno，Sdept，Sloc）至少∈2NF22/40采用投影分解法将一个1NF的关系分解为多个2NF的关系，可以在一定程度上减轻原1NF关系中存在的插入异常、删除异常、数据冗余度大、修改复杂等问题。将一个1NF关系分解为多个2NF的关系，并不能完全消除关系模式中的各种异常情况和数据冗余。关键是寻找码，分清主属性与非主属性，然后判断有无部分函数依赖的情况，最后进行分解。注意：码—既包括主码和也含候选码。两个推论：1、全码情况必符合2NF；2、码均为单一属性，必符合2NF。23/402NF例子例1、工程预算（工程号、材料号、数量、单价、开工日期、完工日期）。确定依赖关系、码、范式，并将模式分解为更高的范式。工程号材料号数量单价开工日期完工日期A023001JT0120105.602－5－2102－6－30A023001BR03292126.502－5－2102－6－30B030233TJ03561526.7003－8－103－12－3C026780CE1266320002－6－702－6－1824/40依赖分析：工程号开工日期、完工日期，材料号单价。（工程号，材料号）数量码：（工程号，材料号）部分依赖：开工日期、完工日期与工程号，单价与材料号。属于1NF。存在的问题：工程暂时不用材料不能录入。宜分解为：（工程号、开工日期、完工日期）（材料号、单价）和（工程号、材料号、数量）例2、已知R={S,D,I,B,O,}有依赖F{SD,IB,BO,OI}。分析码、函数依赖、范式，并合理地分解模式。码(S，I)、(S，B)、(S，O)，D部分依赖于S。属于1NF分解为：(S，D)、（S，I）、（I，B，O）25/40例3、设有关系模式选课SCl(S#，C#，GRADE，CREDIT)其中，S#表示学号，C#表示课程号，GRADE表示成绩，CREDIT表示学分。S#C#GRADECREDIT

S#或C#都不能单独确定成绩GRADE。GRADE只能由属性组合(S#，C#)来确定。课程学分CREDIT是C#决定的，C#CREDIT。由此可知：因此存在部分依赖只属于INF。分解为：

R1（S＃，C＃，GRADE）

R2（C＃，CREDIT）(S#，C#)fGRADE(S#，C#)p

CREDIT26/403NF定义：若关系模式R∈2NF，且关系模式R（U，F）中的所有非主属性对任何候选码都不存在传递依赖，则称此关系模式只是第三范式的。记为R3NF。

每一个非主属性既不部分依赖于码，也不传递依赖于码。

在2NF基础上查验有无传递依赖，注意区别直接依赖。例：关系T（课程名、教师名、教师职称）设：一课一师。依赖：课程名教师名，教师名教师职称码：课程名，传递依赖：课程名与教师职称。2NF。分解为（课程名、教师名）和（教师名、职称）考虑：一课多师？关系名排课表？可排教师表？码：(课程名，教师名)，教师职称是部分依赖，1NF。

27/40关系W（工号、姓名、工种、定额）设：一人一工种。依赖：工号姓名，工种；工种定额。码：工号。存在传递依赖：工号定额。属于2NF。正确的分解方案：W1（工号、姓名、工种），

W2（工号、定额）设有关系模式仓库保管WPE(W#，P#，E#，QNT)其中各属性分别表示仓库号W#、器件号P#、职工号E#、数量QNT。一些具体实例如下页的表所示。它所反映的语义是：

(1)一个仓库有多个职工；

(2)一个职工仅在一个仓库工作；

(3)每个仓库里一种器件由专人负责，但一个人可以管理几种器件。28/40

29/40根据语义可以分析出以下函数依赖：(W#，P#)QNT，(W#，P#)E#，E#W#，(E#，P#)QNT

依赖图：E#P#W#QNT30/40部分函数依赖和传递函数依赖是产生存储异常的两个重要原因，3NF消除了大部分存储异常，具有较好的性能。但3NF并没有要求消除主属性对候选关键字的传递依赖，如果存在这种情况，3NF模式仍然可能发生存储异常现象。31/40BCNF

设R是一个关系模式，F是它的依赖集，R属于BCNF当且仅当其F中每个依赖的决定因素必定包含R的某个候选码。

判断一个模式是否BCNF：（1）所有非主属性对每一个候选码完全依赖；（2）所有主属性对每一个不包含它的候选码完全依赖；（3）没有任何属性完全依赖于非候选码的任何一组属性。例1：C(Cno,Cname,Pcno)码：Cno，无传递依赖，至少属于3NF，也属于BCNF。考虑：若某关系码是唯一的单属性，它符合BCNF的条件是什么？有无反例？若码是单属性，但有多个候选码，成为BCNF的条件又是什么？例2：SJP(学生,课程,名次)，无并列名次。码：（学生、课程）和（课程，名次）属于BCNF。因无非主属性，且主属性对码也是完全依赖。32/40

例3：关系STJ（学生、教师、课程）一师教一课。码：（学生、课程）和（学生，教师）。符合3NF。因(S,J)T,TJ不构成传递依赖！但不符合BCNF的条件。因为：有教师课程，所以教师是一个决定因素，但是它不是候选码，它单独不具备候选码资格。例4：关系R（城市、街道、邮编）码：(城市，街道)和(邮编，街道)，符合3NF，BCNF？不是！因为有邮编城市，邮编是一个决定因素，但它却不能单独成为码。33/40多值依赖与第四范式（4NF）多值依赖

给定关系模式R及其属性A和B，对于一给定的A值，就有一组B属性值与之对应，而与其它的属性（R-A-B）没有关系，则称“B多值依赖于A”或“A多值决定B”。记作：AB。多值依赖的特点：互补、增广、传递。例:学校中某一门课程由多个教师讲授，他们使用相同的一套参考书。关系模式Teaching(C,T,B)，其中：课程C、教师T和参考书B参考数据见下页的表格34/40普通物理学光学原理物理习题集普通物理学光学原理物理习题集数学分析微分方程高等代数数学分析微分方程高等代数…李勇李勇李勇王军王军王军李勇李勇李勇张平张平张平

…物理物理物理物理物理物理数学数学数学数学数学数学

…参考书B教员T课程C用二维表表示Teaching

35/40………课程C教员T参考书B

物理

数学

计算数学李勇王军

李勇张平

张平周峰

普通物理学光学原理物理习题集

数学分析微分方程高等代数

数学分析

Teach具有唯一候选码(C，T，B)，即全码。Teaching∈BCNF。Teaching模式中存在以下的问题。36/401、数据冗余度大：有多少教师，参考书就要存储多少次。2、插入操作复杂：当某一课程增加一名任课教师时，该课程有多少本参照书，就必须插入多少个元组，例如物理课增加一名教师刘关，需要插入两个元组：（物理，刘关，普通物理学）、（物理，刘关，光学原理）3、删除操作复杂：某一门课要去掉一本参考书，该课程有多少名教师，就必须删除多少个元组。4、修改操作复杂：某一门课要修改一本参考书，该课程有多少名教师，就必须修改多少个元组产生原因：存在多值依赖37/40

互补性：若XY，则XU-X-Y(U是全属性集)

增广性：若XY，V<W，则WYVY

传递性：若XY，YZ，则XZ-Y。

4NF

如果对于每个非平凡多值依赖XY，X都含有码，则称4NF。当依赖只包含函数依赖时，4NF就是BCNF。或者说4NF允许的非平凡的多值依赖实际上是函数依赖。

平凡多值依赖：若XY，而Z=U-X-Y为空。U为属性集合。则称Y平凡依赖于X。38/40对于任何没有说明的关联，作出适当的假设，建立合理的依赖关系。范式检验条件校正（规范化）第一范式（1NF）关系中没有非原子属性和嵌套关系。为每个非原子属性和嵌套