Create Presentation
Download Presentation

Download Presentation
## Tutorial 4: Functional Dependency

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -

**Question. Let R be a relation with schema {A, B, C, D, E},**on which the following functional dependencies hold: • A BC, CD E, B D, E A. • Compute the closure of CD. • Also do we have B E?**Let R be a relation with schema {A, B, C, D, E}, on which**the following functional dependencies hold: • A BC, CD E, B D, E A. • Compute the closure of CD. • {CD}+ = {A, B, C, D, E} • Also do we have B E? • {B}+ = {B, D}. So, no.**Question. Let R be a relation with schema {A, B, C, D, E},**on which the following functional dependencies hold: • A BC, CD E, B D, E A. • List all the candidate keys of R.**R = {A, B, C, D, E}.**• A BC, CD E, B D, E A. • Solution sketch: • {A}+= {A, B, C, D, E} • {B}+= {B, D} • {C}+= {C} • {D}+= {D} • {E}+= {A, B, C, D, E} • {A} and {E} are candidate keys. However, there may exist other candidate keys that do not involve these attributes. • {BC}+ = {A, B, C, D, E} • {BD}+ = {B, D} • {CD}+ = {A, B, C, D, E} • {BC} and {CD} are also candidate keys. • Every other combination of attributes fully encloses at least one of the 4 candidate keys already found. So no other candidate key can exist.**Question. 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?**Different cities cannot have theaters with the same name.**• Different theaters in the same city cannot play the same movie. • Functional dependencies: • theatercity • (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 a candidate key of R2.**Question. Consider table TABLE(A, B, C). Write an SQL query**to test whether A B holds. • Given two answers, one containing a nested query and the other not.**Consider table TABLE(A, B, C). Write an SQL query to test**whether A B holds. • Answer 2: • SELECT AFROM TABLE T1WHERE EXISTS (SELECT T2.A FROM TABLET2 WHERE T2.A = T1.A and T2.B <> T1.B) • The functional dependency holds if and only if the result is empty.**Consider table TABLE(A, B, C). Write an SQL query to test**whether A B holds. • Answer 1: • SELECT AFROM TABLE T1GROUP BY AHAVING COUNT(DISTINCT B) > 1 • The functional dependency holds if and only if the result is empty.