Relational Algebra | Database Management System (DBMS) - Computer Science Engineering (CSE) PDF Download

Relational Algebra

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.

Example :

Relational Algebra | Database Management System (DBMS) - Computer Science Engineering (CSE)

Relational Algebra | Database Management System (DBMS) - Computer Science Engineering (CSE)

Note: By Default projection removes duplicate data.

Selection (σ)

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.

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.

Relational Algebra | Database Management System (DBMS) - Computer Science Engineering (CSE)

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.

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.

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’.

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

Relational Algebra | Database Management System (DBMS) - Computer Science Engineering (CSE)

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

Relational Algebra | Database Management System (DBMS) - Computer Science Engineering (CSE)

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

Relational Algebra | Database Management System (DBMS) - Computer Science Engineering (CSE)

Basic Operators in Relational Algebra

Relational Algebra is a procedural query language which takes relations as an input and returns relation as an output. There are some basic operators which can be applied on relations to produce required results which we will discuss one by one. We will use STUDENT_SPORTS, EMPLOYEE and STUDENT relations as given in Table 1, Table 2 and Table 3 respectively to understand the various operators.

Table 1 : STUDENT_SPORTS
 

ROLL_NO
SPORTS
1
Badminton
2
Cricket
3
Badminton
4
Badminton

Table 2 : EMPLOYEE

 

EMP_NO
NAME
ADDRESS
PHONE
AGE
1
RAM
DELHI
9455123451
18
5
NARESH
HISAR
9782918192
22
6
SWETA
RANCHI
9852617621
21
4
SURESH
DELHI
9156768971
18


Table 3 : STUDENT
 

ROLL_NO
NAME
ADDRESS
PHONE
AGE
1
RAM
DELHI
9455123451
18
5
NARESH
GURGAON
9652431543
18
6
SUJIT
ROHTAK
9156253131
20
4
SURESH
DELHI
9156768971
18

 

Selection operator (σ): Selection operator is used to select tuples from a relation based on some condition. Syntax:

σ (Cond)(Relation Name)

Extract students whose age is greater than 18 from STUDENT relation given in Table 1

σ (AGE>18)(STUDENT)

RESULT :
 

ROLL_NO
NAME
ADDRESS
PHONE
AGE
3
SUJIT
ROHTAK
9156253131
20

 

Projection Operator (∏): Projection operator is used to project particular columns from a relation. Syntax:

∏(Column 1,Column 2….Column n)(Relation Name)

Extract ROLL_NO and NAME from STUDENT relation given in Table 1

∏(ROLL_NO,NAME)(STUDENT)

RESULT :
 

ROLL_NO
NAME
1
RAM
2
RAMESH
3
SUJIT
4
SURESH


Note: If resultant relation after projection has duplicate rows, it will be removed. For Example:  ∏(ADDRESS)(STUDENT) will remove one duplicate row with value DELHI and return three rows.

Cross Product(X): Cross product is used to join two relations. For every row of Relation1, each row of Relation2 is concatenated. If Relation1 has m tuples and and Relation2 has n tuples, cross product of Relation1 and Relation2 will have m X n tuples. Syntax:

Relation1 X Relation2

To apply Cross Product on STUDENT relation given in Table 1 and STUDENT_SPORTS relation given in Table 2,

STUDENT X STUDENT_SPORTS

RESULT :
 

ROLL_NO
NAME
ADDRESS
PHONE
AGE
ROLL_NO
SPORTS
1
RAM
DELHI
9455123451
18
1
Badminton
1
RAM
DELHI
9455123451
18
2
Cricket
1
RAM
DELHI
9455123451
18
2
Badminton
1
RAM
DELHI
9455123451
18
4
Badminton
2
RAMESH
GURGAON
9652431543
18
1
Badminton
2
RAMESH
GURGAON
9652431543
18
2
Cricket
2
RAMESH
GURGAON
9652431543
18
2
Badminton
2
RAMESH
GURGAON
9652431543
18
4
Badminton
3
SUJIT
ROHTAK
9156253131
20
1
Badminton
3
SUJIT
ROHTAK
9156253131
20
2
Cricket
3
SUJIT
ROHTAK
9156253131
20
2
Badminton
3
SUJIT
ROHTAK
9156253131
20
4
Badminton
4
SURESH
DELHI
9156768971
18
1
Badminton
4
SURESH
DELHI
9156768971
18
2
Cricket
4
SURESH
DELHI
9156768971
18
2
Badminton
4
SURESH
DELHI
9156768971
18
4
Badminton


