Relational Algebra and Calculus

Chapter 8: The Relational Algebra and Calculus

1. Relational Languages
  • Relational Algebra (ALG): Introduced by E.F. Codd in 1970.
  • Tuple Relational Calculus (TRC): Also put forth by E.F. Codd.
  • Domain Relational Calculus (DRC): Developed by Michel Lacroix and Alain Pirotte in 1977.
2. Example Queries
  • Find specific information:
    • John’s age
    • Students taking courses
    • Students not enrolled in any courses
    • Students taking DB course
    • Students enrolled in all courses
    • Students taking all courses except AL
    • Students enrolled in the same courses as Kate
    • Students taking exactly two courses.
3. Brief History of Algebra
  • Origin from the Arabic word al-jabr.
  • Focus on manipulating mathematical symbols through defined operations.
  • Operators combine simple symbols (operands) into complex operations to achieve minimal representations of cases.
4. Fundamental Concepts of Operators
  • Operands: The values or variables upon which operations are performed.
  • Operators: Functions that act on one or two relations:
    • Unary Operators: Act on a single relation (e.g., select, project).
    • Binary Operators: Act on pairs of relations (e.g., union, intersect).
  • Relational Algebra Properties: Algebra is closed; every operation produces another relation.
5. Types of Relational Algebra Operators

  • Unary Relational Operators:

    • Select (σ)
    • Project (π)
    • Rename (ρ)

  • Set Operations Based on Set Theory:

    • Union (∪)
    • Intersection (∩)
    • Minus (−)
    • Cross Product (Cartesian Product) (×)

  • Binary Relational Operators:

    • Join (⨝)
    • DivideBy (/)

  • Additional Operators:

    • Outer join, Outer union
    • Aggregate functions: SUM, COUNT, AVG, MIN, MAX
6. Select Operator (σ)
  • Definition: Selects tuples from a relation based on a specific condition.
  • Syntax: σ(R)
  • Example:
    • Find students with age > 20: σ(age > 20)(Student)
  • Properties:
    • Result has the same attributes as the original relation.
    • Number of tuples is less than or equal to the original relation.
    • Commutative nature allows reordering of conditions in selection.
    • Multiple selections can be combined into a single selection using conjunction.
7. Project Operator (π)
  • Definition: Creates a vertical partition of a relation keeping the specified attributes and removes duplicates.
  • Syntax: π(R)
  • Example:
    • List names and ages: π(sname, age)(Student)
  • Properties:
    • If the list includes a key attribute, the result may have fewer tuples than in R.
8. Rename Operator (ρ)
  • Definition: Renames the relation or its attributes without modifying the actual data.
  • Syntax: ρ(S(A1,…, An))(R)
9. Set Operations
  • Union (∪): Combines tuples from both relations, removing duplicates.
  • Intersection (∩): Tuples present in both relations.
  • Minus (−): Tuples in one relation but not in the other.
  • Properties:
    • Both union and intersection operations are commutative and associative.
    • Minus operator is non-commutative.
10. Join Operations
  • Definition: Combines tuples from two relations based on a related attribute.
  • Types:
    • Theta Join: Combines results based on a general condition.
    • Equijoin: A specific case of theta join where condition uses equalities only.
    • Natural Join: Eliminates duplicate columns from the result.
11. Outer Joins
  • Definition: Prevents the loss of information by including unmatched tuples.
  • Types include left outer join, right outer join, and full outer join.
12. Aggregate Functions
  • Perform calculations on a set of values and return a single value.
  • Common functions: SUM, AVG, COUNT, MIN, MAX.
  • Example: Count of students, average age, and maximum mark queries.
13. Relational Calculus
  • Tuple Relational Calculus (TRC): Uses tuple variables to query relations.
  • Domain Relational Calculus (DRC): Operates on domains rather than tuples.
  • Key Features: No operators, focuses on set definitions and logic.
14. Quantifiers in TRC
  • Existential Quantifier (∃): Indicates that there exists at least one tuple satisfying the condition.
  • Universal Quantifier (∀): All tuples must satisfy the condition.
15. Examples and Applications
  • Practical implementations of algebra and calculus in database operations.
  • Uses of joins, aggregates, and other operations to efficiently retrieve and manipulate data.