Tutorial 4 functional dependency
Download
1 / 10

Tutorial 4: Functional Dependency - PowerPoint PPT Presentation


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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
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:

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


ad