Normalization

Normalization

The following are the characteristic features of Normalized tables.

·        No data should be duplicated in different rows unnecessarily.

·        The intersection of every row and every column should contain some entry.

·        If a row is added to a table, then other tables in the database must be unaffected.

·        If a row is deleted from a table, then important information should not be lost.

·        Any row of a table can be updated independent of other rows in the table.

First Normal Form (1 NF)

·        A table is said to be in First normal form provided the intersection of every row and column contain only atomic values.

Issues with 1 NF

·        Insertion:A subset of a tuple can’t be inserted.

·        Deletion: information may be lost in the process.

·        Updation:May have to be repeated for the same piece of information.

Second Normal Form (2 NF)

·        A Table is said to be in second normal form provided it is in first normal form and every non-key column is fully functionally dependent on candidate key.

·        In order to reduce a table to 2 NF create a separate table containing the column that is partially dependent on the candidate key and that subset of candidate key on which it depends.

Problem with 2 NF

·        Since a table in 2 NF may have transitive dependencies, it may contain two or more non-key columns that are functionally dependent. This will result in unnecessary duplication of data.

Third Normal Form (3 NF)

·        A Table is said to be in third normal form provided it is in second normal form and every non-key column is non-transitively dependent on candidate key.

·        In order to reduce a table to 3 NF create a separate table containing the columns that are functionally dependent.

Limitations of Normalization

·        Full normalization may not be always desirable.

·        Use your Experience.