1
00:00:00,090 --> 00:00:03,750
All right, so let's hop over to cloud nine and we'll start simple.

2
00:00:03,750 --> 00:00:05,730
Let's work with the reviewers table.

3
00:00:05,730 --> 00:00:08,760
It's as simple as one, so we're going to implement that first.

4
00:00:09,180 --> 00:00:10,380
So we'll go over here.

5
00:00:10,590 --> 00:00:15,810
And just to be totally transparent, I created a new database just so we have a fresh place to work.

6
00:00:15,810 --> 00:00:22,230
You don't have to do that as long as you don't already have tables named series reviewers and reviews.

7
00:00:22,320 --> 00:00:24,660
If you do, you may want to make a new database.

8
00:00:25,050 --> 00:00:31,260
So I'm working in one called Something Ridiculous and Stupid that I probably will regret.

9
00:00:33,420 --> 00:00:34,890
TV review app.

10
00:00:35,010 --> 00:00:36,030
Yeah, not too bad.

11
00:00:36,480 --> 00:00:41,670
So we're going to start off by creating a table which we're going to call reviewers.

12
00:00:43,880 --> 00:00:45,320
I swear to God, Blue.

13
00:00:48,490 --> 00:00:49,000
Stop.

14
00:00:51,930 --> 00:00:52,470
Contact.

15
00:00:54,590 --> 00:00:54,980
How?

16
00:01:00,150 --> 00:01:05,190
So we'll start off by creating our table, which we'll call reviewers.

17
00:01:06,720 --> 00:01:12,000
And reviewers only has three things ID first name and last name.

18
00:01:13,590 --> 00:01:21,330
So we'll start off with ID then first name and last name, and we know ID needs to be an INT.

19
00:01:21,360 --> 00:01:22,920
That should be familiar by now.

20
00:01:23,010 --> 00:01:24,150
Auto increment.

21
00:01:24,150 --> 00:01:25,260
Primary key.

22
00:01:25,950 --> 00:01:28,650
First name will just be var char 100.

23
00:01:29,310 --> 00:01:30,930
And same thing for last name.

24
00:01:31,680 --> 00:01:35,820
So pretty straightforward table to start with for reviewers.

25
00:01:36,450 --> 00:01:41,400
The next thing that we'll move on to before we insert things, let's actually get our tables going.

26
00:01:41,520 --> 00:01:44,250
So all tables first and then we'll insert our data.

27
00:01:44,820 --> 00:01:48,450
So next up will do series ID.

28
00:01:48,500 --> 00:01:50,610
Title release year and genre.

29
00:01:51,420 --> 00:01:54,900
So create table series.

30
00:01:56,440 --> 00:02:02,740
And inside here we'll have an ID, which will be exactly the same auto increment primary key.

31
00:02:04,370 --> 00:02:09,699
And these primary keys are especially important because we're going to use them as foreign keys in the

32
00:02:09,699 --> 00:02:13,540
review table, and we need to have valid rows that we're pointing back to.

33
00:02:13,570 --> 00:02:18,700
So we need to make sure whatever the series ID is here, that it's actually a series in our database.

34
00:02:18,700 --> 00:02:23,500
It's not pointing to some non existent show or non existent reviewer in the case of reviewer ID.

35
00:02:24,040 --> 00:02:24,410
Okay.

36
00:02:24,670 --> 00:02:28,990
So the next thing is the title of the series, which we'll just make a var char.

37
00:02:33,050 --> 00:02:39,410
And then once we do that, we'll move on to release date or release year in our case.

38
00:02:39,740 --> 00:02:43,760
And we have a couple of options for the data type here, so we could make it a date.

39
00:02:44,120 --> 00:02:45,890
But all we want is the year.

40
00:02:45,890 --> 00:02:53,510
And a date has this kind of format where we have the year and then the months and then and so on, which

41
00:02:53,510 --> 00:02:54,590
we don't care about.

