# ALGEBRA(Join) In RDBMS(RELATIONAL DATABASE MANAGEMENT SYSTEM)

// By:Team // No Comment

**ALGEBRA In RELATIONAL DATABASE MANAGEMENT SYSTEM**

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:

**σ(R)**

• The σ would represent the SELECT command

• The 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:

**∏(R )**

• ∏ would represent the ROJECT.

• 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.

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.

**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

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:

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