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. For each loyal member who had placed at least one order, list the member's contact name and delivery address.
   There shouldn't be any duplicates in the result.
   Extra information: A loyal member is defined as someone who has never cancelled their membership with the Food Sharing Program. In other words, their status is still 'Active' and their sign up date and activation date remain the same.
2. For each order that received (and accepted presumably because the data is in the database now) a wrong sized package (i.e., the package size demanded by the order is different from the size of the package delivered), list the contact name and delivery address of the member who placed the order, and the delivered package's batch barcode and package size, and the delivery date.
3. List the contact name, email and delivery address of each member whose status is 'Active' now, but who never placed any order in year 2024.
   There shouldn't be any duplicates in the result.
4. List the contact name, email and farm address of each producer who supplied some food item that's not broccoli to the Food Sharing Program in 2024. Broccoli's name is stored as 'Broccoli' in FoodItems table.
   There shouldn't be any duplicates in the result.
5. List the contact name, email and farm address of each producer who never supplied any broccoli to the Food Sharing Program in 2024. Broccoli's name is stored as 'Broccoli' in FoodItems table.

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