Database Normalization: 1NF, 2NF & 3NF Explained

Normalization: 1NF, 2NF, 3NF

First Normal Form (1NF)

1) 1NF, 2NF, 3NF: Defined to disallow multivalued attributes, composite attributes, and their combinations. It states that the domain of an attribute must include only atomic (simple, indivisible) values and that the value of any attribute in a tuple must be a single value from the domain of that attribute.

1NF disallows relations within relations or relations as attribute values within tuples. The only attribute values permitted by 1NF are single atomic (indivisible) values.

Consider the DEPARTMENT relation schema shown in the figure below:

There are two main techniques to achieve first normal form for such a relation:

1. Remove the multivalued attribute: Remove the attribute Dlocations that violates 1NF and place it in a separate relation DEPT_LOCATIONS along with the primary key Dnumber of DEPARTMENT. The primary key of this relation is the combination {Dnumber, Dlocation}. A distinct tuple in DEPT_LOCATIONS exists for each location of a department. This decomposes the non-1NF relation into two 1NF relations.
2. Expand the key: Expand the key so that there will be a separate tuple in the original DEPARTMENT relation for each location of a department. In this case, the primary key becomes the combination {Dnumber, Dlocation}. This solution has the disadvantage of introducing redundancy in the relation.

Second Normal Form (2NF)

Second normal form (2NF) is based on the concept of full functional dependency. A dependency X → A is a full functional dependency if removal of any attribute A from X means that the dependency no longer holds.

Definition: A relation schema R is in 2NF if every nonprime attribute A in R is fully functionally dependent on the primary key of R. The test for 2NF involves testing for functional dependencies whose left-hand side attributes are part of the primary key.

If a relation schema is not in 2NF, it can be second-normalized (2NF normalized) into a number of 2NF relations in which nonprime attributes are associated only with the part of the primary key on which they are fully functionally dependent.

Full Functional Dependency

A full functional dependency means that a nonprime attribute depends on the whole primary key, not just a part of it.

Third Normal Form (3NF) and Transitive Dependency

Transitive functional dependency: A functional dependency X → Y in a relation schema R is a transitive dependency if there exists a set of attributes Z that are neither a primary key nor a subset of any candidate key of R, and both X → Z and Z → Y hold.

Definition: A relation schema R is in third normal form (3NF) if it is in 2NF and no nonprime attribute A in R is transitively dependent on the primary key.

EMP_DEPT Example and Implications

The relation schema EMP_DEPT is in 2NF since no partial dependencies on a key exist. However, EMP_DEPT is not in 3NF because of the transitive dependency of Dmgr_ssn (and also Dname) on Ssn via Dnumber.

ED1 and ED2 represent independent entity facts about employees and departments. A NATURAL JOIN operation on ED1 and ED2 will recover the original relation EMP_DEPT without generating spurious tuples.

Problematic Functional Dependencies

• Left-hand side is part of the primary key.
• Left-hand side is a non-key attribute.
• Takes into account all candidate keys of a relation.

Definition of 2NF (restated): A relation schema R is in second normal form (2NF) if every nonprime attribute A in R is not partially dependent on any key of R.

Consider the relation schema LOTS, which describes parcels of land for sale in various counties of a state.

Assertion & Trigger in SQL

2) Assertion & Trigger in SQL:

This section title indicates that assertions and triggers in SQL are the next topic. (The original document ends here; include this heading and marker so the content sequence remains intact.)