Posted: November 11th, 2015
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 🙂
Place an order in 3 easy steps. Takes less than 5 mins.