Advanced Database Management System - Tutorials and Notes: cartesian product in relational algebra

Tuesday, 15 August 2017

cartesian product in relational algebra

Cartesian product operation in relational algebra, binary operations in relational model, cross join operation in relational algebra, Binary cartesian product operation to perform cross join in relational algebra


Cartesian Product (X) in Relational Algebra


Operation
CARTESIAN PRODUCT (Cross Join)
Type of operation
Binary
Syntax
R1 X R2


Here, R1 and R2 can be any one as follows;

  • relations like, for example, STUDENT X SUB_REGD
  • expressions like, age>15 (student)) X (πsubject (sub_regd))
Function
Produces a relation that has the attributes of R1 and R2 and includes as tuples all possible combinations of tuples from R1 and R2.
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. For example, if R1 has 5 records and R2 has 5 records then R(A1, A2, …, An, B1, B2, …, Bn) will have 25 records.
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: σage>15 (student X sub_regd)
SQL: SELECT * FROM student, sub_regd WHERE age>15;
Result: Joins the two relations student and sub_regd and returns all students with age greater than 15.
σage>15 (student X sub_regd)
Regno
Name
Age
Phone
SRegno
Subject

BIT023
Selvi
17
7894562310
BIT001
Physics
BIT023
Selvi
17
7894562310
BIT023
Computer
BIT023
Selvi
17
7894562310
BIT023
OS


Example 2
RA: Π regno, name, subject (σage>15 (student X sub_regd))
SQL: SELECT regno, name, subject FROM student, sub_regd WHERE age>15;
Result: Produces a new temporary relation with regno, name and subject attributes of all students whose age is greater than 15 by joining student with sub_regd table.
Π regno, name, subject (σage>15 (student X sub_regd))
Regno
Name
Subject

BIT023
Selvi
Physics
BIT023
Selvi
Computer
BIT023
Selvi
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