42
00:02:54,710 --> 00:02:56,540
So we could make it an int.

43
00:02:58,650 --> 00:02:59,850
And that would work.

44
00:02:59,850 --> 00:03:05,820
But then potentially someone could accidentally insert something with a release here of like 10,000

45
00:03:05,940 --> 00:03:09,880
or 9999999, which is not a valid year.

46
00:03:09,900 --> 00:03:13,800
So there's actually another data type that we can work with which I haven't shown you.

47
00:03:14,340 --> 00:03:20,790
It just called year and if we do year four, it will be a four digit year like 1999 or 2004.

48
00:03:20,820 --> 00:03:25,040
There used to be a year to type, but I believe it's been deprecated now.

49
00:03:25,050 --> 00:03:26,850
So we'll work with the year four.

50
00:03:26,970 --> 00:03:29,620
That's not the point of this lecture, so don't let that hang you up.

51
00:03:29,640 --> 00:03:32,910
I just want to show you that it exists so we have that.

52
00:03:32,910 --> 00:03:38,610
And then the next thing that we have is genre, and genre will just be a bar chart as well.

53
00:03:40,650 --> 00:03:42,540
So we have those two tables.

54
00:03:42,750 --> 00:03:46,950
Now let's actually start with those and let's make sure that they work.

55
00:03:47,970 --> 00:03:52,560
So there's our first one and here's our second series.

56
00:03:53,100 --> 00:03:53,760
Great.

57
00:03:54,240 --> 00:03:56,900
Now, what I'm going to do is actually insert some data.

58
00:03:56,910 --> 00:03:58,650
We'll come back to reviews in a moment.

59
00:04:01,060 --> 00:04:03,730
So I have a bunch of series data here.

60
00:04:04,000 --> 00:04:09,310
You can, of course, copy and paste it from the including the included code, which is what I recommend

61
00:04:09,310 --> 00:04:13,290
you doing because this took forever to type, although it's not as bad.

62
00:04:13,320 --> 00:04:16,130
I'll give you a preview of what our reviews data looks like.

63
00:04:16,149 --> 00:04:20,589
That was a pain to type, so this isn't as bad, but we've got shows.

64
00:04:21,399 --> 00:04:26,590
Most of them are shows that I picked because I like, but I also pick some because I didn't like them.

65
00:04:26,710 --> 00:04:27,970
So that we have some variation.

66
00:04:27,970 --> 00:04:33,310
And in the ratings in particular, I wanted to make sure that our data was realistic and not everything

67
00:04:33,310 --> 00:04:34,390
was a 9.0.

68
00:04:34,660 --> 00:04:41,320
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

69
00:04:41,320 --> 00:04:43,150
insert it and make sure it works.

70
00:04:43,390 --> 00:04:43,990
Okay.

71
00:04:44,380 --> 00:04:46,210
So that was 14 series.

72
00:04:46,210 --> 00:04:49,600
We just insert it and I'll do the same thing for reviewers.

73
00:04:50,080 --> 00:04:52,660
We only have seven here, but that's plenty.

74
00:04:56,580 --> 00:04:57,720
Then we'll paste it in.

75
00:04:58,530 --> 00:04:59,960
Now let's make sure it works.

76
00:04:59,970 --> 00:05:03,030
Let's do a select star from series to start.

77
00:05:03,690 --> 00:05:05,760
You can see if I make this a bit bigger.

78
00:05:05,880 --> 00:05:11,220
Here's all of our series, Archer Arrested Development, Bob's Burgers all the way down.

79
00:05:11,610 --> 00:05:20,430
We've got animation, comedy and dramas, and then let's do a select star from reviewers, and we've

80
00:05:20,430 --> 00:05:26,220
got seven of them, totally random names, except I did put myself in there purely so that I could review

81
00:05:26,220 --> 00:05:31,650
my own my own shows that I like everyone else has fictional just totally made up reviews.

