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 Fetts 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.

After you complete your normalization process, you will write the script to create the tables which resulted from your normalization. Each table should have a primary key defined.

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. If your normalization is done correctly, you will end up with more than two tables.