Programme Name: BCA Semester II

Course Name & Code: Database Management System & BCA27103(T)

Class: BCA2025
Academic Session: 2025-26

Study Material
(Course Name: Database Management System and Course Code: BCA27103(T))
Module III: Relational Query Language
___________________________________________________________________________
Table of Contents

1. Introduction to Relational Query Languages.................................................................................3

1.1. What is a Relational Query Language?..................................................................................3
1.1.1. Procedural Query Languages.........................................................................................3
1.1.2. Non-Procedural (Declarative) Query Languages............................................................3
1.2. Why Do We Need Different Query Languages?.....................................................................3
1.3. Example Comparison.............................................................................................................3
2. Relational Algebra and Its Operations...........................................................................................4
2.1. What is Relational Algebra?...................................................................................................4
2.2. Basic Operations of Relational Algebra..................................................................................4
2.3. Example Scenario...................................................................................................................5
3. Join Operations in Relational Algebra............................................................................................6
3.1. Theta Join (θ Join)..................................................................................................................6
3.2. Equi Join.................................................................................................................................6
3.3. Natural Join (⨝).......................................................................................................................6
3.4. Outer Joins (Extended Joins)..................................................................................................7
3.4.1. Join Example..................................................................................................................7
3.5. Join vs Cartesian Product.......................................................................................................7
4. Tuple and Domain Relational Calculus...........................................................................................8
4.1. Tuple Relational Calculus (TRC)..............................................................................................8
4.2. Domain Relational Calculus (DRC)..........................................................................................8
4.3. Comparison: TRC vs DRC........................................................................................................9
5. Structured Query Language (SQL) and Solving Simple & Complex Queries...................................9
5.1. What is SQL?..........................................................................................................................9
5.2. Basic Structure of a SELECT Query.........................................................................................9
5.3. Simple SQL Queries..............................................................................................................10
5.4. Complex SQL Queries...........................................................................................................10

Department of Computational Sciences

Brainware University, Kolkata 1
Programme Name: BCA Semester II
Course Name & Code: Database Management System & BCA27103(T)
Class: BCA2025
Academic Session: 2025-26
5.5. Real-Life Use Case................................................................................................................12
6. Query Optimization, Stored Procedures, Cursors, and Triggers..................................................12
6.1. Query Optimization.............................................................................................................12
6.1.1. Common Techniques for Optimization:.......................................................................12
6.2. Stored Procedures...............................................................................................................12
6.3. Cursors.................................................................................................................................13
6.4. Triggers................................................................................................................................13

Department of Computational Sciences

Brainware University, Kolkata 2
Programme Name: BCA Semester II
Course Name & Code: Database Management System & BCA27103(T)
Class: BCA2025
Academic Session: 2025-26

1. Introduction to Relational Query Languages

1.1. What is a Relational Query Language?

A relational query language allows users to interact with data stored in a relational
database—a collection of structured tables related by keys. These languages provide tools
for retrieving, modifying, and analyzing data based on relational principles.

They operate on relations (tables) and output new relations, forming the basis for querying
in systems like MySQL, Oracle, PostgreSQL, and MS SQL Server.

There are two primary categories of relational query languages:

1.1.1. Procedural Query Languages

These require the user to specify what data to retrieve and also how to retrieve it step-by-
step. The process includes navigation through the database and applying operations in a
defined order.

 Example: Relational Algebra

 Emphasis is on procedure—you must tell the system what path to take.

1.1.2. Non-Procedural (Declarative) Query Languages

These allow users to specify what data is needed, but the database system decides how to
retrieve it internally. This is more user-friendly and aligns with the principle of data
abstraction.

 Examples: Relational Calculus, SQL

 Emphasis is on result, not method.

1.2. Why Do We Need Different Query Languages?

 Relational Algebra is essential for understanding how query processing and
optimization work internally in DBMS.
 Relational Calculus provides a logical, mathematical foundation for queries.
 SQL is the industry-standard for actual use in real-world applications.

Together, these languages form the theoretical and practical pillars of relational databases.

1.3. Example Comparison

Language Type What it Defines Example Goal
Relational Procedural What data + how to access it List names from Student

Department of Computational Sciences

Brainware University, Kolkata 3
Programme Name: BCA Semester II
Course Name & Code: Database Management System & BCA27103(T)
Class: BCA2025
Academic Session: 2025-26
Language Type What it Defines Example Goal
Algebra
Relational Non- What data satisfies given Same goal, no access
Calculus Procedural conditions method
High-level command for DB SELECT Name FROM
SQL Declarative
access Student

2. Relational Algebra and Its Operations

2.1. What is Relational Algebra?

Relational Algebra is a procedural query language used to operate on relations (tables)
and retrieve results in the form of new relations. It provides a set of mathematical
operations that form the foundation of many database query languages, especially SQL.

