dependencies in a table Haotian Wang Table(a,b,c) Group 1: Left - PowerPoint PPT Presentation

#Trivial functional dependencies in a table Haotian Wang

Table(a,b,c) • Group 1: Left part contains one attribute: subgroups:1.1 a → a, 1.2 b → b, 1.3 c → c. G1= 3 × 1=3 • Group 2: Left part contains two attributes: 2.1 ab → a, ab → b, ab → ab. 2.2 ac → a, ac → c, ac → ac. 2.3 bc → b, bc → c, bc → bc. G2=3 × 3=9 • Group 3: Left part contains three attributes: 3.1 abc → a, abc → b, abc → c. abc → ab, abc → ac, abc → bc. abc → abc. G3=1 × 7=7 total=G1+G2+G3=19

Table(a1,a2,a3…,an) • How many groups? n. • How many subgroups of group 1? n. How many elements in each subgroup? 1. G1=n × 1 • How many subgroups of group 2? --How many different combinations do we have if we select 2 elements for n elements? 𝑜! 𝑜 # subgroups of group 2 = 2 = 2!× 𝑜−2 ! How many elements in each subgroup? 3. G2= 𝑜 2 × 3

• How many subgroups of group 3? 𝑜 3 . How many elements in each subgroup? 7. Suppose we have chosen attributes a,b,c. The functional dependency is like: abc → ? What can “?” be ? We use 3-digit binary numbers to represent our choice. For each digit, “0” means we don’t choose, “1” means we choose. a b c 0 (Don’t choose) 1 (Choose) For example: 101 means we choose a, don’t choose b, choose c. “?” becomes “ac”. The functional dependency is abc → ac

• How many elements in each subgroup of group 3? --How many different 3-digit binary numbers? For normal numbers(decimal numbers). If it has three digits, there 10 3 =1000 different numbers.(0 to 999) For binary numbers. Likewise, if it has three digits, there 2 3 =8 different numbers. They are from 000 to 111. However. 000 means we choose nothing. Null is not allowed . There are 2 3 -1 different choices in each subgroup of group 3. G3= 𝑜 3 × (2 3 −1) • Therefore, for the ith group: Gi= 𝑜 𝑗 × (2 𝑗 −1) 𝑗 × (2 𝑗 − 1) 𝑜 𝑜 𝑜 • total=G1+G2+…+ Gi+Gn= σ 𝑗=1 𝐻𝑗 = σ 𝑗=1

Binomial theorem(Binomial expansion) 𝑜 0 (𝑏 0 𝑐 𝑜 ) + 𝑜 1 (𝑏 1 𝑐 𝑜−1 ) + …+ 𝑜 𝑗 (𝑏 𝑗 𝑐 𝑜−𝑗 ) +…+ 𝑜 • (𝑏 + 𝑐) 𝑜 = 𝑜 (𝑏 𝑜 𝑐 0 ) • What if a=2, b=1 ? It becomes: 𝑜 0 (2 0 1 𝑜 ) + 𝑜 1 (2 1 1 𝑜−1 ) + …+ 𝑜 𝑗 (2 𝑗 1 𝑜−𝑗 ) +…+ 𝑜 (2 + 1) 𝑜 = 𝑜 (2 𝑜 1 0 ) 𝑜 𝑜 𝑗 × 2 𝑗 = σ 𝑗=0 Recall what we already know: 𝑗 × 2 𝑗 − 1 = σ 𝑗=0 𝑗 × (2 𝑗 − 1) 𝑜 𝑜 𝑜 𝑜 total= σ 𝑗=1 𝑗 × 2 𝑗 − σ 𝑗=0 𝑗 × 1 𝑗 = 3 𝑜 − 2 𝑜 𝑜 𝑜 𝑜 𝑜 = σ 𝑗=0 How can the answer be like this?( The logical theory of the formula?)

• Conditions (a set of attributes) → Answers (a subset of the condition set) For each attribute, it has three different choices: 1. No show in either part. 2. Only appears in the condition part. 3. Appears in both part. (It can’t appear only in the answer part.) • For example, if Table(a,b,c,d) and bcd → bd Means “a” chose 1(no show), “c” chose 2(only in C), “b” and “d” chose 3(both). • For a table with n attributes, we can use an n-digit ternary number to show all the trivial functional dependencies we can get.

Table(a,b,c,d,e) a b c d e 0 (No show) 1 (Only in C) 2 (Both A,C) For example: 01021 means: bde → d For a table with n attributes, we have 3 𝑜 different ternary numbers, so we can write 3 𝑜 different sentences. However, some of them are illegal. -- The answer part can’t be empty.

• We should delete those which make the Answer part empty . -- The numbers which don’t have any “2” in them. For table(a,b,c,d,e), numbers like 10101(ace → ),or 00110(cd → ) are illegal. • How many illegal numbers in a table with n attributes? --How many n-digit binary numbers? (Because for illegal numbers, every digit can only be“0” or “1”, they are in fact binary numbers.) 2 𝑜 • total= 3 𝑜 (all posibilities) − 2 𝑜 (𝑗𝑚𝑚𝑓𝑕𝑏𝑚 𝑝𝑜𝑓𝑡)