1 00:00:00,230 --> 00:00:02,560 This video will be a demo of automatic view modifications. 2 00:00:04,120 --> 00:00:05,470 As a reminder, when we've 3 00:00:05,640 --> 00:00:06,720 defined a view called V, 4 00:00:07,240 --> 00:00:10,240 we'd like to issue modification commands against V as if it were any table. 5 00:00:11,250 --> 00:00:12,360 Since V is just a logical 6 00:00:12,690 --> 00:00:13,600 concept and not a stored 7 00:00:13,780 --> 00:00:15,180 table, the modifications have to 8 00:00:15,310 --> 00:00:16,570 be rewritten to modify the 9 00:00:16,640 --> 00:00:18,140 base tables over which V is defined. 10 00:00:19,180 --> 00:00:20,130 We saw in an earlier video 11 00:00:20,490 --> 00:00:22,680 that unlike queries overviews, modifications 12 00:00:23,450 --> 00:00:25,440 overviews cannot be automated in the general case. 13 00:00:26,210 --> 00:00:27,210 We've discussed that there are two 14 00:00:27,400 --> 00:00:28,800 different strategies to dealing with 15 00:00:29,470 --> 00:00:30,930 modifications to views and specifically how 16 00:00:31,050 --> 00:00:32,440 they're rewritten to modify base tables. 17 00:00:33,460 --> 00:00:34,350 One is to have the rewriting 18 00:00:34,850 --> 00:00:37,110 process specified explicitly when a view is created. 19 00:00:38,280 --> 00:00:39,680 The other possibility is to 20 00:00:39,840 --> 00:00:41,580 restrict the allowed view 21 00:00:41,890 --> 00:00:43,470 definitions and modifications over those 22 00:00:43,720 --> 00:00:46,000 views so that the translation can be automatic. 23 00:00:47,030 --> 00:00:49,260 The second strategy is the topic of this video. 24 00:00:50,120 --> 00:00:51,610 Now, in the SQL standard, there 25 00:00:51,830 --> 00:00:53,030 is a definition for restrictions 26 00:00:53,680 --> 00:00:56,410 to views and modifications for what are known as updatable views. 27 00:00:57,560 --> 00:00:58,670 The restrictions are on the 28 00:00:59,040 --> 00:01:00,210 definitions and they apply 29 00:01:00,740 --> 00:01:02,380 to inserts, deletes and updates all together. 30 00:01:03,100 --> 00:01:03,880 What I mean by that is 31 00:01:03,960 --> 00:01:06,170 that a view is either updatable or it's not. 32 00:01:06,960 --> 00:01:08,660 The SQL standard specifies four restrictions. 33 00:01:09,860 --> 00:01:11,180 The first one says that the 34 00:01:11,350 --> 00:01:12,480 view must be defined as a 35 00:01:12,520 --> 00:01:13,810 select statement on a single 36 00:01:14,440 --> 00:01:16,520 table T. That means it cannot be a joining view. 37 00:01:17,430 --> 00:01:18,620 Second of all, when their 38 00:01:18,980 --> 00:01:20,230 attributes from T that are 39 00:01:20,340 --> 00:01:21,300 not in the view, in 40 00:01:21,430 --> 00:01:22,560 other words, they're attributes that don't 41 00:01:22,770 --> 00:01:24,160 appear in the select clause 42 00:01:24,490 --> 00:01:26,360 of the view, those attributes of 43 00:01:26,550 --> 00:01:27,710 the table T must be allowed to 44 00:01:27,780 --> 00:01:28,680 be null or they must 45 00:01:28,900 --> 00:01:30,920 have a default value defined for them. 46 00:01:31,890 --> 00:01:33,490 Third, sub queries in 47 00:01:33,680 --> 00:01:34,940 the view must not refer to 48 00:01:35,100 --> 00:01:36,280 the table T but sub queries 49 00:01:36,720 --> 00:01:37,600 are allowed to refer to other 50 00:01:37,770 --> 00:01:39,080 tables and finally the 51 00:01:39,220 --> 00:01:41,100 view is not allowed to have group by or aggregation. 52 00:01:42,440 --> 00:01:43,350 In our demo, we use our 53 00:01:43,440 --> 00:01:45,020 standard simple college admissions database 54 00:01:45,500 --> 00:01:47,510 with a college table, student table and apply table. 55 00:01:48,920 --> 00:01:50,400 We have, as usual, our four 56 00:01:50,710 --> 00:01:52,930 colleges, our bunch of 57 00:01:53,020 --> 00:01:54,420 students, and our students 58 00:01:55,100 --> 00:01:56,910 applying to colleges for a particular major. 59 00:01:57,870 --> 00:01:58,960 I wanted to mention that this 60 00:01:59,190 --> 00:02:01,410 demo is being run in the MySQL system. 61 00:02:02,270 --> 00:02:03,450 MySQL among the three 62 00:02:03,700 --> 00:02:04,630 systems we're using is the 63 00:02:04,730 --> 00:02:06,450 only one that allows automatic view modifications. 64 00:02:07,730 --> 00:02:09,000 SQL Lite and postgres both 65 00:02:09,230 --> 00:02:10,670 support view modifications through triggers 66 00:02:11,050 --> 00:02:12,880 or rules as we saw in our other video. 67 00:02:14,280 --> 00:02:15,160 The views in this demo may 68 00:02:15,370 --> 00:02:16,640 look familiar there are more 69 00:02:16,880 --> 00:02:17,910 of the same views that we 70 00:02:18,090 --> 00:02:19,090 used in the original video 71 00:02:19,550 --> 00:02:20,600 on defining and using views. 72 00:02:21,350 --> 00:02:22,570 For example, our first view 73 00:02:22,970 --> 00:02:24,500 is the CS Accept view 74 00:02:25,210 --> 00:02:26,850 that finds students IDs and 75 00:02:27,050 --> 00:02:27,890 college names where the student 76 00:02:28,210 --> 00:02:29,510 has applied to major in CS 77 00:02:29,810 --> 00:02:31,520 at that college and the decision was yes. 78 00:02:32,770 --> 00:02:34,000 We'll go ahead and create 79 00:02:34,210 --> 00:02:35,470 the view and then take 80 00:02:35,730 --> 00:02:37,030 a look at the contents of 81 00:02:37,120 --> 00:02:38,130 the view, and we see 82 00:02:38,430 --> 00:02:39,670 that we have a few students here. 83 00:02:40,500 --> 00:02:41,550 Now let's say we want 84 00:02:41,950 --> 00:02:44,430 to delete student ID 123 from the view. 85 00:02:44,820 --> 00:02:46,610 So we would delete the first two tuples of the view. 86 00:02:47,380 --> 00:02:48,430 So here's the command that says 87 00:02:48,690 --> 00:02:50,750 the delete from the view where the student ID is 123. 88 00:02:51,250 --> 00:02:53,140 Because our view definition satisfies 89 00:02:53,970 --> 00:02:55,490 the requirements for an updatable 90 00:02:55,790 --> 00:02:56,610 view in the MySQL 91 00:02:56,950 --> 00:02:58,240 system, we can simply 92 00:02:58,680 --> 00:02:59,670 run the command, and the 93 00:02:59,850 --> 00:03:01,240 system will take care of 94 00:03:01,630 --> 00:03:03,010 modifying the underlying base table 95 00:03:03,400 --> 00:03:04,740 so that we get the effect of this deletion. 96 00:03:05,100 --> 00:03:06,600 So lets go take a look at our view. 97 00:03:07,070 --> 00:03:08,140 And, we'll see that 123 are 98 00:03:08,220 --> 00:03:10,170 indeed gone and if 99 00:03:10,390 --> 00:03:11,830 we look at our apply table, we 100 00:03:11,980 --> 00:03:13,090 see that tuples where 123 101 00:03:13,190 --> 00:03:15,110 has applied to CS are deleted 102 00:03:15,410 --> 00:03:15,770 as well. 103 00:03:16,350 --> 00:03:18,700 So the system automatically translated the 104 00:03:18,950 --> 00:03:20,210 delete command over the view 105 00:03:20,920 --> 00:03:23,480 into delete commands on the base table with the proper effect. 106 00:03:24,770 --> 00:03:25,950 At this point, let me mention that 107 00:03:26,240 --> 00:03:27,110 MySQL is actually a little 108 00:03:27,180 --> 00:03:28,640 bit more generous about the views 109 00:03:28,910 --> 00:03:30,120 and modifications that it allows 110 00:03:30,970 --> 00:03:32,650 than the SQL Standard requires 111 00:03:33,220 --> 00:03:35,620 and we'll see some examples later that make that very clear. 112 00:03:37,290 --> 00:03:39,100 Now let's take a look at insertions into views. 113 00:03:39,890 --> 00:03:41,970 We'll create our view called CSEE 114 00:03:42,510 --> 00:03:43,920 that contains the ID, college name 115 00:03:44,230 --> 00:03:45,400 and major of students who 116 00:03:45,460 --> 00:03:47,740 have applied in major in either CS or EE. 117 00:03:48,740 --> 00:03:50,160 We'll create the view, take a 118 00:03:50,230 --> 00:03:51,240 quick look at it and 119 00:03:51,460 --> 00:03:52,980 we'll see that we have quite a few students. 120 00:03:54,680 --> 00:03:55,690 Now let's suppose that we want 121 00:03:55,750 --> 00:03:58,550 to insert a student into CSEE. 122 00:03:59,750 --> 00:04:01,300 The student with ID 111 applying to Berkley for a CS. 123 00:04:01,640 --> 00:04:02,720 So we'll go ahead and 124 00:04:02,990 --> 00:04:04,070 execute the command and again 125 00:04:04,400 --> 00:04:05,580 because we have automatic view translation, 126 00:04:06,370 --> 00:04:07,970 the system will translate that into 127 00:04:08,230 --> 00:04:10,880 an appropriate underlying insertion into a base table. 128 00:04:11,660 --> 00:04:12,420 Let's first take a look at 129 00:04:12,480 --> 00:04:13,680 the view and we'll see 130 00:04:13,970 --> 00:04:15,500 that we have indeed 111 Berkeley 131 00:04:16,140 --> 00:04:17,210 CS and then we 132 00:04:17,340 --> 00:04:18,100 can take a look at the 133 00:04:18,270 --> 00:04:19,690 apply table, and we will 134 00:04:19,940 --> 00:04:21,580 see that the system apply inserted 135 00:04:22,270 --> 00:04:25,120 the corresponding tuple into apply with a null value. 136 00:04:25,820 --> 00:04:28,430 Of course let me remind you no insertions happening to the view. 137 00:04:28,890 --> 00:04:30,420 The view is really just a logical concept. 138 00:04:31,390 --> 00:04:32,480 So when we take a look at the 139 00:04:32,660 --> 00:04:33,980 contents view, we're actually 140 00:04:34,320 --> 00:04:35,950 running a query over the apply table. 141 00:04:37,220 --> 00:04:38,170 We, by the way, being the system. 142 00:04:38,570 --> 00:04:39,430 It takes care of that for us. 143 00:04:40,930 --> 00:04:41,820 Now let's see a couple of examples 144 00:04:42,430 --> 00:04:43,420 where things seem to go 145 00:04:43,650 --> 00:04:45,870 wrong but don't worry we'll see a fix to them afterward. 146 00:04:46,800 --> 00:04:48,050 Let's suppose that we want 147 00:04:48,420 --> 00:04:50,020 to insert into our CSEE 148 00:04:50,640 --> 00:04:52,400 view not AACS or EE 149 00:04:52,580 --> 00:04:54,320 major, but a psychology major. 150 00:04:55,130 --> 00:04:56,260 So we'll insert student 222 to 151 00:04:56,510 --> 00:04:58,760 apply to Berkeley in psychology. 152 00:04:59,860 --> 00:05:01,050 We'll run the insertion and it 153 00:05:01,140 --> 00:05:03,610 seems to have worked fine, so let's see what happens. 154 00:05:04,160 --> 00:05:05,530 Well we look at CSEE and 155 00:05:05,790 --> 00:05:06,870 obviously the student is not 156 00:05:07,100 --> 00:05:08,620 there because they're majoring in psychology. 157 00:05:09,780 --> 00:05:10,700 If we take a look at 158 00:05:10,810 --> 00:05:13,450 the apply relation, we'll see that there was in fact an insertion. 159 00:05:14,210 --> 00:05:15,330 So this doesn't look good because 160 00:05:15,610 --> 00:05:16,750 we don't want the system to 161 00:05:17,160 --> 00:05:18,740 automatically be inserting data into 162 00:05:19,080 --> 00:05:20,720 the base tables that isn't 163 00:05:21,010 --> 00:05:21,820 appearing in the view and what 164 00:05:22,000 --> 00:05:23,790 we wanted was an insertion into the view. 165 00:05:24,800 --> 00:05:26,750 Let's see a second example of this form. 166 00:05:27,880 --> 00:05:29,530 We'll go back to our accept 167 00:05:29,790 --> 00:05:30,940 view which is students and 168 00:05:31,170 --> 00:05:32,870 colleges where the student applied to 169 00:05:33,540 --> 00:05:34,730 a CS and the decision was yes. 170 00:05:36,670 --> 00:05:36,930 And as a reminder here's what we have. 171 00:05:37,410 --> 00:05:39,740 A few tuples of the student id and the college name. 172 00:05:41,040 --> 00:05:41,950 So let's say we wanted to 173 00:05:42,010 --> 00:05:44,880 insert into CS accept the value 333 Berkeley. 174 00:05:46,120 --> 00:05:47,540 So, we would want that tuple 175 00:05:47,830 --> 00:05:49,590 in CS Accept based on 176 00:05:49,860 --> 00:05:51,590 the definition of CS Accept. 177 00:05:52,330 --> 00:05:53,210 We ought to know that 178 00:05:53,350 --> 00:05:55,030 we could actually insert into the 179 00:05:55,160 --> 00:05:56,650 apply relation the two 180 00:05:56,970 --> 00:05:58,330 values that were specified here 181 00:05:58,670 --> 00:05:59,640 along with CS and yes 182 00:06:00,790 --> 00:06:01,830 because this is the only two missing 183 00:06:02,310 --> 00:06:03,950 values in the apply relation. 184 00:06:05,440 --> 00:06:07,910 Let's go ahead and execute the insertion and see what happened. 185 00:06:08,360 --> 00:06:09,520 We go to our view manager, we 186 00:06:09,740 --> 00:06:10,910 look at CS accept, there's 187 00:06:11,030 --> 00:06:13,280 no sign of 333, 188 00:06:13,450 --> 00:06:15,210 but let's take a 189 00:06:15,330 --> 00:06:16,600 look at apply and we see 190 00:06:16,860 --> 00:06:18,150 that the system actually did 191 00:06:18,530 --> 00:06:20,400 apply, did insert 333 Berkeley 192 00:06:20,900 --> 00:06:21,950 into the apply relation, that's 193 00:06:22,170 --> 00:06:23,650 the translation, but it wasn't 194 00:06:24,070 --> 00:06:25,150 smart enough to put CS 195 00:06:25,650 --> 00:06:26,880 and yes in here, so 196 00:06:27,090 --> 00:06:29,130 again, we have a kind of disappearing insertion. 197 00:06:29,860 --> 00:06:32,740 Not really what we want to happen when we try to insert into the view. 198 00:06:33,610 --> 00:06:35,560 So, I mentioned that we do have a fix for that. 199 00:06:35,650 --> 00:06:37,310 We can add to our view 200 00:06:37,670 --> 00:06:39,920 definitions something called with check option. 201 00:06:40,560 --> 00:06:42,370 And let's call this CS Accept Two. 202 00:06:43,400 --> 00:06:44,500 When we add with check option, 203 00:06:45,100 --> 00:06:46,860 when the system automatically preforms a 204 00:06:47,230 --> 00:06:48,480 translation; it actually checks to 205 00:06:48,590 --> 00:06:50,070 make sure that the translation properly 206 00:06:50,660 --> 00:06:51,890 inserted the data into the view. 207 00:06:52,700 --> 00:06:53,610 So let's go ahead and create 208 00:06:53,970 --> 00:06:56,640 this view and then let's perform our insertion again. 209 00:06:58,030 --> 00:06:59,420 So let's try inserting a 210 00:06:59,620 --> 00:07:00,980 tuple into CS Accept Two 211 00:07:01,260 --> 00:07:02,100 the version of the view with 212 00:07:02,260 --> 00:07:03,640 the check option and we 213 00:07:03,720 --> 00:07:04,960 get an error because the system 214 00:07:04,980 --> 00:07:06,150 will detect that the translated 215 00:07:06,890 --> 00:07:08,260 insertion into the apply relation 216 00:07:09,170 --> 00:07:09,980 would not produce a tuple 217 00:07:10,880 --> 00:07:11,620 that appears in the view. 218 00:07:12,790 --> 00:07:14,340 We can similarly create a corrected 219 00:07:14,590 --> 00:07:17,470 version of our CSEE view, where we add the check option. 220 00:07:17,870 --> 00:07:19,570 We'll call it it CSEE 2. 221 00:07:20,570 --> 00:07:22,010 Now let's try two inserts into 222 00:07:22,370 --> 00:07:23,880 CSEE2, one where the 223 00:07:23,970 --> 00:07:25,190 student is majoring in psychology, 224 00:07:25,760 --> 00:07:26,870 and that should generate an error 225 00:07:27,280 --> 00:07:28,430 and one where the student 226 00:07:28,870 --> 00:07:31,460 is majoring in CS and that should be an okay insertion. 227 00:07:32,410 --> 00:07:33,460 We'll go ahead and execute there as 228 00:07:33,560 --> 00:07:34,670 we see where the first one generated 229 00:07:35,110 --> 00:07:36,420 an error and the second didn't, 230 00:07:36,990 --> 00:07:38,090 so to go ahead now and 231 00:07:38,210 --> 00:07:40,840 take a look at CSEE2 and 232 00:07:40,960 --> 00:07:41,760 we'll see that student 444 is 233 00:07:41,840 --> 00:07:43,210 in that view and actually 234 00:07:44,850 --> 00:07:45,780 that student is also going to be 235 00:07:45,920 --> 00:07:47,750 in the CSEE view because the 236 00:07:47,840 --> 00:07:48,940 result of the correct 237 00:07:49,490 --> 00:07:51,390 insertion was the insertion 238 00:07:51,930 --> 00:07:53,450 of student 444 into the apply table underneath. 239 00:07:54,660 --> 00:07:57,430 Now let's take a look at a few views that aren't allowed to be modified. 240 00:07:58,170 --> 00:07:59,390 The first one finds the average 241 00:07:59,980 --> 00:08:02,520 GPA of students based on their high school size. 242 00:08:03,420 --> 00:08:04,710 So let's go ahead and create the view. 243 00:08:05,170 --> 00:08:05,860 We'll take a look at it. 244 00:08:06,800 --> 00:08:08,120 And we'll see that it contains 245 00:08:08,670 --> 00:08:10,150 for each high school size, the 246 00:08:10,460 --> 00:08:11,620 average GPA of students who 247 00:08:11,690 --> 00:08:13,270 attended this high school of that size. 248 00:08:14,180 --> 00:08:15,340 Now let's suppose that we 249 00:08:15,450 --> 00:08:16,540 decide we want to delete from 250 00:08:16,660 --> 00:08:19,100 that view the tuples where 251 00:08:19,450 --> 00:08:20,930 the high school size is less than five hundred. 252 00:08:21,550 --> 00:08:22,770 We go ahead and run that 253 00:08:22,880 --> 00:08:25,610 and we see that it's not updatable according to our system. 254 00:08:26,000 --> 00:08:27,130 And if you think about it, 255 00:08:27,230 --> 00:08:28,270 it really doesn't make sense 256 00:08:28,580 --> 00:08:31,230 to try to delete tuple from this view because what would you delete? 257 00:08:31,590 --> 00:08:32,760 I suppose you could delete all the 258 00:08:32,900 --> 00:08:33,670 students who went to a 259 00:08:33,800 --> 00:08:34,950 high school of that size 260 00:08:35,700 --> 00:08:36,890 but that doesn't seem to 261 00:08:37,080 --> 00:08:39,510 be what the user would probably be intending to happen. 262 00:08:40,130 --> 00:08:41,590 Similarly we could try 263 00:08:41,780 --> 00:08:43,620 to insert a tuple into high school 264 00:08:43,930 --> 00:08:45,370 GPA and again we'll get an 265 00:08:45,490 --> 00:08:47,610 error and again it just doesn't make since. 266 00:08:47,800 --> 00:08:49,950 How would we insert a tuple with an average GPA. 267 00:08:50,300 --> 00:08:51,320 What students would we insert? 268 00:08:51,780 --> 00:08:53,510 Some fabricated students from small high schools? 269 00:08:54,290 --> 00:08:55,340 Just again, doesn't make a 270 00:08:55,430 --> 00:08:57,370 lot of sense so the system disallows those updates. 271 00:08:59,050 --> 00:09:00,410 So the previous example wasn't updatable, 272 00:09:01,040 --> 00:09:02,110 primarily due to the aggregation. 273 00:09:03,420 --> 00:09:05,090 Here's another example that also is not updatable. 274 00:09:05,940 --> 00:09:07,110 Here we're taking just the majors 275 00:09:07,710 --> 00:09:09,840 from the apply relation, generating a list of them. 276 00:09:10,900 --> 00:09:11,740 So let's take a look at 277 00:09:11,840 --> 00:09:13,450 what the view would contain and we 278 00:09:13,770 --> 00:09:15,560 see it's again, a list of majors. 279 00:09:16,150 --> 00:09:17,580 We have a student here with a null major. 280 00:09:18,160 --> 00:09:19,690 Now would it make 281 00:09:19,990 --> 00:09:22,100 sense to insert or delete from this view? 282 00:09:22,820 --> 00:09:24,250 Well, inserting certainly not. 283 00:09:24,550 --> 00:09:26,880 We'd have to fabricate the student who's applying to that major. 284 00:09:27,940 --> 00:09:29,140 Deleting could make more 285 00:09:29,500 --> 00:09:30,480 sense if we wanted to delete 286 00:09:30,750 --> 00:09:31,930 all the apply tuples for 287 00:09:32,120 --> 00:09:34,550 a particular major, but probably that's not what the user intended. 288 00:09:35,470 --> 00:09:36,430 So if we try to, for 289 00:09:36,850 --> 00:09:37,880 example, add a chemistry 290 00:09:38,450 --> 00:09:40,110 major we would get an error. 291 00:09:40,230 --> 00:09:41,410 And if we decided, for example, to 292 00:09:41,540 --> 00:09:43,690 delete CS major we'd again get an error. 293 00:09:44,220 --> 00:09:45,430 So this view is considered 294 00:09:46,640 --> 00:09:47,960 non-updatable by the underlying 295 00:09:48,470 --> 00:09:49,860 sequel system and by the, 296 00:09:49,950 --> 00:09:50,890 this is my SQL that we're 297 00:09:51,000 --> 00:09:52,440 running, but also by the SQL standard. 298 00:09:53,750 --> 00:09:54,940 So one of the other conditions we 299 00:09:55,110 --> 00:09:56,250 saw for a view to 300 00:09:56,300 --> 00:09:57,620 be update-able is that it 301 00:09:57,810 --> 00:09:59,290 doesn't have a use of 302 00:09:59,420 --> 00:10:01,470 the outer relation also in a sub-query. 303 00:10:02,250 --> 00:10:04,760 And so I've created a view here that violates that constraint. 304 00:10:05,540 --> 00:10:07,190 This says let's find students 305 00:10:07,450 --> 00:10:09,450 where some other student has 306 00:10:09,670 --> 00:10:10,770 the same GPA and the 307 00:10:10,820 --> 00:10:12,860 same high school size and we'll call that view Non-Unique. 308 00:10:14,050 --> 00:10:15,130 So we've got the student in 309 00:10:15,290 --> 00:10:16,180 the outer query, the student in 310 00:10:16,360 --> 00:10:18,470 the inner query, we'll go ahead and create the view. 311 00:10:18,810 --> 00:10:19,630 We can take a look at the 312 00:10:19,800 --> 00:10:21,160 view, and here we'll 313 00:10:21,430 --> 00:10:22,570 see that we have three students 314 00:10:23,880 --> 00:10:25,020 where some other student has 315 00:10:25,320 --> 00:10:26,380 the same GPA and high 316 00:10:26,550 --> 00:10:27,710 school size, and they're all 317 00:10:27,980 --> 00:10:29,000 the same GPA and high school 318 00:10:29,230 --> 00:10:30,080 size it turns out in our 319 00:10:30,250 --> 00:10:32,610 small database, and let's think about it. 320 00:10:32,670 --> 00:10:33,390 Would it make sense 321 00:10:33,600 --> 00:10:35,110 to be able to modify this view. 322 00:10:35,810 --> 00:10:37,190 Well, if we wanted to, 323 00:10:37,330 --> 00:10:38,510 for example, delete the two 324 00:10:38,920 --> 00:10:41,470 Amy tuples, the underlying 325 00:10:42,290 --> 00:10:43,700 system could delete the Amys 326 00:10:44,630 --> 00:10:45,480 that would actually have the effect 327 00:10:45,750 --> 00:10:46,860 of deleting Doris if you 328 00:10:46,940 --> 00:10:48,010 think about it; or they 329 00:10:48,120 --> 00:10:49,680 could delete the first 330 00:10:49,950 --> 00:10:51,990 Amy and Doris and that would delete the other Amy. 331 00:10:52,230 --> 00:10:53,560 So there's quite a few underlying 332 00:10:54,570 --> 00:10:56,670 modifications that could perform 333 00:10:57,080 --> 00:10:59,740 the modification we're trying to perform (by the way, here's that modification). 334 00:11:01,080 --> 00:11:02,040 So, if we try to run 335 00:11:02,250 --> 00:11:04,250 the delete command and it takes the Amy's out of the view. 336 00:11:04,850 --> 00:11:05,490 Again, it's not allowed, 337 00:11:06,360 --> 00:11:08,790 that's because there's no obvious translation. 338 00:11:09,700 --> 00:11:11,500 There's many possible translations in 339 00:11:11,690 --> 00:11:13,230 the underlying base tables and again 340 00:11:13,560 --> 00:11:15,590 the SQL standard disallows subqueries referencing 341 00:11:16,530 --> 00:11:17,620 the same table as the 342 00:11:17,720 --> 00:11:18,970 outer query because of the 343 00:11:19,020 --> 00:11:21,400 ambiguity of modifications on this type of view. 344 00:11:22,790 --> 00:11:23,810 Now that's not to say that 345 00:11:24,010 --> 00:11:25,280 subqueries aren't allowed at 346 00:11:25,360 --> 00:11:26,460 all in views that can 347 00:11:26,690 --> 00:11:27,760 be modified and here's an 348 00:11:27,850 --> 00:11:28,680 example where we have a 349 00:11:28,740 --> 00:11:30,490 subquery and the view is allowed to be modified. 350 00:11:31,780 --> 00:11:33,190 This is a view of 351 00:11:33,590 --> 00:11:34,710 all the information about students 352 00:11:35,200 --> 00:11:36,190 when the student has applied 353 00:11:37,010 --> 00:11:38,270 somewhere for a major that's 354 00:11:38,560 --> 00:11:39,770 like the major that's 355 00:11:39,980 --> 00:11:41,200 a biology major, so we're 356 00:11:41,330 --> 00:11:43,860 using a SQL-like predicate here to match the major. 357 00:11:45,570 --> 00:11:46,090 So let's go ahead and create the view. 358 00:11:47,200 --> 00:11:48,220 We'll take a look at 359 00:11:48,260 --> 00:11:50,090 it and we'll see here 360 00:11:50,290 --> 00:11:51,680 that we have three students who 361 00:11:51,740 --> 00:11:53,860 have applied to some biology-type major. 362 00:11:55,350 --> 00:11:56,380 Let's say that we wanted to 363 00:11:56,570 --> 00:11:57,920 delete the first tuple from the 364 00:11:58,000 --> 00:11:59,550 view, the tuple with the student name Bob. 365 00:11:59,980 --> 00:12:02,150 So we wanted to run this command here. 366 00:12:03,020 --> 00:12:04,220 If we take a look at 367 00:12:04,320 --> 00:12:05,580 the view definition, we can see 368 00:12:05,640 --> 00:12:06,580 that what would make sense 369 00:12:06,910 --> 00:12:08,690 here, because we are selecting students, 370 00:12:08,840 --> 00:12:10,180 is to actually delete the student 371 00:12:10,610 --> 00:12:12,820 tuple for Bob, and that is what the system will do. 372 00:12:13,380 --> 00:12:14,940 There would be other possibilities, like 373 00:12:15,240 --> 00:12:16,630 deleting the apply tuple, so 374 00:12:17,000 --> 00:12:19,760 there is ambiguity here, but 375 00:12:19,920 --> 00:12:21,000 the decision in the MySQL 376 00:12:21,310 --> 00:12:22,500 system and in the 377 00:12:22,580 --> 00:12:23,740 SQL standard is that if you 378 00:12:23,890 --> 00:12:25,710 have an outer query that's 379 00:12:26,420 --> 00:12:27,740 referencing a single table, which is 380 00:12:27,870 --> 00:12:28,860 a requirement in the SQL 381 00:12:28,920 --> 00:12:30,130 standard, then a deletion 382 00:12:30,370 --> 00:12:31,630 from the view is going to 383 00:12:32,030 --> 00:12:33,840 result in a deletion from that one table. 384 00:12:35,000 --> 00:12:36,700 So we'll go ahead and we'll run the delete. 385 00:12:37,140 --> 00:12:38,060 We see that it happened. 386 00:12:38,560 --> 00:12:39,210 We'll take a look at BIO, 387 00:12:39,740 --> 00:12:40,680 we'll see that the first tuple 388 00:12:40,860 --> 00:12:42,130 is gone, but what's most 389 00:12:42,680 --> 00:12:43,720 important is to take a 390 00:12:43,830 --> 00:12:45,230 look at the student table, and we'll 391 00:12:45,500 --> 00:12:48,020 see that Bob is gone from the student table. 392 00:12:48,740 --> 00:12:49,980 Now it is the fact 393 00:12:50,180 --> 00:12:52,320 that Bob's apply tupple actually is still present. 394 00:12:52,720 --> 00:12:55,210 Bob was ID 234, applying to Berkley in biology. 395 00:12:56,570 --> 00:12:57,480 If we have set up 396 00:12:57,540 --> 00:12:59,030 our database properly we'd probably 397 00:12:59,160 --> 00:13:00,280 have a referential and integrity constraint 398 00:13:01,120 --> 00:13:01,960 from apply to student. 399 00:13:02,300 --> 00:13:03,640 And then the deletion to student 400 00:13:03,840 --> 00:13:05,110 would have either generated an 401 00:13:05,270 --> 00:13:06,810 error or generated a cascaded delete. 402 00:13:07,120 --> 00:13:08,000 But we didn't set up those 403 00:13:08,190 --> 00:13:09,570 referential integrity constraints in 404 00:13:09,780 --> 00:13:11,260 this demo so only the 405 00:13:11,340 --> 00:13:12,460 student tuple for Bob was 406 00:13:12,690 --> 00:13:15,300 deleted as a result of the view modification command. 407 00:13:16,700 --> 00:13:18,580 Now coming back to our bio view. 408 00:13:19,050 --> 00:13:21,940 What if we decided we actually wanted to perfrom an insertion into the view. 409 00:13:22,770 --> 00:13:23,610 Let's take a look at 410 00:13:23,690 --> 00:13:24,780 what we have and let's 411 00:13:24,870 --> 00:13:26,590 suppose we want to insert 412 00:13:27,200 --> 00:13:28,300 another student who is applying to 413 00:13:28,660 --> 00:13:29,670 biology but remember the view 414 00:13:29,880 --> 00:13:32,070 is defined over the, here 415 00:13:32,280 --> 00:13:34,720 we go, I'm sorry, over the student table. 416 00:13:35,500 --> 00:13:36,660 So that will result in 417 00:13:36,770 --> 00:13:37,930 an insertion into the student 418 00:13:38,300 --> 00:13:39,380 table just like the deletion resulted 419 00:13:39,880 --> 00:13:41,160 in a deletion from the student table. 420 00:13:41,900 --> 00:13:44,050 So let's say we want to insert a new student, Karen. 421 00:13:45,100 --> 00:13:45,800 Here's her information. 422 00:13:46,480 --> 00:13:49,140 And we want to insert her into the bio view. 423 00:13:49,460 --> 00:13:50,650 So let's go ahead and run the command. 424 00:13:51,060 --> 00:13:52,060 And it seems to have been successful. 425 00:13:53,190 --> 00:13:55,830 But, let's take a look at the view and there is no sign of Karen. 426 00:13:56,580 --> 00:13:57,820 Well, why is there no sign? 427 00:13:58,070 --> 00:13:59,140 Because all it did was 428 00:13:59,470 --> 00:14:01,220 insert a tuple into the student table. 429 00:14:01,750 --> 00:14:03,140 Here we go, that's the basic 430 00:14:03,650 --> 00:14:05,220 translation and there's no 431 00:14:05,660 --> 00:14:07,780 tuples for Karen in the apply table. 432 00:14:08,080 --> 00:14:09,420 We certainly didn't fabricate some 433 00:14:09,650 --> 00:14:11,040 application to some major 434 00:14:11,400 --> 00:14:13,450 that matches biology, so this 435 00:14:13,530 --> 00:14:15,360 is again an example where 436 00:14:15,650 --> 00:14:17,020 we can have the underlying 437 00:14:17,440 --> 00:14:19,110 modification not produce a tuple in the view. 438 00:14:19,950 --> 00:14:21,420 But, it will effect the actual database. 439 00:14:22,380 --> 00:14:25,270 So again we can use that check option to make sure that doesn't happen. 440 00:14:26,500 --> 00:14:27,500 We'll create a new view called 441 00:14:27,780 --> 00:14:29,610 Bio Two and this one has the check option. 442 00:14:30,240 --> 00:14:30,670 Let's go ahead. 443 00:14:31,600 --> 00:14:32,630 And now let's see what 444 00:14:32,810 --> 00:14:33,780 happens when we try to insert 445 00:14:34,000 --> 00:14:35,290 into bio2, so we'll insert 446 00:14:35,600 --> 00:14:37,620 another tuple and this 447 00:14:37,880 --> 00:14:39,440 time we get a failure because 448 00:14:39,790 --> 00:14:41,330 this can't translate automatically into 449 00:14:42,160 --> 00:14:43,660 an insertion that would 450 00:14:43,770 --> 00:14:45,720 actually have the effect that we want on the view. 451 00:14:46,380 --> 00:14:47,260 So we saw that we could delete 452 00:14:47,670 --> 00:14:48,550 from the view with no problem, 453 00:14:48,960 --> 00:14:50,520 but we can't insert into the view. 454 00:14:51,300 --> 00:14:52,430 So, you might wonder why don't 455 00:14:52,910 --> 00:14:55,790 we always include the with check option when we create views. 456 00:14:56,400 --> 00:14:57,340 And certainly, I would say 457 00:14:57,480 --> 00:15:00,030 that's a pretty good idea, but it will have an effect on efficiency. 458 00:15:00,590 --> 00:15:02,320 So, if one can guarantee that 459 00:15:02,560 --> 00:15:03,990 all of the updates that 460 00:15:04,110 --> 00:15:05,390 are made to the view are going 461 00:15:05,670 --> 00:15:07,150 to have the desired effect when 462 00:15:07,280 --> 00:15:08,460 they are translated, then the check 463 00:15:08,710 --> 00:15:09,750 option can be left out, but 464 00:15:09,940 --> 00:15:12,450 if there's any question it's a good idea to include it. 465 00:15:13,610 --> 00:15:15,390 So now let's take a look at a view that involves a join. 466 00:15:16,110 --> 00:15:18,970 This view gathers information about students who have applied to Stanford. 467 00:15:20,030 --> 00:15:21,200 We can also see that in 468 00:15:21,410 --> 00:15:22,510 the view definition, we're giving names 469 00:15:23,140 --> 00:15:24,230 to attributes in the view. 470 00:15:24,880 --> 00:15:26,130 If we don't specify these names 471 00:15:26,380 --> 00:15:27,590 explicitly as in all 472 00:15:27,810 --> 00:15:29,530 of our previous examples, it just 473 00:15:29,740 --> 00:15:31,360 takes the names from 474 00:15:31,610 --> 00:15:33,880 the schema of the select statement defining the view. 475 00:15:34,500 --> 00:15:35,470 Now, that actually wouldn't be allowed 476 00:15:35,710 --> 00:15:37,750 in this case because we have two attributes called SID. 477 00:15:38,570 --> 00:15:39,480 So what we're going to do is, 478 00:15:39,670 --> 00:15:41,270 we're going to call the student ID 479 00:15:41,400 --> 00:15:43,240 that comes from the student relation, SID. 480 00:15:44,070 --> 00:15:46,320 The one that comes from the apply relation, we'll call AID. 481 00:15:46,860 --> 00:15:47,930 Those are always going to be 482 00:15:48,040 --> 00:15:49,080 equal as we'll see, but 483 00:15:49,180 --> 00:15:50,310 they are going to 484 00:15:50,370 --> 00:15:52,210 have some interesting effects when we modify the view. 485 00:15:52,510 --> 00:15:53,770 There will be some meaning to 486 00:15:53,960 --> 00:15:56,150 having two different attributes, also have 487 00:15:56,390 --> 00:15:57,490 the student name that comes from 488 00:15:57,540 --> 00:15:59,810 the student table, and the major that comes from the apply table. 489 00:16:00,600 --> 00:16:02,030 Let's go ahead and create the 490 00:16:02,090 --> 00:16:03,650 view and we'll take a look at it's contents. 491 00:16:05,350 --> 00:16:08,280 We can see that we have a set of students who have applied to Stanford. 492 00:16:09,140 --> 00:16:11,950 Now lets talk about burning modification commands against this view. 493 00:16:13,030 --> 00:16:14,140 Lets say that we wanted to change 494 00:16:14,280 --> 00:16:16,670 our two students who have applied to major in CS. 495 00:16:16,820 --> 00:16:18,160 They have their names not be Helen 496 00:16:18,520 --> 00:16:20,070 and Irene, but be CS Major instead. 497 00:16:20,680 --> 00:16:23,370 Of course we wouldn't want to do that, but it's good for illustrative purposes. 498 00:16:24,630 --> 00:16:25,560 So here's the command and 499 00:16:25,730 --> 00:16:27,080 again we're updating through the view 500 00:16:27,290 --> 00:16:28,260 so we're saying update the STAN 501 00:16:28,810 --> 00:16:29,620 view to set the student 502 00:16:29,960 --> 00:16:32,180 name to be CS major if they've applied to major in CS. 503 00:16:32,310 --> 00:16:34,530 We'll go ahead and run that. 504 00:16:34,640 --> 00:16:35,400 And let's take a look at our view. 505 00:16:36,640 --> 00:16:38,350 Now we see that Helen and 506 00:16:38,640 --> 00:16:39,870 Irene are no longer a 507 00:16:40,150 --> 00:16:42,460 "Helen and Irene," but they're rather both called "CS Major." 508 00:16:43,350 --> 00:16:45,780 Now, we'll take a look at what the update translated to. 509 00:16:46,080 --> 00:16:46,960 So, we'll go take a look 510 00:16:47,090 --> 00:16:48,440 at our students and we'll 511 00:16:48,630 --> 00:16:50,030 see that, what, who are 512 00:16:50,120 --> 00:16:52,240 Helen and Irene are now changed to CS major. 513 00:16:52,560 --> 00:16:54,120 So how did the system 514 00:16:54,520 --> 00:16:57,300 know to translate that modification to the student 515 00:16:57,830 --> 00:16:59,850 table, given that the view is defined over two tables. 516 00:17:00,690 --> 00:17:01,880 Well, we're updating the student 517 00:17:02,100 --> 00:17:03,240 name and the student name 518 00:17:03,530 --> 00:17:04,860 comes from the Student table, 519 00:17:05,620 --> 00:17:06,860 so the system will basically 520 00:17:07,110 --> 00:17:08,240 map the attributes that are 521 00:17:08,310 --> 00:17:09,820 being modified to the underlying 522 00:17:10,200 --> 00:17:11,280 table where the attribute came 523 00:17:11,600 --> 00:17:13,040 from and then it 524 00:17:13,100 --> 00:17:14,380 will perform the corresponding modification 525 00:17:14,950 --> 00:17:15,910 to that underlying table. 526 00:17:17,310 --> 00:17:19,330 Now that approach can introduce some problems. 527 00:17:20,200 --> 00:17:21,430 Let's say that we decide we're 528 00:17:21,560 --> 00:17:22,730 going the update the AID 529 00:17:23,470 --> 00:17:24,240 attribute in our view. 530 00:17:24,910 --> 00:17:26,100 If we update the AID attribute, 531 00:17:26,500 --> 00:17:27,830 the system will translate that into 532 00:17:28,050 --> 00:17:29,530 a modification to the Apply 533 00:17:30,000 --> 00:17:31,180 relation, but then we'll 534 00:17:31,370 --> 00:17:33,460 see that the tuples will no longer properly join. 535 00:17:34,000 --> 00:17:35,360 So let's see that through an example. 536 00:17:36,380 --> 00:17:37,210 So here's our view and let's 537 00:17:37,430 --> 00:17:38,530 decide we'll take our first 538 00:17:38,840 --> 00:17:39,910 tuple and we're going to update it's 539 00:17:40,440 --> 00:17:43,130 AID to not be 123 anymore but to be 666. 540 00:17:44,590 --> 00:17:45,740 So here's the command that does that. 541 00:17:46,220 --> 00:17:49,090 We go ahead and run it and let's see what happened. 542 00:17:49,860 --> 00:17:52,670 In our view, we refresh and we find that that tuple is gone. 543 00:17:52,870 --> 00:17:54,110 We did not get the 123 544 00:17:55,290 --> 00:17:56,120 updated to 666, and that's 545 00:17:57,330 --> 00:17:58,740 because down in our Apply 546 00:17:59,360 --> 00:18:00,690 table we have that 547 00:18:00,860 --> 00:18:01,860 modification and we can 548 00:18:01,910 --> 00:18:03,090 see it's the first tuple to be 549 00:18:03,320 --> 00:18:05,060 modified to be 666,but in our 550 00:18:05,190 --> 00:18:06,530 student table, that student 551 00:18:06,890 --> 00:18:08,400 is still 123, so it's another 552 00:18:08,710 --> 00:18:10,400 one of these examples whereas the 553 00:18:10,580 --> 00:18:12,020 underlying tables were updated as 554 00:18:12,150 --> 00:18:13,230 a result of asking for an 555 00:18:13,400 --> 00:18:14,700 update to the view, but we 556 00:18:14,850 --> 00:18:16,200 didn't get the effect on the view we wanted. 557 00:18:16,860 --> 00:18:18,110 By now you've probably figured out 558 00:18:18,340 --> 00:18:18,950 what we're going to do is 559 00:18:19,210 --> 00:18:20,270 add a check option to our 560 00:18:20,400 --> 00:18:22,490 view to make sure that bad behavior can't occur. 561 00:18:23,360 --> 00:18:24,920 So, here is the same view with the check option. 562 00:18:25,210 --> 00:18:26,790 We'll call this view, Stan 2, 563 00:18:27,240 --> 00:18:28,570 and then we'll try to 564 00:18:28,690 --> 00:18:30,100 do a similar update to 565 00:18:30,370 --> 00:18:31,330 Stan 2 that we did 566 00:18:31,530 --> 00:18:32,850 before, we'll try to 567 00:18:32,980 --> 00:18:34,070 update Stan 2 to set 568 00:18:34,710 --> 00:18:35,780 the AID to 777 569 00:18:35,930 --> 00:18:37,840 we go ahead 570 00:18:38,020 --> 00:18:39,060 and run that, and as 571 00:18:39,450 --> 00:18:40,780 expected the check option fails 572 00:18:41,150 --> 00:18:42,360 and it doesn't allow the update. 573 00:18:43,730 --> 00:18:44,560 Now let's go back to our 574 00:18:44,740 --> 00:18:45,590 original view without the check 575 00:18:45,850 --> 00:18:47,000 option and let's see 576 00:18:47,160 --> 00:18:49,290 if we're allowed to perform insertions into the view. 577 00:18:49,780 --> 00:18:52,150 So first let's remind ourselves of the contents of the view. 578 00:18:53,360 --> 00:18:54,020 Here's what we have now. 579 00:18:54,890 --> 00:18:55,870 And I'm going to try and 580 00:18:56,130 --> 00:18:57,320 insert into this view a tuple 581 00:18:57,410 --> 00:18:59,340 and I'm just going to specify 582 00:18:59,850 --> 00:19:00,790 the student ID and the 583 00:19:00,830 --> 00:19:02,490 name, and let's see what happens. 584 00:19:04,160 --> 00:19:05,090 In SQL when I insert 585 00:19:05,350 --> 00:19:06,260 a tuple, if I only want 586 00:19:06,470 --> 00:19:07,330 to specify some of the 587 00:19:07,590 --> 00:19:08,590 attributes I can list them in 588 00:19:08,690 --> 00:19:10,160 the insertion command, give volumes 589 00:19:10,590 --> 00:19:12,660 for those and then we will put null for the other values. 590 00:19:13,560 --> 00:19:15,770 So lets see if the system allows us to insert into STAN. 591 00:19:16,380 --> 00:19:17,060 Well, it seemed to. 592 00:19:17,220 --> 00:19:18,150 And let's see what happened. 593 00:19:19,150 --> 00:19:19,970 Here's our STAN view. 594 00:19:20,620 --> 00:19:22,460 And we refresh and it looks like nothing happened. 595 00:19:23,290 --> 00:19:24,570 Let's look underneath at our 596 00:19:25,230 --> 00:19:27,260 apply tuple, and we 597 00:19:27,560 --> 00:19:29,550 don't see any 777s happening there. 598 00:19:29,940 --> 00:19:30,750 But if we take a look at 599 00:19:31,300 --> 00:19:34,070 our student tuple, in fact 777 Lance did appear. 600 00:19:35,130 --> 00:19:36,390 So, lets try something similar 601 00:19:36,870 --> 00:19:38,350 on our view that has the check option. 602 00:19:38,900 --> 00:19:40,480 So now let's try inserting 888 603 00:19:41,300 --> 00:19:42,380 Mary into the view 604 00:19:42,580 --> 00:19:43,390 when we have the check option 605 00:19:43,800 --> 00:19:44,860 and we see that it fails 606 00:19:45,190 --> 00:19:47,200 in that case, which we would not want, presumably. 607 00:19:48,840 --> 00:19:50,030 But what if we first inserted 608 00:19:50,580 --> 00:19:52,190 into apply a tuple for 609 00:19:52,390 --> 00:19:53,950 Mary applying to Stanford and 610 00:19:54,090 --> 00:19:55,680 then we try to insert Mary into the view? 611 00:19:57,120 --> 00:20:00,150 Now, everything looks good, and let's go take a look at the view first. 612 00:20:01,670 --> 00:20:02,620 Here's Mary and she was 613 00:20:02,830 --> 00:20:03,870 properly inserted into the 614 00:20:03,980 --> 00:20:05,370 view applying to History at 615 00:20:05,440 --> 00:20:06,830 Stanford, and And if 616 00:20:06,950 --> 00:20:08,480 we take a look at student, Mary 617 00:20:08,890 --> 00:20:10,130 has been inserted there as well. 618 00:20:10,500 --> 00:20:11,760 And it's no problem having the 619 00:20:11,840 --> 00:20:12,870 null values here because they 620 00:20:13,000 --> 00:20:13,990 weren't involved in our views 621 00:20:14,280 --> 00:20:16,640 and those attributes are allowed to be null in the underlying tables. 622 00:20:18,250 --> 00:20:19,760 Now let's try something similar with Nancy. 623 00:20:20,950 --> 00:20:21,960 Before we try to insert 624 00:20:22,170 --> 00:20:23,320 Nancy into our view, and 625 00:20:23,470 --> 00:20:24,650 again this is STAN2 with the 626 00:20:24,710 --> 00:20:26,050 check option, we'll insert a 627 00:20:26,360 --> 00:20:28,100 tuple for Nancy into the apply relation. 628 00:20:28,630 --> 00:20:29,840 But, we are going to insert 629 00:20:30,150 --> 00:20:32,170 an application to MIT, not to Stanford. 630 00:20:32,830 --> 00:20:34,630 So hopefully we'll get 631 00:20:34,860 --> 00:20:36,200 the first insertion but the 632 00:20:36,330 --> 00:20:37,650 check option fails because when 633 00:20:37,770 --> 00:20:39,510 we try to insert Nancy into 634 00:20:39,790 --> 00:20:40,940 the student table as a 635 00:20:40,970 --> 00:20:41,870 result of the view rather 636 00:20:41,930 --> 00:20:43,520 than the system tries to insert Nancy. 637 00:20:44,390 --> 00:20:45,630 Nancy will join her, but 638 00:20:45,800 --> 00:20:48,120 not applying to Stanford, only applying to MIT. 639 00:20:49,320 --> 00:20:50,330 And the last thing that we'll do 640 00:20:50,570 --> 00:20:53,130 is attempt to perform a deletion from the Stanford view. 641 00:20:53,430 --> 00:20:54,560 Let's say we want to 642 00:20:54,620 --> 00:20:57,370 delete the student with ID 678. 643 00:20:57,550 --> 00:20:58,870 So the first tuple from the view. 644 00:20:59,890 --> 00:21:01,880 So here's our command and 645 00:21:01,940 --> 00:21:03,660 when we run the command, we see that we get an error. 646 00:21:03,810 --> 00:21:05,590 We cannot delete from the join view. 647 00:21:05,930 --> 00:21:07,330 So the MySQL system has decided 648 00:21:07,830 --> 00:21:09,200 not to implement deletions from 649 00:21:09,470 --> 00:21:10,760 joined views, and in that 650 00:21:10,810 --> 00:21:13,550 case it's because again, it's quite ambiguous how that would be translated. 651 00:21:14,240 --> 00:21:15,340 A deletion from the STAN 652 00:21:15,770 --> 00:21:17,080 view could be achieved by 653 00:21:17,250 --> 00:21:18,740 deleting from the student table or 654 00:21:18,960 --> 00:21:20,240 from the applied table or from 655 00:21:20,520 --> 00:21:21,600 both and because of that 656 00:21:21,670 --> 00:21:23,530 ambiguity the system doesn't allow it. 657 00:21:23,980 --> 00:21:24,840 When we had a view that 658 00:21:24,950 --> 00:21:26,330 was defined over one table even 659 00:21:26,510 --> 00:21:27,560 with a sub query it 660 00:21:28,000 --> 00:21:29,060 was sort of more clear that 661 00:21:29,210 --> 00:21:30,220 a deletion from the view 662 00:21:30,440 --> 00:21:32,450 should delete from that outer reference table. 663 00:21:33,910 --> 00:21:35,850 That completes our demonstration of automatic view modification. 664 00:21:36,920 --> 00:21:37,800 We saw that in the MySQL 665 00:21:37,960 --> 00:21:38,990 system when we create 666 00:21:39,480 --> 00:21:40,760 views and asked to 667 00:21:40,840 --> 00:21:42,220 write modification commands on the 668 00:21:42,350 --> 00:21:43,390 views, depending on the type 669 00:21:43,650 --> 00:21:44,400 of view and the type of modification, 670 00:21:45,590 --> 00:21:47,410 that modification may be translated 671 00:21:47,840 --> 00:21:50,420 automatically into a modification on the underlying base tables. 672 00:21:51,270 --> 00:21:52,470 Now, if we don't include the 673 00:21:52,530 --> 00:21:54,150 check option, sometimes that translation 674 00:21:54,910 --> 00:21:56,690 will result in modifications to 675 00:21:56,790 --> 00:21:58,030 the base tables that don't 676 00:21:58,380 --> 00:21:59,980 have the proper effect of modifying the view. 677 00:22:00,590 --> 00:22:01,550 If we include the check option, 678 00:22:01,860 --> 00:22:03,920 then the system will guarantee that 679 00:22:04,200 --> 00:22:05,270 the modifications it makes to the 680 00:22:05,410 --> 00:22:07,860 base tables do result in a modification to the view. 681 00:22:09,240 --> 00:22:10,050 MySQL is a little 682 00:22:10,140 --> 00:22:11,030 more generous in what views 683 00:22:11,450 --> 00:22:14,060 it allows to be modified over what the sequel standard specifies. 684 00:22:14,650 --> 00:22:15,960 For example, MySQL does 685 00:22:16,180 --> 00:22:18,030 allow joined views with certain modifications. 686 00:22:19,780 --> 00:22:20,820 Also, in the SQL standard, 687 00:22:21,610 --> 00:22:23,570 every view is either considered updatable or not. 688 00:22:23,960 --> 00:22:25,440 If a view is updatable, any of 689 00:22:25,540 --> 00:22:26,850 the modifications can be performed 690 00:22:27,250 --> 00:22:28,660 on it, insertions, deletions or updates. 691 00:22:29,530 --> 00:22:30,580 In MySQL since it's a 692 00:22:30,690 --> 00:22:32,180 bit more generous about what views 693 00:22:32,580 --> 00:22:35,180 can be modified, it's also a little bit more fine-grained. 694 00:22:35,740 --> 00:22:36,850 So, we can have views that have 695 00:22:37,560 --> 00:22:38,850 certain types of updates allowed, for 696 00:22:39,000 --> 00:22:40,370 example insertions and updates, 697 00:22:41,070 --> 00:22:43,340 while other types might not be allowed, for example deletions.