Normalization | NormalForms

• Normalization is the process of organizing the data in the database.
• Normalization divides the larger table into smaller and links them using relationships.
• The normal form is used to reduce redundancy from the database table.

1NF —

• Each table cell should contain only a single value
• attribute of a table cannot hold multiple values

Below table is not in 1NF as it holds multiple values in same cell.

To make it in 1NF —

2NF —

We can say that a relation is in 2NF when it is already in 1NF, but all the non-key attributes fully and functionally depend on their primary keys.

In this table many subjects(S1, S3) have same Fee.

• So based on Fee, won’t be able to determine the Cand_ID & Subject.
• Based on Fee & Cand_ID, won’t be able to determine Subject as result may have multiple Subjects. Eg. Cand_ID=222 & Fee=1000 => Subjects=S1,S3
• Based on Cand_ID & Subject, won’t be able to determine Fee

So that means Fee is non-prime attribute, depend directly on Subject.

Get Webencyclop’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

To make it in 2NF, will decompose it in 2 tables.

Note: The Second Normal Form tries to reduce any redundant data from getting stored in the system’s memory. For instance, if we take an example of about 100 candidates taking the S1 subject, then we don’t have to store their fees as 1000 as a record for all the 100 candidates. Rather, we can store them all at once in the second table as the subject fee for S1 is 1000.

3NF —

• A relation will be in 3NF if it is in 2NF and not contain any transitive partial dependency.
• 3NF is used to reduce the data duplication. It is also used to achieve the data integrity.
• when none of the non-primary key attributes transitively depend on their primary keys, then we can say that the relation is in the third normal form of 3NF.

Note: If P -> Q and Q -> R are two functional dependencies, then P -> R is known as a transitive dependency.

In above table, Cand_id is primary key and all others are non-primary. ZIP is dependent on the CAND_ID, and CITY & STATE are dependent on ZIP.

Here, all the non-prime attributes (CAND_CITY, CAND_STATE) are dependent transitively on the super key (CAND_ID).

Thus, decompose the table into 3NF:

4NF Or Boyce Codd normal form (BCNF)

• BCNF is the advance version of 3NF. It is stricter than 3NF.
• We can say that a relation is in 4NF when it is in BCNF or Boyce-Codd Normal Form, but it does not have any multi-valued dependencies.

5NF Or Projected Normal Form

We can say that a relation is in the 5NF when it is already in 4NF, but it does not consist of the join dependencies. Also, the joining must be lossless here.