Derived Operators
These are some of the derived operators, which are derived from the fundamental operators.
1. Natural Join(⋈): Natural join is a binary operator. Natural join between two or more relations will result in a set of all combinations of tuples where they have an equal common attribute.
Example:
EMP
| Name | ID | Dept_Name |
|---|---|---|
| A | 120 | IT |
| B | 125 | HR |
| C | 110 | Sales |
| D | 111 | IT |
DEPT
| Dept_Name | Manager |
|---|---|
| Sales | Y |
| Production | Z |
| IT | A |
Natural join between EMP and DEPT with condition :
EMP.Dept_Name = DEPT.Dept_Name
EMP ⋈ DEPT
| Name | ID | Dept_Name | Manager |
|---|---|---|---|
| A | 120 | IT | A |
| C | 110 | Sales | Y |
| D | 111 | IT | A |
2. Conditional Join: Conditional join works similarly to natural join. In natural join, by default condition is equal between common attributes while in conditional join we can specify any condition such as greater than, less than, or not equal.
Example:
R
| ID | Sex | Marks |
|---|---|---|
| 1 | F | 45 |
| 2 | F | 55 |
| 3 | F | 60 |
S
| ID | Sex | Marks |
|---|---|---|
| 10 | M | 20 |
| 11 | M | 22 |
| 12 | M | 59 |
Join between R and S with condition R.marks >= S.marks
| R.ID | R.Sex | R.Marks | S.ID | S.Sex | S.Marks |
|---|---|---|---|---|---|
| 1 | F | 45 | 10 | M | 20 |
| 1 | F | 45 | 11 | M | 22 |
| 2 | F | 55 | 10 | M | 20 |
| 2 | F | 55 | 11 | M | 22 |
| 3 | F | 60 | 10 | M | 20 |
| 3 | F | 60 | 11 | M | 22 |
| 3 | F | 60 | 12 | M | 59 |
Introduction of Relational Algebra in DBMS
Pre-Requisite: Relational Model in DBMS
Relational Algebra is a procedural query language. Relational algebra mainly provides a theoretical foundation for relational databases and SQL. The main purpose of using Relational Algebra is to define operators that transform one or more input relations into an output relation. Given that these operators accept relations as input and produce relations as output, they can be combined and used to express potentially complex queries that transform potentially many input relations (whose data are stored in the database) into a single output relation (the query results). As it is pure mathematics, there is no use of English Keywords in Relational Algebra and operators are represented using symbols.