数据库—03(数据操作、完整性约束)

1、关系代数关系代数 查询优化查询优化 关系模式设计关系模式设计 函数依赖函数依赖 关系模式的范式关系模式的范式 关系代数关系代数 查询优化的方法查询优化的方法 关系模式的范式关系模式的范式 传统的集合操作（并、差、交、乘积）传统的集合操作（并、差、交、乘积） 专门的关系操作（选择、投影、连接、除法等）专门的关系操作（选择、投影、连接、除法等） 五种基本操作（并、差、乘积、选择、投影）五种基本操作（并、差、乘积、选择、投影） 其他非基本操作（可用五种操作合成的其他操作）其他非基本操作（可用五种操作合成的其他操作） 关系代数是以关系为运算对象的一组高级运关系代数是以关系为运算对象的一组高级运 算的集

2、合。算的集合。 集合运算符号：集合运算符号：(并并),(),(交交),-(),-(差差),),( (笛卡尔集笛卡尔集) ) 关系运算符号：关系运算符号： (投影投影), (), (选择选择), ), ( (连接连接) ) 比较运算符号：比较运算符号： ,= , 逻辑运算符号：逻辑运算符号： ( (非非), (), (与与), (), (或或) ) 有相同的关系模式；有相同的关系模式； R(X,Y,Z) S(X,Y,Z)R(X,Y,Z) S(X,Y,Z) 有相同的度（列数相同），且对应有相同的度（列数相同），且对应 属性出自同一个域；属性出自同一个域； R(X,Y,Z) S(A,B,C) R(X

3、,Y,Z) S(A,B,C) 并：由属于并：由属于R R或属于或属于S S或同时属于或同时属于R R和和S S的元组构成的元组构成 的集合。（的集合。（RSRS） 差：由属于差：由属于R R而不属于而不属于S S的所有元组构成的集合。的所有元组构成的集合。 （R-SR-S） 交：由同时属于交：由同时属于R R和和S S的元组构成的集合。的元组构成的集合。 （RSRS） X Y Z a 3 e b 1 d a 5 a R X Y Z b 1 d c 2 c a 3 e S X Y Z a3e b 1d a5a c2c （RSRS） X Y Z a 5a （R-SR-S） X Y Z a3e b1

4、d （RSRS） 由R的第一个元组依次与S的所有元组组合,然后 R的第二个元组直到最后一个元组依次与S所有元组 组合，形成新的关系。 A B C a 1 c b 3 d c 2 c R D E a 1 b 3 S A B C D E a 1 c a 1 a 1 c b 3 b 3 d a 1 b 3 d b 3 c 2 c a a c 2 c b 3 R S 按给定条件从关系中挑选出满足条件的元组组成集合。 选择操作是对关系进行水平分割。 F (R)=t|t R F(t)=true 学号学号 姓姓 名名 性别性别 成绩成绩 001张三男81 002李四女99 003王五女78 F:F:性别性别

5、=“=“女女” ” 成绩成绩8080 学号学号 姓姓 名名 性别性别 成绩成绩 002李四李四女女99 F (学生) 按给定条件从关系中挑选出指定的属性组成新的关系。 投影操作是对关系进行垂直分割。 i1,i2,im (R)=t|t = trR 学号学号 姓姓 名名 性别性别 成绩成绩 001张三男81 002李四女99 003王五女78 成绩单成绩单= 姓名，成绩姓名，成绩（学生）（学生） 姓姓 名名 成绩成绩 张三81 李四99 王五78 投影可能产生重复元组，必须删除重复元组！投影可能产生重复元组，必须删除重复元组！ 学号学号 姓姓 名名 性别性别 成绩成绩 001张三男81 002李四

6、女99 003王五女78 004张三女81 姓姓 名名 成绩成绩 张三81 李四99 王五78 张三81 成绩单成绩单= 2，4（学生）（学生） 满足条件的两关系所有元组，按一切可能拼接后 组成新的关系。 X Y Z a 3 c b 4 d c 2 e A B a 1 d 2 f 3 X Y Z A B a 3 c a 1 a c d 2 b 4 d a 1 b 4 d d 2 b 4 d f 3 c 2 c a 1 F: YB RS=F(R S) F 一般要求参与运算的两个关系必须有一 个以上的公共属性。 如果没有，则自然连接等于乘积操作。 X Y Z A a 1 a a d 2 b d c

