DBMS – RELATIONAL ALGEBRA : Algebra – As we know is a formal structure that contains sets and operations, with operations being performed on those sets. Relational algebra can be defined as procedural query language which is the core of any relational query languages available for the database. It provides a framework for query implementation and optimization. When a query is made internally the relation algebra is being executed among the relations. To perform queries, it uses both unary and binary operators.

DBMS – RELATIONAL ALGEBRA

Let us first study the basic fundamental operations and then the other additional operations.

Fundamental operations are-

  1. Select
  2. Project
  3. Union
  4. Set difference
  5. Cartesian product
  6. Rename

Select operation

  • It performs the operation of selecting particular tuple or a row which satisfies a specific predicate from a relation.
  • It is a unary operation.
  • Represented by σp(r),where σ is selection predicate, r is relation, p is prepositional logic formula that might use connectors like and, or, not.
  • Examples
  1. σname = “Nicholas_sparks”(Novels), selects tuples from the Novels where the name of the author is Nicholas sparks.
  2. σname = “Nicholas_sparks” and price = “300”(Novels), selects tuples from the Novels where the name of the author is Nicholas sparks and price is 300.
  3. σname = “Nicholas_sparks” and price = “300” or “year” (Novels), selects tuples from the Novels where the name of the author is Nicholas sparks and price is 300 or those Novels published in a particular year.

Project Operation

  • It projects column(s) which satisfy a particular predicate (given predicate).
  • It is a unary operation.
  • Represented by ΠA1, A2, An ( r ), where A1, A 2, An  are the attributes of relation r.
  • As the relation is set, duplicate rows are automatically eliminated.
  • Example – Πname, author (Novels), selects and projects columns named as ‘name’ and ‘author’ from the relation Novels.

Union Operation

  • It performs the operation of binary union between two relations.
  • It is a set operation.
  • Represented by r s, where r and s are relations in database.
  • The following criteria have to be satisfied for a union operation to be valid, called as union compatibility.
  1. R and s should have the same degree (same number of attributes in the relation).
  2. Duplicate tuples are eliminated automatically.
  3. Domains of the attribute must be compatible. Say if r and s are two relations, then the ith attribute of r should have the same domain as ith attribute of s.
  • Example – Πauthor (Novels) Πauthor (Articles), projects the names of the authors who have written either a novel or an article or both.

Set Difference Operation

  • It gives the result as tuples which are present in one relation but not in the other relation.
  • It is a binary operation.
  • Represented by Πauthor (Novels) – Πauthor (Articles), results in names of the authors who have written Novels but not the Articles.

Cartesian Product Operation

  • It performs the function of combining information from two or more relations into one.
  • Represented by r Χ s, where r and s are relations.
  • Example – ‘Nicholas_sparks’ (Novels Χ Articles), provides a relation that shows all books and Articles by Nicholas sparks.

Rename Operation

  • Results in relational algebra are just the relations without any name, the rename operation allows to rename the output relation.
  • Represented by ρX (E), where E is a resultant expression with the name given as x.
  • Consider the example of a relation ‘Nicholas_sparks’ (Novels Χ Articles), which outputs all books and Articles by Nicholas sparks, this does not have any name. If required, it can be named as ρnewname [ Nicholas_sparks’ (Novels Χ Articles)] where newname is the name given to it.

Other operations are

Intersection

Natural join

Division

Assignment

Intersection Operation

  • It is a set operation, which selects only the common elements from two given relations.
  • Represented by r s, where r and s are relations in database.
  • Example – Πauthor (Novels) Πauthor (Articles), provides the names of authors who have written both Novels and Articles.

Natural join operation

  • It is a binary operation, combination of some selections and forms cartesian product of its two arguments.
  • Forms cartesian product, then performs selection forcing equality on the attributes appearing in both relations and ultimately removes duplicate attributes.
  • Represented by r |Χ| s, where r and s are relations.

Division operation

  • This outputs the result as restriction of tuples in one relation to the name of attributes unique to it. In other words, restriction of tuples in the header of r but not in the header of s, for which it also indicates all combinations of tuples in r are present in s.
  • Represented by r / s, where r and s are relations.

Assignment operation

  • It is similar to assignment operator in programming languages.
  • Denoted by ←
  • It is useful in the situation where it is required to write relational algebra expressions by using temporary relation variables.
  • The database might be modified if assignment to a permanent relation is made.

So these were the different types of operations in relational algebra.

DBMS - RELATIONAL ALGEBRAhttps://whatisdbms.com/wp-content/uploads/2017/04/relational-algebra.jpghttps://whatisdbms.com/wp-content/uploads/2017/04/relational-algebra-150x150.jpgSumit ThakurWhat Is DBMSDBMS - RELATIONAL ALGEBRA : Algebra - As we know is a formal structure that contains sets and operations, with operations being performed on those sets. Relational algebra can be defined as procedural query language which is the core of any relational query languages available for the database. It...Let's Define DBMS