Funded Budget

Continuing on the theme park concept, 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. You will use normalization to come up with your final

table design.

IMPORTANT: For Project 2, DO NOT CREATE ANY FOREIGN KEYS FOR ANY OF THE TABLES

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 Fett’s 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 table, you must use the following function dependencies:

ProjectId, ActivityId -> projectName, firmFedID, firmName, firmAddress, fundedbudget, projectstartDate, projectStatus ,

projectTypeCode, projectTypeDesc, projectedEndDate, projectManager, activityName, costToDate, activityStatus,

startDate, endDate.

projectId -> projectName, firmFedID, fundedbudget, projectstartDate, 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.

Do you need help with this assignment? Or a different one? We got you covered.

Quality Guaranteed

Any Deadline

No Plagiarism