Simple Database Tutorial v1.0

Today, you will learn how to use Adminer to access, create and manage a MySQL database.

Introduction

In this tutorial, you will learn three main things:

  • The basic elements of a database: tables, colummns, etc.
  • The Adminer database management interface.
  • Elementary SQL language. (The code we use to talk to DBs.)

At the end of the tutorial, you will have created a new DB. This will consitute the assignment called Exercise 1 • Simple Database. You must complete the tutorial to get the points.

Part 1 — Reminder: Elements of a DB

You remember from my lecture that a database is a structure to organize data. In our specific context, a database is designed to contain and manage data generated and used by an app.

Data in a database is organized in tables. A table is a representation of an object used by the system. For example, many apps will have users that can log in and use the system. In fact, you will build apps with users this semester. For an app or a system, a user is an object; so it is stored in a table in the database. To follow our example, let’s call it the user table.

object used by the system => table in the database

Each object, represented as a table in the database, has a series of properties. For example, the user object will have a username, a password, perhaps a real name too. These properties are represented as columns in the table. So the user table, will have at least two columns: username and password. Remember as well that each entry in the table is assigned a unique ID that is used by the system to access the properties of each specific item in the table. So the user table also has an id column.

property of the object => column in the table

The last element we are going to look at here is the data type of the columns. In a database, each column is created to store a specific type or category of data: text, numbers, date, image, etc. If you create a column to store a number, you cannot put a word in it. So you need to specify what kind of data you store in each column. The id column always contains an integer (int), or a number that is not a fraction. And in our example, both username and password columns will contain a string of characters. In MySQL, this type is called varchar (variable characters).

category of data for property => data type of the column

We can now draw a simple schema of the table we are talking about here. This is called a data model.

user <— name of the table
id int() <— id column, contains integer
username varchar() <— username column, contains characters
password varchar() <— password column, contains characters

If we were looking at the content of such a table, it would look something like this:

user
id username password
123 aaa *****
124 bbb *****
125 ccc *****

In more general database theory vocabulary, a property or column is called a field. And an entry or item in the table is called a record. You will hear me say these words sometimes; it would be good for you to remember what they are.

By following this tutorial, you will analyse a mandate, you will determine the tables and the columns required for the system, you will design the required database, and you will create the database on the server. You will not build an app yet, only the database.

Part 2 — Your mandate

Keep in mind that the example in this tutorial is extremely simple, and kept to the very minimum. It will have only one object/table, and we only store minimal information about the object. The point here is for you to learn the process, not the specifics of the example. Pay attention to the steps.

You are asked to:

Design the database for a system that lets me keep track of my vinyl albums collection.

This is a very simple mandate. But it illustrates the basic elements that you will need to determine in order to create the products I am asking you to do in the projects in this class.

First we need to analyse the mandate to determine what database we need to design. To do this, you need to ask yourself a question:

What are we dealing with here? Or, what objects will this system handle?

The answer to this question will determine the table or tables in your database.

According to the mandate, the app will manage “vinyl albums”. So we will need a table in the database to store information about these albums.

The second question you need to ask is:

What are the properties of albums that the system needs to know?

There are many things we could store about the albums. What would be the minimum? An album has title, it has an artist (there can be more than one artist on a record, let’s keep it simple shall we?), it has a year of release, it has songs, it has a cover art, etc. For this example, we are going to only record the title, the artist and the release year in the system’s database. These will be the columns in the table.

Part 3 — The data model

So, according to the results of our analysis of the mandate, we have determined that the database will have a table that we can call album, and it has three columns: title, artist, and year. Of course, we must not forget the id column which every table should have.

Note that the names of things in programming, in database and otherwise, cannot have spaces in them. So the name of the table cannot have spaces in it, the names of columns cannot have spaces in them. You will get errors if you try to create a table with a name that has a space in it. Use underscore _ if you want to separate words in the names.

