dependency preservation
DESCRIPTION
Dependency Preservation. By Jason Allen. Why Do We Preserve The Dependency?. We would like to check easily that updates to the database do not result in illegal relations being created. It would be nice if our design allowed us to check updates without having to compute natural joins. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Dependency Preservation](https://reader033.vdocuments.us/reader033/viewer/2022051117/56815af1550346895dc8ae69/html5/thumbnails/1.jpg)
By Jason Allen
![Page 2: Dependency Preservation](https://reader033.vdocuments.us/reader033/viewer/2022051117/56815af1550346895dc8ae69/html5/thumbnails/2.jpg)
Why Do We Preserve The Dependency?
We would like to check easily that updates to the database
do not result in illegal relations being created.
It would be nice if our design allowed us to check updates
without having to compute natural joins.
![Page 3: Dependency Preservation](https://reader033.vdocuments.us/reader033/viewer/2022051117/56815af1550346895dc8ae69/html5/thumbnails/3.jpg)
Definition
A decomposition D = {R1, R2, ..., Rn} of R is
dependency-preserving with respect to F if
the union of the projections of F on each Ri in
D is equivalent to F; that is
if (F1 F2 … Fn )+ = F +
![Page 4: Dependency Preservation](https://reader033.vdocuments.us/reader033/viewer/2022051117/56815af1550346895dc8ae69/html5/thumbnails/4.jpg)
In Layman’s Term
Each Functional Dependency specified in
F
either appears directly in one of the
relations in the decomposition.
![Page 5: Dependency Preservation](https://reader033.vdocuments.us/reader033/viewer/2022051117/56815af1550346895dc8ae69/html5/thumbnails/5.jpg)
Continue…It is not necessary that all dependencies from
the relation R appear in some relation Ri. It is sufficient that the union of the dependencies on all the relations Ri be equivalent to the dependencies on R.
![Page 6: Dependency Preservation](https://reader033.vdocuments.us/reader033/viewer/2022051117/56815af1550346895dc8ae69/html5/thumbnails/6.jpg)
Property of Dependency-PreservationIf a decomposition is not dependency-preserving,
therefore, that dependency is lost in the
decomposition.
FD1
FD2
FD3
FD4
![Page 7: Dependency Preservation](https://reader033.vdocuments.us/reader033/viewer/2022051117/56815af1550346895dc8ae69/html5/thumbnails/7.jpg)
Example of Dependency PreservationR(A B C D)
FD1: A B
FD2: B C
FD3: C D
Decomposition:R1(A B C) R2(C D)
![Page 8: Dependency Preservation](https://reader033.vdocuments.us/reader033/viewer/2022051117/56815af1550346895dc8ae69/html5/thumbnails/8.jpg)
FD1: A BFD2: B CFD3: C D
FD1
FD2
R1( A B C )
![Page 9: Dependency Preservation](https://reader033.vdocuments.us/reader033/viewer/2022051117/56815af1550346895dc8ae69/html5/thumbnails/9.jpg)
FD1: A BFD2: B CFD3: C D
FD3
R2( C D )
![Page 10: Dependency Preservation](https://reader033.vdocuments.us/reader033/viewer/2022051117/56815af1550346895dc8ae69/html5/thumbnails/10.jpg)
FD1: A B
FD2: B C
FD3: C D
R1( A B C ) R2( C D )
Has all 3 functional dependencies!Therefore, it’s preserving the dependencies
FD1
FD2
FD3
![Page 11: Dependency Preservation](https://reader033.vdocuments.us/reader033/viewer/2022051117/56815af1550346895dc8ae69/html5/thumbnails/11.jpg)
Example of Non-Dependency Preservation
R(A B C D)
FD1: A B
FD2: B C
FD3: C D
Decomposition:R1(A C D) R2(B C)
![Page 12: Dependency Preservation](https://reader033.vdocuments.us/reader033/viewer/2022051117/56815af1550346895dc8ae69/html5/thumbnails/12.jpg)
FD1: A BFD2: B CFD3: C D
FD3
R1( A C D )
![Page 13: Dependency Preservation](https://reader033.vdocuments.us/reader033/viewer/2022051117/56815af1550346895dc8ae69/html5/thumbnails/13.jpg)
FD1: A BFD2: B CFD3: C D
FD2
R2( B C )
![Page 14: Dependency Preservation](https://reader033.vdocuments.us/reader033/viewer/2022051117/56815af1550346895dc8ae69/html5/thumbnails/14.jpg)
FD1: A B
FD2: B C
FD3: C D
R1( A C D ) R2( B C )
Does not support FD1: A => BTherefore, it does not preserve the dependencies
FD3 FD2
![Page 15: Dependency Preservation](https://reader033.vdocuments.us/reader033/viewer/2022051117/56815af1550346895dc8ae69/html5/thumbnails/15.jpg)
More Example
R(A B C D E)
FD1: A B
FD2: BC D
Decomposition:R1(A C E) R2(B C D) R3(A B)
![Page 16: Dependency Preservation](https://reader033.vdocuments.us/reader033/viewer/2022051117/56815af1550346895dc8ae69/html5/thumbnails/16.jpg)
FD1: A BFD2: BC D
R1( A C E )
No Dependencies
![Page 17: Dependency Preservation](https://reader033.vdocuments.us/reader033/viewer/2022051117/56815af1550346895dc8ae69/html5/thumbnails/17.jpg)
FD1: A BFD2: BC D
FD2
R2( B C D )
![Page 18: Dependency Preservation](https://reader033.vdocuments.us/reader033/viewer/2022051117/56815af1550346895dc8ae69/html5/thumbnails/18.jpg)
FD1: A BFD2: BC D
FD1
R3( A B )
![Page 19: Dependency Preservation](https://reader033.vdocuments.us/reader033/viewer/2022051117/56815af1550346895dc8ae69/html5/thumbnails/19.jpg)
FD1: A B
FD2: BC D
R1( A C E ) R2( B C D )
Has all 2 functional dependencies!Therefore, it’s preserving the dependencies
FD1
FD2
R3( A B )
![Page 20: Dependency Preservation](https://reader033.vdocuments.us/reader033/viewer/2022051117/56815af1550346895dc8ae69/html5/thumbnails/20.jpg)
Exercise Problem
![Page 21: Dependency Preservation](https://reader033.vdocuments.us/reader033/viewer/2022051117/56815af1550346895dc8ae69/html5/thumbnails/21.jpg)
R(A, B, C, D, E, F )
FD1: D A, B
FD2: C E, F
Decomposition:R1( A, C, D )R2( A, D, B ) R3( D, E, F )R4( C, E, F )
![Page 22: Dependency Preservation](https://reader033.vdocuments.us/reader033/viewer/2022051117/56815af1550346895dc8ae69/html5/thumbnails/22.jpg)
R1( A C D )
R2( A D B )
R3( D E F )
R4( C E F )
FD1
FD2
FD1: D A, B FD2: C E, F
![Page 23: Dependency Preservation](https://reader033.vdocuments.us/reader033/viewer/2022051117/56815af1550346895dc8ae69/html5/thumbnails/23.jpg)
Answer
Yes! This is a dependency-preservation
![Page 24: Dependency Preservation](https://reader033.vdocuments.us/reader033/viewer/2022051117/56815af1550346895dc8ae69/html5/thumbnails/24.jpg)
Reference
Chen, Y. (2005). “Decomposition”. Retrieved on March 21, 2010 from http://www.cs.sjsu.edu/faculty/lee/cs157/Decomposition_YuHungChen.ppt
Kamel, A. “Chapter 11Relational Database Design Algorithms” Retrieved on March 22, 2010 from http://www.cord.edu/faculty/kamel/08F-330/Presentations/ch11.pdf
Lee, S. “Huffman code and Lossless Decomposition”. Retrieved on March 21, 2010 from http://www.cs.sjsu.edu/~lee/cs157b/29SpCS157BL14HuffmanCode&LosslessDecomposition.ppt
Zaiane, O. (1998) “Dependency Preservation”. Retrieved on March 21, 2010 from http://www.cs.sfu.ca/CC/354/zaiane/material/notes/Chapter7/node8.html