Tutorial 7 : Functional Dependency

Tutorial 7: Functional Dependency

Question 1 According to the tuples in the table below, indicate the functional dependencies in the right table that must be false.

Solution 1

Question 2 • Below is an instance of R(A1,A2,A3,A4). Choose the FD which may hold on R • A4->A1 • A2A3->A4 • A2A3->A1

Solution 2 1. A4->A1 ??? • Incorrect: The 1st and 4th tuple violates it 2. A2A3->A4 ??? • Incorrect: The1st and 2nd tuple violates it. 3. A2A3 -> A1 ??? • Correct!

Question 3 • Consider relation R(title, theater, city) where • title is the name of a movie, • theater is the name of a theater playing the movie, and • city is the city where the theater is located. • We know • Different cities cannot have theaters with the same name. • Different theaters in the same city cannot play the same movie. • Write the functional dependencies implied by the above assumptions. • If we decompose R into (title, theater) and (theater, city), is this a legal decomposition?

Solution 3 • Different cities cannot have theaters with the same name. • Different theaters in the same city cannot play the same movie. • Functional dependencies: • theatercity • (city, title) theater • If we decompose R into R1(title, theater) and R2(theater, city), is this a legal decomposition? • Yes. • R1 and R2 have a common attribute theater. • This attribute is the primary key of R2.

Question 4 • Consider table TABLE(A, B, C). Write an SQL query to test whether A  B holds.

Solution 4 • SELECT AFROM TABLE T1WHERE EXISTS (SELECT T2.A FROM TABLE T2 WHERE T2.A = T1.A and T2.B <> T1.B) • Or • SELECT T1.AFROM TABLE T1, TABLE T2 WHERE T1.A = T2.A and T1.B <> T2.B • Or • SELECT AFROM TABLEGROUP BY AHAVING COUNT(DISTINCT B) > 1 • The functional dependency holds only if the result is empty. Alternative answers are suggested by students in class. Thanks!

Question 5 • Consider relation TABLE{A,B,C,D, E, F,G,H,I, J} with the following functional dependencies: • ABC • ADE • BF • AFGH • DIJ • Prove ABABCDEFGHIJ. • That is, we need to prove AB determines every individual attribute.

Solution 5 • TABLE{A,B,C,D, E, F,G,H,I, J}. • ABC, ADE, BF, AFGH, DIJ • ABA, and ABB • Trival • ABC • This FD is given. • ABD • ABADE D • ABE • ABADE E • ABF • ABBF

Solution 5 (cont.) • TABLE{A,B,C,D, E, F,G,H,I, J}. • ABC, ADE, BF, AFGH, DIJ • ABG • We are given BF. Hence: • ABAF GH  G • ABI • We proved earlier ABD. • Hence, ABDIJ I • ABJ • ABDIJ J

Solution 5 (cont.) • TABLE{A,B,C,D, E, F,G,H,I, J}. • ABC, ADE, BF, AFGH, DIJ • A simpler solution: closure test! • AB+ = ABCDEFGHIJ • Since ABCDEFGHIJ is in AB+, ABABCDEFGHIJ holds!

Question 6 • Let R(ABCDEFGH) satisfy the following functional dependencies: {A->B, CH->A, B->E, BD->C, EG->H, DE->F} • Which of the following FD is also guaranteed to be satisfied by R? 1. BFG --> AE 2. ACG --> DH 3. CEG --> AB

Solution 6 • FDs: {A->B, CH->A, B->E, BD->C, EG->H, DE->F} 1. BFG --> AE ??? • Incorrect: BFG+ = BFGEH, which includes E, but not A 2. ACG --> DH ??? • Incorrect: ACG+ = ACGBEH, which includes H, but not D. 3. CEG --> AB ??? • Correct: CEG+ = CEGHAB, which contains AB

Question 7 • Which of the following could be a key for R(A,B,C,D,E,F,G) with functional dependencies {AB->C, CD->E, EF->G, FG->E, DE->C, and BC->A} 1. BDF 2. ACDF 3. ABDFG 4. BDFG

Solution 7 • {AB->C, CD->E, EF->G, FG->E, DE->C, and BC->A} 1. BDF ??? • No. BDF+ = BDF 2. ACDF ??? • No. ACDF+ = ACDFEG (The closure does not include B) 3. ABDFG ??? • No. This choice is a superkey, but it has proper subsets that are also keys (e.g. BDFG+ = BDFGECA)

Solution 7 (cont.) • {AB->C, CD->E, EF->G, FG->E, DE->C, and BC->A} 4. BDFG ??? • BDFG+ = ABCDEFG • Check if any subset of BDFG is a key: • Since B, D, F never appear on the RHS of the FDs, they must form part of the key. • BDF+ = BDF  Not key • So, BDFG is the minimal key, hence the candidate key

Question 8 • Consider a relation with schema R(A, B, C, D) and FD = {AB → C, BC → D, CD → A, AD→B} (a) What are all the candidate keys of R? (b) What are all the superkeys of R that are not candidate keys?

Solution 8 FD = {AB → C, BC → D, CD → A, AD→B} • Single Attributes: A+ = A B+ = B C+ = C D+ = D • Pairs of Attributes: AB+ = ABCD (candidate key) AC+ = AC AD+ = ADBC (candidate key) BC+ = BCDA (candidate key) BD+ = BD CD+ = CDAB (candidate key)

Solution 8 (cont.) FD = {AB → C, BC → D, CD → A, AD→B} • Triples of Attributes: ABC+ = ABCD (superkey, not candidate key) ABD+ = ABDC (superkey, not candidate key) ACD+ = ACDB (superkey, not candidate key) BCD+ = BCDA (superkey, not candidate key) • All the Attributes: ABCD+ = ABCD (superkey, not candidate key)

Question 9 Consider R = {A, B, C, D, E, F, G} with a set of FDs F = {ABC→DE, AB→D, DE→ABCF, E→C} Find all the candidate keys of R

Solution 9 F = {ABC→DE, AB→D, DE→ABCF, E→C} • First, we notice that: • Gnever appears on RHS of any FD. So, Gmust be part of ANY key of R. • Fnever appears on LHS of any FD, but appears on RHS of some FD. So, Fis not part of ANY key of R • G+ = G ≠ R So, G alone is not a key!

Solution 9 (cont.) F = {ABC→DE, AB→D, DE→ABCF, E→C} • Now we try to find keys by adding more attributes (except F) to G • Add LHS of FDs that have only one attribute (E in E→C): • GE+ = GEC ≠ R • Add LHS of FDs that have two attributes (AB in AB→D and DE in DE→ABCF): • GAB+ = GABD • GDE+ = ABCDEFG = R; [DE→ABCF] It’s a key! • Add LHS of FDs that have three attributes (ABC in ABC→DE), but not taking super set of GDE: • GABC+ = ABCDEFG = R; [ABC→DE, DE→ABCF] It’s a key! • GABE+ = ABCDEFG = R; [AB→D, DE→ABCF] It’s a key! • If we add any further attribute(s), they will form the superkey. Therefore, we can stop here. • The candidate key(s) are {GDE, GABC, GABE}

Tutorial 7 : Functional Dependency