Answer the following question using the database above in SQL Server.

1) Create a stored procedure (usp_PostSixMonthsInterest) that will add the amount of interest an account has earned after a six month period to the account. The stored procedure should use fn_CalculateInterest to calculate the interest and should take the following parameters as input.

a) Account Id

b) Yearly interest rate

2) Add a stored procedure (usp_AccountDeposit) that operates in transactions (e.g. BEGIN TRAN, COMMIT TRAN). The stored procedure should add the deposit amount to the accounts balance. The following parameters are expected as input:

a) Account Id

b) Deposit amount

3) Create the following table ClosedAccounts Account (AccountId, CustomerId, Balance). Then, create a stored procedure or function that accepts a customer Id as a parameter and completes the following operations:

a) Moves any open accounts to the ClosedAccounts archive table. b) Returns the amount that should be returned to the customer for all of their accounts. It may also return a negative amount, which indicates the customer owes the bank money.

15j-CREATE TABLE Customer 16 17 18 19 20 21 CustomerId int IDENTITY CONSTRAINT pk_customers PRIMARY KEY FirstName nvarchar 5e) NOT NULL LastName nvarchar (50) NOT NULL, Email nvarchar(5e) SSN int UNIQUE 23 24 ECREATE TABLE Account 25 26 27 28 29) AccountId int IDENTITY CONSTRAINT pk_account PRIMARY KEY CustomerId int CONSTRAINT fk_account_to_customer FOREIGN KEY REFERENCES Customer (CustomerId), Balance money NOT NULL 15j-CREATE TABLE Customer 16 17 18 19 20 21 CustomerId int IDENTITY CONSTRAINT pk_customers PRIMARY KEY FirstName nvarchar 5e) NOT NULL LastName nvarchar (50) NOT NULL, Email nvarchar(5e) SSN int UNIQUE 23 24 ECREATE TABLE Account 25 26 27 28 29) AccountId int IDENTITY CONSTRAINT pk_account PRIMARY KEY CustomerId int CONSTRAINT fk_account_to_customer FOREIGN KEY REFERENCES Customer (CustomerId), Balance money NOT NULL