Introduction of Relational Algebra in DBMS

Relational Algebra is a formal language used to query and manipulate relational databases,
consisting of a set of operations like selection, projection, union, and join. It provides a
mathematical framework for querying databases, ensuring efficient data retrieval and
manipulation. Relational algebra serves as the mathematical foundation for query SQL
Relational algebra simplifies the process of querying databases and makes it easier to understand
and optimize query execution for better performance. It is essential for learning SQL because
SQL queries are based on relational algebra operations, enabling users to retrieve data
effectively.
Key Concepts in Relational Algebra
Before explaining relational algebra operations, let's define some fundamental concepts:
1. Relations: In relational algebra, a relation is a table that consists of rows and columns,
representing data in a structured format. Each relation has a unique name and is made up of
tuples.
2. Tuples: A tuple is a single row in a relation, which contains a set of values for each attribute.
It represents a single data entry or record in a relational table.
3. Attributes: Attributes are the columns in a relation, each representing a specific characteristic
or property of the data. For example, in a "Students" relation, attributes could be "Name", "Age",
and "Grade".
4. Domains: A domain is the set of possible values that an attribute can have. It defines the type
of data that can be stored in each column of a relation, such as integers, strings, or dates.
Basic Operators in Relational Algebra
Relational algebra consists of various basic operators that help us to fetch and manipulate data
from relational tables in the database to perform certain operations on relational data. Basic
operators are fundamental operations that include selection (σ), projection (π), union (U), set
difference (−), Cartesian product (×), and rename (ρ).
1. Selection(σ)

The Selection Operation is basically used to filter out rows from a given table based on certain
given condition. It basically allows us to retrieve only those rows that match the condition as per
condition passed during SQL Query.
Example: If we have a relation R with attributes A, B, and C, and we want to select tuples
where C > 3, we write:

A B C

1 2 4

2 2 3
 3 2 3

4 3 4

σ(c>3)(R) will select the tuples which have c more than 3.

Output:

A B C

1 2 4

4 3 4

Explanation: The selection operation only filters rows but does not display or change their
order. The projection operator is used for displaying specific columns.

2. Projection(π)

While Selection operation works on rows, similarly projection operation of relational algebra
works on columns. It basically allows us to pick specific columns from a given relational table
based on the given condition and ignoring all the other remaining columns.
Example: Suppose we want columns B and C from Relation R.
π(B,C)(R) will show following columns.
Output:

B C

2 4

2 3

3 4

Explanation: By Default, projection operation removes duplicate values.

3. Union(U)

The 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. Union operation in relational algebra is the same as union operation in set theory.
Example: Consider the following table of Students having different optional subjects in their
course.
FRENCH

