Part 2

Design the database with an ERD, then build the users, loans, copies and books tables in HeidiSQL.

#digital technology #gce #parkwood vale #wjec

← Previous part Next part →

We plan the library's relational database with an entity relationship diagram (ERD), then create it in HeidiSQL with its keys and a first admin user. In this part: 0:13 Why the transactional website needs a relational database 0:42 Drawing the ERD in Visual Paradigm, with primary and foreign keys 2:44 Starting UwAmp and opening phpMyAdmin and HeidiSQL 3:53 Creating the Parkwood Vale database 4:57 The users table, including isAdmin and loanTokens 9:04 The loans table, linked to users by email 12:36 The copies table, and fixing a foreign key error 14:19 The books table 16:58 Adding an admin user and fixing a postcode that was too long 18:46 Checking the tables in phpMyAdmin Software: Visual Paradigm, UwAmp, HeidiSQL, phpMyAdmin, MySQL.

Transcript

In the previous video, we looked at setting up the environment ready to create our
transactional website. Now it's time to look at setting up the relational database
that will run the backend of our transactional website.
We're going to start off by looking at creating an ERD, an entity relationship
diagram, before moving on to actually following that diagram to create our table
within our database using HeidiSQL.
So let's get started.
Okay, so I'm back on my student workstation and I've got online visual paradigm
open. It's a brilliant...
I'm back on my student workstation and I've got online visual paradigm open.
This is a brilliant online ERD creator.
And what I'm going to do is I'm going to start off by showing you a few different
things within here.
Starting off, I have my database laid out just like it would be in the exemplar for
Parkwood Vale, available from the WJEC.
And I start in my books table here, then I've got ISBN and I need to set that to be
the primary key. So I've got to first click to highlight my column.
I'm going to then right click and just create primary key.
Down here we already have our ISBN set as a foreign key.
You can tell it's forward slash, it's slanted.
And I'm just going to click to drag and create a relationship between these two.
The relationship between books and if I go out a second, the copies table is going
to be a one to many. One book can have many copies.
In the same way, I've got one user can have many loans.
The loans to copies table join is going to be a
simple one to one. And that's created my
ERD for me in place. Each of these different elements then I just
simply added by right-clicking and added a column in place and over on the side here
you've got your different styles of templates.
With that then you can either print screen it, you can use a snipping tool, you can
go to file where you can save it or you can go into save as.
This will allow you to download it to your computer, set it as your device and it's
going to ask you how you want to save it to your device.
REID is going to be important because that's going to be the blueprint to our
I'm gonna reinsert the USB so that is just here let's put that back into our
computer and on my device I have
UwAmp I'm going to run UwAmp and get that set
up I
also have that portable HeidiSQL we talked about last time as well I've got these
elements in place I'm not going to be changing any of the settings pre built in to
UwAmp. I'm quite happy with PHP 7, quite happy with MySQL 5.7.
There's no need for any changes there.
I could click onto phpMyAdmin and that'd bring me through to my database editor.
The username and password are both root and that'll allow me access to my database
element here.
Now, personally, I'm not a fan of working with databases directly in phpMyAdmin.
I find it cumbersome. I find it complicated.
I find it doesn't lend itself nicely to production, which is why I said in video
one to install HeidiSQL or get the portable version.
If you want to use
phpMyAdmin, by all means do. I'm going to open up HeidiSQL here though.
I've got my connection to localhost under root and root.
I am going to change the name here.
Just right click and go to rename and call this Parkwood Vale to
make it easy to remember what's going on.
Now when I open this up it's going to be a bit of a nicer interface and it's going
to allow me to do just what I want to do to create my database like I've set out
here.
First thing I want to do is right click and I want to create a new database and it's
going to be called Parkwood Vale.
And click, okay. So there it has created my Parkwood Vale database for me, which
currently has no table sat inside of it.
The way that this works is it connects to my database and provides the SQL
statements for me to create the different elements.
And as you can see here, if I expand this, we've got our create
then show all databases. So everything I am doing, you can see the SQL for it just
underneath if you wanted to see what's going on in depth.
From there, I'm gonna right click, create a new table.
And this is where my visual ERD comes into play because I'm gonna follow what I've
set up here. So the name of my table is gonna be users.
I'm gonna then go through starting off, adding my column.
So I'm gonna add a column. instead of column one I'm going to call it email and
instead of having a data type of the integer I'm going to choose a much more
appropriate data type as a var char.
Now it's currently set to 50 just to make sure if there's no truncation or anything
like that I'm going to set it to 255.
I'm going to leave the rest of the settings as they are.
I'm telling it there is no default element, I'm going to untick actually to say, no,
we can't have null values because this is going to be our primary key.
We can't have a null value primary key.
Next up then, I'm going to add in my password and
simply again, it presets remembering my previous values of Varchar and
255.
Realistically, we don't want a null value for a password either.
So I'm going to leave that unticked and there are
Next up then I'm gonna have forename and then I'm gonna go surname and it's just as
simple as filling in this table.
All of my settings are already put into 255s the only thing that changes is when I
get to postcodes so I can just carry on working through.
In here then I've got street name and town
and then I'm gonna go into postcode.
Now depending how you are setting your database out you could
and link between the two.
For what I created, I don't think there was a need for it, so I didn't include a
separate table. Each person is going to have one address.
We're not doing an ordering system.
We're not doing an Amazon-style system where they could have possible addresses.
They have one address, which is where they are registered to.
So next up then is admin.
Bit of a strange one, but this is going to be our differentiator.
This is going to tell us if the user logged on should have admin rights.
I'm going to change that from Varchar, just scrolling through all of these options
here, to standard integers. It gives me a readout telling me what the standard
integer would be, and that only needs one byte of space, so I can just put one in
the box there.
I'm going to add a second one then, which has appeared just at the top.
I'm just going to move that downwards, scroll down to get to it, and change it to
loan tokens.
this means that in this loan token section which again should be an integer so let's
just set that to integer and i've got it preset as one they are going to be in place
to say how many books someone can take out at once now when we are
looking at our allow nulls realistically we don't need any information to contain
nulls so it's okay to leave them unticked our default values zero for is
admin
Let's get off. No, they're not admins.
Default loan tokens. I can preset that custom text.
I'm going to say three and click on OK.
Now let's put it in as three as in the text and that should fill that in quite
nicely for me. If I click save, what that does is it creates my table, but I haven't
gone through and set my primary key just yet.
We expand that down and close this off a little bit so we can see more of this
section and we can see all of our details.
In our indexes option, I can choose add a primary key.
Index 1, change the type. Isn't just a key, it is a primary key.
And then I can drag email into place and click save.
And that has created my first table.
I'm going to repeat the process for loans and on with books and
copies as well. It really doesn't matter which order you create your tables in, but
I am going to go around in a U shape.
So create new table. Give the table a name.
This is loans.
And add in the columns. So I'm going to put in again email as
Varchar.
And set it to 255. Now this has to mirror the same thing in
users. because when we create the relationship between the two of them, if they
aren't mirrored, then it won't be happy with how we've set that up.
So allow null, no, no default, perfectly fine.
I'm going to add in another column there.
It's going to be library ID.
I'm going to drop the underscore just to uniform it a little bit more because
nothing else had underscores. So everything is just camel cased instead.
So in here, library ID, again, varchar, this time around, I'm presetting it to 18
bits.
Next up is loan date. So I'm going to add that in.
And this is going to use the pre-built date and time function.
So there it is, date and time.
Date and time will contain, as it states, both the time format and the date format.
Realistically, I could probably get away with just date, given the system I'm using,
but I'm going to put it as date and time.
Same goes then for due date and the same goes
for return date.
Now due date and return date are there.
The loan date will be the date they take the book out.
The due date will be when they are supposed to return it and the return date will be
when they actually returned it.
So you could build in some form of analytics where you look at how many people late
returned a book. The last one is the return flag where you can see whether a loan
has been returned or not. So I can say return and that is going to be
set to an integer.
So let's scroll to the integer section and there we go.
Have that in place. Again, character set, length set, one is perfectly fine.
I'm going to click save on there for a second and that's all set up.
Now what I can do on this point is set up my foreign keys.
The way that I set up my foreign key is I click onto the foreign keys option within
my table. And then I click onto add new key.
I've got FK1 with my key name.
And I'm going to choose the email as my starting column.
The table it's going to be linked to is going to be my users table.
And the key there is also called email.
Now, with those in place, then I can click on save and that will save that foreign
key, FK loans link for me and my two tables are in place.
Next up is to create my next table, which is my loans table.
Let me just move this back over to the side here and make this a bit smaller so I
can see the two at once.
And actually, can I snap that to one side and that to the other side and get rid of
that pain?
Sadly not, but I can still see that enough to be able to create what I need to.
So I'm going to create a new table and that table is going to be called loans.
No, sorry, I've already done the loans table.
Let me move over and I'm going to do the copies table next.
And in my copies table, I'm going to have my ISBN, which is a VAR char
and it is 14 in character length.
That is the one foreign key in here.
My library ID would also be a foreign key.
So let me just mark that as a foreign key there.
And I'm going to add in library ID.
It is a Varchar this time around of 18 key loans.
Neither are going to be allowed nulls.
So I'm going to save those. I want to put in my link between my loans and my
copies. So I'm going to go back to my foreign key section.
from my library ID.
That is going to reference my loans table and reference my library ID.
Now, when I click on save, I get a warning.
It's telling me that it cannot add that foreign key constraint.
I need to go into the options section of my database and change the database
engine.
InnoDB isn't very friendly when it comes to foreign keys.
If I change it to MyISAM, this one here, I can then save that
and it'll happily take my foreign key selection in place.
So if you do get that warning, it's probably because you're using the engine InnoDB.
It is the default engine. It's a bit of an outdated engine.
So if you're using your keys, make sure you change that over.
Now, I'm going to add in my final table, my create new table, and it
is going to be my books table.
And I'm going to add in the details for that.
So ISBN, and it is going to be voucher
14.
That is going to be my primary key.
I can't have a null ISBN. I can't have an empty ISBN.
There has to be a value for my database to function.
so I'm going to untick null and have no default.
That will form my primary key on this section here.
Now, when I am doing that, I am already going to swap it over to MyISAM because
that's what I want for my foreign key in a second to put that in place.
I'm going to add in my second column, my name column.
It isn't going to be an integer.
It's going to be a VAR chart and it's going to contain two 55 character bits.
Now it might seem strange, but I am going to leave allow null in place.
That will come clear as to why a little bit later on when we get further into these
series and we look at important information.
I'm going to put in publisher and I'm going to add in as well description.
Now as you can see, I've got a var char for my description, but here I have the
option of text. so let me drop that down swap that over to text that allows me to
have a bit more of a fluid
entry i'm going to have rating which just like i've stated there will be a type of
decimal
and i'm going to add in jacket image
and that should as well be a varchar back to that 255 point
i'm going to add in available copies
and set that to be its integer value,
and put a property in place.
Now, I'll save that, go into my foreign keys, and create the link between my two
tables. So this is going from ISBN to my reference table of copies,
to my ISBN.
Click on Save.
and I should now have in place my links between all my different tables.
So I have created my database that follows my design
parameters here.
I will do one last thing for this video, and that is go into my
users table, which is here, and go into the data section across
the top and add myself into it.
so first off i'm going to put in cpd at mrjjones
.co.uk password for now password one two three nice and
secure
now i do need to fill in these details so forename james surname
jones street i'm going to say police
T, Gwyn,
Town,
Ponte Preeze,
Postcode, CF465HotelXray,
is admin, I am admin, so that's a 1.
It's telling me my data is too long for postcode because I have a break in between
them. So let's just remove that break.
And then it's added me to that database.
Let me expand that out. Let's expand that out.
And you can see it's in place.
Now as I said all along, all the messages and prompts I've got as I've been creating
this have come down the bottom here so I can see the SQL that's included as I've
gone through. You can see the insert element here where my passcode is too long and
insert again below where is then the right length.
I can use the simple interface to add rows if I want to, ready for
my designing and testing of my database.
If I go into books, all the details are there.
Copies, loans, everything is in place.
And every time I click onto a different table, a new SQL command is
executed to display that information for me.
So that should allow you to set up the database that will be used in the back end of
your transactional website. I'm going to close down HeidiSQL.
And then I'm going to go back into UwAmp, which I did minimize, and open up
phpMyAdmin.
Log back in with root. Username and password are always root combined.
I've got in there parkwoodvale.
I've got my users.
And that's all set up. I've got my loans table and everything I've created using
HeidiSQL is also in place and checkable using that
my phpMyAdmin. Everything's in place and running.
I'm done for this session so I'm simply going to stop everything and
they're all stopped and I can close it all down.
hopefully that all made sense to you and we can start moving on to setting up our
dream environment and start building our website any problems just reach out and
give me a message thank you