Relational Algebra

Example schema will be used for examples

• Relations: Movies(mID, title, director, year, length); Artists(aID, aName, nationality); Roles(mID, aID, character)
• Foreign key constraints: Roles[mID]\(\subseteq\) Movies[mID]; Roles[aID]\(\subseteq\) Artists[aID]

Relational algebra

• The value of any expression is a relation
• Assumptions
  • Relations as sets (without duplicated rows)
  • Every cell has a value
• Operands: tables
• Operator examples:
  • Choose only the rows wanted
  • Choose only the columns wanted
  • combine tables

Operators

Select Rows

\(\sigma_c(R)\): \(R\) table, \(c\) boolean expression

• The result is a relation with the same schema but with only the tuples satisfy \(c\)
• Select all British actors \(\sigma_{\text{nationality = 'British'}}(Actors)\)
• Select all movies from 1970s \(\sigma_{1970\leq year\leq 1979}(Movies)\)

Project

\(\Pi_c(R)\) slice vertically

• onto fewer attributes can remove key that makes duplicates possible, whenever duplicates happens, only one copy of each is kept
• To perform multiple query together Example: find the names of all directors of movies from the 1970s \(\pi_{director}(\sigma_{1970 <year<=1979}(Movies))\)

Cartesian Product

\(R_1\times R_2\) map two relations to a new relation with every combination of a tuple from \(R_1\) concatenated to a tuple from \(R_2\)

• Resulted schema is every attribute from \(R_1\) followed by \(R_2\) in order
• The resulted relation have \(R_1.cardinality|\times R_2.cardinality\) tuples

Natural join

\(R_1\bowtie R_2\) take the Cartesian product and select rows with the same attribute and value that are in both relation to ensure equality on attributes, then project to remove duplicate attributes

• Natural join is commutative and associative

Theta Join

\(R_1\bowtie_{c} R_2:= \sigma_c{R\times S}\)

Assignment

\(R:= Expression\) or \(R(A_1,...,A_n):=Expression\), the second way allows to rename all the attributes

• \(R\) must be temporary and not one of the relations in the schema, it should not be updated

Rename

\(\rho_{R_1}(R_2)\) or \(\rho_{R_1(A_1,...,A_n)}(R_2)\) renames the relation. Note that \(R_1:=\rho_{R_1(A_1,...,A_n)}(R_2)\) is equivalent to \(R_1(A_1,...,A_n):=R_2\)

Division

\(R/S:=\) the largest relation \(Q\) s.t. \(Q\times S\subseteq R\). the operation will return a relation will all the attributes in \(R\) that's not in \(S\) and all tuples in \(R\) that match every tuple in \(S\)