Normalization normal forms with examples – Coder in Me

In the previous post we learn about the basics of normalization, now we are going to discuss the more detailed view of it, “THE NORMAL FORMS”.

Normal forms based on Primary Key

Normalization process tests the tables which use to be stored in the database through various normal forms so that data can’t be redundant or inconsistent. At first, Codd started the three basic normal forms; first NF, Second NF, third NF, then with Boyce he proposed BOYCE-CODD NF which is the higher version of 3-NF.

After this Codd introduced more Normal forms as 4-NF, 5-NF etc which was based on multivalued and joined value dependencies. Normal forms on the alone are not sufficient for the best database design it has to be the combination of the normal forms at least up to 3-NF or BCNF to be sufficient.Normalization not only satisfied the Normal forms but also some additional properties as:

  • The non-additive join or lossless join property:

It is the property of decomposition through which we can identify all the records of the smaller relations when we again join it.

  • The dependency preservation property:

It ensures that each functional dependency is represented in some individual relation resulting from decomposition.

 

Definition of Keys:

  1. Super Key: A set of attributes that uniquely define the key if any one attribute remove then can’t uniquely identify the data. It has to be minimal For example

table for Normal form example

 

In the above table {SSN, Bdate, Ename} is the minimal super key of EMPLOYEE

 

  1. Candidate key: When a table has more than a single key then it is termed as the Candidate key. For example in DEPARTMENT Dmgr_ssn is the candidate key as it can also work as the Primary key.

 

  1. Primary key: when a table’s data is uniquely identified by a key it is termed as Primary key.In the above table PROJECT, Pnumber is the primary key.

 

  1. Composite key: When two or more attributes combine to uniquely identify the table as In DEPT_LOCATIONS Dnumber and Dlocation.

  

Now as you understand the concept of keys, Let’s begin with the Normal Forms. There are various types of Normal forms:

  1. First Normal Form or 1-NF:

    In this, it is disallowed to have multivalue or composite value or a combination of both, Hence, it allows only atomic (indivisible) valued cell.

To remove this you can make a separate table or create a separate field as {EmpId, Name, Phone}

Emp_Id Name Phone

In this Phone can be multivalued so you can create a separate column as {EmpId, Name, Phone1, Phone2}

Emp_Id Name Phone1 Phone2

 

2. Second Normal Form or 2-NF:

The table must be in 1-NF and don’t have any partial dependency i.e. the primary key is the only key to extract the data.For example,

{Student,Age,Subject}

Student Subject Age

are the values in a relation where {Student, Subject} is the candidate key. But here the age is only dependent on the Student. Ergo to remove this we make two relations:

One is of {Subject,Age}

Student Subject

& the second one is of {Student, Subject}

Student Subject

so that the attributes are fully dependent on a single attribute.

 

3. Third Normal Form or 3-NF:

The table must be in 2-NF and non-trivial dependency can’t be found in relation i. e. non-key attributes can’t dependently retrieve the table data. For example, a table Employee_Details

Emp_Id Name DOB Zip_Code City Street 

Here Street and City are dependent on Zip_Code this is a trivial dependency,so to remove this make separate tables as

One table is of emp_detail

Emp_Id Name DOB Zip_Code

& the other one is Address_Detail

Zip_Code City Street

 

4.BCNF or Boyce-Codd Normal Form:

A relational schema R is in Boyce–Codd normal form if and only if for every one of its dependencies X → Y, at least one of the following conditions hold:

X → Y is a trivial functional dependency (Y ⊆ X)

X is a Super-key for schema R

Explaining example through this image below:

Normalization normal forms with examples

 

 

 

A Salesforce Developer at AlmaMate Info Tech PVT LTD. An Aligarian and also your query solver in database field.This site mark the difference as Black or White to make you choose the best for you. Don't feel pressurized, feel confident..!!

Leave a reply:

Your email address will not be published.