7、 3 c f X Y Z a 1 a b 2 b c 3 c d 4 d A Y a 1 d 2 f 3 c 7 T = R S 乘积乘积，求出参与运算的，求出参与运算的R和和S的的 乘积乘积R R S S； 选择选择，挑选出在所有公共属性，挑选出在所有公共属性 上相等的值上相等的值 R.Ai=S.AiR.Ai=S.Ai； 投影投影，去掉重复属性；，去掉重复属性； X R.Y S.Y Z A a 1 1 a a a 1 2 a d a 1 3 a f a 1 7 a c b 2 1 b a b 2 2 b d b 2 3 b f b 2 7 b c c 3 1 c a c 3 2 c d c

8、3 3 c f c 3 7 c c d 4 1 d a d 4 2 d d d 4 3 d f d 4 7 d c 在自然连接的基础上，保留自然连接舍弃 的元组得到的关系。 X Y Z a 1 a b 2 b c 3 c R A Y a 1 d 2 f 5 S X Y Z A a 1 a a d 2 b d c 3 c null null 5 null f 外连接外连接 X Y Z A a 1 a a d 2 b d c 3 c null 左连接左连接 null 5 null f 右连接右连接 外部并是并的扩充操作。 b 2 b X Y Z a 1 a c 3 c R R A Y a 1 d

9、2 f 5 S S X Y Z A a 1 a null d 2 b null c 3 c null null 1 null a 外部并外部并 null 2 null d null 5 null f 主要是对关系代数表达式做等价变换，合 理调整关系代数表达式中的操作顺序，减少时 间和空间开销，提高执行效率。 学号学号 001 002 100 分数分数 100 100 100 姓名姓名 张艺张艺 李四李四 赵六赵六 学号学号 001 002 003 100 姓名姓名 张艺张艺 李四李四 王五王五 赵六赵六 分数分数 100 100 80 100 学号学号 001 002 100 分数分数 100

10、 100 100 学号学号 001 002 003 100 姓名姓名 张艺张艺 李四李四 王五王五 赵六赵六 学号学号 001 002 003 100 分数分数 100 100 80 100 求出获满分的学求出获满分的学 生的姓名和学号生的姓名和学号 1）尽可能早执行选择操作； 2）把乘积和随后的选择合并成连接操作； 3）一连串的选择和一连串的投影应同时运算； 4）若在表达式中多次出现某个子表达式，应 预先将该表达式算出结果并且保存； 5）适当地对关系文件进行预处理。 尽可能减少扫描文件的次数尽可能减少扫描文件的次数! 姓名姓名 S1 S1 S1 S2 S2 S3 S3 寝室寝室 R201 R

11、201 R201 R304 R304 R206 R206 课程课程 C1 C3 C4 C1 C2 C2 C3 学分学分 4 3 4 4 3 3 3 成绩成绩 86 91 83 75 79 96 86 1）数据冗余 2）更新异常 3）插入异常 4）删除异常 姓名姓名 S1 S2 S3 寝室寝室 R201 R304 R206 学生关系学生关系 课程课程 C1 C2 C3 C4 学分学分 4 3 3 4 课程关系课程关系 姓名姓名 S1 S1 S1 S2 S2 S3 S3 寝室寝室 R201 R201 R201 R304 R304 R206 R206 课程课程 C1 C3 C4 C1 C2 C2 C

12、3 学分学分 4 3 4 4 3 3 3 成绩成绩 86 91 83 75 79 96 86 姓名姓名 S1 S1 S1 S2 S2 S3 S3 课程课程 C1 C3 C4 C1 C2 C2 C3 成绩成绩 86 91 83 75 79 96 86 学生修课成绩关系学生修课成绩关系 姓名姓名 S1 S1 S1 S2 S2 S3 S3 寝室寝室 R201 R201 R201 R304 R304 R206 R206 课程课程 C1 C3 C4 C1 C2 C2 C3 学分学分 4 3 4 4 3 3 3 成绩成绩 86 91 83 75 79 96 86 l函数依赖函数依赖 l范式与规范化范式与规

