数据库应用技术 （ SQL Server 2008 R2 ）

1. 单元3_2 数据库的逻辑设计 (案例2) 数据库应用技术（SQL Server 2008 R2）

2. 数据库开发与维护-数据库逻辑设计

3. 图书管理信息系统数据库的逻辑设计 工作任务 • 概念模型 关系数据模型 • 将概念设计中所设计的IDEF1X模型转换为关系模型 • 根据需求分析的要求进行数据库的完整性设计和规范化处理

4. 单元目标 • 能力目标 1.能够运用关系数据模型的基本知识将概念模型转换为关系模型。 2.能够根据完整性规则对关系模型进行实体完整性、参照完整性和用户定义完整性的设计。 3.能够应用关系规范化方法对关系模型进行规范化和优化。 4.培养用英文单词或英文缩写描述和识别属性的习惯。 • 知识目标 1.关系数据模型的组成要素和特点。 2.关系数据结构 3.关系的集合运算（并、交、差和笛卡尔积）。 4.关系的专门运算（投影、选择和连接）与应用。 5.关系数据实体完整性、参照完整性、用户定义完整性的概念及应用。 6.概念模型（联系方式1：1，1：n，n：m）到关系模型转换的基本方法。 7.关系规范化的基本概念和关系规范化的基本方法。 8.数据库逻辑设计有关英文术语。 • 素质目标 1.培养严谨的工作态度和工作作风。 2.培养较强的逻辑思维和抽象思维能力。

5. 一、关系规范化 • 不规范：产生数据冗余，带来很多问题。 • 规范：提高数据的结构化、共享性、一致性和可操作性。 • 范式：规范化的程度，级别。 • 规范化：在关系数据库中的每个关系都需要进行规范化，使之达到一定的规范化程度。

6. 一、关系规范化 第一范式 1 第二范式 2 第三范式 3 BC范式 4

7. （一）第一范式1NF（First Normal Form） • 定义：设R是一个关系，R的所有属性不可再分，即原子属性。记作：R∈1NF • 例如：设一个通信录，电话属性可以再分，达不到1NF。 • 问题：电话属性可以再分，不是二维表，不够1NF

8. （一）第一范式1NF（First Normal Form） • 解决方法1：在属性上展开

9. （一）第一范式1NF（First Normal Form） • 解决方法2：分解为二个关系

10. [H1]此列原数据改为现在的数据 （二）第二范式2NF（Second Normal Form） • 定义 设R是一个关系，其所有非主属性完全函数依赖每个候选关键字 记作R∈2NF 或：取消部分函数依赖。 • 假设：有一个教师授课的关系模式。 • 教师授课（职工号,姓名,性别,职称,住址,课程号,课程名,学分,评价） • 主键（候选键）：职工号,课程号。

11. （二）第二范式2NF（Second Normal Form） • 存在问题 • 数据冗余：不同课程同一任教的教师名、职称、住址等存在大量重复（表左边的灰色区域），不同教师同一课程的课程名与学分等大量重复（表右边的灰色区域）。 • 更新异常：冗余带来更新的不一致。如教师张文娟更新职称或地址，课程数据库开发与维护更新名称或学分，多次输入可能因表达方式不同、遗漏或者失误带来不一致。 • 插入异常：没有上课的教师的主属性课程号无值将不允许插入。 • 删除异常：删除某一课程，致使删除有关教师的信息。

12. （二）第二范式2NF（Second Normal Form） • 问题原因：关系属性之间存在部分函数依赖，达不到2NF。 所有非主属性姓名、性别、职称、课程名、学分和教学评价函数依赖主键（职工号,课程号），但是存在主键的一部分“课程号”就可以决定课程名和学分的情况，即非主属性课程名和学分部分函数依赖主键（候选键），依赖关系表现如下。  （职工号，课程号）→课程名，学分 （课程号）→课程名，学分

13. （二）第二范式2NF（Second Normal Form） • 解决办法：对关系进行拆分，原则是概念单一，数据完整（无损） 教师授课（职工号,姓名,性别,职称,住址,课程号,课程名,学分,评价） 上述达不到2NF的关系分解如下： 联系类型关系分解 多教师（职工号,姓名,性别,职称,住址） 对授课（职工号,课程号,评价） 多课程（课程号,课程名,学分）

14. （二）第二范式2NF（Second Normal Form） 教师（职工号,姓名,性别,职称,住址） 授课（职工号,课程号,评价） 课程（课程号,课程名,学分） 教师情况关系 教师授课关系 课程情况关系

15. （三）第三范式3NF（Third Normal Form） • 定义 设R是一个关系，其所有非主属性都不传递函数依赖每个候选键。 或：取消传递函数依赖 记作R∈3NF • 假设：图书管理系统中读者的关系模式。 • 读者（读者号,姓名,类型编号,类型名称,限借数量,限借天数,借阅数量） 主键（候选键）：读者号

16. （三）第三范式3NF（Third Normal Form） • 存在问题 • 数据冗余：同一读者类型的多位（试想有上万名学生）读者对应的类型名称、限借数量和限借天数等数据存在大量重复（表灰色区域）。 • 更新异常：冗余带来更新的不一致。如果要修改限借数量、限借天数可能要改上万处，很可能造成失误。 • 插入异常：在某种读者类型没有对应读者的情况下，不允许插入。 • 删除异常：如果某类型的读者只有一位，则该读者被删除将致使删除对应的读者类型。

