Okey dokey.
Let's go ahead and write these three tables.
We'll start with reviewers and series and then we'll create the reviews table that has the series ID
and reviewer ID foreign keys.
So let's start with reviewers.
Nice and simple ID, first name and last name.
So create table reviewers.
And we're going to have an ID that will be an integer.
It should be a primary key and we want it to auto increment.
And then we have first name, which is I don't know.
Var cha 50 for each name.
I think that's probably a safe threshold.
50 characters and your first name.
Let's make it required.
And then last name also var cha 50.
Not?
No.
So that should be our reviewers table.
Next up, let's make the series table.
Series ID title released here genre.
So we'll do the same thing with ID.
We want a primary key.
So ID.
INT.
Primary key and auto underscore increment.
And then we want to have the series title.
And we'll make that a var cha 100 because titles can be pretty long.
And then we have released year and for this we could do an integer, but I'm going to do something a
little fancier.
We haven't talked about.
There is a data type called data type called year, and it will display and hold the value of a year
implicitly with a width of four characters.
So we don't have to do this, but it's an opportunity to show you this data type.
So let's say that release year is of type year, and then we have genre, which will also be a var char,
let's say 100 in case there's some long genres.
Then we have the reviews table.
So reviews has an ID, we'll start there, create table, put my E there, create table reviews and
we want an ID and primary key auto increment.
Probably tired of typing that by now and then.
The rating.
What data type should the rating be?
If a rating is going to look something like this right here, 8.99.5.
So probably a decimal afloat double.
I'm going to do a decimal and limit it to be two total characters, one of which is after the the decimal
point.
So rating will be a decimal two characters.
Here are two numbers, two digits, one of which can come after the decimal.
So we can have like 8.9 or 9.9, but we're not going to have 9.9877987 rather OC So that's rating.
Then we get the trickier pieces series ID and reviewer ID so we know that series ID, we'll start,
there is going to be an integer.
We also know that reviewer ID will be an integer because they are both going to reference in an integer
column, series ideas and int reviewer's ideas and int.
But then we want to add on the foreign key constraint and say, Oh yeah.
By the way, my SQL, this series ID is in fact a foreign key and it references the series table and
the ID column in that table.
And then after that we'll do the same thing for review or ID.
So foreign key.
Reviewer IDE references the reviewers table, the ID column, and then we have the choice whether to
add on the on delete cascade or not.
Meaning if a series is deleted, should its reviews be deleted, or if a reviewer deletes their account,
do the reviews associated with that account go away?
For now, we'll say no, because maybe we want to keep if somebody deletes their account, we might
want to keep the review and we'll just display it with no user name associated with it.
But that is a choice that we have.
All right.
So let's try running this.
See if I made any syntax errors.
It's a good place to start.
I'm going to make a new database.
I'll call this TV.
DB Use TVP.
DB I'll run my three queries that create my tables and let's do a show tables.
Looks good reviewers reviews and series.
And then the final thing I want us to do is all work with the same data.
So I'd like for us all to run this code that inserts into series like 15 or 20 different TV shows and
then this code that inserts ten or so reviewers, and then this code that inserts 100 ish, I think
it's less than a hundred, but a bunch of reviews where we have the series ID, the reviewer ID and
the rating.
So it's just giving us a starting point.
We're all going to be on the same page, so let's run these three queries.
The first one looks good.
Select star from series.
We now have 14 series in there.
Then let's run reviewers that we insert.
So let's do that.
Select star from reviewers.
And now we have seven reviewers.
And finally we create a bunch of reviews that are a series ID, a reviewer ID and a rating.
Okay, let's try 47.
I don't know where I got 100 from.
I was, I guess, high out of my mind.
Not actually select star from reviews.
We have 47 reviews, and when we look at this reviews table on its own, it's pretty meaningless unless
you happen to know what series has the idea of nine and what reviewer has the ID of five.
You're not going to know who left this review and what show it corresponds to, but that's the whole
point of this section.
We're going to get practice using some joints, but we have the data.
That's all that matters.
We have a starting point, we have our three tables, we have our foreign key constraints, and now
all we have left to do is, is try to do some stuff with these tables.
So that's what's coming up next.
We're going to work on a series of little challenges together.