Union (U): Union on two relations R1 and R2 can only be computed if R1 and R2 are union compatible (These two relation should have same number of attributes and corresponding attributes in two relations have same domain) . Union operator when applied on two relations R1 and R2 will give a relation with tuples which are either in R1 or in R2. The tuples which are in both R1 and R2 will appear only once in result relation. Syntax:

Relation1 U Relation2

Find person who are either student or employee, we can use Union operator like:

STUDENT U EMPLOYEE

RESULT :
 

ROLL_NO
NAME
ADDRESS
PHONE
AGE
1
RAM
DELHI
9455123451
18
2
RAMESH
GURGAON
9652431543
18
3
SUJIT
ROHTAK
9156253131
20
4
SURESH
DELHI
9156768971
18
5
NARESH
HISAR
9782918192
22
6
SWETA
RANCHI
9852617621
21


Minus (-): Minus on two relations R1 and R2 can only be computed if R1 and R2 are union compatible. Minus operator when applied on two relations as R1-R2 will give a relation with tuples which are in R1 but not in R2. Syntax:

Relation1 - Relation2

Find person who are student but not employee, we can use minus operator like:

STUDENT - EMPLOYEE

RESULT :
 

ROLL_NO
NAME
ADDRESS
PHONE
AGE
2
RAMESH
GURGAON
9652431543
18
3
SUJIT
ROHTAK
9156253131
20

 

 

Rename(ρ): Rename operator is used to give another name to a relation. Syntax:

ρ(Relation2, Relation1)

To rename STUDENT relation to STUDENT1, we can use rename operator like:

ρ(STUDENT1, STUDENT)

 If you want to create a relation STUDENT_NAMES with ROLL_NO and NAME from STUDENT, it can be done using rename operator as:

ρ(STUDENT_NAMES, ∏(ROLL_NO, NAME)(STUDENT))

Extended Operators in Relational Algebra

Basic idea about  relational model and basic operators in Relational Algebra:

Relational model

Basicoperators in Relational Algebra

Extended operators are those operators which can be derived from basic operators.There are mainly three types of extended operators in Relational Algebra:

  • Join
  • Intersection
  • Divide 

The relations used to understand extended operators are STUDENT, STUDENT_SPORTS, ALL_SPORTS and EMPLOYEE which are shown in Table 1, Table 2, Table 3 and Table 4 respectively.

STUDENT
 

ROLL_NO
NAME
ADDRESS
PHONE
AGE
1
RAM
DELHI
9455123451
18
2
RAMESH
GURGAON
9652431543
18
3
SUJIT
ROHTAK
9156253131
20
4
SURESH
DELHI
9156768971
18

 

Table 1

STUDENT_SPORTS
 

ROLL_NO
SPORTS
1
Badminton
2
Cricket
2
Badminton
4
Badminton


Table 2

ALL_SPORTS

Relational Algebra | Database Management System (DBMS) - Computer Science Engineering (CSE)

Table 3

EMPLOYEE
 

EMP_NO
NAME
ADDRESS
PHONE
AGE
1
RAM
DELHI
9455123451
18
5
NARESH
HISAR
9782918192
22
6
SWETA
RANCHI
9852617621
21
4
SURESH
DELHI
9156768971
18

 

Table 4

Intersection (∩): Intersection on two relations R1 and R2 can only be computed if R1 and R2 are union compatible (These two relation should have same number of attributes and corresponding attributes in two relations have same domain). Intersection operator when applied on two relations as R1R2 will give a relation with tuples which are in R1 as well as R2. Syntax:

Relation1 ∩ Relation2

Example: Find a person who is student as well as employee-  STUDENT ∩ EMPLOYEE 

In terms of basic operators (union and minus) :

STUDENT ∩ EMPLOYEE = STUDENT + EMPLOYEE - (STUDENT U EMPLOYEE) 

RESULT :

Relational Algebra | Database Management System (DBMS) - Computer Science Engineering (CSE)

Conditional Join(⋈c): Conditional Join is used when you want to join two or more relation based on some conditions. Example: Select students whose ROLL_NO is greater than EMP_NO of employees

STUDENT⋈c STUDENT.ROLL_NO>EMPLOYEE.EMP_NOEMPLOYEE

In terms of basic operators (cross product and selection) :

σ (STUDENT.ROLL_NO>EMPLOYEE.EMP_NO)(STUDENT×EMPLOYEE)

RESULT :

Relational Algebra | Database Management System (DBMS) - Computer Science Engineering (CSE)

