第10讲 范式及反范式设计《数据库原理及应用》教学课件

《数据库原理及应用》✩精品课件合集目录03

范式理论01

规范化概念02函数依赖第10章范式及反范式设计04

范式设计05

反范式设计规范化概念【例】设计一个教学管理数据库，希望从该数据库中经常得到下面的有关信息：学号、姓名、年龄、性别、系、系主任姓名、课程号、成绩等。参考设计的关系模式如下：SCS(sno,sname,cno,cname,term,credit,grade)问题：这是不是一个理想的关系模式呢？规范化概念规范化概念关系模式SCS存在的问题是：①冗余大：表中相同信息多次存储，浪费存储空间。②插入异常：若没有招生，课程信息无法插入到数据库中。③删除异常：删除课程就删除了学生记录。④更新异常：由于信息存在大量冗余，更新信息时要付出很大的代价。规范化概念结论：SCS关系模式不是一个理想的模式。原因：由于模式中存在的数据依赖引起的。解决方法：通过分解关系模式来消除其中不合适的数据依赖

数据依赖是各属性间的关联，包括函数依赖、多值依赖和连接依赖。这里我们只学习函数依赖。函数依赖定义１：设R(U)是属性集U上的关系模式。X，Y是U的子集。对于X的每一个具体值，Y都有唯一的值与之对应，则称“X函数决定Y”或“Y函数依赖于X”，记作X

Y。称X为决定因子，Y是依赖因子。X

Y为模式R(U)的一个函数依赖。如：

snosname,(sno,cno)

grade注意：属性间的函数依赖完全来自于现实世界的语义，而不是凭主

观臆断的。函数依赖函数依赖与属性之间的对应取值有关。在关系模式中，如果属性X与Y的值有1:1关系时，则存在函数依赖X→Y，Y→X，如当学生不重名时，sno→sname，sname→sno。如果属性X与Y的值有m:1关系时，则只存在函数依赖X→Y，例如：“sno”与“sage”之间为m:1联系，所以有sno→sage。如果属性X与Y的值有m:n关系时，则X与Y之间不存在任何函数依赖关系。如sno和cno之间不存在任何函数依赖关系。函数依赖定义２：在关系模式R(U)中，如果X

Y，并且对于X的任何一个真子集X

都有XY，则称Y完全函数依赖于X，记作XY。f如果Y部分函数依赖于X，则X必定是组合属性。如：(sno,cno)

sname，snosname,(sno,cno)

sname。

p若X

Y，并且对于X的某一个真子集X

（X

X），有XY，则称Y部分函数依赖于X，记作：XY。p函数依赖定义３：在关系模式R(U)中，如果X

Y，(Y

X)，Y

X，Y

Z，则称Z传递函数依赖于X，记作XZ

。t【例10.3】在STD(sno,dept,dname)，有sno→dept，dept→dname，且dept

sno，则sno→dname。t范式理论定义4：设有关系模式R(U)，其所有的属性都是不可分的基本数据项，则称R属于第一范式，简称1NF，记作R1NF。在任何一个关系数据库系统中，第一范式是对关系模式的一个最起码的要求。不满足第一范式的数据库模式不能称为关系数据库。【例】关系模式SCD(sno,cno,sname,sage,dept,dname,grade)

SCD1NF范式理论定义5：设有关系模式R(U)1NF

，如果其所有非主(码)属性都完全函数依赖于码，则称R属于第二范式，简称2NF，记作R2NF。即关系模式中不存在非主属性对码的部分函数依赖问题。【例】分解法将SCD分解为两个关系模式：

S(sno,sname,sage,dept,dname)，S2NFSC(sno,cno,grade)，SC2NF范式理论定义6：设有关系模式R(U)2NF，如果其所有非主属性都不传递函数依赖于码，则称R属于第三范式，简称3NF，记作R3NF。即关系模式中不存在非主属性对码的传递函数依赖关系。【例】S分解为两个关系模式： ST(sno,sname,sage,dept)，ST3NF SD(dept,dname)，SD3NF范式设计范式设计：一般是将一个符合1NF的关系模式分解为符合3NF的过程。1NF2NF3NF消除非主属性对码的部分依赖关系消除非主属性对码的传送依赖关系范式设计【例】：已知关系模式R(A,B,C,D,E)，R上存在的函数依赖有F={AB→E,B→C,C→D}，求：

