You’ve got a database diagram and you want to
convert that to an actual SQL database you can use.
But how?
In this video I’ll explain how, by going step by step with an example diagram,
explaining everything along the way,
and ending up with tables created in an SQL database that you can actually use.
Here’s our sample database. It’s based on an electricity company,
which is a company that provides electricity to customers.
It has a few tables: a customer table, an account table, and a usage table.
You may have something similar or you may have a much larger database.
The concept of creating a database from this is the same,
so once you understand it you can continue with larger databases.
What do we do first?
We start by creating a table. Which table? Right now it doesn’t matter.
Pick whichever one you want. We will start with the customer table here.
Now go to your SQL editor. I’ll assume you already have a database installed,
by which I mean you have installed MySQL or Postgres or Oracle or SQL Server. If you haven’t,
then you can check out some of the other videos on my channel for how to install them.
It doesn’t matter which one you use for the purposes of this video.
I’ll demonstrate this in MySQL, but you can use whichever database you like.
You will also need an SQL IDE or SQL editor. In this video, I’ll be using MySQL Workbench,
a popular tool for MySQL databases. If you don’t have an SQL editor,
you can check out my channel where I have videos on how to set these up.
So, inside your SQL editor, you should see something like
this - a big section that allows you to enter SQL commands and run them.
This is what it looks like in MySQL Workbench, and other editors are similar.
In this section, we’ll enter a Create Table statement which is used to create a table.
We start by entering Create Table, then the name of the table which is customer.
After the table name, we enter a bracket. Inside the brackets
we enter the columns we want inside this table.
It’s a common convention to put each column on a new line.
You don’t have to, but it makes the code easier to read and modify.
So, we’ll enter the columns that we want to add to the customer table.
As a reminder, these are the columns from the ERD here, which are ID, firstname, lastname, address.
We’ll start with ID. We enter ID, then a space. Then we need to specify a data type.
Which data type should we use?
This, and choosing the size of the field, is one of the hardest things to do when creating a table.
In this case, we want to store a unique number for the row, so we’ll just use a simple INT data type.
The data types in each database are slightly different, so in Oracle it may be Number instead
of Int. I’ve linked a guide in the description to the different data types in each database.
After the INT, we enter a comma, then go to a new line.
We repeat this for the next column, which is firstname.
For this column, we want to store text. In MySQL this can be done using a type called
VARCHAR. This data type needs a maximum length specified inside brackets.
How long should we make this field? It should be long enough to cater for all possible values,
but not too long that it risks taking up too much storage or allows for too much data to be added.
For this exercise, we’ll go with a size of 200 characters.
For the next field of lastname, we do the same thing.
We specify it as varchar, and use 200 characters.
The final field is address. This is a little longer, and there are better ways of storing this
than in a single field, but for the purposes of this video we’ll just make it one field.
We’ll use 1,000 characters for this field.
Finally, we end with a closing bracket and a semicolon.
The statement to create our first table is done.
Let’s create our second table. Which one do we create?
For the purpose of this video, it doesn’t matter. We’ll choose the account table.
Follow the same steps as before. Write a create table statement to create the account table.
For the columns, we specify an INT for the account ID.
This example also has an account number, which is a number that the customer is aware of and
appears on their bills and statements. We can store this as an INT as well.
This table also has a customer ID, which tells us which customer the account is for.
We should make this the same data type as the customer ID in the customer table, which is INT.
We then have a status column. There are a few ways to do this, but for this video we’ll assume
it’s either a value of Active or Inactive. So, we’ll set it to be a Varchar, but the
size could be 10 characters. This allows us to store other values if we need to in the future.
Now we’ve got our Create Table statements for the first two tables.
Let’s write the create table statement for our last table, which is the usage table.
We’ll follow a similar process. We start with create table, then the name of usage.
It has an ID column, which we’ll specify as INT.
The account ID should be the same data type as the ID in the account table, which is INT.
The usage_date represents the date that a particular number of units was used.
This is a DATE data type in MySQL. In other databases, a different data type is used.
The units_used column is the amount of units that have been used for a specific date.
This will be a number, but it could have decimal places. So, we’ll use a data type of decimal,
and specify the total number of digits as 8 and the number of decimal places as 2.
The final column is the unit_price which is the price that is paid for each unit used. This
is also a decimal number, so we’ll use the same format: a decimal of 8 total digits and 2 places.
We add a closing bracket and semicolon,
and we have the Create Table statements for our three tables.
If we have a look at our design, each table has a primary key,
so that the records can be uniquely identified.
However, we didn’t specify anything about the primary key when we wrote the SQL statements.
Let’s do that now.
For each of our tables, we specify the primary key.
We can do this in two ways, either next to the column which is called inline,
or on its own line inside the Create Table statement which is called out of line.
Both methods will create a primary key, but I prefer to use the out of line method,
which is defining the primary key on its own line, because you can specify a name for it.
So, let’s do that now.
To do this, we enter a new line at the end of the Create Table statement,
ensuring that we have a comma after the last field.
We type Constraint, then enter a name for the constraint.
We’re on the customer table, so we’ll call it pk_customer. The pk stands for primary key and
it makes it easy to see which table it refers to if we see this name in an error message.
Then we enter Primary Key, then specify the column name which is our ID.
That’s how we add a primary key to our table.
We can repeat the same steps for the account table and usage table,
by adding the constraint keyword, then a name for the constraint, then Primary Key, then the column.
We now have primary keys for each of our three tables in these statements.
We’ve got our primary keys. Another thing that makes a well-designed database is foreign keys.
A foreign key is where a column in one table is related to a column in another table.
In our design, this is represented by arrows between the tables. One example
is the customer ID in the account table relates to the ID in the customer table.
We can specify this in our Create Statement, in a similar way to the primary key.
We start with Constraint, then a name for the foreign key. We’ll start with the letters “fk”,
then a few letters for the table which is “acc”, then the name of the column which is “custid”.
This means it’s quite easy to identify what it means when we see it in an error message.
Then we specify the column in this table, which is customer ID.
Then we put the keyword References, then the other table we want to link to, which is
customer. Finally we add the column in the other table, the customer table, which is the ID column.
That’s how we add a foreign key.
We can do the same thing for the usage table. This table has a column that refers to the account ID,
so our statement here is similar.
We don’t need to add a foreign key to the customer table
as there are no relationships that need it from this table.
We’ve written our Create Table statements. Our tables are not yet created.
But we can do this easily by running the statements.
Click the button in your SQL editor that runs your statements.
In MySQL Workbench, it’s this button here. This will run all SQL statements in the script, which
is all three Create Table statements. Some editors have a button that only runs one statement,
so you may need to click a different button or select all of the statements first.
Once the statements are run, you’ll see an output at the bottom of the screen
saying the tables are created.
If you get an error during this process that says a table cannot be found, this is likely
because the foreign key being defined in one table refers to a table that does not exist.
For example, if we created the account table first, and tried to relate it to
the customer table, we would get an error because the customer table does not exist.
To resolve this error, you can rearrange the Create Table statements
to put the tables that have a foreign key below the tables that they are related to.
Alternatively, you can use Alter Table statements to add foreign keys after the tables are created,
but we won’t cover that in this video.
So, assuming there are no errors, the tables are now created.
We can now insert some data into these tables.
We’ll insert a few rows into each table.
We’ll start with the account table for something different.
We start with the keyword Insert Into, then the table name of account. We then
specify the column names we want to specify data for, which in this case is all fields.
Then we specify the Values keyword, and inside brackets we specify values for each of the
columns. For the account ID and account number, we can specify numbers. For the customer ID,
we also specify a number. For the status field, we can specify the word Active,
which needs to be inside single quotes as it’s a text value.
We then end with a bracket and a semicolon.
We can repeat the same steps to add a few more rows. We’ll specify different values for each
column. You can copy and paste the lines here and edit the rows if that’s easier.
There’s also a way to use a form of the Insert statement to insert multiple rows,
which means less code to write, but we won’t cover that in this video.
So we’ve written some Account insert statements. Let’s insert some data into the customer table.
We write a similar Insert statement, specifying the columns,
and the values for each column. The ID is a number value so it does not need single quotes.
The first name, last name, and address all need single quotes as they are text values.
We’ve got a few rows here for the customer table.
Let’s add some records in to the usage table.
Here are the insert statements for the usage table.
This is a little different because we have some decimal values. We also have a date value. In
MySQL, we enclose it in single quotes, and specify it using the format of YYYY-MM-DD.
In other databases, we use a different format, and may need a function to make this work.
So, we have our series of Insert statements. Let’s run them to insert the data into the table.
To run these statements, we may need to select them and click Run. This is so we
don’t run the Create Table statements again from earlier in this script.
So, we select them and click Run.
When we do this, we see some error messages.
This error message here shows something to do with a foreign key.
It’s happened because we are trying to insert an account value and specify a customer ID,
but there is no record for that customer ID yet.
We can resolve this by changing the order of these Insert statements.
We can insert the customer data before we insert the account data.
Now that we have changed the order, we can run the Insert statements again.
We should now see that all statements are successful.
We’ve successfully created tables in our database based on a database diagram,
adding primary keys and foreign keys to the tables.
We’ve also inserted some data into the table, rearranging the statements to avoid errors.
You should be able to use this concept on your own database design to create your own
database based on it. As we mentioned, you can use other SQL editors and databases.
It doesn’t have to be MySQL Workbench with MySQL.
If you want to learn how to insert multiple rows in SQL,
check out this video where I explain the syntax of the Insert statement you can use to do this.
If you’re working on a database design, you may find this video interesting,
which is about 7 different database design mistakes to avoid.
If you learned something new from this video, make sure to subscribe to my channel.
If you want to learn more about database design and development,
visit databasestar.com. That’s where I share my best database-related content.
Thanks for watching.