This preview shows page 149 - 153 out of 169 pages.
Desirable properties of decomposition are: 1. Attribute preservation 2. Lossless-join decomposition 3. Dependency preservation 4. Lack of redundancyAttribute Preservation This is a simple and an obvious requirement that involves preserving all theattributes that were there in the relation that is being decomposed. Lossless-Join Decomposition We decomposed a relation intuitively. We need a better basis for decidingdecompositions since intuition may not always be correct. We illustrate how acareless 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 enrol2asfollows 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 atthe moment. Let an instance of the relation enrolbe SnoCnoDate-EnrolledRoom-no.Instructor830057830057820159825678826789CP302CP303CP302CP304CP3051FEB19841FEB198410JAN19841FEB198415JAN1984MP006MP006MP006CE122EA123GuptaJonesGuptaWilsonSmithand let the decomposed relations enrol1and enrol2be: SnoCnoDate-Enrolled830057830057820159825678CP302CP303CP302CP3041FEB19841FEB198410JAN19841FEB1984150
DATABASE MANAGEMENT SYSTEMS PREPARED BY II CSE(2010-2014) CBIT, PRODDATUR R09 batch (Guidance by J UMA MAHESH, CBIT CSE, DEPT.)826789CP30515JAN1984Date-EnrolledRoom-no.Instructor1FEB19841FEB198410JAN19841FEB198415JAN1984MP006MP006MP006CE122EA123GuptaJonesGuptaWilsonSmithAll the information that was in the relation enrol appears to be still available inenrol1 and enrol2but this is not so. Suppose, we wanted to retrieve the studentnumbers of all students taking a course from Wilson,we would need to join enrol1and enrol2.The join would have 11 tuples as follows: SnoCnoDate-EnrolledRoom-No.Instructor830057830057830057830057830057830057CP302CP302CP303CP303CP302CP3031FEB19841FEB19841FEB19841FEB19841FEB19841FEB1984MP006MP006MP006MP006CE122CE122GuptaJonesGuptaJonesWilsonWilson(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 relationEnrol. Because of these additional tuples, we have lost the information aboutwhich students take courses from WILSON. (we have more tuples but lessinformation because we are unable to say with certainty who is taking courses fromWILSON). Such decompositions are called lossy decompositions.A nonloss or losslessdecomposition is that which guarantees that the join willresult in exactly the same relation as was decomposed. We need to analyze why some decompositions are lossy. The common attribute inabove decompositions was Date-enrolled. The common attribute is the clue thatgives us the ability to find the relationships between different relations by joiningthe relations together. If the common attribute is not unique, the relationshipinformation is not preserved. If each tuple had a unique value of Date-enrolled, the