`Student_Name Roll_Number
 Ram 01

Mohan 02

Vivek 13

Geeta 17

GERMAN

Student_Name Roll_Number

Vivek 13

Geeta 17
 Shyam 21

Rohan 25

If FRENCH and GERMAN relations represent student names in two subjects, we can combine
their student names as follows:
π(Student_Name,Roll_Number)(FRENCH) U π(Student_Name,Roll_Number
)(GERMAN)
Output:

Student_Name

Ram

Mohan

Vivek
 Geeta

Shyam

Rohan

Explanation: 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.
Set Difference in relational algebra is the same set difference operation as in set theory.
Example: To find students enrolled only in FRENCH but not in GERMAN, we write:
π(Student_Name)(FRENCH) - π(Student_Name)(GERMAN)

Student_Name

Ram
 Mohan

Explanation: The only constraint in the Set Difference between two relations is that both
relations must have the same set of Attributes.

5. 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.
Example: We can rename an attribute B in relation R to D

A B C

1 2 4

2 2 3

3 2 3
 4 3 4

ρ(D/B)R will rename the attribute 'B' of the relation by 'D".

Output Table:

A D C

1 2 4

2 2 3

3 2 3

4 3 4

6. Cartesian Product(X)

The Cartesian product 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. 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.
Relation A:

Name Age Sex

Ram 14 M

Sona 15 F

Kim 20 M

Relation B:

ID Course

1 DS
 2 DBMS

Output: If relation A has 3 rows and relation B has 2 rows, the Cartesian product A × B will
result in 6 rows.

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

Kim 20 M 1 DS
 Kim 20 M 2 DBMS

Explanation: If A has 'n' tuples and B has 'm' tuples then A X B will have 'n*m' tuples.
Derived Operators in Relational Algebra
Derived operators are built using basic operators and include operations like join, intersection,
and division. These operators help perform more complex queries by combining basic operations
to meet specific data retrieval needs.

1. Join Operators

Join operations in relational algebra combine data from two or more relations based on a related
attribute, allowing for more complex queries and data retrieval. Different types of joins include:

Inner Join

An inner join combines rows from two relations based on a matching condition and only returns
rows where there is a match in both relations. If a record in one relation doesn't have a
corresponding match in the other, it is excluded from the result. This is the most common type of
join.

Let's proceed with an example demonstrating the process of Inner Join

Student Table

EnrollNo StudentName Address

1001 geek1 geeksquiz1

 1002 geek2 geeksquiz2

1003 geek3 geeksquiz3

1004 geek4 geeksquiz4

StudentCourse Table

CourseID EnrollNo

1 1001

2 1001

3 1001
 1 1002

2 1003

Inner Join/Simple Join

In an INNER join, it allows retrieving data from two tables with the same ID.
An Inner Join returns only the matching rows between the two tables based on a specified
condition. It combines data from two tables based on a common column between them, which is
specified using the ON keyword in SQL. If a row in one table does not have a matching row in
the other table, that row will not be included in the result set.

Syntax:

SELECT COLUMN1, COLUMN2 FROM

[TABLE 1] INNER JOIN [TABLE 2]

ON Condition;

The following is a join query that shows the names of students enrolled in different courses.
Query:

SELECT [Link],[Link]​
FROM Student​
INNER JOIN StudentCourse​
ON [Link] = [Link]​
ORDER BY [Link];

Note: Inner is optional above. Simple JOIN is also considered as INNER JOIN The above query
would produce the following result.

CourseID StudentName

1 geek1

1 geek2

2 geek1

2 geek3

3 geek1

Example:
For example, let's say we have two tables, Table1 and Table2, with the following data:
Table 1

ID Name

1 John

2 Sarah

3 David

Table 2

ID Address

1 123 Main St.

2 456 Elm St.

 4 789 Oak St.

If we perform an Inner Join on these tables using the ID column, the result set would only
include the matching rows from both tables, which are the rows with ID values of 1 and 2:
Query:

SELECT [Link],​
Table 1. Name, Table [Link]​
FROM Table1​
INNER JOIN Table2​
ON [Link] = [Link]

Output:

ID Name Address

1 John 123 Main St.

2 Sarah 456 Elm St.

How To Use Inner Join?

Inner Join is basically performed by just selecting the records having the common values or the
matching values in both tables. In case of no common values, no data is shown in the output.
Syntax:

Select Table1.Col_Name, Table2.Col_Name....​

From Table1​
Inner Join Table2​
on Table1.Common_Col = Table2.Common_Col;

●​ Conditional Join: A conditional join is an inner join where the matching condition
can involve any comparison operator like equals (=), greater than (>), etc. Example:
Joining Employees and Departments on DepartmentID where Salary > 50000 will
return employees in departments with a salary greater than 50,000
●​ Equi Join: An equi join is a type of conditional join where the condition is
specifically equality (=) between columns from both relations. Example: Joining
Customers and Orders on CustomerID where both relations have this column,
returning only matching records.
●​ Natural Join: A natural join automatically combines relations based on columns with
the same name and type, removing duplicate columns in the result. It’s a more
efficient way of joining. Example: Joining Students and Enrollments where
StudentID is common in both, and the result contains only unique columns.

Outer Join

An outer join returns all rows from one relation, and the matching rows from the other relation.
If there is no match, the result will still include all rows from the outer relation with NULL
values in the columns from the unmatched relation.
●​ Left Outer Join: A left outer join returns all rows from the left relation and the
matching rows from the right relation. If there is no match, the result will include
NULL values for the right relation’s attributes. Example: Joining Employees with
Departments using a left outer join ensures all employees are listed, even those who
aren't assigned to any department, with NULL values for the department columns.
●​ Right Outer Join: A right outer join returns all rows from the right relation and the
matching rows from the left relation. If no match exists, the left relation's columns
will contain NULL values. Example: Joining Departments with Employees using a
right outer join includes all departments, even those with no employees assigned,
filling unmatched employee columns with NULL.
●​ Full Outer Join: A full outer join returns all rows when there is a match in either the
left or right relation. If a row from one relation does not have a match in the other,
NULL values are included for the missing side. Example: Joining Customers and
Orders using a full outer join will return all customers and orders, even if there’s no
corresponding order for a customer or no customer for an order.
1. Left Outer join

Left Outer Join returns all rows of a table on the left side of the join. For the
rows for which there is no matching row on the right side, the result contains
NULL on the right side.
Left Outer Join returns all the rows from the left table and matching rows
from the right table. If a row in the left table does not have a matching row in
the right table, the result set will include NULL values for the columns in the
right table.

Left Outer Join

Syntax:
SELECT T1.C1, T2.C2​
FROM TABLE T1 ​
LEFT JOIN TABLE T2 ​
ON T1.C1= T2.C1;

Query:
SELECT [Link],[Link]​
FROM Student​
LEFT OUTER JOIN StudentCourse​
ON [Link] = [Link]​
ORDER BY [Link];
Note: OUTER is optional above. Simple LEFT JOIN is also considered as LEFT
OUTER JOIN

StudentName CourseID

geek4 NULL

geek2 1

geek1 1

geek1 2

geek3 2

geek1 3

2. Right Outer Join

Right Outer Join is similar to Left Outer Join (Right replaces Left everywhere).
Right Outer Join returns all the rows from the right table and matching rows
from the left table. If a row in the right table does not have a matching row in
the left table, the result set will include NULL values for the columns in the
left table.

Right Outer Join

Syntax:
SELECT T1.C1, T2.C2​
FROM TABLE T1

RIGHT JOIN TABLE T2 ​

ON T1.C1= T2.C1;

Example:
Table Record

Ag
Roll_Number Name
e
 1 Harsh 18

2 Ankesh 19

3 Rupesh 18

4 Vaibhav 15

5 Naveen 13

6 Shubham 15

7 Ankit 19

8 Bhupesh 18

Table Course
Course_ID Roll_Number

1 1

2 2

2 3

3 4

1 5

4 9

5 10
 4 11

Query:
SELECT [Link],Course.COURSE_ID​
FROM Record​
RIGHT JOIN Course​
ON Course.Roll_Number = Record.Roll_Number;

Output:

Name Course_ID

Harsh 1

Ankesh 2

Rupesh 2
 Vaibha
3
v

Naveen 1

NULL 4

NULL 5

NULL 4

3. Full Outer Join

Full Outer Join contains the results of both the Left and Right outer joins. It is
also known as cross-join. It will provide a mixture of two tables.
Full Outer Join returns all the rows from both tables, including matching and
non-matching rows. If a row in one table does not have a matching row in the
other table, the result set will include NULL values for the columns in the
table that do not have a match.
 Full Outer Join

Syntax:
SELECT * FROM T1

CROSS-JOIN T2;

Query:
SELECT [Link],Course.COURSE_ID​
FROM Record​
FULL JOIN Course​
ON Course.Roll_Number = Record.Roll_Number;

Output:

Name Course_ID

Harsh 1

Ankesh 2
Rupesh 2

Vaibhav 3

Naveen 1

Shubham NULL

Ankit NULL

Bhupesh NULL

NULL 4

NULL 5
 NULL 4

How To Use Outer Join?

Outer Join is performed by the selection of the records from all the tables
given there. Left Outer Join works by selecting all records from the left table
and matching records from the right table. Similarly, Right Outer Join works
by selecting all records from the right table and matching records from the
left table and the Full Outer Join returns all records if a match occurs in the
left or the right table.

2. Set Intersection(∩)

Set Intersection basically allows to fetches only those rows of data that are common between two
sets of relational tables. Set Intersection in relational algebra is the same set intersection
operation in set theory.
Example: Consider the following table of Students having different optional subjects in their
course.
Relation FRENCH

Student_Name Roll_Number

Ram 01

Mohan 02
 Vivek 13

Geeta 17

Relation GERMAN

Student_Name Roll_Number

Vivek 13

Geeta 17

Shyam 21

Rohan 25

From the above table of FRENCH and GERMAN, the Set Intersection is used as follows:
π(Student_Name)(FRENCH ∩ π(Student_Name)(GERMAN)
Output:

Student_Name

Vivek

Geeta

Explanation: The only constraint in the Set Difference between two relations is that both
relations must have the same set of Attributes.

3. Division (÷)

The Division Operator is used to find tuples in one relation that are related to all tuples in another
relation. It’s typically used for "for all" queries.
Student_Course (Dividend Table):

Student_ID Course_ID

101 C1
 101 C2

102 C1

103 C1

103 C2

Course (Divisor Table):

Course_ID

C1
 C2

Example: Query is to find students who are enrolled in all courses listed in the Course table. In
this case, students must be enrolled in both C1 and C2.
Student_Course(Student_ID, Course_ID)÷ Course(Course_ID)
Output:

Student_ID

101

103

Relational Calculus
Relational calculus is a non-procedural query language used in the context of relational algebra.
It focuses on what data to retrieve, rather than how to retrieve it, making it different from
relational algebra, which is procedural. In relational calculus, queries are expressed using logical
formulas that describe the desired result, without specifying the exact steps to get there.
There are two types of Relational Calculus
1.​ Tuple Relational Calculus(TRC)
2.​ Domain Relational Calculus(DRC)
Tuple Relational Calculus (TRC) in DBMS
Tuple Relational Calculus (TRC) is a non-procedural query language used to

retrieve data from relational databases by describing the properties of the

required data (not how to fetch it).​

It is based on first-order predicate logic and uses tuple variables to

represent rows of tables.

Syntax: The basic syntax of TRC is as follows:

{ t | P(t) }

●​ t: Tuple variable (row placeholder)

●​ P(t): Predicate condition to satisfy

●​ {}: Denotes a set of result tuples

Logical Operators in TRC:

●​ ∧: AND

●​ ∨: OR

●​ ¬: NOT

Quantifiers:
 ●​ ∃ t ∈ r (Q(t)) → There exists a tuple t in relation r satisfying

predicate Q(t)

●​ ∀ t ∈ r (Q(t)) → For all tuples t in relation r, predicate Q(t) holds

For example, let's say we have a table called "Employees" with the following

attributes:

Employee ID

Name

Salary

Department
ID

To retrieve the names of all employees who earn more than $50,000 per

year, we can use the following TRC query:

{ t | Employees(t) ∧ [Link] > 50000 }

Explanation:

●​ Employees(t) means t is a tuple from the Employees table.

●​ ∧ (AND) is used to add a condition on salary.

●​ The result is a set of tuples where each employee earns more than

$50,000.

TRC is non-procedural - it specifies what data to retrieve, not how to retrieve

it.

While expressive, TRC is more abstract and mainly used in academic or

theoretical contexts, not practical database systems.

Tuple Relational Query

In Tuple Calculus, a query is expressed as

{t| P(t)}

●​ t represents the resulting tuples.

●​ P(t) is a predicate (a condition that must be true for t to be included

in the result

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.

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.

{<a1,a2,a3,.....an> | P(a1,a2,a3,.....an)}

where a1,a2,...an are the attributes of the relation and P is the condition.

Tuple Relational Calculus Examples

Table Customer

Stree
Customer name City
t

Saurabh A7 Patiala
 Mehak B6 Jalandhar

Sumiti D9 Ludhiana

Ria A5 Patiala

Table Branch

Branch name Branch City

ABC Patiala

DEF Ludhiana

GHI Jalandhar
Table Account

Account number Branch name Balance

1111 ABC 50000

1112 DEF 10000

1113 GHI 9000

1114 ABC 7000

Table Loan

Loan number Branch name Amount

 L33 ABC 10000

L35 DEF 15000

L49 GHI 9000

L98 DEF 65000

Table Borrower

Customer name Loan number

Saurabh L33

Mehak L49
 Ria L98

Table Depositor

Customer name Account number

Saurabh 1111

Mehak 1113

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}

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)}

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])}

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

Key Concepts:

●​ TRC does not specify execution steps, only the condition of result.
 ●​ It focuses on what to retrieve, not how.

●​ Based on variables, predicates, and quantifiers.

●​ More theoretical, often used in database theory, formal methods,

and GATE questions.

Comparison: TRC vs Relational Algebra

Relational
Feature TRC
Algebra

Type Non-procedural Procedural

How to
Focus What to retrieve
retrieve

Set-based
Expression Style Logical expressions
operators
 Directly
Abstract, not directly
Execution convertible
executable
to query

Basis for
Use in DBMS Theoretical foundation query
execution

Important Points to Remember:

●​ TRC is declarative: Describes result conditions, not how to get

them.

●​ Uses tuple variables referring to rows.

●​ Allows quantifiers and logic operators for flexible querying.

●​ Similar to mathematical set notation.

●​ Does not include built-in operators like SQL or Relational Algebra.

●​ Each TRC query defines a set of tuples satisfying given predicates.

●​ Safer queries (domain-independent) always produce meaningful

results regardless of database size or content.

●​
Tuple Relational Calculus (TRC)

In Tuple Relational Calculus (TRC), queries are based on finding tuples that satisfy
specific conditions. Let’s look at two examples and explain how the workflow of the
queries proceeds step by step.

Example 1: Find Students with Grade ‘A’

Consider the “Students” table:

ID Name Age Grade

1 Alice 20 A

2 Bob 19 B

3 Charlie 22 A

4 David 18 C

TRC Query:

{ t | t ∈ Students ∧ [Link] = ‘A’ }

Workflow:

●​ Define Tuple Variable: Here, t is a tuple variable representing rows in the

“Students” table.
●​ Set the Condition: The condition [Link] = ‘A’ checks if the “Grade” attribute is
‘A’.
●​ Filter the Table: The query searches each row in the “Students” table and selects
those that meet the condition.
●​ Output the Result: Only rows where the grade is ‘A’ are returned.
Result:

ID Name Age Grade

1 Alice 20 A

3 Charlie 22 A

Example 2: Find Students Who Are Older Than 18

TRC Query:

{ t | t ∈ Students ∧ [Link] > 18 }

Workflow:

●​ Define Tuple Variable: The tuple variable t represents rows in the “Students”
table.
●​ Set the Condition: The condition [Link] > 18 filters out students older than 18.
●​ Filter the Table: Each row is checked, and only those where the “Age” attribute is
more than 18 are selected.
●​ Output the Result: The query returns rows that satisfy the age condition.

Result:

ID Name Age Grade

1 Alice 20 A

2 Bob 19 B

3 Charlie 22 A

Domain Relational Calculus (DRC)

Domain Relational Calculus (DRC) uses individual attributes (domains) rather than
whole tuples. Let’s see two examples and the workflow of these queries.

Example 1: Find the Names of Students with Grade ‘A’

Consider the “Students” table:

ID Name Age Grade

1 Alice 20 A

2 Bob 19 B

3 Charlie 22 A

4 David 18 C

DRC Query:

{<Name> | ∃ ID, Age, Grade (Students(ID, Name, Age, Grade) ∧ Grade = ‘A’)}

Workflow:

●​ Define Domain Variables: The variables represent the attributes of the “Students”
table (ID, Name, Age, Grade).
●​ Specify the Condition: The condition Grade = ‘A’ is applied to filter students who
have an ‘A’ grade.
●​ Match the Records: The query checks each row to see if it meets the condition.
●​ Return Attribute Values: Instead of returning the whole row, only the “Name” of
students with grade ‘A’ is retrieved.

Result:
 Name

Alice

Charlie

Example 2: Find the Names and Ages of Students Older Than 18

DRC Query:

{<Name, Age> | ∃ ID, Grade (Students(ID, Name, Age, Grade) ∧ Age > 18)}

Workflow:

●​ Define Domain Variables: Variables Name and Age represent the attributes that
will be in the output.
●​ Set the Condition: The predicate Age > 18 checks which students meet this age
requirement.
●​ Evaluate Each Row: The query scans through the rows in the “Students” table.
●​ Output Specified Attributes: The “Name” and “Age” values of students older than
18 are returned.

Result:

Name Age

Alice 20

Bob 19

Charlie 22

Comparison Between TRC and DRC

 Aspect Tuple Relational Calculus Domain Relational Calculus
(TRC) (DRC)

Variables Uses tuple variables Uses domain variables

Used representing entire rows. representing individual
attribute values.

Expression { t | P(t) } where t is a tuple {<x1, x2, …, xn> | P(x1, x2,

Structure and `P(t)` is a predicate. …, xn)}` where variables
represent attributes.

