Normalization in Oracle database #Diksha Oracle Training Centre

                            Normalization in Oracle database.

  

Hello Everyone ,

Today I am going to discuss about Normalization . Before starting this topic  I suggest you to please go through my previous blogs for your reference. Let’s discuss about Normalization. Normalization rules divide larger tables into smaller tables and links them using relationships. Normalization in Database is very important as it is the process of taking data from a problem and reducing it to a set of relations while ensuring data integrity and eliminating data redundancy.  In Simple word Normalization is a database design technique that reduces data redundancy and eliminates undesirable characteristics like Insertion, Update and Deletion .

Below are the Key Points in Normalization.

Data Integrity : All of the data in the database are consistent , and satisfy all integrity constraints.

Data Redundancy : If  data in the database can be found in two different locations( Direct redundancy) or if data can be calculated from other data items (indirect redundancy) then the data is said to contain redundancy.

Note: Data Integrity we already discussed in my previous logs for keys etc you can take reference .

Without Normalization in SQL we may face below issues.

Insertion anomaly: It happens when we cannot insert data to the table without the presence of another attribute.

Update anomaly:  It is a data inconsistency that results from data redundancy and a partial update of data.

Deletion Anomaly: It happens when certain attributes are lost because of the deletion of other attributes.

SQL with Normalization :SQL is the language that is used to interact with the database. To initiate any interaction the data present in the database has to be of Normalized Form. Else we cannot proceed further as it results in anomalies.

Normalization in SQL will enhance the distribution of data. Now let’s discuss about each and every Normal Form with simple examples.

 

1NF (1st Normalisation Form)

 

2NF (2nd Normalisation Form)

 

3NF (3rd Normalisation Form)

 

BCNF (Boyte –Codd Normal Form)

 

Note: The Theory of Data Normalization in SQL is still being developed further. For example, there are discussions even on 6th Normal Form. But the most practical applications, normalization achieves its best is 3rd Normalization Form.

 

1NF (First Normalisation Form) : 1NF deals with the shape of the record type.

 

Rules of 1NF:

Each table cell should contain a single value.

Each record needs to be unique.

In 1NF a single cell cannot hold multiple values. If a table contains a composite or multi-valued attribute, it violates the First Normal Form.

Note : Key and Constraint I already discussed in my previous blogs so for reference you can see that , but hope you are aware about keys and constraints.

 

Let’s take example from  below table for 1NF which violate 1NF.

 

In the above table, we can clearly see that the cell number column has two values. Thus it violated the 1st NF. To make 1NF we need to modify the value of the table. Below table is 1NF.

 

Applying this, we have achieved atomicity and also each and every column have unique values. And its follow First Norm Form.

 

2NF (2nd Norm Form):  The first condition in the 2nd NF is that the table has to be in 1st NF. The table also should not contain partial dependency. Here partial dependency means the proper subset of candidate key determines a non-prime attribute. To understand in a better way see the below example.

See the below table.

 

This table has a composite primary key Emplyoee ID, Department ID. The non-key attribute is Department Name. In this case, Department Name only depends on Department ID, which is only part of the primary key. Therefore, above table does not satisfy the 2NF.

Make this table as Second Normal Form, we need to break the table into two parts.

Two tables are Below :

 

 

 

 

 

 

 

As you see now we have removed the partial functional dependency that we initially had. Now, in the table, the column department_name is fully dependent on the primary key of that table, that is department_id.

 

3NF (3rd Normal Form)

 

Same rule applies as before i.e, the table has to be in 2NF before proceeding to 3NF.

Rules for 3NF is as below:

 

A relation in 3NF if, and only if  , it is in 2 NF and there are no transitive functional dependencies. Transitive Functional dependencies arise:

 

When one non-key attribute is functionally dependant on another non-key attributes and there is redundancy in data attribute.

 

Let understand this more clearly with example:

 

 

 

 

In the above table, enrollment_no determines subject ID, and subject ID determines Subject. Therefore, enrollment_no determines Subject using subject ID. This implies that we have a transitive functional dependency, and this  does not follow the third normal form(3NF).

 

To achieve third normal form, we need to divide the table in two part as below:

 

 

Here you can see from the above tables all the non-key attributes are now fully functional dependent only on the primary key. In the first table, columns Student Name, Subject ID and Address are only dependent on enrollment_no. In the second table, Subject is only dependent on Subject ID. This follow 3NF(3rd Normal Form).

 

Boyce Cod Normal Form (BCNF)

 

This is also known as 3.5 NF. It’s the higher version 3NF . BCNF to address certain types of anomalies which were not dealt with 3NF.

 

Before proceeding to BCNF the table has to satisfy 3rd Normal Form.

 

In BCNF if every functional dependency A → B, then A has to be the Super Key of that particular table.

 

See the below table:

 

 

 

 

One student can enrol for multiple subjects.There can be multiple Trainer teaching one subject And, For each subject, a Trainer is assigned to the student .In this table, all the normal forms are satisfied except BCNF.

 

Because As you can see enrollment_no,  and Subject form the primary key, which means the Subject column is a prime attribute. But, there is one more dependency, Trainer_name → Subject.

 

And while Subject is a prime attribute, Trainer_name is a non-prime attribute, which is not allowed by BCNF.

 

To satisfy the BCNF, we will be dividing the table into two parts. One table will hold Stud_ID which already exists and newly created column Trainer_ID.

 

By Applying this we are satisfied the BCNF.

 

This is all about Normalization in SQL .I think you have now clear picture about Normalization.  I suggest you to see my below blog where in archive folder you can able to see constraints and different key in and  SQL command for your reference .

https://dikshaoracletraining.blogspot.com/

Please go through this blog carefully and let me know if you have any doubts as Normalization plays important in database and this blog is important for fresher candidate who is preparing for interview.

Thanks.