DBMS - Domain Relational Calculus

Domain Relational Calculus is another formal query language for relational databases. It is quite similar to Tuple Relational Calculus but on columns.

Domain Relational Calculus emphasizes what to retrieve, rather than describing the steps to get there, which makes it a nonprocedural query language. Here, the focus is on conditions and results, not on the process.

In this chapter, we will learn the fundamentals of Domain Relational Calculus, understand its structure, and use examples to illustrate how it works. Along the way, we will also see how Domain Relational Calculus differs from Tuple Relational Calculus.

Domain Relational Calculus

Domain Relational Calculus is all about working with domain variables. Unlike Tuple Relational Calculus, where variables represent entire tuples (rows). The Domain Relational Calculus's variables represent individual attribute values (columns). These variables range over specific attribute domains. These are essentially the possible values for a given attribute.

A basic Domain Relational Calculus query looks like this −

$$\mathrm{\{x_{1},\: x_{2},\: ...,\: x_{n}\: |\: COND(x_{1},\: x_{2},\: ...,\: x_{n})\}}$$

Here −

• x1, x2, ...,xn − Domain variables representing specific attributes.
• COND(x1,x2,...,xn) − A condition that determines which tuples to retrieve.

The result is a set of tuples formed by combining the values of the domain variables x1, x2,...,xn that satisfy the condition.

Tables Used in Domain Relational Calculus Examples

We will use the following dummy tables (EMPLOYEE, DEPARTMENT, and PROJECT) in the examples that follow −

EMPLOYEE Table

Here is the EMPLOYEE table that contains 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 −

Key Concepts in Domain Relational Calculus

In this section, let's understand some key concepts in Domain Relational Calculus –

Domain Variables − The domain variables represent individual attribute values. For instance, x might represent the Fname attribute (first name) of an employee. The y could represent their salary.

Conditions − Conditions in Domain Relational Calculus use −

• Attribute Comparisons − Like x > 50000 or y=′Research′.
• Logical Operators − AND, OR, and NOT.
• Quantifiers − Existential (∃) and Universal (∀) quantifiers.

Structure of a Query − Each Domain Relational Calculus query must specify −

• Variables to Retrieve − Attributes to include in the result.
• Conditions to Meet − Logical expressions that filter the data.

For example −

$$\mathrm{\{x,\: y \:|\: EMPLOYEE(x,\: y,\: z,\: w)\: AND\: z\: >\: 50000\}}$$

This retrieves employees x and y (for example first and last names) with salaries z greater than 50,000.

Examples of Domain Relational Calculus Queries

To better understand Domain Relational Calculus, let us work through several examples with the tables given above.

Example 1: Employees in the Research Department

Query − Retrieve the names and addresses of employees who work in the "Research" department.

Expression −

$$\mathrm{\{x,\: z\: | \:(∃y)(∃w)(EMPLOYEE(x,\: y,\: z,\: w,\: v,\: u)\: AND\: DEPARTMENT(s,\: t,\: u)\: AND\: s \:=\: 'Research')\}}$$

Explanation −

• x and z are free variables for employee names and addresses.
• ∃y − Introduces additional attributes from the EMPLOYEE table.
• EMPLOYEE(x, y, z, w, v, u) − Specifies employees.
• DEPARTMENT(s, t, u) − This links employees to the "Research" department using department numbers.

This query is checking that the department number u matches between EMPLOYEE and DEPARTMENT.

Example 2: Projects in Stafford with Manager Details

Query − For every project located in "Stafford," list the project number. With controlling department, and manager's name and address.

Expression −

$$\mathrm{\{i,\: j,\: x,\: y \:|\: (∃k)(∃m)(∃n)(PROJECT(h,\: i,\: j,\: k)\: AND\: DEPARTMENT(l,\: m,\: n)\: AND\: EMPLOYEE(x,\: y,\: q,\: r,\: s,\: t)\: AND \:j \:=\: m\: AND\: n\: = \:q \:AND\: k \:=\: 'Stafford')\}}$$

Explanation −

• i, j, x, y − Retrieve project number, department number, manager name, and address.
• k, m, n − Variables for the PROJECT and DEPARTMENT attributes.
• EMPLOYEE(x, y, q, r, s, t) − Fetches manager information using n = q.
• k = 'Stafford' − This ensures the project is located in Stafford.

Example 3: Employees without Dependents

Query − Retrieve the names of employees who have no dependents.

Expression −

$$\mathrm{\{x,\: y \:|\: (EMPLOYEE(x,\: y,\: z,\: w,\: v,\: u) \:AND\: (NOT(∃l)(DEPENDENT(l,\: m,\: n,\: o)\: AND\: l \:=\: z)))\}}$$

Explanation −

• x, y − Retrieve employee names.
• EMPLOYEE(x, y, z, w, v, u) − Defines the employees.
• ∃l − Introduces dependents using l.
• NOT − Ensures no dependent l exists where l = z (employee's SSN).

Example 4: Managers with Dependents

Query − Retrieve the names of managers who have at least one dependent.

Expression −

$$\mathrm{\{x,\: y\: |\: (∃t)(∃k)(∃l)(EMPLOYEE(x,\: y,\: z,\: w,\: v,\: u) \:AND\: DEPARTMENT(s,\: t,\: z)\: AND\: DEPENDENT(l,\: m,\: n,\: o)\: AND\: l \:=\: z)\}}$$

Explanation −

• x, y − Retrieve manager names.
• EMPLOYEE(x, y, z, w, v, u) − Fetch manager details.
• DEPARTMENT(s, t, z) − Links manager to their department.
• DEPENDENT(l, m, n, o) − Checks that at least one dependent l exists.

Safety in Domain Relational Calculus

Like tuple relational calculus here also we must consider the term called safe query. This ensures finite results. For example −

$$\mathrm{\{x,\: y\: |\: EMPLOYEE(x,\: y,\: z,\: w)\: AND\: z \:>\: 50000\}}$$

This query is safe because it retrieves values only from the EMPLOYEE table.

However, consider this unsafe query −

$$\mathrm{\{x,\: y \:|\: NOT(EMPLOYEE(x,\: y,\: z,\: w))\}}$$

Here, NOT includes tuples outside EMPLOYEE. This leads to potentially infinite results.

To ensure safety − We must use quantifiers to limit variables. Avoid unrestricted negations. And ensure all variables reference finite domains.

Difference between Domain Relation Calculus and Tuple Relational Calculus

The following table highlights how Domain Relational Calculus differs from Tuple Relational Calculus –

Conclusion

Domain Relational Calculus is a foundational query language in relational databases. In this chapter, we understood how Domain Relational Calculus uses domain variables to represent attributes. We highlighted the structure and key elements of a Domain Relational Calculus query, and illustrated the concept with several examples for retrieving employees, projects, and dependents queries.