1 / 82

第六章 关系数据库理论

第六章 关系数据库理论. 教学目标： 掌握函数依赖的概念 理解范式 如何判断、设计一个好的数据模式 *关系规范化（关系分解） 难点 : 关系规范化 课时： 4. 复习 : 侯选码 主码 主属性 非主属性 外码. 5.1 问题的提出. sname. 函数. sno. Y=x. Y = f(x) y 依赖于 x. y. 1. 张三. X. 3. Student(sno,sname,ssex) Ssex=f( sno) sno ssex Sname ssex X:sno Y:sname

marty
Download Presentation

第六章 关系数据库理论

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 第六章 关系数据库理论 教学目标： 掌握函数依赖的概念 理解范式 如何判断、设计一个好的数据模式 *关系规范化（关系分解） 难点: 关系规范化 课时：4

  2. 复习: • 侯选码 • 主码 • 主属性 • 非主属性 • 外码

  3. 5.1 问题的提出 sname • 函数 sno Y=x • Y = f(x) y 依赖于 x

  4. y 1 张三 X 3

  5. Student(sno,sname,ssex) • Ssex=f( sno) snossex • Snamessex • X:sno • Y:sname • F:{snossex,Snamessex, • snosname,snamesno} • 1 关系模式中的数据依赖 • R(U,F) • U:属性名集合 • F:属性间数据的依赖关系

  6. 2 数据依赖对关系的影响 • 学校数据库: • U={Sno,Sdept,Mname,Cname,Grade} • 1 SX aaa 数据库 90 • 1 SX aaa 数据结构 90 • 一个系有若干学生,一个学生仅属于一个系 • Sno  Sdept • 一个系只有一名系主任 • Sdept  Mname • 一个学生选修多门课程,每门课程有若干学生 • 每个学生所学的每门课程有一成绩 • (Sno,Cname)  Grade • F: {Sno Sdept,Sdept Mname, (Sno,Cname)  Grade

  7. 得到: student (U,F) • 此关系有4各问题: • 1. 数据冗余太大 • 2. 更新复杂 • 3.插入异常 • 4.删除异常 • 问题的产生: 数据依赖 • 职工工资（姓名，工资（工龄、职务）， • 扣除（水电、房租） ）

  8. 5.2 规范化 • 定义5.1. 函数依赖 • 定义4.1 ： • U{A1,A2 … An} 是属性集合，R(U) 是U上的一个关系，x,y是U的子集。若：对于R(U)下的任何一个可能的关系，均有x的一个值对应于y的唯一具体值，称y单值函数依赖于x, x 称决定因素，记为：Xy • 若 Xy 且 yx 记为: Xy • 若 y不函数依赖于X, 记为: Xy

  9. 例如：学生（学号，姓名，年龄，班级） • 01 张三 20 1 • 02 王二 21 1 • 03 李丽 21 1 • 04 张三 20 2 • 05 陈开 20 2 • U{学号，姓名，性别，年龄，成绩，班级） • R: 学生（学号，姓名，年龄，班级） • x:学号 x:学号 x:学号 x:姓名 x:姓名 x:姓名 • y: 姓名 y:年龄 y:班级 y:年龄 y:班级 y:学号 • xy x y x  y

  10. 写函数依赖步骤： • 1 找到码（可有多个） ，码决定其他所有属性 • 2 考察每一个属性能否决定其他属性。 • 注意： • XY , X 为复合属性当且仅当X 为码。

  11. 1 学生（学号，姓名，性别，身份证号) • 码：学号，身份证号 • 学号姓名，学号性别，学号身份证号 • 身份证号学号，身份证号姓名，身份证号性别 • 2 职工(工号，姓名，性别，部门，参加项目) • 某公司有若干部门，每一个部门有多名职工，每一个职工仅属于一个部门；每一个项目有多名职工参加，每个职工可参与多个项目；姓名又重复。 • 3 学生（学号，姓名，课程号，专业，教师) • 每一个学生属于一个专业，每一个学生选修多门课，一个教师只可以上一门课程，一门课程可以有多名教师上 • 4 选课(学号，课程号，成绩)

  12. 写出函数依赖： • 学生1(学号，姓名，性别，年龄，系科) • 学号姓名，学号性别，学号年龄， • 学号系科 • 学生2(学号，姓名，性别，年龄，班级，班主任) • 班级班主任 • 课程( 课程号，课程名，学分，任课老师） • 一门课只有一个老师 • 课程号课程名，课程号学分，课程号任课老师 • 一门课有多个老师 • 课程号课程名,课程号学分 • (课程号,任课老师) 课程名 • (课程号,任课老师) 学分， • 选课(学号，课程号，成绩) • W(日期，工号，姓名，超额，定额，车间，车间主任）

  13. 定义5. 2 完全函数依赖 • 设R(U) 是U上的一个关系，x,y是U的子集,x’ 是x 的真子集。若：对于R(U)下的任何一个可能的关系，均有x  y，但，x’ y,则称y完全函于x，记为： • f • X  y • 部分函数依赖 • 若 x y,且 x’ y,则称 y部分依赖于x,记作： • p • x y • 注意：仅当 x 为复合属性组时，才有可能出现部分函数依赖

  14. 关系：选课（学号，课程号，成绩，学时数) • 01 c1 80 5 • 01 c2 85 3 • 02 c1 89 5 • 02 c2 80 3 • 课程号学时数 • f • 学号+课程号  成绩 • x:学号+课程号 x’：课程号 • P • 学号+课程号  学时数 • x:学号+课程号 x’：课程号

  15. 找完全函数依赖 • 学生(学号，姓名，性别，年龄，班级，班主任) • 课程( 课程号，课程名，学分，任课老师） • 一门课只有一个老师 • 一门课有多个老师 • 选课(学号，课程号，成绩) • R(A,B,C,D,E) • ABC , AD,BE

  16. 定义5.3 传递函数依赖 • 在 R(U) 中，若 x y,但 y  x,若 y z,则 x z，称为 z 传递函数依赖于x,记作 ： • t • x  z • 例如： • 学生（学号，姓名，性别，班级，班主任）、 • 学号班级 班级学号 • 班级班主任 • 学号-〉班主任

  17. 找传递函数依赖 • 学生(学号，姓名，性别，年龄，系科) • R(A,B,C,D,E) • AB,AC,CD

  18. 5.2.2 码 • 定义5.4 • 主属性 • 非主属性 • 如何求主码？ • student(sno,sname,ssex,sage,sdept,idenity) • snosname,snossex,snosage,snosdept • sc(sno,cno,grade) • R(A,B,C,D) • 定义5.5

  19. 练习： • 工号姓名 • 工号 工种 • 工号-〉车间 • 车间 车间主任 • 日期+工号 姓名 （部分） • 日期+工号 工种 （部分） • 日期+工号 车间 （部分） • 日期+工号 超额 （完全函数依赖) • W(日期，工号，姓名，工种，超额，定额，车间，车间主任） • W(日期，工号，姓名，工种，超额，车间，车间主任） 工号+姓名工种 ?

  20. 5.2.3 范式 • 1. 什么叫范式 • 2. 六种范式: • 1NF  2NF  3NF  BCNF  4NF 5NF 1NF 2NF 3NF BCNF 4NF 5NF

  21. 第一范式 ( 1NF ) • 定义 • 缺点 • 数据冗余大 • 修改麻烦 • 插入异常 • 删除异常 • 关系W • 日期 工号 姓名 工种 超额 定额 车间 车间主任 • 90.5 101 丁一 车工 22% 80 金工 李明 • 90.5 102 王二 车工 17% 80 金工 李明 • 90.5 103 张三 钳工 14% 75 工具 赵杰 • 90.6 101 丁一 车工 19% 80 金工 李明 • 90.6 102 王二 车工 25% 80 金工 李明 • 90.6 103 张三 钳工 26% 75 工具 赵杰

  22. 工资(工号,姓名,工资(基本工资,岗位津贴),扣款( 公积金,所得税) ) • 工资符合第一范式吗？

  23. 5.2.4 2NF • 定义5.6：R1NF ,且它的每一非主属性都完全依赖于码 • 判断：是否存在部分函数依赖 • 将1NF 分解成 2NF -- 在1NF上去掉部分函数依赖 • 步骤： • 1 找出码 • 2 找出码的完全函数依赖 • *3 把完全函数依赖中的属性写入一个关系，其余写在另一个关系 • 例1 S-L-C( sno,sdept,sloc,cno, G) • sno,cno G sno,cnosdept • 例2 W(日期，工号，姓名，工种，超额，车间，车间主任） • 日期+工号姓名 （部分） 工号姓名 • 日期+工号工种 （部分） 工号工种 • 日期+工号车间 （部分） 工号-〉车间 • 日期+工号超额 （完全函数依赖) • 车间车间主任 • W1(日期，工号，超额） • W2(工号，姓名，工种，定额，车间，车间主任) • 选课（学号，课程号，成绩) • 2NF 的缺点 • 注意：码为单一属性的关系一定属于 2NF 吗？

  24. 判断范式： • 1学生(学号，姓名，性别，年龄，系科) • 2课程( 课程号，课程名，学分，任课老师） • 一门课只有一个老师 • 一门课有多个老师 • 3选课(学号，课程号，成绩) • 4 R(A,B,C,D,E) • ABC , AD,BE • 5 R(A,B,C,D,E) • ABC, AD,BE , AC

  25. 5.2.5 3NF • 定义5.7 R中若不存在这样的码X,属性组Y,非主属性 Z(ZY),使得 XY,YX（YX）成立，则R3NF • R2NF ,且它的每一非主属性都不传递依赖于码 • 在2NF上消除传递函数依赖 • 例如： • W2(工号，姓名，工种，车间，车间主任) • 工号 姓名 工号工种 • 工号 车间 • 车间车间主任 • W21(车间,车间主任) • W22(工号, 车间,姓名，工种)

  26. 判断范式： • 1学生(学号，姓名，性别，年龄，系科) • 2课程( 课程号，课程名，学分，任课老师） • 一门课只有一个老师 • 一门课有多个老师 • 3选课(学号，课程号，成绩) • 4 R(A,B,C,D ) • ABC, CD

  27. 5.2.6 BCNF -- 扩充的第三范式 • 定义5.8 关系模式 R<U,F>1NF, 若XY且YX时X必含有码，则R<U,F> BCNF • 关系中所有的函数依赖XY，X一定是码 • 1 C( Cno,Cname,Pcno) • CnoCname CnoPcno • Cnamecno CnamePcno • 2 Student(sno,sname,ssex,sage,sdept) • 3 Sjp( s,j,p) • (P,j) S (s,j)p 码(p,j) ,(s,j) • 4 S1(Sno,Sdept,Mname,Cname,Grade)

  28. 5 一个老师只上一门课 (学生，教师，课程) • Stj( s , t , j) 码 ： （s,t) (s,j) • 1 a 数据库 • 2 a 数据库 • 1 b 数据结构 • 2 b 数据结构 • 3 C 数据结构 • (S,J)T , (S,T)J, TJ • STJ  3NF STJ  BCNF • 6 Stj( S,T,J) 码：(S,J) • 假设：一个教师可以上多门课，每门课有多个教师； • 某一学生选定某门课，就对应一个固定教师。 • (S,J)T (S,T)J TJ • STJ BCNF

  29. 范式判断： • 1 写出函数依赖 • 找码，码决定其他属性 • 考察每个属性是否决定其他属性 • 2 范式： • 码为单个属性，属于 2 NF • 没有部分函数依赖，属于 2 NF • 没有传递函数依赖，属于 3NF • 关系的所有函数依赖 XY, X都为码，属于BCNF

  30. 判断范式： • 1 R(A,B,C,D,E), 码为AB,函数依赖为： • {ABC, ABD,ABE , DE }。 • 2 R(A,B,C,D,E), 码为AB,函数依赖为： • {ABC, ABD,ABE , BC,DE }。 • 3R(A,B,C,D,E), 码为A,函数依赖为： • {AB, A C, AD,AE }。 • 4R(A,B,C,D), 码为AB,函数依赖为： • {ABC, ABD}。

  31. 练习 • 指出函数依赖，指出完全函数依赖、部分函数依赖、传递函数依赖，属于哪一级范式 • 1学生(学号，姓名，性别，年龄，班级，班主任) 学号姓名 班级班主任 学号性别 学号年龄 学号班级 学号班主任 • 2选课(学号，课程号，课程名，成绩） • 课程号课程名 • 学号，课程号课程名 • 学号，课程号 成绩 • 3 考勤（日期，工号，姓名，规定工作时间，加班时间) • 4 工种(工号，姓名，工种，车间，车间主任) • 工号姓名 车间车间主任 • 工号工种 • 工号车间 • 工号车间主任 • 5 成绩(学号，课程号，教师工号，成绩） • 1）一个教师仅讲授一门课，一门课有多个教师讲授 • 教师工号课程号 • 学号+课程号 教师工号 （学号，课程号） 成绩 • 2）一个教师讲授多门课，一门课有多个教师讲授 • 学号+课程号 教师工号 （学号，课程号） 成绩 • 6 s1(学号,姓名,课程号,成绩,系科,系科名) • 7 商店(商店号,商店名,商品编号,商品名,价格,销售量)

  32. 5.3 数据依赖的公理系统 • 目的： • 关系的分解，低一级范式分解为高一级范式 注意： • 闭包 F+ • XF+ • 求关系主码的方法 • 闭包等价　G+=F+ • 极小函数依赖集 • 分解 • 证明无损分解 • 函数依赖在分解上的投影 • 分解算法　

  33. 定义5.11 R<U,F> • Armstrong 公理系统 • U,F为U上的一组函数依赖，关系模式R<U,F> • 推理规则： • A1 自反律：若YX U, 则 XY为F所蕴含 • A2 增广律：若XY 为F所蕴含，且 Z U, 则 XZYZ为F所蕴含 • A3传递律：若 XY及YZ为F所蕴含， 则 XZ为F所蕴含

  34. Student( sno,sname,ssex,class,teacher) • 1 X:sno+sname,Y:sname • sno+snamesname • 2 X:sno, Y:sname Z :ssex • 如 snosname则 sno+ssexsname+ssex • 3 X:sno Y:class Z : teacher • snoclass classteacher 则 snoteacher

  35. 推理规则： • 合并规则：由XY,XZ,有XYZ • xxxy, xyyz xyz • 伪传递规则：由XY,WYZ,有 XWZ • 分解规则：由XY 及 Z Y, 有 XZ • 引理5.1 • 如 XA1A2….Ak 则：XAi成立 • 如 XAi (I=1,2..K) 则：X A1A2….Ak • Snosname,snossex  Sno(sname,ssex)

  36. 练习 推导出函数依赖 • R(A,B,C) AB,AC,BA • ABC BC • BAC • ACBC ABBC BCAC • …. • R(A,B,C,D) ABC , CD • ABC BC • BAC • ACBC ABBC BCAC • ….

  37. 定义5.12 R<U,F> 中为F所逻辑蕴含的函数依赖的全体，叫做F的闭包，记为：F+ • 注意： F+ 包括F 及由F推导得到的 • R(A,B,C) • F={ AB,AC,BA} • ABC BC • BAC • ACBC ABBC BCAC • 练习：Student(sno,sname,ssex) F={ Snosname,snossex } • 写 F+ F+ • F={ Snosname,snossex } • Sno+snamesname,sno+ssexssex • Snosname+ssex F+

  38. 定义 5.13 • F 为U 上的函数依赖，X U, • XF+={A | XA 能由F 根据 Armstrong 公理导出} • XF+ 称为属性集 X 关于函数依赖集F的闭包 • 引理5.2 • 设F为属性集U上的一组函数依赖，X,YU, • XY 能由F根据Armstrong 公理导出的充要条件 • 是： Y XF+ • 小结：定义5.13、引理5.2 • 判定 X Y 是否成立？ • 先求 XF+ , 判 Y  XF+ 成立？ • 如 R(A,B,C,D) F={AB,AC,ACD} • 问： AD 成立吗？

  39. 算法5.1 • 2 求 B • B={A | (V)(W)(VWF^ VX(i) ^ AW) } • 左面有X(i) 子集的函数依赖的右部的并 • 例题： • U= {A,B,C,D,E} ; F={ABC,BD,CE,ECB,ACB} • 求 (AB) F+ • 解：1）X(0) = AB • 2) A,B,AB • ABC,BD 右部的并：CD • 3) X(1) = X(0)  CD = ABCD • 4) X(1) = X(0) 或X(1) =U 不满足 • I=1: 2) 得到 ABCD 子集为左部的函数依赖的右部的并 • ABC,BD,CE,ACB • BCDE • 3) X(2) = X(1)  BCDE = ABCDE • 4) X(2) =U 满足,结束 • (AB) F+=ABCDE ABE函数依赖存在吗？ DCB呢？

  40. U= {A,B,C,D,E} ; F={ABC,BD,CE,ECB,ACB} • 求 (BC) F+ , (AB) F+ • 解：1）X(0) = BC • 2) BD,CE 右部的并：DE • 3) X(1) = X(0)  DE = BCDE • 4) X(1) = X(0) 或X(1) =U 不满足 • I=1: 2) 得到 BCDE 子集为左部的函数依赖的右部的并 • BD,CE • DE • 3) X(2) = X(1)  DE = BCDE • 4) X(2) = X(1)满足,结束 • (BC) F+=BCDE

  41. 求码的问题 • R(A, B, C, D, E )，F={ ABC, CD, DB, DE } • 试求R的候选码 • f • 由定义：K U 则 K 为码 •  KF+ = ABCDE （AB) F+ 1: x(0)=AB 2: ABC ABD ABE 3: x(1)=ABCDE

  42. 如何求候选码？ • 方法： • 1．如果有孤立属性（和其它属性没有依赖关系的属性），则它必是构成侯选码的属性之一。 • 2．求出函数依赖中左部属性集，从中去除可有其它属性导出的属性 • 以上两部分属性构成侯选码 • 快速求解判定： • R(U , F), L 类：仅出现在F的左部的属性R 类：仅出现在F的右部的属性N 类：没有出现在F的左右两边的属性LR 类：在F的两边都出现的属性 • 结论：R类属性不在候选码中。N、L类属性一定属于任意一个候选码。

  43. 设有关系模式R(U, F)，其中U={A, B, C, D, E }，函数依赖集Fm={ ABC, CD, DB, DE } • 试求R的所有候选码 • 1 Fm的函数依赖中决定因素的属性集为：A B C D • 2 1) 由ABC可去掉C, 留：ABD • 2) 由ABC，CD知ABD, 可去掉D. 留：AB。 • 因此AB是候选码 • 3同理：由CD可去掉D, 留：A,B,C • 由CD, DB知CB,可去掉B, 留：AC。 • 因此AC是候选码 • 4由DB可去掉B, 留：A CD • 由DB, ABC知ADC,可去掉C, 留：AD。 • 因此AD是候选码

  44. 练习： 1 (U,F),U={A,B,C,D,E,P},F={AB,CP,EA,CED} ,求R的所候选关键字 2 设有关系模式R(U, F)，其中U={A, B, C, D, E }， FD集Fm={ ABC, CD, DB, DE } 求R的所候选关键字 3 设有关系模式R(U, F)，U={A, B, C, D,E }，F={ ABC, CD , BE}。 • 证明：AB为R的码

  45. 求候选码？ • 1 R(A,B,C,D),F={DB,BD,ADB,ACD} • L：A , C R: N: LR: B , D • 2 R(A,B,C,D,E,P),F={AD,ED,DB,BCD,DCA} • L: C ,E R: N: P LR: A , B , D • 4 U= {A,B,C,D,E} ; F={ABC,BD,CE,ECB,ACB} • L: A, R: D N: LR: B,C,E 5 R(U, F)，U={A,B,C,D,E} ，F={AB, CD}

  46. 定义5.14 如F+=G + ,F与G等价 • 引理5.3 F+=G + ,的充要条件：F  G + 和 G  F + • 1 R(A,B,C,D) • F={AB,AC,CD} • G={ABC,CD,AD} • F+ =G+? • 2 R(A,B,C,D) • F={AB,AC,CD} • G={AC,CD,AD} • F+ =G+?

  47. 作业： • 1 R(A,B,C,D), F={ABC,BC,CD}, G={AB,BC,CD} 问 F+ = G+ 吗？ • 2 U= {A,B,C,D,E} ; F={ABC,BD,CE,ECB,ACB} • 1）求 (AE) F+ , (DC) F+ • 2）AEB 函数依赖是否存在？ • 3) 求出关系模式R(U, F)的候选码 • 3 R(U,F),U= {A,B,C,D,E,F} ; F={ABE,ACF,ADB,BC,CD} • 证明：AB,AC,AD 为R的候选码 • 4 R(U,F),U= {A,B,C,D,E} ; F={ABC,CD,DB,DE} • 求出关系模式R(U, F)的所有候选码

  48. 定义5.15 极小函数依赖集 • 1） 函数依赖右部仅一个属性 • Sno (sname,ssex) X • 2) XA 函数依赖可以由Armstrong 推导出来 • F={snosdept,snoMN, sdeptMN , (sno,cname)G) } • {F ,snoMN } 与 F 等价 • 3）不存在XA,X有真子集Z,使 F-{XA}U{ZA} 与F等价 • F , (sno,sdept )  ssex } 与F 等价 • Z: sno • snosex  sno+sdept ssex [ (sno,sdept)ssex 不能在极小函数依赖集中)

  49. 定义 5.3 • 例3：F={AB,BA,BC,AC,CA} • F不是极小函数依赖集 • Fm1={AB,BC,CA} • Fm2={AB,BA,AC,CA} • 注意：Fm1+ fm2+ F+ • Fm1+ = fm2+ =F+ 练习：求极小函数依赖集 1 R(A,B,C,D,E) F={ABC, BD, AD, CE} {AB,AC,BD,CE} 2 R(A,B,C,D,E) F={ABC, BD, AD, ACE,CE}

  50. 5.3作业： • 1 R(A,B,C),F={AB,BA,BC,AC,CA} • 求 F 极小函数依赖集 • 2 R(A,B,C,D), F={AB,BC,CA,CD,ACD,BCA} • 求极小函数依赖集 • 3 W(日期，工号，姓名，工种，超额，车间，车间主任） • 1写出极小函数依赖集 • 2 指出函数依赖中的完全函数依赖、部分函数依赖、传递函数依赖 • 3 关系 W属于哪一级范式 • 工号姓名 • 工号 车间主任 • 日期+工号 超额 工号工种

More Related