[MUSIC]. 
Okay, so we talked about physical data 
independence, and we talked about 
algebraic optimization. 
I want to talk about another kind of data 
independence, which is logical data 
independence. 
And so you know, we argue that physical 
data independence was this ability to 
insulate applications and protect 
applications from changes in the physical 
organization of the data. 
Alright, so things were rearranged on 
disk. 
We want, we don't want to have to rewrite 
all the code in the application. 
And this is what databases provide and 
relational degrees in particular, do a 
great job of providing this. 
But if you go back to Ted [UNKNOWN] first 
paper and the quote, even the quote I 
gave you. 
He talks about, you know, insulating 
applications from the internal changes of 
representation, changes to the internal 
representation. 
But also insulating applications from 
changes to some forms of external 
representation. 
And what he means by external is things 
like adding a column to a table. 
So this isn't an internal shuffling of 
the bits on the disk. 
It's actually a logical change to the 
table, there's more data there then there 
was before. 
But you know if you think about it, if 
your code doesn't care about that new 
column you shouldn't have to rewrite just 
because there is a new column. 
Okay. 
So the ability to provide this logical 
data independence is provided by this 
concept of views. 
And all relational databases have this 
concept, and somewhat surprisingly, I 
find it to be somewhat underused in 
practice. 
Right? 
And while 
Okay. 
So if your using them right now. 
If you know what they are great. 
If you, if your using them even better. 
If you used databases, but have never 
heard of views than this, this is a great 
time to, to learn about them. 
Okay? 
So what is a view? 
A view is just a query with a name. 
So I write a query. 
I give it a name. 
And I put it in the database. 
Base. 
Now, I can then access that view as if it 
was a table in the underlying database 
itself, as if it was a physical table. 
So why can we do this? 
Well, I talked about this notion of 
algebraic closure before, right. 
We, so, and, and you're in, this is 
exactly what empowers, what allows us to 
do this. 
So we know that every query returns a 
table. 
Right? 
We take tables on the input, we do some 
manipulation of them and we produce 
tables. 
So we say that the language is 
algebraically closed. 
And so any, any result of a view will 
always be something that we can then add 
other queries on. 
So we can stack queries on top of 
queries, on top of queries, on top of 
queries, on top of queries. 
Okay? 
So why might we want to do this? 
So one reason is to protect the 
underlying data, you can assign 
permissions to tables. 
So for example, if you only want a 
particular user to see data associated 
with their account you can write a view 
that filters everything out except for 
their account. 
And then you grant the maxis to their 
view. 
The most direct benefit of this is that 
it, it allows you to expose data 
according to a logical organization that 
makes sense for the user. 
So even things as simple as hiding some 
join, if you decide to reorganize your 
data into two tables, requiring that 
programmers use joins to link them back 
up again. 
You can simply write a view that hides 
that join and let everyone access the 
result of it. 
Now, maybe this, this may sound extensive 
but the cool trick here is that, because 
of this Algebraic closure. 
What happens is, the user's query gets 
composed with your query that defines the 
view. 
And the whole thing gets sent as one big 
block to the database for evaluation. 
So the database simply doesn't care 
whether it came as a view and then user 
query. 
Or whether it came all as one, all as one 
query directed from the programmer. 
It's going to optimize it the exact same 
way. 
Okay, so this is, there's nothing but a 
benefit here. 
Okay. 
So let's see an example. 
So given the schema purchase and product. 
Define a view called StorePrice with two 
columns store and price that has this 
definition. 
So select store and select price from 
purchase and product where the product 
ids are equal. 
This is a little funny because we, we 
didn't put p, we didn't put pids here, so 
this is, this is a little bit wrong. 
So assume that each one of these has a, 
let's assume that this is pid, and assume 
that this is also pid. 
And then it matches the query down here. 
Okay, so this result is now like a new 
table and just like I said a second ago 
you've now hidden the join from the 
users. 
And so, complexities like these column 
names perhaps, you can insulate your 
users from and you can name them whatever 
you want. 
And so this allows you to put these, this 
is what this logical data independence 
means, is that. 
No matter how I want to logically 
organize my tables, I can, I can expose a 
different perspective on the data then I 
want to have myself. 
And so this separates the people who are 
administering the data from the ones who 
are actually accessing it. 
Okay, logical data independence, key 
idea, alright? 
Alright, so how do we use a view? 
Well as I said, all you have to do is 
reference the view in a query just like 
it's a table and so here, if we want to 
find the notion of a high end store? 
And we say well, that's any store that 
has sold some product over $1000 dollars. 
And you know for each customer we may 
want to find all the high end stores that 
they visited. 
Well, being able, being able to directly 
reference the store-price relation that 
we defined in the previous slide, this 
view helps simplify this query. 
Right? 
And so you can just write a query that 
directly accesses that view as if it were 
a table. 
And, okay, so how is this actually 
evaluated? 
Well, that actually, oops, that's 
actually what's really fantastic about 
databases is that this query will just be 
folded together with the view definition. 
And passed to the database where the 
whole thing is optimizes in, in one go, 
optimized in one go. 
So you don't need to worry about the 
difference between having a stack of five 
views and all being compiled toget/g, are 
all being folded together as one query. 
The database doesn't care. 
It's going to translate the whole thing 
into one big query, exchange that for an 
algebraic expression. 
And then do the normal optimization 
procedure to come up with the best 
possible plan. 
So it's basically like free abstraction. 
Right? 
It simplifies things for the, for the 
programmer, without any kind of 
performance cost. 
Okay. 
Now, you can actually get better 
performance than writing the whole thing 
by hand. 
to you it's equivalent to writing the 
whole thing by hand. 
You don't pay a penalty. 
But you guys should do better than that 
with views and in some cases by 
materializing views. 
And we're not going to talk too much 
about that because that's, sort of, is 
very specific to databases. 
And we don't see it quite as often in 
this broader context of data science that 
we're trying to talk about. 
but it's a good trick. 
And once you have the mechanism to store 
views, you can essentially cache the 
results, and that's what we call 
materialization. 
Okay. 
So the last key idea I want to convey 
about databases is that of Indexes. 
So while Indexes are certainly not unique 
to databases. 
Databases are perhaps unique as a 
platform that can make them very easy to 
apply and deploy and automatically take 
advantage of. 
And so databases are especially but not 
exclusively effective in, sort of needle 
in the haystack problems. 
Looking up individual records or small 
amounts of records, from large data sets. 
They do other things very well, too, but 
this is one thing that makes, that, that 
they're quite good at. 
and the reason is that they can apply, 
that you can apply indexes. 
This second needs a little bit of 
context, but what I mean here is that if 
you're trying to write code to do this 
yourself. 
In say some programming language like 
Python or C or R. 
You're going to be a slave to what sizes 
of data fit in the main memory, as we 
said before. 
Now you can absolutely be clever and 
start bringing in one chunk of data at a 
time in the memory, processing it. 
Putting it out to disk and bringing in 
the next set and so on. 
But the code will very quickly become 
very, very complex. 
This is something the databases already 
know how to do. 
And so your query will always finish 
regardless of database size, as long as 
it fits on disk. 
Right? 
It doesn't, it doesn't matter how much 
memory you have available, it will 
eventually finish. 
May or may not be that fast. 
They already know how to take advantage 
of main memory in this optimal way. 
And it's, and you know, it's not easy. 
Right? 
It's a pain in the butt to try to code 
that yourself. 
Okay. 
So effective use of, of the memory 
hierarchy, effective use of indexes. 
These are things the databases can do 
well. 
It's a great platform for applying these, 
these tricks. 
And so, finally, you know, this is what I 
mean here, is that the indexes are easily 
built and automatically used by the, by 
the optimizer. 
So to create an index you write, you can 
write a statement like this. 
here I've changed the scheme on you once 
again but here we're sort of filtering on 
genetic sequences. 
And if I create this index, then this 
query will. 
You know, this qu, this, this very simple 
query is looking for all sequences that 
match a particular value. 
It will automatically take advantage of 
that index if it's there. 
You don't have to tell it to do anything 
you write the exact same query. 
Okay.