Relational Algebra

Relational Algebra

Rating:

Algebra is a formal structure, which consists of sets, and operations performed on these sets. Relational algebra is a formal structure for manipulating relations. There are various relational operators for manipulating relations. These operators take two tables, perform the operation, and return back the desired output.

There are basically eight operators in relational algebra to perform operations on tables. Each of them is discussed in detail.

1. Union
2. Intersection
3. Difference
4. Cartesian Product
5. Project
6. Restrict
7. Divide
8. Join

The first four operators, i.e. Union, Intersection, Difference, and Cartesian Product are taken from set theory with some changes to make them work with tables. The last four, i.e. Project, Restrict, Divide, and Join are designed specifically for tables. The most important operators for logical design are project and join.

Union

The union operator takes two tables and returns a table including all the rows appearing in either or both of the tables.

Union is always denoted by the 'U' symbol. Suppose there is a table named P and a table named Q, then the union of the two will be PUQ.

An example, which describes the union operation, is given below.

P Name Roger John Jones

Q EmpId A11 A12 A13

Result of P Union Q

Name	EmpId
Roger	A11
John	A12
Jones	A13

Intersection

The intersection operator takes two tables and returns a table, which consists of all the common rows appearing in both the tables. If no row is common between the two tables, then the result is NULL.

Below is an example, which describes the intersection operation.

P Name EmpId Roger A11 Binnie A12 Annie A13

Q Name EmpId Roger A11

Result of P Intersection Q

Name	EmpId
Roger	A11

Here, only one row is common between P and Q.

Difference

The difference operator takes two tables and returns a table, which consists of all rows appearing in one of the tables but not in the other. The symbol for difference is '-'. If there are two tables named A and B, then the difference between them is "A-B". It consists of all the rows in A but not in B.

Below is an example, which describes the difference operation.

A Name Roger Annie Binnie Richie

B Name Anil Emma Rocky

Result of A Difference B

Name

Roger

Annie

Binnie

Richie

Cartesian Product

The Cartesian product operator takes two tables and returns a table that consists of rows that are possible combination of two rows, i.e. one from each table. The symbol of the Cartesian operator is 'X'.

Below is an example, which describes the Cartesian product.

A Name Roger Binnie

B Working days Monday Tuesday Wednesday

Resultant Table

Name	Working days
Roger	Monday
Binnie	Monday
Roger	Tuesday
Binnie	Tuesday
Roger	Wednesday
Binnie	Wednesday

Here, the resultant table obtained is the possible combination of rows from both the tables.

Project

The project operator takes a table and returns a table that is a subset of the original table. The result obtained is known as projection. A projection is also known as decomposition of the table.

To understand projection, let's take an example. A table named Employee is given below. The projection of the table on two attributes, Empname and salary, is also shown below. The projection is the subset of the original table.

Employee

Name	Salary	DeptId
Smith	800	30
Harry	1250	30
Warden	1260	30
Blake	1245	30

Result of Projection on the Employee table

Name	Salary
Smith	800
Harry	1250
Warden	1260
Blake	1245

Restrict

The restrict operator takes a table and returns a table that consists of a subset of rows. The selected rows must satisfy a specific criteria or restriction condition. It is implemented using the WHERE clause of the SELECT statement.

The result so obtained is known as selection.

An example, which describes the restrict operation, is given below.

The table named student is given below:

Name	Age
Lucky	12
Linda	13
Melinda	13
Binnie	13
James	12

Now, when the following query is issued on this table, the result will be as follows:

SELECT Name
FROM Student
WHERE age>12

Linda

Melinda

Binnie

Divide

The divide operator takes two tables, one table having two columns and the other having one column. It searches in the two-column table for those sets of rows, for which the values in one of the two columns match all the values in the one-column table and for which all the corresponding row values in the second column are the same. The result obtained is a single-column table that consists of all the matching values found in the second column.

An example, which describes the division operation, is given below.

A table named student is given with two columns: Name and Grade; and a table named Grade is given with one column Grade.

Name Name Grade Roger A Roger B Melinda A Melinda C Peter A Peter B Doreen B Doreen C

Grade Grade A B

Resultant table

Roger

Peter

The result contains the value from the Name table. The two names obtained have both the values that are in the Grade table. Only Roger and Peter contain matching values, i.e. A and B from the Grade table.

Join

A join or an equijoin combines the rows of two tables that have equivalent values for the specified columns. The two tables are joined using an equality operator "=". It is also known as simple join and inner join. A join is basically a combination of Cartesian product and restrict operation. The join operation takes all the possible combination of rows from the two tables as it is done in Cartesian product, and then it selects those records for which the values in a common attribute meet some specified criteria. For example, consider the two tables given below named Employee and Salary. Here, the common attribute between the two tables is EmpId. When we perform a join on these two tables, then all the records, which have the same values on EmpId in both the tables will be retrieved. Hence, it is a combination of both Cartesian product and join operations.

Employee Name EmpId Richard A11 Doreen A12 Peter A13 Nancy A14

Salary EmpId Salary A11 2300 A13 3000

When we perform a join operation on these two tables, we get a result as given below:

SELECT *
FROM Employee, Salary
WHERE Employee.EmpId= Salary.EmpId

Result

EmpId	Name	Salary
A11	Richard	2300
A13	Peter	3000

The join and project operators are very important to normal forms. These operators specify whether a particular decomposition of table improves the logical design of the database or not.

Note: Why is a relational database said to be "closed" when relational operators are applied to any of its tables?

When a relational operator is applied to a table in a relational database, the result is another table. That means applying relational operators to the results of relational operations will yield another table. Therefore, a relational database is said to be closed when relational operators are applied to any of its tables.

Rating: