Relational Algebra Overview

Study Notes

Relational algebra is the fundamental set of operations for the relational model
These operations allow users to define basic retrieval requests (queries)
The outcome of an operation is a new relation, potentially derived from one or more input relations
This "closure property" means all elements in relational algebra are relations

Relational Algebra Operations (Unary and Binary Operations, additional operations and examples of queries in relational algebra)
Relational Calculus (Tuple and Domain Relational Calculus)
Example Database Application (COMPANY database)
Overview of the QBE language (appendix D)

Relational algebra consists of various operations for retrieving data
These include unary operations (affecting a single relation) and binary operations (affecting two relations)
Unary operations:
- SELECT ($\sigma$): Selects a subset of tuples from a relation based on a condition
- PROJECT ($\pi$): Selects specific attributes from a relation, removing duplicate tuples
- RENAME ($\rho$): Renames relations or attributes
Binary operations:
- UNION ($\cup$): Combines tuples from two compatible relations, eliminating duplicates
- INTERSECTION ($\cap$): Returns tuples that exist in both compatible relations
- DIFFERENCE (-): Returns tuples in the first relation but not the second
- CARTESIAN PRODUCT ($\times$): Combines all possible pairings of tuples from two relations
- JOIN: Combines tuples from two relations based on a join condition (various types exist)
Additional operations:
- OUTER JOINS (combines all tuples from one or both relations in the output, filling missing values with NULLs
- AGGREGATE FUNCTIONS (SUM, AVERAGE, MAXIMUM, MINIMUM, COUNT): Perform calculations on values from multiple tuples

The SELECT operation ($\sigma$) selects a subset of tuples from a relation based on a condition.
The condition is a Boolean expression involving attributes.
Tuples that satisfy the condition are included in the output; others are discarded.
The operation output is a relation with the same schema as the input.
SELECT is commutative

The PROJECT operation ($\pi$) selects specific attributes from a relation.
The output contains only the specified attributes, excluding duplicates.
The result is a relation where all tuples are unique (according to the selected attributes)
PROJECT is not commutative

It allows nesting operations to combine them into a single expression or execute them stepwise creating intermediate result relations.
Intermediate relation names need to be defined if operations are done step wise

Show how to retrieve data from a relational database using a single relational algebra expression or stepwise with intermediate relations

Explains how to use multiple operations with intermediate results renaming attributes and relations

The UNION operation ($\cup$) combines tuples from two compatible relations; duplicates are eliminated
Resulting relation shares the attribute names from the first relation (by convention)
Compatible relations have the same number of attributes and correspondingly compatible domains

The INTERSECTION operation ($\cap$) returns tuples that are present in both of the compatible relations.
The result attribute names would be of first relation by convention

The SET DIFFERENCE operation (-) returns tuples from the first relation, but not from the second relation.
The result attribute names would be of first relation by convention
Compatible relations are those that have the same number of attributes and compatible domains

A JOIN operation combines tuples from two or more relations based on the common attributes' values present in those relations

A type of join operation where the join conditions use only equality comparisons between attributes.

A NATURAL JOIN operation simplifies the EQUIJOIN by automatically using the common attributes' names for the join condition.

An OUTER JOIN returns all tuples from one of the relations, even if there's no matching tuple in the other relation.
Results may include null values for attributes from the relation that does not have a matching tuple.

Functions that summarize data like SUM, AVERAGE, MAXIMUM, MINIMUM, and COUNT are grouped for retrieval

Enables grouping result with aggregate function such as COUNT, AVERAGE and SUM for various attributes

Demonstrates how to express database queries using relational algebra in both procedural and single-expression forms. Providing examples of finding specific data within specific departments or employees with dependencies

Practical examples of relational algebra queries demonstrating the concepts learned (retrieving data associated with specific departments or employees)