CSE 594, Spring 2002

 

Assignment #2: due Thursday, May 2

 

1.      [20 points] Suppose you are given a relation R with four attributes, ABCD. For each of the following sets of functional dependencies, assuming those are the only dependencies that hold for R, do the following:

§         Compute {AB}+.

§         Specify all of the candidate keys.

§         Identify whether R is in BCNF or a 3NF or neither?

§         If R is not in BCNF, decompose it into a collection of BCNF relations.

 

a.       BàC, DàA

b.      CàD, CàA, BàC

c.       ABCàD, DàA

d.      ABàC, ABàD, CDàA, CDàB

 

2.      [20 points] Suppose R is a relation with attributes A1, A2, …, An. As a function of n, tell how many super keys R has, if:

a.       The only key is A1

b.      The only keys are A1 and A2

c.       The only keys are {A1, A2} and {A3, A4}

d.      The only keys are {A1, A2} and {A1, A3}

 

3.      [40 points] Write the following queries in SQL.  The schema for the database is quoted below, and you can assume that the name fields are primary keys for the Drinkers, Bars, and Beers tables:

§         Drinkers(personname, age, astrosign)

§         Bars(barname, zip, dancing)

§         Beers(beername, price, country)

§         Frequents(personname, barname)

§         Likes(personname, beername)

§         Sells(barname, beername)

a.       [4 points] List the name of all bars that sell "Samuel Adams" beer.

b.      [4 points] List the name of all bars that sell BOTH "Samuel Adams" and "Bud".

c.       [4 points] List the name of all bars that sell AT MOST "Samuel Adams" and "Bud".

d.      [4 points] List the name and price of all beers sold only at "Buzzard's".

e.       [4 points] For each drinker list their name and the number of beers they like.

f.        [4 points] For each bar frequented by at least 3 Sagittarian drinkers, find the cheapest beer they sell.

g.       [4 points] List the names of all pairs of bars that sell at least 5 beers in common. Write your query such that 5 can be easily replaced with 500: the assignment asks only for 5 because the database doesn't have 500 beers.

h.       [6 points] Sad People: List the names and astrological signs of all the drinkers that frequent only bars that sell only beers they don't like.

i.         [6 points] Happy People: List the names and astrological signs of all the drinkers that frequent only bars that sell some beer they like.

 

4.  [20 Points] Translate the following SQL queries into relational algebra expressions. The queries refer to the single-table schema Products(Name, Price, Producer).

a.   SELECT DISTINCT P.Producer
      FROM Products P
      WHERE 100 < ANY ( SELECT P2.Price
                                 FROM Products P2
                                        WHERE P.Producer = P2.Producer ) 
 
 
b.   SELECT DISTINCT P.Producer
      FROM Products P
      WHERE 100 < ALL ( SELECT P2.Price
                              FROM Products P2
                              WHERE P.Producer = P2.Producer )