Focus Queries entire tuples (rows). Queries specific attributes

(domains) of tuples.

Result Returns tuples that satisfy Returns attribute values that

Format the condition. meet the criteria.

Ease of Use More natural for querying Better suited for simple
full rows and complex queries involving specific
relationships. fields.

Example { t | t ? Students ? [Link] = {<Name> | ? ID, Age, Grade

Query Style ‘A’ } (Students(ID, Name, Age,
Grade) ? Age = 20)}

Common Use Often used for retrieving Used to fetch specific columns
Case entire records that match a based on certain conditions.
certain criteria.

How Relational Calculus Works in DBMS

In general terms, relational calculus serves as a logical query language in a database
management system (DBMS). It enables users to phrase queries in a more formal
manner, focusing on what data needs to be retrieved from the database rather than how
to go about the retrieval. Here’s how relational calculus functions in a DBMS:

1. Query Expression:
●​ Users write queries using relational calculus expressions. These expressions
consist of logical conditions or predicates that specify the data requirements.
●​ For Tuple Relational Calculus (TRC), queries are expressed in the form { t | P(t)
}, where t represents a tuple, and P(t) is a condition that the tuple must satisfy.
●​ For Domain Relational Calculus (DRC), queries are expressed as {<x1, x2, …,
xn> | P(x1, x2, …, xn)}, where <x1, x2, …, xn> represents attribute values, and P
is the condition.

