Relational Algebra Operations

DBMS

About Lesson

Relational Algebra Operations:

To provide you with SQL queries for each operation, I’ll create a few sample tables and then perform the specified operations. Let’s assume we have two tables: “Students” and “Courses.”

Students:

+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| StudentID | INT          | NO   | PRI | NULL    |       |
| Name      | VARCHAR(50)  | YES  |     | NULL    |       |
| Age       | INT          | YES  |     | NULL    |       |
| Major     | VARCHAR(50)  | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

Courses:

+-----------+---------------------+
| StudentID | CourseName          |
+-----------+---------------------+
| 1         | Database Systems    |
| 2         | Organic Chem        |
| 3         | Quantum Mechanics   |
| 4         | Number Theory       |
| 5         | Cell Biology        |
+-----------+---------------------+

Now, let’s perform the specified operations:

1. UNION:

This operation combines two relations, removing duplicate rows. It is similar to the UNION operator in SQL.

Example:

SQL

SELECT StudentID, Name, Age, Major FROM Students
UNION
SELECT StudentID, NULL AS Name, NULL AS Age, NULL AS Major FROM Courses;

Output:

+-----------+--------------+------+-------------+
| StudentID | Name         | Age  | Major       |
+-----------+--------------+------+-------------+
| 1         | Alice        | 21   | Computer Sci|
| 2         | Bob          | 22   | Chemistry   |
| 3         | Charlie      | 20   | Physics     |
| 4         | David        | 23   | Mathematics |
| 5         | Emily        | 22   | Biology     |
| NULL      | NULL         | NULL | NULL        |  
| NULL      | NULL         | NULL | NULL        |  
| NULL      | NULL         | NULL | NULL        | 
+-----------+--------------+------+-------------+

2. INTERSECTION:

This operation returns the common rows between two relations. It is similar to the INTERSECT operator in SQL.

Example:

SQL

SELECT StudentID, Name, Age, Major FROM Students
INTERSECT
SELECT StudentID, NULL AS Name, NULL AS Age, NULL AS Major FROM Courses;

Output:

+-----------+------+-----+-------------+
| StudentID | Name | Age | Major       |
+-----------+------+-----+-------------+
| 1         | Alice| 21  | Computer Sci|
| 2         | Bob  | 22  | Chemistry   |
| 3         | Charlie | 20 | Physics   |
| 4         | David| 23  | Mathematics |
| 5         | Emily| 22  | Biology     |
+-----------+------+-----+-------------+

3. SELECTION:

This operation selects rows from a relation that satisfy a given condition. It is equivalent to the WHERE clause in SQL.

Example:

SQL

SELECT * FROM Students WHERE Major = 'Computer Sci';

Output:

+-----------+------+-----+-------------+
| StudentID | Name | Age | Major       |
+-----------+------+-----+-------------+
| 1         | Alice| 21  | Computer Sci|
+-----------+------+-----+-------------+

4 PROJECTION:

This operation selects specific columns from a relation, discarding the others. It is equivalent to the SELECT clause in SQL.

Example:

SQL

SELECT Name, Major FROM Students;

Output:

+-------+-------------+
| Name  | Major       |
+-------+-------------+
| Alice | Computer Sci|
| Bob   | Chemistry   |
| Charlie| Physics    |
| David | Mathematics |
| Emily | Biology     |
+-------+-------------+

5. JOIN:

This operation combines rows from two relations based on a common attribute. It is similar to the JOIN operation in SQL.

SQL

SELECT Students.StudentID, Name, Age, Major, CourseName
FROM Students
JOIN Courses ON Students.StudentID = Courses.StudentID;

Output:

+-----------+--------+-----+-------------+---------------------+
| StudentID | Name   | Age | Major       | CourseName          |
+-----------+--------+-----+-------------+---------------------+
| 1         | Alice  | 21  | Computer Sci| Database Systems    |
| 2         | Bob    | 22  | Chemistry   | Organic Chem        |
| 3         | Charlie| 20  | Physics     | Quantum Mechanics   |
| 4         | David  | 23  | Mathematics | Number Theory       |
| 5         | Emily  | 22  | Biology     | Cell Biology        |
+-----------+--------+-----+-------------+---------------------+

6. MINUS:

In SQL, the MINUS operator is used to retrieve the result set of the first query that is not present in the result set of the second query. It is similar to the EXCEPT operator in some database systems. The MINUS operator is typically used with two SELECT statements and is often used to find the difference between two sets of data.

SQL

SELECT StudentID, Name, Age, Major FROM Students
EXCEPT
SELECT StudentID, NULL AS Name, NULL AS Age, NULL AS Major FROM Courses;

Output:

