You are 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.). The CIO would be evaluating your bid and has put out the following requirements for the system:
The system will maintain the data for Projects, Activities and Employees.
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, Status, 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.
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, Status, Activity Type Code, Activity Type Description, Start Date and End Date. The activity types codes and descriptions are: DE – DESIGN, CO – CONSTRUCTION, and WA -WARRANTY” and these are the ONLY ones the system allows.
An employee for Disney will have Employee Number, First Name, Last Name, SSN, Address, State, Zip and job. The available jobs are “Cast Member, Engineer, Inspector, and Project Manager” and an Employee can only have one job. Employees with the job of “Project Manager” will manage the projects and a relationship needs to be enforced. An employee can potentially manage multiple projects but a project will only have one project manager.