Homework 5 Solution

Step 1
Build procedures around a database for a simple stock brokering system. The schema for this database is provided on the next page, along with some sample data. Your system needs to model the purchasing and selling of stocks on an open market. Individuals or groups (both represented by the Person entity) may purchase and sell stocks from each other on the market, with the price and quantity being determined by the amount owned and available funds.




Your submission must include functions and/or procedures with the following 4 names and behaviors.

BuyStock - Given a person name, stock name, and amount, purchase that many shares of that company from the open market, and return the amount paid for those items. If there are not that many shares available or the funds that person owns are insufficient, the transaction should fail.

SellStock - Given a person name, stock name, amount, and price, put that many shares from the user’s owned shares in that company up for sale, with that price per share as the sale price. If the user does not own the necessary amount of that stock or if the price is less than or equal to 0, do not put any of the stock up for sale. Note that this may require dividing the stocks owned in the stock table into multiple records.

DepositFunds - Given a person name and an amount, add that amount of funds to their available funds.

WithdrawFunds - Given a person name and an amount, subtract that amount of funds from their available funds.

You must also introduce triggers which guarantee the following rules:

The amount of any grouping of stock must be greater than 0.

The amount of available funds for any person must be non-negative.

Each sell order must have a positive amount and a price of at least 0.05.




Put all your function/procedure definitions into a file named buildFunctions.sql, and all your trigger definitions into buildTriggers.sql.




You may wish to write additional functions to help you test your program, generating new stocks and sell orders. You can also reset the database with raw insert statements and as such no other functions are required for this assignment.













Step 2
Write a Java application which connects to the local database.

This application should be able “log in” as a particular person, asking the user for the name of their account. This does not need to use MySQL user permissions. All actions performed by the program should use that account. The application should provide the following features (set up a loop to ask the user which operation they would like to perform)

Query current balance – return the amount of available funds for the current User.
List owned stocks – list each of the company names and amount of shares owned for this user.
Show stocks available for purchase – Enumerate each of the company names, amount of shares being sold, and price per share. The user may then select a lot to purchase using the list number, and have the database either purchase those stocks from the current balance or report failure. You should use the procedure you wrote for this action.
Add Funds – ask the user how much they would like to deposit, then invoke DepositFunds to add to the user’s available funds in the database.
Remove Funds – ask the user how much they would like to deposit, then invoke WithdrawFunds to deduct from the user’s available funds in the database.
Exit – exit the application.



Step 2 should be submitted in a file called StockUser.java in the Homework 5 codio project.