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 previous assignments:

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 to calculate a realtor's commission in a given year.

To simplify the business logic, let's assume that the real estate company is like the company "One Percent Realtoy" (https://bc.onepercentrealty.com/) when they calculate the realtor's commissions: one percent of the purchase price to the listing realtor and one percent of the purchase price to the buyer's realtor (soldBy realtor).

Your program should accept a year number and the service name of a realtor as its command line arguments, and then calculate the given realtor's commission (through transactions as the listing realtor and transactions as the buyer's realtor) in the given year.

The first word in the command line arguements must be a year number, and the subsequent words combined should represent the service name of a realtor.

Your program should first verify that the year number provided is an integer number between 2000 (the year the hypothetical real estate company started) and 2025 (last full year of the activities) (inclusive). If the year number is invalid, show a proper message and terminate your program.

If the service name provided through the command line argument doesn't represent a valid realtor, show a proper error message and terminate your program. Note that there might be white spaces in a realtor's service name.

If both inputs through the command line arguments are valid, your program should retrieve data to display the following results for the realtor:

• the realtor's information, including their RID, service name, contact information, and experience level;
• the number of listings sold in the given year while the realtor acted as the listing realtor; and the total commission the realtor earned from these listings (as listing realtor), calculated as one percent of the sum of the purchase prices of these listed properties;
• the number of listings sold in the given year while the realtor acted as the buyer's realtor; and the total commission the realtor earned from these listings (as soldBy realtor), calculated as one percent of the sum of the purchase prices of these listed properties.

Since this program will run to generate just one report at a time, it is not necessary to use prepared SQL statements. However, user input (including the command line arguments) must be sanitized if unprepared SQL statements are used.

Extra requirement: All the calculations must be done by the database system (in SQL statement(s)).

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 calculateCommission 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 A8 .

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

~liuh/bin/checksubmit 370 A8