2. Evaluation of Conditions:

●​ The DBMS evaluates the conditions specified in the query. It checks which
records in the database satisfy the logical predicates.
●​ For TRC, this involves filtering entire tuples (rows) based on the conditions. For
DRC, it involves filtering attribute values within those tuples.

3. Fetching Data from Relations (Tables):

●​ The relational calculus expression is applied to the relevant database relations

(tables).
●​ The DBMS scans the tables to find the tuples or attribute values that meet the
specified conditions.

4. Logical Optimization:

●​ Before executing the query, the DBMS may optimise the logical expression to
improve performance.
●​ This step involves rewriting the logical conditions or rearranging operations
without changing the result, making the query execution faster.
 5. Result Compilation:

●​ The data that satisfies the conditions is collected and compiled into a result set.
●​ For TRC, the result includes tuples that meet the criteria. For DRC, the result
contains specific attribute values that match the condition.

6. Displaying Results:

●​ Finally, the DBMS displays the output of the query to the user.
●​ The results can be presented as tables (for TRC) or as a list of values (for DRC),
depending on the nature of the query.

Use Cases of Relational Calculus in DBMS

●​ Query Language Foundation: Forms the basis for declarative query languages
like SQL, allowing users to express data retrieval in logical terms.
●​ Query Optimization: Helps in optimising complex queries by providing a formal
structure to simplify logical expressions.
●​ Theoretical Framework: Used in academic and research fields to teach the
fundamentals of database theory and query formulation.
●​ Data Filtering and Selection: Helps in specifying conditions for selecting data
from large datasets without detailing the procedural steps.
●​ Automatic Query Processing: Facilitates automated query processing by focusing
on the conditions needed to retrieve data, rather than the methods.

