Assignment Information

The following relational schema (primary keys are underlined) are used by the Food Share Program as described in Assignment 1.

MemberAccounts(accnum, contactName, email, deliveryAddress, signUpDate, activationDate, status)
Producers(accnum, farmAddress)
FoodItems(productCode, name, unit)
SupplyRecords(accnum, productCode, supplyDate, quantity)
Orders(orderNum, placedBy, placeTime, packageType, expectDate, deliverTime, receivedBarcode, receivedPNum)
Batches(barcode, packingTime, sizeType)
Packages(barcode, pNum)
PackageContent(barcode, product, quantity)

This assignment is based on the above relational schema.

Many more details, including constraints, data types, etc, are captured in the SQL definitions of these tables.

You may use the supplied table definitions to create empty tables. You will then need to populate the tables (insert sample data) yourself in order to test your SQL queries.

Your Tasks:

Express each of the following two queries in an equivalent single SQL query and Relational Algebra query respectively against the relational schema defined above. The extra requirement is that the SQL query must be a pure PSJ (projection, selection and join) one that doesn't have ANY sub-queries.

1. List the contact name and email of each inactive member (whose status is 'Inactive') who has placed at least one order.
   (There shouldn't be any duplicates in the result.)
2. List the contact name and email of each active member (whose status is 'Active') who has never placed any order.

Express each of the following two queries in an equivalent single SQL query and Datalog query respectively against the relational schema defined above. (No extra requirement.)

1. For each active member (whose status is 'Active'), list their contact name and email, and the order number and package type of their latest placed order (whose place time is the largest).
2. List the contact name, email and farm address of each producer who is capable of supply EVERY food item. A producer is considered to be able to supply every food item if this producer had supplied every food item in the FoodItems table before.

The Food Sharing Program created a table called CurrentOrders that has the following attributes: orderNum, placedBy, contactName, email, deliveryAddress, and packageType. Apparently, each tuple in the CurrentOrders table describes the information of an order, including its order number, the information of the member who placed the order and expected to receive the right sized package to fulfill the order.

Every morning, a manager performs the following steps to ensure that the delivery workers can access the data in the CurrentOrders table to deliver the right package to the right order.

• Step 1: delete all the data used in the previous delivery day from the table CurrentOrders;
• Step 2: gather all the relevant information of each order whose expected delivery date is today and insert them into CurrentOrders table. (Note that Oracle uses current_date for today's date.)

Your task is to write the SQL statements to perform the above mentioned two action steps.

How to Submit

You are expected to include a legend to explain your relational algebra operators used in this assignment, and to include your Datalog and SQL queries in a text file (with the extension name .txt or .sql), and to include your Relational Algebra queries in the same text file or in a separated PDF file.

Make your queries logically correct and easy to understand. Make sure that your SQL queries can be successfully executed by Oracle database engine.

You can submit your assignment solutions using one of the following three ways:

• Submit a hard copy before the due date/time.
• Login to csci server, in the directory that holds all the files you want to submit, execute the following command:
  ~liuh/bin/submit 370 A6 .
  This submit script currently accept only .sql, .txt and .pdf files. If your file has other extension names, please ask me to update the submit list before you submit.
  You can use the following command to check what you have submitted to A6:
  ~liuh/bin/checksubmit 370 A6