第8讲-逻辑模型设计(优化-范式与模式分解)

• is trivial (i.e., ) 仅有这样的函数依赖：属性都只依赖于键, • is a superkey for R 但主属性(候选键的属性)须除外! • Each attribute A in – is contained in a candidate key for R. (NOTE: each attribute may be in a different candidate key)
5
2 模式规范化
2.1 规范化的目标
什么是理想 的关系模式 分解？
Goals of Normalization
• Let R be a relation scheme with a set F of functional dependencies. • Decide whether a relation scheme R is in “good” form. • In the case that a relation scheme R is not in “good” form, decompose it into a set of relation scheme {R1, R2, ..., Rn} such that
• If a relation is in BCNF it is in 3NF (since in BCNF one of the first two conditions above must hold). • Third condition is a minimal relaxation of BCNF to ensure dependency preservation (will see why later).
• •
(U ) (R-(-))
• In our example above,
• = dept_name F1：dept_name building, budget • = building, budget and inst_dept is replaced by • R1： (U ) = ( dept_name, building, budget ) • R2：( R - ( - ) ) = ( ID, name, salary, dept_name )
2
instr_dept (ID, name, salary, dept_name, building, budget ) dept_name building, budget, key is ID
这样的分解带 来什么好处？
Decomposing a Schema into BCNF
• Suppose we have a schema R and a non-trivial dependency causes a violation of BCNF. We decompose R into:
10
3.2 (无关)冗余属性
什么是冗余属性 (函数依赖的)?
Extraneous Attributes
无关的
• Consider a set F of functional dependencies and the functional dependency in F. F 等价于(F – { }) {(
4
什么是第三范式， 与BCNF的区别？
Third Normal Form第三范式
• A relation schema R is in third normal form (3NF) if for all:
in F + at least one of the following holds:
A relation schema R is in BCNF with respect to a set F of functional dependencies if for all functional dependencies in F+ of the form where R and R, at least one of the following holds: • is trivial (i.e., ) • is a superkey for R
•
注意蕴含 方向不同！
– A) }
左方冗余：Attribute A is extraneous in if A
•
and F logically implies (F – { }) {( – A) }. 右方冗余： Attribute A is extraneous in if A and the set of functional dependencies F 等价于(F – { }) { ( – A)} (F – { }) { ( – A)} logically implies蕴含/包含 F.
课程名称: 数据库系统 --------------------
第4部分:逻辑模型设计
逻辑模型设计(优化-范式与模式分解)
教 师：朱征宇 单 位：重庆大学计算机学院
初始关系模式如何进行优化？ 有普遍可行的方法吗？
1
1 巴赫范式&第三范式
1.1 巴赫范式(BCNF)
什么样的关系模 式称为BCNF？
Boyce-Codd Normal Form
• where an instructor may have more than one phone and can have multiple children
ID 99999 99999 99999 99999 child_name
David David William Willian
phone
• Intuitively, a canonical cover of F is a “minimal” set of functional dependencies equivalent to F, having no redundant dependencies or redundant parts of dependencies
全键！
它们的键是什么？
inst_phone
ID
99999 99999
phone
512-555-1234 512-555-4321
全键！
This suggests the need for higher normal forms, such as Fourth Normal Form (4NF), which we shall see later.
二者“必居其一”！
Example schema not in BCNF:
仅存在这样的函数依赖：属性都只依赖于键！
instr_dept (ID, name, salary, dept_name, building, budget )
because dept_name building, budget holds on instr_dept, but dept_name is not a superkey
• For example: A C is redundant in: {A B, B C, A C} 冗余的函数依赖！ • Parts of a functional dependency may be redundant
• E.g.: on RHS: {A B, {A B, • E.g.: on LHS: {A B, {A B, B C, B C, B C, B C, A CD} can be simplified to A D} AC D} can be simplified to A D} (无关)冗余的属性！
512-555-1234 512-555-4321 512-555-1234 512-555-4321
（待续）
inst_info 键是什么？ 键由三个属性组成---全键！
7
这的BCNF存 在什么不足？
How good is BCNF? (Cont.) • There are no non-trivial functional dependencies and therefore the relation is in BCNF • Insertion anomalies – i.e., if we add a phone 981-992-3443 to 99999, we need to add two tuples (99999, David, 981-992-3443) (99999, William, 981-992-3443)
存在大量的数据冗余！
8
上述BCNF 模式的不足 如何克服？
How good is BCNF? (Cont.)
ID 99999 99999 child_name
David William
• Therefore, it is better to decompose inst_info into:
inst_child
9
3 正则覆盖
3.1 问题引入
函数依赖集常会出 现什么现象？
wk.baidu.com
Canonical Cover
正则覆盖(等同于王能斌教材-最小函数依赖集)
• Sets of functional dependencies may have redundant dependencies that can be inferred from the others
F2：仅有一非平凡函数依赖：ID所有其它属性！
两模式均为BCNF！
3
1.2 第三范式(3NF)
BCNF分解有 无不足？
BCNF and Dependency Preservation
如何避免这 种现象？
• Constraints, including functional dependencies, are costly to check in practice unless they pertain附属于 to only one relation • If it is sufficient to test only those dependencies on each individual relation of a decomposition in order to ensure that all functional dependencies hold, then that decomposition is dependency preserving. • Because it is not always possible to achieve both BCNF and dependency preservation, we consider a weaker normal form, known as third normal form.
• Note: implication蕴含 in the opposite direction is trivial in each of the cases above, since a “stronger” functional dependency always implies a weaker one F 等价于F ’= {A C }！ • Example1: Given F = {A C, AB C }
6
2.2 BCNF模式规范情况分析
BCNF模式已经 很好了吗？
How good is BCNF?
• There are database schemas in BCNF that do not seem to be sufficiently normalized • Consider a relation (由于关系模式不允许多值属性，故有这样的模式) inst_info (ID, child_name, phone) ID-员工号
• 1) each relation scheme is in good form • 2) the decomposition is a lossless-join decomposition • 3) preferably, the decomposition should be dependency preserving.