2nd Normal Form

When an entity is in the 1st Normal Form, it may be examined to see if it can pass the test for 2nd Normal Form, "No Partial Dependencies". If all of the fields in the table depend on all of the elements of the identifier it is in the 2nd Normal Form. This test only applies to tables with compound, non-atomic identifiers such as we see in the EmployeeProject entity.

Within the EmployeeHours entity, Date, ProjectName, TaskName, Hours and TotalHours are repeating groups because they occur many times within the week. Therefore these data need to be normalized out. ProjectId is assigned as a candidate identifier because this is "Project data".

The analyst investigates EmployeeProject because it has a non-atomic identifier and asks the question, "If I place ProjectName in this entity, is it dependent on all of the key elements?" ProjectName is only dependent on ProjectId, so it is normalized out to a new entity, called Project. TaskName is also partially dependent in EmployeeTask. It only dependent on TaskId. It is normalized out to a new table, called the Task table.

The analyst creates the new entity and assigns it a meaningful name. The name of the original entity, EmployeeTimeReport is modified to reflect the data it now contains.

I find that it is impossible to separate normalization and data modeling, so I start sketching out the model as I go along. When I look at that data, I see this model.

Since a new entity was created by the analyst, it must now be examined to see if it is in 1st Normal Form. This process is highly iterative..