1 00:00:00,001 --> 00:00:07,855 [MUSIC]. 2 00:00:07,855 --> 00:00:11,393 Okay, so we talked about physical data independence, and we talked about 3 00:00:11,393 --> 00:00:14,761 algebraic optimization. I want to talk about another kind of data 4 00:00:14,761 --> 00:00:18,060 independence, which is logical data independence. 5 00:00:18,060 --> 00:00:21,772 And so you know, we argue that physical data independence was this ability to 6 00:00:21,772 --> 00:00:25,600 insulate applications and protect applications from changes in the physical 7 00:00:25,600 --> 00:00:30,446 organization of the data. Alright, so things were rearranged on 8 00:00:30,446 --> 00:00:32,744 disk. We want, we don't want to have to rewrite 9 00:00:32,744 --> 00:00:36,270 all the code in the application. And this is what databases provide and 10 00:00:36,270 --> 00:00:39,620 relational degrees in particular, do a great job of providing this. 11 00:00:40,800 --> 00:00:44,454 But if you go back to Ted [UNKNOWN] first paper and the quote, even the quote I 12 00:00:44,454 --> 00:00:47,764 gave you. He talks about, you know, insulating 13 00:00:47,764 --> 00:00:52,024 applications from the internal changes of representation, changes to the internal 14 00:00:52,024 --> 00:00:55,986 representation. But also insulating applications from 15 00:00:55,986 --> 00:00:59,266 changes to some forms of external representation. 16 00:00:59,266 --> 00:01:04,470 And what he means by external is things like adding a column to a table. 17 00:01:04,470 --> 00:01:07,410 So this isn't an internal shuffling of the bits on the disk. 18 00:01:07,410 --> 00:01:10,644 It's actually a logical change to the table, there's more data there then there 19 00:01:10,644 --> 00:01:13,065 was before. But you know if you think about it, if 20 00:01:13,065 --> 00:01:15,639 your code doesn't care about that new column you shouldn't have to rewrite just 21 00:01:15,639 --> 00:01:21,430 because there is a new column. Okay. 22 00:01:21,430 --> 00:01:25,526 So the ability to provide this logical data independence is provided by this 23 00:01:25,526 --> 00:01:29,589 concept of views. And all relational databases have this 24 00:01:29,589 --> 00:01:33,495 concept, and somewhat surprisingly, I find it to be somewhat underused in 25 00:01:33,495 --> 00:01:35,360 practice. Right? 26 00:01:35,360 --> 00:01:39,800 And while Okay. 27 00:01:39,800 --> 00:01:41,990 So if your using them right now. If you know what they are great. 28 00:01:41,990 --> 00:01:45,499 If you, if your using them even better. If you used databases, but have never 29 00:01:45,499 --> 00:01:50,180 heard of views than this, this is a great time to, to learn about them. 30 00:01:50,180 --> 00:01:51,290 Okay? So what is a view? 31 00:01:51,290 --> 00:01:55,080 A view is just a query with a name. So I write a query. 32 00:01:55,080 --> 00:01:57,032 I give it a name. And I put it in the database. 33 00:01:57,032 --> 00:02:01,676 Base. Now, I can then access that view as if it 34 00:02:01,676 --> 00:02:07,660 was a table in the underlying database itself, as if it was a physical table. 35 00:02:07,660 --> 00:02:10,420 So why can we do this? Well, I talked about this notion of 36 00:02:10,420 --> 00:02:14,190 algebraic closure before, right. We, so, and, and you're in, this is 37 00:02:14,190 --> 00:02:17,610 exactly what empowers, what allows us to do this. 38 00:02:17,610 --> 00:02:20,710 So we know that every query returns a table. 39 00:02:20,710 --> 00:02:22,715 Right? We take tables on the input, we do some 40 00:02:22,715 --> 00:02:25,960 manipulation of them and we produce tables. 41 00:02:25,960 --> 00:02:29,100 So we say that the language is algebraically closed. 42 00:02:29,100 --> 00:02:31,704 And so any, any result of a view will always be something that we can then add 43 00:02:31,704 --> 00:02:33,999 other queries on. So we can stack queries on top of 44 00:02:33,999 --> 00:02:36,274 queries, on top of queries, on top of queries, on top of queries. 45 00:02:36,274 --> 00:02:42,250 Okay? So why might we want to do this? 46 00:02:42,250 --> 00:02:46,202 So one reason is to protect the underlying data, you can assign 47 00:02:46,202 --> 00:02:50,566 permissions to tables. So for example, if you only want a 48 00:02:50,566 --> 00:02:53,686 particular user to see data associated with their account you can write a view 49 00:02:53,686 --> 00:02:58,220 that filters everything out except for their account. 50 00:02:58,220 --> 00:03:00,240 And then you grant the maxis to their view. 51 00:03:00,240 --> 00:03:03,290 The most direct benefit of this is that it, it allows you to expose data 52 00:03:03,290 --> 00:03:07,530 according to a logical organization that makes sense for the user. 53 00:03:07,530 --> 00:03:11,306 So even things as simple as hiding some join, if you decide to reorganize your 54 00:03:11,306 --> 00:03:15,082 data into two tables, requiring that programmers use joins to link them back 55 00:03:15,082 --> 00:03:20,145 up again. You can simply write a view that hides 56 00:03:20,145 --> 00:03:24,620 that join and let everyone access the result of it. 57 00:03:24,620 --> 00:03:28,700 Now, maybe this, this may sound extensive but the cool trick here is that, because 58 00:03:28,700 --> 00:03:33,201 of this Algebraic closure. What happens is, the user's query gets 59 00:03:33,201 --> 00:03:36,624 composed with your query that defines the view. 60 00:03:36,624 --> 00:03:41,810 And the whole thing gets sent as one big block to the database for evaluation. 61 00:03:41,810 --> 00:03:44,555 So the database simply doesn't care whether it came as a view and then user 62 00:03:44,555 --> 00:03:46,979 query. Or whether it came all as one, all as one 63 00:03:46,979 --> 00:03:50,807 query directed from the programmer. It's going to optimize it the exact same 64 00:03:50,807 --> 00:03:52,748 way. Okay, so this is, there's nothing but a 65 00:03:52,748 --> 00:03:54,352 benefit here. Okay. 66 00:03:54,352 --> 00:04:02,209 So let's see an example. So given the schema purchase and product. 67 00:04:03,850 --> 00:04:07,685 Define a view called StorePrice with two columns store and price that has this 68 00:04:07,685 --> 00:04:11,856 definition. So select store and select price from 69 00:04:11,856 --> 00:04:17,070 purchase and product where the product ids are equal. 70 00:04:17,070 --> 00:04:21,731 This is a little funny because we, we didn't put p, we didn't put pids here, so 71 00:04:21,731 --> 00:04:29,177 this is, this is a little bit wrong. So assume that each one of these has a, 72 00:04:29,177 --> 00:04:36,710 let's assume that this is pid, and assume that this is also pid. 73 00:04:36,710 --> 00:04:41,166 And then it matches the query down here. Okay, so this result is now like a new 74 00:04:41,166 --> 00:04:44,226 table and just like I said a second ago you've now hidden the join from the 75 00:04:44,226 --> 00:04:47,533 users. And so, complexities like these column 76 00:04:47,533 --> 00:04:50,848 names perhaps, you can insulate your users from and you can name them whatever 77 00:04:50,848 --> 00:04:54,124 you want. And so this allows you to put these, this 78 00:04:54,124 --> 00:04:57,381 is what this logical data independence means, is that. 79 00:04:57,381 --> 00:05:00,441 No matter how I want to logically organize my tables, I can, I can expose a 80 00:05:00,441 --> 00:05:04,843 different perspective on the data then I want to have myself. 81 00:05:04,843 --> 00:05:08,260 And so this separates the people who are administering the data from the ones who 82 00:05:08,260 --> 00:05:13,212 are actually accessing it. Okay, logical data independence, key 83 00:05:13,212 --> 00:05:17,330 idea, alright? Alright, so how do we use a view? 84 00:05:17,330 --> 00:05:20,450 Well as I said, all you have to do is reference the view in a query just like 85 00:05:20,450 --> 00:05:25,650 it's a table and so here, if we want to find the notion of a high end store? 86 00:05:25,650 --> 00:05:29,460 And we say well, that's any store that has sold some product over $1000 dollars. 87 00:05:29,460 --> 00:05:32,988 And you know for each customer we may want to find all the high end stores that 88 00:05:32,988 --> 00:05:37,349 they visited. Well, being able, being able to directly 89 00:05:37,349 --> 00:05:42,441 reference the store-price relation that we defined in the previous slide, this 90 00:05:42,441 --> 00:05:53,540 view helps simplify this query. Right? 91 00:05:53,540 --> 00:05:55,268 And so you can just write a query that directly accesses that view as if it were 92 00:05:55,268 --> 00:05:56,708 a table. And, okay, so how is this actually 93 00:05:56,708 --> 00:05:59,646 evaluated? Well, that actually, oops, that's 94 00:05:59,646 --> 00:06:05,358 actually what's really fantastic about databases is that this query will just be 95 00:06:05,358 --> 00:06:12,907 folded together with the view definition. And passed to the database where the 96 00:06:12,907 --> 00:06:19,210 whole thing is optimizes in, in one go, optimized in one go. 97 00:06:19,210 --> 00:06:23,306 So you don't need to worry about the difference between having a stack of five 98 00:06:23,306 --> 00:06:30,070 views and all being compiled toget/g, are all being folded together as one query. 99 00:06:30,070 --> 00:06:32,641 The database doesn't care. It's going to translate the whole thing 100 00:06:32,641 --> 00:06:36,314 into one big query, exchange that for an algebraic expression. 101 00:06:36,314 --> 00:06:39,272 And then do the normal optimization procedure to come up with the best 102 00:06:39,272 --> 00:06:43,030 possible plan. So it's basically like free abstraction. 103 00:06:43,030 --> 00:06:45,442 Right? It simplifies things for the, for the 104 00:06:45,442 --> 00:06:49,544 programmer, without any kind of performance cost. 105 00:06:49,544 --> 00:06:51,800 Okay. Now, you can actually get better 106 00:06:51,800 --> 00:06:55,150 performance than writing the whole thing by hand. 107 00:06:55,150 --> 00:06:56,710 to you it's equivalent to writing the whole thing by hand. 108 00:06:56,710 --> 00:06:58,840 You don't pay a penalty. But you guys should do better than that 109 00:06:58,840 --> 00:07:01,310 with views and in some cases by materializing views. 110 00:07:01,310 --> 00:07:03,689 And we're not going to talk too much about that because that's, sort of, is 111 00:07:03,689 --> 00:07:06,475 very specific to databases. And we don't see it quite as often in 112 00:07:06,475 --> 00:07:09,560 this broader context of data science that we're trying to talk about. 113 00:07:09,560 --> 00:07:12,544 but it's a good trick. And once you have the mechanism to store 114 00:07:12,544 --> 00:07:15,908 views, you can essentially cache the results, and that's what we call 115 00:07:15,908 --> 00:07:18,625 materialization. Okay. 116 00:07:18,625 --> 00:07:22,465 So the last key idea I want to convey about databases is that of Indexes. 117 00:07:22,465 --> 00:07:25,433 So while Indexes are certainly not unique to databases. 118 00:07:25,433 --> 00:07:28,666 Databases are perhaps unique as a platform that can make them very easy to 119 00:07:28,666 --> 00:07:32,340 apply and deploy and automatically take advantage of. 120 00:07:32,340 --> 00:07:35,876 And so databases are especially but not exclusively effective in, sort of needle 121 00:07:35,876 --> 00:07:39,672 in the haystack problems. Looking up individual records or small 122 00:07:39,672 --> 00:07:43,276 amounts of records, from large data sets. They do other things very well, too, but 123 00:07:43,276 --> 00:07:46,090 this is one thing that makes, that, that they're quite good at. 124 00:07:46,090 --> 00:07:48,750 and the reason is that they can apply, that you can apply indexes. 125 00:07:48,750 --> 00:07:52,821 This second needs a little bit of context, but what I mean here is that if 126 00:07:52,821 --> 00:07:57,241 you're trying to write code to do this yourself. 127 00:07:57,241 --> 00:08:01,038 In say some programming language like Python or C or R. 128 00:08:01,038 --> 00:08:05,247 You're going to be a slave to what sizes of data fit in the main memory, as we 129 00:08:05,247 --> 00:08:09,000 said before. Now you can absolutely be clever and 130 00:08:09,000 --> 00:08:13,696 start bringing in one chunk of data at a time in the memory, processing it. 131 00:08:13,696 --> 00:08:17,250 Putting it out to disk and bringing in the next set and so on. 132 00:08:17,250 --> 00:08:19,840 But the code will very quickly become very, very complex. 133 00:08:19,840 --> 00:08:22,620 This is something the databases already know how to do. 134 00:08:22,620 --> 00:08:25,770 And so your query will always finish regardless of database size, as long as 135 00:08:25,770 --> 00:08:28,070 it fits on disk. Right? 136 00:08:28,070 --> 00:08:30,449 It doesn't, it doesn't matter how much memory you have available, it will 137 00:08:30,449 --> 00:08:33,870 eventually finish. May or may not be that fast. 138 00:08:33,870 --> 00:08:37,180 They already know how to take advantage of main memory in this optimal way. 139 00:08:37,180 --> 00:08:39,334 And it's, and you know, it's not easy. Right? 140 00:08:39,334 --> 00:08:42,310 It's a pain in the butt to try to code that yourself. 141 00:08:42,310 --> 00:08:45,632 Okay. So effective use of, of the memory 142 00:08:45,632 --> 00:08:49,642 hierarchy, effective use of indexes. These are things the databases can do 143 00:08:49,642 --> 00:08:52,235 well. It's a great platform for applying these, 144 00:08:52,235 --> 00:08:55,819 these tricks. And so, finally, you know, this is what I 145 00:08:55,819 --> 00:08:59,370 mean here, is that the indexes are easily built and automatically used by the, by 146 00:08:59,370 --> 00:09:02,678 the optimizer. So to create an index you write, you can 147 00:09:02,678 --> 00:09:06,402 write a statement like this. here I've changed the scheme on you once 148 00:09:06,402 --> 00:09:11,670 again but here we're sort of filtering on genetic sequences. 149 00:09:11,670 --> 00:09:13,980 And if I create this index, then this query will. 150 00:09:15,020 --> 00:09:17,432 You know, this qu, this, this very simple query is looking for all sequences that 151 00:09:17,432 --> 00:09:20,318 match a particular value. It will automatically take advantage of 152 00:09:20,318 --> 00:09:22,995 that index if it's there. You don't have to tell it to do anything 153 00:09:22,995 --> 00:09:27,551 you write the exact same query. Okay.