Relational Algebra Flashcards

Relational Algebra

• Comprises a set of operations producing a new relation from one or two input relations.
• Unary operations: select, project, rename (operate on one relation).
• Binary operations: union, Cartesian product, set difference (operate on pairs of relations).
• Relations are sets of tuples with no duplicates, but relational algebra can accommodate multisets (with duplicates).

Select Operation (Unary)

• Denoted by lowercase Greek letter sigma (σ).
• Selects tuples that meet a specified condition (predicate).
• Supports comparisons: =, ≠, <, ≤, >, ≥.
• Combines multiple predicates using and (∧), or (∨), not (¬).

Project Operation

• Represented by the uppercase Greek letter pi (Π).
• Returns a relation with selected attributes while omitting others.
• Basic projection allows only attribute names; generalized version permits expressions involving attributes.

Composition of Relational Operations

• Results from relational operations remain relations, allowing for composition into complex expressions.
• Example: Finding instructor names in the Physics department using combined select and project operations.

Cartesian Product Operation (Binary)

• Denoted by a cross (×).
• Combines information from two relations by concatenating their tuples.
• Requires distinct names for input relations to avoid attribute name conflicts.

Join Operation (Binary)

• Combines Cartesian product with selection to link related data.
• Denoted by ⋈; specifically connects two relations based on shared attributes.
• Example: Fetching instructor information along with their taught course IDs based on a matching condition.

Set Operation 1 - Union Operation

• Denoted by ∪, finds the union of tuples from two compatible relations.
• Eliminates duplicates during the union process.
• Example: Courses taught in Fall 2017 and Spring 2018 semesters.

Union Compatibility

• Input relations must have the same number of attributes (arity).
• Corresponding attributes must match in type and number.

Set Operation 2 - Intersection Operation

• Denoted by ∩, finds tuples common to both input relations.
• Compatibility rules apply, similar to union operation.

Set Operation 3 - Set-Difference Operation

• Denoted by −, finds tuples in one relation but absent in another.
• Requires compatible relations for set difference.

Assignment Operation

• Allows temporary variable assignment for relational algebra expressions.
• Evaluation does not display relations directly but designates the results to the variable.

Rename Operation

• Denoted by lowercase Greek letter rho (ρ), allows naming of relational-algebra expressions.
• Facilitates referencing the same relation multiple times with unique identifiers.

Cartesian Product with Rename Operation

• Demonstrates a complex query where an instructor’s salary is compared across two scans of the instructor relation, using unique names to differentiate each instance.

Equivalent Queries

• Describes two different queries that yield the same result.
• Illustrates optimization in database systems where query execution efficiency is prioritized over following a specified step sequence.