DBMS Unit 3.

3 (Relational algebra)

Describe the prelims of relational tuple calculus in RDMS

Prelims (Preliminaries) of Relational Tuple Calculus in RDBMS

Relational Tuple Calculus (RTC) is a non-procedural query language used in Relational Database
Management Systems (RDBMS). It focuses on what to retrieve rather than how to retrieve it. Before
diving into actual tuple calculus expressions, you should understand the basic preliminaries or
foundational concepts:

🔹 1. Tuple Variables
A tuple variable is a variable that represents a tuple (row) from a relation (table).

Example: `t`, `s`, `r` are tuple variables.

A tuple variable ranges over all tuples in a relation.

🔹 2. Relations (Tables)
These are the database tables from which tuples are drawn.

Example: `Student`, `Course`, `Enrollment` are relation names.

🔹 3. Syntax of Tuple Calculus Expression

General Form:

less

{ t | P(t) }

`t` is a tuple variable.

`P(t)` is a predicate (logical condition) applied to `t`.

🔹 4. Predicates / Conditions (P(t))

These are logical formulas that specify the condition a tuple must satisfy.
Can include:

Comparison operators: `=`, `≠`, `<`, `>`, `≤`, `≥`

Logical connectives: `AND (∧)`, `OR (∨)`, `NOT (¬)`

Printed using ChatGPT to PDF, powered by PDFCrowd HTML to PDF API. 1/15
 Quantifiers:
Existential (`∃`): There exists a tuple…

Universal (`∀`): For all tuples…

🔹 5. Free and Bound Variables

A free variable is not quantified (used in the result).

A bound variable is within the scope of a quantifier.

In `{ t | P(t) }`, `t` is a free variable, while any variable inside `P(t)` under a quantifier is
bound.

🔹 6. Domain of Discourse
Each tuple variable ranges over a particular relation.
You must specify which relation a tuple variable belongs to using the notation like:

scss

Student(s)

which means tuple variable `s` is from the `Student` relation.

🔹 7. Example Expression
Retrieve all students enrolled in course with `CourseId = 'CS101'`:

less

{ s | Student(s) ∧ ∃e (Enrollment(e) ∧ e.SID = s.SID ∧ e.CourseID = 'CS101') }

🔹 Summary Table
Concept Description
Tuple Variable Variable representing a row in a relation

Relation Table in the database

