关系数据库原理关系的完整性和规范化.pptx

SQL Server 2005SQL Server 2005第1章 关系数据库原理关系的完整性和关系的规范化主讲人：贺莹第3讲2第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲复习什么是关键字/码/键？什么是主关键字/主码/主键？ER图转化为关系模式的原则是什么？答：是能用来惟一标识元组的属性或属性组合。答：被指定为关键字的候选关键字，称为。答：（1）每个实体转换为一个关系。每个实体转换为一个关系。（2 2）每个联系也转换成一个关系。）每个联系也转换成一个关系。（3 3）有相同码的关系可以合并。）有相同码的关系可以合并。3第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲复习关系应具备什么性质？答：（答：（1 1）关系中每一个属性值都是）关系中每一个属性值都是不可分解不可分解的。的。（2 2）关系中不允许出现相同的行。）关系中不允许出现相同的行。（3 3）关系中不允许出现相同的列。）关系中不允许出现相同的列。（4 4）关系中没有行序、没有列序。）关系中没有行序、没有列序。4第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲第1章 关系数据库原理 关系的完整性规则、规范化教学内容1.关系的完整性规则uu实体完整性、参照完整性、域完整性实体完整性、参照完整性、域完整性2.函数依赖uu完全函数依赖、部分函数依赖、传递函数完全函数依赖、部分函数依赖、传递函数依赖依赖3.关系的规范化uu第一范式、第二范式、第三范式第一范式、第二范式、第三范式5第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲第1章 关系数据库原理 关系的完整性规则、规范化学习目标认知目标：uu理解关系的完整性规则理解关系的完整性规则uu了解函数依赖、关系规范化的含义了解函数依赖、关系规范化的含义uu理解三种范式的定义理解三种范式的定义能力目标:uu能判别关系的范式类别能判别关系的范式类别 (重点、难点重点、难点)uu能进行范式的分解（重点、难点）能进行范式的分解（重点、难点）6第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲一、关系的完整性规则定义定义u是对关系的某种约束条件。目的u用于保证关系数据库中数据的正确性和可靠性。类型u实体完整性规则u参照完整性规则（引用完整性规则）u域完整性规则（用户自定义完整性规则）是关系模型必须满足的完整性约束条件。是关系模型必须满足的完整性约束条件。7第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲一、关系的完整性规则1.实体完整性规则规则要求：规则要求：uu在任何关系的任何一个元组中，主键的值在任何关系的任何一个元组中，主键的值不能为空不能为空值、也不能取重复的值值、也不能取重复的值。目的：用于保证数据库表中的每一个元组都是惟目的：用于保证数据库表中的每一个元组都是惟一的。一的。请思考：请思考：下面的关系是否违反实体完整性规则？下面的关系是否违反实体完整性规则？学号姓名性别年龄系编号03001马力刚男210103102王萍华女200203223王平男2103张华男220403001李萍女19058第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲一、关系的完整性规则 2.域完整性规则(用户定义完整性规则)规则要求uu由用户根据实际情况，定义表中属性的取值由用户根据实际情况，定义表中属性的取值范围范围vv例如：性别只能是男和女、年龄不能为负值、成例如：性别只能是男和女、年龄不能为负值、成绩在绩在0 0100100之间等。之间等。目的uu用于保证给定字段中数据的有效性用于保证给定字段中数据的有效性,即保证数即保证数据的取值在有效的范围内。据的取值在有效的范围内。9第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲一、关系的完整性规则 3.参照完整性规则（引用完整性规则）规则要求：uu“不引用不存在的实体不引用不存在的实体”。即：不允许在一。即：不允许在一个关系中引用另一个关系中不存在的元组。个关系中引用另一个关系中不存在的元组。目的uu用于确保相关联的表间的数据保持一致。用于确保相关联的表间的数据保持一致。10第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲一、关系的完整性规则 3.参照完整性规则（引用完整性规则）请思考：请思考：下面两个关系是否违反参照完整性规则？下面两个关系是否违反参照完整性规则？系表（主表）系表（主表）学生表（从表）学生表（从表）说明说明uu从表的从表的“系编号（外键）系编号（外键）”的取值只能为两种情的取值只能为两种情况：况：vv若取非空值，则它必须是主表中若取非空值，则它必须是主表中存在的值存在的值。vv取空值取空值(null)(null)。表明尚未给学生分配专业。表明尚未给学生分配专业。NullNull不等于不等于0 0或空字或空字符串。符串。学号姓名性别年龄系编号03001马力刚男210103102王萍华女20 03223王平男2105系编号系名系主任办公室电话01计算机龚小勇205600302通信谭中华207602503电子袁勇210601811第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲二、函数依赖1.概念、类型函数依赖函数依赖uu是指关系中各属性或属性组之间的相互依赖关系。它是关系是指关系中各属性或属性组之间的相互依赖关系。它是关系规范化的理论基础。规范化的理论基础。函数依赖的定义函数依赖的定义uu给定一个关系模式给定一个关系模式R R，X X和和Y Y都是都是R R的属性或属性组，对于的属性或属性组，对于X X的每的每个确定值在任何时刻个确定值在任何时刻Y Y只有一个确定的值与之对应，则称只有一个确定的值与之对应，则称“X X函数决定函数决定Y Y”，或称为，或称为“Y Y函数依赖于函数依赖于X X”，X X称为决定因素，称为决定因素，记为：记为：XYXY。函数依赖的类型函数依赖的类型uu完全函数依赖完全函数依赖uu部分函数依赖部分函数依赖uu传递函数依赖传递函数依赖12第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲二、函数依赖2.完全函数依赖定义uu如果属性如果属性Y Y函数依赖于函数依赖于复合属性复合属性X X，且不与，且不与X X的的任何子集函数相依赖，则称任何子集函数相依赖，则称“Y Y完全函数依赖完全函数依赖于于X X”。示例uu有如下的关系模式有如下的关系模式:生产生产(工厂编号工厂编号,产品编号产品编号,计划数量计划数量)“工厂编号工厂编号+产品编号产品编号”“计划数量计划数量”，是完全函，是完全函数依赖。数依赖。13第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲二、函数依赖3.部分函数依赖（局部依赖）定义uu对于一个函数依赖：对于一个函数依赖：XYXY，如果存在，如果存在ZXZX（Z Z属于属于X X），且有），且有ZYZY成立，则称成立，则称“Y Y部分函数依部分函数依赖于赖于X X”。示例uu有如下的关系模式有如下的关系模式 SCD(SCD(学号，姓名，课程号，成绩，系名，系主任学号，姓名，课程号，成绩，系名，系主任)vv主码为复合码：学号主码为复合码：学号+课程号课程号vv“姓名、系名、系主任姓名、系名、系主任”与主码是部分函数依赖与主码是部分函数依赖14第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲二、函数依赖4.传递函数依赖定义uu在关系模式中，如果在关系模式中，如果XY(XY(不能不能YX)YX)，YZYZ，若存在若存在XZ XZ，则称，则称“Z Z传递依赖于传递依赖于X X”。示例uu有如下的关系模式有如下的关系模式 SDSD（学号学号，姓名，系名，系主任），姓名，系名，系主任）“学号学号系名，系名系名，系名系主任系主任”，则系主任传递依赖，则系主任传递依赖于学号。于学号。15第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲三、关系的规范化引uu在关系数据库的设计过程中，对于同一个问题，在关系数据库的设计过程中，对于同一个问题，选用不同的关系模式，其性能的优劣是大不相选用不同的关系模式，其性能的优劣是大不相同的，为了区分关系模式的优劣，人们常常把同的，为了区分关系模式的优劣，人们常常把关系模式分为各种不同等级的范式。关系模式分为各种不同等级的范式。16第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲三、关系的规范化1.概念和范式的类型类型uu第一范式第一范式(1NF)(1NF)uu第二范式第二范式(2NF)(2NF)uu第三范式第三范式(3NF)(3NF)关系规范化的概念uu将低级范式转换为若干个高级范式过程。将低级范式转换为若干个高级范式过程。低 高17第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲三、关系的规范化2.第一范式(1NF)定义uu如果关系模式如果关系模式R R的所有属性的值域中第一个值都是的所有属性的值域中第一个值都是不可不可再分解值再分解值，则称，则称R R是属于是属于1NF1NF模式。模式。uu第一范式是对关系的最低要求。第一范式是对关系的最低要求。示例示例:判断哪一张表是判断哪一张表是1NF1NF？18第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲三、关系的规范化2.第一范式(1NF)第一范式可能存在的问题：第一范式可能存在的问题：uu数据冗余：如各位老师的信息重复。数据冗余：如各位老师的信息重复。uu插入异常：如果要插入刘老师的个人信息，但刘老师未开插入异常：如果要插入刘老师的个人信息，但刘老师未开课，会造成缺关键字课，会造成缺关键字“学号学号”、“课程号课程号”。uu删除异常：当要删除删除异常：当要删除 课程号课程号=“C3C3”的元组，会丢失李老的元组，会丢失李老师的信息。师的信息。uu修改量大。修改量大。838377775757858578789090成绩成绩2-142-143434王王C1C104041-011-014545周周C2C201011-011-014545周周C2C203033-153-152727李李C3C303032-142-143434王王C1C102022-142-143434王王C1C10101办公室办公室教师年龄教师年龄教师教师课程号课程号学号学号19第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲三、关系的规范化3.第二范式(2NF)定义定义uu如果关系模式如果关系模式R R为第一范式，且为第一范式，且R R中中每个每个非主属性非主属性完全完全函数依赖函数依赖于于R R的主码的主码(复合码复合码)，则称，则称R R为为2NF2NF模式。模式。示例示例uu下列关系模式是下列关系模式是1NF1NF，是否是，是否是2NF2NF？为什么？为什么？SCDSCD（学号学号，姓名，姓名，课程号课程号，成绩，系名，系主任），成绩，系名，系主任）vv主码为复合码：学号主码为复合码：学号+课程号课程号解决方法：消除部分函数依赖。解决方法：消除部分函数依赖。不是。因为：不是。因为：“姓名、系名、系主任姓名、系名、系主任”与主码是部分函数依与主码是部分函数依赖（即：只依赖于学号）赖（即：只依赖于学号）20第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲三、关系的规范化3.第二范式(2NF)分解为分解为2NF2NF的方法：的方法：uu把把关关系系模模式式中中对对主主码码完完全全函函数数依依赖赖的的非非主主属属性性与与决决定定它们的主码放在一个关系模式中。它们的主码放在一个关系模式中。uu把把对对主主码码部部分分函函数数依依赖赖的的非非主主属属性性和和决决定定它它们们的的主主属属性放在一个关系模式中。性放在一个关系模式中。uu检查分解后的新模式，如果仍不是检查分解后的新模式，如果仍不是2NF2NF，则继续按照前，则继续按照前面的方法进行分解，直到达到要求。面的方法进行分解，直到达到要求。关系模式关系模式SCDSCD的分解结果如下：的分解结果如下：uuSCSC（学号学号，课程号课程号，成绩），成绩）uuSDSD（学号学号，姓名，系名，系主任），姓名，系名，系主任）21第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲三、关系的规范化4.第三范式(3NF)定义uu如果关系模式如果关系模式R R为第二范式，且为第二范式，且R R中每个非主属中每个非主属性都性都不传递函数依赖于传递函数依赖于R R的某个候选码，则称的某个候选码，则称R R为为3NF3NF模式。模式。示例uu下列关系模式是下列关系模式是2NF2NF，是否为，是否为3NF3NF？为什么？为什么？SDSD（学号学号，姓名，系名，系主任），姓名，系名，系主任）解决方法：消除传递函数依赖。不是。因为：学号不是。因为：学号系名，系名系名，系名系主任，存在传递依赖关系。系主任，存在传递依赖关系。22第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲三、关系的规范化4.第三范式(3NF)分解为分解为3NF3NF的方法：的方法：uu把把直直接接对对主主码码函函数数依依赖赖的的非非主主属属性性与与决决定定它它们们的的主主码码放在一个关系模式中。放在一个关系模式中。uu把把造造成成传传递递函函数数依依赖赖的的决决定定因因素素连连同同被被它它们们决决定定的的属属性放在一个关系模式中。性放在一个关系模式中。uu检检查查分分解解后后的的新新模模式式，如如果果不不是是3NF3NF，则则继继续续按按照照前前面面的方法进行分解，直到达到要求。的方法进行分解，直到达到要求。关系模式关系模式SDSD的分解结果如下：的分解结果如下：uuSDSD（学号学号，姓名，系名），姓名，系名）uuSDSD（系名系名，系主任），系主任）23第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲三、关系的规范化5.各范式间的关系各范式之间的关系各范式之间的关系 24第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲四、综合应用实例：实例：假设某商业集团数据库中有假设某商业集团数据库中有1 1个关系模式个关系模式R(R(商商店编号店编号,商品编号商品编号,数量数量,部门编号部门编号,负责人负责人)。如。如果规定：果规定：(1)(1)每个商店的每种商品只在一个部门销售。每个商店的每种商品只在一个部门销售。(2)(2)每个商店的每个部门只有一个负责人。每个商店的每个部门只有一个负责人。(3)(3)每个商店的每种商品只有一个库存数量。每个商店的每种商品只有一个库存数量。1.写出关系模式写出关系模式R R的基本函数依赖集的基本函数依赖集2.找出关系模式找出关系模式R R的候选码。的候选码。3.关系模式关系模式R R最高已经达到第几范式？为什么？最高已经达到第几范式？为什么？4.如果如果R R不属于不属于3NF3NF，请将，请将R R分解成分解成3NF3NF。25第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲四、综合应用分析与解答：1.基本的函数依赖有：uu(商店编号商店编号+商品编号商品编号)部门编号部门编号uu(商店编号商店编号+部门编号部门编号)负责人负责人uu(商店编号商店编号+商品编号商品编号)数量数量2.由题意,上面的函数依赖可知“商店编号”是主属性，商品编号也是主属性，由这两个属性，可以确定关系元组的所有属性，所以R的候选码是：商店编号+商品编号。26第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲四、综合应用分析与解答：3.R(商店编号,商品编号,数量,部门编号,负责人)最高已达到2NF。因为负责人是码的传递依赖。4.要达到3NF，必须消除非主属性对码的传递依赖。对R分解后的两个模式如下：uuR1(R1(商店编号，商品编号商店编号，商品编号，数量，部门编号，数量，部门编号)uuR2(R2(部门编号部门编号，负责人，负责人)27第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲本课小结1.关系的完整性规则uu实体完整性实体完整性/参照完整性参照完整性/域完整性域完整性2.范式的类型uu1NF1NF、2NF2NF、3NF3NF3.各范式间的关系:uu1NF1NF中的每个属性值是不可再分的，它是关系模式的中的每个属性值是不可再分的，它是关系模式的最低要求。最低要求。uu消除消除1NF1NF中的部分函数依赖，可转换为中的部分函数依赖，可转换为2NF2NF。uu消除消除2NF2NF中的传递函数依赖，可转换为中的传递函数依赖，可转换为3NF3NF。28第1章 关系数据库原理关系的完整性规则、关系的规范化第3讲课余作业布置课外练习课外练习1.1.教材教材P25P25：9 92.2.补充：设关系模式补充：设关系模式SCT(SCT(学号，课程号，成绩，教师学号，课程号，成绩，教师名，教师地址名，教师地址)。如果规定：。如果规定：(1)(1)每个学生每学一门每个学生每学一门课程只有一个成绩；课程只有一个成绩；(2)(2)每门课只有一个教师任教；每门课只有一个教师任教；(3)(3)每个教师只有一个地址（无同名教师）。每个教师只有一个地址（无同名教师）。vv写出关系模式写出关系模式SCTSCT的基本函数依赖集。的基本函数依赖集。vv找出关系模式找出关系模式SCTSCT的候选码。的候选码。vv试把试把SCTSCT分解成分解成2NF2NF模式集，并说明理由。模式集，并说明理由。vv试把试把SCTSCT分解成分解成3NF3NF模式集，并说明理由。模式集，并说明理由。