1 / 19

Join Dependencies—Example

Join Dependencies—Example. Let r = A B C = A B | | A C 1 a x 1 a 1 x 1 a y 1 b 1 y 1 b x 2 a 2 y 1 b y 2 b 2 a y 2 b y.

nelson
Download Presentation

Join Dependencies—Example

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Join Dependencies—Example Let r = A B C = A B || A C 1 a x 1 a 1 x 1 a y 1 b 1 y 1 b x 2 a 2 y 1 b y 2 b 2 a y 2 b y Observe: r = AB r ||AC r This always holds when we build r by joining relationship sets in this way. In general, however, if we arbitrarily create a relation, this may not happen. Add <2, a, x> to r, for example, then r AB r ||AC r because the join also yields <2, b, x>, which is not in r. Note: r is the cross product of B and C wrt A.

  2. Join Dependencies—Example Let r = A B C  A B || A C 1 a x 1 a 1 x 1 a y 1 b 1 y 1 b x 2 a 2 y 1 b y 2 b 2 x 2 a y 2 b y 2 a x Observe: r = AB r ||AC r This always holds when we build r by joining relationship sets in this way. In general, however, if we arbitrarily create a relation, this may not happen. Add <2, a, x> to r, for example, then r AB r ||AC r because the join also yields <2, b, x>, which is not in r. Note: r is the cross product of B and C wrt A.

  3. Join Dependencies—Example Note: All circled values are redundant.* Let r = A B C = A B || A C 1 a x 1 a 1 x 1 a y 1 b 1 y 1 b x 2 a 2 y 1 b y 2 b 2 a y 2 b y Observe: r = AB r ||AC r This always holds when we build r by joining relationship sets in this way. In general, however, if we arbitrarily create a relation, this may not happen. Add <2, a, x> to r, for example, then r AB r ||AC r because the join also yields <2, b, x>, which is not in r. Note: r is the cross product of B and C wrt A. *A value v in a relation is redundant if, from the constraints and other data values in the relation, v can only have the value it has.

  4. Join Dependencies—Example Note: All circled values are redundant. Let r = A B C  A B || A C 1 a x 1 a 1 x 1 a y 1 b 1 y 1 b x 2 a 2 y 1 b y 2 b 3 x 2 a y 3 a 2 b y Observe: r = AB r ||AC r This always holds when we build r by joining relationship sets in this way. In general, however, if we arbitrarily create a relation, this may not happen. Add <2, a, x> to r, for example, then r AB r ||AC r because the join also yields <2, b, x>, which is not in r. 3 Doesn’t work Note: r is the cross product of B and C wrt A.

  5. Join Dependencies—Example Note: All circled values are redundant. Let r = A B C  A B || A C 1 a x 1 a 1 x 1 a y 1 b 1 y 1 b x 2 a 2 y 1 b y 2 b 1 z 2 a y 2 b y Observe: r = AB r ||AC r This always holds when we build r by joining relationship sets in this way. In general, however, if we arbitrarily create a relation, this may not happen. Add <2, a, x> to r, for example, then r AB r ||AC r because the join also yields <2, b, x>, which is not in r. z Doesn’t work Note: r is the cross product of B and C wrt A.

  6. Join Dependencies—Example Note: All circled values are redundant. Let r = A B C  A B || A C 1 a x 1 a 1 x 1 a y 1 b 1 y 1 b x 2 a 2 y 1 b y 2 b 2 z 2 a y 2 b y Observe: r = AB r ||AC r This always holds when we build r by joining relationship sets in this way. In general, however, if we arbitrarily create a relation, this may not happen. Add <2, a, x> to r, for example, then r AB r ||AC r because the join also yields <2, b, x>, which is not in r. Doesn’t work z Note: r is the cross product of B and C wrt A.

  7. Join Dependencies—Example Note: All circled values are redundant. Let r = A B C = A B || A C 1 a x 1 a 1 x 1 a y 1 b 1 y 1 b x 2 a 2 y 1 b y 3 b 3 y 2 a y 2 b y Observe: r = AB r ||AC r This always holds when we build r by joining relationship sets in this way. In general, however, if we arbitrarily create a relation, this may not happen. Add <2, a, x> to r, for example, then r AB r ||AC r because the join also yields <2, b, x>, which is not in r. Does work 3 Note: r is the cross product of B and C wrt A.

  8. Join Dependencies—Example Note: All circled values are redundant. Let r = A B C = A B || A C 1 a x 1 a 1 x 1 a y 1 b 1 y 1 b x 2 a 2 y 1 b y 2 c 2 a y 2 b y Observe: r = AB r ||AC r This always holds when we build r by joining relationship sets in this way. In general, however, if we arbitrarily create a relation, this may not happen. Add <2, a, x> to r, for example, then r AB r ||AC r because the join also yields <2, b, x>, which is not in r. Does work c Note: r is the cross product of B and C wrt A.

  9. Join Dependencies—Definitions • A join dependency (JD) denoted ||(R1, …, Rn) holds for a relation r(R) if r = R1r || … ||Rnr. (e.g., ||(AB, AC) in the previous example) • When n = 2, we call a JD a Multivalued Dependency (MVD) and write X Y or X Z or X Y | Z where X = R1  R2, Y = R1  R2, and Z = R2  R1. (e.g., A B or A C or A B | C) • Note: every FD is an MVD (e.g., if A  B in a relational schema ABC, then ||(AB, AC)).            

  10. B 4NF A C • Let R be a relation scheme and let D be a set of MVDs and FDs over R. R is in Fourth Normal Form (4NF) with respect to D if for every nontrivial MVD X Y  D+, X is a superkey of R. • An MVD is trivial if Y  X or XY = R. • Example: • R = ABC & D = {A B} = {A B|C} = {||(AB, AC)} • No FDs, thus the key is ABC • A B is nontrivial and A is not a superkey; thus ABC is not in 4NF • Decompose: AB, AC (both are in 4NF wrt D)        

  11. B 4NF A C • Let R be a relation scheme and let D be a set of MVDs and FDs over R. R is in Fourth Normal Form (4NF) with respect to D if for every nontrivial MVD X Y  D+, X is a superkey of R. • An MVD is trivial if Y  X or XY = R. • Example: • R = ABC & D = {A  B} ~ {A B|C} = {||(AB, AC)} • Since A  B is an FD, the key is AC • A B is a nontrivial FD and a nontrivial MVD and A is not a superkey; thus ABC is not in 4NF • Decompose: AB, AC (both are in 4NF wrt D)      

  12. B 4NF A C • Let R be a relation scheme and let D be a set of MVDs and FDs over R. R is in Fourth Normal Form (4NF) with respect to D if for every nontrivial MVD X Y  D+, X is a superkey of R. • An MVD is trivial if Y  X or XY = R. • Example: • R = ABC & D = {A  B, A  C} ~ {A B|C} = {||(AB, AC)} • The key is A • Both A  B and A  C are nontrivial FDs and nontrivial MVDs, but A is a key of R • Thus ABC is in 4NF    

  13. B 4NF A C • Let R be a relation scheme and let D be a set of MVDs and FDs over R. R is in Fourth Normal Form (4NF) with respect to D if for every nontrivial MVD X Y  D+, X is a superkey of R. • An MVD is trivial if Y  X or XY = R. • Example: • R = ABC • If a nontrivial MVD X Y holds for R and X is not a superkey, R is not in 4NFdecompose • Otherwise, R is in 4NFdon’t decompose Note that the real question here is whether an n-ary relationship set can be losslessly decomposed into two or more relationship sets.    

  14. N-ary Relationship-Set Reduction Guest Date Room View = Guest Date Room || Room View G1 10 May R1 Sea G1 10 May R1 R1 Sea G1 10 May R1 Forest G1 15 May R2 R1 Forest G1 15 May R2 Forest G2 15 May R1 R2 Forest G2 15 May R1 Sea G2 15 May R1 Forest

  15. Non-reducible N-ary Edge r = Guest Date Room G1 10 May R1 G1 15 May R2 G2 15 May R1 r  p || q r  q || s r  p || s r  p || q || s p = Guest Date q = Date Room s = Guest Room G1 10 May 10 May R1 G1 R1 G1 15 May 15 May R2 G1 R2 G2 15 May 15 May R1 G2 R1

  16. 4NF/5NF/PJNF • 4NF definition Let R be a relation scheme and let D be a set of MVDs and FDs over R. R is in Fourth Normal Form (4NF) with respect to D if for every nontrivial MVD X Y  D+, X is a superkey of R. • 4NF definition rewritten so that it can be generalized Let R be a relation scheme and let D be a set of MVDs and FDs over R (join dependencies over R, JDs over R). R is in Fourth Normal Form (4NF) with respect to D if for every nontrivial JD ||(R1, R2) such that R = R1R2, both R1 and R2 are superkeys of R. ||(XY, XZ)  

  17. 4NF/5NF/PJNF • 4NF definition rewritten so that it can be generalized Let R be a relation scheme and let D be a set of MVDs and FDs over R (join dependencies over R, JDs over R). R is in Fourth Normal Form (4NF) with respect to D if for every nontrivial JD ||(R1, R2) such that R = R1R2, both R1 and R2 are superkeys of R. • 5NF definition Let R be a relation scheme and let D be a set of JDs over R. R is in Fifth Normal Form (5NF) with respect to D if for every nontrivial JD ||(R1, R2, R3) such that R = R1R2R3 every Ri is a superkey of R.

  18. 4NF/5NF/PJNF • 5NF definition Let R be a relation scheme and let D be a set of JDs over R. R is in Fifth Normal Form (5NF) with respect to D if for every nontrivial JD ||(R1, R2, R3) such that R = R1R2R3 every Ri is a superkey of R. • PJNF definition Let R be a relation scheme and let D be a set of JDs over R. R is in Project-Join Normal Form (PJNF) with respect to D if for every nontrivial JD ||(R1, …, Rn) such that R = R1…Rn every Ri is a superkey of R.

  19. Normalization into PJNF • Decompose wrt JDs • The only real question is whether an n-ary relationship set can be losslessly decomposed into two or more relationship sets. • If so, decompose • When all relationship sets are decomposed: • A database schema consisting of these relationship sets is in PJNF. • Based on the FDs, some of the schemas can be joined without introducing redundancy.

More Related