Step 1: Design and create the tables You must create additional tables to hold P

Question

Step 1: Design and create the tables
You must create additional tables to hold Project and Activity Data. You will use normalization to come up with your final
table design.
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 table, you must use the following function dependencies:
ProjectId, ActivityId -> projectName, firmFedID, firmName, firmAddress, fundedbudget, projectstartDate, projectStatus ,
projectTypeCode, projectTypeDesc, projectedEndDate, projectManager, activityName, costToDate, activityStatus,
startDate, endDate.
projectId -> projectName, firmFedID, fundedbudget, projectstartDate, projectStatus , projectTypeCode, projectedEndDate,
projectManager.
projectTypeCode -> projectTypeDesc
firmFedID -> firmName, firmAddress
When creating the tables, use the following column names and data types (important) for columns:
o projectId (char(4)) : A 4 character unique identifier (numbers and letters).
o projectName (varchar(50)) : The name of the construction project.
o firmFedID (char(9)) : A 9 character Federal ID (Example: 123456789)
o firmName (varchar(50)): The name of the construction firm.
o firmAddress (varchar(50)) : The address of the construction firm.
o fundedbudget (decimal(16,2)): The money amount allocated to this project
o projectStartDate (date): The date the project started.
o projectstatus (varchar(25)): The status of the project (either active,inactive,cancelled,completed)
o projectTypeCode (char(5)): The project type code are FAC, RIDE, RET, and FOOD.
o projectTypeDesc (varchar(50)): The project type descriptions for a project are: Facility, Ride, Retail and
Restaurant
o projectedEndDate (date) The date the project is scheduled to end.
o projectManager (char(8)) The employee number of the employee who is managing this project
o activityId (char(4)): A 4 character unique identifier for the activity.
o activityName (varchar(50)): The name of the activity.
o costToDate (decimal(16,2)): The cost of the activity to date.
o activityStatus (varchar(25)) : The status of the activity (either active,inactive,cancelled,completed)
o startDate (date): The date the activity began.
o endDate (date): The date the activity ended.
After you complete your normalization process, you will write the script to create the tables which resulted from your normalization.
Each table should have a primary key defined.
NOTE IMPORTANT!
You should end up with at least:
- a table that will hold the main 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.
If your normalization is done correctly, you will end up with more than two 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.
o Parameters: projectId, projectName, firmFedID, fundedbudget, projectStartDate, projectStatus,
projectTypeCode, projectedEndDate and projectManager
- SP_DeleteProject: Deletes a project by the project Id.
o Parameters: projectId
- SP_AddUpdateActity: Adds/Updates activity with all the field information.
o Parameters: activityId, activityName, projectId, costToDate, activityStatus, startDate, endDate
- SP_DeleteActivity: Deletes an activity by the activity Id and projectId.
o 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 table’s “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 $3,000
(30 X $100) added to the fundedbudget column’s original value. Also, the project’s new projected end date will be
“7/30/17”

Explanation / Answer

create table FirmDetails(
firmFedID char(9) primary key ,
firmName varchar(50),
firmAddress varchar(50)
)

go

CREATE TABLE ProjectMain(
projectId char(4) primary key,
projectName varchar(50),
fundedbudget decimal(16,2),
projectStartDate date,
projectedEndDate date,
projectTypeCode char(5),
statusId char(4),
projectManager char(8),
firmFedID char(9)
)

go

CREATE TABLE ProjectTypeDetails(
projectTypeCode char(5) primary key,
projectTypeDesc varchar(50)
)

go

CREATE TABLE ActivityMain(
activityId char(4) primary key,
projectId char(4),
activityName varchar(50),
costToDate decimal(16,2),
statusId char(4),
startDate date,
endDate date
)

go

CREATE TABLE StatusDescription(
statusId char(4) primary key,
statusDescription varchar(25)

)

go

insert into StatusDescription values ('0001' , 'ACTIVE'),('0002' , 'INACTIVE'),('0003' , 'CANCELLED'),('0004' , 'COMPLETED')

go

/*
Desc: To Add or Edit Project Details
Ececution: EXEC SP_AddUpdateProject (projectId, projectName, firmFedID, fundedbudget, projectStartDate, projectStatus,projectTypeCode, projectedEndDate, projectManager, null)
*/
create procedure SP_AddUpdateProject(
@projectId char(4),
@projectName varchar(50),
@firmFedID CHAR(9),
@fundedbudget DECIMAL(16,2),
@projectStartDate DATE,
@projectStatus CHAR(25),
@projectTypeCode CHAR(5),
@projectedEndDate DATE,
@projectManager CHAR(8),
@ResultCount int output
)
AS

