0:00	foreign
0:07	welcome back in this presentation we are
0:10	going to focus on the next relational
0:12	algebra operation the Cartesian product
0:16	and we know basically there are six
0:18	fundamental relational algebra
0:19
operations the select operation the
0:22
project operation Union say difference
0:25
Cartesian product and rename operation
0:29
and we have already seen about select
0:31
project Union and set difference and in
0:34
this presentation we are going to focus
0:36
on the Cartesian product what is this
0:39
Cartesian product as the name itself
0:41
says that it's going to do the product
0:43
operation between two relations so
0:45
Cartesian product Associates every Tuple
0:48
of relation one with every Tuple of
0:52
relation two it's going to associate
0:54
every Tuple of relation one with every
0:57
Tuple of relation two say for example if
1:00
relation 1 contains five tuples and
1:02
relation 2 contains seven tuples so the
1:05
Cartesian product between these two
1:07
relations will give 35 tuples that is R1
1:11
multiplied by R2 or simply R1 Cartesian
1:15
product R2 don't worry about the details
1:18
of this Cartesian product now once we
1:20
see an example you will be able to
1:21
understand things clearly for now just
1:23
understand that every Tuple of R1 is
1:26
going to be associated with every Tuple
1:28
of relation to or simply R2 and with
1:31
this explanation you would have guessed
1:33
that this is also a binary operation
1:35
because this Cartesian product works on
1:37
two relations as the input R1 and R2 and
1:41
what is the symbol that is used to
1:42
denote this Cartesian product
1:44
Cartesian product operation is denoted
1:47
by the cross symbol in simple terms we
1:50
can say R1 Cartesian product or cross R2
1:53
will give the result as all possible
1:56
padding say for example if relation 1
1:58
has 4 tuples and relation 2 has 6
2:01
doubles then all the four tuples in
2:03
relation one will be associated with all
2:06
six tuples in relation to so obviously 4
2:09
cross 6 which is equal to 24 records
2:12
will be there in the output and that is
2:14
why it is quoted that R1 cross R2 gives
2:17
the result as the all possible pairing
2:19
among all the tuples in the relations
2:21
involved now there is a problem here
2:24
let's say this relation 1 contains
2:26
customer name as one of the attributes
2:28
and relation 2 also has one of the
2:30
attributes which is customer name but in
2:33
the all possible pairing that is in the
2:34
Cartesian product output we will have
2:37
the relation with two customer names
2:39
isn't it One customer name is from this
2:42
relation and the other customer name
2:44
attribute is from this relation will it
2:46
give ambiguity yes that's why one of the
2:49
problems in Cartesian product because
2:51
the same attribute may appear in both R1
2:54
and R2 in that case the output relation
2:57
will have both of these attributes let's
3:00
take some real time example let's say
3:02
there is a depository relation and there
3:04
is a borrower relation and what we are
3:06
going to do is the depositor cross
3:09
borrower and the output is going to be
3:11
the all possible pairing and the result
3:13
I am going to store it in the relation
3:15
are now what's the specialty about this
3:18
Cartesian product this Cartesian product
3:20
is differing from join operations which
3:23
we are going to see in the additional
3:24
relational algebra operations in the
3:26
coming lectures in what way it is
3:28
differing in the join operations there
3:31
should be some joint conditions say for
3:33
example I want to join relation 1 and
3:35
relation 2. in that case I need at least
3:38
one common attribute in order to perform
3:40
the join operation let's say there is a
3:43
situation where we don't have any common
3:45
attribute in both the relations let's
3:47
say depositive relation attains customer
3:49
name and account number let's say
3:52
borrower relation contains low number
3:54
and loan value in this case there are no
3:57
common attributes here Cartesian product
3:59
will work because it's going to perform
4:01
only all possible pairings in simple
4:03
terms this Cartesian product will do all
4:05
possible pairing without checking for
4:07
any conditions whereas join require a
4:10
joint condition anyway don't worry about
4:12
the join operations now in the coming
4:14
lectures we are going to exclusively
4:16
focus on join operations with relevant
4:18
examples for now just understand
4:20
Cartesian product simply performs all
4:23
possible pairings among the relations so
4:25
let's see an example now so in this
4:28
example what we are going to see is we
4:30
are going to perform the depositor
4:32
Cartesian product borrower and let's
4:34
take both the relations now relation one
4:36
is depositor which contains two
4:38
attributes customer name and account
4:40
number and we have two records here Tom
4:43
101 and rows 304 these two tuples are
4:47
there in depositor relation and let's
4:49
take the other relation the moreover
4:51
relation which also contains two
4:52
attributes customer name and law number
4:55
in this case customer John is having the
4:57
low number l201 or 201 Smith with the
5:01
low number 658 rose with the loan number
5:03
254 and Jack with the loan number 547
5:07
now if you Analyze This there is a
5:09
common attribute here customer name and
5:11
customer name in both the relations but
5:13
in order to perform this depositor
5:15
crossbar over it need not be the case
5:17
that there should be at least one common
5:19
attribute in both the relations
5:21
Cartesian product will do all possible
5:23
pairing even if there is no common
5:25
attribute between the relations but in
5:28
this example I have taken a common
5:29
attribute but please be noted that
5:31
Cartesian product works even if there
5:34
are no common attributes pause this
5:36
video for a while and please answer this
5:38
question now how many tuples will be
5:40
there in the result when we perform the
5:43
Cartesian product between depositor and
5:45
borrower
5:47
I hope you are done so depositor
5:49
contains two tuples and borrower
5:51
contains four tuples so two Cross Four
5:54
so obviously it's going to be eight
5:56
tuples in the output relation and this
5:59
depositor crossbar over operation will
6:01
give the temporary relation without any
6:03
name which contains four attributes
6:06
customer name and account number from
6:08
this depositor relation customer name
6:10
and account number and from borrower
6:12
relation customer name and loan number
6:14
can you see here this customer name and
6:16
loan number from borrower relation now
6:18
let's analyze the output when we take
6:21
this first Tuple Tom a101 can you see
6:24
here Tom a101 is associated with all
6:28
these four tuples in the other relation
6:29
can you see here Tom a101 with John with
6:33
Smith with rose with Jack can you see
6:36
here Tom with John with Smith with rose
6:39
with Jack all these four records are
6:42
Tuple one in this relation
6:44
and taking the second relation rows a304
6:48
rows a304 is also associated with all
6:51
other tuples in the other relation can
6:53
you see here rows a304 is associated
6:56
with John Smith Rose and Jack where John
6:59
Smith Rose and Jack are there in the
7:02
other relation and this is what we call
7:04
it as all possible parent now why do we
7:07
need this there are situations where we
7:10
need to do this Cartesian product in
7:11
order to fetch some records in that case
7:14
Cartesian products will be handy and
7:16
useful after doing Cartesian product we
7:19
can apply select condition to retrieve
7:21
the appropriate records what select
7:23
condition can be applied this customer
7:25
name and this customer name should be
7:26
equal in this case can you see here this
7:29
storm is not matching here at all but
7:31
Rose is matching here so after doing
7:33
Cartesian product we can confirm that
7:35
Rose who is having the account number
7:37
a304 might have availed the loan l254
7:41
this is Possible only when we do
7:43
Cartesian product in this case since
7:46
customer name is matching in both the
7:48
relations we can tell that this record
7:50
Rose and this record rows may be
7:52
associated with each other and this
7:54
Cartesian product will do the operation
7:55
even if there are no common attributes
7:57
before we proceed with the real world
7:59
example I request you to pause this
8:01
video for a while just Analyze This
8:04
output relation and tell me is there any
8:07
ambiguity in this real
8:10
I hope you are done yes there is an
8:13
ambiguity in this relation let's say I'm
8:15
renaming this output relation as product
8:17
say when I address this product relation
8:19
by referring the customer name there are
8:22
two customer name attributes here one is
8:24
this and the other one is this and both
8:26
are different can you see here it
8:28
contains only Tom and rows whereas it
8:30
contains different values in that case
8:32
how to address this customer name and
8:34
this customer name if there are no
8:37
common attributes it's easy there is no
8:39
ambiguity but when there are common
8:41
attributes and when we perform Cartesian
8:44
product obviously it leads to ambiguity
8:46
now how to resolve this that's what we
8:49
are going to see now say for example
8:51
when we perform Cartesian product
8:52
between depositor and borrower and the
8:55
output is going to be a temporary
8:56
relation and let's assume this output of
8:59
this Cartesian product I am renaming it
9:01
to R let's say this R is a relation name
9:04
and in this case this output relation R
9:07
will contain four attributes what are
9:09
they customer name from the depositor
9:11
relation and account number from the
9:13
depositor relation and customer name
9:15
from borrower relation loan number from
9:17
borrower relation now there are four
9:19
attributes in the output relation I told
9:22
you that there is an ambiguity that
9:23
customer name is appearing two times in
9:25
the output table but this ambiguity can
9:28
be resolved by referring to the table
9:29
name the original table name this
9:32
customer name is from depositor so I can
9:34
address it as depositor Dot customer
9:36
name and this customer name is from
9:38
moreover relation so I can address it as
9:41
borrower.customer name by this way we
9:44
can resolve the conflicts or the
9:45
ambiguity and there is no ambiguity for
9:48
account number and loan number but still
9:50
we are referring it with the table name
9:52
and it need not be the case we can even
9:54
refer without the table name because
9:56
there is no ambiguity so simply we can
9:58
also represent like deposited or
10:00
customer name comma account number comma
10:03
powerover Dot customer name comma loan
10:05
number because the ambiguity is with the
10:07
customer name only but not with the
10:09
other attributes we are done with the
10:12
basics of the Cartesian product
10:13
operation let's now see an example the
10:16
example that we are going to see now is
10:18
the same University database that we
10:20
have been seeing in the last few
10:22
presentations and I'm just going to
10:24
present the schema here the instructor
10:26
course Department section teachers
10:29
student advisor takes classroom and time
10:33
slot relation I request you to pause
10:35
this video for a while and understand
10:37
the schema
10:39
I hope you are done now we are going to
10:42
solve a question in the Cartesian
10:44
product using this University database
10:46
schema and the question is find the
10:48
names of all instructors in the physics
10:51
department together with the course IDs
10:54
of all courses they taught so we are
10:56
required to find the names of all the
10:59
instructors in the physics department
11:01
and not just the names of all
11:03
instructors in physics department
11:04
together with the course IDs of all the
11:08
courses they have taught I'll just go to
11:09
the schema now
11:11
if we are going to retrieve only the
11:13
instructor's ID it's so simple to
11:15
retrieve because we are going to
11:16
retrieve all the instructor's ID from
11:18
the physics department so we can simply
11:20
use project and inside that project we
11:22
can go for select where the department
11:24
name is physics we have already seen
11:26
similar examples in select operation
11:28
itself but what we are required to find
11:30
we are required to find the instructor's
11:33
ID along with the course ID that they
11:36
have thought so this instructor ID along
11:38
with the course ID if that's the case we
11:41
can directly use this relation teaches
11:43
and we can just project only ID and
11:45
course number isn't it but why do we
11:47
need Cartesian product here because we
11:49
are not going to retrieve all
11:51
instructors ID along with all course IDs
11:53
they have thought we want only the IDS
11:56
of instructors along with the courses of
11:58
the instructors in the physics
11:59
department where the department name is
12:02
residing in another relation and
12:04
Department level information is not
12:06
there in this relation so obviously we
12:09
need to join two tables with some
12:10
condition if there is no common
12:12
attribute or join condition we can go
12:15
for Cartesian product and then we can
12:17
retrieve the records accordingly no
12:19
worries we will proceed with the
12:20
solution then you will be able to
12:22
understand things clearly I am going to
12:24
the question slide now
12:26
so here is the question find the names
12:28
of all instructors in the physics
12:30
department together with the course IDs
12:33
of all courses they taught if you are
12:36
proficient working with the relational
12:38
algebra operations and expressions you
12:40
can directly answer this but here I am
12:43
gonna solve this with some real world
12:44
examples so here is the solution for
12:48
this if you are pretty confident you can
12:50
solve like this directly but what I am
12:52
gonna do now is I'm gonna do a
12:55
step-by-step approach so what's the
12:57
question we are required to find the
12:58
names of all instructors in the physics
13:01
department so remember its physics
13:03
department and the instructors in the
13:06
physics department together with all the
13:09
course IDs of courses they thought so
13:11
all the instructors in the physics
13:13
department along with the course IDs
13:15
they taught so what are the two tables
13:17
that we want here the instructor
13:19
relation because we need to take the
13:21
names of all the instructors from the
13:23
instructor relation and can we take the
13:25
sections relation no section contains
13:27
only the course relation information we
13:30
want the instructor who handled that
13:32
particular course to that particular
13:33
section so we are preferring the
13:35
teacher's relation so we are first doing
13:38
the Cartesian product between instructor
13:40
and teachers don't worry about the
13:42
solution now we will come back to this
13:44
slide again for now we will take two
13:46
relations instructor and teachers with
13:50
some sample records here is the
13:52
instructor relation and here is the
13:54
teacher's relation
13:56
in the instructor relation we have four
13:58
attributes ID name Department name and
14:01
salary and we have two tuples here I am
14:04
not taking more tuples because the
14:06
Cartesian product output will be
14:07
enormous we know if relation 1 has 5
14:10
tuples and relation 2 has 10 tuples
14:12
obviously the output will be 5
14:14
multiplied by 10 50 tuples in the
14:17
Cartesian product output so to save time
14:19
and space I am just taking only two
14:22
tuples in the instructor relation coming
14:24
to the teachers relation we have four
14:27
tuples 10101 20202
14:31
78787-12345 so I request you to pause
14:34
this video for a while and think about
14:36
the instructor's ID that are matching in
14:39
both the relations
14:41
I hope you are done if you see here in
14:44
this case 10101 or 10101 is John and
14:49
here we have one zero one zero one so it
14:52
means this instructor John who is
14:55
working in the biology Department
14:56
drawing the salary of 65 000 who has
15:00
actually handled this course by your 108
15:03
for the section id1 in the semester
15:06
summer in the year 2009 and coming to
15:10
78787 Raj Raj has handled physics
15:14
because 78787 is the ID of Raj so he has
15:18
handled this particular course in the
15:19
section ID to in fall 2011 and what
15:23
about 2022 and 12345 we have not taken
15:27
those examples here so now what's the
15:29
question we want to list the names of
15:31
all instructors who are working in the
15:34
physics department along with the
15:36
courses they taught it's clear that from
15:38
this table Raj is the person who is
15:40
working in the physics department man
15:41
who has handled this particular subject
15:43
physics 101 so by looking at this output
15:46
we can easily say that this Tuple Raj is
15:49
matching with this entry because 78787
15:52
is matching here because this ID of
15:54
instructor is matching with this ID of
15:57
teachers we can say the output easily
15:59
here because we have only limited
16:01
records here think a real time
16:03
University database which contains
16:05
thousands and thousands of information
16:06
in that case we can't easily retrieve
16:09
this we need to do Cartesian product
16:11
between instructor and teachers imagine
16:14
we don't know whether this ID and this
16:16
ID are matching with each other so what
16:19
we are simply going to do is we are
16:21
going to do the Cartesian product I mean
16:23
instructor cross teaches and how many
16:26
tuples will be there in the output
16:28
it's obviously eight because here we
16:31
have two and here we have four so two
16:34
Cross Four is eight so the instructor
16:36
cross teachers will have eight tuples
16:38
can you see here one two three four five
16:42
six seven and eight I'll go back to the
16:45
previous slide
16:47
can you see here this John will be added
16:49
with all these four tuples similarly
16:51
Raj's information will be added with all
16:54
these four tuples so in the output we
16:56
will have one two three four five six
16:59
seven eight and nine columns so the
17:02
output obviously will be containing nine
17:04
columns can you see here one two three
17:07
four five six seven eight and nine there
17:09
is an ambiguity here what is that this
17:12
is also ID this is also ID but this ID
17:15
is from which relation instructor
17:16
relation and this ID is from teachers
17:19
relation to avoid the ambiguity I have
17:22
just made it as instructor dot ID and
17:24
this is Teachers dot ID can you see here
17:26
this John's information is added with
17:28
all four tuples in the other side the
17:31
teacher's relation and coming to Raj
17:33
Raj's information is just added or
17:36
joined with all other four tuples in the
17:38
other relation and it's very clear that
17:41
this is not the exact output that we
17:43
want this is merely the instructor cross
17:46
stitches and what is the actual question
17:48
the question is we want to list the
17:50
names of all instructors who is working
17:52
for physics department along with the
17:54
courses they have taught right so in
17:57
this case 10101 is actually John and
18:00
this is instructor ID and this is
18:02
Teacher's ID so this is matching but in
18:05
this case 10101 John is not this 20202
18:09
so this is an invalid entry and this is
18:12
an invalid entry because this
18:13
teachers.id is not matching with
18:15
instructors this ID at the same time
18:18
when we see the output we are getting
18:20
the output of both the department names
18:22
biology and physics and what is asked in
18:25
the question we want the instructors in
18:27
the physics department from this output
18:29
what I am going to do is I am going to
18:31
apply a filter where I am going to
18:34
retrieve all the tuples that is having
18:36
the department name as physics because
18:38
in the question we are required to find
18:40
the list of instructors who is working
18:43
for physics department along with the
18:45
courses they thought so just see this
18:47
portion now so from this output
18:50
instructor cross teacher and just
18:52
retrieving or selecting all the tuples
18:54
where the department name is equal to
18:56
physics so what are all the tuples we
18:58
won't be getting in the output the
19:00
tuples where the department name is
19:01
biology will not be there in the output
19:04
and we will have only these four output
19:06
so the output of this relational algebra
19:09
expression instructor cross teachers
19:11
will have eight tuples now we have only
19:14
four tuples because we have selected
19:16
only the tuples that are having the
19:18
department name as physics is this the
19:21
output of the question that we are
19:22
dealing with no we want only the names
19:25
of the instructors along with the
19:27
courses they taught but in this case
19:29
there are some invalid entries can you
19:30
see here Raj is having the ID the
19:33
instructor ID 78787 this is actually
19:36
concatenated with an invalid entry one
19:39
zero one zero one this is invalid and
19:42
this is also invalid and this is valid
19:45
and this is invalid
19:47
so what I am going to do now is I'm
19:49
going to take this output right this is
19:51
the output for this expression so I will
19:54
take this into a new slide so here is
19:57
the output for this relational algebra
19:59
expression so what are all the invalid
20:01
entries 1 2 3 why because the
20:05
instructor's ID is not matching with the
20:07
teacher's ID right only this third row
20:10
is a valid entry how to filter this or
20:12
how to select this it's so simple this
20:15
is the output for this particular
20:17
expression right we want only this
20:20
particular Tuple am I right so what we
20:22
are going to do now is we are going to
20:24
again use a select operation and the
20:27
condition is where the instructor's ID
20:29
is equal to the teacher's ID just see
20:32
the expression here this is the
20:34
relational algebra expression that gives
20:36
this whole output from this we are going
20:39
to select all the tuples where the
20:41
instructor's ID which is this ID is
20:44
matching with the teacher's ID which is
20:46
this ID so in this case only this
20:48
particular row will be selected and
20:50
other rows are not selected because the
20:53
condition is violated so the output for
20:55
this particular expression will contain
20:57
only one Tuple in this example which is
21:00
this
21:02
did we get the output no what we are
21:05
asked to find we are asked to find the
21:07
names of all instructors along with the
21:09
course ID not other details right this
21:11
relational algebra expression will give
21:13
a temporary relation which is this
21:15
intermediate relation which contains all
21:17
attributes right but we want only name
21:20
and course ID because that's what the
21:22
question is so what we are going to do
21:24
is from this just project only two
21:27
columns what are they name and course ID
21:30
so I'm just placing this whole
21:32
expression here and we are going to
21:34
project only two columns what are they
21:36
name and course ID so when we do that we
21:40
will get the output as only name and
21:42
course ID only Raj is the instructor who
21:45
is working in physics department along
21:48
with the course ID the course ID here is
21:50
physics 101 which is this now let me go
21:53
to the solution part now and the
21:55
solution goes like this first what we
21:57
did the instructor cross stitches and
22:00
from this we selected all the apples
22:02
that are belonging to physics department
22:04
and then we selected all the tuples
22:06
where the instructor's ID is matching
22:08
with the teacher's ID this contains all
22:11
the attributes but we are required to
22:13
know only the names of the instructors
22:15
along with the course ID right so from
22:17
this output just give this entire
22:19
expression here and the outer expression
22:22
is going to be project where we are
22:24
going to project only name and course ID
22:27
and we can also solve this in other ways
22:30
this is the alternate way of solving the
22:32
same question and what is the difference
22:34
between this and this expression is here
22:37
we have first performed instructor cross
22:39
teachers and then we selected the
22:41
department name is equal to physics and
22:43
then we match the instructor ID and then
22:45
we projected the name and course ID but
22:48
here what we are going to do is first we
22:50
are going to take the instructor
22:51
relation and we are going to select all
22:54
the tuples that are belonging to physics
22:56
department first so Cartesian product is
22:58
not first applied here directly in the
23:00
previous case Cartesian product were
23:02
applied on both the relations first but
23:04
here we are first selecting all the
23:07
tuples that are matching to the
23:08
department name physics and then this
23:11
output is done with the Cartesian
23:13
product operation with teachers and this
23:16
output will not be 100 accurate so we
23:18
are just matching the instructor IDs
23:20
here after this we are projecting only
23:22
name and course ID so there is a
23:25
homework question for you the question
23:27
is just take the same examples I'll show
23:30
the examples now here are these two
23:33
relations instructor and teachers for
23:36
these two relations we got this output
23:38
what is that output this is the output
23:41
for which expression this expression
23:43
right so I want you to give a try using
23:47
this expression so for the same example
23:49
first do this part and then perform the
23:52
Cartesian product and then select with
23:55
this and then project and compare
23:57
whether the results of these two
23:59
expressions are matching with each other
24:02
and also just recollect I told you there
24:05
are multiple ways to solve a problem you
24:07
can choose any approach but please be
24:10
ensured that the logic that we are
24:12
applying should be correct and that's it
24:14
guys what we have seen in this
24:15
presentation the Cartesian product
24:17
operation so in the next presentation we
24:20	will focus on the next fundamental
24:22	relational algebra operation the rename
24:24	operation I'll see you in the next
24:26	presentation and thank you for watching
24:29	[Music]
24:29	[Applause]
24:31	[Music]