0:00	foreign
0:06	we will see the next relational algebra
0:10	fundamental operation the relational
0:12	algebra Union operation we know
0:15	basically there are six fundamental
0:17	relational algebra operations and we
0:20	have already seen about the first two
0:21
operations the select and project
0:23
operations in the previous lectures now
0:26
we are going to focus on the third
0:27
fundamental operation which is the union
0:30
operation let's see the theoretical
0:32
aspects of this Union operation now and
0:35
we have already seen this point in the
0:37
last presentation that any relation is a
0:40
set a relation in dbms is having a close
0:43
correspondence or close association with
0:45
the mathematical concept called the set
0:47
and when we talk about the union
0:49
operation in relational algebra this
0:52
Union operation is similar to the union
0:54
operation in the set theory and that's
0:57
why I told you any relation is a set and
1:00
this Union operation what we are
1:01
focusing on in this presentation is
1:04
similar to the union operation a insert
1:07
Theory and what type of operator is this
1:09
Union operator is it a unary or a binary
1:11
operator the name itself says that it is
1:14
going to do the union operation it means
1:16
it requires at least two tables to
1:18
perform the operation isn't it so it is
1:21
a binary operator where it requires two
1:24
inputs to be precise it requires two
1:27
tables or two relations to perform the
1:29
operation say we have two relations A
1:32
and B and what's the output of a union B
1:35
that's what we are going to see now it
1:37
is a set of all objects that are a
1:40
member of a or b or both if we take a as
1:45
a relation and B as another relation and
1:48
we perform a union B then the output is
1:51
going to be the set of all objects or
1:54
all tuples that are a member of a or b
1:58
or both no worries when we see an
2:01
example at the time it will be easy to
2:03
understand but for now just understand
2:05
when we are are performing a union B the
2:08
output will be containing the members
2:10
from a as well as the members from B and
2:13
there are situations where the values
2:15
may be appearing in both A and B will we
2:17
be having duplicate values no we will
2:20
not be having duplicate values so like
2:22
the project operation here also the
2:25
duplicate rows are eliminated let's say
2:27
a is containing some list of fruits say
2:30
for example apple mango and grapes and B
2:33
contains apple and strawberry and when
2:35
we perform EA Union B we will be getting
2:37
Apple only once why even though the
2:41
fruit apple is in both the sets A and B
2:43
but still we will be getting only one
2:46
apple in the resultant and that's why I
2:48
told you the values in the output will
2:50
be the member of a or b or even both but
2:54
if there are duplicate values these
2:56
duplicate values are eliminated in The
2:58
resultant table and what is the symbol
3:00
which is used to denote the union
3:02
operation this is the symbol the union
3:04
symbol which is used to denote the union
3:07
operation let's see the syntax now when
3:10
we talk about Union we cannot directly
3:13
apply Union on two different relations
3:15
let's say a is a relation and b is
3:18
another relation can we directly perform
3:20
a union B we need to ensure certain
3:23
conditions are met because we cannot
3:26
directly perform a union B the number of
3:28
attributes in EA may be different from
3:31
the number of attributes in B say for
3:33
example if a contains five attributes
3:36
and B contains four attributes in this
3:38
case a union B cannot be performed then
3:41
how it will be helpful just see the
3:43
syntax it will be easy we are not
3:46
directly performing relation One Union
3:48
relation to rather we are performing the
3:51
projection of a single column or
3:53
multiple columns from relation one that
3:56
is performed with the union with another
3:59
projection it may be a single column or
4:01
a group of columns from another relation
4:03
which is relation 2 in this example so
4:06
what we are actually doing here is we
4:08
are going to project a column from
4:10
relation 1 Union and we are going to
4:13
project another column from relation to
4:15
and the output of this will be single
4:18
column and the output of this will be
4:20
single column and we are going to
4:21
perform form one column Union another
4:24
column remember the output of this will
4:26
be a relation only and the output of
4:28
this is also going to be a relation only
4:30
so we are going to perform Union between
4:32
relations but not directly when we see
4:35
an example we will be able to understand
4:37
things clearly let's first understand
4:40
the operation of Union with simple
4:42
example
4:44
let's say we have a relation R which
4:47
contain only one attribute and this
4:49
attribute is alphabets a b c e and f
4:53
let's take another relation which is yes
4:56
and this relation is having a column or
4:58
an attribute which is characters and the
5:00
characters are a b dollar e f 1 and hash
5:06
symbol now what we are going to perform
5:08
is R Union Yes can we do our Union Yes
5:12
yes there are certain conditions to be
5:14
made but at the basic level the first
5:16
condition will be satisfied the number
5:18
of attributes in R is same as that of
5:21
the number of attributes in yes so here
5:23
also we have only one attribute in the
5:25
relation here also we have only one
5:27
attribute in the relation so our Union
5:29
Yes can be performed but still we have
5:31
another condition to be satisfied before
5:33
applying Union which we will see at the
5:36
end of this presentation for now just
5:38
understand I am going to apply R Union
5:40
yes let's see the output of our Union
5:43
Yes and the output is going to be our
5:46
Union is let's see what's the data
5:48
present in this our Union s relation say
5:51
a b c e f so all these value will appear
5:55
in the output relation a b c e and f now
5:59
the members of R is present all the
6:02
members of R is present in the output
6:04
relation now we need to ensure whether
6:06
all the members of Yes are also present
6:08
in the output relation talking about the
6:10
first member a which is already already
6:12
there in the output relation as per the
6:14
concept of uni and the duplicate values
6:16
are eliminated so this a will not be
6:19
appearing again in this output relation
6:21
likewise B Because B is also already
6:24
there in the output relation and talking
6:26
about dollar dollar is not there in R
6:29
but it is there in yes so dollar will be
6:32
there in the output relation coming to e
6:34
and f e and f are already there in R
6:37
which is already sent to the output R
6:39
Union Yes I mean which is already
6:41
available in the output relation so E
6:44
and F will not be appearing again
6:45
because it's a duplicate value now so
6:48
this will be eliminated then coming to 1
6:50
if you see one is there only in yes and
6:53
not in R so one will also be there in
6:55
the output and coming to the hash symbol
6:58
this hash symbol is not available in the
7:00
relation R but available in s so this
7:03
will also be available in the output
7:05
table so this is the working of R Union
7:08
s in simple terms if we consider two
7:11
relations r is the output will be
7:14
containing the members of r or the
7:17
members of Yes or both so this example
7:19
is just for understanding Purpose with
7:22
this knowledge let's see the formal
7:23
example
7:25
the question is list all customer names
7:28
associated with the bank either as an
7:31
account holder or a loan borrower just
7:34
pause this video for a while and think
7:36
what are the two relations that we are
7:38
talking about in this question
7:40
I hope you are done the example
7:43
relations that we are talking here is
7:45
one is related to the account holder and
7:48
the other one is related to the loan so
7:50
what we are assuming here is there are
7:52
two relations one is the deposited
7:54
relation so I am saying if somebody is
7:57
having an account in the bank it means
7:59
that person is having a deposited
8:01
relationship with the bank so here is
8:03
the depositor relation and if somebody
8:05
has awailed a loan from the bank so
8:08
obviously he is a borrower right so he
8:10
is the loan borrower so I am considering
8:12
these two relations are existing number
8:15
one the depositor and number two the
8:18
borrower and what we are required to
8:19
find we are required to find the list of
8:22
all customer names associated with the
8:25
bank either as an account holder or as a
8:28
loan borrower let's say there is a
8:30
customer X if that name X it should
8:33
appear either in the account relation or
8:35
in the depository relation it may even
8:37
appear in both the relations but our
8:40
output should contain the value X
8:42
let's solve this problem now so what we
8:44
are doing is we are considering this
8:46
depositor relation and we are retrieving
8:49
only customer name from the depositor
8:51
relation which is this part
8:53
so all the depositor's name will be
8:55
appearing here just see what operator I
8:57
have used the project operator so this
9:00
project retrieves only the customer
9:02
names from this depositor relation and
9:04
the output of this is going to be a
9:06
relation a temporary relation which
9:09
contains only one attribute which is
9:11
customer name and coming to this side
9:13
here I am focusing on the borrower
9:15
relation and in the borrower relation I
9:17
am retrieving only the customer names
9:19
from the borrower table or borrower
9:21
relation and here what operator I am
9:23
using the same project operator please
9:26
note the depositor relation may contain
9:28
X number of attributes and the borrow
9:30
relation may contain y number of
9:32
attributes Y and X may not be the same
9:34
but here we are not doing depositor
9:37
Union borrower we are doing customer
9:40
name Union customer name only where this
9:43
customer name is from depositor relation
9:45
and this customer name is from borrower
9:47
relation and the output of this
9:49
expression is going to be a table with
9:51
only one attribute and the o output of
9:53
this expression is going to be only one
9:55
relation with one attribute and we can
9:57
perform Union
9:59
so here is the output for this query so
10:02
generally when questions are asked just
10:04
make an assumption what could be the
10:05
table name and just start proceeding
10:07
with the expression if table names are
10:09
not explicitly mentioned we can make
10:11
assumptions on the table name and we can
10:13
proceed with the Expressions to
10:15
understand things clearly let me
10:17
elaborate the solution with an example
10:19
table let's take this is the depositor
10:21
table which contains two attributes
10:23
customer name and account number which
10:25
is this relation let's take this
10:27
relation the borrower relation which
10:29
contains two attributes customer name
10:31
and loan number are we going to do
10:33
depositor Union borrower depositor Union
10:36
borrower may be proceeded with because
10:38
it also has two attributes and this also
10:40
has two attributes but still we have
10:42
other conditions to be checked before
10:44
proceeding with depositor Union borrower
10:46
let's not touch upon that part now but
10:49
for now just understand we are not going
10:51
to proceed with the Positive Union
10:52
borrower rather we are going to proceed
10:55
with projection of customer name from
10:57
depositor Union projecting customer name
11:00
from borrower it means I am going to do
11:02
Union only for these two columns this
11:05
one Union this one let me give a naming
11:08
convention for easy readability let this
11:11
be R and let this be yes so what is r
11:14
it's the customer name from depositor
11:16
relation so what I am doing is I am just
11:18
renaming this output as R which is this
11:20
so This R contains only the customer
11:22
names from depositor relation and this s
11:25
contains the customer name only from
11:28
borrower relation and this is R this is
11:30
s so what I'm gonna perform is R Union
11:33
Yes which is the customer name so Tom
11:36
Amy Rose John Tom Amy Rose John it's
11:41
appearing here then coming to this place
11:43
John John is already there in the table
11:46
so this will be ignored this will be
11:49
eliminated because this is a duplicate
11:51
value coming to Smith Smith is not there
11:53
already in the output table so Smith
11:55
will be added coming to rows Rose is
11:58
already there in the output table so
11:59
rows will also be eliminated and coming
12:02
to Jack Jack is not already there in the
12:04
output table in other words jack is not
12:06
there in the first table and hence Jack
12:08
will be added to the output and this is
12:11
the output of R Union Yes
12:14
before seeing the other example I will
12:16
show you the schema
12:18
just pause this video for a while and
12:20
have a look at the schema
12:23
I hope you all done now let's proceed
12:26
with the next example the question is
12:29
find the set of all courses taught in
12:32
the fall 2009 semester the spring 2010
12:35
semester or both
12:37
what we are required to find we are
12:39
required to find the set of all courses
12:42
and these courses may be taught either
12:44
in Fall 2009 semester or in the spring
12:47
2010 semester or both now from which
12:51
table we need to perform all this
12:52
operation if you want I'll just show you
12:54
the table just think about it
12:57
so from which relation we are going to
12:58
retrieve the data
13:00
section is the relation where this
13:03
contains the course as well as the
13:05
semester as well as the year which has
13:07
been taught right so I am going to take
13:09
the list of all courses that are taught
13:12
in this particular semester and in this
13:15
particular Year from this relation
13:16
section
13:19
so the answer for this part is going to
13:21
be the project operation and before that
13:24
what we need to do from the section
13:26
relation we are going to retrieve all
13:28
the courses that are taught in the
13:31
semester fall and in the year 2009 so
13:34
what I am doing is first I am retrieving
13:36
all the tuples where the semester is
13:39
fall and giving and condition because in
13:42
this form and 2009 year so I am
13:45
selecting all the tuples where the
13:47
semester is fall and the year is 2009
13:50
from the section relation so this will
13:52
give all the tuples and what I need only
13:55
the set of all courses right so course
13:58
ID I need the course ID so I'm just
14:00
projecting only course ID from this
14:02
output temporary relation remember this
14:05
temporary relation will contain the set
14:07
of all tuples with all attributes from
14:10
those attributes I am retrieving only
14:11
course ID
14:13
so this is for this semester fall 2009
14:16
we need to do the same for spring 2010
14:18
so the query goes like this to find the
14:21
set of all courses taught in the spring
14:22
2010 semester we write select semester
14:26
is equal to spring because we are
14:28
focusing on this part and here is equal
14:31
to 2010 and year is equal to 2010 from
14:34
the section relation the output of this
14:37
inner query is going to be a temporary
14:39
relation with the tuples with all
14:41
attributes and from this I am going to
14:43
project only one attribute which is
14:45
course side now this query gives the
14:48
output of all the course ID that is dot
14:51
in Fall 2009 this query gives the output
14:54
of all course ID that are taught in
14:56
Spring 2010 and what's the question we
15:00
need to list all the courses that are
15:02
taught in Fall 2009 spring 2010 or both
15:05
in that case to answer this query we
15:08
need the union of these two sets so the
15:11
output of this is this part which is
15:13
this part Union this part which is this
15:17
so the output is going to be the set of
15:20
all courses taught in Fall 2009 semester
15:23
the spring 2010 semester or both before
15:27
we sign out let's see the two important
15:29
conditions to be valid for performing
15:31
the union between two tables
15:34
the two important conditions for what
15:37
for our Union Yes to be valid
15:40
I mean r is a relation here and S is a
15:43
relation here for our Union Yes to be
15:46
valid then these two conditions should
15:48
be met first R and S must be of same r80
15:51
what do we mean by this it means the
15:54
number of columns in R and the number of
15:56
columns in yes must be the same
15:59
in simple terms the number of columns in
16:02
both the relations R and S should be the
16:04
same for example if the relation R
16:06
contains 10 attribute and the relation s
16:09
contains five attributes R Union Yes is
16:12
not possible because the first condition
16:14
itself is the number of attributes on R
16:17
and S must be the same
16:19
let's say if the number of attributes
16:21
are matching
16:22
then can we directly perform R Union Yes
16:24
No we cannot directly perform R Union
16:26
yes so the next condition is for all I
16:30
the domain of the ith attribute of R
16:32
should be the same as that of the domain
16:35
of ith attribute of yes
16:37
what do we mean by this let's say there
16:40
are two relations R and S and R contains
16:43
three attributes and S also contains
16:45
three attributes so the first condition
16:47
is satisfied both R and S must be of
16:49
same rity yes R and S has three columns
16:53
each and coming to the second condition
16:55
the domain of ith attribute of R should
16:58
be the same as that of the domain of ith
17:01
attribute of yes
17:03
it means the domain of the first
17:05
attribute of the relation R should be
17:07
the same as that of the domain of the
17:09
first attribute of yes the domain of the
17:12
second attribute of the relation R
17:14
should be the same as that of the domain
17:16
of the second attribute of the relation
17:18
yes so likewise all the attributes
17:21
according to their position should be
17:23
belonging to the same domain when these
17:25
two conditions are made R Union s is
17:28
possible but in our example we have not
17:31
simply performed Union between two
17:33
relations directly rather we have
17:35
projected the output the temporary
17:37
output and then we perform the union
17:38
among these two outputs
17:41
and that's it guys before we sign out
17:43
let's see the homework questions here we
17:46
have two homework questions let's see
17:48
what are they before seeing the
17:50
questions I wanted to introduce the
17:51
schema that we are familiar with this is
17:54
the University database schema where we
17:57
have multiple relations instructor
17:59
course Department section teachers
18:02
student advisor takes classroom and time
18:07
slot question number one which is list
18:10
all the course IDs which are taken in
18:13
Spring 2020 or fall 2021 semester so
18:17
this is a pretty straightforward
18:19
question because we are going to find
18:20
all the course IDs which are taken in
18:24
spring semester year 2020 or fall
18:27
semester year 2021
18:29
we can easily answer this question
18:31
number one because it's a simple
18:33
projection along with Union only thing
18:36
is we are required to find the right
18:37
table or the right relation and the
18:39
right attributes for the condition
18:41
spring 2020 or fall 2021
18:44
and coming to question number two list
18:46
all the instructors ID who taught
18:49
courses in Spring 2020 or fall 2021 and
18:54
question number two is a bit tricky
18:55
question because we are required to find
18:57
all the instructor IDs but the tricky
19:00
part is the instructor's ID are
19:02
available in multiple relations can you
19:04
see here this instructor relation also
19:06
has ID and coming to teachers relation
19:09
this also has ID and coming to advisor
19:12
relation this also has ID I am not
19:14
bothering about this because this ID
19:15
represents student so what I mean to say
19:18
here is we have instructors ID in
19:20
multiple relations and the challenge is
19:22
to find the appropriate table that
19:25
contains the information about the
19:27
courses that the instructor has thought
19:29
in Spring 2020 semester or fall 2021
19:33
semester I request you to meticulously
19:35
look into the questions and post your
19:37
answers in the comment section and these
19:40
kind of questions will definitely help
19:41
us to crack the competitive examination
19:43
questions
19:44
and that's it guys in today's
19:46
presentation we have focused on the
19:48
union operation in relational algebra in
19:51
the next presentation we will focus on
19:53
the set difference operation which is
19:54
also one of the important operations in
19:57
relational algebra and that's it guys I
20:00	hope you guys enjoyed this presentation
20:01	and thank you for watching
20:04	[Music]
20:04	[Applause]
20:06	[Music]