Decomposition in Database Design | Database Design

DECOMPOSITION

A decomposition means dividing a table into more than one table.

There are 2 types of decomposition.

1.) Losy Decompsition

2.) Lossless Decomposition

1.) LOSY DECOMPOSITION- When we decompose the table,after recombining it  we doesn't get the accurate result or we can say the same relation as that of original.

2.) LOSSLESS DECOMPOSITION- When we decompose the table,after recombining it  we get the accurate result or we can say the same relation as that of original.

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 contains losy decomposition.