The Databases Course

Exercise No. 4

Relational Algebra, Calculus and QBE

Due on January19th by midnight

  1. Rewrite the first six queries of exercise 3 in the domain relational calculus.
  1. Express the following queries in QBE (Query by Example):
  1. What are the names of sites in Ukraine which hosted a Flamingo in a research project?
  2. List the id's of whales that weigh more than 500 kilograms.
  1. The beer drinkers' database contains the following relations:
    Frequents(drinker, bar) , Serves(bar, beer) , Likes(drinker, beer)
    The first indicates the bars each drinker visits, the second tells what beers each bar serves, and the last indicates which beers each drinker lines to drink. Note that a drinker may not like any beer, a bar may not serve any beers, and so on.
    Express the following queries in both relational algebra and the safe domain relational calculus:
  1. Find the drinkers that frequent only bars that serve some beer that they like.
  2. Find the drinkers that frequent no bar that serves a beer that they like.
  1. Given are two relations, R(A,B,C) and Q(B,C)
  1. Express their semi-join using tuple relational calculus.
  2. Express their division using tuple relational calculus.
  3. Assume that C is a numeric field. Write a tuple relational calculus query that takes Q as input, and returns the list of B's that are attached to the smallest C in the table.
  1. A calculus query is domain independent, or safe, if enlarging the domain from which we assign values to the free variables of the query, beyond the active domain, can never enlarge its result.
  1. Explain why every relational algebra query is safe.
  2. Are unsafe queries expressible in relational algebra?
  1. A query is called monotone if enlarging any of its input relations will not decrease its output relation. Prove that if an algebraic query does not contain non-monotonic operators (that is, minus), then it must be monotonic.
  1. The algebra and two calculus languages have equal expressive power, but SQL can do more. Give an example of a query that can be expressed in SQL but not in any of the theoretical languages.