0:02
[Music]
0:11
hello friends welcome back to our
0:13
channel so in today's session we'll
0:15
discuss about the secondary operations
0:17
of relational algebra in dbms
0:23
set
0:24
theory operations
0:30
in dbms
0:34
so first of all
0:35
let us list out what are the operations
0:37
what are the set theory operations
0:39
available and then we will see the
0:41
syntax and we'll see the example right
0:44
so first the first one is a union
0:48
operation
0:49
okay and it is denoted as the symbol u
0:53
and next intersection
0:57
intersection
0:59
it is denoted as n
1:02
next
1:05
set difference
1:09
or
1:10
we can call it as a minus
1:14
and the last one
1:17
cross product
1:20
cross product
1:22
so these are the
1:24
set theory operations available in
1:26
relational algebra in dbms
1:28
now
1:29
what is the input for all these
1:31
operations
1:32
so input for applying all these
1:34
operations are
1:36
two relations
1:38
two relations so here in the relational
1:42
model a relation is nothing but a table
1:44
okay so here we have we are supposed to
1:47
give a two tables as a input and
1:50
after applying these operations
1:52
say theory operations will get
1:55
one relation
1:57
one relation that means
1:59
we have to apply this section operations
2:01
on two tables so that will get the
2:04
result as of one table
2:06
right so for this we require two tables
2:09
so first let me
2:11
give the two tables
2:13
so i'll take a
2:16
course id
2:17
a course name only two fields a simple
2:20
example so let us take one table
2:25
so course id one zero one let it be c
2:28
plus plus
2:29
one zero two let it be dbms
2:33
one zero three let it be python
2:37
this is one table
2:39
and let us take one more table
2:44
so course id
2:46
course knee
2:50
so
2:51
course id
2:52
1 0 1
2:53
c plus
2:55
1 0 4
2:57
java
2:58
1 0 5
3:02
c
3:05
right so let us take these two tables
3:07
let it be the relation r let it be the
3:09
relation yes the name of this relation
3:12
is r and the name of the relation is s
3:14
now we are supposed to apply the set
3:16
theory operations on these two relations
3:20
so let us see
3:21
first
3:22
coming with the union
3:24
coming with the union so union means it
3:27
will return all the tuples of both the
3:30
relations
3:31
and
3:32
it will automatically deletes the
3:35
duplicates
3:36
so
3:38
resultant
3:43
relation
3:45
is our
3:47
union
3:48
yes
3:49
so this r union s will be having
3:53
all the tuples
3:55
all the tuples we can call it as tuples
3:57
or records or entities whatever it may
3:59
be all the tuples of
4:01
r
4:02
and
4:03
yes
4:05
okay it will contain all the tuples of
4:07
both the relations r and s and the
4:10
second important point is
4:14
second important point is
4:18
automatically
4:23
it will
4:25
remove
4:29
duplicates
4:31
it will remove the duplicates so if you
4:33
observe here in this example the
4:35
relation r is having
4:37
3 rows or 3 tuples
4:40
and s is having
4:42
three rows or three tuples
4:45
right so what is the resultant
4:47
of these two that means our union s will
4:50
be having
4:51
again
4:53
five rows because you can observe one
4:55
zero one
4:57
is a
4:58
common tuple so it will be deleted okay
5:02
there will be no duplication
5:05
so
5:05
the result
5:07
of this one
5:09
will be
5:11
so course id and a course name
5:15
and you will get one zero one with a c
5:17
plus plus
5:19
one zero two
5:20
with a dbms
5:24
zero three
5:25
with the python
5:29
one zero four
5:30
with java
5:32
one zero five with a c
5:35
so this will be the resultant set that
5:37
is r
5:38
union yes
5:40
so here you can observe one not one
5:43
course id one zero one that with the c
5:45
plus plus is a common tuple in both the
5:48
relations
5:49
so it should not be repeated here so the
5:52
one property for this union is it will
5:54
automatically it removes the
5:57
duplicates it automatically removes the
5:59
duplicates
6:02
right so hope you understood
6:05
okay so ah in the previous session we
6:08
have seen the unary operations like
6:09
selection and projection so simply you
6:11
can write it as
6:14
selection of
6:16
our
6:17
union
6:18
selection of yes so this is nothing but
6:20
our union operation
6:23
this is nothing but our union operation
6:26
right so next
6:28
next
6:30
the next one is
6:31
intersection
6:38
intersection and it is denoted as n so
6:41
the resultant relation will be
6:43
r
6:44
n s
6:46
so it will what is the what are the
6:49
tuples that are included in our
6:51
intersection yes
6:53
so all the tuples
6:59
that are
7:02
common
7:05
in
7:07
both
7:08
r
7:09
and
7:10
yes
7:11
so it will return the tuples
7:16
it will return the tuples which are
7:18
common okay which are common
7:21
so now you can observe
7:24
see again it will delete the duplicates
7:27
okay so what is the common relation you
7:29
can observe 1 0 1 and c plus plus is
7:31
available in both the relations
7:34
so
7:36
on
7:37
intersection yes so 1 0 1
7:40
[Music]
7:42
okay 1 0 1 and c plus plus
7:46
will be available in the resultant
7:50
relation right so 1 0 2 it is available
7:52
in r but not is not available in yes so
7:55
you should not consider 1 0 2
7:57
right one zero three which is available
7:59
in r but not in yes
8:01
yeah one zero one which is available in
8:04
s
8:04
and also available in r so one zero one
8:08
one zero four which is available in s
8:10
but not in r so you should not
8:12
include
8:13
so this is should be included this
8:15
should be included so 1 0 5 which is
8:17
available in s but not in r so this is
8:19
also not included so what is the result
8:22
so result is the common
8:25
tuple or common record which is
8:27
available in both the relations r and
8:30
yes
8:31
so that is called the
8:33
intersection so you can observe here one
8:34
not one is available here so we get one
8:37
on one c plus plus again when you
8:38
compare with this second relation again
8:41
we are having one zero one with the c
8:42
plus plus so we should not include this
8:45
one so it will remove all the duplicates
8:47
only it will give the common
8:50
common tuples which are available in
8:52
both the relations r and yes
8:55
that is about the intersection
8:58
then
8:59
then the next one is
9:01
minus all
9:05
set difference
9:07
we'll see
9:17
so this is a r
9:18
and this is an s
9:20
so next one is
9:23
set difference
9:27
or
9:28
minus
9:30
so it is represented with a minus symbol
9:32
it is represented with a minus symbol
9:34
so
9:35
here the resultant
9:38
resultant
9:40
relation
9:42
resultant relation will be
9:45
r minus
9:46
s
9:48
resultant relation will be r minus s so
9:51
what is available in r minus s
9:54
r minus s so it will contain
9:57
all the tuples
9:59
all the tuples
10:01
of relation r
10:05
of relation r
10:07
but not
10:09
in
10:11
relation yes
10:14
right so it should contain all the
10:15
tuples of relation are but not in
10:18
relation yes so we have to consider
10:20
those relations so if you observe here
10:24
what is the resultant here so
10:26
let us take
10:27
c id
10:29
and course name the course id and course
10:31
name
10:32
and you can observe here
10:34
let us consider one zero one so which is
10:37
available in yes so we should not
10:39
consider this one zero one
10:41
right one zero two it's not available so
10:44
one zero 2
10:46
the course name is dbms
10:48
103 which is available in r but not in
10:51
yes you can observe here so
10:53
1 0 3
10:55
with a python
10:57
so this will be the result of r
11:01
minus yes so it contains the tuples of
11:04
relation r but not in yes
11:07
so here 1 0 1 is available so
11:10
1 0 1 should not be included here
11:12
and similarly you can consider
11:16
s minus r
11:18
s minus r means it should contain all
11:20
the tuples of relation yes but not in r
11:25
so let us take here
11:28
so course id and course name
11:36
see
11:36
1 0 1
11:38
which is available in r so we should not
11:40
consider this 1 0 1 1 0 4 which is not
11:42
mlb here so 1 0 4
11:45
with a name java 1 0 5 is also not
11:48
available in r
11:49
okay 1 0 5 c so this will be
11:55
s minus r and this will be r minus s
11:59
r minus s so the symbol is minus and it
12:02
is a set difference so it will remove
12:05
all the common elements of s from r
12:08
okay it will remove the elements of s
12:10
from r
12:11
right so it will it will give all the
12:14
tuples of r
12:16
which are not available in yes which are
12:18
not available in yes
12:21
right so hope you understood this one
12:23
set difference on minus
12:25
and the last one
12:27
that is
12:36
set
12:37
sorry cross product or a cartesian
12:39
product
12:42
see
12:43
let us take the student id
12:47
yes id
12:54
and s name
12:57
yes name right so let it be the student
13:00
id is
13:01
s1
13:03
the name is
13:07
sunday
13:08
and student 2
13:13
let it be this is a r relation r this is
13:15
a relation yes
13:16
now the next one is
13:19
cross product
13:22
cross product or cartesian product we
13:24
can also call it as a cartesian product
13:26
and it is displaced it is represented as
13:28
a cross into one
13:30
so the resultant relation
13:33
resultant relation
13:37
will be
13:38
r
13:39
cross
13:40
s
13:41
or cross s so what will be available in
13:44
r cross s
13:45
so every tuple
13:48
every triple
13:50
of r
13:52
is
13:53
associated with
13:56
is associated with
13:58
every tuple
14:03
every tuple of
14:05
yes
14:05
every tuple of yes so every tuple of r
14:08
is associated with every tuple of yes
14:11
now
14:13
here if r is having
14:18
three rows
14:20
and s is having
14:22
two rows
14:23
so the resultant r cross yes will be
14:26
having
14:27
three into
14:28
two rows
14:31
so total
14:32
six rows because every tuple of r will
14:35
be associated with every tuple of yes
14:38
see what is the result let us take this
14:40
one so c id
14:42
c name
14:44
and s id
14:47
yes name
14:48
so this will be the resultant
14:51
relation
14:52
this will be the resultant relation
14:55
see how the cross product or a cartesian
14:58
product will be applied
15:00
so every tuple of
15:02
r that means 1 0 1
15:05
c plus plus is associated with a student
15:08
1
15:09
name
15:10
sunday similarly
15:13
every tuple of r that means one zero one
15:18
c plus plus is associated with s1
15:21
name is
15:22
solid
15:24
right so every tuple of r is associated
15:27
with every tuple of s so one not one c
15:30
plus plus s one and sunday one r one c
15:32
plus plus s two and s r away similarly
15:35
one zero two
15:38
dbms
15:40
s1 sorry this is s2 right s1
15:46
similarly 102
15:49
bbms associated with student 2
15:53
named sarah
15:55
similarly the third one 103
15:59
python
16:01
is associated with the student 1
16:04
sandi
16:06
103
16:09
python
16:11
student 2
16:19
so
16:20
this is called the cartesian product r
16:23
cross yes
16:26
cross product or cartesian product okay
16:28
every tuple of first relation will be
16:31
associated with every tuple of
16:34
second relation
16:35
so this is called the
16:37
cross product or cartesian product
16:40
so these are all the
16:42
set operations
16:44
of relational algebra in dbms union
16:48
intersection
16:49
set difference and cross product
16:53
right so hope you understood this one so
16:56
let's stop here and if you are having
16:57
any doubts regarding any one of these
16:59
set of set theory operations feel free
17:01
to post your doubts in the comment
17:02
section definitely i will try to clarify
17:04
all your doubts if you really enjoyed my
17:06
session like my session share my session
17:08
with your friends and don't forget to
17:10
subscribe to our channel thanks for
17:12
watching thank you very much