13、范化 主属性：候选关键字的某个属性主属性：候选关键字的某个属性 非主属性：不属于候选关键字的属性非主属性：不属于候选关键字的属性 设设U=A1U=A1，A2A2，AnAn是属性集合，是属性集合，R(U) R(U) 是是 U U上的一个关系。上的一个关系。X X，Y Y是是U U的子集。若对于的子集。若对于R(U)R(U)下下 任何一个可能的关系，均有任何一个可能的关系，均有X X的一个值对应于的一个值对应于Y Y的的 唯一具体值，称唯一具体值，称Y Y单值函数依赖于单值函数依赖于X X。记作。记作 X XY;Y; 进而若进而若Y YX X，则，则X X与与Y Y互相依赖互相依赖, ,记作记作X

14、 XY Y。 学号学号 姓名姓名 学号学号 001 002 003 姓名姓名 张三张三 王四王四 李五李五 性别性别 女女 男男 女女 R（学号，课程学号，课程，成绩），成绩） 学号，课程学号，课程成绩成绩 设R(U)是U上的一个关系。X，Y是U的子 集。X是X的真子集。若对于R(U)下任何 一个可能的关系，均有 XY，但！XY， 则称Y完全函数依赖于X，记作 F X Y 若若XY，且，且XY，则称，则称Y部分依赖于部分依赖于X， 记作：记作： P X Y R（学号，姓名学号，姓名，总成绩），总成绩） 学号，姓名学号，姓名总成绩总成绩 学号学号总成绩总成绩 XY，但！，但！YX，若，若YZ，则

15、，则XZ， 记作：记作： t X Z R（工号工号 , 姓名姓名, 职务职务, 工作量工作量, 系系, 系主任）系主任） 工号工号 系主任系主任 系系 满足某些特定条件的关系模式称为范式满足某些特定条件的关系模式称为范式 NF，Normal Form 一般情况下，第一，第二范式存在许多一般情况下，第一，第二范式存在许多 缺点，实际的关系数据库一般使用缺点，实际的关系数据库一般使用第三第三 范式范式。 如果关系如果关系R的所有属性都是不可再分的数的所有属性都是不可再分的数 据项，则称该关系属于第一范式（据项，则称该关系属于第一范式（ R1NFR1NF ）。）。 这是任何规范关系都要遵守的的最低要

16、求。这是任何规范关系都要遵守的的最低要求。 姓名姓名 S1 S1 S1 S2 S2 S3 S3 寝室寝室 R201 R201 R201 R304 R304 R206 R206 课程课程 C1 C3 C4 C1 C2 C2 C3 学分学分 4 3 4 4 3 3 3 成绩成绩 86 91 83 75 79 96 86 如果如果R1NFR1NF，并且并且R R中的每一个非主中的每一个非主 属性都完全依赖于属性都完全依赖于R R的主键，的主键，则称该关系属则称该关系属 于第二范式（于第二范式（ R2NFR2NF ）。）。 R（S,C,G,TN,TS） F（S,C)G,CTN,TNTS） 因为因为TN

17、TN，TSTS部分依赖于关键字部分依赖于关键字 （S S，C C），），R R不属于第二范式。不属于第二范式。 如果如果R2NFR2NF，并且并且R R中的每一个非主中的每一个非主 属性都不传递依赖于属性都不传递依赖于R R的主键，的主键，则称该关系则称该关系 属于第三范式（属于第三范式（ R3NFR3NF ）。）。 课程课程 C1 C2 C3 C4 教师教师 T1 T2 T3 T4 专长专长 Z1 Z2 Z3 Z4 课程课程 C1 C2 C3 C4 教师教师 T1 T2 T3 T4 教师教师 T1 T2 T3 T4 专长专长 Z1 Z2 Z3 Z4 如果如果R1NFR1NF，并且并且R R中

