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. For example: 101 means we choose a, don’t choose b, choose c. “?” becomes “ac”. The functional dependency is abc→ac

a b c 0 (Don’t choose) 1 (Choose)

• 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 103=1000 different numbers.(0 to 999) For binary numbers. Likewise, if it has three digits, there 23=8 different numbers. They are from 000 to 111.

• However. 000 means we choose nothing. Null is not allowed.

There are 23-1 different choices in each subgroup of group 3. G3= 𝑜

3 × (23−1)

• Therefore, for the ith group: Gi= 𝑜

𝑗 × (2𝑗−1)

• total=G1+G2+…+Gi+Gn=σ𝑗=1

𝑜

𝐻𝑗=σ𝑗=1

𝑜 𝑜 𝑗 × (2𝑗 − 1)

Binomial theorem(Binomial expansion)

• (𝑏 + 𝑐)𝑜=

𝑜 0 (𝑏0𝑐𝑜) + 𝑜 1 (𝑏1𝑐𝑜−1)+ …+ 𝑜 𝑗 (𝑏𝑗𝑐𝑜−𝑗)+…+ 𝑜 𝑜 (𝑏𝑜𝑐0)

• What if a=2, b=1 ? It becomes:

(2 + 1)𝑜=

𝑜 0 (201𝑜) + 𝑜 1 (211𝑜−1)+ …+ 𝑜 𝑗 (2𝑗1𝑜−𝑗)+…+ 𝑜 𝑜 (2𝑜10)

=σ𝑗=0

𝑜 𝑜 𝑗 × 2𝑗

Recall what we already know: total=σ𝑗=1

𝑜 𝑜 𝑗 × 2𝑗 − 1 = σ𝑗=0 𝑜 𝑜 𝑗 × (2𝑗 − 1)

= σ𝑗=0

𝑜 𝑜 𝑗 × 2𝑗 − σ𝑗=0 𝑜 𝑜 𝑗 × 1𝑗= 3𝑜 − 2𝑜

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) 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.

a b c d e 0 (No show) 1 (Only in C) 2 (Both A,C)

• 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𝑜(𝑗𝑚𝑚𝑓𝑕𝑏𝑚 𝑝𝑜𝑓𝑡)