1 / 45

第四章 关系数据库范式

第四章 关系数据库范式. 本章的主要内容： 范式的概念及1 NF — 5NF 模式分解的特性及其判定算法 关系模式的规范化算法. 4.1 数据库及其范式. 例子： r ( 职工姓名 工资级别 基本工资 ) 李小明 高工2 3200 张 亮 中工1 1800 刘 林 中工1 1800

kiaria
Download Presentation

第四章 关系数据库范式

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 第四章 关系数据库范式

  2. 本章的主要内容： • 范式的概念及1NF—5NF • 模式分解的特性及其判定算法 • 关系模式的规范化算法

  3. 4.1 数据库及其范式 例子：r (职工姓名 工资级别 基本工资) 李小明 高工2 3200 张 亮 中工1 1800 刘 林 中工1 1800 王晓宁 高工2 3200 苏 丹 中工4 2000 . • 冗余； • 插入异常； • 删除异常。

  4. 4.1.1 第一范式 定义 (1NF, Normal Form) 如果一个关系模式R中的每个属性A的域值都是原子的，即属性值是不可再分的，则关系模式R属于第一范式，简记为R1NF。若数据库模式R中的每个关系模式都是1NF，数据库模式R1NF。 addr ( 姓 名 地 址 ) 李小明 北京市白石桥路7号 张 亮 天津市和平街18号 王国全 太原市解放路35号 苏 丹 北京市复外大街12号 addr ( 姓 名，城市， 地 址) teach (教 师 课 程 ) 孙鲁涛 DS, DB,OS 周 晴 C，C++

  5. elective(SNAME COURSE DEPT ) 刘 芳 DB 计算机 刘 芳 OS 计算机 林荔娜 C++ 自 控 林荔娜 DB 计算机 周 晴 C++ 自 控 elective (SNAME COURSE 刘 芳 DB 刘 芳 OS 林荔娜 C++ 林荔娜 DB 周 晴 C++ . elective (COURSE DEPT ) DB 计算机 OS 计算机 C++ 自 控 SNAME、COURSEDEPT COURSE DEPT

  6. 4.1.2 第二范式 定义 已知关系模式R，R中的属性A以及R上的函数依赖集F。如果A包含在R的某个候选键中，则称A为主属性，否则称A为非主属性。 定义(2NF) 设关系模式R(U, F)，如果R1NF且所有的非主属性完全依赖于R的每个键，则R2NF。若数据库模式R中的每个关系模式R都属于2NF，则数据库模式R2NF。

  7. 4.1.3 第三范式 course (COURSE DEPT BUILDING ) DB 计算机 中心楼9层 OS 计算机 中心楼9层 C++ 自 控 中心楼12层 。 course(COURSE DEPT) dpt(DEPT BUILDING) COURSE DEPT， DEPT BUILDING 定义(3NF) 设关系模式R(U, F)，若R1NF且在R中没有非主属性传递依赖于R的键，则R3NF。如果数据库模式R中每一关系模式都是第三范式，则数据库模式R3NF。

  8. 证明： 假设R中非主属性A部分依赖于关键字K。则存在KK使得F|=K→A。 因KK，有K→K，但K→K。于是有K→K，K →K，K→A，并且 AK，因而A传递依赖于K，即 R3NF，与已知矛盾。 定理1 若关系模式R(U,F)3NF，则R2NF。

  9. 4.1.4 Boyce-Codd范式 例 R ( Building Room Department) 中心教学楼 801 计算机系 中心教学楼 802 计算机系 中心教学楼 803 计算机系 中心教学楼 823 数 学 系 中心教学楼 824 数 学 系 . Building、Room → Department Department → Building Room、 R1(Department ，Building ) R2(Department ， Room )

  10. 定义1(BCNF) 设关系模式R(U,F)，若R1NF且R中没有任何属性传递依赖于R的任一键，则RBoyce-Codd范式(BCNF)。如果数据库模式R中的每个关系模式RBCNF，则数据库模式RBCNF。 定义2 (BCNF) 设关系模式R(U,F)， 若对于 YR且属性 ARY 有Y→A，Y必为R的关键字，则关系模式 RBCNF。 ** 若关系模式R(U,F)BCNF，则R3NF。

  11. 例1：关系模式SJP（学生，课程，名次） FD：学生、课程→名次 课程、名次→学生 例2：CSZ（城市，街道，邮编） FD：城市、街道→ 邮编 邮编→城市 街道、邮编→城市 Z-C(邮编，城市)；S-Z(街道，邮编)

  12. 无损连接性 定义 设模式R(U，F),ρ={R1, R2, …, RK }是R的一个分解，若对R的任一满足F的关系r下式成立： • r = R1 (r) R2 (r) … Rk (r) • 则称分解ρ是满足F的无损连接分解。记： • mρ(r) =R1 (r) R2 (r) … Rk (r) 4.2 关系模式的规范化 4.2.1 关系模式的分解 定义 设关系模式R(U)，关系模式的集合 ρ={R1(U1), R2(U2),…,RK(UK)}, 若U1∪U2∪…∪UK=U, 则称ρ是关系模式R(U)的一个分解。 无损连接分解：r = mρ(r)

  13. r ( A B C) 2 4 3 3 4 6 4 9 5 r1 ( A B) 2 4 3 4 4 9 r2 ( B C) 4 3 4 6 9 5 引理 设关系r(R)，ρ={R1, R2, …, RK }是R的一个分解。 则有： (1). r  mρ(r) (2). Ri (mρ(r)) =Ri (r) (3). mρ(mρ(r)) = mρ(r) 证明: (2). 因r mρ(r), 则 Ri(r)  Ri (mρ(r))。 又 tiRi(mρ(r)) , 则有tmρ(r), 使t[Ri]=ti， 而由mρ(r)的定义知，ti Ri (r)，因此有Ri (mρ(r)) Ri (r) 则 有Ri (mρ(r)) =Ri (r)。

  14. 算法4.2.1 判断分解ρ是否具有无损连接性 输入： 关系模式R(A1，A2，…，An)及其分解 ρ={R1，R2，…，RK }，函数依赖集F。 输出：分解ρ是否具有无损连接性。

  15. LOSSNESS(R，F，ρ) (1)．构造一个k行n列的表T： a j (Aj Ri ) Ti j = bi j (Aj Ri ) (2). for each FD X→Y in F do for each tl 、tmT if ti[X]=tm[X] and ti [Y]tm [Y] then for each Aj in Y if ti [Aj]=aj or tm[Aj]=aj then ti[Aj ]=tm [Aj ]=aj else ti[Aj]=tm [Aj]=bij (i <m) (3)．for each tT do if t=a1, a2,…,an then return (true) return (false)

  16. 例：关系模式R=ABCDE， F={A→C，B→C，C→D，DE→C，CE→A}， 分解 ={AD，AB，BE，CDE，AE }. 验证：  是否是无损分解。 A B C D E a1 b12 b13 a4 b15 a1 a2 b23 b24 b25 b31 a2 b33 b34 a5 b41b42 a3 a4a5 a1 b52 b53 b54a5

  17. 例： 关系模式R=ABCDE， F={A→C，B→C，C→D，DE→C，CE→A}。  ={AD，AB，BE，CDE，AE }. A B C D E a1 b12b13a4 b15 a1a2 b23 →b13b24 →a4 b25 b31→a1a2b33 →b13→a3b34 →a4a5 b41→a1b42a3 a4a5 a1 b52 b53 →b13 →a3 b54 →a4a5 17

  18. 定理 算法能正确判断分解 是否是无损分解。 证明： 若表中没有全a行。可将结果表看作R的一个关 系r，r满足F。由表T的构造知， r在分解 中的每个Ri上全为a，投影连接后应有全a的行。 但r中没有，这说明mρ(r)  r，分解 是连接有损的。 相反，最后表中有全a行。全a行是为满足FD将T中元 素替代得到的，若对T中元素赋值， 替代结果相当于作连接。全a行对应的值是mρ(r)中的一个元组，而该元组是r中的一个元组， 因而有mρ(r)  r。而r mρ(r) ，则有 r=mρ(r)，分解 是连接无损的。 证毕。 p60 16

  19. 定理3 设关系模式R的一个分解={R1，R2}，F是R上的函数依赖集。若F|=(R1 ∩R2)→(R1－R2) 或 F|=(R1 ∩R2) →（R2 －R1)，则具有无损连接性。 证明：用算法4.2.1 构造一个二行三列的矩阵如下： R1 ∩R2 R1 －R2 R2 －R1 R1 aaa…a aaa…a bbb…b R2 aaa…a bbb…b aaa…a 例： 关系模式R=ABC， R上的FDS集 F ={ A→C，B→C}。 R上的分解 1 ={AB，AC}， 2 ={AC，BC}

  20. 依赖保持性 定义设R的一个分解 ={R1，R2，…，Rp }, R上的FDs集F，若Ri (F)={ X→Y X→YF+且XYRi}，(1≤i≤p)，则称Ri(F)为F在Ri上的投影。 例： 关系模式R=ABC， R上的FDS集 F ={ A→B，B→C}。 R上的分解 1 ={R1，R2}，R1= AB， R2= AC F在R1，R2上的投影

  21. 依赖保持性 • 定义 设 ={R1，R2，…，Rp }是R的一个分解，F是R上的FDs集。F在Ri上投影的集合G=∪Ri (F)。若G≡F，则称分解 保持函数依赖集F。

  22. 算法4.2.2 检验分解ρ是否具有依赖保持性 PERSERVE1(F，ρ) begin G：=φ； for each X→Y in F do for each Ri in ρ do if X  Ri then do begin Z：= LINCLOSURE(X, F)； if Z∩Ri －Xφ then G：= G ∪{X→(Z∩Ri －X)} /*F在Ri上投影的集合 end； for each X→Y in F do if MEMBER(G, X→Y ) then T：=true else return(false)； return(T)； end.

  23. 例 设 ={R1，R2，R3}，其中R1=ABD，R2=BCE，R3=DE， F={A→BD，D→A，C→BE，E→D，C→A}。 判断：是否保持函数依赖集F。 解：（1）计算F在 上的投影G。 考察FD A→BD， AR1，A+=ABD， G={ A→BD }； 考察 D→A， DR1，D+=ABD， G={ A→BD，D→AB }， 又DR3，但ABD∩R3 －D=φ，G不变； 对 C→BE， CR2，C+=ABCED，G={A→BD，D→AB，C→BE} 分别考察 E→D 和 C→A， 结果： G={ A→BD，D→AB ，C→BE, E→D }。 （2）判断F和G是否等价。 可以看出，F中的FD除C→A外都已在G中了，而MEMBER(G, C→A)为真，因此，保持函数依赖集F。

  24. 4.2.2 通过分解实现规范化 算法4.2.4 生成3NF的分解算法 DECOMPOSE(R, K, F) 算法步骤：(1). 若R3NF，算法终止，ρ={R}。 (2).若ρ中有Ri3NF，即YRi，ZKY且Y→Z，Y→K， 则Z传递依赖于Ri中的键K，分解Ri为： Ri1 =R－Z和Ri2= YZ，用Ri1和Ri2代替ρ中的Ri 。 (3). 若ρ中所有Ri3NF，输出ρ，否则转(2) 继续进行分 解，直到使所有关系模式都成为3NF。

  25. 例 : 设关系模式R(A,B,C,D,E,G,H,I,J,K,M)为航空公司数据库。 其中，属性ABCDEGHIJKM分别为： 航班号、出发地、目的地、出发时间、到达时间、飞行时间、机型、头等舱座位数、普通舱座位数、座位总数、用餐时间。 F={A→BCDEGH，BCD→A，BCE→A，DG→M， H→IJK， EG→M，IJ→K，IK→J，JK→I}。 试分解R为3NF。

  26. F={A→BCDEGH，BCD→A，BCE→A，DG→M， H→IJK， EG→M，IJ→K，IK→J，JK→I}。 解：R的键为K={A，BCD，BCE}，R3NF。 因有DG→M，而DG不是键， 分解R为： R1 =ABCDEGHIJK， K1 ={A，BCD，BCE}； R2 =DGM， K2 ={DG}。 R23NF，R13NF，R1中有H→IJK， 分解R1为： R11 =ABCDEGH， K11 ={A，BCD，BCE}； R12=HIJK， K12 ={H}； R123NF， 因 IJ→K，分解R12 为： R121 =HIJ， K121 ={H}； R122 =IJK， K122 ={IJ，IK，JK} 结果： ={R11，R121，R122，R2}。

  27. 分解算法得到的关系模式是无损的。 分解算法存在的问题： （1）一个关系模式的主属性和非主属性是很难确定的。 （2）分解算法得到的关系模式不是惟一的。 （3）分解得到的关系模式不保持函数依赖。 例：职工名，电话，地址

  28. 4.2.2 通过合成实现规范化 算法4.2.5 SYNTHESIZE(U，F) 1. F：=F∪{ U→Z }。 2. 计算F化简的最小函数依赖集G。 3. 将G按等价类划分得到EG 。 4．对EG中每个EG(X)={Xj→Yj，1jp}，构造键为 {X1,X2,…,Xp}的关系模式Ri=X1X2…XpY， 其中，Y由EG(X)中FD的右部属性组成。 5. 考察每个Ri，删除由EG(X)中的FD合成后出现的外部属性。 6．将包含在某个Ri中的附加属性Z删除，输出R. 29

  29. 例：R=ABC，F={A→C，B→C} 由合成算法得3NF为{AC，BC}，R不是无损的。 在F中加入FD ABC→Z，最后得到的数据库模式R={AB，AC，BC}，R具有无损连接性。

  30. 例： F={B1B2→A，D1D2→B1B2，B1→C1，B2→C2， D1→A，D2→A，AB1C2→D2，AB2C1→D1}， F是化简的和最小的。按等价类划分后为： EF(B1B2)={ B1B2→A，D1D2→B1B2}， EF(B1)={ B1→C1}， EF(B2)={B2→C2}，EF (D1)={D1→A}，EF(D2)={D2→A}， EF(AB1C2)={AB1C2→D2}，EF (AB2C1) ={AB2C1→D1}。 因此：R1 =AB1B2D1D2； R2=B1C1 ； R3=B2C2 ； R4=D1A ； R5=D2A ； R6=AB1C2D2 ； R7=AB2C1D1 。 在R1中的属性A是外部的。

  31. 例: 设U=ABCDEGHIJKM， F={A→BCDEGH，BCD→A，BCE→A，A→M，H→IJK， DG→M，EG→M，IJ→K，IK→J，JK→I} 求: 属性集U的数据库模式。 解：(1). F：=F∪{ABCDEGHIJKM→Z} (2) 去掉冗余 A→M 及 ABCDEGHIJKM→Z 和 H→IJK中的外部属性。F化简为最小FDs. (3) EG (A)={ A→BCDEGH Z ，BCD→A，BCE→A }； EG(H)={H→IJ}； EG(DG)={ DG→M}； EG (EG)={EG→M}； EG (IJ)={ IJ→K，IK→J，JK→I } (4) R1 =ABCDEGHZ， K1={A, BCD, BCE}； R2 =HIJ， K2 ={H}； R3 =DGM， K3 ={DG}； R4 =EGM， K4 ={EG}； R5 =IJK， K5 ={IJ，IK，JK}。

  32. 由算法生成的数据库模式R满足以下特性： 1.R保持F且F完全由R表达。即: F≡{Ki→Ri｜Ri R，Ki 是Ri 的关键字}. 2. R中的每个关系模式Ri 对于F都是3NF。 3. R具有无损连接性，对任一满足F的关系r(R)，有 r = R1 (r) R2 (r) … Rk (r) 4. R是满足以上特性且含有最少关系模式的数据库 模式。

  33. 证明：(1) 设RiR，证明Ri3NF。 设Ri具有关键字K={X1，…，XK} 和非主属性集Y。如果Ri 3NF, 则有AY，AW(WRi) 且 Xj→W, W→A而 W→Xj (1jK)，因而有G|=Xj→A，则Xj→AEG(X)，从而Xj→AG，这与G是化简最小覆盖相矛盾。 另外，经步骤(5)的处理，消除了传递依赖。Ri 3NF. / 定理4 SYNTHSIZE算法是正确的，生成的数据库模式是3NF且满足无损连接性、依赖保持性和最小性。

  34. (2).R满足无损连接性。 因F中加入U→Z而ZU，所以，在计算过程中 U→Z不会作为冗余被删除。若U→Z经化简后为X→Z，则说明U－X为外部属性，即X+=U，X是U的键。 因X→ZG， 因此，X属于R中某个Ri ，根据LOSSLESS算法，在X所在Ri 对应行一定是全为a的行，即R满足无损连接性。 (3). R满足依赖保持性，这一点是显然的。 因G与F等价，而生成关系模式时没有去掉任何FD，即每个Ri上的FD是由Ri 上的关键字决定的，即 F≡{Ki→Ri | Ri R，Ki是Ri的关键字}。 (4). R含有模式数最少。 因最小集 |EG | . 证毕。

  35. 算法4.2.7 BCNF-DECOMPOSE(R, F) (1).若RBCNF，算法终止，ρ={R}。 (2).若ρ中有RiBCNF，即有X→Y且XY Ri而X→Ri， 则分解R为Ri1 =R－Y和Ri2= XY； 用Ri1和Ri2代替ρ中的Ri 。 (3). 若ρ中所有RiBCNF，输出ρ，否则转(2)继续进行分解，直到使所有关系模式都成为BCNF。 4.2.4 规范化关系模式为BCNF 例: 设R=ABCDE， F={A→B，B→A，D→C，AC→DE}， 由合成算法得到的数据库模式为： R1 =AB， K1 ={A, B}； R2=DC，K2={D}； R3={ACDE}，K3={AC, AD}。 R1，R2，R3都是3NF，但R3中有D→C， 因而C传递依赖于AD，所以R3不是BCNF。 若分解R3为R31= ADE R32= CD 则R={R1、 R2、 R31}为BCNF。

  36. 例：设 R=ABCDE，F={A→BC, BC→A, BCD→E, E→C}， 由合成算法生成的模式为R={R1，R2，R3}： R1 = ABC，K1 ={A，BC}； R2 =BCDE， K2 ={BCD}； R3 =EC，K3 ={E}。 R2 BCNF。将BC用A代替结果为ADE，R2BCNF，且替换后的R也能完全表征F。 *** 3NF可以满足无损连接性和依赖保持性，BCNF仅能满足无损连接性。

  37. 4.3 第四范式和投影-连接范式 例： COURSE TEACHER CLASS 知识工程 王一平 硕士 知识工程 王一平 博士 知识工程 刘晓彤 硕士 知识工程 刘晓彤 博士 COURSE →→ TEACHER COURSE →→ CLASS

  38. 4.3.1 第四范式 定义(4NF)设关系模式R及R上的FD和MVD集F。若对于每一个可施加于R且由F蕴涵的MVD X→→Y，它们是平凡的，或者X是R的一个超键，则R是关于F的第四范式，即R4NF。 例： R＝ABCDE， F＝{A→BC,C→→DE} R4NF, R1＝ABC 和 R2＝CDE 是 4NF。

  39. 定理5 如果模式R4NF，则RBCNF。 证明：假定R4NF但RBCNF。则必有R的子集K、Y和A，且AKY，和K→Y，Y → K，Y→A。Y→A隐含着Y→→A。 由于Y → K，Y不是R的超键，而A不包含在Y中且YAR。因此，Y→→A不是平凡的MVD。因此，R4NF，与假设矛盾。 证毕。

  40. 算法4.3.1 生成4NF的分解算法 (1). 若R4NF，算法终止，ρ={R}。 (2). 若ρ中有Ri4NF，即有X→→Y，XY Ri且X → Ri，分解R为： Ri1 =R－（Y－X）和Ri2= XY，直到所有模式为4NF。

  41. 函数依赖和多值依赖集 F 在Rj上的投影R j (F)： (1) 若F|=X→Y, X Rj，则X→Y∩Rj ∈Rj (F) (2) 若F|= X→→Y, X  Rj，X→→Y∩Rj∈R j (F) 例: 设R=ABCDEI, F={A→→BCD, B→AC, C→D}。 要求： 将R规范化到4NF。 解：A→→BCD是非平凡的且A不是R的键，分解R成关系模式： R1＝ABCD， R1 (F)={ B→AC，C→D }； R2＝AEI， R2 (F)={φ} 分解R1为4NF： R11＝ABC，R12＝CD；

  42. 4.3.2 投影-连接范式（PJNF） r ( A B C ) r1 ( A B ) r2 ( A C ) r3 ( B C ) a1 b1 c1a1 b1 a1 c1 b1 c1 a1 b2 c2a1 b2 a1 c2 b2 c2 a3 b3 c3a3 b3 a3 c3 b3 c3 a4 b3 c4a4 b3 a4 c4 b3 c4 a5 b5 c5 a5 b5 a5 c5 b5 c5 a6 b6 c5 a6 b6 a6 c5 b6 c5 JD*[AB，AC，BC] 插入→a3 b1 c2 a3 b1 a3 c2 b1 c2 . a1 b1 c2 . 删除 . . . . . 例：SP（供应商，零件，项目）

  43. 4.3.2 投影-连接范式（PJNF） r ( A B C ) r1 ( A B ) r2 ( A C ) r3 ( B C ) a1 b1 c1a1 b1 a1 c1 b1 c1 a1 b2 c2a1 b2 a1 c2 b2 c2 a3 b3 c3a3 b3 a3 c3 b3 c3 a4 b3 c4a4 b3 a4 c4 b3 c4 a5 b5 c5 a5 b5 a5 c5 b5 c5 a6 b6 c5 a6 b6 a6 c5 b6 c5 JD*[AB，AC，BC]

  44. 定义19 设关系模式R，F是R上的函数依赖和连接依赖集。若对于每个由F蕴涵的且施加于R的连接依赖JD*[R1,R2,…,Rp]，JD是平凡的或者每个Ri是R的一个超键，则RPJNF。 平凡的JD：R上的连接依赖被任意r(R)所满足。 JD*[R1, R2, …, Rp]中有某一Ri=R。

  45. 例: 设R＝ABCDEI，F＝{*[ABCD,CDE,BDI]， *[AB, BCD, AD]，A→BCDE，BC→AI}。 试将R规范到PJNF。 结果：R = (ABCD，CDE，BDI) ABCD: {*[AB, BCD, AD]，A→BCD，BC→A}

More Related