Question

The following table contains data about projects and the hours charged against them:

ProjectBilling:
ProjectNbr ProjectName EmployeeNbr EmployeeName JobClass HourlyRate HoursBilled
15 Evergreen 103 June Phillips SA-3 96.75 23.8
15 Evergreen 101 John Baker DD-5 105.00 19.4
15 Evergreen 105 Alice Miller DD-5 105.00 35.7
15 Evergreen 106 William Smithfield SA-2 62.50 12.6
18 Amber Wave 114 Anna Jones SA-1 55.00 23.8
18 Amber Wave 101 John Baker DD-5 105.00 24.6
18 Amber Wave 112 Brad White SA-3 96.75 45.3
22 Starlight 107 Terry Ray SA-2 62.50 32.4
22 Starlight 115 Peter Novak SA-3 96.75 44.0
22 Starlight 101 John Baker DD-5 105.00 64.7
22 Starlight 114 Anna Jones SA-1 55.00 48.4
22 Starlight 108 Susan Brown SA-2 62.50 23.6
(A) Convert the table to 3NF.

Answer 1

The final tables in 3NF are as follows.

Project( ProjectNbr, ProjectName)

Employee( EmployeeNbr, EmployeeName, JobClass)

Job( JobClass, HourlyRate)

ProjectBilling( ProjectNbr, EmployeeNbr, HoursBilled)

Step-by-step 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.