Advantages of Relational Calculus

●​ Simplicity: Allows users to specify “what” data to retrieve without knowing “how”
the query will be executed.
●​ Flexibility: Can represent a wide range of queries using logical conditions.
 ●​ Theoretical Significance: Serves as a foundation for understanding the principles
of database management and query languages.
●​ Declarative Nature: Makes it easier to express complex queries compared to
procedural approaches.
●​ Logical Representation: Provides a formal way to describe database queries
mathematically.

Limitations of Relational Calculus in DBMS

●​ Lack of Procedural Information: Does not provide any indication of how the query
will be executed, which can be inefficient for large datasets.
●​ Complexity in Expression: Writing queries with multiple conditions can become
complicated and hard to understand.
●​ Limited Practical Use: Not directly used as a practical querying method in most
real-world applications; more relevant for theoretical purposes.
●​ Performance Concerns: Since it is purely declarative, performance optimization
may require additional steps.
●​ Difficult Error Handling: Diagnosing errors in relational calculus queries can be
challenging due to the abstract nature of the expressions.

Best Practices for Using Relational Calculus

●​ Keep Queries Simple: Use straightforward conditions to make expressions easier
to understand and maintain. Avoid overly complex predicates.
●​ Use Logical Connectives Carefully: When combining conditions with AND (∧)
and OR (∨), make sure the expressions are logically consistent to avoid
incorrect results.
●​ Optimise Query Conditions: Arrange conditions to reduce the number of
comparisons. This can help in speeding up query evaluation.
●​ Avoid Redundant Predicates: Eliminate unnecessary conditions that don’t add
value to the query. This helps in keeping the expressions clean and efficient.
●​ Document Your Queries: When writing complex queries, add comments or notes
to explain the conditions and logical flow, making it easier to review or update
later.
●​ Understand the Data Model: Familiarise yourself with the database schema to
accurately reference attributes and relations in your queries.
●​ Begin with Simple Queries: If you are using relational calculus for the first time,
start by executing only the initial simple queries, then build them up step by step
in the future. As you gain more experience, you can increase the level of queries
gradually.
●​ Try Running the Queries on Smaller Datasets First: It is advisable to run the
queries on a smaller set of data prior to executing them on a larger data set, to
check if everything is working correctly.
●​ Use Quantifiers as Intended: Use universal (∀) and existential (∃) quantifiers in
a way that clearly reflects the intended logic of the query.
●​ Use it After Relational Algebra: Apply the concepts from relational algebra to the
transformation or simplification of relational calculus queries for enhanced
efficiency.
DBMS Integrity Constraints

Integrity constraints are a set of rules used in DBMS to ensure that the data

in a database is accurate, consistent and reliable. These rules helps in

maintaining the quality of data by ensuring that the processes like adding,

updating or deleting information do not harm the integrity of the database.

Integrity constraints also define how different parts of the database are

connected and ensure that these relationships remain valid. They play an

essential role in making sure the data is meaningful and follows the logical

structure of the database.

What are Integrity Constraints ?

Integrity constraints in a Database Management System are rules that help

keep the data in a database accurate, consistent and reliable. They act like a
set of guidelines that ensure all the information stored in the database

follows specific standards.

Integrity Constraints

Example: Making sure every customer has a valid email address & ensuring

that an order in the database is always linked to an existing customer.

Note: These rules prevent mistakes, such as adding incomplete or incorrect

data, and make sure the database is secure and well-organized.

Types of Integrity Constraints

There are Different types of Integrity Constraints used in DBMS, these are:

1.​ Domain Constraints

2.​ Entity Integrity Constraints

 3.​ Key Constraints

4.​ Referential integrity constraints

5.​ Assertion

6.​ Triggers

1. Domain Constraints

Domain constraints are a type of integrity constraint that ensure the values

stored in a column (or attribute) of a database are valid and within a specific

range or domain. In simple terms, they define what type of data is allowed in

a column and restrict invalid data entry. The data type of domain include

string, char, time, integer, date, currency etc. The value of the attribute must

be available in comparable domains.

Example: Below table demonstrates domain constraints in action by

enforcing rules for each column

Student_Id Name Semester Age

21CSE100 Aniket Kumar 6th 20

 Shashwat
21CSE101 7th 21
Dubey

Manvendra
21CSE102 8th 22
Sharma

21CSE103 Ashmit Dubey 5th 20

1.​ Student_Id: Must be unique and follow a specific format like

21CSE###. No duplicates or invalid formats allowed.