17. （三）第三范式3NF（Third Normal Form） • 问题原因：关系属性之间存在传递函数依赖，达不到3NF。 主键“读者号”决定属性“类型编号”，而“类型编号”决定非主属性“类型名称”、“限借数量”和“限借天数”，即这些非主属性通过“类型编号”传递函数依赖主键（候选键）“读者编号”，依赖关系表现如下。 读者号→类型编号，类型编号→（类型名称、限借数量、限借天数）， 类型编号读者号

18. （三）第三范式3NF（Third Normal Form） • 解决办法：对关系进行拆分，原则是概念单一，数据完整（无损） 读者（读者号,姓名,类型编号,类型名称,限借数量,限借天数,借阅数量） 上述达不到2NF的关系分解如下： 联系类型关系分解 多读者（读者号,姓名,类型编号,借阅数量）PK：读者号FK：类型编号 对所属类型（读者号,类型编号） 此联系可以通过在多端加外键省略 一读者类型（类型编号,类型名称,限借数量,限借天数）PK：类型编号

19. （三）第三范式3NF（Third Normal Form） 读者（读者号,姓名,类型编号,借阅数量）PK：读者号FK：类型编号 读者类型（类型编号,类型名称,限借数量,限借天数）PK：类型编号 读者关系 读者类型关系

20. （四）BC范式BCNF（Boyce－Codd Normal Form） • 定义 • 设R是一个关系，其所有所有属性都不传递依赖每个候选关键字， • 记作：R∈BCNF。 • 说明 • 由于关系规范化理论较深，此处不再赘述，读者可以根据实际设计加以体会。

21. 二、图书管理数据库逻辑设计 信息世界 机器世界 （概念模型：IDEF1X） （数据模型：关系模型）

22. 实体转换为关系 二、图书管理数据库逻辑设计 独立实体：读者类型、读者、出版社、图书 从属实体：罚款、图书修复 直接转换为关系，实体的属性就是关系的属性，实体的主键就是关系的主键

23. 联系转换为关系（1：n） 二、图书管理数据库逻辑设计 确定联系——标识联系：读者与罚款、图书与图书修复（迁移为主属性） 确定联系—非标识联系（强制）：读者类型与读者（迁移为非主属性） 确定联系—非标识联系（非强制）：出版社与图书（设置为允许空） Visio建立IDEF1X概念模型已经自动将父实体的主键迁移到子实体中作为主属性外键（FK）或者非主属性外键（FK） 或者设置为允许空。

24. 联系转换为关系（m:n） 二、图书管理数据库逻辑设计 不确定联系：读者与图书 Visio建立IDEF1X概念模型时建立了一个关联实体“借阅”，并在建立父实体“读者”和关联实体“借阅”，父实体“图书”和关联实体“借阅”之间的标识联系时，分别将父实体的主键迁移到关联实体中作为组合主键（PK），本身成为其外键（FK）。

25. 关系规范化 二、图书管理数据库逻辑设计 分析图书管理系统数据库每个关系的属性之间的依赖关系，均达到了3NF。其中，分解的实体“读者”和“读者类型”、“图书”和“出版社”消除了传递函数依赖，使得关系达到了3NF。 （1）读者类型：ReaderType（TypeID,Typename,LimitNum,LimitDays,DelayFine,LostFine） PK：TypeID （2）读者：Reader（RID,Rname,TypeID,Lendnum,Address,TEL,EMAIL） PK：RID FK：TypeID （3）罚 款：Fine（RID,FineID,FineReason,Fines,FineDate） PK：RID+FineID FK：RID （4）出 版 社：PublishingHouse（PHID,Publisher,Address,TEL,EMAIL,contacts） PK：PHID （5）图 书：Book（BID,Bname,PHID,Author,PubDate,Price,LentOut,Abstract） PK：BID FK：PHID （6）图书修复：BookRepaire（BID,RepaireID,DamagedCondition,DamageCauses,RepairContent, DateOfRepairing,CostOfRepairing）PK：RID+RepaireID FK：BID （7）借 阅：Borrow（RID,BID,LendDate,ReturnDate） PK：RID+BID+LendDate FK：RID,BID

26. 关系模型和数据库逻辑设计小结

27. 关系模型和数据库逻辑设计小结 一、关系模型 1.数据结构 基本定义：二维表 基本术语：关系、元组、属性、候选键、主键、外键、主属性、非主属性、关系模式 2. 操作集合 传统的集合运算：并，交，差，笛卡尔积。 专门的关系运算：∏A(R)投影，σF(t) (R)选择，R S 自然连接。 3.完整性约束 实体：主键PK； 域：数据类型等 参照：外键FK； 用户定义：CHECK 逻辑表达式、函数和存储过程等。

28. 关系模型和数据库逻辑设计小结 二、ER模型到关系模型的转换 实体－关系：直接转换，实体的属性就是关系的属性 联系－关系： • 1对1：与任意端合并，加入另一端主键和联系的属性。 • 1对n：与n端合并，加入1端实体的主键和联系的属性。 • m 对n：转换成一个关系，加两端实体的主键和联系的属性。 三、关系规范化 • 1NF：原子属性 • 2NF：不存在部分函数依赖 • 3NF：不存在传递函数依赖

29. Thank You !