82
00:05:31,650 --> 00:05:36,840
But the ones that I put in there are relatively accurate to my personal TV tastes.

83
00:05:38,580 --> 00:05:44,940
So then that leaves us with having to now implement the reviews table, which we left for last, because

84
00:05:45,180 --> 00:05:50,220
for one thing, it depends on these two tables and it's also a little bit more complicated.

85
00:05:50,370 --> 00:05:53,610
So let's get started, scroll back up.

86
00:05:53,640 --> 00:05:57,030
I like to do all of our kind of schema code up top.

87
00:05:57,300 --> 00:06:01,290
So we'll create table and this is reviews.

88
00:06:01,920 --> 00:06:04,530
And the first part's easy or easy enough.

89
00:06:04,540 --> 00:06:09,900
I have an ID, which is an int auto increment primary key.

90
00:06:11,430 --> 00:06:14,280
And then the next part is pretty simple too, which is rating.

91
00:06:15,930 --> 00:06:18,180
Now, what data type do we want rating to be?

92
00:06:18,390 --> 00:06:23,580
If we look at the examples I've given you, we have 8.99.5.

93
00:06:23,580 --> 00:06:26,880
So we could have just done an integer and that would work.

94
00:06:27,630 --> 00:06:31,980
But that's not the way that we're going to go here because we want decimals after it.

95
00:06:31,980 --> 00:06:38,010
So we could have just only allowed whole numbers from 1 to 10, but instead we're going to do sort of

96
00:06:38,010 --> 00:06:45,990
like IMDB does where they have a decimal system and our maximum rating will be 9.9, so 0 to 9.9.

97
00:06:46,170 --> 00:06:47,760
So that's going to be a decimal.

98
00:06:48,930 --> 00:06:54,450
And if you think back to the section on data types, there are two things we pass into the decimal.

99
00:06:54,480 --> 00:07:00,960
The first is the total number of digits we want allowed, which is just two because we only want things

100
00:07:00,960 --> 00:07:02,580
like 4.4.

101
00:07:03,120 --> 00:07:07,170
We don't want 43.4, we don't want 99.9.

102
00:07:07,950 --> 00:07:12,780
And then the second is how many things or how many numbers after the decimal place?

103
00:07:12,780 --> 00:07:13,680
We just want one.

104
00:07:13,680 --> 00:07:17,550
So that will give us two total numbers like 9.9.

105
00:07:19,270 --> 00:07:21,820
Or 5.6 or something like that.

106
00:07:22,510 --> 00:07:23,620
So that's all we need there.

107
00:07:24,310 --> 00:07:32,110
Now we get to if we go back series ID and review our ID, so both of the both of those will just be

108
00:07:32,110 --> 00:07:32,620
integers.

109
00:07:32,620 --> 00:07:39,570
So we'll just start with that series ID as an INT and reviewer ID is an NT as well.

110
00:07:39,580 --> 00:07:45,220
But of course, we're not done because now we need to explicitly say that they are foreign keys.

111
00:07:45,580 --> 00:07:51,250
Remember from the last section, you don't have to do that, but it's a really smart thing to do to

112
00:07:51,250 --> 00:07:52,720
enforce that.

113
00:07:52,720 --> 00:07:57,940
When you insert a review, it actually corresponds to a valid series and valid review.

114
00:07:58,930 --> 00:08:05,710
So the syntax for this, it's been a little bit, but it's foreign key and then we tell it, we'll work

115
00:08:05,710 --> 00:08:07,510
with a series ID first.

116
00:08:07,510 --> 00:08:14,560
So we're saying, okay, this field series ID is a foreign key references and what is it referencing

117
00:08:14,650 --> 00:08:17,290
the series table and what field?

118
00:08:18,340 --> 00:08:19,870
The ID field.

119
00:08:19,870 --> 00:08:23,590
So just like that and then we'll do the same thing.

