COP 4703 Stored Procedures

Requirements

Building upon your project 1, the park will be a Star Wars themed park. You must design additional parts of the database and create the following SQL Script.

Step 1: Design and create the tables

You must create additional tables to hold Project and Activity Data.

IMPORTANT: For Project 2, DO NOT CREATE ANY FOREIGN KEYS FOR ANY OF THE TABLES

A project represents the construction of a facility with a limited scope of work and financial funding. A Project can be composed of many activities which indicate the different phases in the construction cycle.

Example Project Name: Bobba Fett's Bounty Chase Ride

An activity represents the work that must be done to complete the project.

Example Activity Name:

For Example activity name could be "Phase 1 Design of Bounty Chase ride" Or name could be Final construction of Bounty Chase ride Etc

You must normalize the project table to come up with a new set of tables. You will then write the create script for these tables.

Project (projectId, projectName, firmFedID, firmName, firmAddress, fundedbudget, projectStartDate, projectStatus , projectTypeCode, projectTypeDesc, projectedEndDate, projectManager, (activityId, activityName, costToDate, activityStatus, startDate,endDate) )

To normalize the tables, you must use the following function dependencies:

ProjectId,ActivityId -> projectName, firmFedID, firmName, firmAddress, fundedbudget, startDate, projectStatus, projectTypeCode, projectTypeDesc, projectedEndDate, projectManager, activityName, costToDate, activityStatus, startDate, endDate.

projectId -> projectName, firmFedID, fundedbudget, startDate, projectStatus , projectTypeCode, projectedEndDate, projectManager.

projectTypeCode -> projectTypeDesc

firmFedID -> firmName, firmAddress

When creating the tables, use the following column names and data types (important) for columns:

• projectId (char(4)) : A 4 character unique identifier (numbers and letters).
• projectName (varchar(50)) : The name of the construction project.
• firmFedID (char(9)) : A 9 character Federal ID (Example: 123456789)
• firmName (varchar(50)): The name of the construction firm.
• firmAddress (varchar(50)) : The address of the construction firm.
• fundedbudget (decimal(16,2)): The money amount allocated tthis project
• projectStartDate (date): The date the project started.
• projectstatus (varchar(25)): The status of the project (either active,inactive,cancelled,completed)
• projectTypeCode (char(5)): The project type code are FAC, RIDE, RET, and FOOD.
• projectTypeDesc (varchar(50)): The project type descriptions for a project are: Facility, Ride, Retail and Restaurant
• projectedEndDate (date) The date the project is scheduled tend.
• projectManager (char(8)) The employee number of the employee whis managing this project
• activityId (char(4)): A 4 character unique identifier for the activity.
• activityName (varchar(50)): The name of the activity.
• costToDate (decimal(16,2)): The cost of the activity tdate.
• activityStatus (varchar(25)) : The status of the activity (either active,inactive,cancelled,completed)
• startDate (date): The date the activity began.
• endDate (date): The date the activity ended.

You will write the script to create the tables which resulted from your normalization. Each table should have a primary key defined. You should have more than one table after you normalize.

NOTE IMPORTANT!

You should end up with at least:

- a table that will hold the man project data and will have projectId and projectName, along with other related fields based on your normalization process. Please name this table, ProjectMain.

- a table that will hold the main activity data and will have activityId and activityName, along with other related fields based on your normalization process. Please name this table, ActivityMain.

After correct normalization you will end up with more tables.

Step 2: Create Stored Procedures to populate the tables

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.

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_AddUpdateProject: passes in projectID "AA01" and it DOESN'T exists in the project table(s) , it will insert the values passed in.

If SP_AddUpdateProject: passes in projectID "AA01" and it DOES exists in the project table(s) , it will UPDATE the values passed in for the AA01 record.

Procedures Needed:

• SP_AddUpdateProject: Adds/Updates a project with all the field information.
  • Parameters: projectId, projectName, firmFedID, fundedbudget, projectStartDate, projectStatus, projectTypeCode, projectedEndDate and projectManager
• SP_DeleteProject: Deletes a project by the project Id.
  • Parameters: projectId
• SP_AddUpdateActity: Adds/Updates activity with all the field information.
  • Parameters: activityId, activityName, projectId, costToDate, activityStatus, startDate, endDate
• SP_DeleteActivity: Deletes an activity by the activity Id and projectId.
  • Parameters: projectId, activityId

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 project's projected end date. If this happens, the procedure will calculate how many days it is late (use DATEDIFF) and fines the project $100 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.
  • 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 $3,000 (30 X $100) added to the fundedbudget column's original value. Also, the projects new projected end date will be "7/30/17"

Step 4: Verify Objects

Verify Object names

Once you have completed your work and created the required objects in your database, you will need to run the Project2Verifier to assure that:

• Your objects have been properly created
• Object naming is correct.

Run the Project2Verifier.sql script on the database where you created your objects. If any errors appear, you need to review them before proceeding.