第9章数据库设计

第9章数据库设计9.1概念模型02二月2023华北科技学院白宏斌2用途：描述数据、数据之间联系概念模型数据模型转换按用户观点对信息和数据建模工具：E-R图按计算机系统观点对数据建模层次模型网状模型关系模型02二月2023华北科技学院白宏斌3概念模型概念模型通过对客观事物及其联系的抽象，方便、直接的表达各种语义，以实现数据库设计人员与用户的交流。概念模型中涉及到实体、属性、关键字、域、实体集、联系等02二月2023华北科技学院白宏斌4实体客观存在并可以相互区分的客观事物或事件称为实体。纵贯线歌友会、2014世界杯足球赛……1号仓库、张三……02二月2023华北科技学院白宏斌5属性身高、年龄、性别、体重、……比赛时间、地点、参赛队……实体所具有的某一方面的特性称为属性。02二月2023华北科技学院白宏斌6关键字实体的某一属性或属性组合，其取用的值能唯一标示出某一实体。身份证号学号+课程编号02二月2023华北科技学院白宏斌7域属性的取值范围。性别：{男，女}02二月2023华北科技学院白宏斌8具有相同特征的一类实体的集合称之为实体集。实体集学生实体集订单实体集02二月2023华北科技学院白宏斌9事物之间的关系实体集内部的联系，表现在属性之间或者实体之间。实体集间的联系。最基本的是两个实体间的联系。联系02二月2023华北科技学院白宏斌10两个实体集间联系的类型1:1实体集A中每个实体至多只与实体集B中一个实体相联系。反之亦然。1:n实体集A中每个实体与实体集B中多个实体相联系，而B中每个实体至多只与A中一个实体相联系。m:n实体集A中每个实体与实体集B中多个实体相联系，反之，B中每个实体也与A中多个实体相联系。02二月2023华北科技学院白宏斌11实体：矩形框，框内为实体名称。属性：椭圆，以线与实体连接。属性较多时也可以将实体及其属性单独列表。实体间的联系：菱形框，框内为联系的名称。用线将菱形框与实体相连，线上标注联系类型。E-R图：表示概念模型02二月2023华北科技学院白宏斌12零件mn工人加工工号姓名性别零件号零件名型号02二月2023华北科技学院白宏斌13例：用E-R图表示实体及其联系。学生：学号、姓名、性别、入学分班级：班级号、所属院课程：课程号、课程名、学分教师：教工号、姓名、职称02二月2023华北科技学院白宏斌14E-R图教师1n课程讲授选修n1学生班级组成mn实体间联系的表示学号姓名性别入学分所属院班级号教师职称教工号姓名学分课程号课程名1n课程讲授n1班级组成mn学生选修02二月2023华北科技学院白宏斌159.2数据模型与规范化概念模型数据模型转换按用户观点对信息和数据建模工具：E-R图按计算机系统观点对数据建模层次模型网状模型关系模型02二月2023华北科技学院白宏斌16关系模型基本术语关系：一个关系就是一张二维表，每个关系都有一个关系名。元组：表中的一行即为一个元组，对应存储文件中的一个记录值。属性：表中的列称为属性，每一列有一个属性名。域:属性的取值范围。即不同元组对同一个属性的值所限定的范围。关系模式：对关系的描述。如Student(ID,Sno,Sname,Class)02二月2023华北科技学院白宏斌17关系模型基本术语（续）候选关键字：属性或属性的组合，其值能惟一地标识一个元组。主属性：包含在任何候选关键字中的属性称为主属性，否则为非主属性。主关键字：一个关系中可能有多个候选关键字，从中选择一个作为主关键字。外关键字：如果一个关系中的属性或属性组不是该关系的关键字，但是另一个关系的关键字，称其为该关系的外关键字。如SC（Sno，Cno，Score）中的Sno属性。Student(ID,Sno,Sname,Class)02二月2023华北科技学院白宏斌18关系的性质每列都是同类型的数据，来自同一个域；不同的列给予不同的属性名；任意两个元组不能完全相同。行、列的次序可以任意交换。关系的每个属性都必须是不可分的数据项，不能表中有表；02二月2023华北科技学院白宏斌19是否存在问题？数据冗余不一致性插入异常删除异常学号课程学分B001数据库3B002数据库3B001管理信息系统3B003管理信息系统2课程号课程名授课学时B001数据库56B002管理信息系统48知道了“课程名”的值，即可知道“授课学时”的值。称“授课学时”函数依赖于“课程名”，或“课程名”可以决定“授课学时”，记作课程名→授课学时。函数依赖 设R(U)是一个属性集U上的关系，X和Y是U的子集。如果属性集合X中每个属性的值构成的集合唯一地决定了属性集合Y中每个属性的值构成的集合，则属性集合Y函数依赖于属性集合X，计为：X→Y。学号课程号学分101B0013101B0022102B0023如“学分”函数依赖于主关键字{学号、课程}。但决定“学分”的只是“课程”，与“学号”无关，则称“学分”部分函数依赖于{学号、课程}。部分函数依赖

如果非主属性B函数依赖于构成某个候选关键字的一组主属性A的某一个真子集，则称B部分函数依赖于A。学号楼号住宿费101230010235001033500学生住宿的楼号依赖于学号，学生应交的住宿费是由楼号决定的，即“收费”依赖于“楼号”，“楼号”依赖于“学号”，则“收费”传递函数依赖于“学号”。传递函数依赖

