Autumn 2002
Name___________________________________
Total number of points:
100 points
1. [SQL: 30points] Consider the following relational schema:
Likes(drinker, beer)
Frequents(drinker, bar)
Serves(bar, beer)
a. [10 points] For all patrons of the bar ‘Blue Moon’, find the total
number of beers that they like and are served by ‘Blue
Moon’.
b. [10 points] Find
all drinkers that frequent some bar that serves
some beer that they don’t like.
c. [10 points]
Retrieve all drinkers that frequent only bars that serve
only beers that they don’t like.
2. [E/R: 25 points] Consider the E/R diagram below:
a.
[10points] Design a relational schema for it, and underline all keys in
every table.
b.
b. [15points] Write the SQL statements for creating these tables, and
include statements for primary keys, foreign keys, and unique attributes.
Choose any reasonable domains for the attributes.
3. [FDs and RA: 20 points] Recall that S+ denotes the closure of a
set of attributes S, under a given set of functional dependencies.
a. [5 points] Consider a relation R(A,B,C,D) with
functional dependencies
ABCà D, ADà C, BDà A. Answer the following:
i. Compute (AD)+ =
ii. Compute (BD)+ =
iii. Find all keys in R(A,B,C,D):
b. [5 points] A set S is called closed if S+
= S. For each of the following statements, indicate whether it is TRUE, or
FALSE. If your answer is FALSE, then give an example database, which shows that
it is false.
i. If S1 and S2 are closed, then S1 È S2 is also closed.
ii. If S1 and S2 are closed, then S1
Ç S2 is also closed.
c. [5 points] Does there exists a relation R(A,B,C)
where the closed sets are {}, AB, AC, ABC ? If YES, give an example of a set of
functional dependencies for which the closed sets are precisely these three; if
NO, explain why.
d. . [5 points] Consider the relation Course(name,
instructor). Write an expression in the relational algebra that
returns all instructors that teach only one course. You may use
only the five basic operators in the relational algebra, È, P,
r, ´,
¾, as well as joins.
[XML: 25points] We have the following DTD:
<!ELEMENT vacation
(region*)>
<!ELEMENT region (name,
package*)>
<!ELEMENT package (hotel,
airline, month, price)>
All other elements are assumed to be #PCDATA
a. [10points] write XPath expressions to return the following:
- return all package elements
that use "Northwest Airlines" as airline and whose price
is under $2000. Assume that
prices are expressed in integers, i.e. a test like <= 2000 should
work.
- return all hotel elements in
the "North West" region that are available in
"December"
It is OK to
include duplicates in the answers (XPath does not eliminate duplicates).
b. [15points] write an XQuery
expression that translates the document into the following structure:
<!ELEMENT promotion (hotel*)>
<!ELEMENT hotel (name, package*)>
<!ELEMENT package (region, airline, month, price)>
Your result should have a single entry for each hotel, even if that hotel
occurs under several packages, or under several regions in the input.