大学数据库系统原理《关系数据理论》案例教学课件

案例教学数据库系统原理·关系数据理论案例教学函数依赖·候选码·范式分解·规范化设计et课程导览01函数依赖基础从冗余异常案例引出数据依赖概念02码与范式体系逐级递进剖析1NF到4NF判定标准03算法与分解实践掌握最小依赖集与模式分解算法04综合案例演练通过完整案例贯通理论到应用CHAPTER01函数依赖基础属性之间的决定关系从一张问题表说起，理解数据依赖的本质单表堆砌埋下四大隐患一张表塞下学号、学院、院长、课程号、成绩，冗余与隐患随之而来学号→学院一个学生只属一个学院学院→院长一个学院只有一个院长学号+课程号→成绩学号加课程号共同决定成绩四大异常数据冗余：学院、院长随每条选课记录重复存储更新异常：换院长要改多行，漏改一处便自相矛盾插入异常：新学院尚无学生，主键学号为空无法录入删除异常：学生毕业删记录，学院与院长信息一并丢失函数依赖的形式化定义学号确定了，姓名就确定了，这正是函数依赖的直观表达关键术语决定因素箭头左边的属性集

X被决定因素箭头右边的属性集

Y语义要点函数依赖源于业务规则与数据语义，而非某组数据的巧合一组数据能观察到，不代表依赖成立，必须对所有实例成立例如姓名决定年龄，仅在规定无重名时才成立平凡依赖与非平凡依赖按Y是否被X包含，函数依赖分为两类。平凡函数依赖定义：若

Y包含于X，则X决定Y恒成立示例：学号加课程号决定学号非平凡函数依赖定义：若Y不包含于X，则称非平凡示例：学号加课程号决定成绩设计意义平凡依赖由自反性天然成立，不含额外语义信息规范化计算中，平凡依赖通常直接忽略真正反映属性间实质约束的，是非平凡依赖冗余分析与模式分解，核心处理的都是非平凡依赖VS完全依赖与部分依赖当决定因素由多个属性构成时，需判断依赖的完整程度。判断口诀：拿掉左部中某个属性，若仍能推出右部，则存在部分依赖。意义：部分依赖是

2NF

要消除的目标，也是数据冗余的重要来源。完全函数依赖定义：X决定Y，且X的任何真子集都不能决定Y示例：学号加课程号决定成绩，缺任一都不能确定成绩部分函数依赖定义：存在X的真子集，也能决定Y示例：学号加课程号决定姓名，但姓名只依赖学号VS传递依赖的链式推导若X决定Y，Y决定Z，且Y不决定X，则称Z对X传递函数依赖。间接决定传递依赖形成间接决定链条。链条示意学号→学院→院长冗余根源中间属性是合理实体，却把信息冗余带进学生表。对照案例学号、学院、院长同处一表时，院长信息随每名学生记录反复出现。消除目标它是

3NF

要消除的目标。传递依赖函数依赖集与闭包现实中一个关系模式往往含多条依赖，它们共同构成函数依赖集。自反律阿姆斯特朗公理若

Y包含于X，则

X决定Y。增广律阿姆斯特朗公理若

X决定Y，则

XZ决定YZ。传递律阿姆斯特朗公理若

X决定Y、Y决定Z，则

X决定Z。CHAPTER02码与范式体系衡量关系模式好坏的标准逐级消除部分依赖、传递依赖与多值依赖码家族：四种码的层级辨析超码、候选码、主码、全码四个概念，厘清码家族的最小性与选定关系超码能唯一标识元组的属性集允许含多余属性超码候选码不含多余属性的超码任意真子集都不能唯一标识候选码主码从多个候选码中选定一个作为主码主码全码整个属性组构成码全码主属性与非主属性确定候选码后，按是否被候选码包含来划分属性主属性包含在任意一个候选码中的属性只要出现在某一个候选码中，该属性就是主属性存在多个候选码时，它们的属性可能全部都是主属性例：候选码为

AB和CD

时，A、B、C、D全为主属性非主属性不包含在任何候选码中的属性判断依据：是否被候选码包含未被任何候选码覆盖即为非主属性举例候选码

AB和CD：A、B、C、D全为主属性主属性与非主属性的区分，是判断

2NF、3NF、BCNF

