Project 2

50 points

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.

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 Fetts 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:

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.

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 will hold the main project data and will have projectId and projectName, along with other related fields. Please name this

table, ProjectMain.

- a table will hold the main activity data and will have activityId and activityName, along with other related fields. Please name this

table, ActivityMain

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 DOESNT exist in the project table(s), it will insert the values

passed in.

If SP_AddUpdateProject: passes in projectID AA01 and it DOES exist 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 projects 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.

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 columns original value. Also, the projects new projected end date will be

7/30/17

How you will turn in your project

You will turn in 1 SQL Script file, which will have the filename format First Four letters of last name + underscore + First

Name Initial + PantherId + PROJ2.

So if your name is John Smith, ID 166723 your filename will be

Smit_J166723_PROJ2.sql

(If your last name is less than 5 characters then just use those characters)

Header and Database:

You will have a header on the file and create all the SQL objects under your own database

/* Name: FirstName Last Name

Project #

PantherId: ######

Semester:

*/

--All your project will create the objects under your own personal database that you will have to

create manually.

--Your personal database should have the following name format:

--First Four letters of last name + underscore + First Name Initial + PantherId

Use Smit_J166723

GO

Then you will need to have an insert statement to an assignment table that I will use for grading by

populating this script with your pantherId, firstname, lastname, databasename (see above) and

assignment (1, 2 or 3)

/***This must be created for every assignment and must be done at the beginning) ****/

insert into master.dbo.assignments

(pantherId, firstname, lastname, databasename, assignment)

values

('7777777', 'John', 'Smith','Smit_J7777777',2)

GO

/*******************************************************************************/

--Rest of Project Scripts HERE

Grading Criteria:

- Proper Normalization of Project into multiple tables with create scripts. (20 points)

- Completed Project AddUpdate Procedure (5 points)

- Completed Activity AddUpdate Procedure (5 points)

- Completed DeleteProject Procedure (5 points)

- Completed DeleteActivity Procedure (5 points)

- Completed ProcessProjectDelay Procedure (10 points)

I will run all your scripts on a database so please make sure there are no errors and everything runs smoothly on a

brand-new database.

_________________________________________________________________________________________________________

Code is being built on top of :

--Example of Project 1 Solutions

--Step 1 Create the tables

--Create the Employee table

CREATE TABLE Employee

(

empNumber char(8) not null,

firstName varchar(25),

lastName varchar(25),

ssn char(9),

address varchar(50),

state char(2),

zip char(5),

jobCode char(4),

dateOfBirth date,

certificate bit,

salary money

)

GO

--Create the Job table

CREATE TABLE Job

(

jobCode char(4) not null,

jobDesc varchar(50),

)

GO

--Add primary key on the empNumber column in the Employee table.

ALTER TABLE Employee

ADD PRIMARY KEY (empNumber)

GO

--Add primary key on the jobCode column in the Job table.

ALTER TABLE Job

ADD PRIMARY KEY (jobCode)

GO

--The foreign key will be placed on the Employee table, on the jobcode column

--(which should be of data type char(4)).

--Creating a Foreign Keys through an Alter statement

--create a foreign key constraint named FK_JOB on the Employe tables jobCode column

--which upholds referential integrity to the Job tables primary key.

ALTER TABLE Employee

ADD CONSTRAINT fk_job

FOREIGN KEY (jobCode)

REFERENCES Job (jobCode)

GO

--A Legal Value constraint on the Employee table named EMP_STATECHECK

--on the state column which can only be in either CA or FL.

ALTER TABLE Employee

ADD CONSTRAINT EMP_STATECHECK

check (state in ('CA','FL') )

GO

--A Legal Value constraint on the Job table named JOB_JOBCODE

--on the job column which only have one of the values

--CAST, ENGI, INSP or PMGR

ALTER TABLE Job

ADD CONSTRAINT JOB_JOBCODE

CHECK (jobCode IN ('CAST', 'ENGI', 'INSP', 'PMGR'))

GO

-- Step 2 Insert the Data

--Write the Insert statements to populate the following

--available jobs codes and job descriptions:

INSERT INTO Job

VALUES

('CAST','Cast Member'),

('ENGI','Engineer'),

('INSP','Inspector'),

('PMGR','Project Manager')

GO

--Write the Insert statements to populate 3 sample employees.

--Make sure your data doesnt violate any constraints.

INSERT INTO Employee

VALUES

(00000001,'Tom','Jones','123456789', '100 Augusta Drive', 'CA', '33011', 'ENGI', '05/10/1977',1,50000),

(00000002, 'Jake','Perez','987654321', '200 Elm Street', 'FL', '33111', 'INSP', '04/15/1980',0, 55000),

(00000003, 'Don','Johnson','786622321', '300 Coral Lane', 'FL', '31021', 'CAST', '01/31/1976',0,70000)

GO

--Step 3 Create the views

--Create a view call vw_CertifiedEnginers:

--This View will show the empNumber, firstName, lastName and jobDesc

--of the employess who are engineers and have a certification value of 1.

CREATE VIEW vw_CertifiedEngineers

AS

(

SELECT empNumber, firstName, lastName, jobDesc

FROM Employee inner join job

on Employee.jobCode = job.jobcode

WHERE certificate = '1' AND Employee.jobCode = 'ENGI'

)

GO

--Create a view call vw_ReadyToRetire:

--This View will show the empNumber, firstName and lastName

--of those employees who are over 62

--( Hint: use the birthdate year to calculate their age based on the current date year)

--2017-1955=62

CREATE VIEW vw_ReadyToRetire

AS

(

SELECT empNumber, firstName, lastName

FROM Employee

WHERE datediff(year,dateOfBirth,getdate()) > 62

)

GO

--Create a view call vw_EmployeeAvgSalary:

--This view will show the average salary and the employee jobcode

--grouped per the different job codes.

CREATE VIEW vw_EmployeeAvgSalary

AS

(

--Get the average salary using the avg function

SELECT jobCode, AVG(salary)

AS avgSalary

FROM Employee

GROUP BY jobCode

)

GO

--CreateSQL Scripts for indexes on the following following columns:

--Index name: IDX_LastName Column: lastName

CREATE INDEX IDX_LastName

ON Employee (lastname)

GO

--Index name: IDX_ssn Column: ssn

CREATE INDEX IDX_ssn

ON Employee (ssn)

GO