lecture 10 relational algebra n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Lecture 10: Relational Algebra PowerPoint Presentation
Download Presentation
Lecture 10: Relational Algebra

Loading in 2 Seconds...

play fullscreen
1 / 25

Lecture 10: Relational Algebra - PowerPoint PPT Presentation


  • 276 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Lecture 10: Relational Algebra' - jerry


Download Now 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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
relational algebra
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

relational algebra1
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
1 union and 2 difference
1. Union and 2. Difference
  • R1  R2
  • Example:
    • ActiveEmployees  RetiredEmployees
  • R1 – R2
  • Example:
    • AllEmployees – RetiredEmployees
what about intersection
What about Intersection ?
  • It is a derived operator
  • R1  R2 = R1 – (R1 – R2)
  • Also expressed as a join (will see later)
  • Example
    • UnionizedEmployees  RetiredEmployees
3 selection
3. Selection
  • Returns all tuples which satisfy a condition
  • Notation: sc(R)
  • Examples
    • sSalary > 40000 (Employee)
    • sname = “Smith” (Employee)
  • The condition c can be =, <, , >,, <>
4 projection
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)
5 cartesian product
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
relational algebra2
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
renaming
Renaming
  • Changes the schema, not the instance
  • Notation: rB1,…,Bn (R)
  • Example:
    • rGivenName, SocSecNo(Employee)
    • Output schema: Answer(GivenName, SocSecNo)
renaming example
Renaming Example

Employee

Name

SSN

John

999999999

Tony

777777777

  • GivenName, SocSecNo (Employee)

GivenName

SocSecNo

John

999999999

Tony

777777777

natural join
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
slide16

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

natural join1
Natural Join
  • R= S=
  • R ⋈ S=
natural join2
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 ?
theta join
Theta Join
  • A join that involves a predicate
  • R1 ⋈qR2 = sq(R1  R2)
  • Here q can be any condition
equi join
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
semijoin
Semijoin
  • R ⋉ S = PA1,…,An (R ⋈ S)
  • Where A1, …, An are the attributes in R
  • Example:
    • Employee ⋉ Dependents
semijoins in distributed databases
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

complex ra expressions
Complex RA Expressions

Pname

⋈buyer-ssn=ssn

⋈pid=pid

⋈seller-ssn=ssn

Pssn

Ppid

sname=`Fred’

sname=gizmo

Person

Purchase

Person

Product

operations on bags
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

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