CSC 455

Fall 2009

Lab 11

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)

Here, 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. Assume that an order can be shipped from several warehouses.

Write the relational algebra queries for the following:

a) List the Order# and Ship_date for all orders shipped form Warehouse# W2

b) List the CUSTOMER name, city and ORDER date and amount for Orders placed on 2009-10-08

c) List the SHIPMENT information from which the CUSTOMER named Lopez was supplied his orders. Produce a listing: Order #, Warehouse #, Ship_date