Relational Algebra Cheat Sheet

Relational algebra is a procedural query language used to query relational databases. It defines a set of operations that act on one or more relations (tables) to produce new relations as results, forming the theoretical basis for SQL.

Core Principles

Relational algebra operations act on relations and produce relations.
It's a foundational concept for SQL and query optimization.
Operations include selection, projection, union, intersection, difference, Cartesian product, join, and division.
Each operation has a specific syntax and use case for data retrieval and manipulation.
Combining operations allows for complex query formulation.

Action Steps

Identify the data needed from the database.
Determine the relevant relations (tables) containing the data.
Choose appropriate relational algebra operators to filter, select, and combine data.
Construct the query by combining operators sequentially or hierarchically.
Apply selection (σ) to filter rows based on conditions.
Use projection (π) to select specific columns and remove duplicates.
Employ set operations (Union, Intersection, Difference) for combining or comparing sets of tuples.
Utilize join operations to combine related rows from different relations.
Consider division (÷) for 'all' type queries.
Verify the final query logic against the desired outcome.

Formulas

$ \sigma_{condition}(Relation) $
$ \pi_{(column1, column2, ...)}(Relation) $
$ Relation1 \cup Relation2 $
$ Relation1 \cap Relation2 $
$ Relation1 - Relation2 $
$ Relation1 \times Relation2 $
$ Relation1 \Join Relation2 $
$ Relation1 \div Relation2 $

Key Terms

Relation: A table in a relational database, consisting of rows (tuples) and columns (attributes).
Tuple: A single row in a relation.
Attribute: A column in a relation.
Selection (σ): Retrieves rows that satisfy a given condition.
Projection (π): Retrieves specific columns and removes duplicate rows.
Union (∪): Combines rows from two relations, removing duplicates.
Intersection (∩): Returns rows common to both relations.
Difference (-): Returns rows present in the first relation but not in the second.
Cartesian Product (×): Combines every row from one relation with every row from another.
Join: Combines rows from two relations based on a related column or condition.
Division (÷): Finds tuples in one relation that are related to all tuples in another relation.