DBMS

Normal Forms

 Forms of normalization are given below:

  1. FIRST NORMAL FORM (1NF)
  2. SECOND NORMAL FORM (2NF)
  3. THIRD NORMAL FORM (3NF)
  4. Boyce-Codd Normal Form (BCNF)
  5.  FOURTH NORMAL FORM (4NF)
  6. FIFTH NORMAL FORM (5NF)

FIRST NORMAL FORM (1NF) :

"A relation schema R is in 1NF, if it does not have any composite attributes,multivalued atttribute or their combination."
The objective of first normal form is that the table should contain no repeating groups of data.Data is divided into logical units called entities or tables
All attributes (column) in the entity (table) must be single valued.
Repeating or multi valued attributes are moved into a separate entity (table) & a relationship is established between the two tables or entities.

Example of the first normal form :
Consider the  following Customer table.
Customer :
cid name address contact_no
society city
C01 aaa Amul avas,Anand {1234567988}
C02 bbb near parimal garden,abad {123,333,4445}
C03 ccc sardar colony , surat  

Here,address is a composite attribute , which is further subdivided into two column society and city.And attribute contact_no is multivalued attribute.
Problems with this relation are - 

  • It is not possible to store multiple values in a single field in a relation. so, if any customer has more than one contact number, it is not possible to store those numbers.
  • Another problem is related to information retrieval . Suppose, here, if there  is a need to find out all customers belonging to some particular city, it is very difficult to retrieve. The reason is: city name for all customers are combined with society names and stored whole as an address.

Solution for composite attribute

Insert separate attributes in a relation for each sub-attribute of a composite attribute.
In our example, insert two separate attributes for Society and city in a relation in place of single composite attributes address. Now, insert data values separately for Society and City for all tuples.
 Customer :
cid name Society
city contact_no
C01 aaa Amul avas Anand {1234567988}
C02 bbb near parimal garden abad {123,333,4445}
C03 ccc sardar colony surat  

Solution for Multi-valued attribute

Two approaches are available to solve problem of multi-valued attribute

1. First Approach:
In a First approach, determine maximum allowable values for a multi-valued attribute.In our case, if maximum two numbers are allowed to store, insert two separate attributes attributes to store contact numbers as shown.
Customer:
cid name Society city
contact_no1 contact_no2 contact_no3
C01 aaa Amul avas Anand 1234567988    
C02 bbb near parimal garden abad 123 333 4445
C03 ccc sardar colony surat      

Now,if customer has only one contact number or no any contact number, then keep the related field empty for tupple of that customer. If customer has two contact numbers, store both number in related fields. If customer has more than two contact numbers, store two numbers and ignore all other numbers.

2.Second Approach:
In a second approach, remove the multi-valued attribute that violates 1NF and place it in a separate relation along with the primary key of given original relation. The primary key of new relation is the combination of multi-valued attribute and primary key of old relation. for example, in our case, remove the contact_no attribute and place it with cid in a separate relation customer_contact. Primary Key for relation Customer_contact will be combination of cid and contact_no.
customer:
cid name Society city
C01 aaa Amul avas Anand
C02 bbb near parimal garden abad
C03 ccc sardar colony surat
 
Customer_contact
cid contact_no
C01 1234567988
C02 123
C02 333
C02 4445

First approach is simple.But, it is not always possible to put restriction on maximum allowable values.It also introduces null values for mant fields.
Second approach is superior as it does not suffer from draw backs of first approach. But, it is some what complicated one.For example, to display all information about any/all customers, two relations - Customer and Customer_contact - need to be accessed.
 

SECOND NORMAL FORM (2NF) : 

"A relation schema R is in 2NF, if  It is in First Normal Form, and every non_ prime attribute of relation is fully functionally dependent on primary key."
A relation can violate 2NF only when it has more than one attribute in combination as a primary key. if relation has only single attribute as a primary key, then, relation will definitely be in 2NF.

Example:

consider the folllowing relation Table Depositor_Account 
Depositor_Account
snf1

This relation contains following functional dependencies.
FD1 : {Cid, ano} -> {access_date, balance, bname}
FD2 :   ano   ->   {balance, bname}

In this relation schema, access_date, balance and bname are non - prime attributes. Among all these three attributes, access_date is fully dependent on primary key (cid and ano). But balance and bname are not fully dependent on primary key. tey depend on ano only.
So, this relation is not in Second normal form. Such kind of partia dependencies result in data redundancy.

Solution:

Decompose the ralation such that, resultant relations do not have any partial functional dependency. For this purpose, remove the partial dependent non-prime attributes that violates 2NF in relation. Place them in a  separate new relation along with the prime attribute on which they fully depend. 
In our example, balance and bname are partial dependant attribute on primary key. so, remove them and place in separate ralation called account alog with prime attribute ano. For relation Account, ano will be a Primary key.
The Depositor_ account relation will be decomped in two seperate relations, called Account_holder and Account.
Account:
snf2
Account_Holder:
snf3
 

THIRD NORMAL FORM (3NF) : 

"A relation schema R is in 3NF, if it is in Second normal form, and no any non-prime attribute of relation is transitively dependent on primary key."
Third normal form ensures that the relation does not have any non-prime attribute transitively dependent on primary key. In other words, It ensures that all the non-prime attributes of relation  directly depend on the primary key.
 

Example:

Consider the following relation schema Account_Branch
Account_Branch:
tnf1

This relationcontains following functional dependencies.
FD1 : ano -> {balance, bname, baddress}, and
FD2 : bname -> baddress
In this relation schema, there is a functional dependency ano -> bname between ano & bname as shown in FD1. also, there is another functional  dependency bname -> baddress between bname & baddress as shown in FD2. more over bname is a non-prime attribute. So, there is a transitive dependency from ano to baddress, denoted by ano -> baddress.
Such  kind of transitive dependencies result in data redundancy. In this relation, branch address wil be stored repeatedly for each account of the same branch, occupying more amount of memory.

Solution:

Decompose the relation in such a way that, resultant relatons do not have any non-prime attribute transitively dependent on primary key. For this purpose, remove the transitively dependant non-prime attributes that violates 3NF from relation. Place them in a separate new relation along with the non-prime attribute due to which tansitive dependency occured. The primary key of new relation will be the non-prime atttribute.
In our example, baddress is transitively dependent on ano due to non-prime attribute bname. So, remove baddress and place it in separate relation called Branch along with the non-prime attribute bname. for relation Branch, bname will be a primary key.
The Account_Branch relation will be decomposed in two separate relations called Account and Branch.
Account:
tnf2
 
Branch:
tnf3



Subscribe us on Youtube

Share This Page on

Questions


Sashi | 02-Nov-2017 02:41:40 pm

Bcnf,4nf and 5 nf also need. Plz provide that also


Eflangor Animorphs | 24-Apr-2018 01:16:44 pm

Great work. could you include a legend on the abbreviations used in 2NF and 3NF? took some guesswork and patience to get the gist behind the examples. you used full words for 1NF, so there was no problem flying through that one.


Ask Question