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
CREATE TABLE EMPLOYEE(
id int auto_increment primary key,
empNumber char(8) unique,
firstName varchar(25),
lastName varchar(25),
ssn varchar(9),
address varchar(50),
state char(2),
zip char(5),
job varchar(50));
CREATE TABLE PROJECT_TYPE_CODE(
id int not null auto_increment primary key,
projectTypeCode varchar(50) not null,
description varchar(100) not null);
CREATE TABLE ACTIVITY_TYPE_CODE(
id int not null auto_increment primary key,
activityTypeCode varchar(50) not null,
description varchar(100) not null);
CREATE TABLE AVAILABLE_JOBS (
id int not null auto_increment primary key,
job varchar(50) not null);
CREATE TABLE STATUS(
id int not null auto_increment primary key,
status varchar(50) not null);
CREATE TABLE PROJECT(
id int auto_increment not null primary key,
projectId char(4) unique,
projectName varchar(50) not null,
firmFedID char(9) not null,
firmName varchar(50),
firmAddress varchar(50),
fundedbudget decimal(16,2),
startDate (date),
status varchar(25),
projectTypeCode varchar(5),
projectTypeDesc varchar(50),
projectedEndDate (date),
projectManager varchar(8),
foreign key(projectManager) references EMPLOYEE(empNumber),
foreign key(projectTypeCode) references PROJECT_TYPE_CODE(projectTypeCode)
foreign key(status) references STATUS(status));
CREATE TABLE ACTIVITY(
id int auto_increment not null,
activityId char(4) primary key,
activityName varchar(50) not null,
projectId char(4) not null,
costToDate decimal(16,2),
status varchar(25),
activityTypeCode char(2),
activityTypeDesc varchar(50),
startDate (date),
endDate (date),
index activityIndex(activityId),
foreign key(activityId) references PROJECT(projectId) on delete cascade,
foreign key(projectId) references PROJECT(projectId),
foreign key(status) references STATUS(status));
–Insert data
insert into
PROJECT_TYPE_CODE
(projectTypeCode, description)
values
(‘FAC’,’Facility’),
(‘RIDE’,’Ride’),
(‘RET’,’Retail’),
(‘FOOD’,’RESTAURANT’);
— INSERT INTO ACTIVITY TYPE CODE TABLE
insert into
ACTIVITY_TYPE_CODE
(activityTypeCode, description)
values
(‘DE’,’Design’),
(‘CO’,’Construction’),
(‘WA’,’Warranty’),
(‘FOOD’,’RESTAURANT’);
— INSERT INTO AVAILABLE JOBS TABLE
insert into
AVAILABLE_JOBS
(job)
values
(‘Cast Member’),
(‘Engineer’),
(‘Inspector’),
(‘Project Manager’);
— INSERT INTO STATUSES TABLE
insert into
STATUS
(status)
values
(‘Active’),
(‘Inactive’),
(‘Cancelled’),
(‘On-hold’),
(‘Completed’);