Loading in 5 sec....

Tutorial 4: Functional DependencyPowerPoint Presentation

Tutorial 4: Functional Dependency

- 4808 Views
- Updated On :

Tutorial 4: Functional Dependency. 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 ?.

Related searches for Tutorial 4: Functional Dependency

Download Presentation
## PowerPoint Slideshow about 'Tutorial 4: Functional Dependency' - Faraday

**An Image/Link below is provided (as is) to download presentation**

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

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

Presentation Transcript

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

- Yes.

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

Download Presentation

Connecting to Server..