Data Modeling - Database Normalization

Definition

Normalization is a logical data base design method. Normalization is a process of systematically breaking a complex table into simpler ones. It is built around the concept of normal forms.

Database normalization is a data normalization process that reduce data to its canonical form.

Articles Related

Purposes of Normalization

In the design of a data model, normalization is the process of adjusting table and relations to:

  • eliminate certain types of data (redundancy|replication) to improve consistency,
  • produce a clearer and readable data model.
  • provide maximum flexibility to meet future information needs by keeping tables corresponding to object types in their simplified forms.
  • avoid update anomalies
An update anomaly is a problem with:
  • inserting (no place to insert new information),
  • deleting (lost of information),
  • or updating (inconsistency may occur because of the existence of data redundancy)

a database because of the structure of the relations.

Normalization: Pros and Cons

Pros

  • Reduce data redundancy & space required
  • Enhance data consistency
  • Enforce data integrity
  • Reduce update cost
  • Provide maximum flexibility in responding ad hoc queries
  • Allow the use of parallelism,
  • Can reduce the total number of rows per block.

Cons

  • Many complex queries will be slower because joins have to be performed to retrieve relevant data from several normalized tables
  • Programmers/users have to understand the underlying data model of an database application in order to perform proper model of an database application in order to perform proper joins among several tables
  • The formulation of multiple-level queries is a non-trivial task.

Normalization steps

3NF/2NF Normalization Example

An example of a 2NF table that fails to meet the requirements of 3NF is:

Tournament Winners Tournament

Tournament Year Winner Date of Birth
Indiana Invitational 1998 Al Fredrickson 21 July 1975
Cleveland Open 1999 Bob Albertson 28 September 1968
Des Moines Masters 1999 Al Fredrickson 21 July 1975
Indiana Invitational 1999 Chip Masterson 14 March 1977

Because each row in the table needs to tell us who won a particular Tournament in a particular Year, the composite key {Tournament, Year} is a minimal set of attributes guaranteed to uniquely identify a row. That is, {Tournament, Year} is a candidate key for the table.

The breach of 3NF occurs because the non-prime attribute Winner Date of Birth is transitively dependent on the candidate key {Tournament, Year} via the non-prime attribute Winner. The fact that Winner Date of Birth is functionally dependent on Winner makes the table vulnerable to logical inconsistencies, as there is nothing to stop the same person from being shown with different dates of birth on different records.

In order to express the same facts without violating 3NF, it is necessary to split the table into two:

Tournament Winners

Tournament Year Winner
Indiana Invitational 1998 Al Fredrickson
Cleveland Open 1999 Bob Albertson
Des Moines Masters 1999 Al Fredrickson
Indiana Invitational 1999 Chip Masterson

Player Dates of Birth ^ Player ^ Date of Birth ^

Chip Masterson 14 March 1977
Al Fredrickson 21 July 1975
Bob Albertson 28 September 1968

Update anomalies cannot occur in these tables, which are both in 3NF.

Reference

  • Bookmark "Data Modeling - Database Normalization" at del.icio.us
  • Bookmark "Data Modeling - Database Normalization" at Digg
  • Bookmark "Data Modeling - Database Normalization" at Ask
  • Bookmark "Data Modeling - Database Normalization" at Google
  • Bookmark "Data Modeling - Database Normalization" at StumbleUpon
  • Bookmark "Data Modeling - Database Normalization" at Technorati
  • Bookmark "Data Modeling - Database Normalization" at Live Bookmarks
  • Bookmark "Data Modeling - Database Normalization" at Yahoo! Myweb
  • Bookmark "Data Modeling - Database Normalization" at Facebook
  • Bookmark "Data Modeling - Database Normalization" at Yahoo! Bookmarks
  • Bookmark "Data Modeling - Database Normalization" at Twitter
  • Bookmark "Data Modeling - Database Normalization" at myAOL
 
data_modeling/normalization.txt · Last modified: 2014/08/28 12:41 by gerardnico