For each column, we need to determine the data type. The title column can contain any number of words, so it will be varchar. The same is true for the artist column. For the year column we have a choice in MySQL. There is a specific data type for dates, but we will instead just keep it simple and create it as an integer column. The id of course will be int.

Here is the data model for the simple database you will create:

album
id int()
title varchar()
artist varchar()
year int()

Pay attention to the process here, it will be the same for every project in this class:

  1. Analyse the mandate,
  2. Determine the objects used by the system,
  3. Determine the properties of the objects,
  4. Name the table for each object,
  5. Define the column for each property of each object,
  6. Determine the data type of each column,
  7. Draw the data model.

We are now ready to create the database on the server. In the next step, you will access Adminer and create the table above in your database on the 2022 server.

Part 4 — Create the DB

To manage your databases in this class, and in fact throughout the program, you will use a tool called Adminer. It is a simple web interface to interact with a database server. Nerd Alert! If you are curious, the DBMS we use is MariaDB which is fully compatible with MySQL. Adminer is written in PHP, a language you will also learn in this class. :-)

You can get more info about Adminer here if you want, but it is a very simple tool.

Access your own database

You access Aminer on the 2022 server at the following address: (notice the port number, 12322, after the colon in the url)

https://2022.micromediaweb.com:12322/

Each of you already has an account on the server. Your Username is your student ID. The password was given to you in class. At the login form, enter your credentials, and you should be able to get in. (If you encounter problems accessing Adminer, let me know right away. You need this to complete every assignment in this class.)

After logging in, you should see something like the image below. Adminer looks a bit weird, and the interface is messy, but you’ll get used to it. On this screen, you must select your database. You should see a database called db[your student id]. In the screenshot, it is db1111111. Click on this link, it will bring you to your database. All the tables you will create in this class for your apps will be in this database.

This is the main Adminer interface. I will explain the parts you need to look for and use as you need them. Again, this may look odd to you, but you will get familiar with the main elements quickly. It is quite simple.

You will notice right away that you have two tables already in your database. I created those. You can ignore them for now. If you are curious and check them out, it contains the family tree of Kanye West and Kim Kardashian. (It is possible that you have more tables, especially if you took this class before. Don’t worry about it.)

Create the table

Here we will create the table we have designed after our analysis of the mandate: the album table.

Click on Create table. You will find this button at two locations, I have marked them with arrows above, click one or the other, it does not matter. You will be presented with the table creation tool. (I’m going to crop the image from now on to focus on the tool we use.)

At the top of the tool, you will see a field where you can type the name of the table. And below, there is the control where you can create the columns for this table.

Enter the name of the table: album.

Next, we will create the first column: id. For this one, you need to pay attention. We will do two things that are very important, and that you will need to do for every table you create in this class, and probably ever in your life: set the id column’s data type to int() and set the column to auto increment.

WHAT? Auto increment means that the column will be assigned a value automatically by the database system. Since the id column is used to indentify every individual entry in the table, each entry must have a unique id value. The only way of assuring this, is to have that value created by the system. So every time a new entry is added, it gets assigned a new value of id which is 1 plus the previous value. Later, when we start adding data in the table, you will understand.

OK, so your id column definition should look like this:

Pay attention to the three elements marked by an arrow. Starting left, the first arrow is the name of the column. This should be straight forward, but be careful, you cannot have spaces in the name. no spaces Next you have the data type. This is a drop down menu of all the available data types in MySQL. Find and select int (for interger). (Note that Adminer will select it for you if you name the column “id”.

The third arrow is super important. This is how you set the column as auto increment in Adminer. This also makes the column the primary key. We will talk about keys later in the semester when we make relational databases, but for now just remember the auto increment feature. Make sure the field is checked, but only for the id column, not the others.

Now you can create the other columns. Follow the data model we created above. The other columns are not auto increment, so the AI field should not be checked for them. You just give them a name and a data type. You will notice that as soon as you start naming a column, Adminer creates the fields for you to add another one. So it is quite quick. Your final setup should look like this:

Notice that the varchar columns have a number below Length. This is the size of the column. For now, you can write 255 like my example. You can now click Save.

Your table has been created, and you should see this:

You see how similar the presentation of the table’s definition is to the data model. This is not a surprise. ;-)

