What is the need for normalization of the database

Databases normalization

If you are dealing with databases you will sooner or later come across the topic of database normalization.

Let's take a look at this today.

 

Reasons for database normalization

  • Avoid unwanted dependencies in the event of anomalies
  • Reduce the need to reorganize relations when new types of data are introduced
  • Extension of the lifespan of databases
  • Understandable data model for users and programmers
  • Elimination of redundancies
  • Database designers are forced to deal systematically and intensively with the data

When normalizing a relational schema, the attributes are distributed over relations in such a way that no inconsistencies occur when data records are inserted, deleted or changed.

The three normal forms according to Codd

First normal form

In the first normal form, only atomic (not further decomposable) attributes may be stored in each relation.

Second normal form

A relation is in the second normal form if it is in the first normal form and every non-key attribute of a table is functionally dependent on the key of this table.

However, the second normal form cannot prevent a relation from containing attributes that only depend indirectly on the key and therefore conceptually belong in a separate table

Third normal form

A relation is in the third normal form if it is in the first and second normal form and no non-key attribute depends transitively on the key.

Every relation of the normal forms that has only a single non-key attribute is already in the third normal form.

Important instructions

The higher the normal form, the more relations (tables) are available

The higher the normal form, the less memory is required

The higher the normal form, the lower the maintenance effort, since only one entry in a relation needs to be changed.

But the higher the normal form, the worse the performance of a database can be, since joins have to be executed over and over again.

Denormalization

In practice, an optimum has to be found between access time, maintenance effort and storage requirements.

Therefore it can be advisable not to carry out the transition from the second to the third normal form in the first place (denomralization)

Category: DatabasesTags: Codd, Database, Database, Denormalization, Normalization