2.​ Name: Accepts only valid text (no numbers) and cannot be left

empty (NOT NULL constraint).

3.​ Semester: Allows specific values like 5th, 6th, etc., and ensures

valid input (e.g., no 10th if not permitted).

4.​ Age: Must be an integer within a reasonable range (e.g., 18-30) and

cannot contain invalid data like negative numbers or text.

Types of Domain Constraints:

●​ NOT NULL Constraint: Ensures No records can have NULL value.

 ●​ CHECK Constraint: This Constraint Checks for any specified

condition over any attribute.

Why Domain Constraints Are Important :

●​ They prevent invalid or inconsistent data from entering the

database.

●​ They ensure the database is reliable and follows predefined

business rules.

●​ They make the database easier to manage and maintain by reducing

errors.

Example: Let, the not-null constraint be specified on the "Semester" attribute

in the relation/table given below, then the data entry of 4th tuple will violate

this integrity constraint, because the "Semester" attribute in this tuple

contains null value. To make this database instance a legal instance, its entry

must not be allowed by database management system.

Ag
Student_id Name Semester
e
 21CSE1001 Sonali Rao 5th 20

21CSE1012 Anjali Gupta 5th 21

21CSE1023 Aastha Singh 5th 22

21CSE1034 Ayushi Singh NULL 20

Read more about Domain Constraints and its types, Here.

2. Entity Integrity Constraints

Entity integrity constraints state that primary key can never contain null value

because primary key is used to determine individual rows in a relation

uniquely, if primary key contains null value then we cannot identify those

rows. A table can contain null value in it except primary key field.

Key Features of Entity Integrity Constraints:

 ●​ Uniqueness: The primary key value must be unique for each row in

the table. No duplicate entries are allowed in the primary key

column.

●​ NOT NULL: The primary key column cannot contain NULL values, as

every row must have a valid identifier.

●​ Essential for Table Design: Ensures that every record in the table

can be uniquely identified, preventing ambiguity.

Example: It is not allowed because it is containing primary key (Student_id)

as NULL value.

Student_id Name Semester Age

21CSE101 Ramesh 5th 20

21CSE102 Kamlesh 5th 21

 21CSE103 Aakash 5th 22

NULL Mukesh 5th 20

3. Key Constraints

Key constraints ensure that certain columns or combinations of columns in a

table uniquely identify each row. These rules are essential for maintaining

data integrity and preventing duplicate or ambiguous records.

Why Key Constraints Are Important ?

●​ Prevent Duplicates: Ensure unique identification of rows.

●​ Maintain Relationships: Enable proper linking between tables (via

foreign keys).

●​ Enforce Data Integrity: Prevent invalid or inconsistent data.

Example: It is now acceptable because all rows must be unique.

 Student_id Name Semester Age

21CSE101 Ramesh 5th 20

21CSE102 Kamlesh 5th 21

21CSE103 Aakash 5th 22

21CSE102 Mukesh 5th 20

3.1 Primary Key Constraints

It states that the primary key attributes are required to be unique and not

null. That is, primary key attributes of a relation must not have null values

and primary key attributes of two tuples must never be same. This constraint
is specified on database schema to the primary key attributes to ensure that

no two tuples are same.

Example: Here, in the below example the Student_id is the primary key

attribute. The data entry of 4th tuple violates the primary key constraint that

is specifies on the database schema and therefore this instance of database is

not a legal instance.

Ag
Student_id Name Semester
e

101 Ramesh 5th 20

102 Kamlesh 5th 21

103 Akash 5th 22

●​ Unique Values: Each student_id must be unique. 101, 102, 103 are

valid. Inserting 101 again would result in an error.

 ●​ Not NULL: student_id cannot be NULL.

●​ Invalid: A row with NULL for student_id will be rejected.

3.2 Unique Key Constraints

The Unique key constraint in DBMS ensures that all values in a specified

column (or group of columns) are distinct across the table. It prevents

duplicate entries, maintaining data integrity, but unlike the primary key, it

allows one NULL value.

Example: Here, in the below example the Email column has NULL value in

2nd record.

Employee_ID Email Name

1 aniket@[Link] Aniket Kumar

2 NULL Shashwat Dubey

 3 shashwat@[Link] Manvendra Sharma

●​ Unique Values: The email column must contain unique values.

aniket@[Link] and shashwat@[Link] are valid.

Adding another row with aniket@[Link] would result in an

error.

●​ Allows One NULL: The email column can contain one NULL value.

●​ Valid: NULL in the second row.

●​ Invalid: Adding another row with NULL in email will be rejected.

4. Referential integrity constraints

Referential integrity constraints are rules that ensure relationships between

tables remain consistent. They enforce that a foreign key in one table must

either match a value in the referenced primary key of another table or be

NULL. This guarantees the logical connection between related tables in a

relational database.

Why Referential Integrity Constraints Are Important ?

●​ Maintains Consistency: Ensures relationships between tables are

valid.
 ●​ Prevents Orphan Records: Avoids cases where a record in a child

table references a non-existent parent record.

●​ Enforces Logical Relationships: Strengthens the logical structure of

a relational database.

Example: Here, in below example Block_No 22 entry is not allowed because

it is not present in 2nd table.

Student_id Name Semester Block_No

22CSE101 Ramesh 5th 20

21CSE105 Kamlesh 6th 21

22CSE102 Aakash 5th 20

 23CSE106 Mukesh 2nd 22

Block_No Block Location

20 Chandigarh

21 Punjab

25 Delhi

To read about SQL FOREIGN KEY Constraint Refer, Here.

5. Assertion
An assertion is a declarative mechanism in a database that ensures a specific

condition or rule is always satisfied across the entire database. It is a global

integrity constraint, meaning it applies to multiple tables or the entire

database rather than being limited to a single table or column. An assertion

