All right.
So let's hop over to cloud 9 and we'll start simple.
Let's work with the reviewers tables.
The simplest 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 on one called something ridiculous and stupid that I probably will regret.
TV review app.
Not too bad.
So we're going to start off by creating a table which you're going to call reviewers work with.
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 and first name and last name and we know ID needs
to be an ant that should be familiar by now.
Autoincrement primary key first name will just be char 100 and same thing for last name.
So pretty straightforward table to start with.
For reviewers the next thing that will move on to before we insert things that actually get our tables
going.
So all tables first and then insert data.
So next up will do series ID.
Title released here and genre.
So create table series and inside here will have an ID which will be exactly the same.
Autoincrement primary key and primary keys are especially important because we're going to use them
as foreign keys in the reviews table and we need to have valid rows that were pointing back to.
We need to make sure whatever the series idea 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.
OK.
So the next thing is the title of the series which we'll just make of our 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 you know and so on which we don't care about.
So we could make it an aunt and that would work but then potentially someone could accidentally insert
something with a release year of like 10000 or 9 9 9 9 9 9 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's just called year and year for 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 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 of our char as
well.
OK.
So we have those two tables.
Now let's actually start with those and let's make sure that they work.
Those are first one.
And here's our second series.
Great.
Now what I'm going to do is actually insert some data will come back to reviews in a moment.
So I have a bunch of series data here you can of course copy and pasted 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.
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 picked some because I didn't like them.
So we have some variation in the ratings in particular I want 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 OK.
So that was 14 series who just inserted and I'll do the same thing for reviewers and they have seven
here.
But that's plenty.
And 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 that 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 movie my
own shows that I'd 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.
OK.
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 what I like to do all of our kind of schema code up top to create table.
And this is revues and the first part's easy and easy enough.
We'll have an ID which is an auto increment primary key and then the next part's pretty simple to 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 eight point nine nine point five.
So we could have just done an integer that would work.
But that's not the way that we were going to go here because we want decimals after it.
So we could have just you know only allowed whole numbers from 1 to 10.
But instead we're going to do sort of like I am D-B does where they have a decimal system and our maximum
rating will be nine point nine.
So zero to nine point nine.
So that's going to be a decimal.
And if you think back to the section on data types there are two things you pass into the decimal.
The first is the total number of digits we want allowed which is just too because we only want things
like four point four.
We don't want forty three point four.
We don't want nine nine point nine.
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 something like that.
So that's all we need there.
Now we get to go back series ID and reviewer ID so both of the both of those will just be integers.
So we'll just start with that series ideas an ENT and reviewer.
ID is an ENT 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 will work with 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 reviewer ID field right here is a foreign key references the reviewer
table peer the ID field there.
Oops there we go.
So make sure we have commas in the right places and our semi-colon at the end and then 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 of 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 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 the things and singular We have review were ID versus review ID here it's very easy
to mess that up.
So now hopefully should work just fine.
And it appears to.
OK.
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 are views.
This is like an hour to try and get this with some realistic data.
So I want to add 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've provided and paste it.
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 start 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.