Formal Relational Query Languages Notes

Relational Query Languages

Languages Overview

  • Relational Algebra: Procedural language for querying databases.
  • Tuple Relational Calculus: Non-procedural query language.
  • Domain Relational Calculus: Also non-procedural, focuses on domain variables.

Key Concepts

  • Query languages are not programming languages and do not perform complex computations.
  • Designed for easy access to data in relational databases.

Relational Algebra Operators

  • Operators: Select (σ), Project (π), Union (∪), Set Difference (−), Cartesian Product (×), Rename (ρ).
  • Composition: Operators can be combined to form complex queries.

Operations Explained

  • Select (σ):
    • Filters rows based on a condition (predicate).
    • Example: σ sex='M' (Students).
  • Project (π):
    • Removes columns from a relation.
    • Example: π name, age (Students).
  • Union (∪):
    • Combines two relations with the same schema.
    • Example: π name (Students) ∪ π name (Teachers).
  • Set Difference (−):
    • Returns tuples in one relation that are not in another.
    • Example: Students − Graduated_Students.
  • Cartesian Product (×):
    • Combines all tuples from two relations.
  • Rename (ρ):
    • Assigns a new name to a relation for easier reference.

Additional Operations

  • Natural Join (⋈): Combines related tuples from two relations on common attributes.
  • Outer Join: Retrieves records even if there are no matches.
  • Division: Used for queries involving "for all" conditions.

Safety and Null Values

  • SQL expressions must ensure that results do not create infinite tuples (safety).
  • Null values represent unknown data. Their treatment involves strict rules in operations.

Views**: Define virtual tables based on queries, allowing for controlled access to data.

  • Updatable views face constraints regarding mutations in underlying tables.

Sample Queries

  • Basic Tuple Queries: Formatted as {t | P(t)}, conveying a set of tuples where predicate P holds true.
    • Example: {t | t ∈ Students ∧ t[age] > 18} selects all students older than 18.
  • Find All Instructors with Salary > $80,000: {t | ∃s ∈ instructor (t[ID] = s[ID] ∧ s[salary] > 80000)}
  • Check for Students in All Biology Courses: {t | ∃r ∈ student (t[ID] = r[ID]) ∧ (∀u ∈ course (u[deptname] = "Biology" → ∃s ∈ takes (t[ID] = s[ID] ∧ s[courseid] = u[course_id])))}