Database Query Language

What is a "query"?

• OED: a question, especially one addressed to an official or organization
• Database jargon for question (complex word for simple concept)

Relational Query Language

• Formal: relational algebra, relational calculus, datalog
• Practical: SQL, Quel, QBE
• Both input to and output from a query are relations: makes "piping" possible
• A query is executed against input relations and produces an output relation

• Set semantics: no duplicate tuples. Duplicates are automatically eliminated
  • Multiset semantics for SQL for performance reasons.

Relational Algebra

• Select σ

  • As the name suggest, it selects tuples that satisfy a give predicate
  • σ dept_name ="Physics"∧salary>6000
  • and (∧), or (∨), and not (¬)

• Project Π

  • a unary operation that returns its argument relation
  • "display only the things we want instead of all"
  • It's not useful to compose 2 projection operators next to each other

• The Cartesian-Product Operation ×

  • allows us to combine information from any two relations

    

• Natural Join Operator ⋈

  • Shorthand for σStudent.sid=Enroll.sid (Student × Enroll)

  • Concatenate tuples horizontally

  • Enforce equality on common attributes

  • We may assume only one copy of the common attributes are kept

    

  • if there are no common attributes, this will word as a cross product

• Rename operator ρ

  

• Union Operator U

  • R U S: Union of tuples from R and S
  • The schemas of R and S should be the same
  • No duplicate tuples in the result

• Set Difference Operator —

  • R — S: tuples in R that do not exist in S
  • The schemas of R and S should be the same