Relational Algebra
Relational Algebra is a procedural query language that lays the foundation for understanding how queries are processed in relational databases. Unlike SQL, which is declarative, relational algebra describes how to obtain the result rather than just stating what is required. It manipulates relations (tables) in a methodical manner using a set of operations.
Though not used directly in business applications, relational algebra plays a vital role in query optimization, RDBMS internals, and forms the conceptual base of SQL. In this blog, we’ll explore both the set-oriented and special relational operations that are part of relational algebra.
Machine Learning Tutorial:-Click Here
Data Science Tutorial:-Click Here
Complete Advance AI topics:- CLICK HERE
DBMS Tutorial:-CLICK HERE
Basic Set-Oriented Operations
These operations are derived from mathematical set theory and include:
- Union (∪)
- Intersection (∩)
- Difference (−)
- Cartesian Product (×)
Note: All set operations are binary, meaning they operate on two relations at a time. Also, for operations like union and intersection, the relations must be union-compatible, i.e., have the same number of attributes with compatible data types.
Special Relational Operations
These operations go beyond basic sets and are essential for expressing complex queries with greater precision:
- Selection (σ)
- Projection (π)
- Join (⋈)
- Division (÷)
- Rename (ρ)
Let’s look at each of them in detail.
1. Selection (σ)
The Selection operation filters tuples (rows) based on a specified condition. It’s a unary operation, meaning it works on a single relation.
Notation:
σ_condition(Relation)
Example:
Consider the LOAN relation:
| BRANCH_NAME | LOAN_NO | AMOUNT |
|---|---|---|
| Perryride | L-15 | 1500 |
| Perryride | L-16 | 1300 |
Query:
σ BRANCH_NAME = "Perryride" (LOAN)
Features:
- Operates row-wise
- Keeps all columns
- Resulting rows ≤ original
2. Projection (π)
Projection returns a relation with only specified columns. It is unary and eliminates duplication.
Notation:
π_column1, column2(Relation)
Example:
From the CUSTOMER relation:
Query:
π NAME, CITY (CUSTOMER)
Features:
- Removes unwanted columns
- Removes duplicates
- There might be fewer tuples and attributes in the resulting relation.
3. Union (∪)
Combines tuples from two relations, eliminating duplicates.
Notation:
R ∪ S
Example:
From BORROW and DEPOSITOR, get all customer names:
π CUSTOMER_NAME (BORROW) ∪ π CUSTOMER_NAME (DEPOSITOR)
Features:
- Requires union-compatible relations
- Commutative and associative
4. Intersection (∩)
only gives back the tuples shared by the two relations.
Notation:
R ∩ S
Example:
π CUSTOMER_NAME (BORROW) ∩ π CUSTOMER_NAME (DEPOSITOR)
Features:
- Requires union-compatible relations
- Commutative and associative
5. Difference (−)
Finds tuples in the first relation that are not in the second.
Notation:
R - S
Example:
π CUSTOMER_NAME (BORROW) - π CUSTOMER_NAME (DEPOSITOR)
Features:
- Not commutative or associative
- Requires union-compatible relations
6. Cartesian Product (×)
Returns all possible combinations of tuples from two relations. It is the foundation for join operations.
Notation:
R × S
Example:
Joining EMPLOYEE and DEPARTMENT without a condition:
| EMP_ID | EMP_NAME | DEPT_ID | DEPT_NO | DEPT_NAME |
|---|---|---|---|---|
| 101 | Smith | A | A | Marketing |
| … | … | … | … | … |
Properties:
- Not union-compatible
- Result size = |R| × |S|
- Degree = degree(R) + degree(S)
7. Rename (ρ)
used to change the name of a relational expression’s output or its properties.
Notation:
ρ(NewName, Relation)
Example:
ρ(STUDENT1, STUDENT)
8. Join (⋈)
Using a join condition, join merges related tuples from two relations. It’s more meaningful than a Cartesian product.
Types of Joins:
- Equi Join: Join based on equality.
- Natural Join: automatically matches identically named columns.
- Self Join: A relation joined with itself.
Notation:
R ⋈ condition S
Example:
Joining EMP and DEPT on department ID:
| EMP_ID | ENAME | SALARY | Dept_ID | Dname |
|---|---|---|---|---|
| 101 | Raj | 450000 | 1 | Marketing |
| … | … | … | … | … |
Features:
- Domain compatibility required
- Can join more than two relations
- Cartesian Product + Selection + Projection = Join
9. Division (÷)
For queries requiring “for all” logic, this is used.
Notation:
R ÷ S
Example:
Subjects taught in all courses:
| Subject_Name |
|---|
| DBMS |
Features:
- Suited for universal quantification
- Rarely used but powerful
Cartesian Product vs Join: A Comparison
| Aspect | Cartesian Product (×) | Join (⋈) |
|---|---|---|
| Combines | All combinations of tuples | Only tuples satisfying join condition |
| Result size | Large ( | R |
| Use case | Intermediate step for joins | Final query expression |
| Conditional? | No | Yes (based on join condition) |
Complete Python Course with Advance topics:-Click Here
SQL Tutorial :-Click Here
Download New Real Time Projects :–Click here
Final Thoughts
Relational Algebra is a powerful theoretical tool in database design and query optimization. Understanding these operations helps developers and database engineers build more efficient and logically sound systems. It also deepens the comprehension of SQL’s underlying mechanisms and enhances analytical skills when dealing with complex data queries.
Keep learning with UpdateGadh—your trusted source for simplified tech knowledge.
relational algebra in dbms
relational algebra calculator
relational algebra examples
relational algebra in dbms with examples
relational algebra in dbms questions
relational algebra operations in dbms
relational algebra symbols
relational algebra operators
relational calculus
relational algebra operations
