1 / 10

# Tutorial 4: Functional Dependency - PowerPoint PPT Presentation

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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

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

### 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?

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