Relational Operator
Relational Algebra is procedural query language, which takes Relation as input and generate relation as output. Relational algebra mainly provides theoretical foundation for relational databases and SQL.
Operators in Relational Algebra
Projection (π)
Projection is used to project required column data from a relation.
Projection is used to project required column data from a relation.
Example :
R (A B C) ---------- 1 2 4 2 2 3 3 2 3 4 3 4
π (BC) B C ----- 2 4 2 3 3 4
Note: By Default projection removes duplicate data.
Selection (σ)
Selection is used to select required tuples of the relations.
Selection is used to select required tuples of the relations.
for the above relation
σ (c>3)R
will select the tuples which have c more than 3.
σ (c>3)R
will select the tuples which have c more than 3.
Note: selection operator only selects the required tuples but does not display them. For displaying, data projection operator is used.
For the above selected tuples, to display we need to use projection also.
π (σ (c>3)R ) will show following tuples. A B C ------- 1 2 4 4 3 4
Union (U)
Union operation in relational algebra is same as union operation in set theory, only constraint is for union of two relation both relation must have same set of Attributes.
Union operation in relational algebra is same as union operation in set theory, only constraint is for union of two relation both relation must have same set of Attributes.
Set Difference (-)
Set Difference in relational algebra is same set difference operation as in set theory with the constraint that both relation should have same set of attributes.
Set Difference in relational algebra is same set difference operation as in set theory with the constraint that both relation should have same set of attributes.
Rename (ρ)
Rename is a unary operation used for renaming attributes of a relation.
ρ (a/b)R will rename the attribute ‘b’ of relation by ‘a’.
Rename is a unary operation used for renaming attributes of a relation.
ρ (a/b)R will rename the attribute ‘b’ of relation by ‘a’.
Cross Product (X)
Cross product between two relations let say A and B, so cross product between A X B will results all the attributes of A followed by each attribute of B. Each record of A will pairs with every record of B.
below is the example
A B
(Name Age Sex ) (Id Course)
------------------ -------------
Ram 14 M 1 DS
Sona 15 F 2 DBMS
kim 20 M
A X B
Name Age Sex Id Course
---------------------------------
Ram 14 M 1 DS
Ram 14 M 2 DBMS
Sona 15 F 1 DS
Sona 15 F 2 DBMS
Kim 20 M 1 DS
Kim 20 M 2 DBMS
Note: if A has ‘n’ tuples and B has ‘m’ tuples then A X B will have ‘n*m’ tuples.
Natural Join (⋈)
Natural join is a binary operator. Natural join between two or more relations will result set of all combination of tuples where they have equal common attribute.
Let us see below example
Emp Dep
(Name Id Dept_name ) (Dept_name Manager)
------------------------ ---------------------
A 120 IT Sale Y
B 125 HR Prod Z
C 110 Sale IT A
D 111 IT
Emp ⋈ Dep
Name Id Dept_name Manager
-------------------------------
A 120 IT A
C 110 Sale Y
D 111 IT A
Conditional Join
Conditional join works similar to natural join. In natural join, by default condition is equal between common attribute while in conditional join we can specify the any condition such as greater than, less than, not equal
Let us see below example
R S (ID Sex Marks) (ID Sex Marks) ------------------ -------------------- 1 F 45 10 M 20 2 F 55 11 M 22 3 F 60 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
Relational Set Operators uses relational algebra to manipulate contents in a database. All together there are eight different types of operators. These operators are SQL commands.
The first operator is the UNION. It combines all of the rows in one table with all of the rows in another table except for the duplicate tuples. The tables are required to have the same attribute characteristics for the Union command to work. The tables must be union-compatible which means that two tables being used have the same amount of columns and the columns have the same names, and also need to share the same domain. Relational Set Operators uses relational algebra to manipulate contents in a database. All together there are eight different types of operators. These operators are SQL commands.
The first operator is the UNION. It combines all of the rows in one table with all of the rows in another table except for the duplicate tuples. The tables are required to have the same attribute characteristics for the Union command to work. The tables must be union-compatible which means that two tables being used have the same amount of columns and the columns have the same names, and also need to share the same domain.
INTERSECT is the second SQL command that takes two tables and combines only the rows that appear in both tables. The tables must be union-compatible to be able to use the Intersect command or else it won't work.
DIFFERENCE in another SQL command that gets all rows in one table that are not found in the other table. Basically it subracts one table from the other table to leave only the attributes that are not the same in both tables. For this command to work both tables must be union-compatible.
PRODUCT command would show all possible pairs of rows from both tables being used. This command can also be referred to as the Cartesian Product.
SELECT is the command to show all rows in a table. It can be used to select only specific data from the table that meets certain criteria. This command is also referred to as the Restrict command.
PROJECT is the command that gives all values for certian attributes specified after the command. It shows a vertical view of the given table.
JOIN takes two or more tables and combines them into one table. This can be used in combination with other commands to get specific information. There are several types of the Join command. The Natural Join, Equijion, Theta Join, Left Outer Join and Right Outer Join. been DIVIDE has specific requirements of the table. One of the tables can only have one column and the other table must have two columns only.INTERSECT is the second SQL command that takes two tables and combines only the rows that appear in both tables. The tables must be union-compatible to be able to use the Intersect command or else it won't work.
DIFFERENCE in another SQL command that gets all rows in one table that are not found in the other table. Basically it subracts one table from the other table to leave only the attributes that are not the same in both tables. For this command to work both tables must be union-compatible.
PRODUCT command would show all possible pairs of rows from both tables being used. This command can also be referred to as the Cartesian Product.
SELECT is the command to show all rows in a table. It can be used to select only specific data from the table that meets certain criteria. This command is also referred to as the Restrict command.
PROJECT is the command that gives all values for certian attributes specified after the command. It shows a vertical view of the given table.
JOIN takes two or more tables and combines them into one table. This can be used in combination with other commands to get specific information. There are several types of the Join command. The Natural Join, Equijion, Theta Join, Left Outer Join and Right Outer Join. been DIVIDE has specific requirements of the table. One of the tables can only have one column and the other table must have two columns only.
0 comments:
Post a Comment