Database Normalization

” Database Normalization ”  – Many of us want to know more about this term  

Question should be why we requires database normalization and how it will be helpful?

When we have large database which may consist number of Data Tables (Relations) with different fields (Attributes), then we must perform some task that will reduce the redundancy between attributes (fields) of those relations (tables). There are some points which we have highlight in context of the Normalization as below,

  • It is used to avoid redundancy and problems arising due to the redundancy of data.
  • It is essential and integral feature of Database Design.
  • It helps in understanding the semantics (logic) of the Database Design.
  • It reduces the need to re-organise the attributes, whenever it’s modified or updated.
  • It helps in identifying functional dependency in Database Design.
    • An attribute (i.e ‘X’) of relation-R is said to be functional dependent on attribute ‘Y’ of the same relation-R, if there exist one & only one value of ‘X’ for a given value of ‘Y’ at any given moment of time.

The normalization process is spread across various phases called as ‘Normal Forms’ as described below:

  • 1 Normal Form (1-NF) the Database design which
  • Contain no repeating groups of data.
  • Eliminates repeating group by putting each group in a Separate Table, give each table a primary key and connecting them with one-to-many

i.e. Unnormalized Data Items for Puppies are:

  • Model number
  • Model name
  • Manufacture code
  • Manufacture name
  • Manufacture location
  • Sensor ID
  • Sensor name
  • Sensor Manufacturer
  • Sensor Level

In the original list of data, each Car model description is followed by a list of Sensors of the model. Some model might have 5 sensors, some might not any. Moving Sensors into a separate table helps considerably. By separating the repeating groups of sensor information from model list results in first normal form. The model number in the sensor table matches the primary key in the model table, providing a foreign key for relating the two tables with a join operation. Now we can answer our question with a direct retrieval look to two different but related tables.

1-NF Form:

  • Model Table
  • Model number   — primary key
  • Model name
  • Manufacture name
  • Manufacture location
  • Sensor Table
  • Model number — foreign Key
  • Sensor ID
  • Sensor name
  • Sensor Manufacturer
  • Sensor level
  • 2 Normal Form (2-NF)
  • A relation-R is in 2-NF if it is already in 1-NF and every other non-key attribute is dependent on the primary key attribute.
  • In 2-NF, functional dependency is identified between the key and non-key attribute.
  • 3 Normal Form (3-NF)
  • In 3-NF, non-key on non-key dependency are identified which is also refer to as ‘Transitive Dependency’.
  • 3-NF required that Database design is already aiming to 2-NF.
  • BCNF (Boyce Code Normal Form)
  • This Normalization form deals with relations which has multiple candidate key, composite key or overtopping candidate keys.
  • BCNF is based on a determinant column on which some other columns are functionally dependent.

i.e     Total, Percentage,

  • 4 Normal Form (4-NF)
  • 4-NF identifies separate independent multi-value facts from one relation into another relation.
  • A relation-R in 4-NF will always be in BCNF.
  • 5 Normal Form (5-NF)
  • 5-NF is also called as ‘Projection Join -NF’ in which every Join dependency in relation-R is implied by the candidate key of relation-R.

Leave a Reply

Your email address will not be published. Required fields are marked *