Posted: November 11th, 2015

project Assignment

project Assignment

You are a consulting firm in the bidding process for a Database Implementation

project. Your client, Disney, is creating a new park called StarWars Galaxy and

would like to track schedules and budgeting for the construction of all the

projects (facilities, rides, restaurants, etc.).  The CIO would be evaluating your

bid and has put out the following requirements for the system:
Requirements:
The system will maintain the data for Projects, Activities and Employees.
 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.
The attributes of a project are: Project Id, Project Name, Construction Firm

Federal ID, Construction Firm Name, Construction Firm Address Funded Budget, Start

Date, Status, Project Type Code, Project Type Description, Projected End Date and

Project Manager who is an employee.  A construction firm must be assigned to that

project when the project is created to oversee the project scope.
The project type code and descriptions for a project are: FAC – Facility, RIDE-

Ride, RET – Retail and FOOD- Restaurant, but more project types will be added in

the future.  A project can exist without any activities (for purpose of funding)

but an activity MUST belong to a project.
An activity has the following attributes:  Activity Id, Activity Name, Project Id,

Cost ToDate, Status, Activity Type Code, Activity Type Description, Start Date and

End Date.  The activity types codes and descriptions are:  DE – DESIGN, CO –

CONSTRUCTION, and WA -WARRANTY” and these are the ONLY ones the system allows.Â
An employee for Disney will have Employee Number, First Name, Last Name, SSN,

Address, State, Zip and job. The available jobs are “Cast Member, Engineer,

Inspector, and Project Manager” and an Employee can only have one job.  Employees

with the job of “Project Manager” will manage the projects and a relationship

needs to be enforced.  An employee can potentially manage multiple projects but a

project will only have one project manager.
For both projects and activities, the following are the ONLY applicable statuses:
·Active                        : Has started and is in

progress.
·Inactive          : Has not started.
·Cancelled      : Started but was cancelled.
·On-Hold         : Was originally active but became on hold.
·Completed     : Indicates completion.
COLUMNS / DATA TYPES:
Please use the following names and data types for the columns, regardless of what

table your design places them in:
·PROJECT: projectId  (char(4)) , projectName (varchar(50)), firmFedID (char(9)),

firmName (varchar(50)), firmAddress (varchar(50)), fundedbudget (decimal(16,2)),

startDate (date), status (varchar(25)), projectTypeCode (char(5)), projectTypeDesc

(varchar(50)), projectedEndDate (date)and projectManager (char(8))
·ACTIVITY: activityId (char(4)), activityName (varchar(50)), projectId (char(4)),

costToDate (decimal(16,2)), status (varchar(25)), activityTypeCode (char(2)),

activityTypeDesc (varchar(50)), startDate (date), endDate (date)
·EMPLOYEE: empNumber (char(8)), firstName (varchar(25)), lastName varchar(25)),

ssn (char(9)), address (varchar(50)), state (char(2)), zip (char(5)), job (varchar

(50))
Â
Entity Views: No matter the number of tables that you end up creating as part of

your normalization process, you will create the following views to represent each

entity (Make sure the name and columns specified here are the same):
·vw_Project: projectId, projectName, firmFedID, firmName, firmAddress,

fundedbudget, startDate, status, projectTypeCode, projectTypeDesc, projectedEndDate

and projectManager
·vw_Activity:  activityId, activityName, projectId, costToDate, status,

activityTypeCode, activityTypeDesc, startDate, endDate
·vw_Employee: empNumber, firstName, lastName, ssn, address, state, zip, job.
Several executives would need reports (Views) made and here is the list of the

reports:
·vw_LateProjects: Report any active projects (all project columns) where the

activity’s end date is after the project’s project end date (The project is now

or will be late).
·vw_OverBudget: Report any active projects (project name, summarized activity

cost, funded budget) where the sum of its activities cost are higher than the

project’s funded budget (The project is over budget).
·vw_OverAllocatedPM: Report the First Name, Last Name and SSN of any employees

that manage more than 6 projects.
·vw_CostlyActivity: Report the project id, project name, activity name and cost

for those activities which have been cancelled or on-hold and belong to an active

project.
·vw_CompletedProjects: Report the Project Id, Name, and Type which has an activity

type of “WARRANTY” AND the Activity End Date field is not NULL.
·ww_FundedProjectsNotStarted: Report the Project Id, Project Name and Projected

End Date for those projects that do not have any activities assigned to them.
You will need to write the Scripts to create the tables, as well as insert some

sample data. Make sure the data you insert will allow you to execute the reports.
Create the following stored procedures to drive the Insert, Update, and Delete

Functionality:
·U_DIS_AddProject:                Adds a project with all the field

information.
·Parameters: projectId, projectName, firmFedID, firmName, firmAddress,

fundedbudget, startDate, status, projectTypeCode, projectTypeDesc, projectedEndDate

and projectManager
Â
·U_DIS_DeleteProject:                       Deletes a project

by the project Id.
·Parameters: projectId
Â
·U_DIS_AddActity:                  Adds an activity with all the

field information.
·Parameters: activityId, activityName, projectId, costToDate, status,

activityTypeCode, activityTypeDesc, startDate, endDate
Â
·U_DIS_DeleteActivity:                       Deletes an

activity by the activity Id.
·Parameters: activityId
Â
·U_DIS_AddEmployee:                       Adds an employee

with all the field information.
·Parameters: empNumber, firstName, lastName, ssn, address, state, zip, job.   Â

 Â
          Â
·U_DIS_DeleteEmployee:       Deletes an employee by employee number.
·Parameters:empNumber
Â
·U_DIS_UpdateProject:                      Updates a project

by project Id (can pass all fields).
·Parameters: projectId, projectName, firmFedID, firmName, firmAddress,

fundedbudget, startDate, status, projectTypeCode, projectTypeDesc, projectedEndDate

and projectManager
Â
·U_DIS_UpdateActivity:                      Updates an

activity by activity Id (can pass all fields).
·Parameters: activityId, activityName, projectId, costToDate, status,

activityTypeCode, activityTypeDesc, startDate, endDate
Â
·U_DIS_UpdateEmployee:      Updates an employee by employee number (can pass

all fields).
·Parameters: empNumber, firstName, lastName, ssn, address, state, zip, job.
Â
Test your Stored Procedures with sample parameters.
Please Read this MSDN article on passing optional parameters to your stored

procedures, which you will need to use for your Add/Update Procedures
http://msdn.microsoft.com/en-us/library/ms189330(v=sql.105).aspx
Audit Tables:
The system must log any insertion and deletion of a project AND activity AND

employee into their respective audit tables via TRIGGERS and must capture the data

that got added or deleted, plus the operation (ADD, DELETE), date of operation, and

user who performed operation.
·Trg_ProjectAudit: One for Project to handle Addition, Update and Deletion. Audit

Table name should ProjectAudit.
·Trg_ActivityAudit: One for Activity to handle Addition, Update and Deletion.

Audit Table name should ActivityAudit
·Trg_EmployeeAudit: One for Employee to handle Addition, Update and Deletion.

Audit Table name should EmployeeAudit
Â
Â

PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET A GOOD DISCOUNT 🙂