Create Stored Procedures to Add/Update/Delete an entity table Create a script to create a table named ProjectBilling which will have the following columns:

projectBillID char(6) : A 6 character unique identifier (numbers and letters)

TransAmount decimal(16,9) : The amount of the transaction

TransDesc varchar(255): A description of the transaction

TransDate datetime: The date of the transaction

projectID char(4):The Id of the project

accountMgr char(8):The employee who manages the bill ledger Include this create table script in your script.

You will create the SQL Scripts to create procedures to insert/ update data. The following definitions specify the parameters that can be passed in.

The underlined parameters are required, but the other parameters will be optional. Make sure that your procedure inserts records if the required parameters do not exist, but they update records if the required parameters do exist.

For example: If SP_AddUpdateProjBill: passes in projectBillID AA0102 and it DOESNT exists in the projectbilling table(s) , it will insert the values passed in. If SP_AddUpdateProjectBill: passes in projectBillID AA0102 and it DOES exists in the projectbilling table(s) , it will UPDATE the values passed in for the AA0102 record.

Procedures Needed: - SP_AddUpdateProjectBill: Adds/Updates the ProjectBilling Table with all the field information.

o Parameters: projectBillID, TransAmount, TransDesc, TransDate, projectId, accountMgr. - SP_DeleteProjectBill: Deletes a project bill by the ProjectBill Id.

o Parameters: projectBillId

Step 3: Create Stored Procedure to Process Project Delays You will create the SQL Script to create procedures to insert/ update data and process a project delay - SP_ProcessProjectDelay: Given a project Id, this procedure finds if any max end date of any activity within the project is after the projects projected end date. If this happens, the procedure will calculate how many days it is late (use DATEDIFF) and fines the project $1050 for each day late it is late. In addition, the project tables projectedenddate will be updated with the new end date and the fundedbudget will be updated with the original funded budget plus the fines per day late. o Parameters: projectId. Example: The Falcon Coaster has a ProjectId AA01 has a projected end date of 6/30/2017. It has 2 activities: ActivityId: AA90 ActivityName: Build Coaster EndDate: 6/01/2017 ActivityId: AA91 ActivityName: Inspect Coster EndDate: 7/30/2017 Since Activity AA91 ends 30 days after the projected end date of the project, the project will have an additional $31,500 (30 X $1050) added to the fundedbudget columns original value. Also, the projects new projected end date will be 7/30/17