Due on March 8 (beginning of class). All homework must be done independently.

There are 10 queries (10 points each)

Consider the following six relations for an order processing database application in a company:

    Customer (Cust#, Cname, City)
    Order(Order#, Odate, Cust#, Ord_Amt)
    Order_Item(Order#, Item#, Qty)
    Item(Item#, Unit_Price)
    Shipment(Order#, Warehouse#, Ship_date)
    Warehouse(Warehouse#, City)

Ord_Amt refers to total dollar amount of an order; Odate is the date the order was placed; Ship_date is the date an order is shipped from the warehouse. An order can be shipped from any warehouse. Specify the following queries in relational algebra:

a. List the Order# and Ship_date for all orders shipped from Warehouse# "W2".

b. List the Warehouse information from which the Customer named "Jose Lopez" was supplied his orders. Produce a listing of Order#, Warehouse#.

c. Produce a listing: Cname, #ofOrders, Avg_Order_Amt, where the middle column is the total number of orders by the customer and the last column is the average order amount for that customer. (Use aggregate functions -- Figure 2.29 on page 64 (5th ed) is a good example for incorporating multiple aggregate functions on different attributes but using the same attribute to group results))

d. List the Order# for orders that were shipped from all warehouses in New York. (Division operator?)

e. Delete all orders for customer named "Jose Lopez"

f. Move the shipping date by a week for all those orders originating from warehouses in Baltimore.

g. List all items that have a price greater than the average price.

h. Find out the maximum number of orders shipped out of Baltimore.

i. Find the item with the maximum unit price.

g. List all customer names whose orders were shipped from a warehouse in the same city as they live in.