0:00	foreign
0:07	we have seen about the basics of
0:09	relational algebra in this presentation
0:12	onwards let's focus on the various
0:14	relational algebra operations one by one
0:17	let's start with the first relational
0:19	algebra operation the select operation
0:22	before we step into the topic of the day
0:25	the relational algebra the select
0:27	operation let's quickly revisit the
0:30	various relational algebra operations
0:32	basically relational algebra operations
0:34	are classified into fundamental
0:36	operations and additional operations in
0:39	this presentation we are going to focus
0:41	on the fundamental operations and we
0:43
have basically six fundamental
0:45
operations number one the select
0:47
operation number two the project number
0:50
three Union number four set difference
0:53
number five Cartesian product and number
0:56
six the rename operation and in this
0:58
presentation we are going to focus only
1:00
on the First Fundamental operation the
1:03
select operation by waiting let's step
1:06
into the topic of the day the select
1:09
operation why do we need the select
1:11
operation in relational algebra
1:13
basically this select operation selects
1:15
the tuples that satisfy a given
1:17
predicate this select when we give a
1:20
predicate or a condition based on the
1:22
condition it selects all the tuples the
1:25
key Point here is it's going to select
1:27
all the tuples say for example if we
1:30
have a table or a relation and this
1:32
relation has 100 rows or 100 tuples the
1:35
output is going to be tuples that are
1:38
satisfying the given condition or the
1:40
given predicate no worries when we see
1:42
an example you will be able to
1:44
understand things clearly and how it is
1:46
denoted basically the select operation
1:48
is denoted by the lowercase Greek letter
1:51
Sigma and this is the lowercase Greek
1:53
letter Sigma and this is the symbol
1:56
which is used to denote the select
1:57
operation in relational algebra and what
2:00
are the inputs that this is going to
2:02
take
2:02
one input or two input we know very well
2:05
basically a relational algebra is a set
2:07
of operations and it can take relations
2:09
as the input I mean one or more
2:11
relations as input how many relation it
2:14
is going to take it is going to take
2:16
only one input let's see the syntax of
2:19
this select operation the syntax is the
2:22
select the select operator which is the
2:24
lowercase Greek letter Sigma and there
2:27
is a subscript and in the subscript of
2:29
this symbol Sigma we are required to
2:31
provide the predicate or the condition
2:33
and this condition is referred as the
2:35
selection condition because this select
2:38
operator is going to select the tuples
2:40
that satisfy this condition from where
2:42
it is going to select the tuples it's
2:44
going to select the tuples from the
2:46
relation let's see an example then it
2:48
will be easy for you to understand the
2:50
example here is I am going to select all
2:52
the tuples from which relation employee
2:55
relation where here employee is the
2:57
relation and what is the condition or
2:59
the predicate the department ID is equal
3:02
to two so this employee relation may
3:05
contain a lot of tuples but what tuples
3:08
we are selecting all the tuples that are
3:10
matching the condition or the predicate
3:12
the department ID is equal to two and
3:15
this is how we are going to write
3:16
relational algebra expression for which
3:19
operation the select operation and when
3:22
we talk about the condition or the
3:24
predicate what comparison operator we
3:26
have used the comparison operator that
3:28
we have used in this example is is equal
3:30
to right likewise water on the various
3:33
comparison operators that we can use in
3:35
the predicate of the relational algebra
3:37
expression that we are going to use
3:38
using select operation let's see that
3:41
now the comparison operators are is
3:44
equal to which is used to compare the
3:46
left hand side and the right hand side
3:47
not equal to this is also used to
3:50
compare the left hand side and the right
3:52
hand side after all all these are
3:53
comparison operators all these operators
3:56
are going to compare the left hand side
3:57
and the right hand side and how they are
3:59
different this is going to check if both
4:01
are equal this is going to check if they
4:04
are not equal and this is less than this
4:07
is less than or equal to greater than
4:09
and greater than or equal to so these
4:12
comparison operators can be used in the
4:14
predicate of the relational algebra
4:16
expression and in the example what we
4:18
have seen we have seen only one
4:20
condition that is Department ID is equal
4:22
to 2. Let's assume we need more
4:24
conditions to be satisfied I mean from
4:26
the employee relation I want to retrieve
4:28
the list of all employees where their
4:31
Department ID is equal to 2 and also the
4:34
salary is greater than fifty thousand
4:35
dollars in that case we need to ensure
4:38
that both the conditions are satisfied
4:40
isn't it so how multiple conditions can
4:43
be provided in the relational algebra
4:44
expression multiple conditions can be
4:47
provided in a relational algebra
4:48
expression using connectives and the
4:51
various connectives are and or and not
4:54
so this is the operator used to denote
4:57
and this is the operator that is used to
4:59
denote R and this is the negation The
5:01
Not So with this theoretical Basics
5:04
let's step into example number one and
5:06
we will be able to understand things
5:08
clearly let's take an example table here
5:10
the example table that we are addressing
5:13
here is the instructor table which
5:15
contains the attribute the instructor ID
5:17
or simply ID the name the department
5:20
name and the salary so remember what is
5:23
the name of the relation its instructor
5:25
and what are the attributes ID name
5:28
Department name and salary and we are
5:31
talking about what relational algebra
5:33
operation it's the select operation and
5:36
what will be the output of the select
5:37
operation it will be tuples right and
5:40
the Tuple will be containing all four
5:42
attributes ID name Department name and
5:45
salary remember we are not filtering the
5:47
record based on the attribute we are
5:49
retrieving or selecting couples with all
5:52
four attributes ID name Department name
5:55
and salary
5:56
we can't guarantee that our output will
5:58
be containing all tuples but all
6:01
attributes will be there in the output
6:03
that's the key difference between the
6:05
select and the project operation don't
6:07
worry about the project operation now in
6:10
the next presentation we are going to
6:11
exclusively focus on Project
6:14
let's see example number one here the
6:16
example one is right and relational
6:18
algebra expression to find all the
6:21
instructors working in finance
6:23
department so the question is a
6:25
straightforward question we are required
6:27
to write and relational algebra
6:28
expression to find all the instructors
6:31
working in finance department so what we
6:34
are going to retrieve are we going to
6:35
retrieve the instructor ID or instructor
6:37
name or Department name or simply salary
6:39
now we are going to retrieve the
6:41
information about all the instructors
6:43
working in finance department the output
6:46
is going to contain all attributes ID
6:48
name Department name and salary but the
6:50
condition is who are all working in
6:52
which department finance department it
6:55
means the condition is the department
6:57
name should be financed let's see how
6:59
many finance department instructors are
7:01
there
7:02
here is one here is 2. so the output is
7:05
going to contain two tuples these two
7:07
tuples two six five eight nine use of
7:10
Finance ninety five thousand and one two
7:12
five four seven nil Finance eighty
7:14
thousand this is perfect but what we are
7:17
asked we are asked to write a relational
7:19
algebra expression let's solve it now so
7:22
the solution is we are going to select
7:25
isn't it because we are going to select
7:26
all tuples that are matching this
7:28
condition finance department so the
7:30
relational algebra expression is we are
7:32
going to use the select operator and we
7:34
know the subscript of the select
7:36
operator is going to be the condition
7:37
and what is the condition here the
7:39
condition is the instructors working in
7:41
finance department so this Finance is
7:44
actually the department name so I am
7:45
referring this department name attribute
7:47
can you see here the department name is
7:50
equal to the comparison operator and
7:52
this is a text that I am comparing right
7:54
so text that is always enclosed in
7:57
double quotes and what is the constant
7:58
or the value that I am referring Finance
8:01
so the condition is Select Department
8:04
name is equal to finance which is in the
8:06
subscript of this operator select and
8:09
from which relation I want the output
8:10
the instructor relation because I am
8:13
referring to the instructor relation so
8:15
here is the relational algebra
8:16
expression and the output is going to be
8:19
two tuples can you see here 26589 use of
8:23
Finance 95 000 and 12547 nail Finance 80
8:27
000 so the output is containing two
8:29
tuples and if you note here we are
8:32
getting all the attributes ID name
8:34
Department name and salary
8:37
if you are getting confused what
8:38
operator we should use for select and
8:40
project I'll give you a shortcut select
8:42
the word select starts with the alphabet
8:44
s and the letter Sigma starts with the
8:47
alphabet yes so select yes and sigma yes
8:50
so we are selecting all the tuples based
8:52
on this predicate from this relation
8:55
I hope example number one is clear to
8:57
you let's move on to example number two
9:00
and we are going to use the same
9:01
instructor table which we have taken for
9:03
example number one but the question is
9:05
different here the question is find all
9:08
instructors with salary greater than
9:10
eighty seven thousand dollars find all
9:13
instructors have they mentioned all
9:15
instructor IDs no have they mentioned
9:18
all instructor names no what they have
9:20
mentioned all instructors it means from
9:22
the instructor relation all attributes
9:24
ID name Department name and salary so
9:27
let's see the solution now the solution
9:29
is we are going to select this is the
9:31
operator that is used for selecting the
9:33
tuples from the relation so I am going
9:35
to select from which relation the
9:37
instructor relation which is this am I
9:39
going to select all the tuples no I am
9:42
going to select all the tuples that are
9:44
matching with this condition where the
9:46
salary is greater than eighty seven
9:48
thousand dollars can you see here it's
9:50
simply greater than and not greater than
9:52
or equal to so the condition is salary
9:55
column or the salary attribute can you
9:58
see here this salary attribute we need
10:00
to refer the same name here so this is
10:02
the name of the attribute which we are
10:04
referring salary so the condition is
10:06
salary greater than which is greater
10:09
than 87 000 and pause this video for a
10:12
while and think how many tuples will be
10:14
there in the output
10:16
let's see that now is the salary greater
10:19
than 87 000 no here it is yes one so
10:23
this Tuple will be there in the result
10:25
then this one no this one yes this
10:28
double will be there in the result this
10:30
one no and this one
10:32
no because 87 000 is greater than or
10:36
equal to 87 000 is true but what
10:39
condition we have given salary greater
10:41
than 87 000 here 87 000 greater than 87
10:45
000 which is false so this one will be
10:48
false and this one will be true and this
10:50
one will be false so this Tuple will
10:52
also be there on the output so what are
10:54
all the tuples will be getting the Tuple
10:56
pertaining to Robin
10:58
Yusuf and pratik and these three tuples
11:01
will be there in the output let's verify
11:04
that so the output is going to contain
11:06
yes three records or three tuples this
11:09
is one two one two one Robin computer
11:11
science ninety thousand which is this
11:13
and then yusuf's information which is
11:15
this and then pretakes information which
11:18
is this
11:19
and if you note here select selects all
11:22
the attributes right ID name Department
11:23
name and salary from the original
11:25
relation and only it selects the tuples
11:28
that are matching this particular
11:29
condition so we have seen example one
11:31
and example two that are matching only
11:33
one condition let's see an example which
11:36
involves two conditions for this also we
11:38
are going to take the same instructor
11:40
table and the question is find all
11:43
instructors here all instructor means
11:45
all tuples with all attributes who are
11:48
working in finance department and
11:50
drawing the salary greater than 87 000.
11:53
if you note here I am combining example
11:55
one and example 2 here and what is the
11:58
condition the condition is those who are
12:00
working in finance department and this
12:03
is very important and both the condition
12:06
should match not only they are working
12:08
in finance department they must also
12:10
draw the salary greater than 87 000. so
12:14
in this case we are going to use two
12:15
conditions and what connective we need
12:17
here is it and or foreign
12:21
because the condition mentioned here is
12:23
and so the solution is simple we are
12:26
going to select
12:27
from which relation instructor relation
12:29
and what are the conditions the first
12:32
condition is the department name is
12:33
equal to finance can you see here I'm
12:36
enclosing this within double quotes and
12:38
and the second condition is salary
12:40
greater than 87 000 just pause this
12:44
video for a while and think about the
12:46
right answer
12:48
and the output for this relational
12:49
algebra expression is going to be only
12:51
one row because the condition should be
12:54
Department name should be finance and
12:56
salary should be greater than 87 000. so
12:59
in the previous example we have seen
13:00
three records are shortlisted for salary
13:02
greater than 87 000 and the records are
13:05
pertaining to Robin Yusuf and pratik but
13:09
only yusuf's record will be obtained
13:10
here because he is the one who is
13:12
working for finance department and
13:14
drawing the salary which is greater than
13:16
87 000 but in the other cases Robin no
13:20
though he gets the salary greater than
13:22
87 000 he is not belonging to finance
13:24
department and the same case with pratik
13:27
as well and that's why the output for
13:29
this relational algebra expression is
13:31
going to be only one Tuple and that's it
13:34
guys I hope you guys have understood the
13:37
select relational algebra operation in
13:39
the next presentation let's focus on the
13:41
second relational algebra operation the
13:43
fundamental operation which is the
13:45
project before we sign out let's see the
13:47
homework question in the homework I have
13:49
given the same table that we have taken
13:51
for solving our problems in the examples
13:54
in this presentation but it's not a
13:56
single question in the homework we have
13:58
three questions the first question is
14:01
write and relational algebra expression
14:03
to find all the instructors drawing
14:06
salary greater than sixty thousand
14:08
dollars or simply sixty thousand
14:10
question number one is a straightforward
14:12
question where you are going to directly
14:14
apply the select operation with the
14:16
Condition salary greater than 60 000 and
14:19
from which relation the relation is the
14:22
instructor's relation the instructor
14:24
relation which is this and coming to
14:26
question number two the question is
14:28
write and relational algebra expression
14:31
to find all the instructors drawing
14:33
salary between fifty thousand and
14:36
seventy five thousand remember there is
14:39
no exclusive operator to retrieve values
14:41
between one number and another number
14:43
but we can easily handle the situation
14:45
with the existing operators and coming
14:48
to question number three write and
14:50
relational algebra expression to find
14:52
all the instructors in the instructor
14:54
relation but actually question number
14:56
three is tricky when compared to
14:58
question number one and two because when
15:00
we want to list all the columns from the
15:02
instructor relation we can go for
15:04
project which we are going to see in the
15:06
next presentation but here what we are
15:08
required we are required to write a
15:10
relational algebra expression to find
15:12
all the instructors in the instructor
15:14
relation I mean this relation now
15:17
obviously we need to give the condition
15:19
because select will not work without a
15:21
condition but what condition we will
15:23
give in order to retrieve all the
15:24
records that's the tricky part here I'll
15:27
give you a generic condition where
15:29
salary greater than one in this case
15:31
obviously all the salary values will be
15:33
great than one but I don't want that
15:36
condition to be in the answer
15:37
take another field in the condition and
15:40
ensure that all the records or all the
15:43
tuples are listed in the output relation
15:45
so the task for you is solve all these
15:47
three questions and post your answer in
15:50
the comment section I hope you guys
15:52	enjoyed this presentation and thank you
15:54	for watching
15:55	[Music]
15:56	[Applause]
15:58	[Music]