Expression Format `{ t

Predicate `P(t)` Condition that must be true for tuple to be included

Logical Operators ∧, ∨, ¬ (AND, OR, NOT)
Quantifiers ∃ (exists), ∀ (for all)

Free/Bound Var Used in result vs. used in condition only

Let me know if you want notes on Domain Relational Calculus as well, or a comparison between the
two.

Printed using ChatGPT to PDF, powered by PDFCrowd HTML to PDF API. 2/15
 What is query language ? Discuss the main two types of query language used in RDMS

✅ What is a Query Language?

A query language is a type of computer language used to make queries (requests for information) from
databases and information systems. In the context of Relational Database Management Systems
(RDBMS), a query language is used to retrieve, insert, update, and delete data stored in relational
tables.

✅ Purpose of Query Languages in RDBMS

Access and manipulate data stored in relational tables.
Specify conditions to filter and extract required data.

Define and manage data structures (tables, views, etc.).

✅ Two Main Types of Query Languages in RDBMS

1. Procedural Query Language
🔹 Definition:
A procedural query language specifies how to obtain the desired result — it requires the user to
describe the sequence of operations to be performed on the database.

🔹 Key Features:
Specifies both what to retrieve and how to retrieve it.
Requires understanding of the database structure.

Usually more complex and low-level.

🔹 Example:
Relational Algebra

A formal language with a set of operations (select, project, join, etc.)

Operations are performed step-by-step.

relational

π_name(σ_dept='CSE'(Student))

This means: Select students from 'CSE' department and project their names.

Printed using ChatGPT to PDF, powered by PDFCrowd HTML to PDF API. 3/15
2. Non-Procedural Query Language
🔹 Definition:
A non-procedural query language specifies what data is required without describing the steps to
obtain it. The system determines the best way to execute the query.

🔹 Key Features:
Focuses on what to retrieve.
Easier to use, especially for end users.

More abstract and high-level.

🔹 Examples:
SQL (Structured Query Language)
Relational Calculus (Tuple and Domain)

SQL Example:

sql

SELECT name FROM Student WHERE dept = 'CSE';

Tuple Relational Calculus Example:

pgsql

{ s.name | Student(s) ∧ s.dept = 'CSE' }

✅ Comparison Table
Feature Procedural Query Language Non-Procedural Query Language

Approach Describes how to get data Describes what data is needed

User Control High Low
Complexity More complex Simpler and more user-friendly
Examples Relational Algebra SQL, Relational Calculus
Suitable For Developers/DB professionals End-users and high-level use

✅ Conclusion
In RDBMS, both procedural and non-procedural query languages are used. Procedural languages like
Relational Algebra are useful for understanding the underlying operations, while non-procedural
languages like SQL are more common in real-world applications due to their simplicity and abstraction.
Let me know if you'd like a visual diagram or flowchart to compare these types.

Printed using ChatGPT to PDF, powered by PDFCrowd HTML to PDF API. 4/15
 What is relational algebra . Discuss the operators in detail

✅ What is Relational Algebra?

Relational Algebra is a procedural query language used in Relational Database Management
Systems (RDBMS). It provides a set of operations to manipulate and retrieve data stored in relational
tables (relations). It is based on set theory and logic, and is used to perform queries by specifying a
sequence of operations.

✅ Characteristics of Relational Algebra

Procedural: Specifies how to obtain the result.
Operates on relations (tables).

Produces new relations as output (closed under algebra).

Forms the theoretical foundation for SQL and other query languages.

✅ Types of Relational Algebra Operators

Relational algebra consists of two types of operators:

1. Basic (Fundamental) Operators

2. Additional (Derived) Operators

🔹 1. Basic Operators
a) Selection (σ)

Selects rows (tuples) that satisfy a given predicate (condition).

Works like a filter.

Syntax:

php-template

σ<condition>(Relation)

Example:

bash

σ dept = 'CSE' (Student)

→ Selects all students from the CSE department.

Printed using ChatGPT to PDF, powered by PDFCrowd HTML to PDF API. 5/15
b) Projection (π)

Selects specific columns (attributes) from a relation.

Removes duplicate rows automatically.

Syntax:

php-template

π<attributes>(Relation)

Example:

scss

π name, rollno (Student)

→ Projects only the name and roll number of all students.

c) Union ( ∪ )

Combines tuples from two relations.

Removes duplicates automatically.

Both relations must be union-compatible (same schema and attribute domains).

Syntax:

nginx

Relation1 ∪ Relation2

Example:

scss

π rollno (CSE_Student) ∪ π rollno (ECE_Student)

d) Set Difference ( − )

Returns tuples in one relation that are not present in the other.
Also requires union compatibility.

Syntax:

nginx

Relation1 − Relation2

Example:

Printed using ChatGPT to PDF, powered by PDFCrowd HTML to PDF API. 6/15
 scss

π rollno (CSE_Student) − π rollno (Passed_Student)

e) Cartesian Product ( × )

Combines each tuple of the first relation with every tuple of the second.

Result size = (rows in R1) × (rows in R2)

Syntax:

nginx

Relation1 × Relation2

Example:

nginx

Student × Course

→ Returns all possible student-course pairs.

f) Rename (ρ)

Renames a relation or its attributes.

Syntax:

scss

ρ new_name (Relation)
ρ new_name(attribute1, attribute2, ...) (Relation)

Example:

scss

ρ Temp(name, roll) (Student)

🔹 2. Additional (Derived) Operators

a) Join (⨝)

i) Theta Join (⨝θ)

Combines tuples based on a condition `θ` (like =, <, >).

Syntax:

Printed using ChatGPT to PDF, powered by PDFCrowd HTML to PDF API. 7/15
 nginx

R ⨝θ S

Example:

nginx

Student ⨝ Student.dept = Dept.dept_id

ii) Equi-Join

A special case of theta join where the condition is equality ( = ).

iii) Natural Join (⋈)

Joins two relations automatically on common attributes.

Removes duplicate columns.

Syntax:

nginx

R ⋈ S

b) Intersection ( ∩ )

Returns tuples that appear in both relations.

Derived using set difference:

nginx

A ∩ B = A − (A − B)

c) Division (÷)

Returns tuples in one relation that are related to all tuples in another relation.

Used for "for all" type queries.

Example Use-Case:
Find students who have taken all courses.

✅ Summary Table of Operators

Operator Symbol Function

Selection σ Filters rows based on condition

Projection π Selects specific columns

Printed using ChatGPT to PDF, powered by PDFCrowd HTML to PDF API. 8/15
 Operator Symbol Function

Union ∪ Combines two relations

Difference − Subtracts one relation from another

Cartesian Prod. × Combines all tuple pairs

Rename ρ Renames relation or attributes

Join ⨝, ⋈ Combines tuples with matching values

Intersection ∩ Common tuples in both relations

Division ÷ Tuples related to all in another

✅ Conclusion
Relational Algebra is essential for understanding how database queries are executed at a logical level. It
serves as a foundation for SQL and plays a key role in query optimization.

Let me know if you'd like examples with sample data or a diagram to visualize the operations.

Discuss various join operations with example and procedures

✅ Join Operations in Relational Algebra (with Examples & Procedures)

Join is one of the most powerful and commonly used operations in Relational Algebra. It combines
tuples from two relations based on a common attribute or a condition.

🔹 Purpose of Join
To retrieve related data stored in multiple tables (relations) by matching values in common
attributes.

✅ Types of Join Operations

Type of Join Symbol Description

Theta Join ⨝<condition> Join using a condition

Equi Join ⨝A.a = B.b Special case of theta join using equality

Natural Join ⋈ Auto join on common attributes

Inner Join (Same as above) Returns matching rows only

Outer Join (Left, Right, Full) ⟕⟖⟗ Includes unmatched rows too

Self Join - Join a relation with itself

Semi Join - Returns rows from one relation with matching rows in another

Anti Join - Opposite of semi join

🔹 1. Theta Join (θ-Join)

Printed using ChatGPT to PDF, powered by PDFCrowd HTML to PDF API. 9/15
Definition: Joins two relations using a general condition θ (>, <, =, ≠, etc.)

Syntax:

nginx

R ⨝θ S

Procedure:

1. Perform a Cartesian product of R and S.

2. Apply the condition θ to filter rows.

Example:

text

Employee(EmpID, Name, DeptID)

Department(DeptID, DeptName)

Result = Employee ⨝ Employee.DeptID = Department.DeptID

🔹 2. Equi-Join
Definition: A theta join with only equality ( = ) conditions.

Syntax:

nginx

R ⨝ R.A = S.B S

Procedure:

1. Combine rows with matching values.

2. Keep duplicate columns.

Example:

text

Employee ⨝ Employee.DeptID = Department.DeptID Department

Result includes both `DeptID` from `Employee` and `Department`.

🔹 3. Natural Join
Definition: A special type of equi-join where the join is performed automatically on attributes with
the same name and domain.

Syntax:

Printed using ChatGPT to PDF, powered by PDFCrowd HTML to PDF API. 10/15
 nginx

R ⋈ S

Procedure:

1. Match tuples with equal values in common attributes.

2. Remove one duplicate column in output.

Example:

text

Employee(EmpID, Name, DeptID)

Department(DeptID, DeptName)

Result = Employee ⋈ Department

Joins on `DeptID`

Removes duplicate `DeptID` from one table

🔹 4. Left Outer Join (⟕)

Definition: Returns all matching tuples plus unmatched tuples from the left relation, with nulls in
right side.

Procedure:

1. Perform natural join.

2. Add remaining unmatched rows from the left with NULL for right-side values.

Syntax:

nginx

R ⟕ S

Example:

text

Employee ⟕ Department

🔹 5. Right Outer Join (⟖)

Definition: Returns all matching tuples plus unmatched tuples from the right relation, with nulls in
left side.
Syntax:

Printed using ChatGPT to PDF, powered by PDFCrowd HTML to PDF API. 11/15
 nginx

R ⟖ S

🔹 6. Full Outer Join (⟗)

Definition: Returns all matched rows and all unmatched rows from both relations, filling NULLs
where no match is found.
Syntax:

nginx

R ⟗ S

🔹 7. Self Join
Definition: A relation is joined with itself.
Use Case: Finding relationships within the same table, like manager-employee, friends, etc.

Example:

text

Employee(EmpID, Name, ManagerID)

Result = E1 ⨝ E1.ManagerID = E2.EmpID E2

🔹 8. Semi Join
Definition: Returns rows from the first relation that have matching rows in the second relation.

Use Case: Used in query optimization and distributed databases.

Example:

text

Employee ⋉ Department

→ Only Employee rows that have a department

🔹 9. Anti Join
Definition: Returns rows from the first relation that do not have matching rows in the second relation.

✅ Summary Table of Joins

Printed using ChatGPT to PDF, powered by PDFCrowd HTML to PDF API. 12/15
 Join Type Returns
Theta Join Tuples that satisfy a general condition
Equi Join Tuples where values are equal in common fields

Natural Join Auto join on same-named attributes (no duplicate columns)

Left Outer Join All matched + unmatched from left with NULLs for right side
Right Outer Join All matched + unmatched from right with NULLs for left side
Full Outer Join All matched + unmatched from both with NULLs as needed

Self Join Relation joined with itself

Semi Join Rows from left that have a match in right
Anti Join Rows from left that don’t have a match in right

✅ Example Dataset for Practice

Employee Table:

EmpID Name DeptID

1 Alice 10
2 Bob 20

3 Carol 30

Department Table:

DeptID DeptName

10 HR
20 IT
40 Finance

Let me know if you want visual diagrams for each join or SQL equivalents.

Briefly discuss about division operator with procedure and example

✅ Division Operator in Relational Algebra

The division operator (÷) is a special relational algebra operation used when a query involves a “for all”
condition — i.e., finding entities related to all items of another set.

🔹 Definition
Given two relations:

A(X, Y) — a relation with attributes `X` and `Y`

B(Y) — a relation with attribute `Y`

Then:

Printed using ChatGPT to PDF, powered by PDFCrowd HTML to PDF API. 13/15
 css

A ÷ B

produces a relation C(X) such that:

For every tuple `x` in `A`, and

For every tuple `y` in `B`,

The pair `(x, y)` must exist in `A`

This means: find all `X` values in `A` that are associated with every `Y` value in B.

✅ Use Case
Used to answer “which X is related to all Y?”

🔹 Procedure (Steps to Evaluate A ÷ B)

Let:

A(X, Y): Relation with tuples

B(Y): Relation of values to be fully matched

Steps:

1. πX(A) → Project attribute `X` from relation A.

2. πX(A) × B → Create all possible combinations of `X` with every `Y` in B.

3. (πX(A) × B) − A → Find combinations that don’t exist in A.

4. πX of the result → Find `X` values with missing pairs.
5. πX(A) − result from step 4 → Final output of division.

🔹 Example
Relation A: Enroll(Student, Course)

Student Course
John DBMS
John OS

Alice DBMS
Alice OS
Bob DBMS

Relation B: Course(Course)

Course
DBMS

Printed using ChatGPT to PDF, powered by PDFCrowd HTML to PDF API. 14/15
 Course
OS

❓ Query:
Find all students who have enrolled in all courses listed in `Course`.

✅ Solution:
nginx

Enroll ÷ Course

✅ Result:
Student
John
Alice

Bob is excluded because he is not enrolled in OS.

✅ Summary
Aspect Division Operator (`÷`)
Input A(X, Y) and B(Y)
Output Relation with attribute X
Use Case “For all” type queries

Key Operation Subtract unmatched pairs

Let me know if you'd like this visualized with a Venn diagram or implemented using SQL.

Printed using ChatGPT to PDF, powered by PDFCrowd HTML to PDF API. 15/15