Equijoin: Equijoin is a special case of conditional join where only equality condition holds between a pair of attributes. As values of two attributes will be equal in result of equijoin, only one attribute will be appeared in result.

Example:Select students whose ROLL_NO is equal to EMP_NO of employees

STUDENT⋈STUDENT.ROLL_NO=EMPLOYEE.EMP_NOEMPLOYEE

In terms of basic operators (cross product, selection and projection) :

∏(STUDENT.ROLL_NO, STUDENT.NAME, STUDENT.ADDRESS, STUDENT.PHONE, STUDENT.AGE EMPLOYEE.NAME, EMPLOYEE.ADDRESS, EMPLOYEE.PHONE, EMPLOYEE>AGE)(σ (STUDENT.ROLL_NO=EMPLOYEE.EMP_NO) (STUDENT×EMPLOYEE))

RESULT :

Relational Algebra | Database Management System (DBMS) - Computer Science Engineering (CSE)

Natural Join(⋈): It is a special case of equijoin in which equality condition hold on all attributes which have same name in relations R and S (relations on which join operation is applied). While applying natural join on two relations, there is no need to write equality condition explicitly. Natural Join will also return the similar attributes only once as their value will be same in resulting relation.

Example: Select students whose ROLL_NO is equal to ROLL_NO of STUDENT_SPORTS as:

STUDENT⋈STUDENT_SPORTS

In terms of basic operators (cross product, selection and projection) :

∏(STUDENT.ROLL_NO, STUDENT.NAME, STUDENT.ADDRESS, STUDENT.PHONE, STUDENT.AGE STUDENT_SPORTS.SPORTS)(σ (STUDENT.ROLL_NO=STUDENT_SPORTS.ROLL_NO) (STUDENT×STUDENT_SPORTS))

RESULT :

Relational Algebra | Database Management System (DBMS) - Computer Science Engineering (CSE)

Natural Join is by default inner join because the tuples which does not satisfy the conditions of join does not appear in result set. e.g.; The tuple having ROLL_NO 3 in STUDENT does not match with any tuple in STUDENT_SPORTS, so it has not been a part of result set.

Left Outer Join (Relational Algebra | Database Management System (DBMS) - Computer Science Engineering (CSE)) When applying join on two relations R and S, some tuples of R or S does not appear in result set which does not satisfy the join conditions. But Left Outer Joins gives all tuples of R in the result set. The tuples of R which do not satisfy join condition will have values as NULL for attributes of S.

Example:Select students whose ROLL_NO is greater than EMP_NO of employees and details of other students as well

 STUDENT(⟕)STUDENT.ROLL_NO>EMPLOYEE.EMP_NOEMPLOYEE

RESULT

Relational Algebra | Database Management System (DBMS) - Computer Science Engineering (CSE)

Right Outer Join (Relational Algebra | Database Management System (DBMS) - Computer Science Engineering (CSE)) When applying join on two relations R and S, some tuples of R or S does not appear in result set which does not satisfy the join conditions. But Right Outer Joins gives all tuples of S in the result set. The tuples of S which do not satisfy join condition will have values as NULL for attributes of R.

Example: Select students whose ROLL_NO is greater than EMP_NO of employees and details of other Employees as well

STUDENT(⟖)STUDENT.ROLL_NO>EMPLOYEE.EMP_NOEMPLOYEE

RESULT :

Relational Algebra | Database Management System (DBMS) - Computer Science Engineering (CSE)

