第 7 章: 关系数据库设计

1. 第7章: 关系数据库设计

2. 第7章: 关系数据库设计 • 第一范式 • 关系数据库设计中易犯的错误 • 函数依赖 • 分解 • Boyce - Codd 范式 • 第三范式 • 多值依赖与第四范式 • 数据库设计全过程

3. 第一范式 • 如果域中元素被认为是不可分的, 则域称为是原子的 • 非原子域的例子: • 名字集合, 复合属性 • 象CS101之类的标识号可以分成若干部分 • 如果关系模式R的所有属性的域都是原子的, 则R称为属于第一范式 • 非原子值存储复杂并易导致数据冗余 • E.g. 每个客户的账户集合, 以及每个账户的拥有者集合 • 我们假定所有关系都属于第一范式(参见第9章对象关系数据库)

4. 第一范式 (续) • 原子性实际上是关于如何使用域中元素的一种性质. • E.g. 字符串通常认为是不可分的 • 假设学生具有形如 CS0012 或EE1127的字符串学号 • 若前两个字符可作为系分解出来, 则学号域不是原子的. • 这样做很不好: 导致信息编码于应用程序中而不是数据库中.

5. 关系数据库设计中易犯的错误 • 关系数据库设计要求我们找到一个 “好的” 关系模式集合. 一个坏的涉及可能导致 • 信息的重复 • 某些信息不能表示 • 设计目标: • 避免冗余数据 • 确保属性间联系得以表示 • 方便检查更新是否破坏了数据库完整性约束

6. 例如 • 考虑关系模式:Lending-schema = (branch-name, branch-city, assets, customer-name, loan-number, amount) • 冗余: • branch-name, branch-city, assets数据对某分行的每笔贷款都要重复一次 • 浪费空间 • 使修改操作复杂化, 可能导致assets值的不一致 • 空值 • 不能存储没有贷款的分行信息 • 可以使用空值, 但空值难于处理.

7. 分解 • 将关系模式Lending-schema 分解成: Branch-schema = (branch-name, branch-city,assets) Loan-info-schema = (customer-name, loan-number, branch-name, amount) • 原模式(R)的所有属性都必须出现在分解后的(R1, R2)中: R = R1  R2 • 无损连接分解对模式R上的所有可能的关系r r = R1 (r) R2 (r)

8. 有损连接分解例 • 分解R = (A, B) R1 = (A) R2 = (B) A B A B    1 2 1   1 2 B(r) A(r) r A B A (r) B (r)     1 2 1 2

9. 目标 — 设计一个理论 • 以判断关系模式R是否 “好的” 形式 • 当R不是 “好的” 形式时, 将它分解成模式集合 {R1, R2, ..., Rn} 使得 • 每个关系模式都是“好的” 形式 • 分解是无损连接分解 • 我们的理论基于: • 函数依赖 • 多值依赖

10. 函数依赖 • 对合法关系的约束 • 要求一个属性集的值能唯一确定另一个属性集的值 • 函数依赖是键概念的推广.

11. 函数依赖 (续) • 设R是一关系模式, 且有属性集   R ,  R • 函数依赖  在R上成立当且仅当对任意合法关系r(R), 若r 的任意两条元组t1和t2在属性集上的值相同, 则他们在属性集 上的值也相同. 即, t1[] = t2 []  t1[] = t2 [] • 例如: 考虑r(A,B)及其下列实例r. • 对此实例, AB不成立, 但BA成立. • 4 • 1 5 • 3 7

12. 函数依赖 (续) • K是关系模式R的超键当且仅当K R • K是R的候选键当且仅当 • K R, 并且 • 没有  K 使 R • 函数依赖使我们可以表达用超键无法表达的约束. 考虑模式: Loan-info-schema = (customer-name, loan-number, branch-name, amount). 我们期望下列函数依赖成立: loan-numberamount loan-number  branch-name 而不期望下列函数依赖成立: loan-number customer-name

