1 / 49

RELATIONAL ALGEBRA (III)

CS157A. RELATIONAL ALGEBRA (III). Prof. Sin-Min LEE Department of Computer Science. Lecture 10. Unary Relational Operations: SELECT and PROJECT. The PROJECT Operation Sequences of Operations and the RENAME Operation The SELECT Operation. Relational Algebra Operations from Set Theory.

shaina
Download Presentation

RELATIONAL ALGEBRA (III)

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. CS157A RELATIONAL ALGEBRA (III) Prof. Sin-Min LEE Department of Computer Science Lecture 10

  2. Unary Relational Operations: SELECT and PROJECT • The PROJECT Operation • Sequences of Operations and the RENAME Operation • The SELECT Operation

  3. Relational Algebra Operations from Set Theory • The UNION, INTERSECTION, and MINUS Operations • The CARTESIAN PRODUCT (or CROSS PRODUCT) Operation

  4. Binary Relational Operations: JOIN and DIVISION • The JOIN Operation • The EQUIJOIN and NATURAL JOIN Variations of JOIN • A Complete Set of Relational Algebra Operations • The DIVISION Operation

  5. Additional Relational Operations • Aggregate Functions and Grouping • Recursive Closure Operations • OUTER JOIN Operations • The OUTER JOIN Operation

  6. SPECIAL RELATIONAL OPERATORS The following operators are peculiar to relations: - Join operators There are several kind of join operators. We only consider three of these here (others will be considered when we discuss null values): - (1) Condition Joins - (2) Equijoins - (3) Natural Joins - Division

  7. JOIN OPERATORS Condition Joins: - Defined as a cross-product followed by a selection: R ⋈cS = σc(R  S) (⋈ is called the bow-tie) where c is the condition. - Example: Given the sample relational instances S1 and R1 The condition join S ⋈S1.sid<R1.sid R1 yields

  8. JOIN OPERATORS Condition Joins: - Defined as a cross-product followed by a selection: R ⋈cS = σc(R  S) (⋈ is called the bow-tie) where c is the condition. - Example: Given the sample relational instances S1 and R1 The condition join S ⋈S1.sid<R1.sid R1 yields

  9. Equijoin: Special case of the condition join where the join condition consists solely of equalities between two fields in R and S connected by the logical AND operator (∧). Example: Given the two sample relational instances S1 and R1 The operator S1 R.sid=Ssid R1 yields

  10. Natural Join - Special case of equijoin where equalities are implicitly specified on all fields having the same name in R and S. - The condition c is now left out, so that the “bow tie” operator by itself signifies a natural join. - N. B. If the two relations have no attributes in common, the natural join is simply the cross-product.

  11. Functional Dependency a®b holds on schema R if, in any legal relation r(R), for all pairs of tuples t1 and t2 in r such that t1[a] = t2[a], it is also the case that t1 [b] = t2 [b].

  12. Functional Dependencies R • FDs defined over two sets of attributes: X, Y Ì R • Notation: X à Y reads as “X determines Y” • If X à Y, then all tuples that agree on X must also agree on Y X Y Z 1 2 3 2 4 5 1 2 4 1 2 7 2 4 8 3 7 9

  13. A®C, but not C®A

  14. Minimal cover: AB, DB{A, B, C, D} is a candidate key

  15. Functional Dependencies Graph(example) X Y Z X Y Z 1 2 3 2 4 5 1 2 4 1 2 7 2 4 8 3 7 9

  16. Closure • Let F be a set of functional dependencies. • The closure of F, denoted by F+, is the set of all functional dependencies logically implied by F.

  17. Minimal cover • The concept of minimal cover of F is sometimes called Irreducibe Set of F. To find the minimal cover of a set of functional dependencies F, we transform F such that each FD in it that has more than one attribute in the right hand side is reduced to a set of FDs that have only one attribute on the right hand side.

  18. The minimal cover of F is then a set of FDs such that: • (a) every right hand side of each dependency is a single attribute; • (b) for no X -> A in F is the set F - {X -> A} equivalent to F; • (c) for no X -> A in F and proper subset Z of X is F - {X -> A} U {Z -> A} equivalent to F.

  19. ALGORITHM. Finding a minimal cover G for F 1. set G := F; 2. replace each functional dpendency X->A1,A2,...,An in G by the n functional dependencies X->A1,X->A2,...,X->An; 3. for each functional dependency X -> A in G for each attribute B that is an element of X {if G is equivalent to ((G - (X->A)) UNION ((X-B)->A)) then replace X->A with (X-B)->A in G} 4. for each remaining functional dependency X -> A in G {compute X+ with respect to the set of dependencies (G - (X->A)); if X+ contains A, then remove X->A from G} Note: In step 3 to determine if G is equivalent to ((G - (X->A)) UNION ((X-B)->A)) you need to see if (X-B)+ in G contains A. If it does then they are equivalent.

  20. R(A,B,C,D) F={AB->CD} Following algorithm: 2. G={AB->C,AB->D} 3. a) Try to replace AB->C with B->C: {AB->C,AB->D} is not equivalent to {B->C,AB->D} Note that B+ wrt (with respect to) G ={B}. Since it does not contain C, they are not equivalent. b) Try to replace AB->C with A->C: {AB->C,AB->D} is not equivalent to {A->C,AB->D} Note that A+ wrt G ={A}. Since it does not contain C, they are not equivalent. c) Try to replace AB->D with B->D: {AB->C,AB->D} is not equivalent to {AB->C,B->D} Note that B+ wrt G ={B}. Since it does not contain D, they are not equivalent.

  21. d) Try to replace AB->D with A->D: {AB->C,AB->D} is not equivalent to {AB->C,A->D} Note that A+ wrt G ={A}. Since it does not contain D, they are not equivalent. Therefore, we can not make any changes to G in this step. 4. a) Try to remove AB->C: We can do this if G is equivalent to H={AB->D} However, AB+ wrt G = {A,B,C,D} <> AB+ wrt H = {A,B,D} b) Try to remove AB->D: We can do this if G is equivalent to I={AB->C} However, AB+ wrt G = {A,B,C,D} <> AB+ wrt I = {A,B,C} Therefore we can not make any changes to G in this step. Therefore, F is a minimal cover.

  22. R={A,B,C,D,E,F} G={AB->C,B->CD,D->EF,B->F} ALTERNATIVE I (Synthesis Approach - p 422 in Ramakrishnan) Place into Minimal Cover (p420 in Ramakrishnan Book): 1) G1={AB->C,B->C,B->D,D->E,D->F,B->F} 2) Remove extra attributes on LHS; AB->C: Can remove A as B+ in G1 does contain C. Thus we get G2={B->C,B->D,D->E,D->F,B->F} Don't need to look at remaining FD because all only have 1 attribute on LHS.

  23. 3) Remove extra FD from G2: B->C: Can't be removed since B+ would then not contain C. No other FD in G2 have C on RHS. B->D: Can't be removed since B+ would then not contain D. No other FD in G2 have D on RHS. D->E: Can't be removed since D+ would then not contain E. No other FD in G2 have E on RHS. D->F: Can't be removed since D+ would not contain F. B->F: Can be removed since B->D,D->F. Thus we have the Minimal Cover: G3={B->C,B->D,D->E,D->F} We now decompose. We get: R1={B,C,D} R2={D,E,F}

  24. Note that A is not in either scheme, also we need to add another scheme because neither of these contain a candidate key. Since A,B are not on the RHS any candidate key must contain them. AB+={A,B,C,D,E,F}=R. Thus AB is the key. Since no scheme contains AB, we must add one more scheme: R3={A,B} So we have: R1={B,C,D}, F1={B->C,B->D} R2={D,E,F}, F2={D->E,D->F} R3={A,B}, F3={} Note that this is dependency preserving as F1 union F2 = G3. Is this lossless? (See p 414 in Ramakrishnan) R1 intersect R2 = {D} and D-> R2 (R1 union R2) intersect R3 = {B} and B->{B,C,D,E,F} Therefore this is lossless

  25. ALTERNATIVE II (Decomposition Approach - p 421 in Ramakrishnan) Decompose R using BCNF Decomposition (p416 in Ramakrishnan) From above we know that AB is the key. So we need to look at any FD in F where the LHS is not a superkey. We have the following that are not superkeys: {B->CD,D->EF,B->F} Decompose using B->CD into: R1={A,B,E,F}, R2={B,C,D} F1={B->EF}, F2={B->CD} R2 is in BCNF, but R1 is not. So we split R1 into: R6={A,B}, R7{B,E,F} F6={}, F7={B->EF} We put F into minimal cover: G3={B->C,B->D,D->E,D->F}

  26. The following functional dependencies are not preserved: {D->E,D->F} So we create a relation scheme for each of these: R3={D,E}, R4={D,F} F3={D->E}, F4={D->F} Combining R3 and R4, we get: R6={A,B}, F1={} R7={B,E,F}, F1={B->EF} R2={B,C,D}, F2={B->CD} R5={D,E,F}, F5={D->EF} This is dependency preserving and lossless.

  27. Candidate Keys • an attribute (or set of attributes) that uniquely identifies a row • primary key is a special candidate key • values cannot be null • e.g. • ENROLL (Student_ID, Name, Address, …) • PK = Student_ID • candidate key = Name, Address

  28. … candidate key • a candidate key must satisfy: • unique identification. • implies that each nonkey attribute is functionally dependent on the key (for not(A  B) to be true, A must occur more than once (with a different B), or A must map to more than one B in a given row) • nonredundancy • no attribute in the key can be deleted and still be unique • minimal set of columns (Simsion)

  29. keys and dependencies EMPLOYEE1 (Emp_ID, Name, Dept_Name, Salary) determinant functional dependency

  30. EMPLOYEE2 (Emp_ID, Course_Title, Name, Dept_Name, Salary, Date_Completed) not fully functionally dependant on the primary key

  31. Trivial Functional Dependency • In general, a functional dependency of the form a®b is trivial if bÍa • (Example) A®B, BC®C

More Related