的前置条件，直接影响范式等级的结论VS候选码的快速判定规则快速判定口诀左边优先取，右边不取，两边不算数，组合再验证。按属性出现位置快速缩小候选码范围只在左边一定是候选码成员只在右边一定不是候选码两边不出现一定是候选码成员两边都出现可能进入候选码示例：依赖集含学号决定姓名、课程号决定课程名，两者都只出现在左边，必为候选码成员。候选码求解的三步算法先取必选属性，再补可选项，能显著减少枚举工作量1第一步求最小函数依赖集最小化依赖求最小函数依赖集2第二步按属性位置分类三类属性左类：仅左部属性集右类：仅右部属性集左右类：左右部均出现的属性集3第三步求闭包判定候选码计算左类属性闭包，若等于全集，则左类为唯一候选码若不等，将左类依次与左右类中属性组合，逐一求闭包闭包等于全集的组合即为候选码第一范式：属性的原子性关系模式的每个属性值都是不可再分的原子值，则该模式属于1NF。01反例工资可再分关系含姓名、工资两属性。工资又可再分为基本工资、奖金、补贴。不满足原子性，违反1NF。不满足1NF就不是真正的关系表02改造拆为独立属性把可再分的属性拆成独立属性。改为含姓名、基本工资、奖金、补贴四个属性。1NF是关系数据库的最低要求，是一张合格二维表的底线第二范式：消除部分依赖满足2NF属于1NF，且每个非主属性都完全函数依赖于候选码主码只由一个属性组成时，一定不存在部分依赖，必然属于2NF违反2NF（部分依赖）反面案例：成绩表含学号、课程号、成绩、学分主码为学号加课程号，但学分只依赖课程号，属部分依赖改造：按哪里违反拆哪里选课表（学号，课程号，成绩）课程表（课程号，学分）问题警示部分依赖会让同一课程学分反复存储，修改时易遗漏。第三范式：消除传递依赖传递依赖让中间实体的信息混入主表，造成系信息随学生记录重复存储。属于

2NF，且每个非主属性都不传递依赖于候选码，则属于

3NF学生表（违反3NF）字段：学号、姓名、系号、系名、系位置依赖：学号→系号，系号→系名、系位置系名、系位置

传递依赖

于学号，违反3NF改造方案（拆表）学生表：学号、姓名、系号院系表：系号、系名、系位置BC范式：决定因素必须是码属于1NF，对每个非平凡函数依赖，若其决定因素必含有候选码，则属于BCNF与3NF对比3NF：只约束非主属性BCNF：进一步约束主属性对候选码的部分依赖与传递依赖反例与结论反例：若存在依赖教师决定课程，而教师不是候选码的一部分，则不满足BCNF，需继续分解结论：BCNF消除了任何属性对码的部分依赖与传递依赖第四范式与多值依赖多值依赖：X固定时，Y的取值与其余属性无关，称X多值决定Y。4NF消除的是一对多且互不相关带来的冗余组合。案例：课程表含课程、教师、教材一门课有

2

位教师、3

本教材，教师与教材相互独立若不拆分，需存储

6

行组合数据，冗余明显4NF定义属于1NF，对每个非平凡多值依赖，其决定因素必含候选码改造：按相互独立的一对多关系拆表课程教师表课程，教师课程教材表课程，教材范式递进关系与工程权衡包含关系1NF⊃2NF⊃3NF⊃BCNF⊃4NF，高级必满足低级，反向不成立。范式递进从

1NF

到

4NF，范式等级逐级抬高，约束逐级加严。1NF第一范式消除非原子属性2NF第二范式消除非主属性对候选码的部分依赖3NF第三范式消除非主属性对候选码的传递依赖BCNFBC范式消除主属性对候选码的部分与传递依赖4NF第四范式消除非平凡多值依赖工程权衡实践中以

3NF

为平衡点，过度分解增加连接开销，必要时采用反规范化。CHAPTER03算法与分解实践从理论到可执行步骤最小依赖集求解与无损连接分解算法函数依赖集的等价与覆盖定义若两个函数依赖集

F与G

能互相推导出对方的所有依赖，则称F与G等价，也称互相覆盖。判断方法（两步走）2步第一步对

F

中每条依赖，判断其右部是否包含在左部关于

G

的闭包中第二步对

G

中每条依赖，判断其右部是否包含在左部关于

F

的闭包中要点3项等价意味着表达能力相同，而非逐条相同最小依赖集就是与F等价、且满足最简条件的一个覆盖一个依赖集可以对应多个等价的最小覆盖最小函数依赖集的三条件最小函数依赖集的三个条件右部单一每条依赖的右部只含一个属性无冗余依赖不存在某条依赖可由其余依赖推出左部无冗余属性不存在左部的真子集仍能决定右部意义01去掉形式上与语义上的多余成分02是求解候选码、进行模式分解的基础03定理：每个函数依赖集都等价于某个最小函数依赖集定理每个函数依赖集都等价于某个最小函数依赖集注意同一个依赖集的最小函数依赖集不一定唯一最小依赖集的求解步骤“口诀：先拆右，再去整条，再去左部，回头复查。”第一步分解右部把每条依赖的右部拆成单一属性例：学号决定姓名与学院，拆为学号决定姓名、学号决定学院。第二步消除冗余依赖去掉它后求左部闭包对每条依赖，从集中去掉它后求左部闭包，若右部仍被包含，则该依赖冗余可删。第三步消除左部冗余属性逐个去掉左部某属性后求闭包对左部多属性的依赖，逐个去掉左部某属性后求闭包，若右部仍被推出，则该属性冗余可删。第四步复查重复第二步若第三步改变了左部，需重复第二步。最小依赖集求解案例用一道完整例题演练最小覆盖三步法，验证每一步的判断依据题目属性集{A,B,C,D,E,G}依赖集见原题依赖集第一步

