Feeds:
Posts

## An Overview of Relational Algebra Operators and Their SQL Translations

By Kato Mivule

Database Systems

Outline

Relational Algebra Operators are indispensable mathematical tools that can be used to retrieve queries by describing a sequence operations on relations and schemas. In this discussion, we take a look at various Relational Algebra operations and the SQL implementation of assorted Relational Algebraic operators.

Introduction

Relational Algebra Operators are mathematical functions used to retrieve queries by describing a sequence operations on tables or even databases(schema) involved. With relational algebra operators, a query is always composed of a number of operators, which each in turn are composed of relations as variables and return an individual abstraction as the end product. 

The following are the main relational algebra operators as applied to SQL:

The SELECT Operator

The SELECT operator is used to choose a subset of the tuples(rows) from a relation that satisfies a selection condition, acting as a filter to retain only tuples that fulfills a qualifying requirement. 

• The SELECT operator is relational algebra is denoted by the symbol σ (sigma).
• The syntax for the SELECT statement is then as follows: 

σ<Selection condition>(R)

• The σ would represent the SELECT command
• The <selection condition> would represent the condition for selection.
• The (R) would represent the Relation or the Table from which we are making a selection of the tuples.

To implement the SELECT statement in SQL, we take a look at an example in which we would like to select the EMPLOYEE tuples whose employee number is 7, or those whose date of birth is before 1980…

σempno=7(EMPLOYEE)

σdob<’01-Jan-1980′(EMPLOYEE)

The SQL implementation would translate into:

SELECT empno

FROM EMPLOYEE

WHERE empno=7

SELECT dob

FROM EMPLOYEE

WHERE DOB < ’01-Jan-1980′

The PROJECT Operator

This operator is used to reorder, select and get rid of attributes from a table. At some point we might want only certain attributes in a relation and eliminate others from our query result. Therefore the PROJECT operator would be used in such operations.  

• The symbol used for the PROJECT operation is ∏ (pi).
• The general syntax for the PROJECT operator is:

<attribute list>(R )

• ∏ would represent the ROJECT.
• <attribute list> would represent the attributes(columns) we want from a relational.
• (R ) would represent the relation or table we want to choose the attributes from.

To implement the PROJECT statement in SQL, we take a look at an example in which we would like to choose the Date of Birth (dob) and Employee Number (empno) from the relation EMPLOYE…

• dob, empno(EMPLOYEE )

In SQL this would translate to:

SELECT dob, empno

FROM EMPLOYEE

The RENAME Operator

The RENAME operator is used to give a name to results or output of queries, returns of selection statements, and views of queries that we would like to view at some other point in time:    

• The RENAME operator is symbolized by ρ (rho).
• The general syntax for RENAME operator is: ρ s(B1, B2, B3,….Bn)(R )
• ρ is the RENAME operation.
• S is the new relation name.
• B1, B2, B3, …Bn are the new renamed attributes (columns).
• R is the relation or table from which the attributes are chosen.

To implement the RENAME statement in SQL, we take a look at an example in which we would like to choose the Date of Birth and Employee Number attributes and RENAME them as ‘Birth_Date’ and ‘Employee_Number’ from the EMPLOYEE relation…

ρ s(Birth_Date, Employee_Number)(EMPLOYEE ) ← ∏dob, empno(EMPLOYEE )

• The arrow symbol ← means that we first get the PROJECT operation results on the right side of the arrow then apply the RENAME operation on the results on the left side of the arrow.

In SQL we would translate the RENAME operator using the SQL ‘AS’ statement:

SELECT dob AS ‘Birth_Date’, empno AS ‘Employee_Number’

FROM EMPLOYEE

The UNION, INTERSECTION, and MINUS Operators

UNION: the UNION operation on relation A UNION relation B designated as A B, joins or includes all tuples that are in A or in B, eliminating duplicate tuples.   The SQL implementation of the UNION operations would be as follows:

UNION

