Database Normalization

This article is a concise overview of normalization: what it is, why it's done, its pros and cons, its benefits and costs, the normalization process, and 2NF to 3NF transformation.

Definition

Normalization is a logical database 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 reduces data to its canonical form.

 

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 when:

  • 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.

Improve:

  • Software Design - Maintainability
  • Reusability
  • Readability

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 a database application in order to perform proper model of a 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.


Source: Gerard Nico, https://gerardnico.com/data/modeling/normalization?404id=data_modeling%3Anormalization
Creative Commons License This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 License.

Last modified: Thursday, December 17, 2020, 4:45 PM