数据库技术及应用（SQL Server ）2.2

数据库技术及应用（SQLServer）教学单元2.2第3章关系模型与数据库逻辑设计(关系规范化)案例2-3图书管理数据库逻辑设计关系模型与数据库逻辑设计学习导航2关系模型与数据库逻辑设计知识框架3单元2.2关系模型与数据库逻辑设计能力目标能够将数据库概念设计得到的概念模型转换为关系模型能够对关系模型进行实体完整性、域完整性、参照完整性和用户定义完整性的约束设计能够对关系模型进行规范化和优化培养用英文单词或英文缩写描述和识别属性的习惯4单元2.2关系模型与数据库逻辑设计知识目标关系规范化理论基础关系模型规范化方法数据库逻辑设计有关英文术语素质目标培养团队精神和自主学习的能力培养深入探究的学习态度5工作任务单元2.2关系模型与数据库逻辑设计6案例2-3图书管理数据库逻辑设计概念模型关系模型将图书管理数据库概念设计得到的IDEF1X概念模型（案例2-2-2）转换为关系模型根据需求分析的要求进行数据库的完整性约束设计和规范化处理IDEF1X概念模型到关系模型的转换一关系规范化二单元2.2关系模型与数据库逻辑设计7一、IDEF1X概念模型到关系模型的转换8信息世界机器世界（概念模型：IDEF1X）（逻辑模型：关系模型）

实体（E）转换为关系独立实体：读者类型、读者、出版社、图书从属实体：罚款、图书修复直接转换为关系，实体的属性就是关系的属性，实体的主键就是关系的主键一、IDEF1X概念模型到关系模型的转换9联系（R）转换为关系（1：n）确定联系—标识联系：读者与罚款、图书与图书修复（迁移为主属性）确定联系—非标识联系（强制）：读者类型与读者（迁移为非主属性）确定联系—非标识联系（非强制）：出版社与图书（设置为允许空）Visio建立IDEF1X概念模型已经自动将父实体的主键迁移到子实体中作为主属性外键（FK）或者非主属性外键（FK）或者设置为允许空。一、IDEF1X概念模型到关系模型的转换10联系（R）转换为关系（m:n）不确定联系：读者与图书Visio建立IDEF1X概念模型时建立了一个关联实体“借阅”，并在建立父实体“读者”和关联实体“借阅”，父实体“图书”和关联实体“借阅”之间的标识联系时，分别将父实体的主键迁移到关联实体中作为组合主键（PK），本身成为其外键（FK）。一、IDEF1X概念模型到关系模型的转换11综合以上，根据标识要求，将中文实体和属性名称转换为英文标识的标准命名标识符。图书管理数据库逻辑设计得到的关系模型的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）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,BID一、IDEF1X概念模型到关系模型的转换12IDEF1X概念模型到关系模型的转换一关系规范化二单元2.2关系模型与数据库逻辑设计13二、关系规范化不规范：产生数据冗余，带来很多问题。规范：提高数据的结构化、共享性、一致性和可操作性。范式：规范化的程度，级别。规范化：在关系数据库中的每个关系都需要进行规范化，使之达到一定的规范化程度。14二、关系规范化15

第一范式（1NF）1第二范式（2NF）2

第三范式（3NF）3

BC范式4（一）第一范式1NF（FirstNormalForm）定义：设R是一个关系，R的所有属性不可再分，即原子属性。记作：R∈1NF【例3-23】设一个通信录，电话属性可以再分，达不到1NF。问题：电话属性可以再分，不是二维表，不够1NF学号姓名性别电话号码手机号码家庭号码宿舍号码2022216001赵成刚男13105242***612796361254632022216002李敬女13105543***623115962351592022216003郭洪亮男13105326***389035657903562022216004吕珊珊女13105242***7843567790045316（一）第一范式1NF（FirstNormalForm）解决方法1：在属性上展开学号姓名性别手机号码家庭电话宿舍电话2022216001赵成刚男13105242***6127963612546320202216002李敬女13105543***623115962351592022216003郭洪亮男13105326***389035657903562022216004吕珊珊女13105242***78435677900453（一）第一范式1NF（FirstNormalForm）解决方法2：分解为二个关系学号手机家庭电话宿舍电话202221600113105242***61279636125463202221600213105543***62311596235159202221600313105326***38903565790356202221600113105242***78435677900453学号姓名性别2022216001赵成刚男2022216002李敬女2022216003郭洪亮男2022216001吕珊珊女（二）第二范式2NF（SecondNormalForm）定义设R是一个关系，其所有非主属性完全函数依赖每个候选关键字记作R∈2NF或：取消部分函数依赖。【例3-24】有一个教师授课的关系模式如下，试对其进行规范化。教师授课（职工号,姓名,性别,职称,住址,课程号,课程名,学分,评价）主键（候选键）：职工号+课程号。19职工号姓名性别职称住

址课程号

课

程

名学分评价1011张文娟女教授静海花园5-220010微机组装与维护2.0优1011张文娟女教授静海花园5-220013面向过程程序设计10.0良1011张文娟女教授静海花园5-220014数据库开发与维护6.5优1012刘红霞女讲师福莱花苑3-120014数据库开发与维护6.5良1013李晓峰男讲师先锋小区5-220014数据库开发与维护6.5优