BEGIN
   BEGIN TRY
       -- get the status id
       declare @statusId char(4) = (select statusId from StatusDescription where StatusDescription = @projectStatus)
      
       --Update the details for existing project
       IF EXISTS(select * from ProjectMain where projectId = @projectId)
           BEGIN
               update ProjectMain set projectName = @projectName, projectedEndDate = @projectedEndDate, projectManager = @projectManager,
               projectStartDate = @projectStartDate, projectTypeCode = @projectTypeCode, statusId = @statusId, fundedbudget = @fundedbudget,
               firmFedId = @firmFedID where projectId = @projectId
           END

       -- Insert Details for the new project
       ELSE
           BEGIN
               INSERT INTO ProjectMain(firmFedID, fundedbudget, projectedEndDate, projectId, projectManager, projectName, projectStartDate, projectTypeCode, statusId)
               values
               (@firmFedID, @fundedbudget, @projectedEndDate, @projectId, @projectManager, @projectName, @projectStartDate, @projectTypeCode, @statusId)
           END

       -- To get the number of rows affected
       set @ResultCount = @@ROWCOUNT

   END TRY

   BEGIN CATCH
      print('Error')
   END CATCH

END

go

/*
Desc: To delete the project details
Execution: EXEC SP_DeleteProject(projectid, null)
*/
create procedure SP_DeleteProject(
@projectId char(4),
@ResultCount int output

)
AS

BEGIN
   BEGIN TRY
       -- Delete project related records from ProjectMain Table
       delete from ProjectMain where projectId = @projectId
      
       -- To get the number of rows affected
       set @ResultCount = @@ROWCOUNT
   END TRY

   BEGIN CATCH
      print('Error')
   END CATCH

END

go

/*
Desc: To Add or Edit Activity Details
Ececution: EXEC SP_AddUpdateActity (activityId, activityName, projectId, costToDate, activityStatus, startDate, endDate, null)
*/
create procedure SP_AddUpdateActity(
@activityId char(4),
@activityName varchar(50),
@projectId CHAR(4),
@costToDate DECIMAL(16,2),
@startDate DATE,
@activityStatus CHAR(25),
@projectTypeCode CHAR(5),
@endDate DATE,
@ResultCount int output
)
AS

BEGIN
   BEGIN TRY
       -- get the status id
       declare @statusId char(4) = (select statusId from StatusDescription where StatusDescription = @activityStatus)
      
       --Update the details for existing project
       IF EXISTS(select * from ActivityMain where activityId = @activityId)
           BEGIN
               update ActivityMain set activityName = @activityName, costToDate = @costToDate, projectId = @projectId,
               statusId = @statusId, startDate = @startDate, endDate = @endDate where activityId = @activityId
           END

       -- Insert Details for the new project
       ELSE
           BEGIN
               INSERT INTO ActivityMain(activityId, activityName, projectId, costToDate,endDate,startDate, statusId)
               values
               (@activityId, @activityName, @projectId, @costToDate, @endDate, @startDate, @statusId)
           END

       -- To get the number of rows affected
       set @ResultCount = @@ROWCOUNT

   END TRY

   BEGIN CATCH
      print('Error')
   END CATCH

END

go

/*
Desc: To delete the activity details
Execution: EXEC SP_DeleteActivity(projectid,activityId, null)
*/
create procedure SP_DeleteActivity(
@projectId char(4),
@activityId char(4),
@ResultCount int output

)
AS

BEGIN
   BEGIN TRY

       IF(@activityId is null)
           BEGIN
               -- Delete project related records from ActivityMain Table
               delete from ActivityMain where projectId = @projectId
           END

       ELSE
           BEGIN
               -- Delete project related records from ActivityMain Table
               delete from ActivityMain where activityId = @activityId  
           END
      
       -- To get the number of rows affected
       set @ResultCount = @@ROWCOUNT
   END TRY

   BEGIN CATCH
      print('Error')
   END CATCH

END

go

/*

Desc: To delete the activity details
Execution: EXEC SP_ProcessProjectDelay(projectid, null)

*/
create procedure SP_ProcessProjectDelay(
@projectId char(4),
@ResultCount int output

)
AS

BEGIN
   BEGIN TRY
       declare @maxdate date = (SELECT MAX(enddate) from ActivityMain where projectId = @projectId)
       declare @projectedDate date = (SELECT projectedEndDate from ProjectMain where projectId = @projectId)
       declare @dateDiff int = (SELECT DATEDIFF(day, @maxdate, getdate()))
       declare @revisedBudget decimal(16,2)
      
       -- @dateDiff > 0 means there is a delay
       IF(@dateDiff > 0)
           BEGIN
               set @revisedBudget = ((SELECT fundedbudget from ProjectMain where projectId = @projectId) + (@dateDiff * 100))
               update ProjectMain set fundedbudget = @revisedBudget, projectedEndDate = @maxdate where projectId = @projectId
           END

       -- To get the number of rows affected
       set @ResultCount = @@ROWCOUNT
   END TRY

   BEGIN CATCH
      print('Error')
   END CATCH

END

go

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.