CSE 444 Midterm

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.