chap03数据库设计.ppt

数据库技术与应用 第三章数据库设计 数据库应用程序是如何工作的 内容提要 3 1数据库设计过程3 2概念设计 E R图 3 3逻辑设计3 4关系数据库设计理论 3 1数据库设计过程 E R图到关系模式的转换 关系模式的规范化 需求分析 是数据库设计的第一阶段任务 明确用户需求信息要求 处理要求 安全性和完整性要求结果 形成需求分析报告 案例 一个图书借阅数据库分析 经分析 系统需提供如下服务 可更新 查询书库中现有书籍信息包括 索书号 书名 作者 出版社 出版时间 科目 数量 存放位置 索书号可以唯一标识书籍 可更新 查询读者信息包括 借阅证号 姓名 生日 性别 单位 借阅证号可唯一标识读者 可更新 查询图书借还信息包括 借书证号 姓名 借阅日期 归还日期 规则约定 任何读者可借阅多本书 一本书可以被多个读者借阅 3 2概念模式设计 概念模式设计 也称概念数据建模 是对一个系统中数据的抽象描述 即系统中实体 或类 及它们之间的关系 它独立于具体实现方式 不涉及信息在计算机中的表示 常用方法 E R模型 UML模型 E R模型 实体联系模型 Entity RelationshipModel 简称E R模型 是P P Chen于1976年在 TheEntity RelationshipModel TowardaUnifiedViewofData 的论文中提出的 这个模型直接从现实世界中抽象出实体类型及实体联系 然后用实体联系图即E R图表示数据模型 其中的核心就是设计E R图 Entity RelationshipDiagram 实体 entity 客观存在 可以相互区别的事物称为实体 实体集 性质相同的同类实体的集合 称为实体集 属性 实体有很多特性 每一个特性称为属性 每一个属性有一个值域 其类型可以是整型 字符串型等 键 key 能惟一标识实体的属性或属性组 称为实体标识符 其中 满足元素个数最少的键称为候选键 简称 也称为键 主键是候选键之一 超键是候选键的超集 联系 relationship 是实体之间的相互关系 与一个联系有关的实体集个数 称为联系的元数 分1 1 1 n m n三类 E R模型的基本概念 E R图的基本组成部分 E R图有四个主要部分 1 实体集 用矩形表示 2 属性 用椭圆形表示 3 联系 用菱形或三角形表示 4 直线 表示联系的类型及实体与实体之间的联系 多端用无箭头的直线 1端用有箭头的直线 Isa 数据联系 relationship 联系是实体之间的相互关系 二元联系有以下三种类型 一对一联系 如果实体集E1中每个实体至多和实体集E2中的一个实体有联系 反之亦然 那么实体集E1和E2的联系称为 一对一联系 记为 1 1 E R图 二元联系 一对多联系 如果实体集E1中每个实体可以与实体集E2中任意个 零个或多个 实体间有联系 而E2中每个实体至多和E1中一个实体有联系 那么称E1对E2的联系是 一对多联系 记为 1 N E R图 二元联系 多对多联系 如果实体集E1中每个实体可以与实体集E2中任意个 零个或多个 实体有联系 反之亦然 那么称E1和E2的联系是 多对多联系 记为 M N E R图 案例 一个图书借阅数据库概念设计 数据分析结果 一个图书借阅系统需提供书籍 索书号 书名 作者 出版社 出版时间 科目 数量 存放位置 索书号可以唯一标识书籍 读者 借阅证号 姓名 生日 性别 单位 借阅证号可唯一标识读者 上述实体中存在如下联系 任何一个读者可借阅多本书 一本书同时只能被一个读者借阅 借阅要登记借书时间 还书时间 图书借阅数据库E R图 性别 借书时间 出版时间 科目 数量 存放位置 E R图实例2 例2 请设计一个数据库 要求包括学生 student 课程 course 和教师 teacher 的信息 上述实体中存在如下联系 一个学生可以选修多门课 而每门课也能由多个学生选修 一位教师可以教多门课 每门课也可以由多个教师讲授 请用E R图来描述此数据库 学生信息 学号 sNo 姓名 sname 年龄 age 地址 saddress 课程信息 课程号 cNo 课程名 cname 课程学分 credit 教师信息 教师的工号 tNo 姓名 tname 教师的地址 taddress 注意 如果一个联系具有属性 则这些属性也要用无向边与该联系连接起来 学生 课程 教师E R图 E R图实例3 例3 设计一个数据库 要求包括以下信息 教师 教师号 姓名 性别 职称课程 课程号 课程名工作单位 单位名 电话上述实体中存在如下联系 1 一个教师可讲授多门课程 一门课程可为多个教师讲授 教师讲授的班级信息用班号表示 2 一个单位可有多个教师 一个教师只能属于一个单位 试画出上述实体集及实体联系的E R图 3 3逻辑模式设计 实体到关系模式的转换 规则 将实体的名称作为关系的名称 将实体的属性作为关系的属性 并用下划线标识出主属性 Student snum sname sex age 表示学生关系 其中snum为主键 Course cnum cname credits 表示课程关系 其中cnum为主键 联系到关系模式的转换 两端联系 规则 若实体间联系是1 1 可以在两个实体转换成的两个关系模式后 然后在其中任意一个关系模式的属性集中加入另一个关系模式的主键和联系自身的属性 也就是说1 1联系不单独转换为一个独立的关系 若实体间联系是1 N 则在N端实体转换成的关系模式中加入1端实体的主键和联系的属性 同样1 N联系也不独立构成一个关系 若实体间联系是M N 则将联系转换成一个关系模式 其属性为两端实体的主键加上联系自身的属性 而联系关系的主键为一复合键 通常是两端实体主键的组合 举例 例 对于学生选课E R图的课程和学生之间的多对多的联系 可转换成关系模式 take snum cnum grade 主键为复合键 snum cnum 多端联系到关系模式的转换 什么是多端联系 将三个或三个以上实体间个联系简称为多端联系 在E R图中如何表示 转换规则 多端联系转换成一个关系模式 其属性为多端实体的键加上联系自身的属性 而主键通常为多端实体键的复合 举例 例对一个教师授课系统有如下描述 一门课程可以由多个教师使用多本参考书讲授 而每个教师可教多门课程 每一本参考书可供多门课程使用 1 假设每个实体有如下属性 画出该系统的E R图 2 将ER图转换成关系模式集 并指出每个关系模式的主键和外健 教师 工号 姓名 性别 职称 工号具有唯一标识性 课程 课程号 课程名 学分 课程号具有唯一标识性 参考书 书号 书名 作者 出版社 书号具有唯一标识性 科目 课程 教师 工号 姓名 性别 职称 参考书 书号 书名 作者 科目 出版社 课程 课程号 课程名 学分 讲授 课程号 工号 书号 isa 联系到关系模式的转换 什么是 isa 联系 若A类实体和B类实体之间是 属于 关系 这里假定为B属于A 为了表示出A和B之间的关系 用一种称为 属于 isa 的特殊联系将将实体集A和B相连 A类实体和B类实体也称为超类和子类 在E R图中如何表示 在E R图中 isa 联系用三角形表示 尖端指向超类实体集 这里的A 底边指向子类实体集 这里的B 三角形中写上 isa 的字样 contd isa 联系到关系模式的转换规则 为子类和超类实体集各创建一个关系模式 子类关系模式的属性集由超类关系模式的主键和自身的属性组成 子类与超类有两个性质 子类实体继承超类实体的所有属性 但子类实体本身还可以包含比超类实体更多的属性 这种继承性通过子类实体和超类实体有相同的键实现 E R图转换实例 转换为关系模式 学生 学号 姓名 性别 年龄 系名 研究生 学号 导师 综合实例1 例1 根据需求分析报告描述 在某影展信息管理系统中存在下列实体和联系 设计满足需求的E R图 电影 片名 出品年份 影片长度 影片类型 键码为 片名 出品年份 明星 姓名 联系地址 键码为姓名 影片公司 公司名称 地址 键码为公司名称 卡通片 设计平台 是电影实体的子类 键码为其超类的键码 片名 出品年份 实体集 一个影片公司有一个名称和一个地址 由名称唯一标识 一位明星最多只能签约一个影片公司 而一个影片公司可以拥有多位明星 一部影片由多位明星参演 一位明星可以出演多部影片的多个角色 一部影片最多归属于一家影片公司 而一家公司可以出品多部影片 卡通片是电影的一个子类 综合实例1 Contd 联系描述 设计的E R图的步骤 Step1 设计实体及其属性和键码 Step2 设计两两实体之间的联系 比如电影和明星之间的扮演 Act 联系 明星和影片公司之间的聘用联系等 Step3 在所有实体之间的联系设计完后 在将这些E R图合并为一个总的E R图 在这个过程中要去掉多个实体和属性间的命名冲突和联系冲突 生成一个完整的 满足应用需求的全局E R图 思考题 1 Movie的主键是什么 2 各个实体与联系如何转换为关系模式 Platform 3 4关系数据库设计理论 问题产生 观察一下 student 我们发现 在该表中sdept和mname两列的同一个值多次存储 即数据冗余现象 可能后果 修改异常如果计算机系的系主任换了 也许只在上述关系中的两个元组中修改了 而没在第三个元组中修改 导致数据不一致 插入异常如果成立了一个新系 目前还没招生学生 需要在学生相关列 如snum cname等 输入空值 而主键 snum cname 违反了实体完整性 删除异常如果我们从学生选课表中删除一个记录 而它恰好是一个系的最后一名学生 那么关于该系的其他信息也被从数据表中删除了 例如 如果要删除993851 陈刚 的记录 那么与建筑系相关的信息也丢失了 分解后 上述分解 既消除了冗余 又使得学生 系 选课 Student 是一种好的分解 思考 什么样的分解为好呢 冗余的根源 函数依赖FD 函数依赖 functionaldependency 简记为FD 设有关系R U X和Y是属性集U的子集 函数依赖是形为X Y的一个命题 对R中任意两个元组t和s 若t X s X 则t Y s Y 那么称X Y在关系R U 中成立 称X函数决定Y Y函数依赖于X 实例 例Student关系模式Student snum sname sdept cnumgrade 中存在哪些函数依赖呢 还有吗 snum cname sname sdept mname Splitting CombiningRule 对于关系R 若函数依赖X A1 X A2 X An成立 则X A1A2 An成立 反之亦然 例 A BC等价于A B A C注意 函数依赖的左侧没有分裂规则 splittingrule Armstrong sAxioms Reflexivity If B1 B2 Bm A1 A2 An thenA1A2 An B1B2 Bm ThesearewhatwecalledtrivialFD s 平凡函数依赖 Augmentation IfA1A2 An B1B2 Bm thenA1A2 AnC1C2 Ck B1B2 BmC1C2 CkforanysetofattributesC1 C2 Ck Transitivity IfA1A2 An B1B2 BmandB1B2 Bm C1C2 CkthenA1A2 An C1C2 Ck 3 范式 1NF和2NF 1 第一范式 FirstNormalForm 1NF 如果一个关系模式R的所有属性都是不可分的基本数据项 则这个关系属于第一范式 2 第二范式 SecondNormalForm 2NF 若关系模式R属于第一范式 且每个非主属性都完全函数依赖于任何一个候选键 则R属于第二范式 注意 非主属性 non primeattribute 有时也称为非键属性 non keyattribute 是指不属于任何一个候选键的属性 参考来源 http en wikipedia org wiki Second normal form 思考 为什么候选键为单属性键的关系一定属于2NF 完全函数依赖 部分函数依赖 对于函数依赖W A 如果存在V W V是W的真子集 使得函数依赖V A成立 则称A部分依赖于W 记作W A 否则 若不存在这种V 则称A完全函数依赖于W 记作W A F P 实例 例 对于上述的Student关系模式 Student snum sname sdept mname cname grade 中 snum cname 为主键 存在如下函数依赖 snum sname sdept mname 范式 3NF 3 第三范式 ThirdNormalForm 3NF 若关系模式R属于第二范式 且每个非主属性都不传递依赖于任何一个候选键 则R属于第三范式 byE F CoddArelationRisin3NFif WheneverA1A2 An B1B2 BmisanontrivialFD 非平凡函数依赖 either A1 A2 An isasuperkey orthoseofB1 B2 Bmareeachamemberofsomecandidatekey notnecessarilythasamecandidatekey 或 传递函数依赖 对于函数依赖X Y 如果Y X X不函数依赖于Y 而函数依赖Y Z成立 则称Z传递函数依赖于X 记作X Z 例 对于上述的Student关系模式 Student snum sname sdept mname cname grade 中 snum cname 为主键 存在下列函数依赖 snum cname sdept 又sdept snum cname 而sdept mname snum cname mname因此 student 3NF t t 范式 BCNF 4 BC范式 BCNF BCNFisaslightlystrongerversionofthethirdnormalform 3NF ArelationisinBCNFifandonlyifforeveryoneofitsnontrivialFD sX Y Xisasuperkey 因此 判断一个关系是否属于BCNF 我们仅需判断所有函数依赖的决定因素是否都为超键即可 思考 为什么两个属性构成的关系一定是BCNF的关系 参考来源 http en wikipedia org wiki Boyce Codd normal form Rlationsin1NF 范式之间的关系 共有六种范式 它们之间的关系是 1NF 2NF 3NF BCNF 4NF 5NF Relationsin2NF Relationsin3NF RelationsinBCNF Relationsin4NF Relationsin5NF BCNF及以上范式能消除FD导致的冗余 规范化 规范化 通过模式分解把属于低级范式的关系模式转换为几个属于高级范式的关系模式的集合 这一过程称为规范化 Normalization 模式分解原则 原则一 无损连接性 LosslessJoin 设关系模式R U F 被分解为若干个关系模式R1 R2 Rn 如果R与R1 R2 Rn自然连接的结果相等 则称关系模式R的分解具有无损连接性 无损连接性示例 有损连接性的示例 R2 模式分解的原则 contd 原则二 函数依赖保持性 PreserveDependency 设关系模式R U F 被分解为若干个关系模式R1 R2 Rn 如果R中的函数依赖集F与关系分解模式R1 R2 Rn中所有的函数依赖是一致的 则称关系模式R的分解具有函数依赖保持性 规范化实例分析一 例1 关系模式student的规范化处理过程 student snum sname sdept mname cname grade 1 确定是否是1NF因为在关系模式student中 不存在表中有表的情况 即每个属性都是不可再分的 所以 关系模式student 1NF student snum sname sdept mname cname grade 2 确定是否是2NF在关系student中 主键为 snum cname 因为存在属性集 sname sdept mname 对主键 snum cname 的部分函数依赖 snum cname sname sdept mname p 所以 student 2NF snum sname sdept mname 寻找违反2NF的函数依赖snum sname sdept mname 分解为关系R1 snum sname sdept mname 其中存在函数依赖 sdept mnamesnum sname sdept mname及关系R2 snum cname grade 其中存在函数依赖 snum cname grade显然 R1和R2都不存在部分函数依赖 故R1 R2 2NF student snum sname sdept mname cname grade R2 R1 snum sname sdept mname R2 snum cname grade 3 确定是否是3NF在R2中存在唯一的非平凡函数依赖snum cname grade 所以 R2 3NF 在R1中 存在违反SNF的函数依赖sdept mname 所以 R1 3NF 将R1分解为 R11 snum sname sdept R12 sdept mname 考察R11和R12中的函数依赖 可知R11 R12 3NF 4 确定是否是BCNF在R2 R1