Open In App

Extended Operators in Relational Algebra

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

Basic idea about  relational model and basic operators in Relational Algebra: Relational Model Basic Operators in Relational Algebra Extended operators are those operators which can be derived from basic operators. There are mainly three types of extended operators in Relational Algebra: 

  • Join
  • Intersection
  • Divide 

The relations used to understand extended operators are STUDENT, STUDENT_SPORTS, ALL_SPORTS and EMPLOYEE which are shown in Table 1, Table 2, Table 3 and Table 4 respectively. STUDENT

ROLL_NO NAME ADDRESS PHONE AGE
1 RAM DELHI 9455123451 18
2 RAMESH GURGAON 9652431543 18
3 SUJIT ROHTAK 9156253131 20
4 SURESH DELHI 9156768971 18

Table 1

  STUDENT_SPORTS         

ROLL_NO SPORTS
1 Badminton
2 Cricket
2 Badminton
4 Badminton

Table 2

 ALL_SPORTS

SPORTS
Badminton
Cricket 

Table 3

EMPLOYEE                                                               

EMP_NO NAME ADDRESS PHONE AGE
1 RAM DELHI 9455123451 18
5 NARESH HISAR 9782918192 22
6 SWETA RANCHI 9852617621 21
4 SURESH DELHI 9156768971 18

 Table 4

Intersection (?): Intersection on two relations R1 and R2 can only be computed if R1 and R2 are union compatible (These two relation should have same number of attributes and corresponding attributes in two relations have same domain). Intersection operator when applied on two relations as R1?R2 will give a relation with tuples which are in R1 as well as R2. Syntax:

 Relation1 ? Relation2
Example: Find a person who is student as well as employee-  STUDENT ? EMPLOYEE  

In terms of basic operators (union and minus) :

STUDENT ? EMPLOYEE = STUDENT + EMPLOYEE - (STUDENT U EMPLOYEE) 

RESULT:

ROLL_NO NAME ADDRESS PHONE AGE
1 RAM DELHI 9455123451 18
4 SURESH DELHI 9156768971 18

  Conditional Join(?c): Conditional Join is used when you want to join two or more relation based on some conditions. Example: Select students whose ROLL_NO is greater than EMP_NO of employees

STUDENT?c STUDENT.ROLL_NO>EMPLOYEE.EMP_NOEMPLOYEE

In terms of basic operators (cross product and selection) :

? (STUDENT.ROLL_NO>EMPLOYEE.EMP_NO)(STUDENT×EMPLOYEE)

RESULT:

ROLL_NO NAME ADDRESS PHONE AGE EMP_NO NAME ADDRESS PHONE AGE
2 RAMESH GURGAON 9652431543 18 1 RAM DELHI 9455123451 18
3 SUJIT ROHTAK 9156253131 20 1 RAM DELHI 9455123451 18
4 SURESH DELHI 9156768971 18 1 RAM DELHI 9455123451 18

Equijoin(?): Equijoin is a special case of conditional join where only equality condition holds between a pair of attributes. As values of two attributes will be equal in result of equijoin, only one attribute will be appeared in result. Example: Select  students whose ROLL_NO is equal to EMP_NO of employees.

STUDENT?STUDENT.ROLL_NO=EMPLOYEE.EMP_NOEMPLOYEE

In terms? of basic operators (cross product, selection and projection) :

?(STUDENT.ROLL_NO, STUDENT.NAME, STUDENT.ADDRESS, STUDENT.PHONE, STUDENT.AGE EMPLOYEE.NAME, EMPLOYEE.ADDRESS, EMPLOYEE.PHONE, EMPLOYEE>AGE)(? (STUDENT.ROLL_NO=EMPLOYEE.EMP_NO) (STUDENT×EMPLOYEE))

RESULT:

ROLL_NO NAME ADDRESS PHONE AGE NAME ADDRESS PHONE AGE
1 RAM DELHI 9455123451 18 RAM DELHI 9455123451 18
4 SURESH DELHI 9156768971 18 SURESH DELHI 9156768971 18

Natural Join(?): It is a special case of equijoin in which equality condition hold on all attributes which have same name in relations R and S (relations on which join operation is applied). While applying natural join on two relations, there is no need to write equality condition explicitly. Natural Join will also return the similar attributes only once as their value will be same in resulting relation. Example: Select students whose ROLL_NO is equal to ROLL_NO of STUDENT_SPORTS as:

STUDENT?STUDENT_SPORTS

In terms of basic operators (cross product, selection and projection) :

?(STUDENT.ROLL_NO, STUDENT.NAME, STUDENT.ADDRESS, STUDENT.PHONE, STUDENT.AGE STUDENT_SPORTS.SPORTS)(? (STUDENT.ROLL_NO=STUDENT_SPORTS.ROLL_NO) (STUDENT×STUDENT_SPORTS))

RESULT:

ROLL_NO NAME ADDRESS PHONE AGE SPORTS
1 RAM DELHI 9455123451 18 Badminton
2 RAMESH GURGAON 9652431543 18 Cricket
2 RAMESH GURGAON 9652431543 18 Badminton
4 SURESH DELHI 9156768971 18 Badminton

Natural Join is by default inner join because the tuples which does not satisfy the conditions of join does not appear in result set. e.g.; The tuple having ROLL_NO 3 in STUDENT does not match with any tuple in STUDENT_SPORTS, so it has not been a part of result set. 

Left Outer Join(?): When applying join on two relations R and S, some tuples of R or S does not appear in result set which does not satisfy the join conditions. But Left Outer Joins gives all tuples of R in the result set. The tuples of R which do not satisfy join condition will have values as NULL for attributes of S. Example:Select students whose ROLL_NO is greater than EMP_NO of employees and details of other students as well

