All right, so let's hop over to cloud nine and we'll start simple.
Let's work with the reviewers table.
It's as simple as one, so we're going to implement that first.
So we'll go over here.
And just to be totally transparent, I created a new database just so we have a fresh place to work.
You don't have to do that as long as you don't already have tables named series reviewers and reviews.
If you do, you may want to make a new database.
So I'm working in one called Something Ridiculous and Stupid that I probably will regret.
TV review app.
Yeah, not too bad.
So we're going to start off by creating a table which we're going to call reviewers.
I swear to God, Blue.
Stop.
Contact.
How?
So we'll start off by creating our table, which we'll call reviewers.
And reviewers only has three things ID first name and last name.
So we'll start off with ID then first name and last name, and we know ID needs to be an INT.
That should be familiar by now.
Auto increment.
Primary key.
First name will just be var char 100.
And same thing for last name.
So pretty straightforward table to start with for reviewers.
The next thing that we'll move on to before we insert things, let's actually get our tables going.
So all tables first and then we'll insert our data.
So next up will do series ID.
Title release year and genre.
So create table series.
And inside here we'll have an ID, which will be exactly the same auto increment primary key.
And these primary keys are especially important because we're going to use them as foreign keys in the
review table, and we need to have valid rows that we're pointing back to.
So we need to make sure whatever the series ID is here, that it's actually a series in our database.
It's not pointing to some non existent show or non existent reviewer in the case of reviewer ID.
Okay.
So the next thing is the title of the series, which we'll just make a var char.
And then once we do that, we'll move on to release date or release year in our case.
And we have a couple of options for the data type here, so we could make it a date.
But all we want is the year.
And a date has this kind of format where we have the year and then the months and then and so on, which
we don't care about.
So we could make it an int.
And that would work.
But then potentially someone could accidentally insert something with a release here of like 10,000
or 9999999, which is not a valid year.
So there's actually another data type that we can work with which I haven't shown you.
It just called year and if we do year four, it will be a four digit year like 1999 or 2004.
There used to be a year to type, but I believe it's been deprecated now.
So we'll work with the year four.
That's not the point of this lecture, so don't let that hang you up.
I just want to show you that it exists so we have that.
And then the next thing that we have is genre, and genre will just be a bar chart as well.
So we have those two tables.
Now let's actually start with those and let's make sure that they work.
So there's our first one and here's our second series.
Great.
Now, what I'm going to do is actually insert some data.
We'll come back to reviews in a moment.
So I have a bunch of series data here.
You can, of course, copy and paste it from the including the included code, which is what I recommend
you doing because this took forever to type, although it's not as bad.
I'll give you a preview of what our reviews data looks like.
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 because I like, but I also pick some because I didn't like them.
So that we have some variation.
And in the ratings in particular, I wanted to make sure that our data was realistic and not everything
was a 9.0.
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 it and make sure it works.
Okay.
So that was 14 series.
We just insert it and I'll do the same thing for reviewers.
We only have seven here, but that's plenty.
Then we'll paste it in.
Now let's make sure it works.
Let's do a select star from series to start.
You can see if I make this a bit bigger.
Here's all of our series, Archer Arrested Development, Bob's Burgers all the way down.
We've got animation, comedy and dramas, and then let's do a select star from reviewers, and we've
got seven of them, totally random names, except I did put myself in there purely so that I could review
my own my own shows that I like everyone else has fictional just totally made up reviews.
But the ones that I put in there are relatively accurate to my personal TV tastes.
So then that leaves us with having to now implement the reviews table, which we left for last, because
for one thing, it depends on these two tables and it's also a little bit more complicated.
So let's get started, scroll back up.
I like to do all of our kind of schema code up top.
So we'll create table and this is reviews.
And the first part's easy or easy enough.
I have an ID, which is an int auto increment primary key.
And then the next part is pretty simple too, which is rating.
Now, what data type do we want rating to be?
If we look at the examples I've given you, we have 8.99.5.
So we could have just done an integer and that would work.
But that's not the way that we're going to go here because we want decimals after it.
So we could have just only allowed whole numbers from 1 to 10, but instead we're going to do sort of
like IMDB does where they have a decimal system and our maximum rating will be 9.9, so 0 to 9.9.
So that's going to be a decimal.
And if you think back to the section on data types, there are two things we pass into the decimal.
The first is the total number of digits we want allowed, which is just two because we only want things
like 4.4.
We don't want 43.4, we don't want 99.9.
And then the second is how many things or how many numbers after the decimal place?
We just want one.
So that will give us two total numbers like 9.9.
Or 5.6 or something like that.
So that's all we need there.
Now we get to if we go back series ID and review our ID, so both of the both of those will just be
integers.
So we'll just start with that series ID as an INT and reviewer ID is an NT as well.
But of course, we're not done because now we need to explicitly say that they are foreign keys.
Remember from the last section, you don't have to do that, but it's a really smart thing to do to
enforce that.
When you insert a review, it actually corresponds to a valid series and valid review.
So the syntax for this, it's been a little bit, but it's foreign key and then we tell it, we'll work
with a series ID first.
So we're saying, okay, this field series ID is a foreign key references and what is it referencing
the series table and what field?
The ID field.
So just like that and then we'll do the same thing.
But this time we're saying this review or ID field right here is a foreign key references the reviewer
table up here.
The ID field there.
Oops.
There we go.
Let's make sure we have commas in the right places.
Add in our semicolon at the end and let's see if it works.
So there is a mistake in this code.
I've already gone back and edited this, but I kept this in here because it's a mistake that I think
is pretty common and it's a little tough to spot.
If you look at maybe you already spotted it, but if you look at the result or the error, I get it
says can't create table.
It's really not very useful.
But what's happening here, typically, if we get an error like that, it has something to do with foreign
keys here.
And the main thing remember that these are referencing external tables.
So we have a series table, but we don't have a reviewer singular table.
It's reviewers.
So I end up doing that quite often.
I see some of my students do that often as well because sometimes you just talk about things and singular.
We have reviewer ID versus reviewers ID here.
It's very easy to mess that up.
So now hopefully it should work just fine and it appears to.
Okay, so we still don't have any data in there.
The last thing that we'll do is copy and paste this monster, all these reviews.
This was like an hour to try and get this with some realistic data.
So I wanted numbers that accurately, relatively accurately reflected the quality of shows.
Now, you may disagree with it, but copy this in.
Definitely don't waste time typing this yourself.
Copy it from the code that I provided and paste it.
OC.
So now we should be good to go.
I'm just going to add this in here.
So we have a record of it.
And just to double check that everything worked, we're just going to select Star from reviews and we'll
leave it at that.
So as long as we see our data in here, notice how difficult it is to understand what does any of this
really mean?
At the moment, nothing unless you happen to know you have memorized the different IDs for series and
for reviewers.
So that was preparing our data, creating our schema and inserting our sample data.
Next up, we're going to start working with it.