Tuple Relational Calculus (TRC) and Domain Relational Calculus (DRC) PDF

Tuple Relational Calculus (TRC) and Domain Relational Calculus (DRC) Both Tuple Relational Calculus (TRC) and Domain Relational Calculus (DRC) are non- procedural query languages that describe what data to retrieve rather than how to retrieve it. They differ in how they specify the data conditions:...

Tuple Relational Calculus (TRC) and Domain Relational Calculus (DRC) Both Tuple Relational Calculus (TRC) and Domain Relational Calculus (DRC) are non- procedural query languages that describe what data to retrieve rather than how to retrieve it. They differ in how they specify the data conditions:  TRC uses tuples (rows) to specify conditions.  DRC uses domains (values of attributes) to specify conditions. Let's work with the following datasets for examples. Student Courses Enrollments StudentID Name Age StudentID CourseID CourseID CourseName 1 Alice 20 1 1 1 Math 2 Bob 22 2 2 2 Physics 3 Carol 19 3 1 1. Tuple Relational Calculus (TRC) Example Tuple Relational Query In Tuple Calculus, a query is expressed as {t| P(t)} Where t = resulting tuples, P(t) = known as Predicate and these are the conditions that are used to fetch t. Thus, it generates a set of all tuples t, such that Predicate P(t) is true for t. P(t) may have various conditions logically combined with OR (∨), AND (∧), NOT(¬). It also uses quantifiers: ∃ t ∈ r (Q(t)) = ”there exists” a tuple in t in relation r such that predicate Q(t) is true. ∀ t ∈ r (Q(t)) = Q(t) is true “for all” tuples in relation r. Query: Retrieve the names of students enrolled in the "Math" course. In TRC, we describe the condition using tuples from the relations. The query will look something like: TRC Query: {S.Name ∣ S ∈ Students, E ∈ Enrollments, C ∈ Courses, S.StudentID = E.StudentID AND E.CourseID = C.CourseID AND C.CourseName=′Math′} This query can be broken down as:  S ∈ Students specifies that S is a tuple from the Students table.  E ∈ Enrollments specifies that E is a tuple from the Enrollments table.  S.StudentID=E.StudentID ensures that the student is enrolled.  E.CourseID=C.CourseID links the enrollment to the course.  C.CourseName=′Math′ filters for only the "Math" course.  Name Alice Carol SELECT DISTINCT S.Name FROM Students S JOIN Enrollments E ON S.StudentID = E.StudentID JOIN Courses C ON E.CourseID = C.CourseID WHERE C.CourseName = 'Math'; Example: Tuple Relational Calculus (TRC) is a non-procedural query language that uses variables representing tuples. A query specifies a set of conditions, and all tuples satisfying those conditions are returned. SQL, on the other hand, is a procedural query language. Below is an example comparing a TRC query and an equivalent SQL query? Scenario: Let's say we have a Students table with the following schema: Students (StudentID, Name, Major, GPA) We want to find the names of all students who are majoring in 'Computer Science' and have a GPA greater than 3.5. Tuple Relational Calculus (TRC) Query: ∀ t ∈ r (Q(t)) = Q(t) is true “for all” tuples in relation r. In TRC, the query can be expressed as: {S | S ∈ Students ∧ S.Major = 'Computer Science' ∧ S.GPA > 3.5} This means: retrieve the set of all tuples S such that S is in the Students relation, and S.Major is 'Computer Science', and S.GPA is greater than 3.5. Equivalent SQL Query: The equivalent SQL query would be: SELECT Name FROM Students WHERE Major = 'Computer Science' AND GPA > 3.5; Explanation:  TRC: The tuple variable S represents a tuple in the Students relation, and the conditions specify that we are only interested in those tuples where S.Major equals 'Computer Science' and S.GPA is greater than 3.5.  SQL: The query uses the SELECT statement to retrieve the Name column from the Students table, where the conditions on Major and GPA are applied. Let’s construct a Tuple Relational Calculus (TRC) query. In TRC, the general form of a query is { t | P(t) }, where t is a tuple variable, and P(t) is a predicate condition that specifies which tuples are in the result set. Here’s the TRC query you asked for: Query: ∀ t ∈ r (Q(t)) = Q(t) is true "for all" tuples in relation r 1. Interpretation of the query: This query is asking for a condition to hold true for all tuples in a given relation r. essentially, it asserts that every tuple in relation r satisfies a predicate Q. 2. Example Dataset: Let’s assume we have a dataset of employees (Employee) with the following schema: o Employee(empID, empName, department, salary) Suppose we want to check if all employees in the Employee relation have a salary greater than 30,000. This condition could be expressed as Q(t), where Q(t) = t.salary > 30000. 3. TRC Query: To ensure that this holds true for all employees in the Employee relation, we can use the following TRC query: {t | t ∈ Employee ∧ t.salary > 30000 } SQL query: SELECT * FROM Employee WHERE salary > 30000; For this query to be true for all tuples in the Employee relation, every employee tuple in the dataset must have a salary greater than 30,000. 4. Explanation: o This query essentially filters out all employees who satisfy the condition salary > 30000. o If the query result includes all tuples in Employee, we can conclude that the condition Q(t) = t.salary > 30000 is true for all employees in the dataset. If there are any tuples in Employee with a salary of 30,000 or less, then the condition would not hold for all tuples, and the result set would only contain employees with salaries above 30,000, reflecting that not all tuples meet Q(t). 2. Domain Relational Calculus (DRC) Example Query: Retrieve the names of students who are older than 20. In DRC, we describe the query using domain variables that represent the individual fields (attributes) of the relations. DRC Query: Name {N ∣ ∃ SID, A (SID, N, A) ∈ Students AND A>20} Bob SELECT DISTINCT Name FROM Students WHERE Age > 20; This query can be broken down as:  N is the domain variable representing the Name attribute in the Students table.  SID is the domain variable representing StudentID, and A represents Age.  (SID,N,A)∈ Students ensures that we are picking a tuple from the Students relation.  A>20 filters students based on their age. Domain Relational Calculus (DRC) Domain Relational Calculus is similar to Tuple Relational Calculus, where it makes a list of the attributes that are to be chosen from the relations as per the conditions. { | P(a1,a2,a3,.....an)} Where a1, a2…an are the attributes of the relation and P is the condition. Tuple Relational Calculus Examples Customer Account Loan Customer Account Branch Loan Branch Street City Balance Amount name number name number name Saurabh A7 Patiala 1111 ABC 50000 L33 ABC 10000 Mehak B6 Jalandhar 1112 DEF 10000 L35 DEF 15000 Sumiti D9 Ludhiana 1113 GHI 9000 L49 GHI 9000 Ria A5 Patiala 1114 ABC 7000 L98 DEF 65000 Branch Borrower Depositor Branch Customer Customer Account Branch City Loan number name name name number ABC Patiala Saurabh L33 Saurabh 1111 DEF Ludhiana Mehak L49 Mehak 1113 GHI Jalandhar Ria L98 Suniti 1114 Example 1: Find the loan number, branch, and amount of loans greater than or equal to 10000 amount. {t| t ∈ loan ∧ t[amount]>=10000} SELECT * FROM loan WHERE amount >= 10000; Explanation:  SELECT * retrieves all columns (i.e., the tuple t).  The FROM loan specifies the loan table as the source.  The WHERE amount >= 10000 condition ensures that only loans with an amount greater than or equal to 10,000 are selected. Resulting relation: Loan number Branch name Amount L33 ABC 10000 L35 DEF 15000 L98 DEF 65000 In the above query, t [amount] is known as a tuple variable. Example 2: Find the loan number for each loan of an amount greater or equal to 10000. {t| ∃ s ∈ loan (t [loan number] = s[loan number] ∧ s[amount]>=10000)} SELECT * FROM loan t WHERE t.loan_number IN (SELECT s.loan_number FROM loan s WHERE s.amount >= 10000 ); Explanation:  The outer query selects all rows from the loan table (aliased as t).  The sub query checks for all loan numbers from the loan table (aliased as s) where the amount is greater than or equal to 10000.  The outer query only returns rows from the loan table where the loan_number matches one of the loan numbers found in the sub query. Resulting relation: Loan number L33 L35 L98 Example 3: Find the names of all customers who have a loan and an account at the bank. {t | ∃ s ∈ borrower( t[customer-name] = s[customer-name])∧ ∃ u ∈ depositor ( t[customer-name] = u[customer-name])} SELECT DISTINCT b.[customer-name] FROM borrower b JOIN depositor d ON b.[customer-name] = d.[customer-name]; Resulting relation: Customer name Saurabh Mehak Example 4: Find the names of all customers having a loan at the “ABC” branch. {t | ∃ s ∈ borrower(t[customer-name] = s[customer-name] ∧ ∃ u ∈ loan(u[branch- name] = “ABC” ∧ u[loan-number] = s[loan-number]))} Resulting relation: Customer name Saurabh Comparison of TRC and DRC  Tuple Relational Calculus (TRC) uses tuples to express queries and refers to entire rows (i.e., records in the database).  Domain Relational Calculus (DRC) uses domains (i.e., individual attribute values) to express queries and focuses on the fields in the relations. Both approaches are declarative, meaning they describe what data should be retrieved, without specifying how to retrieve it, as opposed to procedural languages like SQL or relational algebra that describe a specific process for obtaining the data.