120
00:08:24,280 --> 00:08:33,580
But this time we're saying this review or ID field right here is a foreign key references the reviewer

121
00:08:33,700 --> 00:08:35,919
table up here.

122
00:08:37,130 --> 00:08:38,929
The ID field there.

123
00:08:39,590 --> 00:08:40,090
Oops.

124
00:08:40,700 --> 00:08:41,419
There we go.

125
00:08:42,110 --> 00:08:44,240
Let's make sure we have commas in the right places.

126
00:08:45,260 --> 00:08:49,100
Add in our semicolon at the end and let's see if it works.

127
00:08:53,260 --> 00:08:54,880
So there is a mistake in this code.

128
00:08:54,910 --> 00:08:59,740
I've already gone back and edited this, but I kept this in here because it's a mistake that I think

129
00:08:59,740 --> 00:09:02,620
is pretty common and it's a little tough to spot.

130
00:09:02,680 --> 00:09:06,670
If you look at maybe you already spotted it, but if you look at the result or the error, I get it

131
00:09:06,670 --> 00:09:08,050
says can't create table.

132
00:09:08,080 --> 00:09:09,670
It's really not very useful.

133
00:09:10,180 --> 00:09:15,280
But what's happening here, typically, if we get an error like that, it has something to do with foreign

134
00:09:15,280 --> 00:09:16,150
keys here.

135
00:09:16,330 --> 00:09:21,400
And the main thing remember that these are referencing external tables.

136
00:09:21,400 --> 00:09:26,980
So we have a series table, but we don't have a reviewer singular table.

137
00:09:26,980 --> 00:09:28,210
It's reviewers.

138
00:09:28,630 --> 00:09:30,460
So I end up doing that quite often.

139
00:09:30,460 --> 00:09:36,180
I see some of my students do that often as well because sometimes you just talk about things and singular.

140
00:09:36,190 --> 00:09:39,880
We have reviewer ID versus reviewers ID here.

141
00:09:39,880 --> 00:09:41,350
It's very easy to mess that up.

142
00:09:41,530 --> 00:09:48,210
So now hopefully it should work just fine and it appears to.

143
00:09:48,750 --> 00:09:51,290
Okay, so we still don't have any data in there.

144
00:09:51,310 --> 00:09:56,500
The last thing that we'll do is copy and paste this monster, all these reviews.

145
00:09:56,500 --> 00:10:01,570
This was like an hour to try and get this with some realistic data.

146
00:10:01,570 --> 00:10:07,470
So I wanted numbers that accurately, relatively accurately reflected the quality of shows.

147
00:10:07,480 --> 00:10:11,920
Now, you may disagree with it, but copy this in.

148
00:10:11,920 --> 00:10:14,410
Definitely don't waste time typing this yourself.

149
00:10:14,710 --> 00:10:18,790
Copy it from the code that I provided and paste it.

150
00:10:19,850 --> 00:10:20,450
OC.

151
00:10:21,760 --> 00:10:23,530
So now we should be good to go.

152
00:10:23,950 --> 00:10:25,420
I'm just going to add this in here.

153
00:10:25,420 --> 00:10:26,620
So we have a record of it.

154
00:10:29,170 --> 00:10:35,050
And just to double check that everything worked, we're just going to select Star from reviews and we'll

155
00:10:35,050 --> 00:10:35,950
leave it at that.

156
00:10:36,580 --> 00:10:42,490
So as long as we see our data in here, notice how difficult it is to understand what does any of this

157
00:10:42,490 --> 00:10:43,300
really mean?

158
00:10:43,630 --> 00:10:49,060
At the moment, nothing unless you happen to know you have memorized the different IDs for series and

159
00:10:49,060 --> 00:10:50,050
for reviewers.

160
00:10:50,380 --> 00:10:54,880
So that was preparing our data, creating our schema and inserting our sample data.

161
00:10:55,420 --> 00:10:57,490
Next up, we're going to start working with it.