Lecture 10 Relational Algebra 1 Relational Algebra A

  • Slides: 23
Download presentation
Lecture 10: Relational Algebra 1

Lecture 10: Relational Algebra 1

Relational Algebra • A formalism for creating new relations from existing ones • Its

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

Relational Algebra • Five operators: – – – Union: Difference: Selection: s Projection: P

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 3

1. Union and 2. Difference • R 1 R 2 • Example: – Active.

1. Union and 2. Difference • R 1 R 2 • Example: – Active. Employees Retired. Employees • R 1 – R 2 • Example: – All. Employees – Retired. Employees 4

What about Intersection ? • • It is a derived operator R 1 R

What about Intersection ? • • It is a derived operator R 1 R 2 = R 1 – (R 1 – R 2) Also expressed as a join (will see later) Example – Unionized. Employees Retired. Employees 5

3. Selection • Returns all tuples which satisfy a condition • Notation: sc(R) •

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

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

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

4. Projection • Eliminates columns, then removes duplicates • Notation: PA 1, …, An

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

P SSN, Name (Employee) 9

P SSN, Name (Employee) 9

5. Cartesian Product • Each tuple in R 1 with each tuple in R

5. Cartesian Product • Each tuple in R 1 with each tuple in R 2 • Notation: R 1 R 2 • Example: – Employee Dependents • Very rare in practice; mainly used to express joins 10

11

11

Relational Algebra • Five operators: – – – Union: Difference: Selection: s Projection: P

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: r B 1, …,

Renaming • Changes the schema, not the instance • Notation: r B 1, …, Bn (R) • Example: – r. Given. Name, Soc. Sec. No (Employee) – Output schema: Answer(Given. Name, Soc. Sec. No) 13

Renaming Example Employee Name John Tony r. Given. Name, Soc. Sec. No Given. Name

Renaming Example Employee Name John Tony r. Given. Name, Soc. Sec. No Given. Name John Tony SSN 99999 77777 (Employee) Soc. Sec. No 99999 77777 14

Natural Join • Notation: R 1 ⋈ R 2 • Meaning: R 1 ⋈

Natural Join • Notation: R 1 ⋈ R 2 • Meaning: R 1 ⋈ R 2 = PA(s. C(R 1 R 2)) • Where: – The selection s. C checks equality of all common attributes – The projection eliminates the duplicate common attributes 15

Natural Join Example Employee Name John Tony SSN 99999 77777 Dependents SSN 99999 77777

Natural Join Example Employee Name John Tony SSN 99999 77777 Dependents SSN 99999 77777 Dname Emily Joe Employee ⋈ Dependents = PName, SSN, Dname(s SSN=SSN 2(Employee r. SSN 2, Dname(Dependents))) Name John Tony SSN 99999 77777 Dname Emily Joe 16

Natural Join • R= • R ⋈ S= A B X S= B C

Natural Join • R= • R ⋈ S= A B X S= B C Y Z U X Z V W Y Z Z V A B C X Z U X Z V Y Z U Y Z V W 17

Natural Join • Given the schemas R(A, B, C, D), S(A, C, E), what

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 • R 1 ⋈q R

Theta Join • A join that involves a predicate • R 1 ⋈q R 2 = sq (R 1 R 2) • Here q can be any condition 19

Equi-join • A theta join where q is an equality • R 1 ⋈A=B

Equi-join • A theta join where q is an equality • R 1 ⋈A=B R 2 = s. A=B (R 1 R 2) • Example: – Employee ⋈SSN=SSN Dependents • Most useful join in practice 20

Semijoin • R ⋉ S = PA 1, …, An (R ⋈ S) •

Semijoin • R ⋉ S = PA 1, …, An (R ⋈ S) • Where A 1, …, An are the attributes in R • Example: – Employee ⋉ Dependents 21

Complex RA Expressions P name ⋈buyer-ssn=ssn ⋈pid=pid ⋈seller-ssn=ssn Ppid sname=`Fred’ s name=gizmo Person Purchase

Complex RA Expressions P name ⋈buyer-ssn=ssn ⋈pid=pid ⋈seller-ssn=ssn Ppid sname=`Fred’ s name=gizmo Person Purchase Person Product 22

Operations on Bags A bag = a set with repeated elements All operations need

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, c, e, f, f} • {a, b, b, b, c, c} – {b, c, c, c, d} = {a, b, b} • s. C(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 23