Formal relational query languages

     

    Formal relational query languages are mathematical languages used to define and manipulate data stored in relational databases. The two primary formal relational query languages are Relational Algebra and Relational Calculus.

    Relational Algebra

     

    Relational Algebra is a procedural query language that uses a set of operations to manipulate relations (tables). It provides a formal foundation for SQL and other relational database query languages. The primary operations in relational algebra include:

    1. Selection (σ): Selects rows from a relation that satisfy a given predicate.

      • Syntax: σ<sub>condition</sub>(Relation)
      • Example: σ<sub>age > 30</sub>(Employees) selects all employees older than 30.

    2. Projection (π): Selects specific columns from a relation.

      • Syntax: π<sub>columns</sub>(Relation)
      • Example: π<sub>name, age</sub>(Employees) selects only the name and age columns from the Employees relation.

    3. Union (∪): Combines the tuples of two relations, removing duplicates.

      • Syntax: Relation1 ∪ Relation2
      • Example: Employees ∪ Managers gives a relation containing all employees and managers, with duplicates removed.

    4. Set Difference (−): Returns tuples that are in one relation but not in another.

      • Syntax: Relation1 − Relation2
      • Example: Employees − Managers gives a relation containing employees who are not managers.

    5. Cartesian Product (×): Combines tuples from two relations in a pairwise manner.

      • Syntax: Relation1 × Relation2
      • Example: Employees × Departments combines each employee with each department.

    6. Rename (ρ): Renames the relation or its attributes.

      • Syntax: ρ<sub>new_name</sub>(Relation)
      • Example: ρ<sub>Emp</sub>(Employees) renames the Employees relation to Emp.

    7. Intersection (∩): Returns tuples that are in both relations.

      • Syntax: Relation1 ∩ Relation2
      • Example: Employees ∩ Managers gives a relation containing employees who are also managers.

    8. Join: Combines related tuples from two relations.

      • Natural Join (⋈): Combines tuples with matching attribute values.
        • Syntax: Relation1 ⋈ Relation2
        • Example: Employees ⋈ Departments combines employees with their corresponding departments based on common attributes.
      • Theta Join (⋈<sub>condition</sub>): Combines tuples based on a specified condition.
        • Syntax: Relation1 ⋈<sub>condition</sub> Relation2
        • Example: Employees ⋈<sub>Employees.dept_id = Departments.dept_id</sub> Departments combines employees with their departments based on the department ID.

    Relational Calculus

     

    Relational Calculus is a non-procedural query language that describes what to retrieve rather than how to retrieve it. There are two types of relational calculus:

    1. Tuple Relational Calculus (TRC): Specifies the desired tuples using a tuple variable.

      • Syntax: {t | P(t)}, where t is a tuple variable and P(t) is a predicate formula.
      • Example: {t | t ∈ Employees ∧ t.age > 30} retrieves all tuples t from the Employees relation where the age is greater than 30.

    2. Domain Relational Calculus (DRC): Specifies the desired attributes using domain variables.

      • Syntax: {<a1, a2, …, an> | P(a1, a2, …, an)}, where a1, a2, …, an are domain variables and P is a predicate formula.
      • Example: {<name, age> | ∃e (e ∈ Employees ∧ e.name = name ∧ e.age = age ∧ age > 30)} retrieves the name and age of employees who are older than 30.

    Differences between Relational Algebra and Relational Calculus

    • Nature:

      • Relational Algebra is procedural, specifying a sequence of operations to obtain the result.
      • Relational Calculus is non-procedural, specifying the properties of the desired result.

    • Use Cases:

      • Relational Algebra is used to understand and optimize query execution.
      • Relational Calculus provides a foundation for declarative query languages like SQL, focusing on the specification of what results are desired.

     

    Summary

     

    Formal relational query languages provide a rigorous foundation for querying and manipulating data in relational databases. Relational Algebra focuses on a sequence of operations to transform relations, while Relational Calculus specifies the properties of the result without detailing the steps to achieve it. Both languages are essential for understanding the theory behind modern SQL-based query languages.