• 1. 数据库系统原理与应用 第六讲
  • 2. 上堂课主要内容:第4章 关系数据库规范化理论 函数依赖 函数依赖的基本定义 / 完全函数依赖 / 部分函数依赖 / 传递函数依赖/互为函数依赖 范式和规范化 第一范式(1NF)
  • 3. 4.3 范式和规范化 什么叫范式 第一范式(1NF) 第二范式(2NF) 第三范式(3NF) BC范式(BCNF)
  • 4. 4.3.1 什么叫范式 范式(Normal Form)是符合某一种级别的关系模式的集合。 关系数据库中的关系必须满足一定的要求。满足不同程度要求的为不同范式。包括:第一范式、第二范式、第三范式,直至第五范式。 一个较低范式的关系,可以通过关系的无损分解转换为较高级范式,这个过程称为关系规范化。需要指出的是, 并不是规范化程度越高的关系就越优。因为当系统过于频繁地进行联接运算, 将付出响应时间上的代价。 实际关系数据库一般使用第三范式以上的关系。
  • 5. 各范式及规范化过程 小结1NF2NF3NFBCNF消除非主属性对关键字的部分函数依赖消除非主属性对关键字的传递函数依赖消除主属性对关键字的部分和传递函数依赖
  • 6. 1.分解必须是无损的 要求分解后不丢失信息,即连接分解后的两个关系使其恢复成原关系。 2.分解要保持函数依赖关系模式的规范化 规范化的基本思想是逐步消除关系模式中的不同函数依赖情况,最终达到每个关系只存在一个主题。 关系模式的分解原则是:分解后产生的模式与原模式等价,从以下两方面来实现:
  • 7. 第一种: W1(工号,定额) W2(工种,定额) 因车、铣工种的定额相同,而无法区别某工号的工种。4.4 关系模式的分解 W(工号,工种,定额)tf工号工种定额01车8002车8003钳7504铣80第二种 : W1(工号,工种) W2(工号,定额) 若某工人改变工种,须同时修改W1和W2;若新增加一个工种,须有工人才可以输入第三种: W1(工号,工种) W2(工种,定额) 此种分解是无损的。前两种方案,未实现“分解要保持函数依赖”,分解后的关系发生异常情况。 结论:关系模式的分解,既要有无损的连接性,又必须保持函数依赖。此关系存在传递依赖,有三种分解方案:
  • 8. 4.4.1 何为无损分解设关系模式R,如果把它分解为两个(或多个)子模式R1和R2,相应一个R关系中的数据就要被分成R1 、R2两个(或多个)子表。假如将这些子表自然连接,即进行R1R2操作,得到的结果与原来关系中的数据一致,信息并没有丢失,则称该分解具有无损连接性,否则如果R≠R1R2,则称该分解不具有无损连接性。
  • 9. 4.4.2 无损分解的测试方法输入:关系模式R(A1,A2,…,An), 函数依赖集F R的分解ρ={R1,R2,…,Rk} 输出 :确定ρ是否具有无损?
  • 10. 4.4.2 无损分解的测试方法算法: 1)构造一k行n列的表,第i行对应于关系模式Ri,第j列对应属性Aj。如Aj∈Ri,则第i行j列上放符号ai,否则放bij。 2)逐个检查F中的函数依赖,如函数依赖X→Y,则在X的分量中找相同的行,将这些行中Y的分量改为相同的符号,若有aj则将bij改为aj,若无aj,则改为bij。 3)反复进行第2步 ,如果某行变成a1,a2,…ak,则ρ是否具有无损。
  • 11. A1…Aj…AnR1…Ri…Rks[i,j]Aj在Ri中, ajAj不在Ri中, bij4.4.2 无损分解的测试方法(1)构造一个k行n列表S,其中:
  • 12. …X…Y…R1…Ri…Rk若Y值中有 aj,其它也改为aj 若Y值中无 aj,其它改为bij(下标小)F的选择顺序可随意4.4.2 无损分解的测试方法(2)依据函数依赖集F进行修正:X→Y
  • 13. …X…Y…R1…Ri…Rka1ana2……分解ρ具有无损联接性4.4.2 无损分解的测试方法(3)判断条件:
  • 14. ABCDER1a1b12b13a4b15R2a1a2b23b24b25R3b31a2b33b34a5R4b41b42a3a4a5R5a1b52b53b54a5第一步:构造表S例 设R(ABCDE),F={A→C,B→C,C→D,DE→C,CE→A},ρ={R1(AD),R2(AB),R3(BE),R4(CDE),R5(AE)},检验分解ρ是否具有无损联接性。
  • 15. 第二步:修正①A→CABCDER1a1b12b13a4b15R2a1a2b23b24b25R3b31a2b33b34a5R4b41b42a3a4a5R5a1b52b53b54a5例 设R(ABCDE),F={A→C,B→C,C→D,DE→C,CE→A},ρ={R1(AD),R2(AB),R3(BE),R4(CDE),R5(AE)},检验分解ρ是否具有无损联接性。
  • 16. 第二步:修正①A→CABCDER1a1b12b13a4b15R2a1a2b13b24b25R3b31a2b33b34a5R4b41b42a3a4a5R5a1b52b13b54a5例 设R(ABCDE),F={A→C,B→C,C→D,DE→C,CE→A},ρ={R1(AD),R2(AB),R3(BE),R4(CDE),R5(AE)},检验分解ρ是否具有无损联接性。
  • 17. 第二步:修正②B→CABCDER1a1b12b13a4b15R2a1a2b13b24b25R3b31a2b33b34a5R4b41b42a3a4a5R5a1b52b13b54a5例 设R(ABCDE),F={A→C,B→C,C→D,DE→C,CE→A},ρ={R1(AD),R2(AB),R3(BE),R4(CDE),R5(AE)},检验分解ρ是否具有无损联接性。
  • 18. 第二步:修正②B→CABCDER1a1b12b13a4b15R2a1a2b13b24b25R3b31a2b13b34a5R4b41b42a3a4a5R5a1b52b13b54a5例 设R(ABCDE),F={A→C,B→C,C→D,DE→C,CE→A},ρ={R1(AD),R2(AB),R3(BE),R4(CDE),R5(AE)},检验分解ρ是否具有无损联接性。
  • 19. 第二步:修正③C→DABCDER1a1b12b13a4b15R2a1a2b13b24b25R3b31a2b13b34a5R4b41b42a3a4a5R5a1b52b13b54a5例 设R(ABCDE),F={A→C,B→C,C→D,DE→C,CE→A},ρ={R1(AD),R2(AB),R3(BE),R4(CDE),R5(AE)},检验分解ρ是否具有无损联接性。
  • 20. 第二步:修正③C→DABCDER1a1b12b13a4b15R2a1a2b13a4b25R3b31a2b13a4a5R4b41b42a3a4a5R5a1b52b13a4a5例 设R(ABCDE),F={A→C,B→C,C→D,DE→C,CE→A},ρ={R1(AD),R2(AB),R3(BE),R4(CDE),R5(AE)},检验分解ρ是否具有无损联接性。
  • 21. 第二步:修正④DE→CABCDER1a1b12b13a4b15R2a1a2b13a4b25R3b31a2b13a4a5R4b41b42a3a4a5R5a1b52b13a4a5例 设R(ABCDE),F={A→C,B→C,C→D,DE→C,CE→A},ρ={R1(AD),R2(AB),R3(BE),R4(CDE),R5(AE)},检验分解ρ是否具有无损联接性。
  • 22. 第二步:修正④DE→CABCDER1a1b12b13a4b15R2a1a2b13a4b25R3b31a2a3a4a5R4b41b42a3a4a5R5a1b52a3a4a5例 设R(ABCDE),F={A→C,B→C,C→D,DE→C,CE→A},ρ={R1(AD),R2(AB),R3(BE),R4(CDE),R5(AE)},检验分解ρ是否具有无损联接性。
  • 23. 第二步:修正⑤CE→AABCDER1a1b12b13a4b15R2a1a2b13a4b25R3b31a2a3a4a5R4b41b42a3a4a5R5a1b52a3a4a5例 设R(ABCDE),F={A→C,B→C,C→D,DE→C,CE→A},ρ={R1(AD),R2(AB),R3(BE),R4(CDE),R5(AE)},检验分解ρ是否具有无损联接性。
  • 24. 第二步:修正⑤CE→AABCDER1a1b12b13a4b15R2a1a2b13a4b25R3a1a2a3a4a5R4a1b42a3a4a5R5a1b52a3a4a5例 设R(ABCDE),F={A→C,B→C,C→D,DE→C,CE→A},ρ={R1(AD),R2(AB),R3(BE),R4(CDE),R5(AE)},检验分解ρ是否具有无损联接性。
  • 25. 第三步:判断ABCDER1a1b12b13a4b15R2a1a2b13a4b25R3a1a2a3a4a5R4a1b42a3a4a5R5a1b52a3a4a5分解ρ具有无损联接性例 设R(ABCDE),F={A→C,B→C,C→D,DE→C,CE→A},ρ={R1(AD),R2(AB),R3(BE),R4(CDE),R5(AE)},检验分解ρ是否具有无损联接性。
  • 26. E-R模型的设计 及 向关系模型的转换 (参考书上的第2章)
  • 27. ●为了把现实世界中的具体事物, 抽象、组织为某一DBMS(数据库管理系统)支持的数据模型, 人们常首先把现实世界抽象为信息世界, 然后将信息世界转换为机器世界。 ●首先抽象为某一种信息结构, 其并不依赖于具体的计算机系统, 不是某一个DBMS支持的数据模型, 而是概念级的模型;然后, 再把概念模型, 转换为计算机上某一DBMS支持的数据模型(逻辑数据模型) 。数据模型与概念模型 信息的现实世界 通过实体(Entity)、实体的特征(Entity Characteristic)、实体集(Entity Set)及联系(Relation)进行划分和认识。
  • 28. ●概念模型, 面向用户, 应具有较强的语义表达能力, 简单、清晰、易于理解; 数据模型(逻辑数据模型), 比如网状模型、层次模型、关系模型等,面向DBMS, 用以刻画实体在数据库中的存储形式, 以及实体之间的联系。数据模型与概念模型 信息世界 实体的特征在头脑中形成的知识称为属性;实体通过其属性表示称为实例;同类实例的集合称为对象,对象即实体集中的实体用属性表示得出的信息集合;实体集之间的联系用对象联系表示。
  • 29. 概念模型是对现实世界的第一层抽象, 是用户与数据库设计人员之间进行交流的语言。 ●建立概念模型, 涉及如下术语: 实体、实体集、属性、域、关键字、联系。 ●概念模型的表示方法很多, 其中最为常用的是P.P.S.Chen于1976年提出的E-R图。 概念模型中的术语
  • 30. E-R模型中的三个组织成分 实体、属性、联系
  • 31. 实体(entity)●客观存在并可相互区别的事物称为实体 ●实体可以是具体的人、事、物。比如一个学生、一门课等 ●实体也可以是抽象的概念或联系 比如, 学生的一次选课、部门的一次订货、老师与系的工作关系(即某位老师在某系工作)等。 ●具有相同属性的实体必然具有共同的特征和性质。用实体名及其属性名集合来抽象和刻画的同类实体, 称为实体集。 比如:学生(学号, 姓名, 性别, 院系) 就是一个实体集。 ●实体的表示: 实体名学 生课 程二个实体集与实体集(entity set)
  • 32. 属性(attribute)●实体所具有的某一特性称为属性。 比如学生实体, 可具有学号、姓名等属性,属性组合起来表征了一名学生。 比如课程实体, 可具有课号、课名等属性,属性组合起来表征了一门课程。 ●属性的表示:属性名学 生课 程姓名学号年龄性别课号课名学分
  • 33. 学 生课 程姓名学号年龄性别课号课名学分选修成绩联系●实体集之间关系称为联系。 例如, 学生与课程之间, 存在着“选修”联系。 ●特别需要指出的是, 一个联系, 也可以是一个实体, 也可以有自己的属性。 比如, “选修”是一个联系, 它有自已的属性:成绩、绩点。 ●联系的表示:联系名绩点
  • 34. 实体—联系(Entity Relationship)模型的表示用E—R图来表示 实体集AA联系集RR属性XXB1NYE—R图的描述约定
  • 35. 实体之间的三种联系
  • 36. ●如果对于A中的每一个实体, B中至多有一个实体与之联系, 反之亦然, 则称实体集A与实体集B具有一对一联系。院名地址电话学 院任职院长任职年月姓名性别年龄职称11假设有两个实体集A和B。实体集A实体集B●例如, 一个学院只有一个正院长, 而一个正院长只在一个学院中任职, 则学院与院长之间只具有一对一联系。一对一联系 (记为1:1)
  • 37. 实体集A实体集B一对多联系 (记为 1::n) ●如果对于A中的每一个实体, B中有n个实体与之联系; 反之, 对于B中的每一个实体, A中至多只有一个实体与之联系, 则称实体集A与实体集B具有一对多联系。车间号车间名电话车 间聘用职 工工号姓名性别年龄1N聘用期●例如, 一个车间中有若干名工人, 而每个工人只在一个车间中工作, 则车间与职工之间具有一对多联系。
  • 38. 实体集A实体集B多对多联系(记为m:n) ●如果对于A中的每一个实体, B中有n个实体与之联系; 反之, 对于B中的每一个实体, A中有m个实体与之联系, 则称实体集A与实体集B具有多对多联系。●例如, 一门课程同时有若干个学生选修, 而一个学生可以同时选修多门课程, 则课程与学生之间具有多对多联系。学 生课 程姓名学号年龄性别课号课名学分选修绩点成绩NM
  • 39. 建立E—R模型的步骤定义实体集; 定义联系集; 将实体集和联系集组合成E-R图; 确定实体集与联系集的属性,标识出实体集与联系集的主码,形成完整的E-R图。
  • 40. 书上例2.2某大学选课管理中,学生可根据自己的情况选修课程,每名学生可同时选修多门课程,每门课程可由多位老师讲授,每位教师可讲授多门课程,画出对应的E-R图。学生学号姓名性别年龄教师教师号姓名性别职称课程课程号课程名课程开课选修学生教师nnmm
  • 41. 书上例2.2学生学号姓名性别年龄教师教师号姓名性别职称课程课程号课程名开课选修nnmm上课地点分数
  • 42. 把E-R图转换成关系模式一个系统的E-R图不是唯一的,从不同侧面出发画出的E-R图是不同的。E-R图必须要转换成数据模型才能被实际的DBMS接受。
  • 43. 学生(学号#, 姓名, 性别, 年龄) 课程(课号#, 课程名, 学分)⒈一个实体集 选修(学号#, 课程号#, 成绩, 绩点)学 生姓名学号年龄性别课 程课号课名学分选修绩点成绩NM一个实体可转换成一个关系模式 ⒉一个m : n 联系●可转换为一个独立的关系模式 , ●把相联系的二个实体集的主码, 作为属性, 并把二者组合成关系的主码组. ●再把该联系本身的属性, 作为关系的属性.
  • 44. ●可转换为一个独立的关系模式 应把n端实体的主码, 作为关系的主码。 把1端实体的主码, 作为关系的属性 再把该联系本身的属性, 作为关系的属性。 学生(学号#, 姓名, 性别, 年龄) 班级(班级号#, 班主任)●该联系也可不转换为关系模式, 而把1端关系的主码、联系的属性,加到n端的关系中去 ( 理由是“7.具有相同主码的关系可合并 ” 见后页) 学生(学号#, 姓名, 性别, 年龄, 班级号) 班级(班级号#, 班主任)⒊一个1 : n 联系 班级学号姓名性别年龄1组合n学生班级号班主任组合(学号#, 班级号)
  • 45. ●可转换为一个独立的关系模式 , ●也可以与任一端对应的实体合并。 (可视为 1 : n 联系的特例)⒋一个1 : 1 联系公司公司名地址电话邮政编码任职公司总裁出生年月总裁姓名职称性别任职年月11公司关系(公司名,地址,电话,邮政编码,总裁姓名,任职年月) 总裁关系(总裁姓名,性别,出生年月,职称)或:公司关系(公司名,地址,电话,邮政编码) 总裁关系(总裁姓名,性别,出生年月,职称,公司名,任职年月)
  • 46. ●该联系应独立转换成一个关系模式, 相关各实体的主码, 组合成本关系的属性. 并取m与n端实体的主码作为本关系的主码。⒌三个或三个以上实体间的一个多元联系(上)供应商号地址供应商名供应商供应m项目项目编号开工日期项目名mn零件零件号重量零件名零件数供应商(供应商号#,供应商名,地址) 零件(零件号#,零件名,重量) 项目(项目编号#,项目名,开工日期) 供应(供应商号#, 项目编号#, 零件号#,零件数)
  • 47. ●若其中有一对多联系,也可以按规则3, 与另一端对应的实体合并。(以下二种均可) ⒌三个或三个以上实体间的一个多元联系(下)课号学分课程名课程讲授n教科书书号出版社单价书名教师教师号性别姓名职称m1 课程(课程号#,课程名,学分) 教师(教师号#,姓名,性别,职称) 教科书(书号#,书名,出版社,单价) 讲授(课程号#, 教师号#, 书号)(设一门课程, 指定一本教科书, 有多名教师讲授. )课程(课程号#,课程名,学分,书号) 教师(教师号#,姓名,性别,职称) 教科书(书号#,书名,出版社,单价) 讲授(课程号#, 教师号# )
  • 48. 可按上述三种情况分别处理。 例如, 如果教师实体集内部, 存在领导与被领导的1:n 自联系, 可以将该联系与教师实体合并, 比如在教师实体内增设一个“系主任”属性, 存放教师号. ⒍同一实体集的实体间的联系, 即自联系教师号姓名性别电话n领导1教师
  • 49. ⒎具有相同主码的关系模式可合并 学生(学号# ,姓名,性别,出生年月) 通讯录(学号# ,姓名, 联系方式, 电子邮箱)学生(学号#,姓名,性别,出生年月,联系方式,电子邮箱)可合并为一个关系:
  • 50. 举例:用E-R图描述图书馆读者信息
  • 51. 对学生借书表进行规范化处理
  • 52. 学生表 存书表 借书表
  • 53. 学生信息表 借阅信息表 书目表借阅m学 生学号姓名电话住址还期借期书目n书号书号出版社作者⒈确定实体型 分析可知, 有二个实体:读者和图书。 ⒉确定联系类型 二个实体间存在一个m:n联系, 取名为“借阅” 。 该联系本身具有二个属性:借阅时间、归还时间 ⒊把实体型、联系类型和属性组成E-R图。
  • 54. 讨论:完善E-R图,转成关系模式
  • 55. 有如下描述航空售票系统的E-R图, 尚待完善: ●请补充属性“航空公司”、“日期”、“舱位号”、“票价” ●请指明联系的类型(n:m, 1:n, 1:1) ●把E-R图, 转换成关系模式,用 #号标明主码, 用下划线指明外码。航班号起飞地点目的地航班身份证号姓名性别联系方式乘客订票起飞时间
  • 56. 航班号起飞地点目的地航班身份证号姓名性别联系方式乘客订票日期舱位号票价航空公司可把描述航空售票系统的E-R图补充如下 : ●请指明联系的类型(n:m, 1:n, 1:1) ●把E-R图, 转换成关系模式,用 #号标明主码, 用下划线指明外码。起飞时间
  • 57. 可把如上E-R图转换成三个关系模式: 航班(航班号#, 航空公司, 起飞地点, 目的地,起飞时间) 乘客(身份证号#, 姓名, 性别, 联系方式) 订票(航班号#,身份证号#, 日期, 舱位号, 票价)航班号起飞地点目的地航班身份证号姓名性别联系方式乘客订票mn日期舱位号票价可把描述航空售票系统的E-R图补充完善如下 :航空公司起飞时间
  • 58. 带有明细清单的单据实体的E-R图转换
  • 59. 成交单价 1n收 银客户号名称地址客户电话送货单发生日期付款方式单据号应付总额1n选 购 nm数 量清单组成品名包装条码单价商品姓 名收银员号收银员性 别用E-R图描述
  • 60. 送货单(单据号#, 客户号,收银员号,发生日期, 付款方式, 应付总额 ) 客户(客户号#, 客户名, 地址, 电话) 收银员(收银员号#, 姓名,性别 ) 商品信息(条码#, 品名, 包装, 单价)送货清单(单据号#, 条码#, 数量, 成交单价,成交金额 )最终得到如下五个关系模式:
  • 61. 送货单整单信息表送货单商品明细表金额可计算
  • 62. 只输出单价不在10至30元之间的酒类商品。 USE 购进凭证 SELECT * FROM 购进凭证表 WHERE (大类名= '酒类') AND (单价> 30) OR (单价< 10)错select * from 购进凭证表 where 大类名='酒类' and 单价 between 10 and 30错上机练习2小结
  • 63. 上机练习2小结统计所有商品的总数量、总金额。 USE 购进凭证 select sum(数量) as '总数量‘ from 购进凭证表 union select sum(数量*单价) as '总金额‘ from 购进凭证表不正确select sum(数量) as 总数量, sum(单价*数量) as 总金额 from 购进凭证表
  • 64. 上机练习2小结除了“饼干”,再同时输出“酒类”商品;再同时输出“饮料”、“糖果”商品。 use 购进凭证 SELECT * from 购进凭证表where 大类名='饼干' or 大类名='酒类' or 大类名='饮料' or 大类名='糖果'
  • 65. 上机练习2小结⒍统计所有商品的总数量、总金额。 use 购进凭证 SELECT sum(单价*数量) AS 总金额, SUM(数量) AS 总数量FROM 购进凭证表 ⒎统计饼干大类的总数量、总金额。 use 购进凭证 SELECT 大类名,sum(单价*数量) AS 总金额, SUM(数量) AS 总数量FROM 购进凭证表where 大类名='饼干' group by 大类名
  • 66. 上机练习2小结⒏统计饼干、糖果大类的总数量、总金额。 SELECT sum(单价*数量) AS 总金额, SUM(数量) AS 总数量FROM 购进凭证表where 大类名='饼干' or 大类名='糖果‘ ⒐统计饼干大类和糖果大类各自的总数量、总金额。 SELECT 大类名,sum(单价*数量) AS 总金额, SUM(数量) AS 总数量FROM 购进凭证表where 大类名='饼干' or 大类名='糖果' group by 大类名 ⒑统计各大类各自的总数量、总金额。 SELECT 大类名,sum(单价*数量) AS 总金额, SUM(数量) AS 总数量FROM 购进凭证表 group by 大类名
  • 67. 上机练习2小结⒒统计购进凭证表中的凭证张数。 use 购进凭证 SELECT count(凭证号) as 凭证张数 FROM 购进凭证表 ⒓统计各大类的凭证张数。 use 购进凭证 SELECT 大类名,count(凭证号) as 凭证张数 FROM 购进凭证表group by 大类名
  • 68. 上机练习2小结求选修了课程的学生学号 。 use 学生_课程 select distinct 学号 from 学生错use 学生_课程 select distinct 学号 From 选课
  • 69. 上机练习2小结三.2 求选修了课程的学生学号 (利用select命令中DISTINCT选项) use 学生_课程 select distinct 学号from 选课 三.9 求选修课程C1且成绩在80-90分之间的学生学号和成绩,将成绩乘以系数0.8输出 use 学生_课程 select 学号,成绩*0.8 as 成绩from 选课where 课程号='C1'and (成绩>=80 and 成绩<=90)
  • 70. 上机练习2小结三.11 求计算机学院的所有学生以及自动化学院姓张的学生信息 use 学生_课程 select* from 学生where 所在系='计算机学院' or (所在系='自动化学院' and charindex ('张',姓名)>0) 三.12 求缺少了成绩的学生的学号和课程号 use 学生_课程 select 学号,课程号from 选课where 成绩is null
  • 71. 自学要求与上课安排 ●自学与上机内容: 自学第5章、第12章和第13章 完成上机实习:做完上机练习4、练习5 ●要求: 在上机时,显示你的第四部分实习结果。
  • 72. (本页无文本内容)