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 queries in an equivalent Relational Algebra query, Datalog query and SQL query respectively against the relational schema defined above.

1. List each member's name, email and delivery address;
2. List each producer's name, delivery address and farm address;
3. For each member whose delivery address contains "Nanaimo" and signed up to the program before March 15th, 2020, list the member's contact name, delivery address and current status.
4. For each unsatisfied order (i.e., their delivery time is still null) placed by any food producer, list the order's place time, expected delivery time, and the contact name of the member who placed the order.
5. For each member who placed at least one order whose expected delivery time is in November 2024, list that member's account number, contact name and delivery address. There shouldn't be duplicates in the result.

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 A3 .
  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 A3:
  ~liuh/bin/checksubmit 370 A3