0:00
Now we've seen all the operators of relational algebra.
0:03
Before we wrap up the
0:04
video I did want
0:05
to mention that there are
0:06
some other notations that can
0:07
be used for relational algebra expressions.
0:10
So far we've just been writing
0:11
our expressions in a standard form
0:12
with relation names and operators
0:14
between those names and applying to those names.
0:17
But sometimes people prefer to
0:18
write using a more
0:19
linear notation of assignment statements
0:22
and sometimes people like to write the expressions as trees.
0:25
So I'm just gonna briefly show a couple of examples of those and then we'll wrap up.
0:29
So assignment statements are a
0:30
way to break down relational
0:32
algebra expressions into their parts.
0:34
Let's do the same query we
0:36
just finished as a big
0:37
expression which is the
0:38
pairs of colleges that are on the same state.
0:40
We'll start by writing two assignment
0:42
statements that do the rename
0:43
of the two instances of the college relation.
0:47
So we'll start with
0:48
C1 colon equals and we'll
0:49
use a rename operator and now
0:51
we use the abbreviated form that just lists attribute names.
0:54
So we'll see say C one,
0:56
S, E one of
0:58
college and we'll similarly
1:01
say that C2 gets the
1:04
rename, and we'll call it
1:05
C2SE2 of college,
1:08
and remember we use the
1:09
same S here so that we can do the natural join.
1:12
So, now we'll say
1:14
that college pairs gets C1
1:17
natural join C2, and
1:19
then finally we'll do our selection condition.
1:21
So our final answer will be
1:23
the selection where N1 is
1:26
less than N2 of CP.
1:29
And again, this is equivalent to
1:30
the expression that we saw on the earlier slide.
1:33
It's just a notation that sometimes
1:34
people prefer to modularize their expressions.
1:38
The second alternate notation I'm going to show is expression trees.
1:42
And expression trees are actually commonly used in relational algebra.
1:45
They allow you to visualize the structure
1:47
of the expression a little bit better.
1:50
And as it turns out when SQL
1:51
is compiled in database systems,
1:53
it's often compiled into an
1:54
expression tree that looks very
1:56
much like what I'm gonna show you right now.
1:58
So for this example let's suppose
2:00
that we want to find the
2:01
GPAs of students who are applying to CS in California.
2:05
So that's going to
2:06
involve all three relations because
2:08
we're looking at the
2:09
state is in California, and
2:11
we're looking at the student GPA's
2:13
and we're looking at them applying to CS.
2:16
So what we're going
2:17
to do is we're going to
2:18
make a little tree notation here
2:20
where we're going to first do
2:22
the natural join of these three relations.
2:24
So technically the expression
2:26
I'm going to show you is going to stop down here.
2:27
It's not going to actually have the tables.
2:29
So the leaves of the expression are
2:30
going to be the three relations: college, students, and apply.
2:33
And in relational algebra trees, the
2:35
leaves are always relation names.
2:38
And we're going to do the natural
2:39
join of those three which
2:40
as a reminder enforces equality of
2:43
the college name against the
2:45
college name here against the
2:46
college name here, and the
2:47
student ID here and the student ID here.
2:49
That enforcement means that we
2:50
get triples that are talking
2:52
about a student applying to a particular college.
2:54
And then we're going to apply to
2:57
that, and so that's going to
2:58
be written as a new note above this one in the tree.
3:01
The selection condition that says
3:03
that the state equals California and the major equals CS.
3:12
And finally, we'll put on
3:13
top of that the projection that gets the GPA.
3:17
okay?
3:18
Now actually this expression is
3:20
exactly equivalent to if
3:21
we wrote it linearly, project the
3:23
GPA, select etc. of
3:25
the three college join student, join apply.
3:28
I'm just abbreviating here.
3:30
That would be an equivalent expression.
3:31
But again, people often like
3:33
to use the tree notation
3:35
because it does allow you to
3:36
visualize the structure of the
3:38
expression, and it is used
3:39
inside implementations of the SQL language.
3:43
Let me finish up by summarizing relational algebra.
3:46
Let's start with the core constructs of the language.
3:50
So a relation name is
3:52
a query in relational algebra,
3:54
and then we use operators that combine relations and filter relations.
3:58
So we have the select operator
3:59
that applies a condition to the result of an expression.
4:03
We have the project operator that
4:05
gives us a set of
4:06
attributes that we take from the result of an expression.
4:10
We have the expression one
4:12
cross-product expression two.
4:15
And again those can be any expressions.
4:17
Then we have expression one
4:19
union expression two.
4:22
And we have expression one minus expression two.
4:25
And finally we have
4:27
the rename operator that takes
4:30
an expression and renames the
4:31
result of that, the
4:33
schema in the result of that expression.
4:38
Now, you probably noticed that
4:40
I skipped a few of our
4:41
favorite operators, but this
4:42
is the core of the language
4:44
and all the other operators are
4:46
actually abbreviations that don't
4:47
increase the expressive power of
4:49
the language but they can be very useful performing queries.
4:52
And the abbreviations that we
4:54
learned were expression one
4:56
natural join expression two.
4:59
They were expression one
5:01
theta join expression two.
5:04
And, finally, expression one intersect expression two.
5:08
All of those where we had
5:09
a method of rewriting them using the core operators.
5:13
Just a small aside about parentheses.
5:15
A parentheses are used in
5:16
relational expressions for, relational
5:18
algebraic expressions, for disambiguation, similarly to arithmetic expressions.
5:23
I was a little cavalier about whether
5:24
I included parentheses or not,
5:26
but as you write your relational
5:28
algebra expressions you will
5:29
see that it's pretty straightforward
5:31
to figure out when disambiguation is needed.
5:34
So to conclude relational algebra,
5:36
entirely it's a formal language.
5:38
It's based on sets, set
5:39
operators and other operators
5:41
that combine data from multiple relations.
5:43
It takes relations as input,
5:45
it produces relations as answers
5:47
and it does form the formal
5:49
foundation of implemented relational database management.