In relational algebra, operations are performed step by step, and the user must define both
the data required and the method to retrieve it. Though not used directly in most real-
world applications, it is vital for understanding how queries are processed internally.

2.2. Basic Operations of Relational Algebra

1. Selection (σ)

This operation filters rows (tuples) from a relation based on a given condition.

 Syntax:
σ_condition(Relation)
 Example:
σ_{Dept = 'CSE'}(Student)
→ Returns all students from the CSE department.

2. Projection (π)

Selects specific columns (attributes) from a relation.

 Syntax:
π_{attribute list}(Relation)
 Example:
π_{Name, Age}(Student)
→ Returns only the Name and Age columns of all students.

3. Union (∪)

Combines the tuples of two relations, removing duplicates.

Department of Computational Sciences

Brainware University, Kolkata 4
Programme Name: BCA Semester II
Course Name & Code: Database Management System & BCA27103(T)
Class: BCA2025
Academic Session: 2025-26
 Condition: Both relations must have the same number of attributes and compatible
domains.

Student_CSE ∪ Student_IT
 Example:

4. Set Difference (−)

Finds tuples that are in the first relation but not in the second.

 Example:
Student − Alumni
→ Students who are not alumni.

5. Cartesian Product (×)

Returns a combination of every tuple in one relation with every tuple in another.

 Example:
Student × Course
→ All possible student-course combinations.

Often used as an intermediate step for joins, but rarely useful by itself.

6. Rename (ρ)

Renames a relation or its attributes for clarity.

 Syntax:
ρ_NewName(Relation)
or
ρ_{NewName(A1, A2)}(Relation)
 Example:
ρ_S(Student)
→ Renames the "Student" relation to "S".

2.3. Example Scenario

Let’s say we have a relation:

STUDENT(StudentID, Name, Dept)

To list all students from 'IT' department:

σ_Dept='IT'(STUDENT)

To retrieve only student names:

π_Name(STUDENT)

Department of Computational Sciences

Brainware University, Kolkata 5
Programme Name: BCA Semester II
Course Name & Code: Database Management System & BCA27103(T)
Class: BCA2025
Academic Session: 2025-26
To find students who are not in the 'Passed' table:

STUDENT – PASSED

3. Join Operations in Relational Algebra

In relational algebra, a join operation combines related tuples (rows) from two or more
relations (tables) based on a specified condition. Unlike the Cartesian product, which
creates all possible combinations, joins are more selective and meaningful—they help to
reconstruct data that has been normalized across multiple relations.

Joins are essential when data is split across different tables and needs to be combined for
query results, especially in relational database design.

3.1. Theta Join (θ Join)

The most general form of join, it combines tuples from two relations based on a condition
involving comparison operators like =, <, >, ≠, etc.

R ⨝_θ S
 Notation:

(where θ is the join condition)

 Example:

STUDENT ⨝_{[Link] = [Link]}

DEPARTMENT

This returns a relation combining student records with department details where the
department IDs match.

3.2. Equi Join

A special case of theta join where the only comparison operator used is =.

 The condition is based on attribute equality.

 Often used when attributes with the same name are being matched.
 Example:

STUDENT ⨝_{[Link] = [Link]} DEPARTMENT

This example is both a theta join and an equi join.

3.3. Natural Join (⨝)

The natural join automatically joins relations by all attributes with the same name and
domain. It removes duplicate columns in the result.

Department of Computational Sciences

Brainware University, Kolkata 6
Programme Name: BCA Semester II
Course Name & Code: Database Management System & BCA27103(T)
Class: BCA2025
Academic Session: 2025-26

R⨝S
 Notation:

 Example:

STUDENT ⨝ DEPARTMENT

If both tables have DeptID, the join will be performed automatically on that attribute, and
DeptID will appear only once in the result.

Natural joins are widely used, but require caution when multiple attributes have the same
name but are unrelated.

3.4. Outer Joins (Extended Joins)

Relational algebra also allows outer joins, although they are more commonly discussed in
SQL.

These joins include not only the matching tuples but also unmatched tuples, filling in NULL
or blanks where data is missing.

 Left Outer Join: All tuples from the left relation, with matching tuples (or NULLs)
from the right.
 Right Outer Join: Opposite of left.
 Full Outer Join: Combines both.

While not part of classical relational algebra, many modern extensions support them.

3.4.1. Join Example

Assume two relations:

STUDENT(StudentID, Name, DeptID)

DEPARTMENT(DeptID, DeptName)

A natural join:

STUDENT ⨝ DEPARTMENT

Result:

(StudentID, Name, DeptID, DeptName)

Where DeptID is matched and not repeated.

3.5. Join vs Cartesian Product

Department of Computational Sciences

