Introduction to Relational Algebra

What is Relational Algebra?

Relational Algebra is a widely used Conceptual Procedural Query Language. It collects instances of relations as inputs and provides the occurrences (yield instances) of those relations as outputs. Basically, the output of these operations is a new Relation that may be formed from one or more input relations. It uses various operations to perform this process. Relational Algebra operations are performed recursively on a relation.

What is Query Analyzer?

Query Analyzer is an interesting tool that takes the queries and examines and analyses the data inside the SQL server. We can execute queries and stored procedures using the Query Analyser and are able to get a visual display of the SQL server executed result.

Query Analyzer can be found in the SQL Server folder. For using the Query Analyzer you must have connected to the SQL Server dialog by executing the "USE" keyword (use [databasename]). 

Benefits of Relational Algebra

• Easiness of composting operations to form a complex query.
• Easiness of building the query logic easily.

Relational Algebra Operators

Relational Algebra Operators can be categorized into three major parts. They are:

1. Set Operators
2. Relational Operators
3. Set Functions    

Each of above mentioned relational operators can be categorized into two major types according to their operation. They are:

1. Unary Relational Operators [*]
2. Binary Relational Operators  [#]

✱important:- I have mentioned Unary Relational Operators as [*] and Binary Relational Operators as [#] in the below. Please note that they are not standard symbols to represent those types. I have used that for the easiness of identification.

 1. Set Operators

Set Operators are Relational Algebra operators from Set Theory. There are four major Set operators in Relational Algebra.
They are:    

1. Union [#]
2. Intersection [#]
3. Minus/Difference [#]
4. Product [#]

1. Union

• Union returns all values found in either set with duplicates removed (Default it eliminates duplicates). 
• But "UNION ALL" operator doesn't eliminate duplicates (Allows duplicates).

Eg: - Generally Union is used to combine a result of two output results takes from two different subqueries such as "Students who are either Computing or Mathematics Students".

2. Intersection

• Intersection represents the overlap between two result sets. It returns only those values that are present in both sets with any duplicates removed.

Eg: -  Displaying any individual on a university campus who are both Tutors and Students.

3. Minus/Difference

• This operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement.    
• During the operation, each SELECT statement will define a dataset. The MINUS operator retrieves all records from the first dataset and then removes the resulted records from the output of the second dataset.

✱Note: - MINUS operator is not supported in every SQL DBMS. It supports several DBMS such as       ORACLE. For DBMS such as SQLSever (MsSql), PostgreSQL, and SQLLite uses the "EXCEPT" operator to perform this operation. Further "EXCEPT ALL" will remove the duplicates too.

4. Product/Cartesian Product

• This operation is helpful to merge columns from two relations.
• Generally, this is never a meaningful operation when it performs alone. However, it becomes meaningful when it is followed by other operations.

Eg: - Shows all rows from Relation A and B whose column has value 1.

 2. Relational Operators

Relational Operators are Relational Algebra operators based on joins and join-like operators. There are five major Relational Operators in Relational Algebra.

They are:

1. Selection/Restrict [*]
2. Project [*]
3. Join [#]
4. Rename [*]
5. Division [#]    

1. Selection/Restrict

• Selection is a Horizontal slice through a relation that extracts all columns but only for those rows that satisfy particular criteria (condition).

Eg: - For example let's take a scenario of filtering all Computer Science Students out of all the            Students by categorizing students by their course.

2. Project

• Projection is a Vertical Slice through a relation that extracts all rows but only for specific named attributes (columns). 

Eg: - For example let's take the same Student Table which includes a large no of attributes (columns), but we only required a specific no of attributes to be displayed such as displaying only Reg-Number, Name from the table which contains columns Reg-Number, Name, Email, and Contact Number.

3. Join

• Join is a process that combines data from two or more relations.
• Joins can be mainly categorized into three major units.

1. Natural Join
2. Inner Join
3. Outer Join

4. Rename 

• The results of relational algebra are also relations without any name.
• The Rename operation allows us to rename the output relation.    

3. Set Functions

Set Functions operates on a set of values and computes one single output value. They are used to formulate "group conditions" (conditions on the set of tuples). Aggregate or Set functions are introduced to relational algebra to increase its excessive power.

They are:

1. sum
2. avg
3. count
4. any
5. max
6. min