WEBVTT 00:00.090 --> 00:00.510 All right. 00:00.690 --> 00:03.660 So let's hop over to cloud 9 and we'll start simple. 00:03.810 --> 00:05.820 Let's work with the reviewers tables. 00:05.820 --> 00:07.080 The simplest one. 00:07.080 --> 00:08.930 So we're going to implement that first. 00:09.330 --> 00:12.240 So we'll go over here and just to be totally transparent. 00:12.270 --> 00:15.830 I created a new database just so we have a fresh place to work. 00:15.840 --> 00:22.280 You don't have to do that as long as you don't already have tables named series reviewers and reviews. 00:22.470 --> 00:24.960 If you do you may want to make a new database. 00:25.200 --> 00:31.950 So I'm working on one called something ridiculous and stupid that I probably will regret. 00:33.410 --> 00:35.240 TV review app. 00:35.330 --> 00:36.590 Not too bad. 00:36.620 --> 00:59.350 So we're going to start off by creating a table which you're going to call reviewers work with. 01:00.230 --> 01:09.290 So we'll start off by creating our table which we'll call reviewers and reviewers only has three things 01:09.470 --> 01:20.370 Id first name and last name so we'll start off with ID and first name and last name and we know ID needs 01:20.380 --> 01:22.920 to be an ant that should be familiar by now. 01:23.050 --> 01:31.780 Autoincrement primary key first name will just be char 100 and same thing for last name. 01:31.780 --> 01:34.640 So pretty straightforward table to start with. 01:34.750 --> 01:40.750 For reviewers the next thing that will move on to before we insert things that actually get our tables 01:40.750 --> 01:41.540 going. 01:41.650 --> 01:44.470 So all tables first and then insert data. 01:44.980 --> 01:48.370 So next up will do series ID. 01:48.610 --> 01:51.050 Title released here and genre. 01:51.490 --> 02:00.770 So create table series and inside here will have an ID which will be exactly the same. 02:00.890 --> 02:08.330 Autoincrement primary key and primary keys are especially important because we're going to use them 02:08.330 --> 02:13.650 as foreign keys in the reviews table and we need to have valid rows that were pointing back to. 02:13.790 --> 02:18.690 We need to make sure whatever the series idea is here that it's actually a series in our database. 02:18.710 --> 02:23.670 It's not pointing to some non-existent show or non-existent reviewer in the case of reviewer ID. 02:24.070 --> 02:24.510 OK. 02:24.860 --> 02:28.780 So the next thing is the title of the series which we'll just make of our char 02:33.120 --> 02:40.480 and then once we do that we'll move on to release date or release year in our case and we have a couple 02:40.480 --> 02:44.220 of options for the data type here so we could make it a date. 02:44.230 --> 02:50.530 But all we want is the year and a date has this kind of format where we have the year and then the months 02:50.800 --> 02:54.540 and then you know and so on which we don't care about. 02:54.820 --> 03:02.660 So we could make it an aunt and that would work but then potentially someone could accidentally insert 03:02.660 --> 03:09.890 something with a release year of like 10000 or 9 9 9 9 9 9 which is not a valid year. 03:10.070 --> 03:14.330 So there's actually another data type that we can work with which I haven't shown you. 03:14.390 --> 03:21.000 It's just called year and year for it will be a four digit year like 1999 or 2004. 03:21.050 --> 03:25.170 There used to be a year to type but I believe it's been deprecated now. 03:25.220 --> 03:27.100 So we'll work with year four. 03:27.140 --> 03:29.710 That's not the point of this lecture so don't let that hang you up. 03:29.750 --> 03:31.830 I just want to show you that it exists. 03:32.060 --> 03:38.120 So we have that and then the next thing that we have is genre and genre will just be of our char as 03:38.120 --> 03:38.940 well. 03:39.610 --> 03:40.260 OK. 03:40.730 --> 03:42.740 So we have those two tables. 03:42.890 --> 03:47.720 Now let's actually start with those and let's make sure that they work. 03:48.060 --> 03:50.120 Those are first one. 03:50.590 --> 03:53.080 And here's our second series. 03:53.150 --> 03:54.370 Great. 03:54.410 --> 03:58.710 Now what I'm going to do is actually insert some data will come back to reviews in a moment. 04:01.200 --> 04:07.560 So I have a bunch of series data here you can of course copy and pasted from the Including the included 04:07.560 --> 04:13.370 code which is what I recommend you doing because this took forever to type although it's not as bad. 04:13.380 --> 04:16.170 Give you a preview of what our reviews data looks like. 04:16.170 --> 04:23.070 That was a pain to type so this isn't as bad but we've got shows most of them are shows that I picked 04:23.070 --> 04:26.640 because I like but I also picked some because I didn't like them. 04:26.710 --> 04:32.160 So we have some variation in the ratings in particular I want to make sure that our data was realistic 04:32.400 --> 04:34.470 and not everything was a 9.0. 04:34.830 --> 04:41.640 So I'm just going to copy this over put it in this file so we have a record of it and I'm going to insert 04:41.640 --> 04:44.470 it and make sure it works OK. 04:44.550 --> 04:50.850 So that was 14 series who just inserted and I'll do the same thing for reviewers and they have seven 04:50.850 --> 04:51.490 here. 04:51.600 --> 04:53.100 But that's plenty. 04:56.560 --> 04:58.580 And we'll paste it in. 04:58.600 --> 04:59.940 Now let's make sure it works. 05:00.040 --> 05:03.280 Let's do a select star from series to start. 05:03.710 --> 05:05.780 You can see that make this a bit bigger. 05:06.010 --> 05:09.170 Here's all of our series Archer Arrested Development. 05:09.160 --> 05:11.670 Bob's Burgers all the way down. 05:11.710 --> 05:14.680 We've got animation comedy and dramas. 05:15.100 --> 05:21.520 And then let's do a select star from reviewers and we've got seven of them. 05:21.700 --> 05:27.310 Totally random names except I did put myself in there purely so that I could review my own movie my 05:27.310 --> 05:32.590 own shows that I'd like everyone else has fictional just totally made up reviews but the ones that I 05:32.590 --> 05:37.170 put in there are relatively accurate to my personal TV tastes. 05:37.540 --> 05:38.170 OK. 05:38.710 --> 05:44.680 So then that leaves us with having to now implement the reviews table which we left for last because 05:45.340 --> 05:47.700 for one thing it depends on these two tables. 05:47.890 --> 05:50.480 And it's also a little bit more complicated. 05:50.560 --> 05:52.840 So let's get started. 05:52.840 --> 05:59.310 Scroll back up what I like to do all of our kind of schema code up top to create table. 05:59.770 --> 06:04.430 And this is revues and the first part's easy and easy enough. 06:04.550 --> 06:13.420 We'll have an ID which is an auto increment primary key and then the next part's pretty simple to which 06:13.420 --> 06:15.670 is rating. 06:15.920 --> 06:18.250 Now what data type Do we want rating to be. 06:18.460 --> 06:23.540 If we look at the examples I've given you we have eight point nine nine point five. 06:23.740 --> 06:27.010 So we could have just done an integer that would work. 06:27.760 --> 06:32.000 But that's not the way that we were going to go here because we want decimals after it. 06:32.080 --> 06:35.350 So we could have just you know only allowed whole numbers from 1 to 10. 06:35.550 --> 06:42.670 But instead we're going to do sort of like I am D-B does where they have a decimal system and our maximum 06:42.670 --> 06:44.440 rating will be nine point nine. 06:44.470 --> 06:46.220 So zero to nine point nine. 06:46.320 --> 06:48.390 So that's going to be a decimal. 06:49.090 --> 06:54.520 And if you think back to the section on data types there are two things you pass into the decimal. 06:54.520 --> 07:00.940 The first is the total number of digits we want allowed which is just too because we only want things 07:00.940 --> 07:03.150 like four point four. 07:03.250 --> 07:05.380 We don't want forty three point four. 07:05.440 --> 07:07.450 We don't want nine nine point nine. 07:08.080 --> 07:12.730 And then the second is how many things or how many numbers after the decimal place. 07:12.730 --> 07:13.690 We just want one. 07:13.820 --> 07:22.200 So that will give us two total numbers like 9.9 or 5.6 something like that. 07:22.520 --> 07:23.910 So that's all we need there. 07:24.460 --> 07:32.610 Now we get to go back series ID and reviewer ID so both of the both of those will just be integers. 07:32.650 --> 07:37.990 So we'll just start with that series ideas an ENT and reviewer. 07:37.990 --> 07:44.230 ID is an ENT as well but of course we're not done because now we need to explicitly say that they are 07:44.230 --> 07:45.660 foreign keys. 07:45.670 --> 07:51.970 Remember from the last section you don't have to do that but it's a really smart thing to do to enforce 07:52.270 --> 07:54.390 that when you insert a review. 07:54.550 --> 07:58.840 It actually corresponds to a valid series and valid review. 07:59.020 --> 08:00.670 So the syntax for this. 08:00.960 --> 08:03.970 It's been a little bit but it's foreign key. 08:03.970 --> 08:07.540 And then we tell it will work with series ID first. 08:07.690 --> 08:14.860 So we're saying okay this field series ID is a foreign key references and what is it referencing the 08:14.860 --> 08:19.880 series table and what field the ID field. 08:20.020 --> 08:23.640 So just like that and then we'll do the same thing. 08:24.300 --> 08:33.340 But this time we're saying this reviewer ID field right here is a foreign key references the reviewer 08:33.820 --> 08:39.530 table peer the ID field there. 08:39.760 --> 08:41.770 Oops there we go. 08:42.060 --> 08:49.100 So make sure we have commas in the right places and our semi-colon at the end and then see if it works. 08:53.350 --> 08:54.870 So there is a mistake in this code. 08:54.880 --> 08:59.830 I've already gone back and edited this but I kept this in here because it's a mistake that I think is 08:59.830 --> 09:02.610 pretty common and it's a little tough to spot. 09:02.860 --> 09:06.820 If you look at maybe you already spotted it but if you look at the result of the error I get it says 09:06.820 --> 09:08.080 can't create table. 09:08.170 --> 09:11.640 It's really not very useful but what's happening here. 09:11.830 --> 09:18.510 Typically if we get an error like that has something to do with foreign keys here and the main thing. 09:18.730 --> 09:21.430 Remember that these are referencing external tables. 09:21.460 --> 09:26.940 So we have a series table but we don't have a reviewer singular table. 09:26.990 --> 09:28.780 It's reviewers. 09:28.780 --> 09:34.450 So I end up doing that quite often I see some of my students do that often as well because sometimes 09:34.450 --> 09:40.480 you just talk about the things and singular We have review were ID versus review ID here it's very easy 09:40.480 --> 09:41.710 to mess that up. 09:41.710 --> 09:46.760 So now hopefully should work just fine. 09:47.070 --> 09:48.290 And it appears to. 09:48.680 --> 09:49.180 OK. 09:49.300 --> 09:51.410 So we still don't have any data in there. 09:51.430 --> 09:54.950 The last thing that we'll do is copy and paste this monster. 09:55.190 --> 09:56.560 All these are views. 09:56.560 --> 10:01.550 This is like an hour to try and get this with some realistic data. 10:01.660 --> 10:07.450 So I want to add numbers that accurately relatively accurately reflected the quality of shows. 10:07.450 --> 10:14.740 Now you may disagree with it but copy this in definitely don't waste time typing this yourself. 10:14.740 --> 10:21.730 Copy it from the code that I've provided and paste it. 10:21.760 --> 10:23.660 So now we should be good to go. 10:24.100 --> 10:27.090 I'm just going to add this in here so we have a record of it. 10:29.290 --> 10:31.780 And just to double check that everything worked. 10:31.840 --> 10:36.690 We're just going to select start from reviews and we'll leave it at that. 10:36.700 --> 10:38.550 So as long as we see our data in here. 10:38.800 --> 10:43.490 Notice how difficult it is to understand what does any of this really mean. 10:43.720 --> 10:49.030 At the moment nothing unless you happen to know you have memorized the different IDs for series and 10:49.030 --> 10:50.170 for reviewers. 10:50.500 --> 10:55.050 So that was preparing our data creating our schema and inserting our sample data. 10:55.390 --> 10:57.400 Next up we're going to start working with it.