0:00
Our last operator is the rename operator.
0:03
The rename operator is necessary
0:05
to express certain queries in relational algebra.
0:07
Let me first show the form of the operator and then we'll see it in use.
0:11
The rename operator uses the Greek symbol rho.
0:14
And like all of our
0:15
other operators, it applies to
0:16
the result of any expression of relational algebra.
0:19
And what the rename operator does
0:21
is it reassigns the schema
0:24
in the result of E. So
0:25
we compute E, we get
0:26
a relation as a result,
0:27
and it says that I'm
0:29
going to call the result of
0:30
that, relation R with
0:33
attributes A1 through An and
0:34
then when this expression itself
0:37
is embedded in more complex expression,
0:39
we can use this schema to
0:41
describe the result of the
0:42
E. Again we'll see shortly why that's useful.
0:45
There are a couple of the abbreviations
0:47
that are used in the rename
0:48
operator, this form is the general form here.
0:51
One abbreviation is if we
0:53
just want to use the
0:54
same attribute names that came
0:55
out of E, but change the
0:57
relation name, we write row
0:58
sub R applied to E,
1:01
and similarly, if we want
1:03
to use just the attribute
1:05
names, if we
1:07
want to change, I'm sorry, just
1:09
the attribute names then we
1:10
write attribute list here
1:12
and it would keep the same relation name.
1:14
This form of course has
1:16
to have a list of attributes
1:17
or we would not be able to
1:18
distinguish it from the previous form.
1:20
But again these are just abbreviations
1:21
and the general form is the one up here.
1:24
Okay, so now let's see the rename operator in use.
1:29
The first use of the rename
1:30
operator is something I alluded
1:32
to earlier in this video
1:33
which is the fact that when
1:34
we do the set operators,
1:36
the union, difference, and intersect
1:39
operators, we do expect
1:41
the schemas on the two
1:42
the sides of the operator to
1:43
match, and in a couple
1:45
of our examples they didn't match,
1:46
and the rename operator will allow us to fix that.
1:49
So, for example, if we're
1:51
doing the list of college and
1:52
student names, and let me
1:53
just remind you how we wrote that query.
1:55
We took the C name from
1:56
college and we
1:58
took the s name from students
2:02
and we did the big union of those.
2:04
Now, to make this technically
2:06
correct, these two attribute names would have to be the same.
2:09
So we're just going to apply the rename operator.
2:12
Let's say that we're gonna
2:13
rename the result of this
2:15
first expression to say
2:17
the relation name C with attribute name.
2:19
And let's make the
2:21
result of the second expression similarly
2:24
be the relation C with attribute name.
2:26
And now we have two
2:27
matching schemas and then
2:29
we can properly perform the union operator.
2:31
Again, this is just a
2:32
syntactic necessity to have
2:34
well-formed relational algebra expressions.
2:37
Now, the second use of
2:39
the rename operator is a
2:40
little more complicated and quite
2:43
a bit more important actually which
2:45
is disambiguation in self
2:47
joins and you probably have no
2:48
idea what I'm talking
2:50
about when I say that, but let me give an example.
2:53
Let's suppose that we wanted
2:54
to have a query that finds
2:56
pairs of colleges in the same state.
2:58
Now, think about that.
2:59
So we want to have, for example,
3:01
Stanford and Berkeley and
3:04
Berkeley and UCLA and so on.
3:07
So that, as you
3:09
can see, unlike the union
3:11
operator, we're looking for this
3:12
horizontal joining here. So
3:14
we're going to have to combine essentially
3:16
two instances of the college relation.
3:19
And that's exactly what we're going to do.
3:21
We're effectively going to do college join
3:24
college making the state equal.
3:26
So, let's work on that a little bit.
3:28
So, what we wanna
3:29
do is we wanna have college
3:32
and we want to, let's just
3:34
start with, say, the cross-product of college.
3:38
And then we want to somehow say,
3:40
"Well, the state equals the state."
3:42
But that's not gonna work.
3:43
Which state are these?
3:45
And how do we describe the two instances of college?
3:48
So what we're going to
3:49
do and let me just
3:50
erase this, is we're going
3:52
to rename those two instances
3:54
of colleges so they have different names.
3:56
So we're going to take the first
3:58
instance of college here and
4:00
we're going to apply a rename operator to that.
4:02
And we'll call it C1 and we'll
4:04
say that that has name1, state1, and enrollment1.
4:09
And then we'll take the second instance here.
4:11
We'll call it C2, so N2,
4:14
S2, E2 of college and
4:17
now we have two different relations.
4:19
So what we can do is
4:20
we can take the cross-product
4:22
of those two like that,
4:24
and then we can select where
4:27
S1 equals S2, okay?
4:31
And that gives us pairs of college in the same state.
4:34
Actually, let me show you
4:35
an even trickier, simpler way of doing this.
4:38
Let's take away the selection operator
4:40
here, okay?
4:42
And let's take away this.
4:45
And let's make this into a natural join.
4:48
Now that's not gonna work quite
4:49
yet because the natural join
4:51
requires attribute names to
4:52
be the same, and we don't
4:53
have any attribute names that are the same.
4:56
So the last little trick we're
4:57
gonna do is we're
4:59
gonna make those two attribute names,
5:00
S, be the same.
5:03
And now when we
5:05
do the natural join, it's gonna
5:06
require equality on those two
5:08
S's and everything is gonna be great.
5:11
Okay?
5:11
Now, things are still a little bit more complicated.
5:15
One problem with this query
5:17
is that we are going to
5:18
get colleges paired with themselves.
5:20
So we're going to get from
5:21
this, for example, Stanford Stanford.
5:24
If you think about it, right?
5:26
Berkeley Berkeley, as well as Stanford Berkeley.
5:34
Now, that's not really what we want presumably.
5:37
Presumably we actually want different colleges.
5:40
but that's pretty easy to handle, actually.
5:42
Let's put a selection condition here
5:44
so that the name one is not equal to name two.
5:49
Great. We took care of that.
5:50
So in that case we will
5:51
no longer get Stanford Standford and Berkeley Berkeley.
5:55
Ah, but there's still one more problem.
5:57
We'll get Stanford Berkeley but we'll also get Berkeley Stanford.
6:02
Now, let me
6:05
pause for a moment and see
6:06
if you can think of a
6:07
simple way to solve this problem.
6:10
Actually, there's a surprisingly simple way, kind of clever.
6:13
We're gonna take away this not
6:15
equals and we're going replace it with a less than.
6:17
And now we'll only
6:20
get pairs where the first one is less than the second.
6:23
So Stanford and Berkeley goes away and we get Berkeley Stanford.
6:27
And this is our final query
6:29
for what we wanted to do here.
6:30
Now what I really wanted to
6:32
show, aside from some of the
6:33
uses of relational algebra, is
6:35
the fact that the rename operator
6:36
was for this query absolutely necessary.
6:39
We could not have done this query without the rename operator.