13. 函数依赖的使用 • 我们用函数依赖来: • 检查关系在给定函数依赖之下是否合法. • 若关系r在函数依赖集F 下是合法的, 则称r满足F. • 对合法关系集合指定约束 • 如果R 上的所有合法关系都满足F, 则称F在R上成立. • 注意: 关系模式的特定实例可能满足一函数依赖, 但该函数依赖不是在所有合法实例上成立. 例如, Loan-schema的特定实例可能碰巧满足loan-number customer-name.

14. 函数依赖 (续) • 被所有关系实例都满足的函数依赖称为平凡的 • 例如 • customer-name, loan-number customer-name • customer-name customer-name • 一般地,若   则  是平凡的

15. 函数依赖集的闭包 • 给定函数依赖集F, 存在其他函数依赖被F 逻辑蕴含. • 例如:如果 A  B 且 B  C, 则可推出 A  C • 被F逻辑蕴含的全体函数依赖的集合称为F 的闭包. • 用F+ 表示F的闭包. • 可利用Armstrong公理找出F+ : • 若  , 则   (自反) • 若  , 则    (增广) • 若   且  , 则  (传递) • 这些规则是 • 正确的 (只产生确实成立的函数依赖) • 完备的 (产生所有成立的函数依赖).

16. 例如 • R = (A, B, C, G, H, I)F = { A BA CCG HCG IB H} • F+ 的某些成员 • A H • 从A B 和B H 根据传递规则 • AG I • 用G增广A C 得AG CG, 再由CG I 根据传递规则 • CG HI • 由CG H and CG I : 可根据函数依赖的定义导出“并规则”, 或 • 增广CG I 得到CG  CGI, 增广CG H 得到CGI HI, 再利用传递规则

17. 下列过程计算函数依赖集F的闭包: F+ = Frepeatfor eachF+中的函数依赖f对f 应用自反和增广规则将结果函数依赖加入F+for each F+中的一对函数依赖f1 和f2if若f1和f2可利用传递规则合并then将结果函数依赖加入F+until F+不再变化 注意: 后面会介绍完成此任务的另一过程 计算F+

18. 函数依赖的闭包 (续) • 可用下列规则进一步简化F+的手工计算. • 若  与 成立, 则  成立(合并) • 若  成立, 则  与成立(分解) • 若  与  成立, 则  h成立(伪传递) 以上规则可以从Armstrong公理推出.

19. 属性集的闭包 • 给定属性集合a,定义a在F下的闭包 (记做a+) 为被a 在F 下函数决定的属性的集合: ais in F+   a+ • 计算a+ 的算法 result := a;while (result 有变化) do for each in F do begin if  result then result := result end

20. 属性集闭包例 • R = (A, B, C, G, H, I) • F = {A BA CCG HCG IB H} • (AG)+ 1. result = AG 2. result = ABCG (A C and A  B) 3. result = ABCGH (CG H and CG  AGBC) 4. result = ABCGHI (CG I and CG  AGBCH) • AG是候选键么? • AG 是超键么? • AG R? • 存在AG的子集是超键么? • A+R? • G+R?

21. 属性闭包算法有多种用途: 测试超键: 为检测 是否超键, 可计算+并检查+ 是否包含R的所有属性 测试函数依赖 为检测函数依赖   是否成立(即属于F+), 只需检查是否  +. 即, 可计算+, 并检查它是否包含. 这个检查简单而高效, 非常有用 计算F的闭包 对每个   R, 计算 +, 再对每个S  +, 输出函数依赖  S. 属性闭包的用法

22. 正则覆盖 • 函数依赖集合可能有冗余依赖(即他们能从其他函数依赖推出) • 例如: A  C 在 {A  B, B  C, A  C} 中是冗余的 • 函数依赖的某部分可能是冗余的 • 依赖右部: {A  B, B  C, A  CD} 可化简为 {A  B, B  C, A  D} • 依赖左部: {A  B, B  C, AC  D} 可化简为 {A  B, B  C, A  D} • 直观地说, F的正则覆盖是指与F等价的“极小的”函数依赖集合, 没有冗余依赖, 依赖也没有冗余部分

