1.a. trivial

b. i.  empty set of FD's (i.e. only trivial ones)
   ii. A-->B, B->C, C->D, D->A
   iii. A->B, B->A, C->A, D->A

2.a. THIS HAS ONLY 5 POINTS (THE EXAM SAID 15, BUT IT WAS WRONG)

select y.cid
from claim x, claim y, policy u, policy v
where x.pid=03492 and x.pid = u.pid and y.pid = v.pid
  and u.propertyName = v.propertyName
  and u.year = v.year

b. THIS HAS ONLY 5 POINTS (THE EXAM SAID 15, BUT IT WAS WRONG)

The following doesn't quite work because p.premium is not a group by
attribute:

select c.pid, c.year
from   claim c, policy p
where c.pid=p.pid
group by c.pid, c.year
having sum(c.amount) > p.premium


This is ugly, but works:

select c.pid, c.year
from   claim c, policy p
where c.pid=p.pid
group by c.pid, c.year, p.pid, p.premium
having sum(c.amount) > p.premium

Another way:

select c.pid, c.year
from   claim c, policy p
where c.pid=p.pid
group by c.pid, c.year
having sum(c.amount) > max(p.premium)   (can take min here too)


c.

i.  (B)

ii. (C) Q2 returns properties A with a claim of $10000: that claim is
enough for the first to return A too.

iii. (B) If a property A is returned by Q1, then there is a policy P
with two claims, one in 1980 the other in 2002.  Query Q2 will also
return that property, by having both P1 and P2 be the same policy P.
But Q2 may return a property if it has two distinct policies P1, P2,
the first with a claim in 1982 the other with a calim in 2002, and
that property won't be returned by Q1

iv.  (A)  The two queries return the same answers.  (It takes some
thinking to see that).

3.
a. /university/college/department[name/text()="CS"]/student
b.

let $ss := distinct-values(/university/college/department/student/text())
for $s in $ss
return
  <student> <name> { $s } </name>
            <no_depts> {
              count(/university/college[department/student/text()=$s] }
            </no_depts>
  </student>

c.

college(cid,name,dean)   key: cid
department(did, cid, name, chair)   key: did
                                    foreign key: cid to college.cid
student(sid, did, name)   key: sid
                          foreign key: did to department


4.

a.  P1 only.  The others perform a selection on S and the index cannot
be used any more

b. P1, P2, P3.  In all cases the right operant returns tuples sorted
by the primary index, i.e. by S.D

c.  P1.  Push selections down, it's always better (we lose indexes,
but we don't need them for hash-join

d. i.NO ii.YES  iii.NO  iv. NO

e. i.NO ii.YES  iii.NO  iv. NO