An employee is assigned to one department and a department m

An employee is assigned to one department and a department may have many employees assigned to it. An employee may work on many projects and each project an employee works on the date and hours worked is recorded. A project is assigned a name, budget, start date and completion date. An employee can be assigned to a project on more than one occasion.

Solution

Arelation is in 2nd normal form if all non key attributes are fully functionally dependent on primary key.

A relation is in 3rd normal if it is in 2nd normal form and no non key attribute is transitively dependent on the primary key.

The relation (EmployNo,EmpName,DepartNo.DepartName,projectNo,Projectname,ProjectStartDate,ProjectEndDate,Budget,EmpDateWorked,EmpHoursWorked)

is not in 2nd normal form and thus not in 3rd normal form

functional dependencies

EmployNo --------> EmpName

DepartNo --------> DepartName

ProjectNo ---------> ProjectName

ProjectNo ---------> ProjectStartDate

ProjectNo ---------> ProjectEndDate

ProjectNo ---------> Budget

ProjectNo ---------> EmpDateWorked

ProjectNo ---------> EmpHoursWorked

based on these functional dependencies we can make three tables

EMPLOYEE (EmployNo,EmpName)

DEPARTMENT(DepartNo,DepartName)

PROJECT(ProjectNo,ProjectName,ProjectStartDate,ProjectEndDate,Budget,EmpDateWorked,EmpHoursWorked)

There is no transitive dependency in these tables and all non key attributes are fully functionally dependent on primary keys(underlined ). So the tables are in 3rd Normal Form.