Types of Relational Operators

Relational operators are classified into two types:
• Traditional Set Operators
• Special Operators

Traditional Set Operators

Traditional set operators are:
Union: In mathematical set theory, the union of two sets is the set of all elements belonging to both sets. The set, which results from the union, must not, of course, contain duplicate elements. It is denoted by U. Thus the union of sets:

 
S1 = {1, 2, 3, 4, 5} 
and S2= {4,5,6,7,8} 
would be the set {l, 2,3,4,5,6,7,8}. 
   
A union operation on two relational tables follows the same basic principle but is more complex in practice. In order to perform the Union operation, both operand relations must be union-compatible i.e. they must have same number of columns drawn from the same domain (means must be of same data type).
Suppose that two tables, R and the S have the following tuples at some instant in time, and that their header parts are as shown below:
These can certainly be combined in to one table containing a valid relation by the relational union operator ( R US) as follows:
Intersection: In mathematics an intersection of two sets produces a set, which contains . all the elements those are common to both sets. It is denoted by n., Thus the intersection of the two sets:
   
 
S1 = { 1,2,3,4,5} and
S2= {4,5,6,7,8}
would be {4,5}. 
In above example, both the tables are union compatible and it can be intersected together. The intersection operation on the R and Stables (R n S) defined above would return: The intersection operator is used in a similar fashion to the union operation, but provides an ‘and’ function. 
Difference: In mathematics, the difference between two sets S 1 and S2 produces a set, which contains all the members of one set, which are not in the other. It is denoted by “-” The order in which the difference is taken is, obviously, significant. Thus the difference between the two sets:
 
S1 = { 1,2,3,4,5}
Minus
S2 = {4,5,6,7,8}
would be {1,2,3} and between
S2 = {4,5,6,7,8}
Minus
161
S1 = {1,2,3,4,5}
would be {6,7,8}. 
As for the other set operations discussed so far, the difference operation can only be performed on tables that are union compatible. The difference operation on the R and S (R – S) defined above would return:
It is used in a similar fashion to the union and intersection operators, but provides a qualifying ‘riot’ function.
Minus is not associative
In order to prove this mathematically consider three sets A,B;C With following members
 
A = {1, 2,3,4,5}
B = {2, 3}
c = {1,4}
(A MINUS B) MINUS C = {1,4,5,} MINUS {1,4} = {5}
A MINUS (B MINUS C) = {1,2,3,4,5} MINUS {{2,3} MINUS {1,4}}= {1,2,3,4,5}
MINUS {2,3} ={l,4,5} 
Both the cases give different result. So, minus is not an associative operator.
Minus is not commutative
It means that A MINUS B is different from B MINUS A. In order to prove it we again take the above values of A and B.
 
A MINUS B={1,4,5}
B MINUS A is empty or null because there is 
not any value, which is in B but not in A. 
Cartesian product: In mathematics, the Cartesian product of two sets is the set of all ordered pairs of elements such that the first element in each pair belongs to the first set and the second element in each pair belongs to the second set. It is denoted by cross (x). It is for example, given two sets:
 
S1 = {1,2,3}
and
S2 = { 4,5,6}
The Cartesian product S1 x S2 is the set:
{( 1,4),( 1,5),( 1,6),(2,4),(2,5),(2,6),(3,4),(3,5),(3,6)} 
Consider the two tables with sample population as below
Assume that the tables refer to male and female staff respectively. Now, in order to obtain all possible inter-staff marriages, the Cartesian product can be taken, giving.
In order to preserve unique names for attributes; the original attribute names have had to be concatenated with the original table names. The New table has also been given an identity.
Special Relational Operators
There are four special relational operators:
(i) Selection
(ii) Projection
(iii) Join
(iv) Division
Selection: The selection operator yields a ‘horizontal subset of a given relation that is, that subset of tuples or rows of table should be selected within the’ given relation for which a particular condition is satisfied.
In mathematics, a set can have any number of subsets. A set is said to be a subset of another if all its members are also members of the other set Thus, in the following example:
 