Brainware University, Kolkata 7
Programme Name: BCA Semester II
Course Name & Code: Database Management System & BCA27103(T)
Class: BCA2025
Academic Session: 2025-26
Feature Cartesian Product (×) Join (⨝)
Condition None Based on attribute comparison
Output Size m × n (can be huge) Subset of Cartesian product
Relevance Often meaningless without condition Always meaningful
Usage Intermediate step for joins Used for combining related data

4. Tuple and Domain Relational Calculus

Relational Calculus is a non-procedural query language, meaning it focuses on what to
retrieve rather than how to retrieve it. Unlike relational algebra, which specifies a sequence
of operations, relational calculus expresses queries using mathematical predicates
(conditions).

It is based on first-order logic, and serves as the theoretical foundation for declarative
query languages like SQL.

There are two types of relational calculus:

4.1. Tuple Relational Calculus (TRC)

In Tuple Relational Calculus, variables represent tuples (rows) in a relation. A typical TRC
query has the form:

{ t | P(t) }

Where:

 t is a tuple variable.
 P(t) is a predicate or condition.

Example:

To get all students from the STUDENT relation who belong to the CSE department:

{ t | t ∈ STUDENT ∧ [Link] = 'CSE' }

This reads as:

"Return all tuples t from STUDENT where the department is CSE."

Existential and Universal Quantifiers:

TRC supports:

∃ (exists): checks if a value exists.

∀ (for all): checks if a condition is true for all values.



Department of Computational Sciences

Brainware University, Kolkata 8
Programme Name: BCA Semester II
Course Name & Code: Database Management System & BCA27103(T)
Class: BCA2025
Academic Session: 2025-26
4.2. Domain Relational Calculus (DRC)
In Domain Relational Calculus, variables represent individual fields (attributes) rather
than tuples.

A typical DRC query looks like this:

CopyEdit
{ <x₁, x₂, ..., xₙ> | P(x₁, x₂, ..., xₙ) }

Where:

 x₁, x₂, ..., xₙ are domain variables (attribute values).

 P(...) is a condition on those variables.

Example:

To retrieve names of all students from the CSE department:

{ <Name> | ∃ ID, Dept (STUDENT(ID, Name, Dept) ∧ Dept = 'CSE') }

This means:
"Return all Name values such that there exists an ID and Dept in STUDENT where Dept =
CSE."

4.3. Comparison: TRC vs DRC

Feature Tuple Relational Calculus (TRC) Domain Relational Calculus (DRC)
Variables
Entire Tuples Individual Attributes
Represent
Syntax `{ t condition on t }`
More readable for tuple-based
Readability More granular and field-specific
logic
Logical foundation for querying Logical foundation for querying
Theoretical Use
tuples attributes

5. Structured Query Language (SQL) and Solving Simple

& Complex Queries

5.1. What is SQL?

SQL (Structured Query Language) is the standard language used to interact with
relational databases. It is a non-procedural, declarative language, meaning users specify
what data they want, not how the system should retrieve it.

Department of Computational Sciences

Brainware University, Kolkata 9
Programme Name: BCA Semester II
Course Name & Code: Database Management System & BCA27103(T)
Class: BCA2025
Academic Session: 2025-26
SQL is inspired by relational algebra and calculus, but provides a user-friendly, practical
interface for:

 Retrieving data (SELECT)

 Modifying data (INSERT, UPDATE, DELETE)
 Defining structures (CREATE, DROP, ALTER)
 Controlling access (GRANT, REVOKE)

5.2. Basic Structure of a SELECT Query

SELECT column1, column2
FROM table_name
WHERE condition
GROUP BY column
HAVING condition
ORDER BY column ASC|DESC;

Not all clauses are required—SQL is flexible depending on the task.

5.3. Simple SQL Queries

Simple queries involve straightforward retrievals using SELECT, WHERE, and ORDER
BY clauses.

Example 1: Basic Select

SELECT Name, Age
FROM Student;

→ Returns names and ages of all students.

Example 2: Conditional Select

SELECT Name
FROM Student
WHERE Dept = 'CSE';

→ Returns names of students in the CSE department.

Example 3: Sorting Results

SELECT Name, Marks
FROM Student
ORDER BY Marks DESC;

→ Lists students sorted by marks in descending order.

5.4. Complex SQL Queries

Complex queries may involve nested queries, joins, grouping, or aggregate functions.

Department of Computational Sciences

Brainware University, Kolkata 10
Programme Name: BCA Semester II
Course Name & Code: Database Management System & BCA27103(T)
Class: BCA2025
Academic Session: 2025-26
1. Nested Queries (Subqueries)

A subquery is a query inside another query.

Example: Students with marks above the average

SELECT Name
FROM Student
WHERE Marks > (SELECT AVG(Marks) FROM Student);

