Advanced Database Management System - Tutorials and Notes: Equi-join in relational algebra

Search Engine

Please visit, subscribe and share 10 Minutes Lectures in Computer Science

Saturday, 26 August 2017

Equi-join in relational algebra

Equi-join in relational algebra, equi-join in relational model, equi-join relational algebra query and its equivalent SQL queries, equi-join examples

Equi-Join in Relational Algebra

Operation
EQUI-JOIN
Type of operation
Binary
Syntax
R1 <join condition> R2
Here, the <join condition> is of the form R1.a = R2.b.
Equivalent expression
R1 <join condition> R2 = σ<join condition>(R1 X R2)
Function
Produces all combinations of tuples from R1 and R2 that satisfy the join condition. This join condition involves attributes from both relations such as follows;
R1.a = R2.b
If R1(A1, A2, …, An) is joined with R2(B1, B2, …, Bn) using the condition R1.A1 = R2.B1, then it produces a new temporary relation R(A1, A2, …, An, B1, B2, …, Bn) and every tuple (record) of R1 is combined with every tuple of R2 where R2.B1 value is same as the R1.A1 value. The result consists of all records that satisfy the join condition.
Example 1

STUDENT
Regno
Name
Age
Phone
2 records
BIT001
Ram
15
8652398452
BIT023
Selvi
17
7894562310

SUB_REGD
SRegno
Subject
3 records
BIT001
Physics
BIT023
Computer
BIT023
OS

STUDENT X SUB_REGD
Regno
Name
Age
Phone
SRegno
Subject
2 X 3 = 6 records
BIT001
Ram
15
8652398452
BIT001
Physics
BIT001
Ram
15
8652398452
BIT023
Computer
BIT001
Ram
15
8652398452
BIT023
OS
BIT023
Selvi
17
7894562310
BIT001
Physics
BIT023
Selvi
17
7894562310
BIT023
Computer
BIT023
Selvi
17
7894562310
BIT023
OS


RA: σregno = sregno (student X sub_regd)
SQL: SELECT * FROM student, sub_regd WHERE regno = sregno;
Result: Joins the two relations student and sub_regd on regno and sregno attributes. If the values of the join attributes are same, only those records are combined as shown below.
σregno = sregno (student X sub_regd)
Regno
Name
Age
Phone
SRegno
Subject
BIT001
Ram
15
8652398452
BIT001
Physics
BIT023
Selvi
17
7894562310
BIT023
Computer
BIT023
Selvi
17
7894562310
BIT023
OS
Example 2
RA: σregno = sregno(Π regno, name, phone (student) X sub_regd)
SQL: SELECT regno, name, phone FROM student, sub_regd WHERE regno = sregno;
Result: Produces a new temporary relation with regno, name, phone, sregno and subject attributes of all students. The records that satisfy the join condition regno = sregno are included in the final result.
σregno = sregno (Π regno, name, phone (student) X sub_regd)
Regno
Name
Phone
SRegnoubject
Subject
BIT001
Ram
8652398452
BIT001
Physics
BIT023
Selvi
7894562310
BIT023
Computer
BIT023
Selvi
7894562310
BIT023
OS




***********




No comments:

Post a comment

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents