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

• is logically correct,
• handles exceptional situations reasonably,
• is easily human readable and understandable, and,
• is developed properly following the database application development principles (including the general software development principles).

If your program has compile-time errors (on otter), you get zero for this assignment.

Assignment Information

For this assignment, you are required to develop a database application program for the real estate company using the following relational schema (primary keys are underlined) described in Assignment 1.

Properties(folio, civicAddress, zoning, builtYear, description)
MajorProjects(folio, year, description)
Realtors(RID, serviceName, name, contactInfo, expLevel)
Listings(listingID, folio, listedBy, listDate, status, description, askingPrice)
ClosedListings(listingID, soldBy, closingDate, purchasePrice)
Clients(email, name, otherInfo)
Leads(email, listingID, interest)

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 real estate company to simulate the closing process for many listed properties.

Upon start of the program, your application should continuously process requests of closing a sales transaction of a listed property until it gets a signal to shut down.

For each sales transaction, your program should first ask the listing id of the listed property involved in this transaction. Your program should check the following things about this listing:

• the given listing id represents a valid listing;
• the valid listing's status is either 'Active' or 'Pending';

If both the above checks are successful, then your program should show the following information about the listing to be closed (presumably to be viewed and confirmed a last time by the sellers and buyers and their respective realtors): the listing id, the civic address of the listed property, the service name of the listing realtor, the list date and the asking price.

Then, your program should proceed to ask and read in the RID of the selling realtor and the purchase price of the listed property.

For these two inputs, your program needs to validate that the given RID represent a valid realtor, and the purchase price is at least a positive number. Note that it doesn't matter whether the selling realtor is the same as the listing realtor.

If all the checks are successful, your program should update the listing's status to 'Sold', and insert the closed listing's data to the ClosedListings table. The current date should be used as the closing date.

If any of the checks failed in the process, your program should show a proper message, then go back to waiting for the next request of closing a sales transaction.

When your program receives a request to close a sales transaction but is given a sentinel value of 'shutdown' as the listing id, that's the signal for your program to terminate.

Additional Notes:

Don't forget to commit after each successful closing process to make the transaction complete and the data changes permanent in the database.

Your program should be running continuously and process many sales transaction requests, and terminate only when it reads in the sentinel value 'shutdown' as the listing id.

Because this program would be running continuously and processing many sales transactions using almost identical steps, 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 retrieve ONLY the data needed by your application with as few statements as possible, instead of retrieving many data using very simple SQL statements and then manipulating the data into something needed by your application using customized code.

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

This is a relatively simple program, business logic wise, so it is not necessary to use multiple modules/files. However, it is expected and required 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 closingTransactions 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