2. Joins

Used to combine data from multiple tables based on related columns.

Example: List student names and department names

SELECT [Link], [Link]

FROM Student
JOIN Department ON [Link] = [Link];

Joins include:

 INNER JOIN: Only matching rows.

 LEFT JOIN: All from the left + matched from the right.
 RIGHT JOIN: All from the right + matched from the left.
 FULL JOIN: All records from both tables, matched where possible.

3. Aggregate Functions and Grouping

Used to perform operations on sets of rows.

Function Description

COUNT() Number of rows

SUM() Total sum

AVG() Average

MAX() Maximum value

MIN() Minimum value

Example: Average marks by department

SELECT Dept, AVG(Marks)

FROM Student
GROUP BY Dept;

Department of Computational Sciences

Brainware University, Kolkata 11
Programme Name: BCA Semester II
Course Name & Code: Database Management System & BCA27103(T)
Class: BCA2025
Academic Session: 2025-26
4. HAVING Clause

Used to filter grouped records.

Example: Departments with average marks above 75

SELECT Dept, AVG(Marks)

FROM Student
GROUP BY Dept
HAVING AVG(Marks) > 75;

5.5. Real-Life Use Case

Let’s say we want to:

 List students enrolled in more than 3 courses

 Who are not in the IT department
 Ordered by name alphabetically

SELECT Name
FROM Student
WHERE Dept <> 'IT'
AND StudentID IN (
SELECT StudentID
FROM Enrollment
GROUP BY StudentID
HAVING COUNT(CourseID) > 3
)
ORDER BY Name ASC;

6. Query Optimization, Stored Procedures, Cursors, and

Triggers

6.1. Query Optimization

Query optimization is the process of improving the efficiency of a SQL query by
minimizing resource usage—such as CPU time, memory, and disk I/O.

Relational databases often generate multiple execution plans for the same query and select
the most efficient one using a query optimizer.

6.1.1. Common Techniques for Optimization:

 Rewriting Queries
A well-structured query often performs better. For example, avoid using SELECT *
unless necessary.
 Using Indexes
Indexes on columns used in WHERE, JOIN, or ORDER BY clauses speed up access.

Department of Computational Sciences

Brainware University, Kolkata 12
Programme Name: BCA Semester II
Course Name & Code: Database Management System & BCA27103(T)
Class: BCA2025
Academic Session: 2025-26
 Avoiding Nested Queries
Where possible, use JOIN or EXISTS instead of nested subqueries.
 Using LIMIT / OFFSET
To restrict result size for large datasets.

6.2. Stored Procedures

A stored procedure is a named block of SQL code that is stored in the database and can be
reused.

It encapsulates logic like a function in programming, and improves performance by reducing

the amount of SQL sent from the application.

Example: Simple Stored Procedure (MySQL)

DELIMITER //

CREATE PROCEDURE GetStudentByDept(IN deptName VARCHAR(50))

BEGIN
SELECT Name, Marks FROM Student WHERE Dept = deptName;
END //

DELIMITER ;

To execute:

CALL GetStudentByDept('CSE');
Benefits:

 Reduces code duplication

 Improves security and encapsulation
 Offers better performance on repeated executions

6.3. Cursors
A cursor is a database object used to retrieve rows one at a time from a result set.

It is useful when row-by-row processing is required (e.g., updating rows with custom logic).

Types:

 Implicit Cursors: Handled automatically by SQL.

 Explicit Cursors: Defined manually by the developer.

Basic Cursor Usage (PL/SQL Style):

DECLARE
CURSOR student_cursor IS SELECT Name FROM Student;
BEGIN
FOR student_record IN student_cursor LOOP

Department of Computational Sciences

Brainware University, Kolkata 13
Programme Name: BCA Semester II
Course Name & Code: Database Management System & BCA27103(T)
Class: BCA2025
Academic Session: 2025-26
-- Do something with each student
END LOOP;
END;
Drawbacks:

 Cursors are slower than set-based operations

 Should be used only when necessary

6.4. Triggers
A trigger is a procedural code that automatically executes in response to certain events on a
table, such as INSERT, UPDATE, or DELETE.

Syntax Example (AFTER INSERT Trigger):

CREATE TRIGGER log_insert
AFTER INSERT ON Student
FOR EACH ROW
BEGIN
INSERT INTO AuditLog(Action, Time)
VALUES ('Insert on Student', NOW());
END;
Types of Triggers:

Type Description

BEFORE Executes before the triggering event

AFTER Executes after the event completes

INSTEAD OF Replaces the triggering action (views only)

Use Cases:

 Automatically updating audit logs

 Enforcing business rules
 Validating input before updates

Department of Computational Sciences

Brainware University, Kolkata 14