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.
