Home » DBMS

Normalization in Database Management System

Learn: In this article, we are going to discuss the introduction of normalization different types of normal form and also discuss how to find the highest normal form in relation and types of Anomalies in Database management system.
Submitted by Prerana Jain, on June 07, 2018

Normalization in DBMS

Every table must have a single idea. The method by which we divide tables approximately is called normalization and the rest used for normalization is a functional dependency. For the normalization process, every column in a table will have a unique name. Order of rows and columns are insignificant and every cell will contain single or atomic values. Every table will contain a primary key and the value of the primary key cannot be null.

Because of normalization number of tables in the database increase because of which excess become slow and query retrieval time increases in that cases, we must be in the position to combine decomposed table to get back the parent table. this process is called denormalization.

Types of Normalization form in DBMS

1) First Normalization form

A relational schema R is said to be in first normal form if every cell in the row contains atomic values. It means first normal form does not allow composite or multivalued attributes. A table in first normal form will always have exactly one primary key (null value is not allowed in primary key). It always we assumed that if we have a proper relational table than it will always be in first normal form.

2) Second Normalization form

A table is said to in second normal form if it is first normal form and there must not be exist any partial dependency.

Partial dependency

If a non-prime attribute instead of depending on the entire candidate key depends on its subset than this is the partial dependency (from prime to non-prime).a attribute is called prime attribute if it is the part of any candidate key.

If a table is not in second normal form than construct first table for the candidate key only those attribute will come which totally depend on the key and then for every partial dependency we have a separate table.

3) Third Normalization form

A relational table is said third normal form when it is in first normal form and there exit no transitive dependency.

A relational schema is said to be in third normal form if every dependency from A → B either A is a superkey or a prime attribute.

Example- R (ABC) B → C

Here, A is a prime attribute and BC are the non-prime attribute here dependency from B to C is transitive as one non-prime is finding another non-prime. Here if B gets null values at runtime then we cannot identify C.

4) Boyce-Codd Normalization form (BCNF)

A relational table is said to be in BCNF normal form if for dependency from A to B and A must be a super key.

Steps to find the highest normal form of a relation

  1. First of all, find the entire possible candidate key in the given relation.
  2. Divide all given attribute into two categories one is prime and other one is non-prime attributes.
  3. Firstly check for the first normal form then second normal form and so on if we fail to satisfy nth normal form than the highest normal form will be n-1.

Anomalies in Database Management System

There are different types of anomalies which can occur in referencing and the referenced relation which we will discuss with the help of an example,

Ename Ssn Dnumber Dname Dmgrssn
Smith 1234 5 Research 3334
Wong 3334 5 Research 3334
Zeloya 9998 4 Admin 9876
Wallace 9876 4 Admin 9876
Jabbar 9879 4 Admin 9876

Insertion Anomalies

If we need to insert a new employee with no department assigned yet, we need to enter null values. We cannot insert a new department with no employees working on it on SSN= null write not be valid, a primary key cannot be null.

Deletion Anomalies

If we delete last working employee of the department, the information regarding department will be lost.

Modification anomalies

If we change the 'depart_mgr' of dept 5 we must update the tuples of all employees who work that department otherwise database will be inconsistent.

Comments and Discussions

Ad: Are you a blogger? Join our Blogging forum.

Languages: » C » C++ » C++ STL » Java » Data Structure » C#.Net » Android » Kotlin » SQL
Web Technologies: » PHP » Python » JavaScript » CSS » Ajax » Node.js » Web programming/HTML
Solved programs: » C » C++ » DS » Java » C#
Aptitude que. & ans.: » C » C++ » Java » DBMS
Interview que. & ans.: » C » Embedded C » Java » SEO » HR
CS Subjects: » CS Basics » O.S. » Networks » DBMS » Embedded Systems » Cloud Computing
» Machine learning » CS Organizations » Linux » DOS
More: » Articles » Puzzles » News/Updates

© https://www.includehelp.com some rights reserved.