Desirable properties of decomposition are 1 attribute

Desirable properties of decomposition are:
1. Attribute preservation
2. Lossless-join decomposition
3. Dependency preservation
4. Lack of redundancy
Attribute Preservation
This is a simple and an obvious requirement that involves preserving all the
attributes that were there in the relation that is being decomposed.
Lossless-Join Decomposition
We decomposed a relation intuitively. We need a better basis for deciding
decompositions since intuition may not always be correct. We illustrate how a
careless decomposition may lead to problems including loss of information.
Consider the following relation
149

DATABASE MANAGEMENT SYSTEMS PREPARED BY II CSE(2010-2014) CBIT, PRODDATUR
R09 batch (Guidance by J UMA MAHESH, CBIT CSE, DEPT.)
enrol (sno, cno, date-enrolled, room-No., instructor)
Suppose we decompose the above relation into two relations enrol1 and enrol2 as
follows
enrol1 (sno, cno, date-enrolled)
enrol2 (date-enrolled, room-No., instructor)
There are problems with this decomposition but we wish to focus on one aspect at
the moment. Let an instance of the relation enrol be
Sno Cno Date-Enrolled Room-
no.
Instructor
830057
830057
820159
825678
826789
CP302
CP303
CP302
CP304
CP305
1FEB1984
1FEB1984
10JAN1984
1FEB1984
15JAN1984
MP006
MP006
MP006
CE122
EA123
Gupta
Jones
Gupta
Wilson
Smith
and let the decomposed relations enrol1 and enrol2 be:
Sno Cno Date-Enrolled
830057
830057
820159
825678
CP302
CP303
CP302
CP304
1FEB1984
1FEB1984
10JAN1984
1FEB1984
150

DATABASE MANAGEMENT SYSTEMS PREPARED BY II CSE(2010-2014) CBIT, PRODDATUR
R09 batch (Guidance by J UMA MAHESH, CBIT CSE, DEPT.)
826789 CP305 15JAN1984
Date-Enrolled Room-
no.
Instructor
1FEB1984
1FEB1984
10JAN1984
1FEB1984
15JAN1984
MP006
MP006
MP006
CE122
EA123
Gupta
Jones
Gupta
Wilson
Smith
All the information that was in the relation enrol appears to be still available in
enrol1 and enrol2 but this is not so. Suppose, we wanted to retrieve the student
numbers of all students taking a course from Wilson , we would need to join enrol1
and enrol2 . The join would have 11 tuples as follows:
Sno Cno Date-
Enrolled
Room-No. Instructor
830057
830057
830057
830057
830057
830057
CP302
CP302
CP303
CP303
CP302
CP303
1FEB1984
1FEB1984
1FEB1984
1FEB1984
1FEB1984
1FEB1984
MP006
MP006
MP006
MP006
CE122
CE122
Gupta
Jones
Gupta
Jones
Wilson
Wilson
(add further tuples ...)
151

DATABASE MANAGEMENT SYSTEMS PREPARED BY II CSE(2010-2014) CBIT, PRODDATUR
R09 batch (Guidance by J UMA MAHESH, CBIT CSE, DEPT.)
The join contains a number of spurious tuples that were not in the original relation
Enrol. Because of these additional tuples, we have lost the information about
which students take courses from WILSON. (we have more tuples but less
information because we are unable to say with certainty who is taking courses from
WILSON). Such decompositions are called lossy decompositions.
A nonloss or lossless decomposition is that which guarantees that the join will
result in exactly the same relation as was decomposed.
We need to analyze why some decompositions are lossy. The common attribute in
above decompositions was Date-enrolled. The common attribute is the clue that
gives us the ability to find the relationships between different relations by joining
the relations together. If the common attribute is not unique, the relationship
information is not preserved. If each tuple had a unique value of Date-enrolled, the