Tuple Relational Calculus (TRC) in DBMS

---

---

Apart from Relational Algebra, there are some other techniques for querying, such as Relational Calculus. There are two types of relational calculus −

• Tuple relational calculus
• Domain relational calculus

Tuple Relational Calculus is a powerful query language. Unlike procedural languages like Relational Algebra, Tuple Relational Calculus is nonprocedural. It focuses on what to retrieve rather than detailing how to retrieve it. This distinction makes Tuple Relational Calculus relevant for understanding logical data retrieval.

In this chapter, we will have a look at the basics of Tuple Relational Calculus, understand its fundamental components, and go through some examples. We will also highlight the differences between Tuple Relational Calculus and other query methods.

Tuple Relational Calculus

Tuple Relational Calculus is a declarative approach. It is used for querying relational databases. It uses mathematical logic to define queries. This is specifying the desired outcome without prescribing the steps to achieve it.

Queries in Tuple Relational Calculus take the form −

$$\mathrm{\{t\: |\: COND(t)\}}$$

Here −

• t is a tuple variable, representing rows in a table.
• COND(t) is a conditional expression, evaluated as either TRUE or FALSE for each tuple.

The query result is the set of tuples for which the condition COND(t) evaluates to TRUE.

Key Components of Tuple Relational Calculus

Following are the key components of Tuple Relational Calculus –

Tuple Variables and Range Relations − The tuple variables represent rows in relations. This is ranging over specific tables. For instance, if t is a tuple variable for the EMPLOYEE table, then EMPLOYEE(t) limits t to rows within the EMPLOYEE table.

Conditions (COND) − Conditions are logical expressions built using −

• Attribute comparisons − t.A > 50000, Name = 'John'
• Logical operators − AND, OR, NOT
• Quantifiers − Existential (∃) and universal (∀) quantifiers

Attributes to Retrieve − The result of a Tuple Relational Calculus query depends on the attributes specified before the condition. For instance, the query −

$$\mathrm{\{t.Fname,\: t.Lname\: | \:EMPLOYEE(t)\: AND\: t.Salary\: >\: 50000\}}$$

It retrieves only the first and last names of employees earning over 50,000.

Example of Queries in Tuple Relational Calculus

The following examples show Tuple Relational Calculus queries for a database containing tables like EMPLOYEE, DEPARTMENT, and PROJECT.

We will use the following tables in the examples given below –

EMPLOYEE Table

The EMPLOYEE table that collects the data relevant to all employees –

DEPARTMENT Table

There are three departments as highlighted in the following DEPARTMENT table −

PROJECT Table

The PROJECT table contains the relevant details of all the projects −

Example 1: Retrieve Names and Addresses of Employees in the 'Research' Department

Query − We can use the following query to retrieve names and address of Employees in the Research department −

$$\mathrm{{t.Fname,\: t.Lname,\: t.Address \:|\: EMPLOYEE(t)\: AND\: (∃d)(DEPARTMENT(d)\: AND\: d.Dname='Research' \:AND\: d.Dnumber=t.Dno)}}$$

Explanation −

• EMPLOYEE(t) − Limits t to rows in the EMPLOYEE table.
• ∃d − Introduces d as a tuple variable for DEPARTMENT.
• Dname = 'Research' − Restricts d to the 'Research' department.
• Dnumber = t.Dnod − Joins EMPLOYEE and DEPARTMENT using Dnumber.

The query retrieves the first name, last name, and address of employees working in the 'Research' department.

Example 2: List Projects in Stafford with Manager Details

Query − We can use the following query to get all the projects in Stafford with their manager details −

{p.Pnumber, p.Dnum, m.Lname, m.Bdate, m.Address | PROJECT(p) AND EMPLOYEE(m) AND p.Plocation = 'Stafford' AND ((∃d)(DEPARTMENT(d) AND p.Dnum = d.Dnumber AND d.Mgr_ssn = m.Ssn))}

Explanation −

• PROJECT(p) − Filters projects.
• Plocation = 'Stafford' − Limits results to Stafford-based projects.
• ∃d − Introduces DEPARTMENT as d, linking departments to projects (p.Dnum = d.Dnumber).
• Mgr_ssn = m.Ssn − Connects department managers to employee records.

This query returns project numbers, controlling department numbers, and manager details.

Example 3: Employees Working on All Projects in Department 5

Universal quantifiers are used here −

{e.Lname, e.Fname | EMPLOYEE(e) AND (∀x)(NOT(PROJECT(x)) OR NOT(x.Dnum=5) OR ((∃w)(WORKS_ON(w) AND w.Essn = e.Ssn AND x.Pnumber = w.Pno)))}

Explanation −

• ∀x − Ensures all tuples in PROJECT meet a condition.
• NOT(PROJECT(x)) − Excludes tuples outside PROJECT.
• Dnum = 5 − Focuses on Department 5.
• ∃w − Ensures employee e works on each project (w.Essn = e.Ssn).

This query will return the list of employees engaged in every project controlled by Department 5.

Example 4: Employees without Dependents

Negation is key in this query −

{e.Fname, e.Lname | EMPLOYEE(e) AND (NOT(∃d)(DEPENDENT(d) AND e.Ssn = d.Essn))}

Explanation −

• EMPLOYEE(e) − Restricts e to EMPLOYEE.
• ∃d − Checks for dependents d.
• NOT − Shows no dependents exist where e.Ssn = d.Essn.

The result includes employees with no dependents.

Example 5: Managers with Dependents

Query − We can use the following query to get the details of all the managers with their dependents −

{e.Fname, e.Lname | EMPLOYEE(e) AND ((∃d)(∃ρ)(DEPARTMENT(d) AND DEPENDENT(ρ) AND e.Ssn=d.Mgr_ssn AND ρ.Essn=e.Ssn))}

Explanation −

• Ssn = d.Mgr_ssn − Identifies managers.
• ∃ρ − Checks for dependents ρ linked to managers.

This query outputs names of managers who have at least one dependent.

Safety in Tuple Relational Calculus

A Tuple Relational Calculus query must be safe; it means the query should return a finite number of tuples.

Safe Example − Here's a safe query −

{t | EMPLOYEE(t) AND t.Salary > 50000}

This query restricts results to existing EMPLOYEE tuples.

Unsafe Example − Now, take a look at this unsafe query −

{t | NOT(EMPLOYEE(t))}

This unsafe query attempts to include tuples outside EMPLOYEE. This will lead to an infinite result set. To ensure safety, we must use quantifiers and conditions that constrain tuple variables to finite domains. And avoid unrestricted negations.

Difference between Tuple Relational Calculus and Relational Algebra

The following table highlights the differences between Tuple Relational Calculus and Relational Algebra −

Conclusion

In this chapter, we understood the basics and workings of Tuple Relational Calculus. We gone through different concepts of its nonprocedural nature, components, and basic syntax. Through detailed examples, we explored how to retrieve data using logical conditions, how to apply existential and universal quantifiers, and how to ensure query safety for practical use.

Tuple Relational Calculus bridges the gap between database theory and SQL. It provides a logical foundation for high-level querying.