在R(U)中，如存在X，Y，Z包含于U，且满足：X—>Y，Y—>Z，则称Z传递函数依赖于X。02二月2023华北科技学院白宏斌23范式定义：在关系规范化过程中，必须遵循一定的准则以指导，一般把这些准则称之为范式。02二月2023华北科技学院白宏斌24范式第一范式：任何符合关系定义的表即满足第一范式。IDNameSexAgeMaleFemale101张三Y

20102李四

Y2102二月2023华北科技学院白宏斌25第二范式定义：如果一个关系不存在部分依赖关系，那么该关系就属于第二范式。 凡是以单个属性作为主关键字的关系自动就是第二范式。因为主关键字只有一个，不会存在部分依赖的情况。因此，第二范式只是针对主关键字是组合属性的关系。02二月2023华北科技学院白宏斌26学号课程学分B001数据库3B002数据库3B001管理信息系统3B003管理信息系统2学号课程B001数据库B002数据库B001管理信息系统B003管理信息系统课程学分数据库3管理信息系统202二月2023华北科技学院白宏斌27第三范式定义：一个关系如果是第二范式的，并且没有传递依赖关系，则该关系就是第三范式。每个非主属性不部分依赖于关键字，也不传递依赖于关键字的关系就是第三范式。02二月2023华北科技学院白宏斌28学号楼号楼号收费1002250012044600130288001508180202二月2023华北科技学院白宏斌29关系规范化的目的：解决关系模式中存在的插入、删除异常，以及数据冗余问题，基本思想：围绕函数依赖的主线，对一个关系模式进行分解，使关系从较低级范式变换到较高级范式。

消除部分函数依赖1NF2NF3NFBCNF。。。消除传递函数依赖。。。。。。关系规范化过程02二月2023华北科技学院白宏斌30例：T(Student(SNO,SName,DeptID,DeptName),CNO,Score)消除复合关系，规范到1NF：关系规范化实例T(SNO,SName,DeptID,DeptName,CNO,Score){SNO,CNO}作为主键，Sname依赖于SNO，则此关系中存在部分函数依赖，T1NF02二月2023华北科技学院白宏斌31T1(SNO,CNO,Score)T2(SNO,SName,DeptID,DeptName)T1关系无部分函数依赖和传递函数依赖，T13NFT2关系SNO-〉DeptID，DeptID-〉DeptName，因此T22NF02二月2023华北科技学院白宏斌32T1(SNO,CNO,Score)T2(SNO,SName,DeptID)T3(DeptID,DeptName)T1，T2，T3关系无部分函数依赖和传递函数依赖，因此T13NF，T23NF，T33NF概念模型转换为关系模型实体转换实体的属性转化为关系的属性，实体的关键字转化为关系的关键字。联系转换1：1StudentIDS(SNO,SName,ID_NO,ID_Address)1：nStudent S_ProvinceS(SNO,SName,Pro_ID)Pro(Pro_ID,Pro_Name)m：nStudent CourseS(SNO,SName)C(CNO,CName)SC(SNO,CNO)9.3数据库设计案例分析02二月2023华北科技学院白宏斌34案例一学校教学管理信息系统中，有学生、生源地省份、班级、班长、教师、班主任相关对象，请设计合理关系模型。02二月2023华北科技学院白宏斌35案例二某销售公司信息系统包含员工、产品、供应商、客户、订单等每个产品属于某一类别，一个类别的产品具有统一的外观，一个产品可以由不同的供应商提供客户分为不同的类别，一个客户可以属于不同类别员工负责不同地区的业务，每位员工有一个上级领导一个订单中有多个产品信息

依据以上描述设计数据库结构。02二月2023华北科技学院白宏斌36案例三某图书出版信息系统中有员工、职位、书店、出版社等基本信息一个员工只能做一个职位的工作每本书有一个书名，一本书可以由多个作者，一个作者可以写多本书每本书在一个时间段内可以有一定幅度的打折每本书在每个销售量区间内会提供作者一定比例的版税，一本书有多个作者时，按照事先约定比例划分

依据以上描述设计数据库结构。02二月2023华北科技学院白宏斌37扩展一一个仓库可以存放多种零件，一种零件可以存放在多个仓库中，用库存量描述某种零件在某个仓库中的数量；一个仓库有多个职工，一个职工只在一个仓库中工作；一个供应商可以供应若干项目的多种零件，每个项目可以使用不同供应商供应的零件，每种零件可以由不同的供应商提供，用供应量描述某个供应商为某个项目提供的零件的数量。请绘制E-R图并给出满足第三范式的关系模型。02二月2023华北科技学院白宏斌38扩展二一个企业数据库，各部门均有多名员工，一名员工仅属于一个部门，每个员工可参与多项工程的研制或者在其中负责管理，每个项目需要多名员工参与，但只有一个管理者，该企业有若干供应商同时为各不同工程供应各种零件，每种零件又可以同时从不同供应商处采购，一个零件又可以由其他若干零件组装而成，或者用来组装其它多种零件。

请绘制E-R图并给出满足第三范式的关系模型。02二月2023华北科技学院白宏斌39扩展三一个学院有多个学生，一个学生只属于一个学院；一个学院有多个教师，一个教师只属于一个学院；一门课归属于一个学院管理，一个学院可以管理多门课程；一门课程可以开设多个教学班，每门课程每个教学班只安排一个任课老师，一个老师可以教多门课程的多个教学班；一个教学班可以有多个同学选修。请绘制E-R图并给出满足第三范式的关系模型。02二月2023华北科技学院白宏斌409.4数据库设计步骤02二月2023华北科技学院白宏斌411系统需求分析2数据库概念结构设计3