[H1]此列原数据改为现在的数据（二）第二范式2NF（SecondNormalForm）存在问题数据冗余：不同课程同一任教的教师名、职称、住址等存在大量重复（表左边的灰色区域），不同教师同一课程的课程名与学分等大量重复（表右边的灰色区域）。更新异常：冗余带来更新的不一致。如教师张文娟更新职称或地址，课程数据库开发与维护更新名称或学分，多次输入可能因表达方式不同、遗漏或者失误带来不一致。插入异常：没有上课的教师的主属性课程号无值将不允许插入。删除异常：删除某一课程，致使删除有关教师的信息。20（二）第二范式2NF（SecondNormalForm）问题原因：关系属性之间存在部分函数依赖，达不到2NF。所有非主属性姓名、性别、职称、课程名、学分和教学评价函数依赖主键（职工号+课程号），但是存在主键的一部分“课程号”就可以决定课程名和学分的情况，即非主属性课程名和学分部分函数依赖主键（候选键），依赖关系表现如下。

（职工号+课程号）→课程名，学分

（课程号）→课程名，学分21（二）第二范式2NF（SecondNormalForm）解决办法：对关系进行拆分，原则是概念单一，数据完整（无损）

教师授课(职工号,姓名,性别,职称,住址,课程号,课程名,学分,评价)

上述达不到2NF的关系分解如下：联系类型

关系分解多

教师(职工号,姓名,性别,职称,住址)对

授课(职工号,课程号,评价)多

课程(课程号,课程名,学分)22（二）第二范式2NF（SecondNormalForm）教师(职工号,姓名,性别,职称,住址)授课(职工号,课程号,评价)课程(课程号,课程名,学分)23教师情况关系教师授课关系课程情况关系职工号姓名性别职称住址职工号课程号评价课程号课程名学分1011张文娟女教授静海花园5-2101120010优20010微机组装与维护2.01012刘红霞女讲师福莱花苑3-1101120013良20013面向过程程序设计10.01013李晓峰男讲师先锋小区5-2101120014优20014数据库开发与维护6.5101220014良101320014优（三）第三范式3NF（ThirdNormalForm）定义设R是一个关系，其所有非主属性都不传递函数依赖每个候选键。或：取消传递函数依赖记作R∈3NF假设：图书管理系统中读者的关系模式。读者(读者号,姓名,类型编号,类型名称,限借数量,限借天数,借阅数量)

主键（候选键）：读者号24读

者编号姓名类型编号类型名称限借数量限借天数借阅数量2000186010张子健1教师69002022216117孟霞3学生33002023216008杨淑华3学生33002023216009程鹏3学生3302（三）第三范式3NF（ThirdNormalForm）存在问题数据冗余：同一读者类型的多位（试想有上万名学生）读者对应的类型名称、限借数量和限借天数等数据存在大量重复（表灰色区域）。更新异常：冗余带来更新的不一致。如果要修改限借数量、限借天数可能要改上万处，很可能造成失误。插入异常：在某种读者类型没有对应读者的情况下，不允许插入。删除异常：如果某类型的读者只有一位，则该读者被删除将致使删除对应的读者类型。25（三）第三范式3NF（ThirdNormalForm）问题原因：关系属性之间存在传递函数依赖，达不到3NF。主键“读者号”决定属性“类型编号”，而“类型编号”决定非主属性“类型名称”、“限借数量”和“限借天数”，即这些非主属性通过“类型编号”传递函数依赖主键（候选键）“读者编号”，依赖关系表现如下。

读者号→类型编号

类型编号→(类型名称、限借数量、限借天数)

类型编号

读者号26（三）第三范式3NF（ThirdNormalForm）解决办法：对关系进行拆分，原则是概念单一，数据完整（无损）

读者（读者号,姓名,类型编号,类型名称,限借数量,限借天数,借阅数量）上述达不到2NF的关系分解如下：联系类型

关系分解多

读者(读者号,姓名,类型编号,借阅数量)PK：读者号FK：类型编号对

所属类型(读者号,类型编号)

此联系可以通过在多端加外键省略一

读者类型(类型编号,类型名称,限借数量,限借天数)PK：类型编号27（三）第三范式3NF（ThirdNormalForm）读者(读者号,姓名,类型编号,借阅数量)PK：读者号FK：类型编号

读者类型(类型编号,类型名称,限借数量,限借天数)PK：类型编号28

读者关系读者类型关系读者编号姓名类型编号借阅数量类型编号类型名称限借数量限借天数2000186010张子健101教师6902022216117孟霞302职员4602023216008杨淑华303学生3302023216009程鹏32（四）BC范式BCNF（Boyce－CoddNormalForm）29定义设R是一个关系，其所有所有属性都不传递依赖每个候选关键字，记作：R∈BCNF。说明由于关系规范化理论较深，此处不再赘述，读者可以根据实际设计加以体会。二、关系规范化30图书管理数据库逻辑设计——关系规范化分析图书管理系统数据库每个关系的属性之间的依赖关系，均达到了3NF。其中，分解的实体“读者”和“读者类型”、“图书”和“出版社”消除了传递函数依赖，使得关系达到了3NF。（1）读者类型：ReaderType（TypeID,Typename,LimitNum,LimitDays,DelayFine,LostFine）PK：TypeID（2）读

者：Reader（RID,Rname,TypeID,Lendnum,Address,TEL,EMAIL）PK：RIDFK：TypeID（3）罚