0:00
foreign
0:07
welcome back in this presentation we are
0:10
going to focus on the relational algebra
0:12
the set intersection operation we have
0:15
already seen about all the six
0:17
fundamental relational algebra
0:19
operations in the previous lectures the
0:21
select operation the project Union set
0:25
difference Cartesian product and the
0:28
rename operations now we are going to
0:30
turn our attention towards knowing the
0:32
additional operations I mean the
0:34
additional relational algebra operations
0:36
what are the operations that we are
0:38
going to see in the coming lectures the
0:41
set intersection operation the
0:43
assignment operation the natural join
0:46
then division operation and then the
0:49
outer join in outer join we are going to
0:51
focus on the left outer join the right
0:54
outer join and the full outer join
0:56
operation and in this presentation we
0:59
are going to focus on the first
1:00
additional operation the set
1:02
intersection operation let's see the
1:05
basics of the set intersection operation
1:07
now we know basically any relation is a
1:11
set and that is why it is named as
1:13
relational algebra because it has a
1:15
close correspondence with the
1:16
mathematical concept the set theory so
1:19
when we say that any relation is a set
1:22
then obviously all the properties that
1:24
are applicable for the set that is also
1:27
applicable for relations and obviously
1:30
in set theory we have the set
1:32
intersection operation likewise this set
1:34
intersection is applicable in relational
1:37
algebra also and that's why the second
1:39
Point says this set intersection is
1:41
similar to the same intersection
1:43
operation in set theory and what type of
1:46
operator is this whether it is a unary
1:48
operator or a binary operator set
1:50
intersection is going to combine the
1:52
output of two different sets or two
1:54
different relations then obviously it is
1:57
a binary operator which takes two
1:59
operands as input and produces one
2:02
output here the operands are two
2:04
different relations
2:06
and what's the basics of the set
2:08
intersection operation actually this set
2:10
intersection is a set of all objects
2:13
that are a member of both A and B say if
2:17
we have two relations A and B then the
2:20
output of the set intersection operation
2:22
is a intersection B which contains the
2:25
members that are belonging to both the
2:28
set or both the relations A and B anyway
2:31
when we see an example then it will be
2:34
easy for you to understand
2:35
and this set intersection is denoted by
2:38
the inverted U which is this symbol
2:41
let's see the syntax with an example now
2:43
so here what we are doing is we are
2:46
taking relation one as one of the
2:48
operands and we are projecting a column
2:51
or a set of columns here so what we are
2:53
doing here is we are simply projecting a
2:56
column from relation one and similarly
2:59
we are projecting another column or
3:01
another set of columns from relation to
3:03
and we will be having a temporary
3:05
relation here here also we will be
3:07
having a temporary relation and what we
3:10
are doing we are doing the set
3:11
intersection operation between these two
3:14
temporary relations now you may be
3:16
asking me a question why don't we
3:17
directly use relation one set
3:19
intersection relation to this is
3:21
Possible only if the two conditions are
3:24
met we have already seen about those two
3:26
conditions in the previous lectures when
3:28
we have dealt about the union and the
3:30
set difference operation what are they
3:32
they are first relation 1 and relation 2
3:36
must be of same arity it means whatever
3:38
the number of columns in relation one we
3:40
have the same number of columns we need
3:43
to have in relation to at the same time
3:45
the domain of the ith attribute of
3:47
relation 1 is same as that of the ith
3:50
attribute of relation 2. so if these two
3:53
conditions are met then we can directly
3:55
apply set intersection but there are
3:58
situations where we need to retrieve
3:59
certain information and then perform set
4:01
intersection so here what we have done
4:03
we have simply projected a column or a
4:06
group of columns from relation one set
4:08
intersection again projection of column
4:11
or a set of columns from relation to
4:13
then we can easily perform set
4:15
intersection I hope the theoretical
4:17
Basics are clear to you now let's see
4:19
the basic working of the set
4:21
intersection operation then we will
4:23
proceed with the example to understand
4:25
things I am bringing in the first set or
4:27
the first relation R which contains
4:30
alphabets A B C D E and F so this is
4:35
input number one and let's take another
4:38
input which is s where it contains some
4:40
group of characters letters a b dollar e
4:45
f 1 and hash symbol now we have two
4:48
inputs R and S if we want to perform our
4:52
intersection s then the condition should
4:54
be the rity should be same on both the
4:57
relations here are the relation R which
4:59
is containing only one column similarly
5:02
s is also containing one column and
5:04
coming to Second condition the domain of
5:06
this column is same as that of the
5:08
domain of this column hence we can apply
5:11
the set difference operation where we
5:13
are going to perform R intersection s
5:15
and here is the output of our
5:18
intersection s how did we get this
5:20
output
5:21
what is our intersection is it is the
5:24
set of all objects or elements that are
5:26
the member of r as well as s the
5:29
elements in the output will be belonging
5:31
to both R and S when we take r as an
5:35
example we have a here we also have a
5:38
here and hence a is there in the output
5:41
coming to the next value b b is here in
5:44
r as well as in s so B will also be
5:47
there in R intersection s output and
5:50
coming to c c is there in R but not in s
5:54
so c will not be there in the output and
5:57
that's why we are not able to see the
5:59
element C in the output and coming to
6:02
the element d d is there in R but not in
6:05
s and that's why we are not able to see
6:08
D in the output coming to E and F both E
6:11
and F are there in R as well as s and
6:15
hence we can see E and F are there in
6:18
the output so what do we understand from
6:20
this is our intersection s is a set of
6:23
all objects that are the members of both
6:25
A and B in this case the output R
6:29
intersection s will contain the set of
6:31
all elements that are the member of both
6:34
r and s
6:37
with this basic understanding let's move
6:39
on to example number one here is the
6:42
example we are required to find the
6:45
names of all customers who have
6:47
deposited money and also a wild loan
6:51
I request you to pause this video for a
6:53
while and think what is expected in this
6:56
question
6:58
I hope you are done so what we are
7:00
required to find is we are required to
7:03
find the names of all customers
7:05
associated with the bank these customers
7:08
should have deposited the money as well
7:11
as they should have availed loan
7:13
so what do we understand from here is we
7:16
have two relations one is the depositor
7:18
relation another one is the loan related
7:21
borrower relation and we are required to
7:23
take the output of all customer names
7:26
who has deposited money in the bank as
7:29
well as he or she should have availed
7:31
the loan so what we can do can we
7:33
directly perform depositor intersection
7:35
borrower no because the depositor
7:38
relation may have the number of
7:40
attributes which may not exactly
7:42
matching with the other relation which
7:43
is the borrow relation so we cannot
7:46
directly do depositor intersection
7:48
borrower but what we are required to do
7:50
is we are required to list the names of
7:53
all customers from the depositor account
7:55
or from the depositor relation and then
7:58
intersection list all the names of the
8:01
customers from the borrower relation so
8:03
the solution for this question is we are
8:06
just projecting only customer name from
8:08
which relation the depositor relation so
8:11
here we will be getting a temporary
8:13
relay Edition which contains only one
8:15
column which is customer name so all the
8:18
customer names from the depositor
8:19
relation will be listed here
8:21
so all the customers who have deposited
8:24
money in the bank will be listed here
8:26
and coming to this side we will be
8:28
getting the list of all customers who
8:30
have availed the loan so here we will be
8:33
getting all the customer names who have
8:35
Avail the loan but what we are asked to
8:37
find we are asked to find the names of
8:39
all customers who have deposited the
8:41
money as well as available to the loan
8:43
and that's why we are using intersection
8:45
here so let's see an example let's take
8:48
the depositor relation which contains
8:49
two columns the customer name and
8:51
account number Tom with the account
8:53
number a101 Amy with a502 rows with a304
8:59
as the account number and John with a689
9:03
as the account number so this depositor
9:05
relation contains four tuples and coming
9:08
to the other relation which is the
9:10
moreover relation this borrow relation
9:12
also contains four tuples but it need
9:15
not be the case that the number of
9:16
tuples should match on both the
9:18
relations but the condition is the
9:20
number of columns on both the relation
9:21
should match in this case even if there
9:24
are differences in the number of columns
9:25
in both depositor and borrower relation
9:27
this will still work because we are
9:30
projecting only customer name here and
9:31
customer name here so the output
9:33
contains only one column here and this
9:36
output also contains one column we are
9:38
doing intersection between a relation
9:40
with one column with another relation
9:42
with one column
9:44
and in borrower relation we have John
9:46
Smith Rose and Jack with corresponding
9:49
loan numbers and what will be the output
9:52
of this relational algebra expression
9:54
for Simplicity I am giving a temporary
9:57
name for this output as r
9:59
and again this side I am giving the name
10:02
s for Simplicity so R will contain only
10:05
one column which is customer name and S
10:08
will also contain only one column which
10:10
is also customer name now we are going
10:11
to perform R intersection s which is
10:14
customer names from both the relations
10:17
if you see here this output will contain
10:19
a relation with one attribute and this
10:22
output will also contain one relation
10:24
with one output and we are performing
10:26
set intersection since the number of
10:28
attributes are same on both the operands
10:30
it can be performed at the same time
10:33
this is also customer name and this is
10:35
also customer name and we can confirm
10:37
that condition number two is also
10:38
getting satisfied because both the
10:40
attributes are belonging to the same
10:42
domain so we got our intersection s with
10:45
the output rows and John
10:48
why Rose and John and why not others
10:50
because Rose has deposited the money in
10:53
the bank as well as Rose as a wild loan
10:55
from the bank coming to John John is a
10:58
depositor as well as a loan borrower
11:01
and that's why we can see Rose and Jon
11:03
are there in the output why not Tom and
11:06
Amy because Tom and Amy are merely
11:08
depositors but not borrowers and what
11:12
about Smith and Jack Smith and Jack are
11:14
just borrowers not depositors
11:17
so set intersection can also be
11:19
inevitable in certain situations I hope
11:22
this example is clear for you now let's
11:24
proceed with the example that we have
11:26
been dealing in the previous
11:27
presentations the formal example which
11:29
deals with the university database what
11:32
about the database schema it has
11:34
multiple relations the instructor
11:36
relation the course relation the
11:38
department relation the section relation
11:40
the teacher's relation student relation
11:43
advisor relation takes relation
11:46
classroom relation and the time slot
11:49
relation now let's focus on the formal
11:52
example in example number two we are
11:54
going to find the set of all courses
11:56
taught in Fall 2009 semester and the
12:00
spring 2010 semester I request you to
12:03
pause this video for a while and think
12:05
about the answer for this question
12:08
I hope you are done so this is a pretty
12:11
straightforward question because we have
12:13
already seen similar kind of examples in
12:15
the union and the set difference
12:16
operations so the solution for this
12:19
question is going to be like this first
12:21
we will list all the set of courses that
12:23
are taught in Fall 2009 semester so what
12:26
relation we are going to take it's the
12:29
set of all courses taught not related to
12:31
instructor so we are not going to take
12:33
the teacher's relation rather we are
12:35
going to take the section relation which
12:37
contains all the courses taught in a
12:39
particular semester so the output is
12:42
first we will find the set of all
12:44
courses taught in Fall 2009 semester
12:46
which is this part and what output we
12:49
want here only the set of all courses so
12:52
what we will do is from the section
12:54
relation we will filter all the tuples
12:56
which are belonging to the semester fall
12:58
and the year 2009 and that output will
13:02
contain all the tuples that are
13:04
belonging to fall 2009 but it will also
13:07
contain additional attributes all the
13:08
columns we want only course ID so here
13:11
we will apply a project and filter the
13:13
course ID let's see the output now so
13:16
first what we are doing is from the
13:18
section relation we are just filtering
13:20
we are just selecting all the tuples
13:22
where the semester is fall and year is
13:25
2009 so this output will be giving a
13:28
relation which contains all the tuples
13:31
with all the attributes where the
13:33
semester is fall and year is 2009 I mean
13:36
we are dealing this part and from this
13:38
output we are filtering only the course
13:40
ID so I am just projecting only course
13:43
ID here so this is pertaining to fall
13:46
2009 semester and likewise we need to
13:49
retrieve the records pertaining to
13:51
Spring 2010 semester so what I'm gonna
13:53
do now here is I'm gonna find out the
13:56
set of all courses taught in Spring 2010
13:59
semester and the query is simple the
14:01
same select operation where the semester
14:03
is spring because this is spring and the
14:06
year is 2010 which is this from which
14:08
relation section relation again this
14:11
inner query will give all the tuples
14:13
which are belonging to Spring 2010 from
14:16
section relation and we want a project
14:18
only course ID because our intention is
14:20
to find all the courses so here I am
14:22
giving a project for this inner query by
14:25
retrieving or by projecting only course
14:27
ID now we have two relations this is
14:29
relation one and this is relation two
14:31
and what we are required to find we are
14:34
required to find the set of all courses
14:36
taught in Fall 2009 semester and spring
14:39
2010 semester it means the course that
14:43
our handle should be there in both fall
14:45
2009 and spring 2010 say for example if
14:49
dbms is the course this dbms score
14:52
should be offered in both fall 2009
14:54
semester and spring 2010 semester so the
14:58
output should be appearing in both the
15:00
relations and that's why we are
15:02
performing the set intersection between
15:04
these two sets or relations to answer
15:06
the query we need the intersection of
15:08
these two sets or relations so the
15:10
output is I'm just projecting the course
15:12
ID so this is the first half which is
15:15
this and this is the second half which
15:18
is this and we are simply performing
15:20
intersection so that the output will
15:22
contain the set of all course IDs dot in
15:25
both fall 2009 semester and spring 2010
15:28
semester
15:29
I hope this example is clear to you
15:31
before we sign out we need to understand
15:33
the important thing about the set
15:35
intersection can we do the set
15:37
intersection operation without set
15:39
intersection operation
15:41
confusing I mean to say imagine we don't
15:45
have set intersection operation
15:47
exclusively how can we achieve the
15:49
functionality of the set intersection
15:51
operation without using this operator
15:53
the intersection operator is that
15:56
possible
15:57
yes it is possible let's see that now we
16:00
have already seen about the basic
16:02
concepts of the set intersection now
16:04
this set intersection can be achieved
16:07
without using the set intersection
16:08
operator how it is our intersection s
16:12
gives the same output as that of R minus
16:16
r minus s we know what is minus the set
16:19
difference first we need to perform R
16:22
minus s and then this is the output
16:24
which is going to be applied on R minus
16:27
it means our intersection s is equal to
16:30
R minus r minus s don't worry about this
16:34
we shall prove this now so what we are
16:36
going to prove now is our intersection s
16:39
is equal to R minus r minus s so let's
16:42
bring in this formula and let's take the
16:44
first relation R which is containing the
16:46
set of alphabets A B C D E and F let's
16:50
take another set of relation which is s
16:52
which is containing set of characters a
16:54
b dollar e f 1 and hash
16:57
we have already performed R intersection
16:59
s in our previous slide and the output
17:01
of R intersection s will be the set of
17:03
elements a b e and f where a b e and f
17:07
are the elements that are there in both
17:10
r as well as s now what we are required
17:13
to do is R minus r minus s first we will
17:16
compute this R minus s so we are just
17:18
applying R minus s what do we mean by R
17:21
minus s r minus s will give the output
17:24
where the output will be the member of R
17:26
but not s
17:29
if you see the elements a b e and f
17:32
these members are there in R as well as
17:35
s r minus s will give the output where
17:38
they are the member of R but not s right
17:41
C and D are the members of R but not yes
17:45
can you see C and D are not there and
17:47
yes so obviously R minus s will give C
17:50
and D as the output so R minus s we have
17:53
computed now we will perform R minus r
17:55
minus s it means we are going to perform
17:58
R minus r minus s and this output will
18:02
be giving a b e and f because this is C
18:05
and D this is a b c d e and f now R
18:09
minus r minus s means the set of members
18:13
of R but not R minus s right C and D are
18:17
the members of R minus s here also they
18:20
are the members but a b e and f are the
18:24
members of R but not R minus s and
18:27
that's why in the output we get a b e
18:30
and f if you compare R intersection S
18:32
and R minus r minus s both are same why
18:36
we are dealing this basic operation now
18:38
in examinations they might be expecting
18:41
us to solve this problem without using
18:43
set intersection in that case we need
18:46
not truly rely on this operator rather
18:48
we can directly use R intersection S as
18:51
R minus r minus s we have already seen
18:54
about the various fundamental operations
18:56
why we are calling this as fundamental
18:58
operation is that even the additional
19:00
operations can be achieved through these
19:02
fundamental operations before we sign
19:05
out let's see the homework question we
19:07
have three homework questions here
19:08
question number one is the relational
19:11
algebra operation r Union is commutative
19:14
question number two is the relational
19:17
algebra operation r intersection as
19:19
commutative and question number three is
19:22
the relational algebra operation r minus
19:25
s commutative I request you to think
19:28
about these questions and post your
19:30
answers in the comment section before we
19:33
sign out I wanted to give you an insight
19:35
about what is accommodative property
19:37
when we take integers as a set now in
19:40
this integer plus is an operator to be
19:43
precise this is an arithmetic operator
19:45
which performs addition where this plus
19:47
operator the addition operator is
19:49
commutative if we take any two elements
19:52
say 5 and 10 5 plus 10 is equal to 10
19:55
plus 5 right so plus is commutative take
19:58
any integers 200 500 200 plus 500 is
20:02
equal to 500 plus 200 so the operator
20:05
plus is commutative likewise think
20:08
logically and answer this question
20:09
whether this Union intersection and
20:12
minus are commutative or not as far as
20:15
relational algebra is concerned and
20:18
that's it guys I hope you guys enjoyed
20:20
this presentation and thank you for
20:22
watching
20:23
[Music]
20:23
[Applause]
20:25
[Music]