This series of videos covers the topic of modifying views.
Now, I have to admit that
the amount of coverage is
a bit disproportionate compared to defining and using views.
Commonly, people only define
views and use them and don't
try to modify them in applications,
but when views are modified
the issues become a bit tricky.
So, it is important to cover the topic.
First, a reminder of why people use views.
They use them to hide data from some users, so for authorization purposes
they make some queries easier,
they help you modularize
your database applications, and, as
I've said several times, our
real applications do use lots and lots of views.
Now, querying views is
relatively straight forward, as we've seen.
Once a view has been
defined and given a name, say
V, then, in our
queries over the database, we
can reference V as if it were a regular table.
What happens underneath is that
when we reference V in a
query, it's rewritten to use
the tables over which V
was defined, and we'll call those the base tables.
Those can actually be regular tables or they can be other views.
Now, what about modifying views?
Once a view has been defined,
can we modify it just like it's any table in the database?
Well, in one way that
doesn't make sense because V
isn't stored, so it's not
a table that we can go in
and update, insert tupples,
modify tupples, or delete tupples.
But, in another way, it absolutely
has to make sense, because
the idea of views is that
some users or application developers
are seeing those views as their
only view of the database.
We saw that three-level
idea of databases.
We have the physical layer, the
disc, the conceptual layer, the
relation, and then the logical
layer which again, some applications
see as their access to the database.
Well, our solution is actually parallel to what we do with queries.
When we have a modification command
that involves a view V, we'll
rewrite that modification command to
modify instead the base
tables over which V is defined.
If those base tables happen to
be views themselves that we have
a recursive process of rewriting
those modifications to further go
down to, until we get
to the base tables that are actually
the tables stored in the database.
So maybe this is no big deal.
We saw in our demo that
modifying queries that reference
views into queries that
reference the base tables is
a relatively straight-forward process.
Well, I am going to say
right up front, it's not quite
as straight-forward when we are talking about modifications.
So let's draw a picture to see what's going on.
Let's say we have our
view V here, and V
is defined based on a
query over its base tables.
And for now, let's just assume that those are stored actual relations.
So it's defined over relations R1 to RN.
Now someone comes along
and they want to modify V.
Now V is just a logical
concept but, the user
thinks of V as a table,
so they write a modification
command so that would be say,
insert, delete, or update command using SQL language.
And they're imagining that V is
a stored table, so they're
imagining that the result
of that modification command is
going to be a new table V prime.
What needs to happen
is down here at the
bottom, that modification, that
imaginary modification to V,
has to be translated to actual
modifications on the base
tables R1 through RN.
So now, we modify one or
more of the base tables to
R1 prime through RN prime.
And now the idea is
that our imaginary V prime
then is the same query.
It's the result of this same
query, V's definition over the
new value, the new R1
prime through RN prime, the updated base tables.
So, if we can always figure
out how to translate this modification
up here into the modifications
down here so that the
square diagram holds so that the
resulting modifications here give
us the effect we wanted upstairs,
then, we're in good shape.
So, the question is can we
always perform this translation,
the modifications, so the square diagram holds.
And the answer is, actually
usually, yes.
Usually there is a translation
that works for us,
and we'll see some examples of that in our demos.
The problem actually is that
there's often many such translations,
and so we don't know actually which one the user intended.
So let me give an extremely simple example.
Let's suppose that our relation,
R, has two attributes,
A and B, and our
view, V, is defined as
the projection on A of
R.  Let's say that
our current contents of relation
are just one tuple to tuple 1-2.
In that case, the current contents
of view V are just the tuple 1.
Now let's say the user comes
along, it's a user who is
operating on the database through view
V, and they say insert
into view V please the tuple three.
So we need to translate that
insertion, which is up here,
into insertion or some
modification on the base
tables so that the
view will, when we're done,
contain the tuples one and three.
Well, we can certainly do that.
The problem is, what exactly do we insert?
We could insert for example 3-1.
We could insert 3-2 and so on.
So there's actually an infinite
number of translations that will
create the tuple three in the view.
Here's an even more extreme example.
Let's suppose we have a
relation with one attribute
and our view be
the average value of
that. So if, for example,
our relation has the values
135, then the average at this point would be three.
Now let's say that the user
comes along and says let me update that average.
I'm gonna set that average to be seven.
Well how do we update the
base data so that its average is now seven.
Well, as you can imagine, there
are many, many, many, actually
an infinite number, of ways to
update the base data so that the view average would now be seven.
So that's the crux of the problem.
How do we decide which modifications
to make to the base tables
so that we get the desired modification to the view?
Correctness is not so
hard to achieve but resolving
ambiguity can be.
Existing systems have actually taken
a fairly different approach as to this problem.
Again, to specify the problem
we have modifications specified on
a view V. We need to
rewrite those to modify the
base tables so that when
we have our view of
over new base tables that
reflects the desired new state of the view.
One approach is that the
view creator actually specifies that rewriting process.
So they will say, you know, when somebody
tries to insert into the view,
here's what we need to do
on the base tables, if they
try to delete here's what we do, and update here's what we do.
So the positive of this
approach is that all modifications
can be handled, because the
view creator is going to specify
exactly what happens in the case of those modifications.
The downside is that there's
no guarantee of correctness, meaning there's
no guarantee that that square
diagram is adhered to, and
we'll see examples in our
demo of where it's not,
or even that the translations are meaningful.
So we're relying on the view
creator to create correct
and meaningful translations explicitly.
The second approach is to
restrict the views and
modifications that are allowed
so that the system can automatically
perform the translation into modifications
on the base tables that are
correct, meaningful and and unambiguous.
So the plus now, of
course, is there's no user intervention
and the result is going to be correct.
The downside is that the
restrictions on the view
and the modification combinations are
fairly significant, as we'll see.
So, the first approach is actually
enabled by a type of trigger
that's known as, "instead of."
I alluded to those triggers
briefly in the trigger video
but here is where we are actually going to see them in action.
In postgres, there's a concept called "Rules," that's very similar.
The second approach is actually
the one that's adopted
by the SQL Standard, and the
SQL Standard gives a very
rigorous limitations on what views can be modified.
Systems vary in what they implement.
Most of them are actually a
little bit more flexible than the
standard, and we'll see that in upcoming demos as well.