1 / 27

Relational Algebra

Relational Algebra. NEU – CCIS – CSU430 Tony. Content. Set-theoretic operations Native relational operations Other join operations. 1. Set-theoretic operations. Union Intersection Difference Product. R. S. S. A. B. C. A. A. B. B. D. C. a1. b1. c1. a1. a1. b1. b1. c1.

dyllis
Download Presentation

Relational Algebra

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. Relational Algebra NEU – CCIS – CSU430 Tony

  2. Content • Set-theoretic operations • Native relational operations • Other join operations

  3. 1. Set-theoretic operations • Union • Intersection • Difference • Product

  4. R S S A B C A A B B D C a1 b1 c1 a1 a1 b1 b1 c1 d1 a1 b2 c2 a1 a1 b2 b2 c2 d2 1.0 Compatible tables • Tables R and S are compatible if they have the same headings.

  5. R U S A B C R S A B C a1 A B b1 C c1 a1 b1 c1 a1 a1 b1 b2 c1 c2 a1 b2 c2 a1 a1 b2 b2 c3 c3 1.1 Union (only for compatible tables)

  6. R n S A B C R S A B C a1 A b1 B C c1 a1 b1 c1 a1 b1 c1 a1 b2 c2 a1 b2 c3 1.2 Intersection (only for compatible tables)

  7. R - S A B C R S A B C a1 A b2 B C c2 a1 b1 c1 a1 b1 c1 a1 b2 c2 a1 b2 c3 1.3 Difference (only for compatible tables)

  8. R x S R.A R.B S.B S.D a1 b1 b1 d1 a1 b1 b1 d3 a1 b2 b1 d1 a1 b2 b1 d3 1.4 Product R A B a1 b1 a1 b2 S B D b1 d1 b1 d3

  9. 2. Native relational operations • Projection • Selection • Join • Division

  10. 2.1 Projection • For a table T with the headings [A1,…,An], the projection R on attributes [A1,…,Ak], where [A1,…,Ak] belongs to (or equal to) [A1,…,An], is a table with the headings [A1,…,Ak] and all the corresponding fields values from T.

  11. Name Name Client Staff_ID Tony Tony Amanda 001 Robbie Robbie Smith 002 Robbie Lily 003 2.1 Projection (cont) T Staff_ID Name Position Client 001 Tony Manager Amanda 002 Robbie Agent Smith 003 Robbie Agent Lily R := T[Safff_ID] R:= T[Name,Client] R:= T[Name]

  12. 2.2 Selection • Given a table T with headings [A1,…,An], the selection (T where C) is a table S that includes all the records in T that satisfy the condition C. • Example: Find the employee whose name is “Robbie”.

  13. Staff_ID Name Position Client 002 Robbie Agent Smith 003 Robbie Agent Lily Staff_ID Name Position Client 002 Robbie Agent Smith 2.2 Selection (cont) T Staff_ID Name Position Client 001 Tony Manager Amanda 002 Robbie Agent Smith 003 Robbie Agent Lily T where name = ‘Robbie’ T where staff_id = 002

  14. 2.3 Join • For two tables R and S with the headings: Head(R) = A1… An B1 … Bk Head(S) = B1… Bk C1 … Cm The join of R and S is a table T with the headings: Head(R) = A1… An B1 … Bk C1 … Cm. A row t is T is and only if there two rows u in R and v in S, such that u[Bi] = v[Bi] for all i, 1 ≤ i ≤ k;

  15. R lxl S A B1 B2 C a1 b1 b1 c1 a1 b1 b1 c2 a2 b1 b2 c3 2.3 Join (cont) A B1 B2 a1 b1 b1 R a1 b2 b1 a2 b1 b2 B1 B2 C b1 b1 c1 S b1 b1 c2 b1 b2 c3 b2 b2 c4

  16. 2.4 Division • For two tables R and S with the headings: Head(R) = A1… An B1 … Bk Head(S) = B1… Bk • The division R ÷ S is a table T with the headings: Head(R) = A1… An • T contains exactly those rows t such that for every row s in S, the row resulting from concatenating t and s can be found in table R. • Division is the inverse of the product operation.

  17. 2.4 Division (cont) • R÷S = R[A1..An] – ((R[A1..An] X S) – R)[A1..An] Detail steps: • Step 1 T1 := R[A1..An] (Projection) • Step 2 T2 := T1 X S (Product) • Step 3 T3 := T2 – R (Difference) • Step 4 T4 := T3[A1..An] (Projection) • Step 5 T5 := T1 – T4 (Difference)

  18. 2.4 Division Example R S A B C C a1 b1 c1 c1 a2 b1 c1 c2 a1 b2 c1 a1 b2 c2 a2 b1 c2 a1 b2 c3 R ÷ S = ??? a1 b2 c4 a1 b1 c5

  19. T1 := R[A1..An] A B a1 b1 a2 b1 a1 b2 2.4 Division Example – step 1 R A B C a1 b1 c1 a2 b1 c1 a1 b2 c1 a1 b2 c2 a2 b1 c2 a1 b2 c3 a1 b2 c4 a1 b1 c5

  20. T2 := T1 X S A B C a1 b1 c1 a1 b1 c2 a2 b1 c1 a2 b1 c2 a1 b2 c1 a1 b2 c2 2.4 Division Example – step 2 T1 A B a1 b1 a2 b1 a1 b2 S C c1 c2

  21. A B C a1 b1 c2 2.4 Division Example – step 3 R T3 := T2 - R T2 A B C A B C a1 b1 c1 a1 b1 c1 a1 b1 c2 a2 b1 c1 a2 b1 c1 a1 b2 c1 a2 b1 c2 a1 b2 c2 a1 b2 c1 a2 b1 c2 a1 b2 c2 a1 b2 c3 a1 b2 c4 a1 b1 c5

  22. 2.4 Division Example – step 4 T3 T4 := T3[A1..An] A B C A B a1 b1 c2 a1 b1

  23. A B A B a2 b1 a1 b1 a1 b2 2.4 Division Example – step 5 T1 T4 T5 := T1 – T4 A B a1 b1 a2 b1 a1 b2 R ÷ S = T5

  24. 3. Other join operations • Outer join • Left outer join • Right outer join

  25. R lxlo S A B1 B2 C a1 b1 b1 c1 R lxl S a1 b1 b1 c2 A B1 B2 C a2 b1 b2 c3 a1 b1 b1 c1 a1 b2 b1 null a1 b1 b1 c2 null b2 b2 c4 a2 b1 b2 c3 3.1 Outer join A B1 B2 a1 b1 b1 R a1 b2 b1 a2 b1 b2 B1 B2 C b1 b1 c1 S b1 b1 c2 b1 b2 c3 b2 b2 c4

  26. R left outer join S A B1 B2 C a1 b1 b1 c1 a1 b1 b1 c2 a2 b1 b2 c3 a1 b2 b1 null 3.1 Left outer join A B1 B2 a1 b1 b1 R a1 b2 b1 a2 b1 b2 B1 B2 C b1 b1 c1 S b1 b1 c2 b1 b2 c3 b2 b2 c4

  27. R right outer join S A B1 B2 C a1 b1 b1 c1 a1 b1 b1 c2 a2 b1 b2 c3 null b2 b2 c4 3.1 Right outer join A B1 B2 a1 b1 b1 R a1 b2 b1 a2 b1 b2 B1 B2 C b1 b1 c1 S b1 b1 c2 b1 b2 c3 b2 b2 c4

More Related