Database Normalization | DBMS

A database is an organized collection of data whose content must be quickly and easily accessed, managed and updated.

A relational database is one whose data are split up into tables, sometimes called relations.

What is normalization ?

Normalization is set of rules which :-

1. Cures the ‘SpreadSheet Syndrome’.
2. Store only the minimal amount of information.
3. Remove redundancies.
4. Remove anomalies.
5. Restructure data.

What are normalization rules?

1. Prevent update anomalies (mistakes) and data inconsistencies.
2. Degrade performance, usually only slightly.
3. Denormalization is common on read-only databases and in report generation or data warehouses.
4. Normalizing the data model is a technical exercise, done in a back room; it does not change the business rules.

There are a few rules for database normalization. Each rule is called a “normal form.”

Types of normal form.

1. First Normal Form (1NF): All occurrences of an entity must contain the same number of attributes.
2. Second Normal Form (2NF): All non-key fields must be a function of the key.
3. Third Normal Form (3NF): All non-key fields must not be a function of other non-key fields.
4. Forth Normal Form (BCNF): A row must not contain two or more independent multi-valued facts about an entity.
5. Fifth Normal Form (5NF): A record cannot be reconstructed from several smaller record types.

The Zero Form:

Table in zero normal form.

1. No rules of normalization have been applied.
2. Where most people start.
3. No room for growth.
4. Usually wastes space.

First Normal Form (1NF) :

All rows must be fixed length. It is a restrictive assumption not a design principle.

1. Eliminate repeating columns in each table.
2. Create a separate table for each set of related data.
3. Identify each set of related data with a primary key.
4. All attributes are single valued & non-repeating.
5. Does not allow variable length lists.

First Normal Form (1NF)

Benefits: Now we can have infinite phone numbers or company addresses for each contact.

Drawback: Now we have to type in everything over and over again. This leads to inconsistency, redundancy and wasting space. Thus, the second normal form.

Second Normal Form (2NF):

All non-key fields must be a function of the full key.

1. Create separate tables for sets of values that apply to multiple records.
2. Relate these tables with a “foreign key”.
3. Addresses the concept of removing duplicative data.
4. Meet all the requirements of the first normal form.
5. Create relationships between these new tables and their pre-decessors through the use of foreign keys.
6. Remove subsets of data that apply to multiple rows of a table and place them in separate tables.

Third Normal Form (3NF):

Non-key fields cannot be a function of other non-key fields.

1. Eliminate fields that do not depend on the primary key.
2. Each non-primary key attribute must be dependent only on primary key.

Fourth Normal Form | Boyce Codd Normal Form (BCNF):

A row should not contain two or more independent multi-valued facts about an entity.

1. In many-to-many relationships, independent entities cannot be stored in the same table.
2. A relation is in 4NF if it has no multi-valued dependencies.
3. Occasionally, it becomes necessary to stray from them to meet practical business requirements. However, when variations take place, it’s extremely important to evaluate any possible ramifications they could have on your system and account for possible inconsistencies.

Example:

An employee may have several skills and languages.

An employee may have several skills and languages.

Blank fields ambiguous.

Blank skill could mean:

1. Person has no skill.
2. Attribute doesn’t apply to this employee.
3. Data is unknown.
4. Data may be found in another record (as in this case).

Solution:

Employee with skill.

Employee with language.

Two entity types, employee-skill and employee-language.

Fifth Normal Form (5NF):

A record cannot be reconstructed from several smaller record types.

1. The “very esoteric” one that is probably not required to get the most out of your database.
2. “The original table must be reconstructed from the tables into which it has been broken down.”
3. The rule ensures that you have not created any extraneous columns and all the tables are only as large as they need to be. The rule do exist, but are rarely considered in practical design.

Example.

Repetition of facts.

If an agent sells a certain product and she represents the company, then she sells that product for that company.

We can reconstruct all true facts from 3 tables instead of the single table.