STUDENT?STUDENT.ROLL_NO>EMPLOYEE.EMP_NOEMPLOYEE

RESULT

ROLL_NO NAME ADDRESS PHONE AGE EMP_NO NAME ADDRESS PHONE AGE
2 RAMESH GURGAON 9652431543 18 1 RAM DELHI 9455123451 18
3 SUJIT ROHTAK 9156253131 20 1 RAM DELHI 9455123451 18
4 SURESH DELHI 9156768971 18 1 RAM DELHI 9455123451 18
1 RAM DELHI 9455123451 18 NULL NULL NULL NULL NULL

Right Outer Join(?): When applying join on two relations R and S, some tuples of R or S does not appear in result set which does not satisfy the join conditions. But Right Outer Joins gives all tuples of S in the result set. The tuples of S which do not satisfy join condition will have values as NULL for attributes of R. Example: Select students whose ROLL_NO is greater than EMP_NO of employees and details of other Employees as well

STUDENT?STUDENT.ROLL_NO>EMPLOYEE.EMP_NOEMPLOYEE

RESULT:

ROLL_NO NAME ADDRESS PHONE AGE EMP_NO NAME ADDRESS PHONE AGE
2 RAMESH GURGAON 9652431543 18 1 RAM DELHI 9455123451 18
3 SUJIT ROHTAK 9156253131 20 1 RAM DELHI 9455123451 18
4 SURESH DELHI 9156768971 18 1 RAM DELHI 9455123451 18
NULL NULL NULL NULL NULL 5 NARESH HISAR 9782918192 22
NULL NULL NULL NULL NULL 6 SWETA RANCHI 9852617621 21
NULL NULL NULL NULL NULL 4 SURESH DELHI 9156768971 18

Full Outer Join(?): When applying join on two relations R and S, some tuples of R or S does not appear in result set which does not satisfy the join conditions. But Full Outer Joins gives all tuples of S and all tuples of R in the result set. The tuples of S which do not satisfy join condition will have values as NULL for attributes of R and vice versa. Example:Select students whose ROLL_NO is greater than EMP_NO of employees and details of other Employees as well and other Students as well

STUDENT?STUDENT.ROLL_NO>EMPLOYEE.EMP_NOEMPLOYEE

RESULT:

ROLL_NO NAME ADDRESS PHONE AGE EMP_NO NAME ADDRESS PHONE AGE
2 RAMESH GURGAON 9652431543 18 1 RAM DELHI 9455123451 18
3 SUJIT ROHTAK 9156253131 20 1 RAM DELHI 9455123451 18
4 SURESH DELHI 9156768971 18 1 RAM DELHI 9455123451 18
NULL NULL NULL NULL NULL 5 NARESH HISAR 9782918192 22
NULL NULL NULL NULL NULL 6 SWETA RANCHI 9852617621 21
NULL NULL NULL NULL NULL 4 SURESH DELHI 9156768971 18
1 RAM DELHI 9455123451 18 NULL NULL NULL NULL NULL

Division Operator (÷): Division operator A÷B or A/B can be applied if and only if:

  • Attributes of B is proper subset of Attributes of A.
  • The relation returned by division operator will have attributes = (All attributes of A – All Attributes of B)
  • The relation returned by division operator will return those tuples from relation A which are associated to every B’s tuple.

                                                                                                  A

x y
a 1
b 2
a 2
d 4

                                                                                                    ÷?

                                                                                                  B

y
1
2

 The resultant of A/B is  

                                                                                                A ÷ B

x
a

 Division can be expressed in terms of Cross Product , Set Difference and Projection.

 In the above example , for A/B , compute all  x values that are not disqualified by some y in B.

 x value is disqualified if attaching y value from B, we obtain xy tuple that is not in A.

 Disqualified x values:     ?x(( ?x(A) × B ) – A)

 So     A/B  = ?x( A )  ? all disqualified tuples

          A/B   = ?x( A )  ?  ?x(( ?x(A) × B ) – A)

 In the above example , disqualified tuples are 

b 2
d 4

 So, the resultant is 

x
a

Advantages:

Expressive Power: Extended operators allow for more complex queries and transformations that cannot be easily expressed using basic relational algebra operations.

Data Reduction: Aggregation operators, such as SUM, AVG, COUNT, and MAX, can reduce the amount of data that needs to be processed and displayed.

Data Transformation: Extended operators can be used to transform data into different formats, such as pivoting rows into columns or vice versa.

More Efficient: Extended operators can be more efficient than expressing the same query in terms of basic relational algebra operations, since they can take advantage of specialized algorithms and optimizations.

Disadvantages:

Complexity: Extended operators can be more difficult to understand and use than basic relational algebra operations. They require a deeper understanding of the underlying data and the operators themselves.

Performance: Some extended operators, such as outer joins, can be expensive in terms of performance, especially when dealing with large data sets.

Non-standardized: There is no universal set of extended operators, and different relational database management systems may implement them differently or not at all.

Data Integrity: Some extended operators, such as aggregate functions, can introduce potential problems with data integrity if not used properly. For example, using AVG on a column that contains null values can result in unexpected or incorrect results.

Overview of Relational Algebra Operators Previous Year Gate Questions https://www.geeksforgeeks.org/gate-gate-cs-2012-question-50/ https://www.geeksforgeeks.org/gate-gate-cs-2012-question-43/ Article contributed by Sonal Tuteja.



Last Updated : 08 May, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads