Desirable properties of decomposition are 1 Attribute preservation 2 Lossless

Desirable properties of decomposition are 1 attribute

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 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
Image of page 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
Image of page 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
Image of page 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
Image of page 152
Image of page 153

You've reached the end of your free preview.

Want to read all 169 pages?

  • Spring '15
  • Alexander
  • J UMA MAHESH

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture