Every database management system must define a query language to allow users to access the data stored in the database. Relational Algebra is a procedural query language used to query the database tables to access data in different ways. In relational algebra, input is a relation(table from which data has to be accessed) and output is also a relation(a temporary table holding the data asked for by the user).
Relational Algebra works on the whole table at once, so we do not have to use loops etc to iterate over all the rows(tuples) of data one by one. All we have to do is specify the table name from which we need the data, and in a single line of command, relational algebra will traverse the entire given table to fetch data. The primary operations that we can perform using relational algebra are:
Select
Project
Union
Set Different
Cartesian product
Rename
Relational algebra operations are performed recursively on a relation. The output of these operations is a new relation, which might be formed from one or more input relations.
Relational Algebra divided in various groups
Unary Relational Operations
SELECT (symbol: σ ) - sigma
PROJECT (symbol: ∏) - pi
RENAME (symbol: ρ) - rho
Relational Algebra Operations From Set Theory
UNION (∪)
INTERSECTION (∩ ),
DIFFERENCE (-)
CARTESIAN PRODUCT (X )
Binary Relational Operations
JOIN(⋈)
DIVISION
SELECT (σ) - It selects ROWS based on given condition(predicate)
The SELECT operation is used for selecting a subset of the tuples according to a given selection condition. Sigma(σ)Symbol denotes it. Select operation selects tuples that satisfy a given predicate.
σ p(r)
σ is the predicate
r stands for relation which is the name of the table
p is prepositional logic
Example:1
σ topic = "Database" (Tutorials)
Output - Selects tuples from Tutorials where topic = 'Database'.
Example 2
σ topic = "Database" and author = "rk"( Tutorials)
Output - Selects tuples from Tutorials where the topic is 'Database' and 'author' is guru.
Example 3
σ marks>85 (Student)
Output - Selects tuples from Student where marks is greater than 85.
Projection( ∏)
The projection eliminates all attributes of the input relation but those mentioned in the projection list. The projection method defines a relation that contains a vertical subset of Relation. (pi) The symbol used to choose attributes from a relation. This operation helps you to keep specific columns from a relation and discards the other columns.
Ex: Table : Customer
CustomerID CustomerName Status
1 Google Active
2 Amazon Active
3 Apple Inactive
4 Alibaba Active
If we want to get CustomerName and Status of Customer table we use projection as follows.
∏CustomerName, Status (Customers)
Output:
CustomerName Status
Google Active
Amazon Active
Apple Inactive
Alibaba Active
Union operation (∪)
UNION is symbolized by ? symbol. It includes all tuples that are in tables A or in B. It also eliminates duplicate tuples. So, set A UNION set B would be expressed as:
The result <-- A U B
For a union operation to be valid, the following conditions must hold -
R and S must be the same number of attributes.
Attribute domains need to be compatible.
Duplicate tuples should be automatically removed
Example : Consider the following tables A,B for UNION, DIFFERENCE &
INTERSECTION.
Table A
col1 col2
1 1
1 2
Table B
col1 col2
1 1
1 3
A ∪B gives
Table : A ∪ B
col1 col2
1 1
1 2
1 3
Set Difference (-)
- Symbol denotes it. The result of A - B, is a relation which includes all tuples that are
in A but not in B.
The attribute name of A has to match with the attribute name in B.
The two-operand relations A and B should be either compatible or Union compatible.
It should be defined relation consisting of the tuples that are in relation A,but not in B.
Example A-B
Table A - B
col1 col2
1 2
Intersection
An intersection is defined by the symbol n
A ∩ B
Defines a relation consisting of a set of all tuple that are in both A and B. However, A and B must be union-compatible.
Example: A ∩ B
Table A ∩ B
col1 col2
1 1
Cartesian product(X)
This type of operation is helpful to merge columns from two relations. Generally, a Cartesian product is never a meaningful operation when it performs alone. However, it becomes meaningful when it is followed by other operations
Example – Cartesian product
σ col2 = '1' (A X B)
Output – The above example shows all rows from relation A and B whose col2 has
value 1
σ col2 = '1' (A X B)
col1 col2
1 1
1 1
Types of JOIN:
Various forms of join operation are:
Inner Joins:
Theta join
EQUI join
Natural join
Outer join:
Left Outer Join
Right Outer Join
Full Outer Join
Inner Join:
In an inner join, only those tuples that satisfy the matching criteria are included, while the rest are excluded. Let's study various types of Inner Joins:
Theta Join(θ):
The general case of JOIN operation is called a Theta join. It is denoted by symbol ?
Example : A ⋈θ B
Theta join can use any conditions in the selection criteria.
For example:
A ⋈θ A.col2 > B.col2 (B)
A ⋈θ A.col2 > B.col2 (B)
col1 col2
1 2
EQUI join(=):
When a theta join uses only equivalence condition, it becomes a equi join.
For example:
A ⋈ A.col2 = B.col2 (B)
A ⋈ A.col2 = B.col2 (B)
col1 col2
1 1
EQUI join is the most difficult operations to implement efficiently in an RDBMS and
one reason why RDBMS have essential performance problems
NATURAL JOIN (⋈)
Natural join can only be performed if there is a common attribute (column) between the relations. The name and type of the attribute must be same.
Example
Consider the following two tables
C
Num Square
2 4
3 9
D
Num Cube
2 8
3 18
C ⋈ D
Num Square Cube
2 4 4
3 9 9
OUTER JOIN
In an outer join, along with tuples that satisfy the matching criteria, we also include some or all tuples that do not match the criteria.
In the left outer join, operation allows keeping all tuple in the left relation. However, if there is no matching tuple is found in right relation, then the attributes of right relation
in the join result are filled with null values.
Consider the following 2 Tables
A
Num Square
2 4
3 9
4 16
B
Num Cube
2 8
3 18
5 75
A B
Num Square Cube
2 4 4
3 9 9
4 16 -
However, if there is no matching tuple is found in the left relation, then the attributes of the left relation in the join result are filled with null values.
A B
Num Cube Square
2 8 4
3 18 9
5 75 -
In a full outer join, all tuples from both relations are included in the result, irrespective of the matching condition.
A B
Num Cube Square
2 4 8
3 9 18
4 16 -
5 - 75
No comments:
Post a Comment