Explain the unary operations of algebra relations in DBMS?

DBMSDatabaseBig Data Analytics

Query is a question or requesting information. Query language is a language which is used to retrieve information from a database.

Query language is divided into two types −

  • Procedural language

  • Non-procedural language

Procedural language

Information is retrieved from the database by specifying the sequence of operations to be performed.

For Example − Relational algebra.

Structure Query language (SQL) is based on relational algebra.

Relational algebra consists of a set of operations that take one or two relations as an input and produces a new relation as output.

Types of Relational Algebra operations

The different types of relational algebra operations are as follows −

  • Select operation

  • Project operation

  • Rename operation

  • Union operation

  • Intersection operation

  • Difference operation

  • Cartesian product operation

  • Join operation

  • Division operation

Select, project, rename comes under unary operation (operate on one table).

Select operation

It displays the records that satisfy a condition and is denoted by sigma (σ). It is a horizontal subset of the original relation.

Syntax

The syntax for the select operation is as follows −

σcondition(table name)

Example 1

Consider the student table given below −

RegnoBranchSection
1CSEA
2ECEB
3CIVILB
4ITA

To display all the records of student table, use the command given below −

σ(student)

To display all the records of CSE branch in student table, we can use the command mentioned below −

σbranch=cse(student)

Output

RegNoBranchSection
1CSEA

To display all the records in student tables whose regno>2, we can use the command given below −

σRegNo>2(student)

Output

RegNoBranchSection
3CIVILB
4ITA

To display the record of ECE branch section B students, use the command given below −

σbranch=ECE ^ section=B(student)

To display the records of section B CSE and IT branch, use the following command −

σSection=B ^ Branch=cse ∨ branch=IT(student)

Example 2

Consider the EMPLOYEE TABLE as another example to know about selection operation −

Retrieve information about those employees whose salary is greater than 20,000.

  • If one condition is specified then, we get the following −

σ salary > 20,000 (emp)
  • If more than one condition specified in the query then ( AND: ∧, OR:∨ , Not:#, equal: =, >, <, >=, <=)

Relational operator will be used to combine the multiple conditions into a single statement.

Example − If we wish to retrieve information of those employees whose salary > 20,000 and working as the Head of the Department (HOD) and Department number is 20.

σ salary > 20,000 ^LOC=HOD ^Deptno=20(emp)

Projection operation

It displays the specific column of a table. It is denoted by pie (Π). It is a vertical subset of the original relation. It eliminates duplicate tuples.

Syntax

The syntax of projection operation is as follows −

∏regno(student)

Example 1

Consider the student table given below −

RegnoBranchSection
1CSEA
2ECEB
3CIVILB
4ITA

To display regno column of student table, use the following command −

∏regno(student)

Output

RegNo
1
2
3
4

To display branch, section column of student table, use the command given below −

branch,section(student)

Output

BranchSection
CSEA
ECEB
CIVILB
ITA

To display regno, section of ECE students, use the command mentioned below −

∏regno,section(σbranch=ECE(student))

Output

RegnoSection
2B

Note − Conditions can be written in select operation but not in projection operation.

Example 2

Consider the employee table to know more about projection.

  • If no condition is specified in the query then,

∏ empid, ename, salary, address, dno (emp)
  • If condition is specified then, the composition of the select and projection is as follows −

∏ empid, ename, salary, address, dno (σ salary >20,00 ^ LOC = HOD ^ dno=20) (emp)

Rename operation

It is used to assign a new name to a relation. It is denoted by ρ (rho).

Syntax

The syntax for rename operation is as follows −

ρnewname (tablename or expression)

Consider the student table given below −

RegnoBranchSection
1CSEA
2ECEB
3CIVILB
4ITA

Example 1

The student table is renamed with newstudent with the help of the following −

ρnewstudent (student)

Example 2

The names, branch columns of the student table are renamed and newbranch respectively with the help of the following command −

ρnewname,newbranch(Πname,branch( student))

Binary operations are applied on two compatible relations.

Two relations R1, R2 are to be compatible, if they are of the same degree and the domain of corresponding attributes are the same.

The Rho in DDL used for name of relation and in DML used for name of attributes.

            SQL                      Old name                   New name

Types of RENAME

Renaming can be used by three methods, which are as follows −

  • Changing name of the relation.

  • Changing name of the attribute.

  • Changing both.

raja
Published on 06-Jul-2021 14:58:50
Advertisements