Mastering Relational Algebra for Database Queries

Relational Algebra Fundamentals

• Relational Algebra is a mathematical query language used in databases.
• The result of any operation is always another relation (table).

Relational Algebra Operations Explained

Unary Relational Operations

SELECT (σ) – Filters Rows

• Retrieves specific rows from a table based on a condition.
• Syntax: σ (condition) (Relation)
• Example: σ (Dept_ID = 4) (EMPLOYEE)

PROJECT (π) – Filters Columns

• Retrieves specific columns from a table.
• Syntax: π (column1, column2) (Relation)
• Example: π (Name, Salary) (EMPLOYEE)

RENAME (ρ) – Changes Table or Column Name

• Syntax: ρ (NewTable (NewColumn1, NewColumn2), OldTable)
• Example: ρ (Staff (Emp_ID, FullName), EMPLOYEE)

Set Theory Operations

UNION (∪) – Combines Two Tables

• Combines tuples from two tables, removing duplicates.
• Syntax: R ∪ S

SET DIFFERENCE (−) – Finds Unique Tuples

• Retrieves tuples present in R but not in S.
• Syntax: R - S

INTERSECTION (∩) – Finds Common Tuples

• Retrieves tuples present in both R and S.
• Syntax: R ∩ S

CARTESIAN PRODUCT (×) – Matches All Tuples

• Returns all possible combinations of two relations.
• Syntax: R × S

Binary Relational Operations

Theta (θ) JOIN (⨝θ) – Combines Two Tables with a Condition

• Matches rows based on a specified condition.
• Syntax: R ⨝θ (Condition) S

Equi JOIN – Special Case of Theta JOIN (equality condition)

• Matches rows where column values are equal.
• Syntax: EMPLOYEE ⨝ (EMPLOYEE.Dept_ID = DEPARTMENT.Dept_ID) DEPARTMENT

Natural JOIN (*) – Removes Duplicate Columns

• Automatically matches columns with the same name.
• Syntax: EMPLOYEE * DEPARTMENT

Outer JOIN – Keeps Unmatched Rows

• Left Outer JOIN: Keeps all rows from the left table.
• Right Outer JOIN: Keeps all rows from the right table.
• Full Outer JOIN: Keeps all rows from both tables.
• Syntax Example: EMPLOYEE LEFT OUTER JOIN DEPARTMENT

DIVISION (÷) – "For All" Queries

• Finds tuples related to all values in another table.
• Syntax: STUDENT ÷ COURSES

Aggregate Functions and Grouping

• SUM: SUM (Salary) (EMPLOYEE)
• AVG: AVG (Salary) (EMPLOYEE)
• MAX: MAX (Salary) (EMPLOYEE)
• MIN: MIN (Salary) (EMPLOYEE)
• COUNT: COUNT (Emp_ID) (EMPLOYEE)

Relational Calculus Concepts

Tuple Relational Calculus (TRC)

• Uses tuple variables to describe what data to retrieve.
• Syntax: {t | EMPLOYEE(t) AND t.Dept_ID = 5}

Domain Relational Calculus (DRC)

• Uses domain variables instead of tuples.
• Syntax: { | ∃ t (EMPLOYEE(t) ∧ t.Dept_ID = 5)}

SQL Equivalents for Relational Algebra

• SELECT (σ): SELECT * FROM EMPLOYEE WHERE Dept_ID = 4;
• PROJECT (π): SELECT Name, Salary FROM EMPLOYEE;
• UNION (∪): SELECT Name FROM HR UNION SELECT Name FROM IT;
• SET DIFFERENCE (−): SELECT * FROM EMPLOYEE WHERE Dept_ID != 5;
• JOIN (⨝): SELECT * FROM EMPLOYEE JOIN DEPARTMENT ON EMPLOYEE.Dept_ID = DEPARTMENT.Dept_ID;