0:00	foreign
0:07	welcome back in this presentation we are
0:10	going to focus on the set difference
0:11	operation in relational algebra we know
0:15	basically there are six fundamental
0:17	relational algebra operations number one
0:20	the select number two project number
0:22	three Union we have already elaborately
0:25
focused on these three topics in the
0:27
previous presentations and we are left
0:29
with three more fundamental operations
0:31
the set difference the Cartesian product
0:33
and the rename operation in this
0:36
presentation we are going to focus our
0:38
attention towards the fourth fundamental
0:40
operation which is the set difference
0:42
operation let's directly step into the
0:45
topic of the day the set difference
0:48
we know basically a relation is a set
0:50
and we know that there is a close
0:52
correspondence between the relational
0:54
Concept in dbms with the set Concept in
0:58
mathematics and what operation is
1:00
matching for this
1:02
it is like the same set difference
1:04
operation in the set theory so whatever
1:06
we are going to see the set difference
1:08
in the rdbms in the relational algebra
1:11
which is like the same as that of the
1:13
set difference operation in set theory
1:15
and the name itself says that it's the
1:17
set difference so obviously it requires
1:20
two tables or two relations right so
1:22
this is a binary operation where it
1:25
takes two relations as the input and
1:27
produces one relation as the output
1:30
and what's the logic of this set
1:32
difference the logic is this set
1:34
difference operation is used to find the
1:36
tuples that are in one relation but they
1:40
are not in another relation say if we
1:42
have two relations r and s
1:45
say if we perform R minus s what does it
1:48
mean so that's what the next Point says
1:50
R minus s means we are going to find the
1:53
tuples that are in R but not in s so it
1:58
means we have two relations R and S
2:00
there are certain tuples in R and
2:02
certain tuples in yes and the output of
2:04
R minus s is going to be the set of
2:07
tuples in R where these tuples are not
2:10
there in yes no worries when we see an
2:12
example it will be easy for you to
2:14
understand and we know what is the
2:16
symbol that is used to denote this a
2:18
difference operation this set difference
2:20
is denoted by the minus symbol before
2:23
stepping into the formal example let's
2:25
understand the working of the set
2:27
difference operation then we will see
2:28
the formal examples so let's understand
2:31
the say difference operation
2:33
let's say there is a relation R which
2:36
contains the set of alphabets A B C D E
2:39
and F and let's say we have another
2:42
relation s and this s contains
2:44
characters a b dollar l y one hash and
2:50
what operation I am going to perform
2:51
which is R minus s and what is the
2:55
output of R minus s the set of all
2:58
elements in R but not in yes so let's
3:01
see that now the element a which is
3:04
there in R which is also there and yes
3:07
so the output R minus s will not contain
3:09
this element a because the concept of R
3:12
minus s is the set of elements that are
3:16
in R but not in yes here the element a
3:19
is in s so we won't have a in the output
3:22
coming to B this B is in r as well as s
3:25
so obviously B will not be there in the
3:28
output relation and coming to c c is
3:31
there in R but not in yes can you see
3:34
there is no C in yes so R minus s means
3:37
the set of elements that are in R but
3:40
not in yes so c will be there in the
3:43
output relation and coming to the next
3:45
element d d is there in R but not in yes
3:48
so obviously D will be there in the
3:50
output relation coming to e The Element
3:53
e is there in R and not in s so e will
3:57
also be there in the output and coming
3:59
to f f is there in R but not in s so
4:03
obviously the result R minus s will
4:06
contain C D E and F because c d e and f
4:10
are the set of elements that are there
4:13
in R but not in s so with this basic
4:17
knowledge let's see the formal example
4:19
example number one
4:21
list all customer names those who have a
4:25
deposit account but not a wild Loan in
4:28
the previous lecture which is the union
4:30
operation where we have seen the
4:32
question to list all customer names
4:34
those who have a deposit account also
4:36
they have availed loan but here we need
4:39
to list all customer names those who
4:42
have a deposit account but they have not
4:44
aviled loan so what's the output we are
4:48
going to list all the customer names
4:49
from the deposit relation minus all the
4:53
customer names from the loan relation
4:54
for this I am not going to use select
4:57
because here we are going to list only
4:59
the customer names so here also we are
5:01
considering the same relations we have
5:03
seen in the previous lecture the
5:05
depositor and the borrow over relation
5:07
so before going into the solution let's
5:09
see the table the depositor table or the
5:12
depositor relation contains customer
5:14
name and account number where the
5:16
customer name is Tom Amy Rose and John
5:19
and let's not bother about the account
5:21
number and we also have another relation
5:24
which is borrower which also contains
5:26
the customer name and the loan number
5:28
here the customer names are John Smith
5:30
Rose and Jack now what we are required
5:32
to find we are required to find to list
5:35
the customer names who have a deposit
5:38
account it means they have a deposit
5:40
account but they have not available
6:21
and this is the output Tom and Amy are
6:25
the customer names those who have a
6:27
deposited account but they are not
6:29
borrowers because they have not availed
6:31
any loan and what's the query for this
6:33
so the answer for this query is we are
6:36
going to project the customer name from
6:38
depositor which is this minus we are
6:42
going to project the customer name from
6:43
the borrower relation which is this and
6:46
the output of this expression is going
6:48
to be a temporary table which contains
6:50
all the customer names and the output of
6:52
this expression is going to be the set
6:54
of all customer names from this table so
6:57
the output of this is going to contain
6:59
only one attribute and the output of
7:01
this expression is going to be a
7:02
temporary relation with one attribute so
7:05
we are going to perform minus between
7:07
these two outputs and here is the answer
7:09
for example number one we are done with
7:13
example number one before we see example
7:15
number two we need to recap the
7:17
university database
7:19
I request you to pause this video for a
7:21
while and make a note of this keyboard
7:25
I hope you are done let's now proceed
7:27
with example number two and example
7:30
number two is find all the courses
7:32
taught in Fall 2009 semester but not in
7:36
Spring 2010 semester so what table we
7:39
are going to use I'll bring in the
7:41
previous slide
7:43
what table we are going to use the table
7:45
that we are going to use is the section
7:47
relation because this section relation
7:49
contains the information about the
7:51
courses and to which section that course
7:53
was offered which semester which year
7:55
and other information
7:58
so the solution for this is going to be
8:00
first we are going to retrieve the set
8:02
of all courses that are taught in Fall
8:04
2009 semester which is this part so the
8:07
query is going to be very simple we are
8:09
going to select all the tuples from
8:11
section relation where the semester is
8:13
fall and the year is 2009 so this part I
8:17
am focusing on so the output of this
8:20
inner expression is going to be the set
8:22
of all tuples where semester is fall and
8:26
year is 2009 and we are going to
8:28
retrieve only the courses right so I'm
8:30
gonna project only the course ID from
8:33
this temporary output so this relational
8:36
algebra expression gives the output of
8:38
all course ID taught in Fall 2009
8:41
semester and we want to do the same
8:43
operation for spring 2010 so we are
8:46
going to find the set of all courses
8:47
taught in Spring 2010 semester so we
8:50
write the query like this and this inner
8:53
query the select query is going to
8:54
retrieve the set of all tuples where the
8:57
semester is spring and year is 2010 and
9:00
from this output the temporary relation
9:03
which contains all the attributes I am
9:05
going to project only the course ID now
9:08
we have two temporary relations this is
9:10
one and this is 2. now what is the
9:12
question we want to find all the courses
9:15
taught in Fall 2009 semester but not in
9:18
spring right so we want to take this
9:21
minus this so this will give you the set
9:24
of all courses that are taught in Fall
9:26
2009 but not in Spring 2010.
9:30
so to answer the query we need the minus
9:33
of these two sets what do you mean by
9:35
this this is the first expression which
9:38
is this minus this is the second
9:40
expression which is this so here is the
9:43
output of example number two as the
9:46
course progresses I am not showing the
9:48
output of the relations in a tabular
9:50
format there is a reason for this say
9:52
when we wanted to face some competitive
9:54
examinations like ISRO or gate or any
9:57
other competitive examinations they
9:59
might not be giving the tables with data
10:01
explicitly we need to make some
10:03
assumptions and this kind of practice
10:05
will help us to assume things and solve
10:08
the problems quickly before we sign out
10:10
let's see the two important conditions
10:12
that are to be satisfied for any R minus
10:16
s to be valid for R minus s to be valid
10:19
the first condition is R and S must be
10:22
of same arity it means the relation R
10:25
and relation s both must be having the
10:28
same number of attributes or same number
10:30
of columns this is also like the same
10:33
condition we have seen for our Union Yes
10:35
and coming to the second condition for
10:37
all I the domain of the ith attribute of
10:40
the relation R should be the same as
10:42
that of the domain of the ith attribute
10:44
of the relation s so this is also like
10:47
the same condition we have seen for our
10:49
Union yes if there are two relations R
10:52
and S we cannot directly do R minus s
10:55
because first condition is R and S must
10:57
be of same arity let's say the relation
11:00
R has 10 columns or attributes and
11:02
relation s has nine attributes so we
11:05
cannot simply perform R minus s because
11:07
both are differing in terms of the num
11:09
number of attributes but in the example
11:12
what we have seen we have projected only
11:14
one column and then only we did the
11:16
minus operation between the projections
11:19
I'll show that now so what we have done
11:21
here this is the projection which
11:23
contains only course ID and this is
11:26
another projection that is also
11:27
containing only course ID so the output
11:29
is going to be a temporary relation with
11:31
only one column and the output of this
11:33
is also going to be a temporary relation
11:35
with only one column so minus is being
11:38
performed on two different temporary
11:40
relations which is having only one
11:42
column and condition number one is
11:44
satisfied because both are having same
11:45
arity and coming to the second condition
11:47
the domain of the course ID here also it
11:50
is course ID and here also it is course
11:52
ID and this course ID and this course ID
11:54
are belonging to the same table or the
11:57
same relation section so obviously the
11:59
domain of the course ID is the same in
12:02
both the relations
12:03
and that's it guys I hope the set
12:06
difference operation in relational
12:07
algebra is clear to you before we sign
12:10
out let's see the homework questions in
12:13
this presentation also we have two
12:14
homework questions let's see the first
12:16
question this is homework question
12:18
number one we are given with two
12:21
relations relation R and relation s in
12:25
the example what we have seen in this
12:26
presentation is we computed or we found
12:29
out R minus s but what you are required
12:32
to find is s minus r so I request you to
12:36
perform the S minus r relational algebra
12:38
operation and find out what all the
12:41
elements that this temporary relation
12:43
will contain remember the output of the
12:45
relational algebra expression is going
12:47
to be a temporary relation with the
12:49
elements now you are required to find
12:51
what are all the elements of this set s
12:53
minus r so this is about homework
12:56
question number one and coming to
12:58
homework question number two for this I
13:00
am going to project the same University
13:02
database schema which we have been
13:04
addressing in this lecture series the
13:06
same University schema with the
13:08
instructor course Department section
13:10
teacher student advisor takes classroom
13:13
and time slot relations here we have two
13:16
questions question number one is list
13:19
all the instructors ID who are not
13:22
advisors we are required to find all the
13:25
instructors ID it's not the instructor's
13:28
ID that exists in the instructor
13:29
relation directly if this is the case we
13:32
can simply do project ID from instructor
13:34
but the question is actually list all
13:37
the instructors ID who are not advisors
13:39
so what we are understanding here is we
13:42
have an instructor relation we also have
13:44
an advisor relation and what we
13:46
understand is not all instructors are
13:49
advisors and what we are required to
13:51
find we are required to find only the
13:53
instructor's IDE who are not advisors so
13:57
I'll give you a clue so for this you
13:59
need to involve two tables or two
14:01
relations the relations are instructor
14:04
relation and advisor relation and we
14:07
know the title of this presentation is
14:08
actually set difference and we are going
14:10
to seek the help of set difference
14:12
operation in order to find the answer
14:13
for this so use it wisely and retrieve
14:16
all the instructors ID who are not
14:18
advisors and coming to question number
14:20
two list all the instructors ID who
14:23
taught in Spring 2020 but not advisors
14:27
so I'm adding some more complexity to
14:30
the previous question where we are going
14:31	to list all the instructors ID this
14:34	instructor should have taught at least
14:36	one subject in the semester spraying in
14:39	the year 2020 so we are required to list
14:41	all the instructors ID who taught in
14:44	Spring 2020 but they are not advisors as
14:48	mentioned in the previous presentation
14:49	these kind of questions will help us to
14:52	know more about the relational algebra
14:54	operations and certainly it will help us
14:56	to crack the competitive examination
14:58	questions
14:59	and in this presentation we focused on
15:01	the set difference operation in the next
15:03	presentation we will focus on the
15:05	Cartesian product relational algebra
15:07	operation I hope you guys enjoyed this
15:09	presentation and thank you for watching
15:13	[Music]
15:13	[Applause]
15:15	[Music]