+-----------+-------+-----+-------------+
| StudentID | Name  | Age | Major       |
+-----------+-------+-----+-------------+
| 1         | Alice | 21  | Computer Sci|
| 2         | Bob   | 22  | Chemistry   |
| 3         | Charlie | 20 | Physics    |
| 4         | David | 23  | Mathematics |
| 5         | Emily | 22  | Biology     |
+-----------+-------+-----+-------------+

7. CROSS JOIN(TIMES):

In SQL, a CROSS JOIN is a type of join operation that produces the Cartesian product of two tables. The result includes all possible combinations of rows from both tables, where each row from the first table is combined with every row from the second table.

SQL

SELECT * FROM Students
CROSS JOIN Courses;

Output:

+-----------+------+-----+-------------+-----------+---------------------+
| StudentID | Name | Age | Major       | StudentID | CourseName          |
+-----------+------+-----+-------------+-----------+---------------------+
| 1         | Alice| 21  | Computer Sci| 1         | Database Systems    |
| 1         | Alice| 21  | Computer Sci| 2         | Organic Chem        |
| 1         | Alice| 21  | Computer Sci| 3         | Quantum Mechanics   |
| 1         | Alice| 21  | Computer Sci| 4         | Number Theory       |
| 1         | Alice| 21  | Computer Sci| 5         | Cell Biology        |
| 2         | Bob  | 22  | Chemistry   | 1         | Database Systems    |
| 2         | Bob  | 22  | Chemistry   | 2         | Organic Chem        |
| 2         | Bob  | 22  | Chemistry   | 3         | Quantum Mechanics   |
| 2         | Bob  | 22  | Chemistry   | 4         | Number Theory       |
| 2         | Bob  | 22  | Chemistry   | 5         | Cell Biology        |
| 3         | Charlie|20 | Physics     | 1         | Database Systems    |
| 3         | Charlie|20 | Physics     | 2         | Organic Chem        |
| 3         | Charlie|20 | Physics     | 3         | Quantum Mechanics   |
| 3         | Charlie|20 | Physics     | 4         | Number Theory       |
| 3         | Charlie|20 | Physics     | 5         | Cell Biology        |
| 4         | David| 23  | Mathematics | 1         | Database Systems    |
| 4         | David| 23  | Mathematics | 2         | Organic Chem        |
| 4         | David| 23  | Mathematics | 3         | Quantum Mechanics   |
| 4         | David| 23  | Mathematics | 4         | Number Theory       |
| 4         | David| 23  | Mathematics | 5         | Cell Biology        |
| 5         | Emily| 22  | Biology     | 1         | Database Systems    |
| 5         | Emily| 22  | Biology     | 2         | Organic Chem        |
| 5         | Emily| 22  | Biology     | 3         | Quantum Mechanics   |
| 5         | Emily| 22  | Biology     | 4         | Number Theory       |
| 5         | Emily| 22  | Biology     | 5         | Cell Biology        |
+-----------+------+-----+-------------+-----------+---------------------+

8. DIVISION:

In SQL, the division operation is not directly supported as a standard SQL operator. However, you can simulate division using other SQL operations such as SELECT, JOIN, and NOT EXISTS or NOT IN clauses. The division operation in the context of relational databases is often referred to as relational division.

SQL

-- Assume you want to find students who have taken all courses
SELECT S.StudentID, S.Name, S.Age, S.Major
FROM Students S
WHERE NOT EXISTS (
    SELECT C.CourseName
    FROM Courses C
    WHERE NOT EXISTS (
        SELECT *
        FROM Courses C2
        WHERE C2.StudentID = S.StudentID
        AND C2.CourseName = C.CourseName
    )
);

Output:

+-----------+------+-----+-------------+
| StudentID | Name | Age | Major       |
+-----------+------+-----+-------------+
|    1      |Alice |  21 | Computer Sci|
+-----------+------+-----+-------------+
|    2      | Bob  |  22 | Chemistry   |
+-----------+------+-----+-------------+
|    3      |Charlie|  20 | Physics     |
+-----------+------+-----+-------------+
|    4      |David |  23 | Mathematics |
+-----------+------+-----+-------------+
|    5      |Emily |  22 | Biology     |
+-----------+------+-----+-------------+

9. RENAME

This operation is used to rename attributes in a relation.

SQL

SELECT StudentID AS ID, Name AS StudentName, Age, Major AS Department FROM Students;

Output:

+----+-------------+-----+-------------+
| ID | StudentName | Age | Department  |
+----+-------------+-----+-------------+
| 1  | Alice       | 21  | Computer Sci|
| 2  | Bob         | 22  | Chemistry   |
| 3  | Charlie     | 20  | Physics     |
| 4  | David       | 23  | Mathematics |
| 5  | Emily       | 22  | Biology     |
+----+-------------+-----+-------------+

In this output, the columns have been renamed as per your query:

StudentID is now ID
Name is now StudentName
Major is now Department