CSE 444 Midterm Solutions

Autumn 2002

Total number of points: 100

1.      a. SELECT f.drinker, count( distinct f.beer)
     FROM frequents f, likes l, serves s
     WHERE f.bar = ‘Blue Moon’ and
                   f.drinker = l.drinker and l.beer = s.beer and s.bar = f.bar
     GROUP BY f.drinker

b. SELECT DISTINCT f.drinker
    FROM Frequent f, Serve s
    WHERE f.bar = s.bar and (f.drinker, s.beer) not in Likes

c. SELECT drinker
     FROM Likes
     WHERE drinker not in (SELECT l.drinker
                                          FROM Frequents f, Likes l, Serves s
                                          WHERE f.drinker = l.drinker and
                                                         f.bar = s.bar and
                                                         s.beer = l.beer)

The answers in your midterms included a number of non-solutions for 1b and 1c. These answers where correct SQL queries, but which computed something else than what we asked on the midterm. Here is what they computed: all these are interesting questions, but they differ from the questions on the midterm. If your SQL query was one of them, please make sure you understand why your answer is incorrect. On the other hand you may try to answer all the questions below: they are excellent exercises in SQL:

1. Return all drinkers that frequent only bars that don't serve some beer that they like. Equivalently: drinkers that like some beer that is not served by any bar that they frequent.

2. Return all drinkers that frequent some bar that doesn't serve some beer they like. Equivalently: drinkers that like some beer that is not served by some bar they frequent. Still equivalently: drinkers that like some beer and frequent some bar, but that bar doesn't serve that beer.

3. Return all drinkers that frequent some bar that serves only beers that they don't like. Equivalently: drinkers that frequent some bar that serves no beer that they like.

4. Return all drinkers that frequent some bar that serve only beer that none of their customers like

2. a.

Organization(oid, name)
Building(name, oid)
Department(oid, chair)
Located(oid, bname)

b.

CREATE TABLE Organization(oid varchar(20) PRIMARY KEY,

name varchar(60) NOT NULL)

CREATE TABLE Building(name varchar(25) PRIMARY KEY,

oid varchar(20) REFERENCES Organization(oid))

CREATE TABLE Department (oid varchar(10) PRIMARY KEY
REFERENCES Organization(oid),

chair varchar(30))

CREATE TABLE Located (oid varchar(20) REFERENCES Department(oid),

bname varchar(25) REFERENCES Building(name),

PRIMARY KEY(oid,bname))

3. a. (AD)+ = ACD,

(BD)+ = ABCD,

minimal keys are (BD), (ABC); keys are these plus all their supersets.

i. FALSE: R(A,B,C), ABàC. Then A is closed, B is closed, but AB is not closed.

ii. TRUE

c. NO. If AB and AC are closed then A must also be closed.
d.

P_instructor(Course) ¾
P_instructor(s_name_{!= n AND instructor = i}(Course ´ r_n,i(Course)))

4. a.

/vacation/region/package[airline/text()=”Northwest Airlines][price/text() <= 2000]

/vacation/region[name/text()=”North West”]/package[month/text()=”December”]/hotel

b.
<promotion>

{ FOR $h in distinct(//hotel/text())

RETURN
<hotel>

<name> { $h } </name>

{ FOR $r in vacation/region,

$p in $r/package[hotel/text()=$h]

RETURN
<package>

<region> { $r/name/text() } </region>

<airline> { $p/ariline/text() } </airline>

<month> { $p/month/text() } </month>

<price> { $p/price/text() } </price>

</package>

}

</hotel>
}

</promotion>