Full Outer Join (Relational Algebra | Database Management System (DBMS) - Computer Science Engineering (CSE)

 When applying join on two relations R and S, some tuples of R or S does not appear in result set which does not satisfy the join conditions. But Full Outer Joins gives all tuples of S and all tuples of R in the result set. The tuples of S which do not satisfy join condition will have values as NULL for attributes of R and vice versa.

Example: Select students whose ROLL_NO is greater than EMP_NO of employees and details of other Employees as well and other Students as well

STUDENT(⟗)STUDENT.ROLL_NO>EMPLOYEE.EMP_NOEMPLOYEE

RESULT :

Relational Algebra | Database Management System (DBMS) - Computer Science Engineering (CSE)

Division Operator (÷): Division operator A÷B can be applied if and only if:

  • Attributes of B is proper subset of Attributes of A.
  • The relation returned by division operator will have attributes = (All attributes of A – All Attributes of B)
  • The relation returned by division operator will return those tuples from relation A which are associated to every B’s tuple.

Consider the relation STUDENT_SPORTS and ALL_SPORTS given in Table 2 and Table 3 above.

To apply division operator as

STUDENT_SPORTS÷ ALL_SPORTS

  • The operation is valid as attributes in ALL_SPORTS is a proper subset of attributes in STUDENT_SPORTS.
  • The attributes in resulting relation will have attributes {ROLL_NO,SPORTS}-{SPORTS}=ROLL_NO
  • The tuples in resulting relation will have those ROLL_NO which are associated with all B’s tuple {Badminton, Cricket}. ROLL_NO 1 and 4 are associated to Badminton only. ROLL_NO 2 is associated to all tuples of B. So the resulting relation will be:

Relational Algebra | Database Management System (DBMS) - Computer Science Engineering (CSE)

The document Relational Algebra | Database Management System (DBMS) - Computer Science Engineering (CSE) is a part of the Computer Science Engineering (CSE) Course Database Management System (DBMS).
All you need of Computer Science Engineering (CSE) at this link: Computer Science Engineering (CSE)
2 videos|51 docs|31 tests

Up next

Normal Forms
Doc | 11 pages

FAQs on Relational Algebra - Database Management System (DBMS) - Computer Science Engineering (CSE)

1. What is relational algebra in computer science engineering (CSE)?
Relational algebra in computer science engineering is a theoretical framework used to describe and manipulate relational databases. It is a set of operations that can be applied to relations or tables, such as selection, projection, union, intersection, and join. These operations help in retrieving and transforming data from databases.
2. How is relational algebra used in computer science engineering (CSE)?
Relational algebra is used in computer science engineering to perform various operations on relational databases. It provides a formal and mathematical approach to query and manipulate data stored in tables. By using relational algebra, engineers can efficiently retrieve specific data, combine data from multiple tables, filter data based on certain conditions, and perform other database-related tasks.
3. What are the main operations in relational algebra for computer science engineering (CSE)?
The main operations in relational algebra for computer science engineering include: - Selection: Selects rows from a relation based on given conditions. - Projection: Selects specific columns from a relation. - Union: Combines two relations, removing duplicate tuples. - Intersection: Retrieves common tuples from two relations. - Join: Combines two relations based on a common attribute. - Difference: Retrieves tuples from one relation that do not exist in another relation. - Cartesian Product: Generates a new relation by combining every tuple from one relation with every tuple from another relation.
4. How does relational algebra relate to database management systems (DBMS) in computer science engineering (CSE)?
Relational algebra plays a crucial role in the design and implementation of database management systems (DBMS) in computer science engineering. DBMS uses relational algebra operations to process and manipulate data stored in tables. DBMS translates high-level SQL queries into relational algebra expressions to optimize and execute the queries efficiently. Relational algebra also helps in defining and enforcing data integrity constraints, ensuring consistency and accuracy in the database.
5. What are the benefits of using relational algebra in computer science engineering (CSE)?
Using relational algebra in computer science engineering offers several benefits, including: - Simplicity: The algebraic operations provide a concise and formal way to express database queries and transformations. - Portability: Relational algebra is independent of any specific database system, making it portable across different platforms and DBMS. - Optimized Query Execution: Relational algebra allows DBMS to optimize query execution by transforming high-level SQL queries into efficient algebraic expressions. - Data Integrity: Relational algebra helps in defining and enforcing integrity constraints, ensuring the accuracy and consistency of data in databases. - Standardization: Relational algebra provides a standardized approach to query and manipulate relational databases, facilitating interoperability among different database systems.
2 videos|51 docs|31 tests
Download as PDF

Up next

Normal Forms
Doc | 11 pages
Explore Courses for Computer Science Engineering (CSE) exam
Signup for Free!
Signup to see your scores go up within 7 days! Learn & Practice with 1000+ FREE Notes, Videos & Tests.
10M+ students study on EduRev
Download the FREE EduRev App
Track your progress, build streaks, highlight & save important lessons and more!
Related Searches

Viva Questions

,

Sample Paper

,

ppt

,

Semester Notes

,

Summary

,

past year papers

,

video lectures

,

Free

,

Extra Questions

,

Objective type Questions

,

shortcuts and tricks

,

study material

,

Exam

,

pdf

,

Relational Algebra | Database Management System (DBMS) - Computer Science Engineering (CSE)

,

practice quizzes

,

Previous Year Questions with Solutions

,

Important questions

,

mock tests for examination

,

Relational Algebra | Database Management System (DBMS) - Computer Science Engineering (CSE)

,

Relational Algebra | Database Management System (DBMS) - Computer Science Engineering (CSE)

,

MCQs

;