SQL-Server-2012数据库技术及应用-关系模型与数据库逻辑设计

周慧教学单元2.2第3章关系模型与数据库逻辑设计(关系规范化)SQLServer2012数据库技术及应用案例2-3图书管理数据库逻辑设计MicrosoftSQLServer2012《SQLServer2012数据库技术及应用》人民邮电出版社2020年5月20日4时45分关系模型与数据库逻辑设计学习导航2数据库系统数据模型数据库设计创建数据库关系模型分析报告概念模型概念模型转为关系模型关系规范化创建基本表操作基本表数据查询创建视图程序设计系统维护数据库开发与维护建模方法SQLServer资源管理器查询编辑器数据库安全数据库恢复数据库传输析分求需计设念概计设理物发开用应计设辑逻MicrosoftSQLServer2012《SQLServer2012数据库技术及应用》人民邮电出版社2020年5月20日4时45分关系模型与数据库逻辑设计知识框架3关系,元组,属性,候选键,主键,外键主属性,非主属性,关系模式关系模型数据操作数据完整性传统:并、交、差、笛卡儿积实体完整性约束专门:投影、选择、连接域完整性约束参照完整性约束数据结构关系规范化1NF:属性不可再分，2NF:取消部分函数依赖，3NF:取消传递函数依赖用户定义完整性约束客观世界事物及联系需求分析↓分析报告概念设计↓概念模型逻辑设计↓关系模型实体转关系：属性→属性，主键→主键联系转关系：一对一：联系并入任意端，加另一端主键一对多：联系并入多端，加另一端主键多对多：联系转为关系，加两端主键内模式—DATABASE模式—TABLE外模式—VIEWRDB转换方法MicrosoftSQLServer2012《SQLServer2012数据库技术及应用》人民邮电出版社2020年5月20日4时45分单元2.2关系模型与数据库逻辑设计能够将数据库概念设计得到的概念模型转换为关系模型能够对关系模型进行实体完整性、域完整性、参照完整性和用户定义完整性的设计能够对关系模型进行规范化和优化培养用英文单词或英文缩写描述和识别属性的习惯4MicrosoftSQLServer2012《SQLServer2012数据库技术及应用》人民邮电出版社2020年5月20日4时45分单元2.2关系模型与数据库逻辑设计关系规范化理论基础关系模型规范化方法数据库逻辑设计有关英文术语培养团队精神和自主学习的能力培养知难而进的学习态度5MicrosoftSQLServer2012《SQLServer2012数据库技术及应用》人民邮电出版社2020年5月20日4时45分工作任务6概念模型关系模型将图书管理数据库概念设计得到的IDEF1X概念模型（案例2-2-2）转换为关系模型根据需求分析的要求进行数据库的完整性设计和规范化处理单元2.2关系模型与数据库逻辑设计MicrosoftSQLServer2012《SQLServer2012数据库技术及应用》人民邮电出版社2020年5月20日4时45分IDEF1X概念模型到关系模型的转换一关系规范化二7单元2.2关系模型与数据库逻辑设计MicrosoftSQLServer2012《SQLServer2012数据库技术及应用》人民邮电出版社2020年5月20日4时45分一、IDEF1X概念模型到关系模型的转换信息世界机器世界（概念模型：IDEF1X）（逻辑模型：关系模型）8读者读者编号姓名类型编号(FK)已借数量地址电话EMAIL...读者类型类型编号类型名称限借数量限借天数逾期罚款丢失罚款...图书图书编号书名出版社编号(O)(FK)作者名出版日期定价是否借出内容简介...借阅图书编号(FK)读者编号(FK)借期还期...图书修复图书编号(FK)修复编号损坏程度损坏原因修复内容修复日期修复费用...罚款读者编号(FK)罚款编号罚款原因罚款金额罚款日期...出版社出版社编号出版社名称出版社地址联系电话EMAIL联系人MicrosoftSQLServer2012《SQLServer2012数据库技术及应用》人民邮电出版社2020年5月20日4时45分读者读者编号姓名类型编号(FK)已借数量地址电话EMAIL...读者类型类型编号类型名称限借数量限借天数逾期罚款丢失罚款...图书图书编号书名出版社编号(O)(FK)作者名出版日期定价是否借出内容简介...借阅图书编号(FK)读者编号(FK)借期还期...图书修复图书编号(FK)修复编号损坏程度损坏原因修复内容修复日期修复费用...罚款读者编号(FK)罚款编号罚款原因罚款金额罚款日期...出版社出版社编号出版社名称出版社地址联系电话EMAIL联系人实体转换为关系独立实体：读者类型、读者、出版社、图书从属实体：罚款、图书修复直接转换为关系，实体的属性就是关系的属性，实体的主键就是关系的主键9一、IDEF1X概念模型到关系模型的转换MicrosoftSQLServer2012《SQLServer2012数据库技术及应用》人民邮电出版社2020年5月20日4时45分读者读者编号姓名类型编号(FK)已借数量地址电话EMAIL...读者类型类型编号类型名称限借数量限借天数逾期罚款丢失罚款...图书图书编号书名出版社编号(O)(FK)作者名出版日期定价是否借出内容简介...借阅图书编号(FK)读者编号(FK)借期还期...图书修复图书编号(FK)修复编号损坏程度损坏原因修复内容修复日期修复费用...罚款读者编号(FK)罚款编号罚款原因罚款金额罚款日期...出版社出版社编号出版社名称出版社地址联系电话EMAIL联系人联系转换为关系（1：n）确定联系—标识联系：读者与罚款、图书与图书修复（迁移为主属性）确定联系—非标识联系（强制）：读者类型与读者（迁移为非主属性）确定联系—非标识联系（非强制）：出版社与图书（设置为允许空）Visio建立IDEF1X概念模型已经自动将父实体的主键迁移到子实体中作为主属性外键（FK）或者非主属性外键（FK）或者设置为允许空。10一、IDEF1X概念模型到关系模型的转换MicrosoftSQLServer2012《SQLServer2012数据库技术及应用》人民邮电出版社2020年5月20日4时45分读者读者编号姓名类型编号(FK)已借数量地址电话EMAIL...读者类型类型编号类型名称限借数量限借天数逾期罚款丢失罚款...图书图书编号书名出版社编号(O)(FK)作者名出版日期定价是否借出内容简介...借阅图书编号(FK)读者编号(FK)借期还期...图书修复图书编号(FK)修复编号损坏程度损坏原因修复内容修复日期修复费用...罚款读者编号(FK)罚款编号罚款原因罚款金额罚款日期...出版社出版社编号出版社名称出版社地址联系电话EMAIL联系人联系转换为关系（m:n）不确定联系：读者与图书Visio建立IDEF1X概念模型时建立了一个关联实体“借阅”，并在建立父实体“读者”和关联实体“借阅”，父实体“图书”和关联实体“借阅”之间的标识联系时，分别将父实体的主键迁移到关联实体中作为组合主键（PK），本身成为其外键（FK）。11一、IDEF1X概念模型到关系模型的转换MicrosoftSQLServer2012《SQLServer2012数据库技术及应用》人民邮电出版社2020年5月20日4时45分综合以上，根据标识要求，将中文实体和属性名称转换为英文标识的标准命名标识符。图书管理数据库逻辑设计得到的关系模型的7个关系模式如下。（1）读者类型：ReaderType（TypeID,Typename,LimitNum,LimitDays,DelayFine,LostFine）PK：TypeID（2）读者：Reader（RID,Rname,TypeID,Lendnum,Address,TEL,EMAIL）PK：RIDFK：TypeID（3）罚款：Fine（RID,FineID,FineReason,Fines,FineDate）PK：RID+FineIDFK：RID（4）出版社：PublishingHouse（PHID,Publisher,Address,TEL,EMAIL,contacts）PK：PHID（5）图书：Book（BID,Bname,PHID,Author,PubDate,Price,LentOut,Abstract）PK：BIDFK：PHID（6）图书修复：BookRepaire（BID,RepaireID,DamagedCondition,DamageCauses,RepairContent,DateOfRepairing,CostOfRepairing）PK：RID+RepaireIDFK：BID（7）借阅：Borrow（RID,BID,LendDate,ReturnDate）PK：RID+BID+LendDateFK：RID,BID12一、IDEF1X概念模型到关系模型的转换MicrosoftSQLServer2012《SQLServer2012数据库技术及应用》人民邮电出版社2020年5月20日4时45分IDEF1X概念模型到关系模型的转换一关系规范化二13单元2.2关系模型与数据库逻辑设计MicrosoftSQLServer2012《SQLServer2012数据库技术及应用》人民邮电出版社2020年5月20日4时45分不规范：产生数据冗余，带来很多问题。规范：提高数据的结构化、共享性、一致性和可操作性。范式：规范化的程度，级别。规范化：在关系数据库中的每个关系都需要进行规范化，使之达到一定的规范化程度。14二、关系规范化MicrosoftSQLServer2012《SQLServer2012数据库技术及应用》人民邮电出版社2020年5月20日4时45分二、关系规范化第一范式（1NF）1第二范式（2NF）2第三范式（3NF）3BC范式415MicrosoftSQLServer2012《SQLServer2012数据库技术及应用》人民邮电出版社2020年5月20日4时45分（一）第一范式1NF（FirstNormalForm）定义：设R是一个关系，R的所有属性不可再分，即原子属性。记作：R∈1NF例如：设一个通信录，电话属性可以再分，达不到1NF。问题：电话属性可以再分，不是二维表，不够1NF学号姓名性别电话手机家庭宿舍2014216001赵成刚男13105242389612796361254632014216002李敬女13105543364623115962351592014216003郭洪亮男13105326757389035657903562014216004吕珊珊女131052423367843567790045316MicrosoftSQLServer2012《SQLServer2012数据库技术及应用》人民邮电出版社2020年5月20日4时45分解决方法1：在属性上展开学号姓名性别手机家庭电话宿舍电话2014216001赵成刚男13105242389612796361254632014216002李敬女13105543364623115962351592014216003郭洪亮男13105326757389035657903562014216004吕珊珊女1310524233678435677900453（一）第一范式1NF（FirstNormalForm）MicrosoftSQLServer2012《SQLServer2012数据库技术及应用》人民邮电出版社2020年5月20日4时45分解决方法2：分解为二个关系学号手机家庭电话宿舍电话20142160011310524238961279636125463201421600213105543364623115962351592014216003131053267573890356579035620142160041310524233678435677900453学号姓名性别2014216001赵成刚男2014216002李敬女2014216003郭洪亮男2014216004吕珊珊女（一）第一范式1NF（Fi