in SQL-92 takes the form:

create assertion <assertion-name> check <predicate>

When an assertion is made, the system tests it for validity. This testing may

introduce a significant amount of overhead; hence assertions should be used

with great care.

Example of an Assertion:

CREATE ASSERTION sum_constraint​

CHECK (​
NOT EXISTS (​
SELECT *​
FROM branch​
WHERE (​
SELECT SUM(amount)​
FROM loan​
WHERE loan.branch_name = branch.branch_name​
) >= (​
SELECT SUM(amount)​
FROM account​
WHERE account.branch_name = branch.branch_name​
 )​
)​
);

Explanation:

The following SQL statement creates an assertion to ensure that the total

loan amount at each branch is always less than the total account balances at

the same branch.

●​ Purpose: This assertion enforces a global business rule. The sum of

all loan amounts for a branch must always be less than the sum of

all account balances in the same branch. This prevents branches

from issuing loans beyond their financial capacity.

●​ Subqueries: The first subquery (SELECT SUM(amount) FROM loan)

calculates the total loan amount for each branch. The second

subquery (SELECT SUM(amount) FROM account) calculates the

total balance of accounts for the same branch.

●​ Condition: The NOT EXISTS clause ensures there is no branch

where the loan amount is greater than or equal to the account

balance.

●​ Behavior: If a transaction (e.g., inserting a loan or updating an

account) violates this rule, the operation will be rejected by the

database.
6. Triggers

A trigger is a procedural statement in a database that is automatically

executed in response to certain events such as INSERT, UPDATE, or DELETE.

Triggers are often used to enforce complex integrity constraints or implement

business rules that cannot be captured using standard constraints like

primary keys or foreign keys.

Example SQL Trigger:

CREATE TRIGGER handle_overdraft​

AFTER UPDATE ON account​
FOR EACH ROW​
BEGIN​
-- Check if the balance has become negative after the update​
IF [Link] < 0 THEN​
-- Set the account balance to zero​
UPDATE account​
SET balance = 0​
WHERE account_number = NEW.account_number;​
​
-- Create a loan record with the same account number as the loan
number​
INSERT INTO loan (loan_number, loan_amount)​
VALUES (NEW.account_number, ABS([Link])); -- ABS to ensure
positive loan amount​
END IF;​
END;
Explanation:

●​ Trigger Type: The trigger runs after an update on the account table.

●​ Condition: It checks if the account balance is negative after the

update.

●​ Actions: If the balance is negative, the account balance is reset to

zero. A loan is created with the same account number as the loan

number, and the loan amount is the absolute value of the negative

balance. The ABS() function ensures the loan amount is positive.

Integrity Constraints in Relational Databases

Constraints are rules applied to data in a relational database to maintain correctness and
consistency.​
Two very important ones are:

1.​ Entity Integrity Constraint​

2.​ Referential Integrity Constraint​

These constraints help avoid anomalies (insertion, deletion, update problems).

1. Entity Integrity Constraint

●​ Rule: Primary key cannot be NULL or duplicate.​

●​ Why? → Every row must be uniquely identifiable.​

✅ Example:
Suppose we have a table Student:

StudentID (PK) Name Course

 101 Riya DBMS

102 Amit CN

●​ ​
Entity Integrity ensures:​

○​ No two students can have the same StudentID.​

○​ StudentID cannot be empty.​

How it prevents anomalies:

●​ Insertion anomaly: Stops adding a student without ID.​

●​ Update anomaly: Prevents two different students from sharing the same ID.​

●​ Deletion anomaly: Ensures that deletion of one student doesn’t confuse identity with
another (since IDs are unique).​

2. Referential Integrity Constraint

●​ Rule: A foreign key must match an existing primary key in the parent table, or be
NULL.​

●​ Why? → Relationships between tables remain valid.​

Example:

Two tables:

Course (Parent)

CourseID (PK) CourseName

C1 DBMS

C2 CN

Enrollment (Child)
 EnrollID StudentID CourseID (FK)

E1 101 C1

E2 102 C2

●​ ​
Referential Integrity ensures:​

○​ Every CourseID in Enrollment must exist in Course table.​

○​ You cannot assign a student to a non-existing course (e.g., “C5”).​

How it prevents anomalies:

●​ Insertion anomaly: You can’t insert enrollment for a course that doesn’t exist.​

●​ Deletion anomaly: You can’t delete a course (say C1) if students are still enrolled in it →
prevents accidental loss of related data.​

●​ Update anomaly: If course ID changes, it must be updated in both tables, or else it’s
rejected.​

(In short)
●​ Entity Integrity keeps each row unique → prevents duplicate/missing identities →
avoids anomalies.​

●​ Referential Integrity keeps relationships valid → prevents dangling references,

accidental deletions, and inconsistent updates.​

Together, these constraints maintain database consistency and prevent insertion, deletion,
and update anomalies.
Types of Functional dependencies in DBMS
Last Updated : 23 Jul, 2025

​
​
​

In relational database management, functional dependency is a concept that

specifies the relationship between two sets of attributes where one attribute

determines the value of another attribute. It is denoted as X → Y, where the

attribute set on the left side of the arrow, X is called Determinant, and Y is

called the Dependent.

What is Functional Dependency?

A functional dependency occurs when one attribute uniquely determines

another attribute within a relation. It is a constraint that describes how

attributes in a table relate to each other. If attribute A functionally determines

attribute B we write this as the A→B.

Functional dependencies are used to mathematically express relations

among database entities and are very important to understanding advanced

concepts in Relational Database Systems.

Example:
roll_no name dept_name dept_building

42 abc CO A4

43 pqr IT A3

44 xyz CO A4

45 xyz IT A3

46 mno EC B2

47 jkl ME B2
From the above table we can conclude some valid functional

dependencies:

