1 Functional Dependencies 函数依赖 Meaning of FD’s Keys and Superkeys Functional Dependencies.
-
Upload
grant-williamson -
Category
Documents
-
view
258 -
download
0
description
Transcript of 1 Functional Dependencies 函数依赖 Meaning of FD’s Keys and Superkeys Functional Dependencies.
![Page 1: 1 Functional Dependencies 函数依赖 Meaning of FD’s Keys and Superkeys Functional Dependencies.](https://reader033.fdocument.pub/reader033/viewer/2022061604/5a4d1b1a7f8b9ab059992f8b/html5/thumbnails/1.jpg)
1
Functional Dependencies函数依赖Meaning of FD’s
Keys and SuperkeysFunctional Dependencies
![Page 2: 1 Functional Dependencies 函数依赖 Meaning of FD’s Keys and Superkeys Functional Dependencies.](https://reader033.fdocument.pub/reader033/viewer/2022061604/5a4d1b1a7f8b9ab059992f8b/html5/thumbnails/2.jpg)
2
Functional Dependencies X → A is an assertion about a relation R that
whenever two tuples of R agree on all the attributes of X, then they must also agree on the attribute A. Say “X → A holds in R.” Convention: …, X, Y, Z represent sets of attributes; A,
B, C,… represent single attributes. Convention: no set formers in sets of attributes, just
ABC, rather than {A,B,C }.
![Page 3: 1 Functional Dependencies 函数依赖 Meaning of FD’s Keys and Superkeys Functional Dependencies.](https://reader033.fdocument.pub/reader033/viewer/2022061604/5a4d1b1a7f8b9ab059992f8b/html5/thumbnails/3.jpg)
3
Example
Movies(title, year,length,filmType,studioname)
Reasonable FD’s to assert:1. tile year → length2. tile year →filmType3. tile year → studioName
title year length
film type
studioName
starName
Star WarsStar WarsStar WarsMighty DucksWayne’s WorldWayne’s World
197719771977199119921992
1241241241049595
colorcolorcolorcolorcolorcolor
FoxFoxFoxDisneyParamountParamount
Carrie FisherMark HamillHarrison FordEmilio EstevezDana CarveyMike Meyers
![Page 4: 1 Functional Dependencies 函数依赖 Meaning of FD’s Keys and Superkeys Functional Dependencies.](https://reader033.fdocument.pub/reader033/viewer/2022061604/5a4d1b1a7f8b9ab059992f8b/html5/thumbnails/4.jpg)
4
FD’s With Multiple Attributes
No need for FD’s with > 1 attribute on right. But sometimes convenient to combine
FD’s as a shorthand. Example: tile year → length , tile
year →filmType and tile year → studioName become tile year → length filmType studioName
![Page 5: 1 Functional Dependencies 函数依赖 Meaning of FD’s Keys and Superkeys Functional Dependencies.](https://reader033.fdocument.pub/reader033/viewer/2022061604/5a4d1b1a7f8b9ab059992f8b/html5/thumbnails/5.jpg)
5
Keys of Relations K is a superkey for relation R if K
functionally determines all of R. K is a key for R if K is a superkey,
but no proper subset of K is a superkey.
![Page 6: 1 Functional Dependencies 函数依赖 Meaning of FD’s Keys and Superkeys Functional Dependencies.](https://reader033.fdocument.pub/reader033/viewer/2022061604/5a4d1b1a7f8b9ab059992f8b/html5/thumbnails/6.jpg)
6
ExampleMovies(title,
year,length,filmType,studioname) {tile, year,starName} is a superkey
because together these attributes determine all the other attributes.
{tile, year,starName,length,filmType} is a superkey too.
![Page 7: 1 Functional Dependencies 函数依赖 Meaning of FD’s Keys and Superkeys Functional Dependencies.](https://reader033.fdocument.pub/reader033/viewer/2022061604/5a4d1b1a7f8b9ab059992f8b/html5/thumbnails/7.jpg)
7
E/R and Relational Keys Keys in E/R concern entities. Keys in relations concern tuples. Usually, one tuple corresponds to one
entity, so the ideas are the same. But --- in poor relational designs, one
entity can become several tuples, so E/R keys and Relational keys are different.
![Page 8: 1 Functional Dependencies 函数依赖 Meaning of FD’s Keys and Superkeys Functional Dependencies.](https://reader033.fdocument.pub/reader033/viewer/2022061604/5a4d1b1a7f8b9ab059992f8b/html5/thumbnails/8.jpg)
8
Example
Drinkers addrname
Beers
manfname
Bars
name
license
addr
Note:license =beer, full,none
Sells Bars sell somebeers.
LikesDrinkers likesome beers.Frequents
Drinkers frequentsome bars.
![Page 9: 1 Functional Dependencies 函数依赖 Meaning of FD’s Keys and Superkeys Functional Dependencies.](https://reader033.fdocument.pub/reader033/viewer/2022061604/5a4d1b1a7f8b9ab059992f8b/html5/thumbnails/9.jpg)
9
Example Data (Cont.)
name addr beersLiked manf favBeerJaneway Voyager Bud A.B. WickedAleJaneway Voyager WickedAle Pete’s WickedAleSpock Enterprise Bud A.B. Bud
Relational key = {name beersLiked}But in E/R, name is a key for Drinkers, and beersLiked is a key
for Beers.Note: 2 tuples for Janeway entity and 2 tuples for Bud entity.
![Page 10: 1 Functional Dependencies 函数依赖 Meaning of FD’s Keys and Superkeys Functional Dependencies.](https://reader033.fdocument.pub/reader033/viewer/2022061604/5a4d1b1a7f8b9ab059992f8b/html5/thumbnails/10.jpg)
10
Where Do Keys Come From?
1. Just assert a key K. The only FD’s are K → A for all attributes A.
2. Assert FD’s and deduce the keys by systematic exploration.
E/R model gives us FD’s from entity-set keys and from many-one relationships.
![Page 11: 1 Functional Dependencies 函数依赖 Meaning of FD’s Keys and Superkeys Functional Dependencies.](https://reader033.fdocument.pub/reader033/viewer/2022061604/5a4d1b1a7f8b9ab059992f8b/html5/thumbnails/11.jpg)
11
More FD’s From “Physics” Example: “no two courses can meet in
the same room at the same time” tells us: hour room → course.
![Page 12: 1 Functional Dependencies 函数依赖 Meaning of FD’s Keys and Superkeys Functional Dependencies.](https://reader033.fdocument.pub/reader033/viewer/2022061604/5a4d1b1a7f8b9ab059992f8b/html5/thumbnails/12.jpg)
12
Rules About Functional Dependencies
The Splitting/Combining Rule We can replace a FD A1A2…An→ B1B2…Bm by a set
of FD’s A1A2…An→ Bi,for i=1,2,…,m. This transformation we call splitting rules.
We can replace a set of FD’s A1A2…An→ Bi for i=1,2,…,m by a set A1A2…An→ B1B2…Bm,. This transformation we call combining rules.
![Page 13: 1 Functional Dependencies 函数依赖 Meaning of FD’s Keys and Superkeys Functional Dependencies.](https://reader033.fdocument.pub/reader033/viewer/2022061604/5a4d1b1a7f8b9ab059992f8b/html5/thumbnails/13.jpg)
13
Examplestitle year → length title year → studioName
title year → length studioName
![Page 14: 1 Functional Dependencies 函数依赖 Meaning of FD’s Keys and Superkeys Functional Dependencies.](https://reader033.fdocument.pub/reader033/viewer/2022061604/5a4d1b1a7f8b9ab059992f8b/html5/thumbnails/14.jpg)
14
MoreConsider FD:
title year → length if we try to split the left side into
title → length year → length
Then we get two false FD’s.
![Page 15: 1 Functional Dependencies 函数依赖 Meaning of FD’s Keys and Superkeys Functional Dependencies.](https://reader033.fdocument.pub/reader033/viewer/2022061604/5a4d1b1a7f8b9ab059992f8b/html5/thumbnails/15.jpg)
15
A functional dependency A1A2…An→B is said to be trivial( 平凡 ) if B is one of the A’s, otherwise is said to be nontrivial( 非平凡 ). Example: Suppose Functional Dependencies
title, year → title is a trivial dependency.
Trivial Dependencies
![Page 16: 1 Functional Dependencies 函数依赖 Meaning of FD’s Keys and Superkeys Functional Dependencies.](https://reader033.fdocument.pub/reader033/viewer/2022061604/5a4d1b1a7f8b9ab059992f8b/html5/thumbnails/16.jpg)
16
The Closure of Attributes
Suppose {A1,A2,…,An} is a set of attributes and S is a set of FD’s. The closure of {A1,A2,…,An} under the FD’s in S is the set of attributes B such that every relation that satisfies all the FD’s in the set S also satisfies A1A2…An→ B.That is, A1A2…An→ B follows from the FD’s of S.
![Page 17: 1 Functional Dependencies 函数依赖 Meaning of FD’s Keys and Superkeys Functional Dependencies.](https://reader033.fdocument.pub/reader033/viewer/2022061604/5a4d1b1a7f8b9ab059992f8b/html5/thumbnails/17.jpg)
17
Computing the closure
An easier way to test is to compute the closure of Y, denoted Y +.
Basis: Y + = Y. Induction: Look for an FD’s left side X
that is a subset of the current Y +. If the FD is X -> A, add A to Y +.
![Page 18: 1 Functional Dependencies 函数依赖 Meaning of FD’s Keys and Superkeys Functional Dependencies.](https://reader033.fdocument.pub/reader033/viewer/2022061604/5a4d1b1a7f8b9ab059992f8b/html5/thumbnails/18.jpg)
18
Y+new Y+
X A
![Page 19: 1 Functional Dependencies 函数依赖 Meaning of FD’s Keys and Superkeys Functional Dependencies.](https://reader033.fdocument.pub/reader033/viewer/2022061604/5a4d1b1a7f8b9ab059992f8b/html5/thumbnails/19.jpg)
19
Example R = (A, B, C, G, H, I) F = {A B
A C CG HCG IB H}
(AG)+
1. result = AG2. result = ABCG (A C and A B)3. result = ABCGH (CG H and CG AGBC)4. result = ABCGHI (CG I and CG AGBCH)
Is AG a candidate key? 1. Is AG a super key?
1. Does AG R? == Is (AG)+ R2. Is any subset of AG a superkey?
1. Does A R? == Is (A)+ R2. Does G R? == Is (G)+ R
![Page 20: 1 Functional Dependencies 函数依赖 Meaning of FD’s Keys and Superkeys Functional Dependencies.](https://reader033.fdocument.pub/reader033/viewer/2022061604/5a4d1b1a7f8b9ab059992f8b/html5/thumbnails/20.jpg)
20
例 设关系模式 R ( U,F ) , 其中 ,U={A,B,C,D,E,I},F={A→D,AB→C,BI→C
,ED→I,C→E}, 求( AC ) F+ 。
解: (1) 令 X={AC}, 则 X(0)=AC 。 (2) 在 F 中找出左边是 AC 子集的函数依赖: A→D,C→E 。 (3) X(1)=X(0) D E=ACDE∪ ∪ 。
Another Examples
![Page 21: 1 Functional Dependencies 函数依赖 Meaning of FD’s Keys and Superkeys Functional Dependencies.](https://reader033.fdocument.pub/reader033/viewer/2022061604/5a4d1b1a7f8b9ab059992f8b/html5/thumbnails/21.jpg)
21
(4) 很明显 X(1)≠X(0), 所以 X(i)=X(1), 并转向算法中的步骤 (2) 。
(5) 在 F 中找出左边是 ACDE 子集的函数依赖: ED→I 。(6) X(2)=X(1)∪I=ACDEI 。(7) 虽然 X(2)≠X(1), 但是 F 中未用过的函数依赖的左边
属性已没有 X(2) 的子集 , 所以 , 可停止计算 , 输出( AC) F
+ = X(2)=ACDEI 。
![Page 22: 1 Functional Dependencies 函数依赖 Meaning of FD’s Keys and Superkeys Functional Dependencies.](https://reader033.fdocument.pub/reader033/viewer/2022061604/5a4d1b1a7f8b9ab059992f8b/html5/thumbnails/22.jpg)
22
Transitive( 传递 ) Functional Dependencies Suppose we have a relation R with three attributes A,
B, and C, the FD’s A→B and B→C both hold for R. Then it is easy to see that the FD A→C also holds for R, So C is said to depend on A transitively, via B.
The transitive rule
![Page 23: 1 Functional Dependencies 函数依赖 Meaning of FD’s Keys and Superkeys Functional Dependencies.](https://reader033.fdocument.pub/reader033/viewer/2022061604/5a4d1b1a7f8b9ab059992f8b/html5/thumbnails/23.jpg)
23
Examples
Two of the FD’s title year → studioName studioName →studioAddr
Combine the two FD’s above to get title year → studioAddr
title year length
film type
studioName
studioAddr
Star WarsMighty DucksWayne’s World
197719911992
12410495
colorcolorcolor
FoxDisneyParamount
HolleywoodBuena VistaHolleywood
![Page 24: 1 Functional Dependencies 函数依赖 Meaning of FD’s Keys and Superkeys Functional Dependencies.](https://reader033.fdocument.pub/reader033/viewer/2022061604/5a4d1b1a7f8b9ab059992f8b/html5/thumbnails/24.jpg)
24
Closing Sets of Functional Dependencies
Any set of given FD’s from which we can infer all the FD’s for a relation will be called a basis for that relation. If no proper subset of the FD’s in the basis can also derive the complete set of FD’s, then we say the basis is minimal, that is the closing set of the functional dependencies of the relation
Consider a relation R(A,B,C), One of its FD’s is{A→B,B →A, B→C,C →B}
Another is{A→B,B→C,C →A}