23. 无关紧要的属性 • 考虑函数依赖集合F及其中的函数依赖 . • 如果A  并且F逻辑蕴含 (F – {})  {( – A) }, 则称属性A 在 中是无关紧要的. • 如果A   并且(F – {})  {(– A)} 逻辑蕴含F, 则称属性A 在中是无关紧要的. • 注意:上面两种情形中反方向的蕴含平凡成立, 因为 “较强的”函数依赖总是蕴含较弱的函数依赖 • 例如: 给定F = {AC, ABC } • B在AB C中是无关紧要的, 因为AC逻辑蕴含ABC. • 例如: 给定 F = {AC, ABCD} • C在ABCD中是无关紧要的, 因为即使删除C 也能推出AC

24. 检测属性是否无关紧要 • 考虑函数依赖集合F以及其中的函数依赖 . • 为检测属性A  在 中是否无关紧要 • 计算在F 下的 ( – {A})+ • 检查(A – {})+是否包含; 如果是, 则A是无关紧要的 • 为检测属性A 在中是否无关紧要 • 计算在F’ = (F – {})  {(– A)} 下的+ • 检查 + 是否包含A; 如果是, 则A 是无关紧要的

25. 正则覆盖 • 函数依赖集合F 的一个正则覆盖是满足下列条件的函数依赖集合Fc • F逻辑蕴含Fc 中的所有函数依赖 • Fc逻辑蕴含F 中的所有函数依赖 • Fc中的函数依赖不含无关紧要的属性 • Fc中的函数依赖的左部都是唯一的 • 计算F 的正则覆盖:repeat对F 中的依赖利用合并规则11 和11替换成112找出含有无关紧要属性的函数依赖 (在 或中) 如果找到无关紧要的属性, 从中删去until F不再变化 • 注: 删除某些无关紧要的属性之后,可能导致合并规则可应用, 所以必须重新应用

26. 计算正则覆盖例 • R = (A, B, C)F = {A BC B C A BABC} • 合并A BC 及A B 成A BC • 集合变成{A BC, B C, ABC} • A在ABC 中是无关紧要的, 因为BC 逻辑蕴含AB C. • 集合变成{A BC, B C} • C在ABC 中是无关紧要的, 因为A BC可由A B 和B  C逻辑推出. • 正则覆盖是: A B B C

27. 范式化的目标 • 确定一个关系是否属于 “好的” 形式. • 如果关系R不属于“好的”形式, 则将它分解为关系集合 {R1, R2, ..., Rn} 使得 • 每个关系都属于好的形式 • 分解是无损连接分解 • 范式化理论基于: • 函数依赖 • 多值依赖

28. 分解 • 将关系模式Lending-schema 分解成: Branch-schema = (branch-name, branch-city,assets) Loan-info-schema = (customer-name, loan-number, branch-name, amount) • 原模式 (R) 的所有属性都必须出现在分解结果 (R1, R2)中: R = R1  R2 • 无损连接分解对模式R上的所有可能关系r r = R1 (r) R2 (r) • R 分解成R1和R2是无损连接的当且仅当下列依赖中的至少一个属于F+: • R1 R2R1 • R1 R2R2

29. 有损连接分解例 • 有损连接分解导致信息丢失. • 例如: R = (A, B)分解成R1 = (A) R2 = (B) A B A B    1 2 1   1 2 B(r) A(r) r A B A (r) B (r)     1 2 1 2

30. 利用函数依赖范式化 • 当我们将具有函数依赖集合F 的关系模式R分解成R1, R2, ..., Rn时, 我们希望 • 无损连接分解: 否则分解导致信息丢失. • 无冗余: 关系Ri最好属于Boyce-Codd 范式或第三范式. • 保持依赖:令Fi是F+中只包含Ri 中属性的依赖集合. • 分解最好保持依赖, 即 (F1 F2  …  Fn)+ = F+ • 否则检查更新是否破坏了函数依赖可能需要计算连接,代价较大.