RESULT ← A B

SQL Statement:

SELECT * From A

UNION

SELECT * From B

INTERSECTION: the INTERSECTION operation on a relation A INTERSECTION relation B, designated by A B, includes tuples that are only in A and B. In other words only tuples belonging to A and B, or shared by both A and B are included in the result.   The SQL implementation of the INTERSECTION operations would be as follows:

INTERSECTION

RESULT ← A B

SQL Statement:

SELECT dob From A

INTERSECT

SELECT dob from B

MINUS Operations: the MINUS operation includes tuples from one Relation that are not in another Relation. Let the Relations be A and B, the MINUS operation A MINUS B is denoted by A – B, that results in tuples that are A and not in B.   The SQL implementation of the MINUS operations would be as follows:

MINUS

RESULT ← A – B

SQL Statement

SELECT dob From A

MINUS

SELECT dob from B

CARTESIAN PRODUCT Operator

The CARTERSIAN PRODUCT operator, also referred to as the cross product or cross join, creates a relation that has all the attributes of A and B, allowing all the attainable combinations of tuples from A and B in the result.   The CARTERSIAN PRODUCT A and B is symbolized by X as in A X B.

Let there be Relation A(A1, A2) and Relation B(B1, B2)

The CARTERSIAN PRODUCT C of A and B which is A X B is

C = A X B

C = (A1B1, A1B2 , A2B1, A2B2 )

The SQL implementation would be something like:

SELECT A.dob, B.empno

from A, B

JOIN Operator

The JOIN operation is denoted by the ⟗ symbol and is used to compound similar tuples from two Relations into single longer tuples. Every row of the first table is joined to every row of the second table. The result is tuples taken from both tables.  

• The general syntax would be A ⟗ <join condition> B

SQL translation example where attribute dob is Date of Birth and empno is Employee Number:

SELECT A.dob, A.empno

from employee

JOIN B on B.empno=A.empno

THETA JOIN Operator

This operation results in all combinations of tuples from Relation A and Relation B satisfying a join requirement. The THETA JOIN is designated by: The SQL implementation would be the same as for the JOIN example above.

• A ⟗ <join condition> B

EQUIJOIN Operator

The EQUIJOIN operation returns all combinations of tuples from Relation A and Relation B satisfying a join requirement with only equality comparisons. The EQUIJOIN operation is symbolized by : 

A ⟗ <join condition> B, OR

• A ⟗ (<join attributes 1>),

(<join attributes 2>) B

SQL translation example where attribute dob is Date of Birth and empno is Employee Number:

SELECT * from A

INNER JOIN B

on A.empno=B.empno

NATURAL JOIN Operator

The NATURAL JOIN operation returns results that does not include the JOIN attributes of the second Relation B. It is not required that attributes with the same name be mentioned. The NATURAL JOIN operator is symbolized by:  

• A * ⟗ <join condition> B,

OR A * ⟗ (<join attributes 1>),

(<join attributes 2>) B

OR A * B

SQL translation example where attribute dob is Date of Birth and empno is Employee Number:

SELECT A.dob, B.empno

FROM A

NATURAL JOIN B

//where depno =5

We can always use the ‘where’ clause to further restrict our output and stop a Cartesian product output.

DIVISION Operator

The DIVISION operation will return a Relation R(X) that includes all tuples t[X] in R(Z) that appear in R1 in combination with every tuple from R2(Y), where Z = X ∪ Y. The DIVISION operator is symbolized by: 

• R1(Z) ∻ R2(Y)

The DIVISION operator is the most difficult to implement in SQL as no SQL command is given for DIVISION operation. The DIVISION operator would be seen as the opposite of the CARTERSIAN PRODUCT operator; just as in standard math, the relation between division and multiplication. Therefore a series of current SQL commands have to be utilized in implementation of the DIVISION operator. An example of the SQL implementation of DIVISION operator: 

SELECT surname, forenames

FROM employee X