I encourage you to click on the button that says SQL Command in the green area. (If you see it, it does not alway appear.) This will show you the command that was sent to the server in order to create this table. Every time you do anything in Adminer, the tool generates some code that is sent to the server to do what you ask. And you can see that code. It is a great way of learning the SQL language which your apps are also going to use to communicate with the database.

Bravo! You have created a database table! OMG!

Part 5 — Add data to the table

The table has been created now, but it contains nothing. If you click on Select data in the screen after you create the table, you will see that it contains no rows. A row is an entry in the table.

In what follows, you will be introduced to two basic SQL commands: INSERT and SELECT. These are the two main SQL commands used by systems. You will use these two commands a lot in this class.

Add entries into the table – INSERT

To add an item in your table using Adminer, you need to click the New item button. (I specify “using Adminer” because when you create your apps later in the semester, they will send raw SQL code to the server instead of clicking on buttons. More about this below.) When you click the button, you see a form that lets you enter values for each column in the table.

Be careful! You should not enter a value for the id column. Remember that this column is automatically incremented. So leave this field empty.

Enter values for the other fields; then, click Save.

You now see that your table has a row! Also, click on the SQL command button to see the SQL code. I will explain it below.

The SQL command to add an item in the table is INSERT. The command (with my values as example, yours will have your values, of course) looks like this:

INSERT INTO album (title, artist, year)
VALUES ('Best album aver', 'My favourite band', '1982');

Let me explain. First you say INSERT INTO which starts the command. Then you specify the table in which you want to insert the data. In this case album. In the following parenthesis, you write the columns you wish to provide data for. Note how they are separated by commas. Also notice that the id column is not included because we do not provide a value for it. Then comes the VALUES keyword, and another set of parenthesis. These are for the values you want to insert in each column. Notice how the values are wrapped around prime characters (this is prime: ' , this is double prime: " ). Also note that the values are listed in the same order as the columns; this is how the database knows which value to insert in which column.

When you start building your own apps, your app code will send similar INSERT commands to the database every time the app needs to store a new item in a table. For example, when a new user account is created.

I suggest you enter a few items in your table before moving on to the next step.

Get some data from the table – SELECT

The next SQL command I will introduce to you is SELECT. This is used to get the data from the database. Your apps are going to make a lot of SELECT commands. We will keep it simple for now, but SELECT is probably the most complex of all the SQL commands. It has lots of options to make a variety of data selections.

If you want to see what a SELECT looks like, just click on Select data. You will see the command appear above the result table.

The command is this:

SELECT * FROM album LIMIT 50

First you have the SELECT command. Then you have an asterisk (this thing: * ). Generally, you would list the columns you want to select here. But in this case, we select everything. So * means all columns. Next you specify the table from which the data comes, here album. The last part, LIMIT 50 sets a limit of 50 items in the results. You don’t need this, but Adminer does it on its own.

Let’s say you wanted to select only the title and the artist from the albums released in a specific year; 1982 for example. (You will need to use your own values for this to work on your table.) The SELECT would look like this:

SELECT title, artist FROM album WHERE year = "1982"

See how we specify the columns to return, and we add a condition: WHERE year = "1982". This will select the values in the title and in the artist columns for the items that have 1982 as their value for year.

If you want to test this command, click on the button SQL command on the left of the screen. Not the one we talked about before. I know this is confusing, but Adminer sometimes uses the same name for different things. Pay attention.

In the large textarea that appears, paste the command and click Execute. You should get the result. If you see a red message, it means there is something wrong with your code. Keep it simple. And make sure you use your own values, not mine.

This command prompt in Adminer is extremely useful while you are developing your apps, to test your SQL code in your database. Remember this.

This is it for now. Next week, we will start building a real app!