Relational database implementation
Relational Algebra in DBMS
Relational Algebra is a procedural query language used to retrieve and
manipulate data stored in relational databases. It provides a foundation for SQL
and other query languages by defining operations that work on relations
(tables).
These are the basic/fundamental operators used in Relational Algebra
1. Selection(σ)
2. Projection(π)
3. Union(U)
4. Set Difference(-)
5. Set Intersection(∩)
6. Rename(ρ)
7. Cartesian Product(X)
1. Selection(σ) : Selection Operation is basically used to filter out rows from a
given table based on certain given condition. It basically allows you to retrieve
only those rows that match the condition as per condition passed during SQL
Query.
It is used to select required tuples of the relations.
Illustration : If we want to get the details of all the work in the Department “IT”,
we would use the selection operator to filter out these based on the given
condition.
Example:
A B C
1 2 4
2 2 3
3 2 3
4 3 4
Output Table :
For the above relation, σ(c>3)R will select the tuples which have c more than 3.
A B C
1 2 4
4 3 4
Note: The selection operator only selects the required tuples but does not display
them.
1. Selection(σ) : Selection Operation is basically used to filter out rows from a
given table based on certain given condition. It basically allows you to retrieve
only those rows that match the condition as per condition passed during SQL
Query.It is used to select required tuples of the relations.
Illustration : If we want to get the details of all the work in the Department “IT”,
we would use the selection operator to filter out these based on the given
condition.Example:
A B
C
1 2 4
2 2 3
3 2 3
4 3 4
Output Table :For the above relation, σ(c>3)R will select the tuples which have
c more than 3.
A B
C
1 2 4
A B
C
4 3 4
Note: The selection operator only selects the required tuples but does not display
them.
3. Union(U) : Union Operator is basically used to combine the results of two
queries into a single result. The only condition is that both queries must return
same number of columns with same data types.
Illustration : If in case we want a list of all the employee from two different
department . Then in that case we should use union operation to merge both the
list from two different department.Union operation in relational algebra is the
same as union operation in set theory.
Example: FRENCH
Student_Name Roll_Number
Ram 01
Mohan 02
Vivek 13
Geeta 17
GERMAN
Student_Name Roll_Number
Vivek 13
Geeta 17
Shyam 21
Student_Name Roll_Number
Rohan 25
Consider the following table of Students having different optional subjects in
their course.
π(Student_Name)FRENCH U π(Student_Name)GERMAN
Student_Name
Ram
Mohan
Vivek
Geeta
Shyam
Rohan
Note: The only constraint in the union of two relations is that both relations must
have the same set of Attributes.
4. Set Difference(-) : Set difference basically provides the rows that are present
in one table , but not in another tables.
Illustration : If you have two lists of employees, one from the IT Department
and one from the Marketing department & you want to know which employees
are in IT but not in Marketing , the set difference operator would used.Set
Difference in relational algebra is the same set difference operation as in set
theory.
Example: From the above table of FRENCH and GERMAN, Set Difference is
used as follows
π(Student_Name)FRENCH - π(Student_Name)GERMAN
Student_Name
Ram
Mohan
Note: The only constraint in the Set Difference between two relations is that both
relations must have the same set of Attributes.
5. Set Intersection(∩) : Set Intersection basically allows to fetches only those
rows of data that are common between two sets of relational tables.
Illustration : If we want to find the number of employees who work in both IT
and Marketing Department , then in that case we use Intersection Operator.
Set Intersection in relational algebra is the same set intersection operation in set
theory.
Example: From the above table of FRENCH and GERMAN, the Set Intersection
is used as follows:
π(Student_Name)FRENCH ∩ π(Student_Name)GERMAN
Student_Name
Vivek
Geeta
Note: The only constraint in the Set Difference between two relations is that both
relations must have the same set of Attributes.
6. Rename(ρ) : Rename operator basically allows you to give a temporary name
to a specific relational table or to its columns. It is very useful when we want to
avoid ambiguity, especially in complex Queries.
Rename is a unary operation used for renaming attributes of a relation.
ρ(a/b)R will rename the attribute 'b' of the relation by 'a'.
7. Cross Product(X) : Cartesian product Operator combines every row of one
table with every row of another table , producing all the possible combination.
It’s mostly used as a precursor to more complex operation like joins.
Cross-product between two relations. Let’s say A and B, so the cross product
between A X B will result in all the attributes of A followed by each attribute of
B. Each record of A will pair with every record of B.
Example:
Name Age Sex
Ram 14 M
Sona 15 F
Kim 20 M
ID Course
1 DS
2 DBMS
AXB
Name Age Sex ID Course
Ram 14 M 1 DS
Ram 14 M 2 DBMS
Sona 15 F 1 DS
Sona 15 F 2 DBMS
Name Age Sex ID Course
Kim 20 M 1 DS
Kim 20 M 2 DBMS
Note: If A has ‘n’ tuples and B has ‘m’ tuples then A X B will have ‘ n*m ‘
tuples.
1. Natural Join(⋈): Natural join is a binary operator. Natural join between two
or more relations will result in a set of all combinations of tuples where they have
an equal common attribute.
Example:
EMP
ID
Name Dept_Name
A 120 IT
B 125 HR
C 110 Sales
D 111 IT
DEPT
Dept_Name Manager
Sales Y
Production Z
IT A
Natural join between EMP and DEPT with condition :
EMP.Dept_Name = DEPT.Dept_Name
EMP ⋈ DEPT
ID
Name Dept_Name Manager
A 120 IT A
C 110 Sales Y
D 111 IT A
2. Conditional Join: Conditional join works similarly to natural join. In natural
join, by default condition is equal between common attributes while in
conditional join we can specify any condition such as greater than, less than, or
not equal.
Example:
ID
Sex Marks
1 F 45
2 F 55
3 F 60
ID
Sex Marks
10 M 20
ID
Sex Marks
11 M 22
12 M 59
Join between R and S with condition R.marks>= S.marks
R.ID R.Sex R.Marks S.ID S.Sex S.Marks
1 F 45 10 M 20
1 F 45 11 M 22
2 F 55 10 M 20
2 F 55 11 M 22
3 F 60 10 M 20
3 F 60 11 M 22
3 F 60 12 M 59
Theta Join (⨝θ) – Join with Condition
Notation: R ⨝_condition S
A join with a condition other than equality.
Example: Employee ⨝_Employee.ID = Manager.ID Manager
Division (÷) – Finding Related Tuples
Used when querying for tuples associated with all values of another
relation.
Notation: R ÷ S
Example: Course ÷ Students → Finds courses taken by all students.
Relational Calculus in DBMS
Relational Calculus is a non-procedural query language that describes what to
retrieve rather than how to retrieve it. Unlike Relational Algebra, which
specifies a sequence of operations, Relational Calculus defines queries using
declarative logic-based expressions.
Relational Calculus is mainly divided into:
1. Tuple Relational Calculus (TRC)
2. Domain Relational Calculus (DRC)
1. Tuple Relational Calculus (TRC)
Queries are expressed in the form: {t ∣ P(t)}
Uses tuples (variables that represent entire rows) to define queries.
o t → A tuple variable (row).
o P(t) → A condition that the tuple must satisfy.
Example of TRC Query
Find names of employees working in the "Sales" department:
{t.Name ∣ t∈Employee∧t.Department=′Sales′}
This query selects Name from the Employee relation where the department
is "Sales".
2. Domain Relational Calculus (DRC)
Uses domain variables (which represent column values instead of
Queries are expressed as: {<x1,x2,...,xn> ∣ P(x1,x2,...,xn)}
whole tuples).
o x₁, x₂, ..., xₙ → Domain variables (column values).
o P(x₁, x₂, ..., x ₙ) → A condition that the values must satisfy.
Example of DRC Query
Find names of employees working in the "Sales" department:
{<N> ∣ ∃D,A,S(Employee(N,D,A,S)∧D=′Sales′)}
Here, N represents the Name column, and the condition ensures that only
employees from "Sales" are selected.
Target List and Qualifying Statement in Relational Calculus
1. Target List
Example in TRC: {t.Name,t.Salary ∣ P(t)}
Specifies what attributes (columns) to retrieve.
Example in DRC: {<N,S> ∣ Employee(N,D,S,A)∧D=′Sales′}
o Retrieves Name and Salary from a relation.
o Retrieves Name (N) and Salary (S) of employees.
2. Qualifying Statement
Example in TRC: {t ∣ t∈Employee∧t.Department=′HR′∧t.Salary>50000}
Defines conditions that the retrieved tuples must satisfy.
o Retrieves employees in the HR department with salaries above
Example in DRC: {<N> ∣ Employee(N,D,S,A)∧D=′HR′∧S>50000}
50,000.
o Retrieves names of employees in HR with salaries above 50,000.
Key Differences Between Relational Algebra and Relational Calculus
Feature Relational Algebra Relational Calculus
Procedural (specifies how to Non-procedural (specifies what to
Type
retrieve data) retrieve)
Uses operations like selection Uses logical expressions to define
Operations
(σ), projection (π), join (⨝), etc. conditions
Tuple Relational Calculus (TRC)
Types Only one type and Domain Relational Calculus
(DRC)
Example π Name (σ Department = 'HR'
`{ t.Name
Query (Employee))
Existential Quantifier (∃) in DBMS
What is an Existential Quantifier (∃)?
The existential quantifier (∃) is used in Relational Calculus to express that at
least one tuple exists in the database that satisfies a given condition. It is mainly
used in Tuple Relational Calculus (TRC) and Domain Relational Calculus
(DRC).
Syntax in Tuple Relational Calculus (TRC):
{t ∣ ∃s∈R(P(s)∧Q(t,s))}
t → Tuple we are selecting.
s → Tuple variable used to check existence.
P(s) → Condition on s.
Q(t, s) → Relationship between t and s.
Example in TRC
Find employees who work in at least one department.
{t.Name ∣ t∈Employee∧∃d∈Department(t.ID=d.EmpID)}
This query selects employees whose ID exists in the Department relation.
Use Cases of Existential Quantifiers in DBMS
1. Checking Existence – "Find employees who work in a department."
2. Subqueries in SQL – Equivalent to EXISTS in SQL:
Sql
SELECT Name FROM Employee e
WHERE EXISTS (SELECT * FROM Department d WHERE e.ID = d.Emp_ID);
Joins without Explicit Use of Join Operators – Relational Calculus
automatically links relations.
Universal Quantifier (∀) in DBMS
What is a Universal Quantifier (∀)?
The universal quantifier (∀) is used in Relational Calculus to express that a
condition must hold for all tuples in a given relation. It ensures that a property is
true for every possible value in the database.
Syntax in Tuple Relational Calculus (TRC):
{t ∣ ∀s∈R(P(s)→Q(t,s))}
t → Tuple we are selecting.
s → Tuple variable that must satisfy the condition.
P(s) → Condition that applies to all tuples in relation R.
Q(t, s) → Relationship between t and s that must be true for all s.
Example in TRC
Find employees who work on all projects.
{e ∣ e∈Employee∧∀p∈Project(p∈Project→∃w∈WorksOn(w.EmpI
D=e.ID∧w.ProjectID=p.ID))}
This query selects employees e if for every project p, there exists an entry in
WorksOn showing that e works on p.
Use Cases of Universal Quantifiers in DBMS
1. Checking "For All" Conditions – "Find employees who work on every
project."
3. Used Indirectly in SQL – Since SQL does not support ∀ directly, it is
2. Expressing Constraints – "Ensure that all students pass every course."
rewritten using NOT EXISTS:
This ensures that for every subject, there is a passing grade.
