Relational Algebra vs SQL Queries: Joins, Nested Subqueries & Solved TU Past Questions

Author: Bhuban Subedi | Subject: Database Management Systems (CACS255) | Semester: Fourth Semester

In relational database theory, Relational Algebra is the procedural mathematical query language that forms the theoretical engine beneath all commercial RDBMS query optimizers. SQL (Structured Query Language) is its practical, declarative implementation.

In the Tribhuvan University (TU) BCA Fourth Semester DBMS (CACS255) board examinations, questions asking you to convert English business requirements into both Relational Algebra mathematical expressions and SQL statements carry 10 full marks.

In this guide, we will map relational algebra symbols directly to SQL syntax, master all types of Joins, understand HAVING vs WHERE, and solve authentic past board exam problems.

1. Relational Algebra Operators vs. SQL Mappings

+---------------------------+------------+-------------------------------+-----------------------------------+
| Operation Name            | RA Symbol  | SQL Equivalent                | Purpose / Function                |
+---------------------------+------------+-------------------------------+-----------------------------------+
| **Select** (Row filter)   | $\sigma$   | `WHERE condition`             | Filters tuples (rows) satisfying  |
|                           |            |                               | a given predicate condition.      |
+---------------------------+------------+-------------------------------+-----------------------------------+
| **Project** (Col filter)  | $\pi$      | `SELECT col1, col2`           | Extracts specified attribute      |
|                           |            |                               | columns, discarding duplicates.   |
+---------------------------+------------+-------------------------------+-----------------------------------+
| **Cartesian Product**     | $\times$   | `FROM TableA CROSS JOIN TableB`| Pairs every tuple in A with every|
|                           |            |                               | tuple in B ($m \times n$ rows).   |
+---------------------------+------------+-------------------------------+-----------------------------------+
| **Natural / Inner Join**  | $\bowtie$  | `INNER JOIN ... ON ...`       | Combines matching tuples based on |
|                           |            |                               | common key attributes.            |
+---------------------------+------------+-------------------------------+-----------------------------------+
| **Union**                 | $\cup$     | `UNION` (Distinct)            | Combines tuples from two union-   |
|                           |            |                               | compatible relations.             |
+---------------------------+------------+-------------------------------+-----------------------------------+
| **Set Difference**        | $-$        | `EXCEPT` / `NOT IN`           | Tuples in relation A but not in B.|
+---------------------------+------------+-------------------------------+-----------------------------------+
| **Rename**                | $\rho$     | `AS alias_name`               | Renames a relation or attribute.  |
+---------------------------+------------+-------------------------------+-----------------------------------+

2. SQL Joins Explained Visually

+--------------------+-----------------------------------------------------+
| Join Type          | Result Set Description                              |
+--------------------+-----------------------------------------------------+
| **INNER JOIN**     | Returns only rows where there is a match in BOTH.   |
| **LEFT JOIN**      | Returns ALL rows from left table + matched right.   |
| **RIGHT JOIN**     | Returns ALL rows from right table + matched left.   |
| **FULL OUTER JOIN**| Returns ALL rows when there is a match in EITHER.   |
+--------------------+-----------------------------------------------------+

3. Sample University Database Schema

Consider the following standard TU university relation schemas:
– STUDENT $(\underline{\text{std_id}}, \text{name}, \text{city}, \text{gpa}, \text{dept_id})$
– DEPARTMENT $(\underline{\text{dept_id}}, \text{dept_name}, \text{location})$
– COURSE $(\underline{\text{course_id}}, \text{course_title}, \text{credits}, \text{dept_id})$
– ENROLLMENT $(\underline{\text{std_id}, \text{course_id}}, \text{semester}, \text{grade})$

4. Solved TU Board Exam Queries (Relational Algebra & SQL)

Query 1: Selection & Projection

Question: “Find the names and cities of all students whose GPA is greater than 3.5.”

Relational Algebra:
$$\pi_{\text{name, city}}(\sigma_{\text{gpa} > 3.5}(\text{STUDENT}))$$

SQL Statement:

SELECT name, city 
FROM STUDENT 
WHERE gpa > 3.5;

Query 2: Natural Join across Two Relations

Question: “Retrieve student names along with their respective Department Names.”

Relational Algebra:
$$\pi_{\text{name, dept_name}}(\text{STUDENT} \bowtie_{\text{STUDENT.dept_id = DEPARTMENT.dept_id}} \text{DEPARTMENT})$$

SQL Statement:

SELECT s.name, d.dept_name
FROM STUDENT s
INNER JOIN DEPARTMENT d ON s.dept_id = d.dept_id;

Query 3: Multi-Table Join with Filter

Question: “Find the names of all students enrolled in the course titled ‘Data Structures’.”

Relational Algebra:
$$\pi_{\text{name}}(\sigma_{\text{course_title} = ‘\text{Data Structures}’}(\text{STUDENT} \bowtie \text{ENROLLMENT} \bowtie \text{COURSE}))$$

SQL Statement:

SELECT s.name
FROM STUDENT s
JOIN ENROLLMENT e ON s.std_id = e.std_id
JOIN COURSE c ON e.course_id = c.course_id
WHERE c.course_title = 'Data Structures';

Query 4: Aggregation with GROUP BY and HAVING

Question: “Find the Department Names that have more than 5 enrolled students.”

SQL Statement:

SELECT d.dept_name, COUNT(s.std_id) AS total_students
FROM DEPARTMENT d
JOIN STUDENT s ON d.dept_id = s.dept_id
GROUP BY d.dept_id, d.dept_name
HAVING COUNT(s.std_id) > 5;

Difference between WHERE and HAVING:
– WHERE filters individual rows before aggregation happens.
– HAVING filters aggregated groups after the GROUP BY calculation.

Query 5: Correlated Subquery

Question: “Find the students who have a GPA higher than the average GPA of their own department.”

SQL Statement:

SELECT s1.name, s1.gpa, s1.dept_id
FROM STUDENT s1
WHERE s1.gpa > (
    SELECT AVG(s2.gpa)
    FROM STUDENT s2
    WHERE s2.dept_id = s1.dept_id
);

Frequently Asked Questions (FAQ)

Q1: Is Relational Algebra procedural or non-procedural?

Relational Algebra is procedural. It specifies what data to retrieve and the exact sequence of relational operations ($\sigma, \pi, \bowtie$) to compute it. In contrast, Tuple Relational Calculus (TRC) and SQL are non-procedural/declarative (they specify what data is desired without dictating how to fetch it).

Q2: What are the conditions for two relations to be Union-Compatible?

Two relations $R$ and $S$ are union-compatible if:
1. They have the same number of attributes (same arity/degree).
2. The domain (data type) of the $i^{\text{th}}$ attribute of $R$ is identical to the domain of the $i^{\text{th}}$ attribute of $S$.