Now, we're going to start with setting up
the database and the tables.
And here I'm back on my Mac
which is why this looks slightly different,
but I am in MySQL workbench here in the end.
And there, we still can go to the schemas tab
to start creating databases and then tables there.
Of course, you might still have the databases and tables
from the previous course section and you can keep them,
they won't interfere here,
but you can also of course delete them if you want to
simply with help off this MySQL workbench
by right clicking on the tables and databases.
Now, once you're ready,
we can go to schemas here and create a new schema
by simply right clicking into this field
or using this button here,
this create a new schema button
and the schema is simply just a database.
And here we wanna choose a name
and I think blog is a quite fitting name
because we are creating a blog database here.
Therefore I'm going to name this blog and apply this,
we then see the command here
that will be executed to create this schema,
this database,
and hence I'll click apply and apply does as well,
now here's my blog database.
Now we have no tables in there yet
and therefore by right clicking on tables
we can create a new table
or directly run the SQL command
for doing so if you know that
but here, I'm going to create a new table,
and simply name that first table authors
because I'll create that table first,
and then we wanna define the columns
that we wanna have there.
And here, I of course will implement the columns
I showed you on the slide,
which means I want to have an ID column
which is of type end,
which is a primary key,
So this field here should be checked,
it should not be null,
and I'll also check this auto increment field here
so that the ideas automatically incremented
and automatically set whenever we add a new entry
and we don't have to manually manage this ID.
Then every author should have a name,
so I'll add a name column
and this should be of type Varchar
I'll just set the maximum length to 255 characters.
And this should also not be null so I'll check this as well.
And then last but not least,
I wanna have an email that also will be a Varchar
of length 255,
like this,
and it should also not be null.
And that is all I want to set here.
So with that, if I click apply,
here's the SQL statement that will be executed
for creating table called authors in the blog database
with these fields and that configuration here
and if I now click apply that's created.
So now we have that first table here that authors table,
now, of course we're going to create a second table
and that's our posts table.
And then here again, we have an ID,
primary key not null
and auto incrementing should be checked.
And then we have all the fields I showed you on the slide,
like for example, a title which is a Varchar of length 255,
again not null,
so it's not optional a title has to be set.
I also want to have add summary field which is also a text,
but a relatively short text,
so I'll also use Varchar 255 here,
and also set this to not null
because it's also not optional.
And then I'll have that body field,
which is the full blog post text,
and therefore here, I'll not use Varchar
instead here, I'll just use texts like this,
and also check not null here,
and to text us that special data type
which also accepts longer texts.
Then as a next step, of course
I also wanna have a date,
and here the value type I'll choose is date time
because I wanna store well the combination
of a date and the time
and here, I'll then go to default expression
and set this to current_timestamp.
Written like this current_timestamp
so that by default this built in command is executed
and the current timestamp is inferred
and stored for the date
so that we don't have to set the date manually,
but it will be set for us whenever we add new data
into that posts table.
And last but not least,
of course, we wanna establish a connection
to the authors table
hence I'll add a last field called author_ID,
following that naming convention
I outlined in the last course section,
and this will be off type INT
so to same type as we have for the primary key
in the authors table
and it should not be null
so I'll check this
because the author should not be optional
every post should have an author.
And with that,
we implemented what we discussed on this slide before
with the value types discussed here,
and hence, we can now click apply here as well,
here's the SQL statement that will be executed
for creating that posts table
with all the fields be discussed and set up,
and if you now click apply here,
that was created as well.
And with that,
we of course completed this second step at the bottom here,
we created the database and the tables in it.
The next step now is to add some initial data
that we might want.
And indeed there is some initial data I wanna set up
because the blog we're going to build,
of course, it's a fairly basic blog
and whilst it will allow us to manage all the blog posts
with all the crud operations,
it will not have any functionality
for adding or managing offers.
That is something you could build or add as a bonus task
but it's not something we will build here.
Instead, it is also fair to assume
that something like a list of offers
might be managed by some global administrator,
maybe directly through a tool like MySQL workbench.
And therefore what I'll do here is
I will add some starting offers through MySQL workbench,
which we can then utilize later
in our node JS driven website.
And hence I'll click this button here
to create a new SQL statement.
And then the SQL statement I wanna execute here
is insert into,
to insert a new item
a new record into a table,
and I wanna insert into the authors table,
now you can write it like this,
if you later do get an error
about the database not being found though,
or no database being selected,
you can also be very clear
about which database holds this table
by writing blog.authors,
so here we say, it's the authors table in the blog database
and we wanna insert something into it.
Now with insert into, we then have to define the columns
into which we wanna insert data,
and here for me,
that of course would be the name and the email column,
because in authors,
we have the name and email columns which we wanna to set.
And then we can set the values in the same line
or in a new line that doesn't matter,
and here for my name, I'll add a string
so I'll use single quotes here
and choose Maximilian Schwarzmuller here,
and then for the email, maybe max@test whoops,
at test.com,
which is of course, just some dummy email.
And with that, if I now execute this query,
this worked and this was inserted,
and now I'll add a second author Manual Lorenz
and simply use manual@test.com
and also execute this statement,
and with that, these authors should have been added
as some initial data in that author's table,
and we can verify this by selecting from authors
or from blog.authors in case you're getting errors,
and here, if I run this indeed in my results
that I see the two authors with their email addresses
and the automatically assigned IDs.
So now we have that initial data here as well.
And with that, we completed that third step as well.
And therefore, now we basically practiced again
what we learned in the last course section already,
now we're going to move on to the fourth
and very important step for this course section,
where we are going to see
how we can now write node JS express code
that will actually then connect to our database
and query that database from inside node JS.