- 4808 Views
- Uploaded on

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.

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