- The Union, Intersection and Difference operations requires following condition
- The two relation/table must contain the same number of columns
- Each column of the first relation/table must be either the same data type as the corresponding column of the second relation/table or convertible to the same data type as corresponding column of the second.
Union
The result of operation is denoted by depositor Ս borrower, is the relation that includes all tuples that are either in depositor or borrower or both. Duplicates are eliminated.
Intersection
The Result of intersection operation is a relation that includes all tuples that are in both depositor and borrower. It is denoted by depositor ∩ borrower.
Difference
The difference operation is denoted by depositor – borrower.
The result of the difference operator is the relation that contain all tuples in depositor but not in borrower.
- Both Union and intersection operations are commutative and associative
- AUB =BUA and A∩B=B∩A
- AU(BUC)=(AUB)UC and A∩(B∩C)=(A∩B)∩C
- The difference operation is not commutative.
- A-B≠B-A
Cartesian Product
- It is known as CROSS PRODUCT or CROSS JOINS.
- It is denoted by 'x'
- The Cartesian product of two relations A and B is denoted by AxB
- The result of cartesian product of two relation which have X and Y columns is relation that has X+Y Columns. The resulting relation will have one tuple or each combination of tuples from each participating relation.
- If a relation have m and n tuples, then the CARTESIAN PRODUCT will have m x n tuples.
Additional Operations
We define additional operations that do not add any power to the relational algebra, but that simplify common queries.
- Set intersection
- Natural join
- Assignment
- Outer join
Natural Join
The natural join is a binary operation.
It is denoted by the join symbol ⨝
The natural join operation forms a cartesian product of its two arguments, performs a selection forcing equality on those attributes that appear in both relation schemes and finally removes duplicate attributes.
The difference between natural join and cartesian product is : the query involving cartesian product includes a selection operation on the result of the cartesian product.
Consider following schema
- employee(emp_id, emp_name)
- salary(emp_id,salary)
- Πemp_name,salary(σemployee.emp_id=salary.emp_id(employee x salary)) = Πemp_name,salary(employee ⨝ salary)
- The query using natural-join is simpler than cartesian product query.
Πemp_name,salary(employee ⨝ salary)
Division Operator
- The division operation is denoted by '÷' it is used for the queries include the phrase 'for all' / 'for every'.
- A(X,Y)÷B(Y) will results X values for that there should be tuple <x,y> for every Y value of relation B.
Division Example
List the E_id of works relation worked for all /every projects of Projects relation
The Assignment Operator
- The assignment operation is denoted by '←'.
- It works like assignment in programming language
- Example t1←σroll_no=1(students)
- The result of right side expression is assigned to the relation variable on the left.
- With assignment operation, a query can be written as a sequential program consisting of a series of assignment followed by an expression whose value is displayed as the result of the query.
Extended Relational Algebra Operations
- The basic relational-algebra operations have been extended in several ways
- A simple extension allow arithmetic operations as part of project.
- An important extension is, allow aggregate operations such as computing the sum of set, or their average.
- Another important extension is outer join operation, which allow relational algebra expression to deal with null values.
Generalized Projection
- The generalized projection operation extends the projection operation by allowing arithmetic function to be used in the projection list.
- It is in the form of ∏f1,f2,f3,…,fn(E) where E: relation algebra expression and f1,f2,f3... are an arithmetic expression involving constants and attributes of E.
- Example : ∏emp_id, salary*1.05(employees)
Aggregate Functions
- Aggregate Functions take a collection of values and return a single value as a result.
- It is represented 'calligraphic G' '𝒢'
- 𝒢sum(salary)(employees)
- Display the sum of salary of employees
- 𝒢count(emp_id)(employees)
- Count the number of employees in employees relation
- The 𝒢 signifies that aggregate function is to be applied and subscript specifies the aggregate operation to be applied.
Outer Join
It is extension of the join operation to deal with missing information.
In the below example the record of Raju was missing in the outcome of natural join
- To avoid the loss of information, we can use outer join operation.
- There are three forms of outer-join
- Left Outer-join
- Right Outer-join
- Full Outer-join
Left Outer-join
- It is denoted by ⟕
- It take all tuples in the left relation and matching records of right relation, pads the tuples with null values for all other attributes from the right relation.
Right Outer-join
- It is denoted by ⟖
- It take all tuples in the right relation and matching records of left relation, pads the tuples with null values for all other attributes from the left relation.
Full Outer-join
- It is denoted by ⟗
- It take all tuples in the right relation and all the tuples from left relation, pads the tuples with null values for non-matching attributes from the left and right relation.
Joins Summary
