Answer:
The final tables in 3NF are as follows.
Project( ProjectNbr, ProjectName)
Employee( EmployeeNbr, EmployeeName, JobClass)
Job( JobClass, HourlyRate)
ProjectBilling( ProjectNbr, EmployeeNbr, HoursBilled)
Explanation:
The given table is given below.
ProjectBilling( ProjectNbr, ProjectName, EmployeeNbr, EmployeeName, JobClass, HourlyRate, HoursBilled)
ProjectNbr -> ProjectName
EmployeeNbr -> EmployeeName
JobClass -> HourlyRate
ProjectNbr, EmployeeNbr -> HoursBilled
1NF
1. All the fields in the given table contain only a single value. The table is in 1NF.
2NF
2. New tables are formed based on the given functional dependencies.
Project( ProjectNbr, ProjectName)
Employee( EmployeeNbr, EmployeeName)
Job( JobClass, HourlyRate)
ProjectBilling( ProjectNbr, EmployeeNbr, HoursBilled)
3. Every table is assigned a primary key which are as follows.
ProjectNbr is the primary key for Project table.
EmployeeNbr is the primary key for Project table.
JobClass is the primary key for Project table.
(ProjectNbr, EmployeeNbr) is the composite primary key for the ProjectBilling table.
4. The tables which are related to each other are linked via primary key and foreign key.
5. In the ProjectBilling table, the composite primary key, ProjectNbr, EmployeeNbr is composed of the primary keys of the Project and Employee tables, i.e., ProjectNbr and EmployeeNbr respectively.
6. Job table is related to Employee table. Hence, primary key of Job table, JobClass, is introduced as foreign key in Employee table.
Employee( EmployeeNbr, EmployeeName, JobClass)
7. Partial dependency arises when composite primary key exists and non-prime attributes (columns other than the primary key) depend on a part of the primary key, i.e., partial primary key.
In the ProjectBilling table, no partial dependency exists.
8. All the tables are in 2NF as given below.
Project( ProjectNbr, ProjectName)
Employee( EmployeeNbr, EmployeeName, JobClass)
Job( JobClass, HourlyRate)
ProjectBilling( ProjectNbr, EmployeeNbr, HoursBilled)
3NF
9. All the tables are in 2NF.
10. In every table, all non-prime attribute depend only on the primary key.
11. No transitive dependency exists, i.e., all non-prime attributes do not depend on other non-prime attributes.
12. Hence, all the conditions are satisfied and the tables are in 3NF.