31. 例 • R = (A, B, C)F = {A B, B C) • R1 = (A, B), R2 = (B, C) • 无损连接分解: R1  R2 = {B} and B BC • 依赖保持 • R1 = (A, B), R2 = (A, C) • 无损连接分解: R1  R2 = {A} and A  AB • 不保持依赖(不计算R1 R2 就不能检查B C)

32. 为检查依赖  在R 到 R1, R2, …, Rn的分解中是否得到保持, 可进行下面的简单测试 (设已计算了F下的属性闭包) result = while (result有变化) dofor each分解后的Rit = (result  Ri)+  Riresult = result  t 若result包含中的所有属性, 则   得到保持. 需要对F中所有依赖进行依赖保持的测试 此算法需要多项式时间, 而计算F+和(F1 F2  …  Fn)+需要指数时间 检查依赖保持

33. Boyce-Codd 范式 •  是平凡的 (i.e.,  ) • 是R的超键 具有函数依赖集合F的关系模式R属于BCNF当且仅当对F+中所有函数依赖, 下列两条件至少一个成立:

34. 例 • R = (A, B, C)F = {AB B  C}键= {A} • R不属于BCNF • 分解成R1 = (A, B), R2 = (B, C) • R1与R2属于BCNF • 无损连接分解 • 保持依赖

35. 检查是否BCNF • 为检查非平凡依赖是否违反BCNF的要求 1. 计算+, 2. 检验+是否包含R 的所有属性, 即, 是否R 的超键. • 简化的测试: 为检查具有函数依赖集合F的关系模式R是否属于BCNF, 只需检查F 中的依赖是否违反 BCNF即可, 而不需检查F+中的所有依赖. • 可以证明如果F 中没有违反BCNF的依赖, 则F+中也没有违反BCNF的依赖. • 但是, 当检查R的分解后的关系时仅用F是错误的 • 例如:考虑R (A, B, C, D), 具有F = { A B, B C} • 分解R到R1(A,B) 与R2(A,C,D) • F中的依赖都不是只包含(A,C,D)中属性的, 因此我们可能错误地认为R2满足 BCNF. • 事实上, F+中的依赖AC显示R2不属于BCNF.

36. BCNF 分解算法 result := {R};done := false;compute F+;while (not done) do if (result 中存在模式Ri不属于BCNF)then begin令 是Ri 上的一个非平凡函数依赖使得 Ri不属于F+, 且  = ;result := (result – Ri)  (Ri – )  (,  );end else done := true; 注意: 每个Ri都属于BCNF, 且分解是无损连接的.

37. BCNF分解例 • R = (branch-name, branch-city, assets, customer-name, loan-number, amount)F = {branch-name assets branch-city loan-number amount branch-name}键 = {loan-number, customer-name} • 分解 • R1 = (branch-name, branch-city, assets) • R2 = (branch-name, customer-name, loan-number, amount) • R3 = (branch-name, loan-number, amount) • R4 = (customer-name, loan-number) • 最终分解R1, R3, R4

38. 检查分解是否属于BCNF • 为检查关系R 的分解结果Ri 是否属于BCNF, • 针对F在Ri上的限制 (即, F+中所有只包含Ri中属性的FD) 检查Ri 是否属于BCNF • 或者: 使用R 的原始依赖集F, 但作如下测试: • 对每个属性集  Ri, 检查+是否要么不包含Ri-  的属性, 要么包含Ri 的所有属性. • 若该条件被某个 破坏, 则可证明依赖 (+ - )  Ri在Ri 上成立, 且违反BCNF. • 利用上面的依赖分解Ri

39. BCNF与依赖保持 • R = (J, K, L)F = {JK L L K}两个候选键 = JK and JL • R 不属于BCNF • R的任何分解都不会保持 JK L BCNF分解不总是保持依赖的

