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

// By:Team // No Comment

Email this to someonePrint this pageShare on Google+1Share on Facebook3Tweet about this on TwitterShare on LinkedIn0Share on Reddit0Pin on Pinterest0Share on StumbleUpon1

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

Email this to someonePrint this pageShare on Google+1Share on Facebook3Tweet about this on TwitterShare on LinkedIn0Share on Reddit0Pin on Pinterest0Share on StumbleUpon1

About Team

Browse Archived Articles by Team

Related

Sorry. There are no related articles at this time.