Third Normal Form (3NF) |Normalization and its type

THIRD NORMAL  FORM(3NF)

INTRODUCTION--

A relation is said to be in third normal form(3NF) if both conditions hold simultaneously.

1.) The relation is in Second Normal Form(2NF)

2.) Non-key attributes of the relation should not be transitively dependent on the primary key.

The main purpose of the 3NF is to remove the transitive dependency which is the main cause of anomalies in the 2NF.

EXAMPLE- To explain the 3NF, let us consider the example of STUDENT relation.Here the primary key is Stu_Id.

Stu_Id	Stu_Name	Teach_Id	Teach_Name	Teach_Qual
2523	Anurag	T001	Navathe	Ph.D
3712	Pankaj	T004	Date	M.Tech
4096	Gagan	T001	Navathe	Ph.D
2716	Anshu	T004	Date	M.Tech
1768	Harman	T009	Desai	M.Tech

The STUDENT relation is in 2NF but it suffers from insertion,deletion,updation anomalies.The main reason of these anomalies is because some attributes are transitively functionally dependent on the primary key.This results in redundancy in the relation.The dependencies that exist in STUDENT relation are:-

Here, the Stu_Name is functionally dependent on the attribute Stu_Id. Also Teach_Id is functionally dependent on the attribute Stu_Id. Also, Teach_Name is functionally dependent on Teach_Id  so we can say that Teach_Name is transitively functionally  dependent on Stu_Id.Similarly, Teach_Qual is transitively functional dependent on Stu_Id attribute.

                   So to make the relation in 3NF, We have to eliminate this transitive dependence on the primary key.To achieve this

1.) Create a new relation from the source relation that contains all the original attributes but without those attributes that are transitively dependent on the primary key.So in case of STUDENT relation,the resulting relation will contains Stu_Id ,Stu_Name and Teach_Id attributes.The primary key in this relation is same as in the original relation i.e. (Stu_Id).

Stu_Id	Stu_Name	Teach_Id
2523	Anurag	T001
3712	Pankaj	T004
4096	Gagan	T001
2716	Anshu	T004
1768	Harman	T009

2.) The other relation created from the source relation will contain attributes that are transitively functionally dependent on the primary key of the original relation plus the non key attribute from the original source relation on which the transitive functional dependent  attributes are functionally dependent. The primary key of the resulting relation will be a non key attribute on which the non key attributes are functionally dependent.

Teach_Id	Teach_Name	Teach_Qual
T001	Navathe	Ph.D
T004	Date	M.Tech
T009	Desai	M.Tech

Now the relation is in 3NF.

Read More

Multi valued dependency | Database design

MULTI-VALUED DEPENDENCY(MVD)

DEFINITION- MVD's occurs when two or more independent multi-valued facts about some attribute occur within the same table. Multi-valued dependency represented as

                                         

defines a relationship in which set of values in attribute B are determined  by a single value of A.

EXAMPLE:- Consider a relation EMP .

              

Ename	Child	Salary	Grade
Ranjit	Sunny	25000	2012
Ranjit	Sunny	30000	2012
Pankaj	Akshay	15000	2013
Pankaj	Akshay	25000	2014
Pankaj	Rupa	15000	2013
Pankaj	Rupa	25000	2014

In the above example child is the multi-valued fact of Ename which means that corresponding to the employee name, we have multiple values of children. e.g. Ename pankaj has two values {Akshay,Rupa} corresponding to him at this particular instance of time or we can say child is multi-valued dependent on Ename.

                                       

Read More

Fourth normal form | normalization and its type

FOURTH  NORMAL FORM(4NF)

A relation is said to be in  fourth normal form, if the following conditions hold.

1.) A relation should be in  BCNF (or 3NF).

2.) It contains no multi-valued dependency.

EXAMPLE- Consider a relation "EMPLOYEE".In this relation all the three attributes act as a primary key since no single attribute can uniquely identify a tuple (record).

Empname	Equipment	Language
Anurag	PC	English
Anurag	PC	French
Anurag	Mainframe	English
Anurag	Mainframe	French
Kapil	PC	English
Kapil	PC	French
Kapil	PC	Japanese

In the above example relationship between Empname and Equipment is a multi-valued dependency.Thus it implies that

Similarly, the relationship between Empname and Language is a multi-valued dependency which is represented as  

So,the relation is not in 4NF .To make it into 4NF we have to remove all the multi-valued dependency.For that move each MVD (Multi-valued dependency)  pair to a new relation having relation names  EMP_GPP and EMP_APP.

Relation EMP_GPP-                                                                      

Empname	Equipment
Anurag	PC
Anurag	Mainframe
Kapil	PC

Relation EMP_APP-

Empname	Language
Anurag	English
Anurag	French
Kapil	English
Kapil	French
Kapil	Japanese

Now it is in 4NF.

Read More

Join Dependency in Database management | Database design

JOIN DEPENDENCY

INTRODUCTION- Join dependency states that after a relation has been decomposed into multiple smaller relations,it must be capable of being joined again on common keys to form the original relation.

EXAMPLE-Consider a relation "PRO"

FACTORY	COMPONENT	PROJECT
GM	Engine	MPC
GM	Gear box	125A
Honda	Engine	125A
GM	Engine	125A

Now decompose this relation into two parts-first part is A1 and second part is A2.

Relation A1

FACTORY	COMPONENT
GM	Engine
GM	Gear box
Honda	Engine

Relation A2

COMPONENT	PROJECT
Engine	MPC
Gear box	125A
Engine	125A

Now again combine these two relations and check either we get the original relation or not. The relation after recombine is

FACTORY	COMPONENT	PROJECT
GM	Engine	MPC
GM	Gear box	125A
Honda	Engine	125A
GM	Engine	125A
Honda	Engine	MPC

Now here we can see that after recombine the relation, we could not get the original relation.An extra tuple is added to the relation which is Honda,Engine,MPC. So, this relation does'nt hold Join dependency.

Read More

Fifth normal form in Database management system | Normalization and its type

FIFTH NORMAL FORM-

DEFINITION- A table is in fifth normal form (5NF) or Project-Join Normal Form (PJNF) if
1. It is in 4NF 
2. It cannot have a lossless decomposition into any number of smaller tables. 

EXAMPLE- Consider a relation as an example of a buying table.This is used to track buyers, what they buy, and from whom they buy.

Buyer	Vendor	Item
Sally	Liz Claiborne	Blouses
Mary	Liz Claiborne	Blouses
Sally	Jordach	Jeans
Mary	Jordach	Jeans
Sally	Jordach	Sneakers

Problem:- The problem with the above table structure is that if Claiborne starts to sell Jeans then how many records must you create to record this fact? The problem is there are pair wise cyclical dependencies in the primary key. That is, in order to determine the item you must know the buyer and vendor, and to determine the vendor you must know the buyer and the item, and finally to know the buyer you must know the vendor and the item.

Solution:- The solution is to break this one table into three tables; Buyer-Vendor, Buyer-Item, and Vendor-Item. So following tables are in the 5NF.

Relation Buyer-Vendor-

Buyer	vendor
Sally	Liz Claiborne
Mary	Liz Claiborne
Sally	Jordach
Mary	Jordach

Relation Buyer-Item-

Buyer	Item
Sally	Blouses
Mary	Blouses
Sally	Jeans
Mary	Jeans
Sally	Sneakers

Relation Vendor-Item-

Vendor	Item
Liz Claiborne	Blouses
Jordach	Jeans
Jordach	Sneakers

These relation are in 5NF.

Read More