18、的每一个函数中的每一个函数 依赖依赖X XY Y（ ! Y Y X），必有，必有X X是是R R的超键，的超键， 则称该关系属于则称该关系属于Boyce-Codd范式范式 （ RBCNFRBCNF ）。）。 所有非主属性对键是完全函数依赖所有非主属性对键是完全函数依赖 所有主属性对不包含它的键是完全所有主属性对不包含它的键是完全 函数依赖函数依赖 没有属性完全函数依赖于非键的任没有属性完全函数依赖于非键的任 何属性组合何属性组合 判断关系式是否属于判断关系式是否属于BCNF 设有关系式设有关系式 R=R=学生学号学生学号S S，课程号，课程号C C，教师，教师TT 每门课有若干个教师，每个教师

19、只教每门课有若干个教师，每个教师只教 一门课程，学生选定课程就对应一个一门课程，学生选定课程就对应一个 固定的教师。固定的教师。 （S，C）T （S，T）C TC R BCNF 非规范表 使每个属性都不再可分使每个属性都不再可分 1NF1NF 消除非主属性部分依赖消除非主属性部分依赖 2NF2NF 消除非主属性传递依赖消除非主属性传递依赖 3NF3NF BCNFBCNF 消除主属性对键的部分依赖和传递依赖消除主属性对键的部分依赖和传递依赖 1）确定所有的侯选关键字 2）确定关系的主属性和非主属性 3）选定主键 4）找出属性间的函数依赖 5）根据应用特点，确定规范到几范式 6）分解必须是无损的

20、7）分解后的关系必须是独立的 lR（A，B，C，E，F，G，H） lF（ABCH),(BG), (CE),(EF) lR1(A,B,C,H) lR2(C,E,F) lR3(A,B,C,G,H) lR1,R2,R3分别属于什么范式分别属于什么范式 l如果不分解关系模式，对数据操作会出现如果不分解关系模式，对数据操作会出现 异常情况。异常情况。 l分解就是运用关系代数的投影运算把一个分解就是运用关系代数的投影运算把一个 关系模式分解为几个关系模式，就是用几关系模式分解为几个关系模式，就是用几 个小表代替原来的一个大表，使数据结构个小表代替原来的一个大表，使数据结构 更合理。更合理。 l要求：通过自

21、然连接运算可以把分解后的要求：通过自然连接运算可以把分解后的 几个表还原成原来的大表，还原信息不能几个表还原成原来的大表，还原信息不能 多也不能少。多也不能少。 l自反律自反律 如果如果Y YX XU U，则，则X XY Y l增广律增广律 如果如果X XY Y，且，且Z ZU U，则，则XZXZYZYZ l传递律传递律 如果如果X XY,YY,YZ,Z,则则X XZ Z l合并律合并律 如果如果XY,XZ,则则XYZ l伪传递律伪传递律 如果如果XY,YWZ,则则XWZ l分解律分解律 如果如果XY,Z Y,则则XZ 分解方法：分解方法： 工号工号 101101 102102 103103

22、104104 工种工种 车工车工 车工车工 钳工钳工 铣工铣工 定额定额 8080 8080 8080 7070 W W W1(工号，工种) W2(工种，定额) W W W1(W1(工号，工种工号，工种) ) W2(W2(工号，定额工号，定额) ) W W W1(工号，定额) W2(工种，定额) 工号工号 101 102 103 104 定额定额 80 80 80 70 工种工种 车工车工 钳工钳工 铣工铣工 定额定额 80 80 70 W W W1(W1(工号，定额工号，定额) ) W2(W2(工种，定额工种，定额) ) 当定额有相当定额有相 同值的时候，不同值的时候，不 清楚工号和工种清楚

23、工号和工种 之间的关系，信之间的关系，信 息丢失。此方案息丢失。此方案 不妥。不妥。 当工号为当工号为103的的 工人定额由工人定额由70变成变成 80，此时不清楚其，此时不清楚其 工种变换的情况。工种变换的情况。 更新异常，此方案更新异常，此方案 不妥。不妥。 W W W1(W1(工号，工种工号，工种) ) W2(W2(工号，定额工号，定额) ) 工号工号 101 102 103 104 工种工种 车工车工 车工车工 钳工钳工 铣工铣工 工号工号 101 102 103 104 定额定额 80 80 80 70 通过自然连接通过自然连接 可恢复信息。没有可恢复信息。没有 操作异常。操作异常。

