CSE 594, Spring 2002

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 A_{1}, A_{2},
…, A_{n}. As a function of n, tell how many super keys R has, if:

a. The
only key is A_{1}

b. The
only keys are A_{1} and A_{2}

c. The
only keys are {A_{1}, A_{2}} and {A_{3}, A_{4}}

d.
The only keys are {A_{1}, A_{2}} and {A_{1},
A_{3}}

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 )