LosslessJoin Decomposition
Let us show an intuitive decomposition of a relation. We require a better basis for deciding decompositions since intuition may not always be right. We show how a careless decomposition may lead to problems containing loss of information.
Consider the following relation
ENROL (stno, cno, dateenrolled, roomno, instructor)
Suppose we decompose the above relation into two relations enrol and enrol2 as follows:
ENROL1 (stno, cno, dateenrolled)
ENROL2 (dateenrolled, roomno, instructor)
There are troubles with this decomposition but we do not wish to focus on this aspect at the moment. Let a case of the relation ENROL be:
St no

cno

Dateenrolled

Roomno

Instructor

1123

MCS011

20062004

1

Navyug

1123

MCS012

26092004

2

Anurag Sharma

1259

MCS011

26092003

1

Preeti Anand

1134

MCS015

30102005

5

Preeti Anand

2223

MCS016

05022004

6

Shashi Bhushan

Figure: A sample relation for decomposition
Then on decomposition the relations ENROL1 and ENROL2 would be:
St no

Cno

Dateenrolled

1123

MCS011

20062004

1123

MCS012

26092004

1259

MCS011

26092003

1134

MCS015

30102005

2223

MCS016

05022004



ENROL1
ENROL2
Dateenrolled

Roomno

Instructor

20062004

1

Navyug

26092004

2

Anurag Sharma

26092003

1

Preeti Anand

30102005

5

Preeti Anand

05022004

6

Shashi Bhushan

All the information that was in the relation ENROL appears to be still existing in ENROL1 and ENROL2 but this is not so. Assume, we wanted to retrieve the student numbers of all students taking a course from Preeti Anand, we would require to join ENROL1 and ENROL2. For joining the only general attribute is Dateenrolled. Therefore, the resulting relation obtained will not be the similar as that of Figure. (Please do the verify and join the resulting relation).
The join will have a number of spurious tuples that were not in the unique relation. Because of these additional tuples, we have lost the correct information about which students take courses from Preeti Anand. (Yes, we have many tuples but less information because we are not capable to say with certainty who is taking courses from Preeti Anand). Such decompositions are known as lossy decompositions. Lossless or nonloss decomposition is that which surety that the join will result in exactly the similar relation as was decomposed. One might think that there may be other ways of recovering the unique relation from the decomposed relations but, sadly, no other operators can recover the unique relation if the join does not (why?).
We require to analyse why the decomposition is lossy. The general attribute in the above decompositions was Dateenrolled. The general attribute is the glue that provides us the ability to find the relationships among different relations by joining the relations simultaneously. If the general attribute have been the primary key of at least one of the two decomposed relations, the trouble of losing information would not have existed. The trouble arises because various enrolments may take place on the similar date.