Relational Algebra

4.1 Background

• Considered: structural properties and constraints
• Want to consider: foundations of database operations

Types of Operations

• Query: mapping of a database into a relation
• Update: mapping between databases

Origin of Operations

• From set theory
• Specifically developed for relational databases

The Approach

• Relational algebra:
  • A procedural language
  • Based on algebraic concepts

• Algebra:
  • Studies quantities
  • Refers to relations and properties by symbols

4.2 Set Theory Operations

• Union ($A\cup B$)
• Intersection ($A\cap B$)
• Difference ($A-B$)
• Defined only for relations over identical attributes

4.3 Renaming (${\rho }_{S\left(B_1,...,B_n\right)}\left(R\right)$)

• The relation name from $R$ to $S$: ${\rho }_S\left(R\right)$
• The attributes to $B_1$,...,$B_n$: ${\rho }_{\left(B_1,...,B_n\right)}\left(R\right)$
• Both: ${\rho }_{S\left(B_1,...,B_n\right)}\left(R\right)$

Use

4.4 Assignments ($S\left(B_1,...,B_n\right)\leftarrow R$)

The renaming operation under alternative notation

4.5 Selection ${\sigma }_{<selectioncondition>}\left(R\right)$

The condition must be a propositional formula

Clauses

of the operations $\left\{=,<,\le ,>,\ge ,\ne \right\}$ on attributes

Boolean operations

AND, OR, NOT

An attribute with unordered domain allows only the comparison operations $=,\ne$.

4.6 Projection (${\pi }_{<attributelist>}\left(R\right)$)

• The selection operation chooses tuples (rows of tables)
• The projection operation chooses attributes (columns of tables)

Identical tuples collapse into a single tuple

4.7 Cross Join ($R\times S$)

Also called Cartesian product and cross product. Combines the attributes of the given relations, in all possible ways.

$R\left(A_1,...,A_n\right)\times S\left(B_1,...,B_m\right)=Q\left(A_1,...,A_n,B_1,...,B_m\right)$

Application

• Combine related information with the cross join operation

  Instructor $\times$ Administrator

  Name Number Adm Num Don 3412 Dean 3412 Don 3412 Secretary 4123 Nel 2341 Dean 3412 Nel 2341 Secretary 4123

• Extract tuples with the selection operation

  $Temp\leftarrow {\sigma }_{Number=Num}$ (Instructor $\times$ Administrator)

  Name Number Adm Num Don 3412 Dean 3412

• Weed out attributes with the projection operation

  ${\pi }_{Name,Number,Adm}\left(Temp\right)$

  Name Number Adm Don 3412 Dean

4.8 Join ($R{\bowtie }_{<joincondition>}S$)

$R\left(A_1,...,A_n\right){\bowtie }_{<condition>}S\left(B_1,...,B_m\right)=Q\left(A_1,...,A_n,B_1,...,B_m\right)$

• Cross product operation restricted to combinations which satisfy the stated conditions
  • Cross product includes all possible combinations of tuples
  • Join = cross product + selection

• Called equi-join if only relational operations ‘=’ are in use.

Application

Variant of the previous example:

4.9 Natural Join (R$\bowtie$S)

• Natural-join = equi-join + projection
• The join checks for equality of values of common attributes
• The projection collapses together the common attributes

  Instructor Name Number Don 3412 Nel 2341 Administrator Adm Number Dean 3412 Secretary 4123

  Instructor $\bowtie$ Administrator Name Number Adm Don 3412 Dean

• When the operands have no attribute in common, the natural join operations reduces to the join cross (i.e., Cartesian product) operation

4.10 Outer Joins

• A join operation is complete, if all the tuples of the operands contribute to the result.
• Tuples not participating in the result are said to be dangling
• Outer join operations are variants of the join operations in which the dangling tuples are appended with NULL fields. They can be categorized into left, right, and full outer joins.

  Instructor ⟗ Administrator

  Name Number Adm Don 3412 Dean Nel 2341 NULL NULL 4123 Secretary

  Instructor ⟕ Administrator

  Name Number Adm Don 3412 Dean Nel 2341 NULL

4.11 Division ($R÷S$)

$R\left(Z\right)÷S\left(X\right)$ equals maximal $T\left(Z-X\right)$ which satisfies $T\left(Z-X\right)\times S\left(X\right)\subseteq R\left(Z\right)$

4.12 A Complete Set of Operations

• A subset of the relational algebra operations is complete, if it can express any relational algebra operation
• $\left\{\sigma ,\pi ,\cup ,-,\times \right\}$ is complete for relational algebra
• The extra operations are provided for convenience
• The outer join operations, and the following operations, can’t be expressed in terms of the basic relational algebra operations

Example: $A\cap B=A\cup B-\left(\left(A-B\right)\cup \left(B-A\right)\right)$

4.13 Global Aggregate Functions

Aggregate Functions (${\Im }_{<functionlist>}\left(R\right)$)

Mathematical functions on collections of values from the database: SUM, AVERAGE, MAXIMUM, COUNT

Student Name Number Sex Ben 3412 M Dan 1234 M Nel 2341 F

${\Im }_{COUNT}\left(Student\right)$ Count 3

Grouping (${}_{<groupingattributes>}{\Im }_{<functionlist>}\left(R\right)$)

of tuples in a relation by the value of some of their attributes

${}_{Sex}{\Im }_{COUNT,SUM(Number)}\left(Student\right)$

Sex Count SUM M 2 4646 F 1 2341

[Problem 6.21]

4.14 Assignment #2

Due: Th, Oct 23

Q&A

Reference: Ch. 5.3, 6.1–6.5 in textbook.