CSE 544, Winter 2001

Assignment 2: due Thursday, February 1

1. [20 points] Exercise 2.5 from the textbook. Draw an ER diagram for the described scenario.

2. [20 points]
   1. Create a relational schema that captures the E/R diagram below.
   2. Which relation in your relational schema represents the relationship "insures" in the E/R diagram, and why is that your representation?
   3. Compare the representation of the relationships "drives" and "operates" in your schema, and explain why they are different.
   4. What are the keys for each relation in your relational schema? You have to underline the attributes that form a key in each relation.  Hint: R(A, B, C) and R(A, B, C) mean two different things.

    

3. [40 points] Write the following queries in SQL. You can test them by running them on this database:[SQL Server Version] [PostgreSQL Version]. If you want to use PostgreSQL, here are instructions. Please include with your submission a print out of the results of the query evaluation on the provided database. Also, turn in the queries in electronic form. 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)

   1. [4 points] List the name of all bars that sell "Samuel Adams" beer.
   2. [4 points] List the name of all bars that sell BOTH "Samuel Adams" and "Bud".
   3. [4 points] List the name of all bars that sell AT MOST "Samuel Adams" and "Bud".
   4. [4 points] List the name and price of all beers sold only at "Buzzard's".
   5. [4 points] For each drinker list their name and the number of beers they like.
   6. [4 points] For each bar frequented by at least 3 Sagittarian drinkers, find the cheapest beer they sell.
   7. [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.
   8. [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.
   9. [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). In this problem you have to write two relational algebra trees.

   1.  
      SELECT DISTINCT P.Producer
      FROM Products P
      WHERE 100 < ANY ( 	SELECT P2.Price
      			FROM Products P2
      			WHERE P.Producer = P2.Producer  ) 
      
          
       
      SELECT DISTINCT P.Producer
      FROM Products P
      WHERE 100 < ALL ( 	SELECT P2.Price
      			FROM Products P2
      			WHERE P.Producer = P2.Producer  )