Requirements
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.
o endDate (date): The date the activity ended.
Need help with this assignment or a similar one? Place your order and leave the rest to our experts!