SIGN UP MEMBER LOGIN:
   
ARTICLE

Normalization in Database

Posted by Lakshit Gaur Articles | SQL Server January 02, 2013
In this articleI will explain about Normalization in Database.
Reader Level:

Database Normalization

The database normalization is a data design and organization process applied to data structure based on rules that help build relational database.

Normalization usually involves dividing a database into two or more tables and defining the relationship between the tables.
there are 8 types of normalization process which are as follows:-

1NF(First Normal Form)

A relation is said to be in First Normal Form (1NF) if and only if each attribute of the relation is atomic. to be in 1NF, each column must contain only a single value and each row must contain the same columns.

It is used for eliminating the repeating groups and make a seperate table for each set of related attributes and give each table a primary key.

Example:

1NF but not 2NF
FIRST (supplier_no, status, city, part_no, quantity)
Functional Dependencies
(supplier_no, part_no) ® quantity
(supplier_no) ® status
(supplier_no) ® city
city ® status (Supplier's status is determined by location)

Comments

Non-key attributes are not mutually independent (city ® status).
Non-key attributes are not fully functionally dependent on the primary key (i.e., status and city are dependent on just part of the key, namely supplier_no).

2NF(Second Normal Form)

It is used for eliminating the redundant data and if an attribute depends on only part of multi-valued key then remove it to a seperate table.

Example (2NF but not 3NF)

SECOND (supplier_no, status, city)

Functional Dependencies:

supplier_no ® status
supplier_no ® city
city ® status

Comments

Lacks mutual independence among non-key attributes. Mutual dependence is reflected in the transitive dependencies: supplier_no ® city, city ® status.

3NF(Third Normal Form)

In this we have to eliminate the columns not dependent on key and if an attribute do not contribute to a description of the key remove them to a seperate table and all attribute must be directly dependent on the primary key.

Example (3NF but not BCNF)

SUPPLIER_PART (supplier_no, supplier_name, part_no, quantity)

Functional Dependencies

We assume that supplier_name's are always unique to each supplier. Thus we have two candidate keys:

(supplier_no, part_no) and (supplier_name, part_no)
Thus we have the following dependencies:
(supplier_no, part_no) ® quantity
(supplier_no, part_no) ® supplier_name
(supplier_name, part_no) ® quantity
(supplier_name, part_no) ® supplier_no
supplier_name ® supplier_no
supplier_no ® supplier_name

Comments

Although supplier_name ® supplier_no (and vice versa), supplier_no is not a non-key column — it is part of the primary key! Hence this relation technically satisfies the definition(s) of 3NF (and likewise 2NF, again because supplier_no is not a non-key column).

BCNF(Boyce-Codd Normal Form)

In this if there are non trivial dependencies between candidate keys attributes seperate them out into distinct tables.

Example

An example of a relation in 3NF but not in BCNF (and exhibiting the three properties listed) was given above in the discussion of 3NF. The following relation is in BCNF (and also in 3NF):

SUPPLIERS (supplier_no, supplier_name, city, zip)

We assume that each supplier has a unique supplier_name, so that supplier_no and supplier_name are both candidate keys.

Functional Dependencies

supplier_no ® city
supplier_no ® zip
supplier_no ® supplier_name
supplier_name ® city
supplier_name ® zip
supplier_name ® supplier_no

Comments

The relation is in BCNF since both determinants (supplier_no and supplier_name) are unique (i.e., are candidate keys).
The relation is also in 3NF since even though the non-primary-key column supplier_name determines the non-key columns city and zip, supplier_name is a candidate key. Transitive dependencies involving a second (or third, fourth, etc.) candidate key in addition to the primary key do not violate 3NF.

4NF(Forth Normal Form)

In this we have to check the isolate independent multiple relationship. No table may contain two or more 1:n or n:m relationship that are not directly related.

5NF(Fifth Normal Form)

We have to check the isolate sequentially related multiple seperate logically related many to many relationship.

ONF(Optimal Normal Form)

It is expressed in object role model notation.

DKNF(Domain Key Normal Form)

It is free from all modification. The reason to use domain/key normal form is to avoid having general constraints in the database that are not clear domain or key constraints. Most databases can easily test domain and key constraints on attributes.


Login to add your contents and source code to this article
This Feature is Sponsored By
post comment
COMMENT USING
TRENDING UP