●​ roll_no → { name, dept_name, dept_building }→ Here, roll_no can

determine values of fields name, dept_name and dept_building,

hence a valid Functional dependency

●​ roll_no → dept_name , Since, roll_no can determine whole set of

{name, dept_name, dept_building}, it can determine its subset

dept_name also.

●​ dept_name → dept_building , Dept_name can identify the

dept_building accurately, since departments with different

dept_name will also have a different dept_building

●​ More valid functional dependencies: roll_no → name, {roll_no,

name} ⇢ {dept_name, dept_building}, etc.

Here are some invalid functional dependencies:

●​ name → dept_name Students with the same name can have

different dept_name, hence this is not a valid functional dependency.

●​ dept_building → dept_name There can be multiple departments in

the same building. Example, in the above table departments ME and

EC are in the same building B2, hence dept_building → dept_name

is an invalid functional dependency.

 ●​ More invalid functional dependencies: name → roll_no, {name,

dept_name} → roll_no, dept_building → roll_no, etc.

Read more about What is Functional Dependency in DBMS ?

Types of Functional Dependencies in DBMS

1.​ Trivial functional dependency

2.​ Non-Trivial functional dependency

3.​ Multivalued functional dependency

4.​ Transitive functional dependency

1. Trivial Functional Dependency

In Trivial Functional Dependency, a dependent is always a subset of the

determinant. i.e. If X → Y and Y is the subset of X, then it is called trivial

functional dependency.​

Symbolically: A→B is trivial functional dependency if B is a subset of A.

The following dependencies are also trivial: A→A & B→B

Example 1 :

●​ ABC -> AB

●​ ABC -> A
 ●​ ABC -> ABC

Example 2:

roll_no name age

42 abc 17

43 pqr 18

44 xyz 18

Here, {roll_no, name} → name is a trivial functional dependency, since the

dependent name is a subset of determinant set {roll_no, name}. Similarly,

roll_no → roll_no is also an example of trivial functional dependency.

2. Non-trivial Functional Dependency

In Non-trivial functional dependency, the dependent is strictly not a subset

of the determinant. i.e. If X → Y and Y is not a subset of X, then it is called

Non-trivial functional dependency.

Example 1 :

●​ Id -> Name

●​ Name -> DOB

Example 2:

roll_no name age

42 abc 17

43 pqr 18

44 xyz 18

Here, roll_no → name is a non-trivial functional dependency, since the

dependent name is not a subset of determinant roll_no. Similarly, {roll_no,

name} → age is also a non-trivial functional dependency, since age is not a

subset of {roll_no, name}

3. Semi Non Trivial Functional Dependencies

A semi non-trivial functional dependency occurs when part of the dependent

attribute (right-hand side) is included in the determinant (left-hand side), but

not all of it. This is a middle ground between trivial and non-trivial functional

dependencies. X -> Y is called semi non-trivial when X intersect Y is not

NULL.

Example:​

Consider the following table:

Student_ID Course_ID Course_Name

101 CSE101 Computer Science

102 CSE102 Data Structures

 103 CSE101 Computer Science

Functional Dependency:

{StudentID,CourseID}→CourseID

This is semi non-trivial because:

●​ Part of the dependent attribute (Course_ID) is already included in the

determinant ({Student_ID, Course_ID}).

●​ However, the dependency is not completely trivial because

{StudentID}→CourseID is not implied directly.

4. Multivalued Functional Dependency

In Multivalued functional dependency, entities of the dependent set are not

dependent on each other. i.e. If a → {b, c} and there exists no functional

dependency between b and c, then it is called a multivalued functional

dependency.

Example:
bike_model manuf_year color

tu1001 2007 Black

tu1001 2007 Red

tu2012 2008 Black

tu2012 2008 Red

tu2222 2009 Black

 tu2222 2009 Red

In this table:

●​ X: bike_model

●​ Y: color

●​ Z: manuf_year

For each bike model (bike_model):

1.​ There is a group of colors (color) and a group of manufacturing

years (manuf_year).

2.​ The colors do not depend on the manufacturing year, and the

manufacturing year does not depend on the colors. They are

independent.

3.​ The sets of color and manuf_year are linked only to bike_model.

That’s what makes it a multivalued dependency.

In this case these two columns are said to be multivalued dependent on

bike_model. These dependencies can be represented like this:

Read more about Multivalued Dependency in DBMS.

5. Transitive Functional Dependency

In transitive functional dependency, dependent is indirectly dependent on

determinant. i.e. If a → b & b → c, then according to axiom of transitivity, a →

c. This is a transitive functional dependency.

Example:

dep
enrol_no name building_no
t

42 abc CO 4

43 pqr EC 2

44 xyz IT 1

45 abc EC 2
Here, enrol_no → dept and dept → building_no. Hence, according to the

axiom of transitivity, enrol_no → building_no is a valid functional

dependency. This is an indirect functional dependency, hence called

Transitive functional dependency.

6. Fully Functional Dependency

In full functional dependency an attribute or a set of attributes uniquely

determines another attribute or set of attributes. If a relation R has attributes

X, Y, Z with the dependencies X->Y and X->Z which states that those

dependencies are fully functional.

Read more about Fully Functional Dependency.

7. Partial Functional Dependency

In partial functional dependency a non key attribute depends on a part of the

composite key, rather than the whole key. If a relation R has attributes X, Y, Z

where X and Y are the composite key and Z is non key attribute. Then X->Z is

a partial functional dependency in RBDMS.

Read more about Partial Dependency.

Conclusion
Functional dependency is very important concept in database management

system for ensuring the data consistency and accuracy. In this article we have

discuss what is the concept behind functional dependencies and why they

are important. The valid and invalid functional dependencies and the types of

most important functional dependencies in RDBMS. We have also discussed

about the advantages of FDs.