Thursday, August 17, 2017

theta join in relational algebra

Theta join in relational algebra, theta join in relational model, theta join relational algebra query and its equivalent SQL queries, binary theta join operation in relational algebra

Theta Join in Relational algebra


Operation
THETA JOIN
Type of operation
Binary
Syntax
R1 <join condition> R2
Here, the <join condition> is of the form R1.a θ R2.b, and θ is any of the comparison operators {=, <, <=, >, >=, ≠}
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) 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. 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


Example 3

AGE_GROUP
Min_Age
Max_Age
Age_Group
18
30
18-30
31
60
31-60
61
100
Above 60

CUSTOMER
Customer_Name
Age
Rahul   
35
Meera
25
Steve
62

RA: πcustomer_name, age_group(σage >= min_age ^ age <= max_age (customer X age_group))
SQL: SELECT customer_name, age_group FROM customer, age_group WHERE age between min_age and max_age;
Result: Joins two tables and accepts the records that satisfy the condition given in the WHERE clause.
πcustomer_name, age_group(σage >= min_age ^ age <= max_age (customer X age_group))
Customer_Name
Age_Group
Rahul   
31-60
Meera
18-30
Steve
Above 60

Note: THETA join uses any comparison operator in joining two tables.



***************




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

data recovery