①关系模式的码；

②该关系模式满足几范式，为什么？

③将R分解为高一级的范式，并说明理由。范式设计解：①根据函数依赖集F={AB→E,B→C,C→D}由于(A,B)能够决定所有的属性集合，且没有任一真子集能够决定所有的属性集合，所以R的码为(A,B)。③将R分解为R1，R2：

R1（A,B,E），F1={(A,B)→E}；

R2（B,C,D），F2={B→C，C→D}。R1中：不存在非主属性对码的部分函数依赖，所以R1∈2NF。R2中：码为B，则非主属性为C，D，不存在非主属性对码的部分函数依赖，所以R2∈2NF。②已知R的码为(A,B)，所以非主属性为C,D,E。∵F中存在B→C∴存在非主属性C对码(A,B)的部分函数依赖，所以R∈1NF。范式设计【例10.5】某教学管理系统中有学生(student)、课程(course)两个实体，一个学生可以选修多门课程，一门课程可以供多个学生选修，学生通过考试可以获得该门课程的成绩(grade)。若成绩合格可以获得相应的学分，若成绩不合格可以重修该课程，每一次考试成绩保存到成绩表中。可以通过学生选修的课程统计出该学生所获得的总学分(totalcredit)。（1） 根据上述需求，结合实际情况，建立E-R图；（2） 将E-R图转换为关系模式，注明主码。（3）按照数据库规范化设计步骤，分析关系模式是否满足范式设计要求。解：按照数据库的设计步骤进行数据库设计。（1）按照教学管理系统的业务需求,得出系统的概念模型如下。范式设计（2）将概念模型转换为关系模式如下。student(sno,sname,sex,dept,birthday,totalcredit,remarks)course(cno,cname,term,ctime,credit)score(sno,cno,grade)（3）分析关系模式是否满足范式设计要求和业务需求。在关系模式student中，码sno与非码属性totalcredit不存在函数依赖关系，若把总学分属性放在学生关系中，在录入学生信息时，总学分的值总为NULL值。另外，总学分是根据学生选修课程计算出来的，总学分直接依赖grade和credit两个属性，与sno无直接依赖关系，应该把totalcredit从student关系模式中去掉。范式设计在关系模式score（sno,cno,grade）中，sno和cno做为组合主键，它与grade之间是1对1函数依赖关系，但业务需求中应该是1对多的函数依赖关系，即一个学生选修某门课程可以存在多个成绩。要解决上述问题，则可以使用一个无符号的自增逻辑主键（score_id）来替代业务主键（sno,cno），它的作用是区分每一条记录。至于总分学totalcredit的值，可以设计一个存储过程进行计算并显示，无需把它看成某个实体的属性来存放他的值。满足设计要求的关系模式如下。student(sno,sname,sex,dept,birthday,remarks)course(cno,cname,term,ctime,credit)score(score_id,sno,cno,grade)范式设计反范式设计问题：规范化理论使得每张表只表达围绕主码的数据库最核心的内容，数据之间的关联性需要通过外码进行表间连接来实现。在实际使用中，往往需要进行多表连接才能满足业务需求，这会降低系统的查询效率。规范化的思路是从空间最小化的角度来进行的数据库设计，通过规范化使得数据库的空间占用率最低。然而在具体使用中，可能需要关注更高的时间效率，“以空间换时间”也会经常被使用，所以会出现反范式设计的思想。反范式设计【例10.6】在教学管理系统中，经常用到的一个业务需求是查询某学生某门课程的成绩，返回的信息包含学号、姓名、课程号、课程名、成绩等信息。给出反范式设计的关系模式如下。student(sno,sname,sex,dept,birthday,remarks)

∈3NFcourse(cno,cname,term,ctime,credit)

∈3NFscore(sno,cno,sname,cname,grade)

∈1NF反范式设计反范式设计也会带来一些问题，比如更新异常问题，因为学生的姓名(sname)和课程名(cname)在score表中被重复保存，若course表的cname值改变了，则在score表中已经保存的cname值也要发生变化，否则就有更新异常发生。可以考虑在course表中设计一个更新触发器来解决课程名变化的问题。DELIMITER$$CREATETRIGGERtr_course_cna