CSCI 343 Relational operator notes

Relational algebra is one of the most abstract areas of computer science. It is a theory based on mathematical logic, formal algebra, and set theory. It also forms the foundations for SQL, Structured Query Language.

A course like MATH 280, Foundations of Mathematics, would be very useful for understanding formal aspects of the relational algebra.

What is a relation?

A relation consists a attribute set and a collection of tuples drawn from values. Values may be integers, strings, etc. The tuple is a collection of values. Formally, the tuple is a function from attributes to values.

The relation corresponds to the table of database theory while the tuple is similar to the row or record and the attribute is similar to the column or field.

Notation for Relational Algebra

• Logical operators as written in the style of mathematical logic, in particular propositional calculus, rather than the style of digital logic.
  • logical and: ∧
  • logical or: ∨
  • logical not: ¬
• Relational operators
  • selection: σ_condition
    • condition is formula in the propositional calculus roughly equivalent to a Java-expression with simple variables and constants.
  • projection: π_{attributes ...}
    • attributes... are the attributes being projected.
  • product: ×
    • A Cartesian product. Care must be taken when relations share an attribute name. In fact, many relational algebra theorists do not allow relations with shared attributes to be used with ×.
  • renaming: ρ_{new-attribute ← old-attribute ...}
    • Renames attributes. There are other ways to write the renaming. It is a useful operator to use before taking the product of two relations with stared attribute names.
  • natural join: ⋈
    • This is a binary operation on relations. It is like a Cartesian product, but paired tuples must match on values with common attribute names. It can be implemented with ρ, × and σ.
  • theta join: ⋈_condition
    • S ⋈_θ T is σ_θ(S × T)
• Set operators should be used on relations with the same attribute sets
  • union: ∪
    • In the relational algebra, relations are sets. Consequently tuples appearing in both relations only appear once in the union. This is not the case with SQL.
  • difference: −
  • intersection: ∩
• A nice reference from Universität Tübingen

Reading

You can read about relational algebra from the Wikipedia page or the Chapter 2: “The Relational Model of Data” of Database Systems: The Complete Book by Hector Garcia-Molina, Jeffrey D. Ullman and Jennifer Widom.

The relation model was first described in E. F. Codd’s paper “A relational model of data for large storage objects” which was published by Communications of the ACM in July 1970. (Available from UNC Asheville library.)

Software

Jun Yang has write RA: A Relational Algebra Interpreter. The program interfaces with sqlite3 to provide a relational algebra programming experience.