2020/3/14 CSCI 370 - Assignment 3
csci.viu.ca/~liuh/370/assignments/A3.html 1/3
CSCI 370 Spring 2020 - Assignment 3
Due: 13:00, 19 March 2020, Thursday
This assignment is worth 8% of your overall course grade.
Your solution 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).
If your program has compile-time errors (on otter), you get zero.
Assignment Information
For this assignment, you are required to develop two database application programs for the
corporation with warehouse chain stores using the following relational schema derived from
the requirement of Assignment 1 and also used in assignment 2. The primary keys are
underlined.
Stores(sid, name, address, phone)
Products(barcode, name, description, price)
Members(mid, name, contactInfo, openAtStore, openTime, lastRenewTime)
Transactions(sid, pid, transactionTime, customer, employee)
TransactionContent(sid, pid, barcode)
ReturnTransactions(returnStore, returnPid, sid, pid, barcode, returnTime,
returnComment, employee)
Many details, including foreign keys, data types, etc, are captured in the SQL definition of
these tables.
All the database application programs should be developed using C++ plus the Oracle CLI
occi.
Problem 1 Description
Develop a database application to generate the single store sales data analysis report for a
given store.
Your program should have one and only one store id as its command line argument and then
retrieve the information needed to print the sales data analysis report in a text file named
Report-[StoreId].txt (replace the [StoreId] with the real store's id).
Your program should first make sure the store id provided is valid, then retrieve and save the
following information:
store's id,
store's name,
store's address,
store's phone number,
2020/3/14 CSCI 370 - Assignment 3
csci.viu.ca/~liuh/370/assignments/A3.html 2/3
the yearly total number of purchase transactions for each year, and
the yearly total dollar amount sold in these purchase transactions.
The result in the last two items should ordered in year number descending.
Click here to view a sample report text file.
If the store id provided is invalid, display an appropriate error message and terminate the
program.
Since this program will run to generate report for only one store at a time, it is not necessary
to use prepared sql statements. However, user input (including the command line argument)
must be sanitized if unprepared sql statements are used.
Problem 2 Description
Develop a database application to simulate the purchasing transactions at a check out point in
a store.
Your program should accept two strings as command line argument. This first string is the id
of the store, and the second string is the id of the employee assisting the purchasing
transactions.
Since it is a simulation, the program should receive the necessary information (customer's
membership id, product's barcode) through keyboard input and display messages on the
screen.
For each purchase transaction, your program should ask for the customer's membership id
first. If the membership id is not valid, terminate this transaction. If the id is valid, your
program should repeatedly read in a product's barcode until a special barcode "0000000000"
is entered to indicate the end of the purchase transaction. For each barcode (except the
sentinel value), your program should check to see whether it is valid. If it is valid, display the
product's name and price on the screen. If it is not valid, display an error message and ignore
it.
At the end of the purchase transaction, your program should display the total number of
products purchased in this transaction, and the total amount the customer should pay. Your
program should ask the customer whether he/she would like to make the payment. If the
customer answers "yes", complete the transaction and commit updates to the database. Any
other answer entered by the user, your program should roll back the transaction.
When update the transactions table, use either the operating system's time or the Oracle's
system time (sysdate) as the transaction time.
Once a transaction is completed (with either commit or roll back), your program should start
to process the next purchase transaction.
This program should shutdown only if the program read in "000000000000" as membership
id.
2020/3/14 CSCI 370 - Assignment 3
csci.viu.ca/~liuh/370/assignments/A3.html 3/3
Because this program would be running continuously and processing many purchase
transactions in almost the identical steps, it must use prepared sql statements in as many
places 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
You need to submit:
Your source code files. (It is assumed that each source code file has the extension name
of either .cpp or .h. If your files have other extension names, please let the instructor
know before you submit.)
Makefile. Upon typing make, your makefile should generate two executable files named
"reporting" and "checkout" respectively.
README. In which you should describe how to build/compile and use your programs,
what kind of input each of your programs expects, any known bugs in your programs,
and any other information that is related to your program that you would like the
instructor to know.
You have to submit your assignment electronically. To submit your assignment, first transfer
all the files you want to submit to your csci account, then use the command
~liuh/bin/submit 370 A3 .
on csci in the directory in which all the files are located.
You can also use the command
~liuh/bin/checksubmit 370 A3
on otter to check what you have submitted so far.
You can submit more than once. Each submission will first delete all the files submitted by you
previously, then copy your new submission. You should make use of this feature of the submit
script to ensure that your submission is not late. Submit early, submit often.