S1 = {1,2,3,4,5}
S2 = {2,3,4} 
S2 is a subset of S1. Since the body part of a table is a set, it is possible for it to have subsets, that is, a selection from its tuples can be used to form another relation. However, this would be a meaningless operation if no new information were to be gained from the new relation. On the other hand, a subset of, say an EMPLOYEE relation, which contained all tuples where the employee represent those employees who earn more than some given values of salary, would be useful. What is required is that some explicit restriction be placed on the sub-setting operation.
Restriction as originally defined was defined on relations only and is achieved using the comparison operators such as equal to (=), not equal to (< >), greater than (>), less than (<), greater than or equal to (>=) and less than or equal to <=).
Example: Consider the database having following tables:
                                   
Here, in Supplier table
 
SNo: Supplier number of supplier that is unique
Sname: Supplier name 
Examples:
We can also use lowercase Greek letter sigma (a) to denote selection.
Example Table: P WHERE WEIGHT < 15 Can be represented as: aweigh < 15 (P) SP where Sno = ‘S1′ and Pno =’P1’
Projection: The ·projection operation on a table simply forms another table by copying specified columns (both header and body parts) from the original table, eliminating any duplicated rows. The projection operator yields a vertical subset of a given relation – that is, that subset obtained by selecting specified attributes, in a specified left to right order, and then eliminating duplicate tuples within the attributes selected. Projection is denoted by the Greek letter pi(TI).
For example, consider the table EMPLOYEE, as shown:
The projections of the ‘age’, the ‘age and salary’ and the ‘personnel_number and name’ columns would return the three tables, say, A, B and C respectively:
Join: The most general form of join operation is called a theta join, where theta has the same meaning as ‘compares with’ as it was used in the context of the restriction operation. That is, it stands for any of the comparative operators equals, not equals, greater than and so forth. A theta join is performed on two tables, which have one or more columns in common which are domain compatible.
It forms a new table which contains all the columns from both the joined tables whose tuples are those defined by the restriction applied.
For example, consider the tables:
The tables list employees who make products and customer who buy those products and can be joined over the columns ‘product’ and ‘c-product’ in both tables since the values in both columns is domain compatible. The result of a theta join, where the restriction is that the product attributes values in EMPLOYEE_PRODUCT should be equal to the product attribute values in PRODUCT_CUSTOMER would be:
In the above example, the theta operator was ‘equals’ and this, the most common form of theta join, is referred to as an equi-join. Note that an equi-join must always result in a table, which has pairs of columns, like ‘product’ and ‘product’ in the above example, which contain identical lists of attribute values.
By far the most common form of join is a variation of the equi-join where this duplication of column values is eliminated by taking a projection of the table which includes only one of the duplicated columns. This is referred to as a natural join.
The natural join of the tables in the last example would give the table:
It may help in understanding the different types of join if the operation is looked at from a different point of view. The join is actually a composite operator. The theta join is a Cartesian product operation on the two tables followed by a restriction operation on the resultant table.
The tuples of the Cartesian product of the two tables in the earlier example would be:
The restriction operation on this product then selects only those tuples from this relation, which confirm to the restriction. In the example, the restriction was that the ‘product’ attributes should have equal values in each tuple and the result of this as shown below:
Since theta equated to ‘equals’, this was an equi-join. By carrying out a further, projection operation which eliminates one of the duplicated ‘product’ column resulting from the equi-join, the natural join is obtained. Thus, Join operator is combination of Cartesian product, Selection and Projection operator.
The examples given so far have all been of so-called inner joins. The fact that Sparsh makes Rubber is not recorded in any of the resultant tables from the joins, because the joining values must exist in both tables. If it requires that the value exist in only one table must appear in the output then the solution is outer join.
An outer join of the EMPLOYEE_PRODUCT and PRODUCT_CUSTOMER tables exemplified above would return:
The expression A JOIN B is defined if and only if, for every unqualified attribute-name that is common to A and B, the underlying domain is the same f(jr both relations. Assume that this condition is satisfied. Let the qualified attribute-names for A and B, in their left to right order, be
 A.A1,….. A.Am AND B.B(m+1),……. B.B(m+n), respectively;
let Ci… , Cj be the unqualified attribute name that are common to A and B and let Br,· Bs be the unqualified attribute-names remaining for B (with their relative order undisturbed) after removal of Ci, Cj.
Then A JOIN B defined to be equivalent to
 
(A TIMES B) [ A.A1……….. A.Am, B.Br……………… B.Bs]
where A.Ci = B.Ci
and A.Cj = B.Cj 
Apply this definition to JOIN operation on EMP and Dept tables with following attributes:
 
EMP (empno, ename, job, sal, deptno)
DEPT (deptno, dname, loc)
EMP JOIN DEPT EMP TIMES DEPT
[emp.empno, emp.ename, empjob,emp.sal, emp.deptno, dept.dname, dept.loc]
where EMP.deptno = DEPT.deptno
So, we can say that JOIN is a combination of Product, Selection and Projection operators.
JOIN is an associative operator, which means
(A JOIN B) JOIN C = A JOIN (B JOIN C).
JOIN is also commutative.
A JOIN B = B JOIN A 
So, we can say that JOIN is a combination of Product, Selection and Projection operators.
JOIN is an associative operator, which means
(A JOIN B) JOIN C = A JOIN (B JOIN C)
JOIN is also commutative
A JOIN B = B JOIN A
Division: The division operator divides a dividend relation A of degree (means number of columns in a relation) m+n by a divisor relation B of degree n, and produces a\ resultant relation of degree m. It is denoted by ÷.
             
In this example dividend relation A has two attributes of Sno,Pno (of degree 2) and division relation B has only one attribute Pno (of degree 1). Then, A divide by B gives a resultant relation of degree 1. It means it has only one attribute of Sno.
A = SNo * SNo = SNo
B = PNo
The resultant relation has those tuples that are common values of those attributes, which appears in the resultant attribute sno.
For example, in CASE II,
P2 has Snos. -- S1,S2,S3,S4
P4 has Snos. -- S1,S4
S1, S4 are the common supplier who supply both P2 and P4.
So the resultant relation has tuples S1 and S4.
In CASE III
There is only one supplier S1 who supply all the parts from PI to P6
Examples based on Relational Algebra
Consider the following database:
S (S#, SNAME, STATUS, CITY)
P (P#, PNAME, COLOR, WEIGHT)
SP (S#, P#, QTY)
1) Get supplier names for suppliers who supply part P2
We first show a step-at-a-time solution
TEMP1 [S#, SNAME, STATUS, CITY P#, QTY]:= S JOIN SP ;
TEMP2 [S#, SNAME, STATUS, CITY P#, QTY]:= TEMP1 WHERE P# ='P2';
RESULT [SNAME]: = TEMP2 [SNAME];
Using a nested expression;
( ( S JOIN SP ) WHERE Pno = 'P2' ) [ SNAME]
The result of this expression has one attribute, with qualified name S.SNAME.
2) Get supplier numbers for suppliers who supply at least one red part.
(( P WHERE COLOR = 'RED' ) [ Pno ] JOIN SP ) [Sno]
Resultant attribute-name: SP.S#
3) Get supplier numbers for supplier who supply at least all those parts supplied by supplier S2
SP [ Sno, Pno] DIVIDE BY ( SP WHERE Sno = 'S2') [Pno]
Resultant attribute-name: SP.S#.
4) Get supplier names for suppliers who do not supply part P2.
(( S [ Sno] MINUS ( SP WHERE Pno = 'P2' [ Sno ] ) JOIN S ) [ SNAME ]
Resultant attribute-name: S.SNAME.
 [/vc_column_text][/vc_column][/vc_row]
You’ll also like:
Write A C++ Program To Comparing Integers Using If Statements, Relational Operators And Equality Operators. 
Relational Operators in Java Example 
Relational and Equality Operators 
Explain purpose of relational operators and logical operator 
Write C++ program illustrates the hierarchy rule in a Boolean expression involving arithmetic, relational and logical operators 
   
Next →
← Prev