1 00:00:00,140 --> 00:00:01,630 This sequence of videos 2 00:00:02,080 --> 00:00:04,500 introduces the very importance concept called views. 3 00:00:05,480 --> 00:00:08,080 In this video, we'll talk about defining and using views. 4 00:00:08,200 --> 00:00:09,180 And then, we'll give a demonstration. 5 00:00:10,550 --> 00:00:12,010 The next sequence of videos, we'll 6 00:00:12,260 --> 00:00:14,450 talk about modifying views, and 7 00:00:14,540 --> 00:00:16,340 then, we'll also talk about materialized views. 8 00:00:17,610 --> 00:00:18,680 Views are based on a 9 00:00:19,040 --> 00:00:20,550 three level vision of databases, 10 00:00:21,320 --> 00:00:22,340 which are known as the physical layer, 11 00:00:22,830 --> 00:00:24,790 the conceptual layer, and the logical layer. 12 00:00:26,000 --> 00:00:27,100 At the bottom level, we have 13 00:00:27,430 --> 00:00:29,520 the actual data stored on a disk. 14 00:00:29,740 --> 00:00:31,120 So, here's my picture of a 15 00:00:31,170 --> 00:00:33,300 disk, and then, we have disk pages. 16 00:00:33,880 --> 00:00:36,910 And on those pages is stored the actual data. 17 00:00:37,610 --> 00:00:38,870 So, that's the physical layer. 18 00:00:40,150 --> 00:00:41,770 The next layer up, known as 19 00:00:41,920 --> 00:00:43,320 the conceptual layer, is the 20 00:00:43,530 --> 00:00:44,730 abstraction of the data 21 00:00:45,030 --> 00:00:46,630 on these disk into relations. 22 00:00:47,850 --> 00:00:48,650 And that's what we have been talking 23 00:00:49,050 --> 00:00:51,260 about for a lot of our videos. 24 00:00:52,920 --> 00:00:54,090 What we are introducing now is 25 00:00:54,590 --> 00:00:56,470 what's known as the logical layer. 26 00:00:57,290 --> 00:00:58,460 And that's a further abstraction 27 00:00:59,190 --> 00:01:01,900 above relations into what are known as views. 28 00:01:02,880 --> 00:01:04,000 As we'll see, a view is 29 00:01:04,340 --> 00:01:06,160 defined as a query over relation. 30 00:01:06,690 --> 00:01:09,120 So, it's still in the relational data model. 31 00:01:09,990 --> 00:01:11,100 So, we showed one view that 32 00:01:11,250 --> 00:01:12,600 is query over two relations here, 33 00:01:12,800 --> 00:01:13,540 we could have a view that's 34 00:01:14,260 --> 00:01:16,770 query over views and maybe views together with relations. 35 00:01:17,590 --> 00:01:19,060 It's a quite general concept. 36 00:01:20,360 --> 00:01:22,100 So, until now, we've seen 37 00:01:23,440 --> 00:01:24,600 applications and users of 38 00:01:24,630 --> 00:01:27,450 those applications operating on the relations at the conceptual level. 39 00:01:28,070 --> 00:01:29,540 But users and applications can 40 00:01:29,770 --> 00:01:32,010 also operate on views at the logical level. 41 00:01:33,590 --> 00:01:34,710 So, why do we introduce this 42 00:01:34,880 --> 00:01:36,410 additional level on top 43 00:01:36,610 --> 00:01:39,230 of this conceptual layer which is the layer of relation? 44 00:01:40,150 --> 00:01:41,260 Certainly we understand why we 45 00:01:41,360 --> 00:01:42,450 want to use relations and not 46 00:01:42,650 --> 00:01:43,970 talk about pages like this but 47 00:01:44,110 --> 00:01:46,580 what's the benefit of adding views on top of relations. 48 00:01:47,480 --> 00:01:48,600 Well one benefit is that 49 00:01:48,770 --> 00:01:49,900 it allows us to hide some 50 00:01:50,140 --> 00:01:51,490 data from some users when 51 00:01:51,830 --> 00:01:53,070 the granularity of which we want 52 00:01:53,280 --> 00:01:56,390 to hide data doesn't correspond exactly to relations. 53 00:01:57,610 --> 00:01:58,570 Another is that it might make 54 00:01:58,830 --> 00:02:00,400 certain queries easier and 55 00:02:00,490 --> 00:02:01,590 more natural when we query 56 00:02:02,050 --> 00:02:04,190 over the views rather than directly over the relations. 57 00:02:05,040 --> 00:02:06,410 And the third reason is 58 00:02:06,690 --> 00:02:07,810 modularity of data base 59 00:02:08,160 --> 00:02:09,170 access because we can think 60 00:02:09,450 --> 00:02:11,080 of these views as modules 61 00:02:11,830 --> 00:02:14,110 giving us access to portions of the database. 62 00:02:14,900 --> 00:02:16,360 Again, when that granularity 63 00:02:17,050 --> 00:02:19,140 that we want doesn't correspond precisely to relations. 64 00:02:20,210 --> 00:02:20,880 One thing that I should mention 65 00:02:21,310 --> 00:02:23,200 is that real applications do 66 00:02:23,440 --> 00:02:24,670 tend to use lots of views, 67 00:02:25,290 --> 00:02:26,340 lots and lots of views, 68 00:02:26,910 --> 00:02:28,180 so the bigger the application 69 00:02:28,620 --> 00:02:29,520 is, the more need there might be 70 00:02:29,660 --> 00:02:31,430 for modularity, for ease 71 00:02:31,770 --> 00:02:33,110 of certain queries or for hiding 72 00:02:33,270 --> 00:02:34,660 data and views are 73 00:02:34,730 --> 00:02:36,720 the mechanism to achieve those goals. 74 00:02:37,560 --> 00:02:40,340 So let's talk about how views are defined and used. 75 00:02:41,160 --> 00:02:42,650 It's actually a pretty simple concept. 76 00:02:43,080 --> 00:02:44,340 To define a view, which we'll 77 00:02:44,460 --> 00:02:46,780 call V, we specify a 78 00:02:47,030 --> 00:02:48,430 query of, we'll just 79 00:02:48,580 --> 00:02:49,990 say, "View query," in a 80 00:02:50,180 --> 00:02:51,530 query language, typically in SQL 81 00:02:52,350 --> 00:02:54,960 over a set of existing tables. 82 00:02:55,450 --> 00:02:56,710 As we'll see those could even be used. 83 00:02:57,270 --> 00:02:59,240 The schema of the 84 00:02:59,370 --> 00:03:00,300 view then, we can think 85 00:03:00,440 --> 00:03:01,580 of it like a table, is 86 00:03:01,810 --> 00:03:03,810 the schema of the result of that query. 87 00:03:05,130 --> 00:03:07,680 Now let's say we want to run a query Q Over our database. 88 00:03:08,140 --> 00:03:08,930 This is not the view query. 89 00:03:09,220 --> 00:03:10,330 This is just some ad hoc 90 00:03:10,610 --> 00:03:11,680 query over our database and 91 00:03:11,930 --> 00:03:14,280 we want to reference V in that query. 92 00:03:14,970 --> 00:03:16,080 So, we can once V is define 93 00:03:16,240 --> 00:03:17,220 , reference it just like 94 00:03:17,390 --> 00:03:19,730 it's a table and conceptually what 95 00:03:19,910 --> 00:03:21,570 we can think of is V 96 00:03:21,950 --> 00:03:23,450 being, say a temporary table that 97 00:03:24,630 --> 00:03:25,570 is assigned to the result 98 00:03:26,070 --> 00:03:27,160 of running the query over the 99 00:03:27,380 --> 00:03:29,270 current instances of R1 100 00:03:29,490 --> 00:03:30,870 through RN, so we've 101 00:03:31,020 --> 00:03:32,420 now populated V like it 102 00:03:32,560 --> 00:03:33,570 is a table and then we 103 00:03:33,730 --> 00:03:34,850 evaluate Q and we can 104 00:03:35,110 --> 00:03:36,350 simply refer to V as 105 00:03:36,460 --> 00:03:39,110 we evaluate Q. So that's what happens conceptually. 106 00:03:40,300 --> 00:03:41,490 Now, in reality what happens 107 00:03:42,330 --> 00:03:43,900 is the query Q that references 108 00:03:44,750 --> 00:03:46,950 V is actually rewritten to 109 00:03:47,180 --> 00:03:49,110 use the tables R1 through 110 00:03:49,400 --> 00:03:50,240 N that are in the definition 111 00:03:50,960 --> 00:03:52,360 of V. Instead of referencing 112 00:03:52,870 --> 00:03:53,690 V and we'll see that in 113 00:03:53,860 --> 00:03:55,360 our demo and as I 114 00:03:55,430 --> 00:03:57,730 mentioned, the RIs, these 115 00:03:57,910 --> 00:03:59,090 tables that are referenced in 116 00:03:59,310 --> 00:04:00,940 the view definition can themselves 117 00:04:01,520 --> 00:04:02,530 be views as we saw 118 00:04:02,850 --> 00:04:04,830 in our diagram earlier with those purple squares. 119 00:04:06,240 --> 00:04:06,870 The syntax for. 120 00:04:07,040 --> 00:04:08,770 Creating a view in sql is very simple. 121 00:04:09,180 --> 00:04:10,470 We use the keywords create view, 122 00:04:10,830 --> 00:04:11,560 we give the view a name, 123 00:04:11,630 --> 00:04:12,720 and then we specify 124 00:04:13,260 --> 00:04:14,260 the query for that view, and 125 00:04:14,400 --> 00:04:16,140 that query would be in standard sql. 126 00:04:17,030 --> 00:04:17,790 Now as I said, the schema 127 00:04:18,600 --> 00:04:20,210 for the viewed that will 128 00:04:20,470 --> 00:04:21,470 become known as the name, 129 00:04:21,680 --> 00:04:23,800 is the schema of the result of this query. 130 00:04:24,580 --> 00:04:25,590 If we want to actually name 131 00:04:26,070 --> 00:04:27,770 the schema so rename the 132 00:04:27,980 --> 00:04:28,850 attributes in the results of 133 00:04:28,930 --> 00:04:29,800 the query, then there is 134 00:04:29,880 --> 00:04:31,510 an alternative syntax that actually 135 00:04:31,960 --> 00:04:33,510 lists the attribute names for the view. 136 00:04:34,610 --> 00:04:35,770 Once this command has been 137 00:04:36,010 --> 00:04:37,210 executed from this point on, 138 00:04:37,720 --> 00:04:39,310 we can use new name as 139 00:04:39,420 --> 00:04:41,060 it's a regular table in the database. 140 00:04:41,650 --> 00:04:43,190 So let's move ahead now to the demo. 141 00:04:43,970 --> 00:04:44,740 In the demo, we will be 142 00:04:44,790 --> 00:04:47,240 using our standard, simple college admissions database. 143 00:04:47,960 --> 00:04:49,040 As a reminder in case it's 144 00:04:49,150 --> 00:04:49,980 been a while or you're new 145 00:04:50,110 --> 00:04:52,720 to our videos, it's a database with three tables. 146 00:04:53,180 --> 00:04:54,340 One with information about colleges 147 00:04:55,300 --> 00:04:56,750 that have a college name statement enrollment. 148 00:04:57,470 --> 00:04:58,520 One with information about students 149 00:04:59,100 --> 00:05:01,770 and finally records showing that students have applied to colleges. 150 00:05:04,070 --> 00:05:05,040 As usual, we have our four 151 00:05:05,250 --> 00:05:07,770 colleges; Stanford, Berkeley, MIT and Cornell. 152 00:05:08,280 --> 00:05:09,540 We have a bunch of 153 00:05:09,600 --> 00:05:10,940 students with their name, 154 00:05:11,220 --> 00:05:12,160 GPA, and the size of 155 00:05:12,230 --> 00:05:13,510 their high school, and finally 156 00:05:13,880 --> 00:05:15,080 we have students who have 157 00:05:15,190 --> 00:05:16,430 applied to colleges for a 158 00:05:16,760 --> 00:05:18,790 specific major and there's a decision of their application. 159 00:05:19,720 --> 00:05:21,200 So let's create our first view. 160 00:05:22,470 --> 00:05:23,980 Our view is called CS Accept 161 00:05:24,490 --> 00:05:25,940 and it contains the IDs 162 00:05:26,330 --> 00:05:27,710 and college names of students 163 00:05:27,900 --> 00:05:29,000 who have applied to major 164 00:05:29,640 --> 00:05:30,930 in CS, and the decision was yes. 165 00:05:31,660 --> 00:05:34,360 So it's the IDs and names of students who have been accepted to a CS major. 166 00:05:35,510 --> 00:05:36,860 We execute the command that 167 00:05:37,140 --> 00:05:39,330 creates the view, and now we can take a look at the view. 168 00:05:40,450 --> 00:05:41,670 We go to our view manager, and 169 00:05:41,760 --> 00:05:43,020 we see CS Accept, and we 170 00:05:43,150 --> 00:05:45,470 double-click, and here are the contents of the view. 171 00:05:46,170 --> 00:05:49,040 Now even though it looks like it, this view is actually not stored. 172 00:05:49,350 --> 00:05:50,140 When we ask to see the 173 00:05:50,460 --> 00:05:51,650 contents of the view, or as 174 00:05:51,740 --> 00:05:53,030 we see momentarily, run a query 175 00:05:53,240 --> 00:05:54,850 that uses the view, that command 176 00:05:55,020 --> 00:05:58,310 or query is rewritten based on the view definition that we gave. 177 00:05:59,710 --> 00:06:02,250 So now let's run a query that uses the view as if it's a table. 178 00:06:03,080 --> 00:06:03,880 Our query is going to find 179 00:06:04,210 --> 00:06:05,510 students who were accepted to 180 00:06:05,740 --> 00:06:06,970 computer science at Stan ford 181 00:06:07,160 --> 00:06:08,560 and have a GPA less than 3.8. 182 00:06:08,650 --> 00:06:10,840 So here's our query written in 183 00:06:11,060 --> 00:06:12,210 SQL, and we can see 184 00:06:12,510 --> 00:06:13,720 here in the [xx]clause we 185 00:06:14,120 --> 00:06:16,100 are referring to CS Accept as if it's a table. 186 00:06:16,650 --> 00:06:19,460 So we'll join the student relation with the CS Accept relation. 187 00:06:20,700 --> 00:06:21,640 We'll make sure that the college 188 00:06:22,070 --> 00:06:24,330 is Stanford and the GPA is less than three point eight. 189 00:06:24,730 --> 00:06:25,890 We run the query and 190 00:06:25,990 --> 00:06:27,280 we see that Helen is 191 00:06:27,410 --> 00:06:29,070 the only student who satisfies the criteria. 192 00:06:30,530 --> 00:06:33,260 So what actually happens when we run this query that refers to a view? 193 00:06:33,730 --> 00:06:34,710 First now I'm going to talk 194 00:06:34,820 --> 00:06:36,650 about what happens conceptually, and then 195 00:06:36,720 --> 00:06:38,640 I'm going to talk about what the system actually does. 196 00:06:39,330 --> 00:06:40,460 So conceptually we can think 197 00:06:41,170 --> 00:06:42,860 of the query referencing the 198 00:06:42,950 --> 00:06:44,520 view as triggering a 199 00:06:44,770 --> 00:06:45,790 process where we create 200 00:06:46,130 --> 00:06:47,260 an actual table with the 201 00:06:47,320 --> 00:06:48,680 contents of the view, and 202 00:06:48,770 --> 00:06:50,310 then we run our query over that table. 203 00:06:51,000 --> 00:06:52,840 So, I've demonstrated that here. 204 00:06:52,970 --> 00:06:54,550 We created temporary table T, 205 00:06:55,180 --> 00:06:57,430 and that query contains exactly our view definition. 206 00:06:58,160 --> 00:07:00,320 So this was the definition of our view CS Accept. 207 00:07:01,400 --> 00:07:02,270 Then we take the query that 208 00:07:02,390 --> 00:07:03,960 we want to run, this was 209 00:07:04,080 --> 00:07:05,580 our query, and we replace the 210 00:07:05,720 --> 00:07:06,510 reference to the view with 211 00:07:06,730 --> 00:07:07,890 the reference to the temporary table 212 00:07:08,400 --> 00:07:10,010 T otherwise everything is exactly the same. 213 00:07:10,570 --> 00:07:12,740 We'll run that query and then we'll drop our temporary table. 214 00:07:13,460 --> 00:07:14,550 So, if we execute all of 215 00:07:14,650 --> 00:07:15,740 this, again we will see 216 00:07:15,980 --> 00:07:17,820 our result is Helen. 217 00:07:18,800 --> 00:07:20,290 what happens underneath in most 218 00:07:20,640 --> 00:07:22,120 systems is that the 219 00:07:22,290 --> 00:07:23,080 query that we write is actually 220 00:07:23,460 --> 00:07:25,010 rewritten not to reference 221 00:07:25,300 --> 00:07:26,350 a view or to reference a 222 00:07:26,610 --> 00:07:27,540 temporary table, but actually to 223 00:07:27,780 --> 00:07:28,930 reference the what are 224 00:07:29,000 --> 00:07:31,750 known as base tables, the ones that are used in the view definition. 225 00:07:32,790 --> 00:07:33,990 So let's take a look at that process. 226 00:07:35,340 --> 00:07:36,390 So here we've taken our reference 227 00:07:37,040 --> 00:07:38,210 to the view CS Accept 228 00:07:38,320 --> 00:07:40,280 and we've created a 229 00:07:40,750 --> 00:07:41,850 sub-query in the from clause. 230 00:07:42,170 --> 00:07:43,710 I hope you remember that from the SQL videos. 231 00:07:44,480 --> 00:07:46,360 So we've just taken the definition of the view. 232 00:07:46,670 --> 00:07:47,240 It's right here. 233 00:07:47,990 --> 00:07:49,050 We've put it into a sub-query, 234 00:07:49,660 --> 00:07:51,360 we've named it CS Accept, and 235 00:07:51,530 --> 00:07:54,120 then the rest of the query can remain the same. 236 00:07:54,300 --> 00:07:55,760 So this is actually convenient, a very 237 00:07:55,950 --> 00:07:57,940 useful feature where we 238 00:07:58,010 --> 00:07:59,630 use the sub-queries in the from clause. 239 00:08:00,910 --> 00:08:03,200 So we'll run the query, and once again we see Helen. 240 00:08:04,350 --> 00:08:05,110 Well, believe it or not, 241 00:08:05,380 --> 00:08:06,840 this is not exactly what 242 00:08:07,050 --> 00:08:08,370 the system tends to do either. 243 00:08:08,990 --> 00:08:10,270 This is a very easy and 244 00:08:10,600 --> 00:08:12,260 convenient rewrite, but unfortunately 245 00:08:12,420 --> 00:08:14,330 , underlying database systems 246 00:08:14,770 --> 00:08:17,160 don't always execute queries that 247 00:08:17,320 --> 00:08:18,260 have sub-queries in the from 248 00:08:18,580 --> 00:08:19,850 clause in the most efficient way. 249 00:08:20,750 --> 00:08:22,660 So a sophisticated system will 250 00:08:23,320 --> 00:08:25,640 actually do a rewrite into a simpler query. 251 00:08:25,960 --> 00:08:27,520 The rewriting process is more complicated. 252 00:08:28,500 --> 00:08:29,720 The query ends up being simpler. 253 00:08:30,570 --> 00:08:31,420 And here's that query. 254 00:08:31,860 --> 00:08:33,350 It's actually a simple query 255 00:08:33,910 --> 00:08:35,000 over the student and the 256 00:08:35,250 --> 00:08:36,780 apply relations based on the 257 00:08:36,850 --> 00:08:38,390 definition of our view, where 258 00:08:38,570 --> 00:08:40,830 we're finding here from the 259 00:08:41,270 --> 00:08:42,330 first part of our wear comes from 260 00:08:42,730 --> 00:08:44,040 the view definition the major 261 00:08:44,410 --> 00:08:45,980 has to be CS and the decision is yes. 262 00:08:46,820 --> 00:08:47,910 Then we have the joint condition 263 00:08:48,350 --> 00:08:49,330 that comes from the query and 264 00:08:50,100 --> 00:08:52,350 the last two conditions are also from our query. 265 00:08:52,800 --> 00:08:53,710 So you might want to 266 00:08:53,820 --> 00:08:55,010 look back and think about it 267 00:08:55,100 --> 00:08:56,980 but this is exactly what 268 00:08:57,320 --> 00:08:58,320 we were asking for when 269 00:08:58,540 --> 00:08:59,780 we were defining the view 270 00:09:00,150 --> 00:09:01,490 and then running a query over that view. 271 00:09:01,940 --> 00:09:04,830 So when we run this, we should once again get Helen, and we do. 272 00:09:06,570 --> 00:09:09,120 Of course is that as a user you don't have to worry about any of this. 273 00:09:09,360 --> 00:09:10,510 You just define the views and 274 00:09:10,650 --> 00:09:11,640 you use them, and it's 275 00:09:11,930 --> 00:09:14,560 up to the system to have an efficient and correct implementation. 276 00:09:16,390 --> 00:09:17,690 As I mentioned in the introduction 277 00:09:18,290 --> 00:09:19,810 we can define views that 278 00:09:20,030 --> 00:09:22,640 reference other views and that's what I've done in this example. 279 00:09:23,650 --> 00:09:24,420 This example finds 280 00:09:24,590 --> 00:09:25,900 students who are accepted to 281 00:09:26,160 --> 00:09:27,350 computer science at Berkeley, and 282 00:09:27,440 --> 00:09:29,420 they come from a high school that's greater than 500. 283 00:09:29,530 --> 00:09:31,490 So I'm calling the view 284 00:09:31,960 --> 00:09:33,790 CS-Berk, and the view 285 00:09:34,440 --> 00:09:35,480 is a join of the 286 00:09:35,550 --> 00:09:37,910 student relation with our CS accept view. 287 00:09:38,620 --> 00:09:39,900 It's going to join based on the IDs. 288 00:09:40,330 --> 00:09:41,790 So, the accept view is 289 00:09:42,000 --> 00:09:43,030 already going to be 290 00:09:43,130 --> 00:09:44,340 finding students who are accepted 291 00:09:44,910 --> 00:09:46,030 out of college for computer 292 00:09:46,470 --> 00:09:47,520 science and then we're 293 00:09:47,620 --> 00:09:48,440 going to constrain it to those 294 00:09:48,680 --> 00:09:50,410 who are accepted to Berkley and 295 00:09:50,750 --> 00:09:52,290 from a high school size greater than 500. 296 00:09:53,340 --> 00:09:54,620 So, we run this command, but 297 00:09:54,710 --> 00:09:56,910 this doesn't show us the result, it just creates the view. 298 00:09:58,110 --> 00:09:58,900 Now we go to our view 299 00:09:59,060 --> 00:09:59,990 manager and we see that 300 00:10:00,100 --> 00:10:01,750 we have two views, and the 301 00:10:01,870 --> 00:10:03,230 Berkley view finds that there 302 00:10:03,430 --> 00:10:05,620 were two students who were accepted to Berkley. 303 00:10:06,350 --> 00:10:07,720 They come from a large 304 00:10:08,020 --> 00:10:09,140 high school, or high school 305 00:10:09,340 --> 00:10:10,500 greater than 500, although 306 00:10:10,680 --> 00:10:12,610 we're still returning their GPA in the view. 307 00:10:13,470 --> 00:10:14,270 So now that the view 308 00:10:14,580 --> 00:10:16,250 CS-Berk has been defined, we can 309 00:10:16,440 --> 00:10:17,750 run a query on CS-Berk. 310 00:10:17,960 --> 00:10:19,330 So let's find those 311 00:10:19,620 --> 00:10:21,670 students who have been accepted to Berkeley. 312 00:10:22,420 --> 00:10:23,550 Or computer science with a high 313 00:10:23,670 --> 00:10:24,580 school size greater than five hundred 314 00:10:25,090 --> 00:10:26,120 and that have a GPA greater 315 00:10:26,360 --> 00:10:28,590 than three point eight, and we see that that's Amy. 316 00:10:29,720 --> 00:10:31,140 So this is a beautifully simple query. 317 00:10:31,710 --> 00:10:32,900 But what's happening underneath is a 318 00:10:32,990 --> 00:10:34,390 little bit complicated because this is 319 00:10:34,650 --> 00:10:36,070 defined over one view, which 320 00:10:36,220 --> 00:10:37,770 itself is defined over other tables. 321 00:10:38,590 --> 00:10:39,820 So the rewrite process in this 322 00:10:40,040 --> 00:10:42,520 case is a kind of recursive process of expansion. 323 00:10:43,770 --> 00:10:46,100 Let's take a look at what the query would look like rewritten. 324 00:10:47,190 --> 00:10:50,200 So now that simple reference to the view CS Burke has. 325 00:10:50,460 --> 00:10:51,480 It's been replaced by this whole 326 00:10:51,880 --> 00:10:53,470 section here, lines 2 through 5. 327 00:10:54,120 --> 00:10:56,420 And you can see sort of the 2 layer process. 328 00:10:57,400 --> 00:10:58,670 At the outer layer we have. 329 00:10:59,170 --> 00:11:00,600 The definition of CS Burke 330 00:11:01,210 --> 00:11:02,840 which itself referred to CS accept. 331 00:11:03,400 --> 00:11:04,940 So, we had in 332 00:11:05,050 --> 00:11:06,580 of CS Burke, not this 333 00:11:06,740 --> 00:11:08,290 expansion here but just CS accept. 334 00:11:08,790 --> 00:11:10,070 And then we had the additional joint 335 00:11:10,400 --> 00:11:12,120 condition and the reference 336 00:11:12,570 --> 00:11:13,660 to it being Berkley and the 337 00:11:13,910 --> 00:11:15,150 size High School and then 338 00:11:15,370 --> 00:11:17,000 this CS accepted self is 339 00:11:17,160 --> 00:11:19,460 expanded to its view definition. 340 00:11:20,440 --> 00:11:21,320 So now when we run 341 00:11:21,530 --> 00:11:23,950 the query we should still get the same result and we do. 342 00:11:25,330 --> 00:11:26,250 I'm not going to show it 343 00:11:26,330 --> 00:11:27,590 here but this query could 344 00:11:28,210 --> 00:11:29,580 similarly have a flattened 345 00:11:30,290 --> 00:11:31,470 rewrite into a join 346 00:11:32,230 --> 00:11:35,050 as we saw when we saw the expansion of CS accept. 347 00:11:36,360 --> 00:11:37,100 So now we have the view 348 00:11:37,360 --> 00:11:39,860 CS berk that's defined over the view CS accept. 349 00:11:40,380 --> 00:11:43,060 What happens if we try to drop the view CS accept? 350 00:11:43,800 --> 00:11:44,600 We get an error here. 351 00:11:45,170 --> 00:11:46,120 We can't drop the view 352 00:11:46,350 --> 00:11:48,410 because other objects depend on it. 353 00:11:48,500 --> 00:11:49,220 So that's a nice error. 354 00:11:49,720 --> 00:11:51,130 That's from the Post Grist system. 355 00:11:51,860 --> 00:11:52,970 Unfortunately, if we used one 356 00:11:53,160 --> 00:11:54,450 of the other two systems, SQL Light 357 00:11:55,030 --> 00:11:56,270 or My SQL, what would actually 358 00:11:56,570 --> 00:11:57,730 happen is it would allow us 359 00:11:57,950 --> 00:11:58,970 to drop the view and 360 00:11:59,160 --> 00:12:00,270 then when we attempted to refer 361 00:12:00,840 --> 00:12:03,640 to CS berk, at that point we would get an error. 362 00:12:03,950 --> 00:12:04,750 Because when it did the rewriting 363 00:12:05,230 --> 00:12:07,720 process it would discover that CS accept no longer exists. 364 00:12:08,930 --> 00:12:11,750 Now let's create what happens to be one of my favorite types of views. 365 00:12:12,390 --> 00:12:13,250 We're going to take our three 366 00:12:13,540 --> 00:12:14,990 tables in the database and we're 367 00:12:15,130 --> 00:12:16,280 going to join them together so 368 00:12:16,490 --> 00:12:17,390 that we have all of our 369 00:12:17,560 --> 00:12:19,040 information together in one table. 370 00:12:19,750 --> 00:12:21,030 So we're going to apply our 371 00:12:21,230 --> 00:12:22,680 join conditions to get the 372 00:12:22,740 --> 00:12:24,040 colleges coordinated with the 373 00:12:24,470 --> 00:12:26,170 applications and the students, and 374 00:12:26,520 --> 00:12:28,150 then we're going to take all of the attributes involved. 375 00:12:29,130 --> 00:12:30,230 So let's run that view. 376 00:12:30,380 --> 00:12:32,350 We'll call it mega and let's 377 00:12:32,610 --> 00:12:33,930 take a look at what we have in mega. 378 00:12:34,840 --> 00:12:35,130 Here it is. 379 00:12:35,380 --> 00:12:36,510 So this is all of the 380 00:12:36,790 --> 00:12:37,690 information in our three tables 381 00:12:38,300 --> 00:12:41,210 joined together and we'll see that makes queries quite convenient. 382 00:12:42,220 --> 00:12:43,100 Now, let me remind you that 383 00:12:43,290 --> 00:12:44,820 this information, the view contents, 384 00:12:45,380 --> 00:12:46,440 are not actually stored. 385 00:12:47,300 --> 00:12:50,220 So, this is a toy database; it wouldn't be a big deal to store it here. 386 00:12:50,630 --> 00:12:51,860 But if we have a very large 387 00:12:52,240 --> 00:12:53,520 database, where it's crazy to 388 00:12:54,050 --> 00:12:55,330 think about actually storing the join 389 00:12:56,000 --> 00:12:57,130 of everything together, that doesn't 390 00:12:57,570 --> 00:12:58,790 mean we cannot create the 391 00:12:58,860 --> 00:12:59,540 view, because the view is 392 00:12:59,630 --> 00:13:01,080 just a logical concept, and 393 00:13:01,410 --> 00:13:03,270 again can ease querying, as we'll see now. 394 00:13:04,340 --> 00:13:07,330 Once we have our mega view we don't need to think about joins. 395 00:13:07,820 --> 00:13:09,500 We simply state the conditions 396 00:13:09,860 --> 00:13:11,850 that we want in the data in our query results. 397 00:13:12,270 --> 00:13:13,830 So, for example, if we 398 00:13:13,930 --> 00:13:15,190 want to find high GPA 399 00:13:15,780 --> 00:13:18,210 applications to a CS major at large colleges. 400 00:13:19,160 --> 00:13:20,350 We just use mega in 401 00:13:20,460 --> 00:13:21,700 our clause and give our 402 00:13:21,840 --> 00:13:23,560 conditions, high GPA, CS major, 403 00:13:24,290 --> 00:13:26,960 high enrollment and we run the query and here's our result. 404 00:13:28,190 --> 00:13:29,220 And incidentally if you're worried 405 00:13:29,700 --> 00:13:30,740 about the rewrite of that 406 00:13:30,980 --> 00:13:32,750 query, it's actually no big deal. 407 00:13:33,110 --> 00:13:34,400 That one's going to rewrite 408 00:13:34,620 --> 00:13:36,070 in its flattened version to 409 00:13:36,210 --> 00:13:37,140 adjoin of the three tables 410 00:13:37,650 --> 00:13:40,190 with the join conditions and then the conditions that we specified. 411 00:13:40,820 --> 00:13:43,120 And again, if we run this query, we'll get the same result. 412 00:13:44,300 --> 00:13:45,780 So that completes our demonstration of 413 00:13:45,850 --> 00:13:47,830 defining views and using views in queries. 414 00:13:48,710 --> 00:13:49,610 As you can can see it's a pretty 415 00:13:49,760 --> 00:13:51,350 straight forward and convenient concept 416 00:13:52,100 --> 00:13:54,670 for the application developer and are 417 00:13:54,770 --> 00:13:57,080 used very commonly for modularizing 418 00:13:57,720 --> 00:13:59,330 applications for making queries easier 419 00:13:59,990 --> 00:14:01,850 to, to formulate and 420 00:14:01,900 --> 00:14:03,380 for authorization purposes.