The Investments database holds information for brokerage portfolios. Each portfolio is identified by a Portfolio ID. The Holdings table has records of the individual stocks and the number of shares (quantity) for each Portfolio ID. For example, Portfolio ID = 7 has 200 shares of GE stock and 500 shares of Home Depot Stock. There will be 2 rows in the Holdings table that represent this information.

If I want to know the market value of a holding for a specific day, I can look up the price of the stock in the prices table and multiply the price of the stock by the quantity held in the portfolio. For example, if I want to know the market value of the GE stock on 12/20/2017 for Portfolio #7, I would get the quantity of shares from the Holdings table as of 12/07/2017 and multiply it by the stock price for that day.

If I want to know the market value of the entire portfolio for a specific day, I would have to sum up the market values for all the individual holdings in that portfolio. Note that the date the stock is purchased and the date it is sold is tracked in the Holdings table. If I want to find all the holdings in Portfolio #7 for today, I would need to find the rows in the Holdings table for Portfolio #7 that were purchased before today, but have not yet been sold.

1. You will write a function that will accept 3 input parameters a stock ID, number of shares, and a date. The function will return the market value of the stock (shares*price) for that date. (30 pts)

2. You will then write a stored procedure that uses this function to compute the total market value for a given portfolio on a given date. (30 pts)

3. You will write statements to execute the stored procedure to find the total portfolio market value for Portfolio #3 on 12/14/2017. You must include statements that print the resulting market value output from the stored procedure. (10 pts)

4. You will create a trigger that is activated after a stock split event is inserted into the events table. Details below. (30 pts)

The events table has information on stock splits. When a new split occurs, a row will be inserted into the events table that specifies the stockID, the date of the split and the split factor. The trigger will execute the following events for all affected data PRIOR to the date of the split:

All shares of stock that were purchased prior to the date of the split will be multiplied by the split factor.

All prices for the stock that are earlier than the date of the split will be divided by the split factor.

You will test your trigger by inserting the following data into the events table:

Insert into events values (2,2017-12-14,3);

The script you submit to Canvas shall have the following:

Code for the function

Code for the stored procedure

Statements to execute the stored procedure (see #3)

Code for the trigger

Insert statement to test the trigger (see #5)

Holdings (i) PortfoliolD \& STOCKID Quantity Purch_Date Sell_date PRICES 8 StocklD 8 PriceDate Price