ZCubes Experience

Please Wait....
Your Experience is being rendered.
Click the [Live] button if the Experience does not load in few moments.

















Database Normalization


-Priyanka
History

  • Edgar F. Codd first proposed the process of normalization and what came to be known as the 1st normal form
Definition

  • Normalization , is the process of organizing data to minimize redundancy in design of relational design

 

  • Usually involves dividing a database into two or more tables and defining relationships between the tables
Goals

  • Eliminating redundant data (for example, storing the same data in more than one table) .

 

  • Ensuring data dependencies make sense (only storing related data in a table).

 

  • Both the goals reduce the amount of space a database consumes and ensure that data is logically stored

 

 

 

Problems

  • Update Anomaly

http://upload.wikimedia.org/wikipedia/en/1/12/Update_anomaly.png
Problems
  • Update Anomaly -Employee 519 is shown as having different addresses on different records.

http://upload.wikimedia.org/wikipedia/en/1/12/Update_anomaly.png
Problems
  • An insertion anomaly -Until the new faculty member is assigned to teach at least one course, his details cannot be recorded.

http://upload.wikimedia.org/wikipedia/en/2/25/Insertion_anomaly.png
Problems
  • A deletion anomaly. All information about Dr. Giddens is lost when he temporarily ceases to be assigned to any courses.

http://upload.wikimedia.org/wikipedia/en/9/99/Deletion_anomaly.png
Normal Forms

  • The database community has developed a series of guidelines for ensuring that databases are normalized .

 

  • Numbered from one (the lowest form of normalization, referred to as first normal form or 1NF) through five (fifth normal form or 5NF).
First Normal Form (1NF)

  • First normal form (1NF) sets the very basic rules for an organized database:

 

    • Eliminate duplicative columns from the same table.

 

    • Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).
Second Normal Form (2NF)

  • Second normal form (2NF) further addresses the concept of removing duplicative data:

 

    • Meet all the requirements of the first normal form.
    • Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
    • Create relationships between these new tables and their predecessors through the use of foreign keys.


Third Normal Form (3NF)

  • Third normal form (3NF) goes one large step further:

 

    • Meet all the requirements of the second normal form.

 

    • Remove columns that are not dependent upon the primary key.
Fourth Normal Form (4NF)

  • Finally, fourth normal form (4NF) has one additional requirement:

 

    • Meet all the requirements of the third normal form.

 

    •  relation is in 4NF if it has no multi-valued dependencies.
Summary

  • Process of efficiently organizing data in a database.