24、 W W W1(W1(工号，工种工号，工种) ) W2(W2(工种，定额工种，定额) ) 工号工号 101 102 103 104 工种工种 车工车工 车工车工 钳工钳工 铣工铣工 工种工种 车工车工 钳工钳工 铣工铣工 定额定额 80 80 70 v分解必须是无损的分解必须是无损的 v分解是否保持函数依赖分解是否保持函数依赖 W W W1(W1(工号，工种工号，工种) ) W2(W2(工种，定额工种，定额) ) W W W1(W1(工号，工种工号，工种) ) W2(W2(工号，定额工号，定额) ) W W W1(W1(工号，定额工号，定额) ) W2(W2(工种，定额工种，定额) ) 信息丢

25、失，有损连接信息丢失，有损连接 函数依赖丢失函数依赖丢失 最佳方案最佳方案 关系模式关系模式R（学号（学号S，课程号，课程号C，成绩，成绩G，任课教，任课教 师师TN，教师专长，教师专长TS） 函数依赖函数依赖F=(S,C)G，CTN，TNTS 1=SCG(S,C,G),CTN(C,TN),TNTS(TN,TS) 2=SCG(S,C,G),GTNTS(G,TN,TS) 判断分解是否为无损分解判断分解是否为无损分解 1.1.构造表，根据模式填入构造表，根据模式填入a aj j或或b bij ij 2.2.根据函数依赖修改表中项目根据函数依赖修改表中项目 3.3.如果表中有一行为全如果表中有一行为

26、全a a，则分解为无，则分解为无 损分解损分解 SCGTNTS SCGa1a2a3b14b15 CTNb21a2b23a4b25 TNTSb31b32b33a4a5 (S,C)G CTN TNTS a2 a2 a4 a5 a4 a4 a4 a4 a4 a4 a5 a5 a5 a1a2a3a4a5 SCGTNTS SCGa1a2a3b14b15 GTNTSb21b22a3a4a5 (S,C)G CTN TNTS 关系模式关系模式R（工号（工号S，工种，定额），工种，定额） 函数依赖函数依赖F=， 1=SN(S，N)，TN(T，N) 2=SN(S，N)，ST (S，T) 3=ST(S，T)，TN(

27、T，N) ST TN SNa1b12a3 TNb21a2a3 STN ST TN SNa1b12a3 STa1a2b23 STN a1 a1 a2 a2a2a1a3 ST TN STa1a2b13 TNb21a2a3 STN a2 a2 a3 a3a2a1a3 R1R1 R2R2R1-R2R1-R2 或者或者R1R1 R2R2R2-R1R2-R1 关系模式关系模式R（工号（工号S，工种，定额），工种，定额） 函数依赖函数依赖F=， 1=SN(S，N)，TN(T，N) 2=SN(S，N)，ST (S，T) 3=ST(S，N)，TN(T，N) SNTN=N SN-TN=S SNTN=N SN-TN

28、=S 或或TN-SN=T; TN-SN=T; N NS S或或N NT T不成立不成立 SNST=S SN-ST=N SNST=S SN-ST=N 或或ST-SN=T; ST-SN=T; S ST T和和S SN N都成立都成立 STTN=T ST-TN=S STTN=T ST-TN=S 或或TN-ST=N; TN-ST=N; T TN N成立成立 设设X=Y,Z，Y,Z属于不同的域，属于不同的域， 如果如果X1=X2， 则则Y1,Z1=Y2,Z2 因为因为Y,Z属于不同域，属于不同域， Y1!=Z2; 此时，此时，Y1=Y2; Z1=Z2 根据函数依赖的定义，根据函数依赖的定义， XY 学号学号姓名姓名课程号课程号成绩成绩 201乔丹乔丹312676 201乔丹乔丹312883 202布什布什312691 202布什布什312865 X=X=学号，姓名学号，姓名 ； 第一个元组第一个元组 学号，姓名学号，姓名 等于第二个元组等于第二个元组 学号，姓名学号，姓名 ； 此时，第一个学号等于第二个学号。此时，第一个学号等于第二