1 / 25

Lecture 10: Relational Algebra

Lecture 10: Relational Algebra. Relational Algebra. A formalism for creating new relations from existing ones Its place in the big picture:. Declarative query language. Algebra. Implementation. Relational algebra Relational bag algebra. SQL, relational calculus. Relational Algebra.

jerry
Download Presentation

Lecture 10: 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. Lecture 10: Relational Algebra

  2. Relational Algebra • A formalism for creating new relations from existing ones • Its place in the big picture: Declarativequerylanguage Algebra Implementation Relational algebraRelational bag algebra SQL,relational calculus

  3. Relational Algebra • Five operators: • Union:  • Difference: - • Selection: s • Projection: P • Cartesian Product:  • Derived or auxiliary operators: • Intersection, complement • Joins (natural, equi-join, theta join, semi-join) • Renaming: r

  4. 1. Union and 2. Difference • R1  R2 • Example: • ActiveEmployees  RetiredEmployees • R1 – R2 • Example: • AllEmployees – RetiredEmployees

  5. What about Intersection ? • It is a derived operator • R1  R2 = R1 – (R1 – R2) • Also expressed as a join (will see later) • Example • UnionizedEmployees  RetiredEmployees

  6. 3. Selection • Returns all tuples which satisfy a condition • Notation: sc(R) • Examples • sSalary > 40000 (Employee) • sname = “Smith” (Employee) • The condition c can be =, <, , >,, <>

  7. Find all employees with salary more than $40,000. sSalary > 40000(Employee)

  8. 4. Projection • Eliminates columns, then removes duplicates • Notation: PA1,…,An(R) • Example: project social-security number and names: • PSSN, Name (Employee) • Output schema: Answer(SSN, Name)

  9. PSSN, Name (Employee)

  10. 5. Cartesian Product • Each tuple in R1 with each tuple in R2 • Notation: R1  R2 • Example: • Employee  Dependents • Very rare in practice; mainly used to express joins

  11. Relational Algebra • Five operators: • Union:  • Difference: - • Selection: s • Projection: P • Cartesian Product:  • Derived or auxiliary operators: • Intersection, complement • Joins (natural, equi-join, theta join, semi-join) • Renaming: r

  12. Renaming • Changes the schema, not the instance • Notation: rB1,…,Bn (R) • Example: • rGivenName, SocSecNo(Employee) • Output schema: Answer(GivenName, SocSecNo)

  13. Renaming Example Employee Name SSN John 999999999 Tony 777777777 • GivenName, SocSecNo (Employee) GivenName SocSecNo John 999999999 Tony 777777777

  14. Natural Join • Notation: R1 ⋈ R2 • Meaning: R1 ⋈ R2 = PA(sC(R1  R2)) • Where: • The selection sC checks equality of all common attributes • The projection eliminates the duplicate common attributes

  15. Natural Join Example Employee Name SSN John 999999999 Tony 777777777 Dependents SSN Dname 999999999 Emily 777777777 Joe Employee ⋈ Dependents = PName, SSN, Dname(sSSN=SSN2(Employee rSSN2, Dname(Dependents))) Name SSN Dname John 999999999 Emily Tony 777777777 Joe

  16. Natural Join • R= S= • R ⋈ S=

  17. Natural Join • Given the schemas R(A, B, C, D), S(A, C, E), what is the schema of R ⋈ S ? • Given R(A, B, C), S(D, E), what is R ⋈ S ? • Given R(A, B), S(A, B), what is R ⋈ S ?

  18. Theta Join • A join that involves a predicate • R1 ⋈qR2 = sq(R1  R2) • Here q can be any condition

  19. Equi-join • A theta join where q is an equality • R1 ⋈A=BR2 = sA=B(R1  R2) • Example: • Employee ⋈SSN=SSN Dependents • Most useful join in practice

  20. Semijoin • R ⋉ S = PA1,…,An (R ⋈ S) • Where A1, …, An are the attributes in R • Example: • Employee ⋉ Dependents

  21. Semijoins in Distributed Databases • Semijoins are used in distributed databases Dependents Employee network Employee ⋈ssn=ssn (sage>71 (Dependents)) T = PSSNs age>71 (Dependents) R = Employee ⋉ T Answer = R ⋈ Dependents

  22. Complex RA Expressions Pname ⋈buyer-ssn=ssn ⋈pid=pid ⋈seller-ssn=ssn Pssn Ppid sname=`Fred’ sname=gizmo Person Purchase Person Product

  23. Operations on Bags A bag = a set with repeated elements All operations need to be defined carefully on bags • {a,b,b,c}{a,b,b,b,e,f,f}={a,a,b,b,b,b,b,c,e,f,f} • {a,b,b,b,c,c} – {b,c,c,c,d} = {a,b,b,d} • sC(R): preserve the number of occurrences • PA(R): no duplicate elimination • Cartesian product, join: no duplicate elimination • New operator for Duplicate Elimination Relational Engines work on bags, not sets ! Reading assignment: 5.1-5.2

  24. Finally: RA has Limitations • Cannot compute “transitive closure” • Find all direct and indirect relatives of Fred • Cannot be expressed in RA!

More Related