40. 第三范式: 动机 • 存在这样的情况 • BCNF 不保持依赖, 并且 • 有效地检查更新违反FD是重要的 • 解决方法: 定义较弱的范式, 称为第三范式. • 允许出现一些冗余 (从而带来问题; 见后例) • 但FD可以在单个关系中得到检查, 不必计算连接. • 总是存在到3NF 的保持依赖的无损连接分解.

41. 第三范式 • 关系模式R属于第三范式 (3NF) 当且仅当对所有F+中依赖: 下列条件中至少一个成立: • 是平凡的 (i.e.,  ) • 是R 的超键 •  –  中的每个属性A包含在R 的某个候选键中. (注: 各属性可能包含在不同候选键中) • 若一个关系属于BCNF则必属于3NF (因为BCNF要求的正是上面的前两个条件). • 第三个条件是对BCNF要求的最小的放宽, 以便保持依赖.

42. 3NF (续) • 例 • R = (J, K, L)F = {JK L, L K} • 两个候选键JK and JL • R属于3NF JK L JK 是超键L K K 包含在一候选键中 • BCNF 分解导致 (JL) 和 (LK) • 检查JK L 需要连接 • 上面模式存在一些冗余 • 等价于书上的例子: Banker-schema = (branch-name, customer-name, banker-name) banker-name  branch name branch name customer-name  banker-name

43. 检查是否属于3NF • 优化: 只需检查F 中的FD, 而不必检查F+中的所有FD. • 对每个依赖  , 利用属性闭包来检查 是否超键. • 如果 不是超键, 必须检查中的每个属性是否包含在R的某个候选键中 • 这个检查较昂贵, 因为它涉及求候选键 • 检查3NF是 NP-hard 的 • 有趣的是, 分解到第三范式可以在多项式时间内完成

44. 3NF 分解算法 令Fc是F 的正则覆盖;i := 0;for each Fc中的函数依赖do if 没有模式Rj (1  j  i) 包含 then begini := i + 1;Ri := endif 没有模式Rj (1  j  i) 包含R 的候选键then begini := i + 1;Ri := R 的任意候选键;end return (R1, R2, ..., Ri)

45. 3NF 分解算法(续) • 上述算法确保: • 每个关系模式Ri属于3NF • 分解是保持依赖的和无损连接的 • 正确性证明在本文件末尾 (click here)

46. 例 • 关系模式: Banker-info-schema = (branch-name, customer-name, banker-name, office-number) • 本关系模式上的函数依赖包括:banker-name branch-name office-number customer-name branch-name banker-name • 键: {customer-name, branch-name}

47. 对Banker-info-schema应用3NF • 算法中的for循环使下列模式包含在分解中: Banker-office-schema = (banker-name, branch-name, office-number)Banker-schema = (customer-name, branch-name, banker-name) • 由于Banker-schema包含Banker-info-schema的候选键, 分解过程到此为止.

48. BCNF与 3NF的比较 • 总是可以将一个关系分解到3NF并且满足 • 分解是无损的 • 保持依赖 • 总是可以将一个关系分解到BCNF并且满足 • 分解是无损的 • 但可能不保持依赖.

49. BCNF与 3NF的比较 (续) • 因3NF的冗余引起的问题 • R = (J, K, L)F = {JK L, L K} J L K j1 j2 j3 null l1 l1 l1 l2 k1 k1 k1 k2 属于3NF但不属于 BCNF的模式有下面的问题 • 信息重复 (e.g., 联系l1, k1) • 需要使用空值 (e.g., 表示联系l2, k2 , 这里没有对应的J 值).

50. 设计目标 • 关系数据库设计的目标是: • BCNF. • 无损连接. • 依赖保持. • 如果不能达到这些, 也可接受 • 缺少依赖保持 • 因3NF引起的冗余 • 除了超键之外, SQL 并没有提供直接声明函数依赖的方法. 可以通过断言声明FD, 但检测代价大. • 因此即使我们有一个保持依赖的分解, 用SQL我们也不能有效地检测左部不是键的函数依赖.