CSE3330 Homework #2
Due on Monday November 1, before midnight
Worth 4% of the final grade


Note: this homework is about relational algebra, NOT SQL.

  1. (50 points) Consider the following relational database schema:
     
    employee ( employee_name, street, city )               key: employee_name
    works ( employee_name, company_name, salary )          key: employee_name, company_name
    company ( company_name, city )                         key: company_name
    manages ( employee_name, manager_name )                key: employee_name, manager_name
    
    Express the following queries in relational algebra:
    1. Print the names of all employees who work for First Bank.
    2. Print the names, street address, and cities of residence of all employess who work for First Bank and earn more than $50,000.
    3. Print the names of all employees who do not work for First Bank.
    4. Print the names of all employees who live in the same city and on the same street as do their managers.
    5. Print the names of all employees who earn more than every employee of First Bank.

  2. (50 points) Consider the following relational database schema:
     
    pizza ( pid, pname, size )               key: pid
    soldby ( pid, sname, price )             key: pid, sname
    store ( sname, phone, quality )          key: sname
    
    Express the following queries in relational algebra:
    1. Print the names and phone numbers of all excellent stores.
    2. Print the price of the 10-inch pepperoni pizza sold by Dominos.
    3. Print the names of all stores that sell pepperoni or Hawaiian pizza.
    4. Print the names of all stores that sell both pepperoni and Hawaiian pizza.
    5. Print the names of all good or excellent stores that sell pepperoni pizza under $8.
    6. Print the names of all stores that do not sell pepperoni pizza.
    7. Print the names of all stores that sell Hawaiian pizza but do not sell pepperoni pizza.

How to Submit the Homework

You need to submit your homework solution online using the following web form. Acceptable document formats are: Microsoft word/powerpoint document, PDF, and postscript. You may submit it as many times as you like, but only the most recently submitted file will be retained and evaluated.

Submit Homework #2:

Last modified: 10/20/10 by Leonidas Fegaras