Skip to content
Advertisements

DBMS/U2 Topic 6 Decomposition of Relation Schemes

A functional decomposition is the process of breaking down the functions of an organization into progressively greater (finer and finer) levels of detail. 
In decomposition, one function is described in greater detail by a set of other supporting functions. 
The decomposition of a relation scheme R consists of replacing the relation schema by two or more relation schemas that each contain a subset of the attributes of R and together include all attributes in R.
Decomposition helps in eliminating some of the problems of bad design such as redundancy, inconsistencies and anomalies. 
There are two types of decomposition :

  1. Lossy Decomposition
  2. Lossless Join Decomposition

Lossy Decomposition :

“The decompositio of relation R into R1 and R2 is lossy when the join of R1 and R2 does not yield the same relation as in R.”
 One of the disadvantages of decomposition into two or more relational schemes (or tables) is that some information is lost during retrieval of original relation or table.
Consider that we have table STUDENT with three attribute roll_no , sname and department.

STUDENT

Roll_no Sname Dept
111 parimal COMPUTER
222 parimal ELECTRICAL

This relation is decomposed into two relation no_name and name_dept :

No_name:

Roll_no Sname
111 parimal
222 parimal

Name_dept :

Sname Dept
parimal COMPUTER
parimal ELECTRICAL

In lossy decomposition ,spurious tuples are generated when a natural join is applied to the relations in the decomposition.

stu_joined :

Roll_no Sname Dept
111 parimal COMPUTER
111 parimal ELECTRICAL
222 parimal COMPUTER
222 parimal ELECTRICAL

The above decomposition is a bad decomposition or Lossy decomposition.

Lossless Join Decomposition :

“The decompositio of relation R into R1 and R2 is lossless when the join of R1 and R2  yield the same relation as in R.”
A relational table is decomposed (or factored) into two or more smaller tables, in such a way that the designer can capture the precise content of the     original table by joining the decomposed parts. This is called lossless-join (or non-additive join) decomposition.
This is also refferd as non-additive decomposition.
The lossless-join decomposition is always defined with respect to a specific set F of dependencies.
Consider that we have table STUDENT with three attribute roll_no , sname and department.

STUDENT :

Roll_no Sname Dept
111 parimal COMPUTER
222 parimal ELECTRICAL

This relation is decomposed into two relation Stu_name and Stu_dept :

Stu_name:

Roll_no Sname
111 parimal
222 parimal

Stu_dept:

Roll_no Dept
111 COMPUTER
222 ELECTRICAL

Now ,when these two relations are joined on the comman column ‘roll_no’ ,the resultant relation will look like stu_joined.

stu_joined :

Roll_no Sname Dept
111 parimal COMPUTER
222 parimal ELECTRICAL

In lossless decomposition, no any spurious tuples are generated when a natural joined is applied to the relations in the decomposition.

Advertisements
Advertisements
Advertisements
, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

1 Comment »

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: