COP 4703 Group Project

Overall 125 Points

Your group is 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.). and employees. The CIO would be evaluating your bid and has put out the following requirements for the system:

Phase 2: 65 points(Due End of Semester)

Step 1 Create Tables

You will create the tables the following entities “Project” and “Activity”

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, projectStatus, 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.

Example Project Name: Bobba Fett’s Bounty Chase Ride

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, Activity Status, Activity Type Code, Activity Type Description, Start Date and End Date. The same Activity Id can be reused under multiple projects. The activity types codes and descriptions are: DE - DESIGN, CO - CONSTRUCTION, and WA -WARRANTY” and these are the ONLY ones the system allows. Note: An Activity ID must be unique only if its in the same project.

Example Activity Name:

For Type of Design, activity name could be “Phase 1 Design of Bounty Chase ride”

For Type of Construction, activity name could be “Final construction of Bounty Chase ride”

Etc…

For both projects and activities status, 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), projectstatus (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)), activityStatus (varchar(25)), activityTypeCode (char(2)), activityTypeDesc (varchar(50)), startDate (date), endDate (date)

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, activityTypeCode, activityTypeDesc, startDate, endDate.

projectId -> projectName, firmFedID, fundedbudget, startDate, projectStatus , projectTypeCode, projectTypeDesc, projectedEndDate, projectManager.

projectTypeCode -> projectTypeDesc

firmFedID -> firmName, firmAddress

activityTypeCode -> activityTypeDesc

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

Step 2 Stored Procedures

Create the following stored procedures to drive the Insert, Update, and Delete Functionality:

-U_DIS_AddUpdateProject: Adds/Updates a project with all the field information.

  • Parameters: projectId, projectName, firmFedID, firmName, firmAddress, fundedbudget, startDate, projectStatus, projectTypeCode, projectTypeDesc, projectedEndDate and projectManager

-U_DIS_DeleteProject: Deletes a project by the project Id.

  • Parameters: projectId

-U_DIS_AddUpdateActity: Adds/Updates an activity with all the field information.

  • Parameters: activityId, activityName, projectId, costToDate, activitystatus, activityTypeCode, activityTypeDesc, startDate, endDate

-U_DIS_DeleteActivity: Deletes an activity by the activity Id and project Id.

  • Parameters: projectId,activityId

-U_DIS_ProcessProjectDelay:Given a project Id, this procedure finds if any max end date of any activity within the project is after the project’s projectedend ended date. If this happens, the sp calculates how many days it is late (use DATEDIFF) and fines the project $100 for each day late it is late. The project table’s “projectededenddate” will be updated with the new end date and the “projectedfundedbudget ” will be updated with the original funded budget plus the fines per day late.

  • Parameters: projectId.

Step 3 Create Triggers

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 from the main project table (the table that contains the most fields). Audit Table name should ProjectAudit.

-Trg_ActivityAudit: One for Activity to handle Addition, Update and Deletion from the main activity table (the table that contains the most fields). Audit Table name should ActivityAudit

Entity Relationship Diagram and Presentation

Once all your tables have been created you will create an Entity Relationship Diagram for all your tables and relationships. In addition, your team will create a presentation and present to the class your project solution at the end of the semester.

Phase 2 Deliverables:

The Phase 2 Deliverables will include all the work that was done on phase 1.The following files will be turned in by each group:

  1. Tables.sql
  2. Data Inserts.sql
  3. Views.sql
  4. Triggers.sql
  5. StoredProcedures.sql

Grading Criteria:

  • Normalization and Table Creation(10 pts)
  • Triggers(10 pts)
  • Add/Update Procedures (10 pts)
  • ProcessDelay Procedure(10 pts)
  • ERD(15 pts)
  • Presentation(10 pts)