Tuple Relational Calculus (TRC)
TRC, also known as Tuple Relational Calculus, is a powerful language designed for specifying queries in databases. Unlike other query languages like SQL, TRC focuses on expressing what data should be retrieved from a database, rather than getting into the specifics of how it should be retrieved.
When formulating a query in TRC, you use a formula that outlines the conditions that the desired tuples, or rows of data, need to satisfy. This formula comprises variables and logical expressions that define the criteria the variables must meet.
In TRC, there are two types of variables: free variables and bound variables. Free variables are not restricted by any quantifier in the formula, which means they can take on various values across different tuples. On the other hand, bound variables are tied to a quantifier that determines their scope within the formula.
TRC provides two quantifiers to work with: the existential quantifier (∃) and the universal quantifier (∀). The existential quantifier ensures that at least one tuple in the relation satisfies the specified conditions, while the universal quantifier requires all tuples in the relation to meet the conditions.
One of TRC's standout features is its declarative nature. This means that users can focus on expressing their data retrieval requirements without needing to concern themselves with the specific retrieval mechanisms employed by the database management system. The responsibility of optimizing the query and determining the most efficient retrieval method lies with the database system.
By offering a formal and expressive language for query specification, TRC enables precise and concise formulation of queries. It empowers users to manipulate and retrieve data from relational databases efficiently, supporting effective data management and analysis.
In essence, TRC stands as a non-procedural query language that enables users to define their data retrieval needs using logical formulas with variables, quantifiers, and conditions. Its declarative nature allows users to focus on their information requirements while entrusting the implementation details to the capable hands of the database system.
- Example 1:
| EmpID | Emp_Name | Salary |
|---|---|---|
| 1 | Fitsum Tessema | 60000 |
| 2 | Abreham Kebede | 45000 |
| 3 | Kobamo W/Giorgis | 70000 |
| 4 | Tilahun Gezimu | 55000 |
Example Formula:
{ t | Employee(t) ∧ t.Salary > 50000 }
To find all employees in a company who earn a salary greater than $50,000 using TRC (Tuple Relational Calculus), we can define the formula as follows:
Employee(t) ∧ t.Salary > 50000
In this formula:
- "Employee(t)" specifies that we want to retrieve all tuples (denoted by 't') from the Employee relation.
- "t.Salary > 50000" specifies the condition that the Salary attribute of the tuple 't' should be greater than $50,000.
By combining these two expressions with the logical conjunction operator "∧" (AND), we create a formula that captures the desired query. This formula ensures that only the tuples representing employees with salaries exceeding $50,000 will be retrieved from the Employee relation.
Applying this TRC formula to the database will result in retrieving all the relevant employee records that meet the specified criteria, providing valuable insights into the high-earning employees within the company.
| EmpID | Emp_Name | Salary |
|---|---|---|
| 1 | Fitsum Tessema | 60000 |
| 3 | Kobamo W/Giorgis | 70000 |
| 4 | Tilahun Gezimu | 55000 |
- TRC formula to retrieve all employees whose first name is "Kobamo":
{ t | Employee(t) ∧ t.Emp_Name = "Kobamo W/Giorgis" }
In this formula:
- "Employee(t)" specifies that we want to retrieve all tuples (represented by 't') from the Employee relation.
- "t.FirstName = 'Kobamo'" specifies the condition that the FirstName attribute of the tuple 't' should be equal to "Kobamo".
By combining these expressions using the logical conjunction operator "∧" (AND), we create a formula that captures the query. This formula ensures that only the tuples representing employees with the first name "Kobamo" will be retrieved from the Employee relation.
Applying this TRC formula to the database will retrieve all the relevant employee records that have the first name "Kobamo", providing information about employees with that specific name.
| EmpID | Emp_Name | Salary |
|---|---|---|
| 3 | Kobamo W/Giorgis | 70000 |
Table Name Customer
| CustomerID | Name | Age | Gender | Occupation |
|---|---|---|---|---|
| 1 | Abaynesh Belay | 28 | Female | Engineer |
| 2 | Fekadu Anbiko | 35 | Male | Bank Manager |
| 3 | Tsion Tessema | 20 | Female | Doctor |
| 4 | Kaltamo W/Gioegis | 24 | Male | Accountant |
| 5 | Abera Sugebo | 28 | Male | Manager |
| 6 | Obo Mengistu | 25 | Male | Football Keeper |
{ t | Customer(t) ∧ t.Gender = "Female" ∧ t.Age < 25 }
| CustomerID | Name | Age | Gender | Occupation |
|---|---|---|---|---|
| 1 | Abaynesh Belay | 28 | Female | Engineer |
| 3 | Tsion Tessema | 20 | Female | Doctor |
In this formula :
- "Customer(t) " specifies that we want to retrieve all tuples (represented by 't') from the Customer relation.
- "t.Gender = 'Female'" specifies the condition that the Gender attribute of the tuple 't' should be equal to "Female".
- "t.Age < 25" specifies the condition that the Age attribute of the tuple 't' should be less than 25.
By combining these expressions using the logical conjunction operator "∧" (AND), we create a formula that captures the query. This formula ensures that only the tuples representing female customers who are younger than 25 will be retrieved from the Customer relation.
Applying this TRC formula to the database will retrieve all the relevant customer records that satisfy these criteria, providing information about female customers who are younger than 25.
Exercise| OrderID | CustomerID | Product | Price |
|---|---|---|---|
| 1 | 1 | Laptop | 35000 |
| 2 | 2 | Phone | 20000 |
| 3 | 1 | Headphone | 1000 |
| 4 | 3 | Desktop | 13000 |
| 5 | 3 | Television | 50000 |
| 6 | 2 | Camera | 15000 |
{ t | Customer(t) ∧ (t.Occupation = "Doctor" ∨ t.Occupation = "Engineer") }
Here are the customers who fit the criteria:
CustomerID Name Age Gender Occupation 1 Abaynesh Belay 28 Female Engineer 3 Tsion Tessema 20 Female Doctor
{ o | Order(o) ∧ ∃ c (Customer(c) ∧ o.CustomerID = c.CustomerID ∧ c.Occupation = "Doctor") }
Here's the table based on the given query:
OrderID CustomerID Product Price 4 3 Desktop 13000 5 3 Television 50000
Domain Relational Calculus (TRC)
DRC (Domain Relational Calculus) is indeed a formal language used to specify database queries. It shares similarities with TRC (Tuple Relational Calculus) but utilizes a different syntax and has some variations in its capabilities.
In DRC, a query is represented as a formula that outlines the conditions that the desired values must meet. This formula consists of variables and logical expressions that define these conditions.
DRC also incorporates two types of variables: free variables and bound variables. Free variables are not bound by any quantifier in the formula, allowing them to range over the entire domain. Bound variables, on the other hand, are bounded by a quantifier, determining their scope within the formula.
Similar to TRC, DRC employs two quantifiers: the existential quantifier (∃) and the universal quantifier (∀). The existential quantifier specifies that there exists at least one value in the domain that satisfies the given conditions. Conversely, the universal quantifier implies that all values in the domain must fulfill the specified conditions.
Now, let's consider the following Employee table with attributes:
- Employee_ID
- Name
- Age
- Gender
- Salary
- Department_ID
Using DRC, we can construct queries to retrieve specific information from this Employee table based on desired conditions. The precise formulation of these queries will depend on the specific requirements and criteria of each query.
Table Name Employee
| EmployeeID | Name | Gender | Age | Salary | DepartmentID |
|---|---|---|---|---|---|
| 1 | Mulugeta Kebede | Male | 30 | 75000 | 1 |
| 2 | Adinew Gezimu | Male | 32 | 60000 | 2 |
| 3 | Esuanesh Mengistu | Female | 25 | 55000 | 1 |
| 4 | Dr. Alemu Ersido | Male | 40 | 90000 | 3 |
{<b>Name, Age</b> | Employee(Name, Age) ∧ Salary > 65000
Here's the table generated from the query specified:
| Name | Age |
|---|---|
| Mulugeta Kebede | 30 |
| Dr. Alemu Ersido | 40 |
The table shows the names and ages of employees who have a salary greater than $65,000, as specified by the query provided.
- Example 2: Retrieve the department IDs of all employees who are older than 30.
{<b>Department_ID</b> | Employee(Age, Department_ID) ∧ Age > 30}
| Department_ID |
|---|
| 2 |
| 4 |
0 Comments