Step 1: Design and create the tables You must create additional tables to hold P
ID: 3857656 • Letter: S
Question
Step 1: Design and create the tables
You must create additional tables to hold Project and Activity Data.
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 tables, you must use the following function dependencies:
ProjectId,ActivityId -> projectName, firmFedID, firmName, firmAddress, fundedbudget, startDate, projectStatus , projectTypeCode, projectTypeDesc, projectedEndDate, projectManager, activityName, costToDate, activityStatus, startDate, endDate.
projectId -> projectName, firmFedID, fundedbudget, startDate, 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.
You will write the script to create the tables which resulted from your normalization. Each table should have a primary key defined. You should have more than one table after you normalize.
Explanation / Answer
Normalized Tables
1.
Project(projectId ,projectName, firmFedID, fundedbudget, projectStartDate, projectStatus , projectTypeCode, projectedEndDate, projectManager)
2.
Firm(firmFedID, firmName, firmAddress)
3.
ProjectActivity(projectId,activityId , activityName, costToDate, activityStatus, startDate, endDate)
4.
ProjectType(projectTypeCode, projectTypeDesc)
underlined are primary keys and italicised are foreign keys.
sql tables
create table Project
(
projectId char(4) NOT NULL ,
projectName varchar(50) NOT NULL,
firmFedID char(9) NOT NULL ,
fundedbudget decimal(16,2) NOT NULL ,
projectStartDate date NOT NULL ,
projectStatus varchar(25) ,
projectTypeCode char(5) NOT NULL,
projectedEndDate date NOT NULL,
projectManager char(8) NOT NULL,
PRIMARY KEY (projectId),
FOREIGN KEY (firmFedID ) REFERENCES Firm (firmFedID ),
FOREIGN KEY (projectTypeCode ) REFERENCES ProjectType(projectTypeCode)
);
Create table Firm
(
firmFedID char(9) NOT NULL,
firmName varchar(50) NOT NULL,
firmAddress varchar(50) NOT NULL,
PRIMARY KEY (firmFedID)
);
Create table ProjectActivity
(
projectId char(4) NOT NULL,
activityId char(4) NOT NULL ,
activityName varchar(50) NOT NULL,
costToDate decimal(16,2) NOT NULL,
activityStatus varchar(25),
startDate date NOT NULL,
endDate date NOT NULL,
PRIMARY KEY (projectId,activityId),
FOREIGN KEY (projectId ) REFERENCES Project (projectId )
);
Create table ProjectType
(
projectTypeCode char(5) NOT NULL ,
projectTypeDesc varchar(50),
PRIMARY KEY(projectTypeCode)
);
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.