Relational Algebra refers to a procedural query language that accepts a Relation as input and outputs another Relation. Theoretical foundations for relational databases and SQL are provided by relational algebra. A relation’s needed column data is projected via projection.
In this article, we will look closer into Relational Algebra in DBMS according to the GATE Syllabus for (Computer Science Engineering) CSE. Read ahead to learn more on this.
Table of Contents
What is Relational Algebra in DBMS?
Relational algebra refers to a procedural query language that takes relation instances as input and returns relation instances as output. It performs queries with the help of operators. A binary or unary operator can be used. They take in relations as input and produce relations as output. Recursive relational algebra is applied to a relationship, and intermediate outcomes are also considered relations.
Relational Algebra Operations
The following are the fundamental operations present in a relational algebra:
- Select Operation
- Project Operation
- Union Operation
- Set Different Operation
- Cartesian Product Operation
- Rename Operation
Select Operation (or σ)
It selects tuples from a relation that satisfy the provided predicate.
The notation is − σp(r)
Here σ stands for the selection predicate while r stands for the relation. p refers to the prepositional logic formula that may use connectors such as or, and, and not. Also, these terms may make use of relational operators such as − =, ≠, ≥, < , >, ≤.
Example
σsubject = “information”(Novels)
The output would be − Selecting tuples from the novels wherever the subject happens to be ‘information’.
σsubject = “information” and cost = “150”(Novels)
The output would be − Selecting tuples from the novels wherever the subject happens to be ‘information’ and the ‘price’ is 150.
σsubject = “information” and cost = “150” or year > “2015”(Novels)
The output would be − Selecting tuples from the novels wherever the subject happens to be ‘information’ and the ‘price’ is 150 or those novels have been published after 2015.
Project Operation (or ∏)
It projects those column(s) that satisfy any given predicate.
Here B1, B2 , An refer to the attribute names of the relation r.
The notation is − ∏B1, B2, Bn (r)
Remember that duplicate rows are eliminated automatically, since relation is a set.
Example
∏subject, writer (Novels)
The output would be − Selecting and projecting columns named as writer as well as the subject from the relation Novels.
Union Operation (or ∪)
It would perform binary union between two relations.
The notation is − r U s
It is defined as follows:
r ∪ s = { t | t ∈ r or t ∈ s}
Here r and s either refer to DB relations or the relation result set (or temporary relation).
The given conditions must hold if we want any union operation to be valid:
- s, and r must contain a similar number of attributes.
- The domains of an attribute must be compatible.
- The duplicate tuples are eliminated automatically.
∏ writer (Novels) ∪ ∏ writer (Articles)
The output would be − Projecting the names of those writers who might have written either an article or a novel or both.
Set Different Operation (or −)
Tuples refers to the result of the set difference operation. These are present in just one of the relations but not at all in the second one.
The notation is − r − s
Finding all the tuples present in r and not present in s.
∏ writer (Novels) − ∏ writer (Articles)
The output would be − Providing the writer names who might have written novels but have not written articles.
Cartesian Product Operation (or Χ)
It helps in combining data and info of two differing relations into a single one.
The notation is − r Χ s
Where s and r refer to the relations. Their outputs would be defined as the follows:
s Χ r = { t ∈ s and q t | q ∈ r}
σwriter = ‘mahesh'(Novels Χ Articles)
The output would be − Yielding a relation that shows all the articles and novels written by mahesh.
Rename Operation (or ρ)
Relations are the results of the relational algebra, but without any name. Thus, the rename operation would allow us to rename the relation output. The ‘rename’ operation is basically denoted by the small Greek letter ρ or rho.
The notation is − ρ x (E)
Here the result of the E expression is saved with the name of x.
The additional operations are as follows:
- Natural join
- Assignment
- Set intersection
Relational Calculus
Relational Calculus, as compared to Relational Algebra, refers to a non-procedural type of query language. It means that it would tell you what to do but would never explain how to do it.
The relational calculus exists in these two forms:
Tuple Relational Calculus (or TRC)
These filter the variable ranges over the tuples
The notation is − {T | Condition}
It returns all the tuples T that satisfy the given condition.
Example
{ T.name | Writer(T) AND T.blog = ‘information’ }
The output would be − It would return the tuples with the ‘name’ from the Writer that has written a blog on the ‘information’.
Remember that TRC can be quantified. Thus, one can use Existential (or ∃) as well as Universal Quantifiers (or ∀).
Example
{ R| ∃T ∈ Writers(T.blog=’information’ AND R.name=T.name)}
The output would be − The query given above would yield a similar result as the very previous one.
Domain Relational Calculus (or DRC)
The filtering variable in DRC makes use of the attributes domain instead of the values of an entire tuple (as mentioned in TRC, done above).
The notation is − { b1, b2, b3, …, an | P (b1, b2, b3, … ,bn)}
Here b1, b2 refer to the attributes, and P refers to the formulae that the inner attributes have built.
Example
{< page, article, subject > | ∈ Chocolate ∧ subject = ‘information’}
The output would be − Yielding Page, Article, as well as Subject from the relation Chocolate, where subject refers to the database.
DRC, just like TRC, can be written using universal and existential quantifiers. Also, DRC involves relational operators.
Remember that the expression power of the TRC and the DRC is equivalent to the Relational Algebra.
Keep learning and stay tuned to get the latest updates on the GATE Exam along with Eligibility Criteria, GATE Syllabus for CSE (Computer Science Engineering), GATE CSE Notes, GATE CSE Question Paper, and more.
Also Explore,
Comments