CS 347 -- Assignment #3

Relational Algebra

Due Friday, September 19 by 5pm

 
Use the mail-order database, whose schema definition and tuple population is defined by a single SQL script. Write relational algebra expressions for the for the following queries.  Note: for some queries, you may have to store intermediate results in temporary relations, and then query those relations, and you may need to use the Count(<relation>) operator.
  1. How many customers and employees live in 'Wichita'.
     
  2. List names and numbers of each part where its total sales exceeds $40.
     
  3. What employees have sold no parts?
     
  4. How many parts on level 20 have been sold?
     
  5. What are the names of customers that have ordered 'When Harry Met Sally'?
  6. What part appears on the most orders?
     
  7. The following is a list of relational algebra expressions over the schema A(x,y,z) and B(a,b,z).  Assume that processing more rows and retaining unnecessary attributes (e.g., during a join) is expensive.  For each expression below, write a relational algebra expression that is equivalent to the original expression but runs optimally:

a. project(x, y, b) ( A natural-join B )

b. project (y, b) select (x = 5 and a =4) (A natural-join B)