Normalization and Normal Forms Example

Example Table "company", not normalized:

Company
Attribute
Name John Walker Jim Bim
Pers.ID 1 1
Dept. No. 1 2
Dept. Name Europe USA
Project No. 5, 7, 8 5, 8
Project Name Soap, Pasta, OliveOil Soap, OliveOil
Job in Project Analysis, Leader, Marketing Leader, Leader
Salary/h 13, 18, 15 18, 18

First NF (1NF): A relation cannot have repeating fields or groups (no field must have more than one value):

Employees
Attribute
Name John Walker Jim Bim
Pers. ID 1 1
Dept. No. 1 2
Dept. Name Europe USA

Projects
Attribute
Project No. 578
Project Name SoapPastaOliveOil

Project Assignment
Attribute
Project No. 55788
Pers. ID 11111
Dept. No. 12112
Job in Project AnalysisLeaderLeaderMarketingLeader
Salary/h 1318181518

Second NF (2NF): 1NF + every non-key field must be functionally dependent on all of the key.

Here: In the relation "Employees" the "Dept. Name" depends only on the "Dept. No." but not on "Pers. ID", thus not on all parts of the key:

Employees
Attribute
Name John Walker Jim Bim
Pers. ID 1 1
Dept. No. 1 2
Departments
Attribute
Dept. No. 1 2
Dept. Name Europe USA

The relations "Projects" and "Project Assignment" can remain as they are:

Projects
Attribute
Project No. 578
Project Name SoapPastaOliveOil

Project Assignment
Attribute
Project No. 55788
Pers. ID 11111
Dept. No. 12112
Job in Project AnalysisLeaderLeaderMarketingLeader
Salary/h 1318181518

Third NF (3NF): There must be no transitive dependency, that is an attribute depends on one or more other non-key attributes.

Here: The relations "Employees", "Depends" and "Projects" can remain as they are.
But in "Projects Assignment" the salary depend only on the job, thus only indirectly on the key. This is a transitive dependency. We have to add a "Payment" relation.

Employees
Attribute
Name John Walker Jim Bim
Pers. ID 1 1
Dept. No. 1 2
Departments
Attribute
Dept. No. 1 2
Dept. Name Europe USA

Projects
Attribute
Project No. 578
Project Name SoapPastaOliveOil
Payment
Attribute
Job AnalysisMarketingLeader
Salary/h 131518

Project Assignment
Attribute
Project No. 55788
Pers. ID 11111
Dept. No. 12112
Job in Project AnalysisLeaderLeaderMarketingLeader

Note: We could add an"Assignment No." in the "Project Assignment" relation as new key to avoid having only key attributes.

Now the DB is in 3NF: No Data-field modification should necessitate any other "follow-up" adjustment in any other data-field, because of transitive dependencies. Vale?