WHERE NOT EXISTS

(SELECT ‘X’

FROM employee y

WHERE NOT EXISTS

(SELECT ‘X’

FROM employee z

WHERE x.empno = z.empno

AND y.surname = z.surname))

ORDER BY empno

Conclusion

Relational Algebra Operators are critical mathematical tools used to retrieve queries by describing a sequence operations on tables or even databases(schema) involved mathematically. With relational algebra operators, a query is always composed of a number of operators, which each in turn are composed of relations as variables and return an individual abstraction as the end product. Relational Algebra operations can easily be translated into SQL commands to retrieve query results, making it a powerful tool in the hands of any Database designer, user, and administrator.

References

 Clare Churcher, Beginning SQL Queries: From Novice to Professional, Apress Series, Books for Professionals by Professionals, Expert’s voice in databases, Edition illustrated, Apress, 2008, ISBN1590599438, 9781590599433, Page 12-13

 Peter Z. Revesz, Introduction to constraint databases, Texts in computer science, Edition illustrated, Springer, 2002, ISBN 0387987290, 9780387987293, Page 26

 Ramez Elmasri, Shamkant B. Navathe, Fundamentals of Database Systems, Edition 6, Addison Wesley Pub Co Inc, 2010, ISBN 0136086209, 9780136086208, Page 145 – 164

 “Relational Algebra.” [Online]. Available: http://technet.microsoft.com/en-us/library/cc966426.aspx. [Accessed: 02-Oct-2010].

 “Rename (relational algebra) – Wikipedia, the free encyclopedia.” [Online]. Available: http://en.wikipedia.org/wiki/Rename_(relational_algebra). [Accessed: 02-Oct-2010].

 “Relational algebra – Wikipedia, the free encyclopedia.” [Online]. Available: http://en.wikipedia.org/wiki/Relational_algebra. [Accessed: 02-Oct-2010].

 Rajesh Narang, Database Management Systems, PHI Learning Pvt. Ltd., 2006, ISBN 8120326458, 9788120326453, Page 223 – 239

 P. S. Deshpande, Sql/Pl Sql For Oracle 9I (W/Cd), Dreamtech Press, 2003, ISBN 8177224719, 9788177224719, Page 45

 Allen G. Taylor, SQL All-in-one Desk Reference for Dummies, For Dummies Computers Series, For Dummies, 2007, ISBN 0470119284, 9780470119280, Page 303 – 314

 “Oracle simplifies SQL with ISO 99 Syntax.” [Online]. Available: http://www.dba-oracle.com/art_sql_iso_99.htm. [Accessed: 03-Oct-2010].

 L. I. McCann and W. I. Kenosha, “On making relational division comprehensible,” in FRONTIERS IN EDUCATION CONFERENCE, vol. 2, pp. 2–6, 2003.

 Sikha Bagui, Sikha Saha Bagui, Richard Earp, Essential SQL on SQL Server 2008, Edition revised, Jones & Bartlett Learning, 2009,ISBN 076378138X, 9780763781385, Page 314

### 9 Responses

1. on July 29, 2011 at 1:28 PM prabhu

i want relational algebra index paragraph.please send to my email.its my request because i am student i am doing project in my college. so iw ant to know about the relational algebra index.if u send me means i wil thank u

2. on July 28, 2012 at 5:07 PM dedunu

this post was very usefull to me when i have only one day for the exam complete and good summary thanks

3. on April 12, 2013 at 11:15 AM dodo bird

some pictures would be nice ?

4. on July 3, 2013 at 9:02 AM Saam

Very good post.

5. on September 29, 2013 at 6:47 PM manhir

good post !!

6. on November 17, 2013 at 6:05 PM rimu

itz really very nyc

7. on May 13, 2014 at 1:14 PM Arkit

8. on October 1, 2015 at 6:23 PM rinkal prajapati
9. on May 4, 2017 at 10:18 PM Tom Musuuza