RELATIONAL ALGEBRA in DBMS
In order to access or modify data in any relational database systems, there is a need of a query language.
Relational Algebra is one of those query languages along with Relational Calculus, which makes use of operators (unary and binary) to perform the queries. As the name suggests, this language accepts relations as input and delivers the same as output.
The operations that can be performed using Relational Algebra is as follows,
1.Select (σ)
2.Union (U)
3.Set Difference (−)
4.Cartesian Product (Χ)
5.Project (∏)
6.Rename (ρ)
|
|
SELECT |
UNION |
SET DIFFERENCE |
CARTESIAN PRODUCT |
PROJECT |
RENAME |
|
Notation |
σp(r) |
r U s |
r − s |
r Χ s |
∏A1, A2, An (r) |
ρ x (E) |
|
Notation Expansion |
r denotes relation and p denotes propositional logic formula ( AND,OR,NOT) |
r and s denotes relation or relation result set |
r and s denotes relation or relation result set |
r and s denotes relation or relation result set |
A1, A2 , An are attribute names of relation r |
E denotes Expression And x denotes the name given to result of the expression. |
|
Example |
σmonth = "feb" and age>18(Voters)
|
∏ name (Math) ∪ ∏ name (CS)
|
∏ name (Math) - ∏ name (CS)
|
σstudent = 'name'(Math X CS)
|
∏name, age (Voters)
|
ρ sname,sage (Voters) |
|
Example Explanation |
Outputs those records of people who voted in the month of February from the table Voters. |
Outputs those names of students who opted for Math, CS or both. |
Outputs those names of students who opted for Math but not CS. |
Outputs name of all students who chose Math and CS. |
Outputs the name and age columns of table Voters. |
Renames the attributes name and age as sname and sage. |
Apart from these common operations, Set Intersection, Assignment and Natural Join are other notable operations in Relational Algebra.
Unlike Relational Algebra, which is a procedural language, Relational Calculus is a non-procedural language, meaning, it lacks explanation about how to perform an operation.
These are of two forms, namely,
1.Tuple Relational Calculus
2.Domain Relational Calculus
|
|
Tuple Relational Calculus |
Domain Relational Calculus |
|
Notation and Expansion |
{T | Condition} T denotes tuples and | denotes that T are those tuples that satisfy a condition. |
{a1, a2, a3, ..., an | P (a1, a2, a3, ... ,an)} a1,a2 are attributes P is a formula built by inner attribute |
|
Example |
{ T.name | Stud(T) AND T.subject = 'Math' }
|
{< name,age > | ∈ Stud ∧ subject = 'Math'}
|
|
Example Explanation |
Outputs names of Students from table Stud who opted Math as the subject. |
Outputs name and age of students from table Stud who opted Math as the subject |