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 Forum Management using the following relational schema (primary keys are underlined) described in Assignment 1.

Users(username, nickname, contactInfo, signupDate, status)
Hosts(username)
Channels(channelName, description, creator, createTime)
ActiveSubscriptions(username, channel, subscribeTime)
Messages(messageID, messageBody, poster, channel, postTime, replyToMessage)
SubscribeHistory(username, channel, subscribeTime, unsubscribeTime)

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 Forum Management to handle the subscribe/unsubscribe process of the users.

Upon start of the program, it recognizes the following three commands:

• subscribe username channel_name, where subscribe is the command name, and username and channel_name are the two parameters for this command.
• unsubscribe username channel_name, where unsubscribe is the command name, and username and channel_name are the two parameters for this command.
• quit, to terminate the program.

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

• the given username represent a valid active user (whose status is 'Active');
• the channel_name represent a valid channel; and
• the given user is not currently subscribed to the given channel.

If everything is in order from the previous checks, your program records data of the new subscription to the ActiveSubscriptions table, using current date/time (from Operating System or from the Database Engine) as the subscribeTime.

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

When handling the unsubscribe command, your program only needs to check that the given user is currently subscribed to the given channel. If that's true, then your program should copy the data of this given subscription from ActiveSubscriptions table to SubscribeHistory table, using current date/time (from Operating System or from the Database Engine) as the unsubscribeTime, then delete the data of the given subscription from the ActiveSubscriptions table.

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

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

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

Because this program would be running continuously and processing many subscribe/unsubscribe commands in almost the identical steps, it must use prepared SQL statement 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.

How to Submit

Note that 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 I don't think that multiple modules/files are necessary. However, I do expect and demand that functions are used to achieve a proper level of abstraction in your program.

If your program (the one I'm supposed to mark) 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 you upload it to VIU Learn.

Submit ONLY your source code file (*.cpp) to A7 under CSCI 370's assessment and assignments tab on VIU Learn.

You can submit multiple times, please make sure that I understand which file is the one you would like me to mark.