分解右部判断依据每条依赖的右部拆成单一属性第二步

去冗余依赖判断依据去掉它后求左部闭包，右部仍被包含则冗余可删删除结论一删除可由其余依赖推出的依赖删除结论二删除左部真子集仍能决定右部的属性第三步

去左部冗余判断依据逐个去掉左部某属性后求闭包，右部仍被推出则该属性冗余可删最终最小依赖集：结果横幅横跨全页模式分解的两大要求无损连接性自然连接还原分解后的子表经自然连接能完全还原原表。信息不丢失不丢失信息，不产生多余元组。最基本要求保持函数依赖性依赖并集等价分解后各子表上的依赖并集，与原依赖集等价。约束未丢失业务约束规则没有丢失。无损连接的表格判定法终止条件：扫描必然终止（符号数量有限）；出现整行统一符号即可提前结束并判定无损1第一步建表行对应子模式·列对应属性属性属于某子模式→填统一符号否则填带下标的符号2第二步按依赖修改左部相同·右部统一若若干行在左部属性上符号相同把这些行在右部属性上的符号统一3第三步反复扫描扫描直至判定某一行全部为统一符号→分解无损表格不再变化→分解有损二元分解的无损判定例：学号系名与系名系位置，公共属性系名决定系位置，分解无损。当关系只分解为两个子模式时，公共属性能否牵住至少一侧独有属性，就是最简便的判定。充分必要条件两子模式的公共属性能决定第一个子模式的独有属性或能决定第二个子模式的独有属性直观理解公共属性必须能牵住至少一侧的独有信息连接时才不会产生虚假元组保持依赖与3NF合成法保持依赖：分解后各子模式上的依赖并集，与原依赖集等价原依赖学号→系名系名→系位置两者均保留，业务一致分解后丢失依赖分解为学号系名与学号系位置虽无损却丢失系名→系位置破坏业务一致性，属未保持依赖结论：3NF总能做到既无损又保依赖；BCNF分解只保证无损，不保证保持依赖。VSBCNF无损分解算法目标：把关系模式分解到BCNF，且保持无损连接。1求原模式的候选码2逐一检查依赖若某依赖的左部不含候选码，则不满足BCNF3拆分把该依赖的左部与右部属性组成一个新子模式4剩余属性构成另一子模式去掉该右部属性后，并重新计算候选码5重复检查与拆分直到全部满足BCNF每拆一步都要验证无损性；BCNF分解不保证保持函数依赖。CHAPTER04综合案例演练贯通理论与工程实践从需求分析到规范化设计的完整闭环综合案例：教务系统需求一个学号只对应一名学生、一个学院一个学院只有一位院长、一个位置一门课只有名称与学分一个学生一门课只有一个成绩学生5项学号姓名性别年龄所在学院学院3项学院名院长学院位置课程3项课程号课程名学分选课1项某学生选修某课程的成绩从需求提炼函数依赖集业务规则→函数依赖4项学号→姓名、性别、年龄、学院名学号可唯一确定学生的基本属性与所属学院。学院名→院长、学院位置学院名可唯一确定院长与学院位置。课程号→课程名、学分课程号可唯一确定课程名与学分。学号+课程号→成绩学号与课程号联合可唯一确定成绩。传递依赖推导2项学号→学院名→院长由学号可推得学院名，再由学院名可推得院长。故

学号→院长

为传递依赖学号通过学院名间接确定院长。要点2项依赖来自语义，不能凭样本推断函数依赖由业务语义决定，不可仅凭样本数据推断。关系模式属性集学号、姓名、性别、年龄、学院名、院长、学院位置、课程号、课程名、学分、成绩判定初始模式的范式等级学号+课程号设定主码通过1NF所有属性均为原子值，满足1NF2NF检查姓名、性别、学院名等只依赖学号，不依赖课程号存在非主属性对候选码的部分依赖，违反2NF3NF检查即便部分依赖被解决，院长仍传递依赖学号，违反3NF结论初始单表同时存在部分依赖与传递依赖范式等级低于2NF，必须进行模式分解规范化分解方案学生表主码：学号字段：学号、姓名、性别、年龄、学院名学院表主码：学院名字段：学院名、院长、学院位置课程表主码：课程号字段：课程号、课程名、学分选课表主码：学号加课程号字段：学号、课程号、成绩分解结果的验证从范式等级、无损连接、保持依赖三方面验证分解方案的合理性范式等