Note that your solution program will be marked based on whether it

• is logically correct,
• handles exceptional situations reasonably,
• can be easily understood, and,
• properly follows the database application development principles (including the general software development principles).

Assignment Information

For this assignment, you are required to develop a database application program for the Food Sharing Program using the following relational schema (primary keys are underlined) 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)

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

Your database application program must be developed using C++ plus the Oracle CLI occi.

Problem Description:

Develop a database application used by the Food Sharing Program to simulate providing common service to multiple members.

Upon start of the program, your application should recognize the following three commands:

• place_order account_number package_size, where place_order is the command name and the account_number and the package_size are the two parameters for this command.
• unsubscribe account_number, where unsubscribe is the command name, and the account_number is the parameter for this command.
• terminate, to terminate the program.

The command place_order simulate the process that the Food Sharing Program accepting orders from active members for a particular delivery day (expectDate). I randomly picked the expected delivery day (expectDate) as March 31, 2025, Monday. It would be the best if your program uses a constant to represent this date value so that it can be changed easily.

Your program can safely assume that, whenever the program starts, the order numbers for new orders start from 60001 and then increment by 1 for each subsequent new order successfully accepted by your program.

When handling the place_order command, your program needs to check:

• the given account_number represent a valid active member (whose status is 'Active');
• the given package_size is one of the valid package sizes ('S', 'M' and 'L');
• the given member hasn't already placed an order for that particular expected delivery day.

If everything is in order from the previous checks, your program records data of the new order to the Orders table, using current date/time (from Oracle (easiest) or from the operating system) as the placeTime.

If any of the above checks fails, your program should display an appropriate error message and move on to process the next command.

When handling the unsubscribe command, your program needs to check that the given account_number represents a valid active member.

If that's true, then your program simply updates this member's status in the MemberAccounts table to 'Inactive'.

If the check fails, then your program should display an appropriate error message and move on to process the next command.

Additional Notes:

Don't forget to commit after each successful command processing to make the place_order/unsubscribe activity complete and data changes permanent in the database.

Your program should be running continuously and process many place_order/unsubscribe requests, and terminate only when it reads in the command terminate.

Because this program would be running continuously and processing many place_order/unsubscribe commands using almost identical steps respectively, prepared SQL statements must be used in as many places in the program as possible.

One of the important principles of developing a database application program is to trust the database management system and ALWAYS use one (although sometimes moderately complicated) query to retrieve ONLY the data needed by your application, instead of using one or many simple queries to retrieve many data and using customized code to manipulate the data into something needed by your application.

Following general software development principles, your source code file should have the proper header and inline comments to help me understand your program logic.

This is a relatively simple program, business logic wise, so it is not necessary to use multiple modules/files. However, I do expect and demand that functions should be used to achieve a proper level of abstraction in your program.

How to Submit

Your submission should include source code file(s) and Makefile. Upon typing make, an executable file named foodSharing should be generated.

If your program can't compile on our csci server (otter), you get 0 for this assignment. So at least compile and test run your program once on otter before submit.

Login to our csci server, in the directory that holds your source code file(s), submit using the following command:

~liuh/bin/submit 370 A7 .

You can use the following command to check what you have submitted to A7:

~liuh/bin/checksubmit 370 A7