• Skip to main content
  • Skip to primary sidebar
  • Skip to secondary sidebar
  • Skip to footer

Computer Notes

Library
    • Computer Fundamental
    • Computer Memory
    • DBMS Tutorial
    • Operating System
    • Computer Networking
    • C Programming
    • C++ Programming
    • Java Programming
    • C# Programming
    • SQL Tutorial
    • Management Tutorial
    • Computer Graphics
    • Compiler Design
    • Style Sheet
    • JavaScript Tutorial
    • Html Tutorial
    • Wordpress Tutorial
    • Python Tutorial
    • PHP Tutorial
    • JSP Tutorial
    • AngularJS Tutorial
    • Data Structures
    • E Commerce Tutorial
    • Visual Basic
    • Structs2 Tutorial
    • Digital Electronics
    • Internet Terms
    • Servlet Tutorial
    • Software Engineering
    • Interviews Questions
    • Basic Terms
    • Troubleshooting
Menu

Header Right

Home » Database » Rdbms » Types of Relational Operators
Next →
← Prev

Types of Relational Operators

By Dinesh Thakur

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:RS Table

These can certainly be combined in to one table containing a valid relation by the relational union operator ( R US) as follows:

RUS TableIntersection: 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: R Intersection SThe 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:

R-S

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

Cartesian Product

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.

Male_Female

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:

Selection Operator

                                   Shipment Table

Here, in Supplier table

 
SNo: Supplier number of supplier that is unique
Sname: Supplier name 

Examples:Example Table

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’

Example TableProjection: 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:

Employee Table

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:

Projection tableJoin: 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:

joined tableThe 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:

Employee_product_customer

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:

natural join

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:

tuple of the cartesian product

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:

restriction operation

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:

out join

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

Relation A

             Relation B

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:

  1. Write A C++ Program To Comparing Integers Using If Statements, Relational Operators And Equality Operators.
  2. Relational Operators in Java Example
  3. Relational and Equality Operators
  4. Explain purpose of relational operators and logical operator
  5. Write C++ program illustrates the hierarchy rule in a Boolean expression involving arithmetic, relational and logical operators
Next →
← Prev
Like/Subscribe us for latest updates     

About Dinesh Thakur
Dinesh ThakurDinesh Thakur holds an B.C.A, MCDBA, MCSD certifications. Dinesh authors the hugely popular Computer Notes blog. Where he writes how-to guides around Computer fundamental , computer software, Computer programming, and web apps.

Dinesh Thakur is a Freelance Writer who helps different clients from all over the globe. Dinesh has written over 500+ blogs, 30+ eBooks, and 10000+ Posts for all types of clients.


For any type of query or something that you think is missing, please feel free to Contact us.


Primary Sidebar

DBMS

Database Management System

    • DBMS - Home
    • DBMS - Definition
    • DBMS - What is
    • DBMS - Entity Sets
    • DBMS - Components
    • DBMS - Languages
    • DBMS - Normalization
    • DBMS - Data Models
    • DBMS - Processing System
    • DBMS - Advantages
    • DBMS - ER-Model
    • DBMS - Functional Dependence
    • DBMS - Relational Model
    • DBMS - Architecture
    • DBMS - Network Model
    • DBMS - Approach
    • DBMS - Data Independence
    • DBMS - Relational Schema
    • DBMS - Instance
    • DBMS - Functions and Service
    • DBMS - Server
    • DBMS - DBA
    • DBMS - Instance & Schemas
    • DBMS - System Type
    • DBMS - DDL, DML and DCL
    • DBMS - Users
    • DBMS - Model
    • DBMS - System Structure
    • DBMS - Role of DBA
    • DBMS - Metadata
    • DBMS - ER-Diagram
    • DBMS - E-R Model Problems
    • DBMS - DBMS Vs.RDBMS
    • DBMS - Basic Construction of E-R
    • DBMS - E-R Notation
    • DBMS - Database View
    • DBMS - Concurrency Control
    • DBMS - Schema
    • DBMS - Procedure for Access
    • DBMS - Object
    • DBMS - dBase
    • DBMS - Relational Algebra
    • DBMS - Deadlock
    • DBMS - Relational Database
    • DBMS - Query
    • DBMS - Schema

DBMS Normal Forms

    • Database - CODD’S Rules
    • Database - 1NF
    • Database - 2NF
    • Database - 3NF
    • Database - 4NF
    • Database - 5NF
    • Database - BCNF

Advance Database

    • Database - File Organization
    • Database - Type Lock
    • Database - Transaction
    • Database - Key Type
    • Database - Relational Algebra
    • Database - Components
    • Database - Deadlock Detect
    • Database - Design Methodology
    • Database - Relational Operators
    • Database - Relational Calculus
    • Database - Lock Granularity
    • Database - Deadlocks Handling
    • Database - Concurrent Control
    • Database - Denormalization
    • Database - Starvation
    • Database - OODB
    • Database - Data Warehouse
    • Database - Fragmentation
    • Database - Data Replication
    • Database - Distributed
    • Database - Transparences
    • Database - ORDBMSS
    • Database - Data Mining
    • Database - Security
    • Database - DBTG
    • Database - OLAP
    • Database - Integrity
    • Database - Data Encryption
    • Database - Recover
    • Database - Data Protection

Some Other Advance Articls

  • Adv of Distributed DBMS
  • Homogeneous and Heterogeneous
  • Causes for Database Failure
  • DBMS Architecture
  • Features for Any DBMS
  • OLTP Systems Vs Data Warehousing
  • Data Warehousing Architecture

Other Links

  • DBMS - PDF Version

Footer

Basic Course

  • Computer Fundamental
  • Computer Networking
  • Operating System
  • Database System
  • Computer Graphics
  • Management System
  • Software Engineering
  • Digital Electronics
  • Electronic Commerce
  • Compiler Design
  • Troubleshooting

Programming

  • Java Programming
  • Structured Query (SQL)
  • C Programming
  • C++ Programming
  • Visual Basic
  • Data Structures
  • Struts 2
  • Java Servlet
  • C# Programming
  • Basic Terms
  • Interviews

World Wide Web

  • Internet
  • Java Script
  • HTML Language
  • Cascading Style Sheet
  • Java Server Pages
  • Wordpress
  • PHP
  • Python Tutorial
  • AngularJS
  • Troubleshooting

 About Us |  Contact Us |  FAQ

Dinesh Thakur is a Technology Columinist and founder of Computer Notes.

Copyright © 2023. All Rights Reserved.