SQL Databases - RDBMS

Databases (DBs) are fundamental to all applications and are at the core of all sites/apps which handle any form of data. The services/sites/apps you use daily will employ databases, from online blogs, forums to even mobile apps. Some examples include The New York Times, Daily Mail, Wikipedia, Facebook, Twitter, Uber, Lyft, Trivago, Netflix, YouTube, eBay amongst millions of others. (As an interesting read, you can see “Programming languages used in most popular websites” on Wikipedia.)

There are a selection of databases we can choose to develop with, however we will be utilising MySQL. Technically speaking we will be using MariaDB, which is a fork of MySQL (i.e. is based on MySQL). MySQL, MariaDB and Percona were founded by the same person and are all cross-compatible; they use the same syntax for all queries. (Many mobile apps tend to use SQLite or Firebase for their databases.)

As we have covered working with both the client and server-side, combining working with databases collectively will enable us to build web applications. SQL databases are widely used within the development industry as the standard, therefore you’ll find tonnes of information online if you are to search.

As we have installed XAMPP onto our machines, in addition to providing Apache, Perl and PHP it also provides MariaDB which we can utilise. XAMPP provides a local database server on our machine. Using a database client such as HeidiSQL will allow us to connect to any SQL database, including both local and remote databases.

1. Using a Database Client

Database clients are tools which we install onto our machines to connect to databases. At the start of this course we installed HeidiSQL which is a DB client we can use to connect to database servers (i.e. MariaDB via XAMPP). However if you do have your own preference of a DB client, you may feel free to use the one you’re most comfortable with.

I’m currently using NaviCat, but other offerings are (but not limited to):

• Sequel Pro (free, macOS) which is one I would recommend as it is a simple no-thrills client.
• HeidiSQL (free, Windows only). Practical with plenty of features, but can seem a little confusing.
• DataGrip (free 30 day trial, available on Windows, macOS and Linux).
• phpMyAdmin (installed with XAMPP – click “Admin” next to MySQL in XAMPP Control Panel. Primarily used to connect to a local database and is accessible from your browser).

This guide below will be based on using HeidiSQL, but you can use phpMyAdmin if you find that easier.

2. Connecting to a Database Server

Using HeidiSQL, we can connect to our local MariaDB database server. Firstly in XAMPP ensure both your “Apache” and “MySQL” are running, you can do this by clicking “Start” beside the appropriate row in XAMPP’s control panel.

Now open HeidiSQL and on the window which appears, click “New” in the bottom left of the application. It will automatically populate some default configuration details, such as:

• Network type: The database server software and connection type
• Hostname/IP: The default is 127.0.0.1 which is the IP address for our local machine
• User: The default is ‘root’
• Password: The default is blank (i.e. no password)
• Port: The default database port standard is 3306.

From the above you can see that having the default credentials of the username (root) and password (blank) is not secure. However these are the MySQL defaults on a new installation, and to be honest, you don’t need to change these on your machine as it’s only local and no one can access your machine remotely. We should never however have this username/password combination on a production server as it would compromise security.

You can now click “Open” in HeidiSQL to connect. You will then be presented with the default MySQL databases (information_schema, mysql, performance_schema, phpmyadmin and test) which we can ignore.

You will see all these databases on the left hand-side of HeidiSQL. On the right hand-side at the top, there is a “Query” tab where we can manually type in our own SQL queries. Above the “Query” tab is a blue play button, which will execute our commands (you can use the F9 shortcut key to run this).

3. Basic Understanding of Databases

MariaDB on our XAMPP installation acts as a SQL server. When we connect to the “database server” using a client such as HeidiSQL, we can then create multiple databases which will hold our data. We should create a new database for each project we work on and we can name the databases appropriately after the project name, e.g. “johns_blog”, “partshop” and etc.

• Each database will hold multiple tables.
• The tables will then have multiple records.
• The records will be stored with appropriate data under each column (i.e. attribute).

The terms “tables”, “records”, “columns” also have alternative names but I won’t mention them now to avoid confusion.

Here’s an example. There’s a database named after a project, let’s say “theworld”. In this database we hold a list of all countries and languages, therefore this database will have two tables for each of these respectively.

The countries table will have the following columns/attributes:

• id
• name
• iso
• dialing_code

Therefore this table with some example records may look like:

Notice we have an “id” column; this is a Primary Key (PK) which mandates that the value in the column (i.e. id) for all records must be unique. As a result we can use the PK to identify a single record. From the table above we know the record with ‘id’ = 1 is the United Kingdom.

When we insert records into the table, we don’t need to specify an ‘id’ value if we have the column set to auto_increment (in MySQL) as this will automatically generate the id for us.

Next, our languages table will have the following columns:

• id
• language

So it’s (one of) the most simplest table(s) we can create. With some example records, the table may look like:

And again we can identify records using the PK, e.g. 1 = English.

We’ve described the use of Primary Keys above, now let’s look at Foreign Keys (FK). Sequel (i.e. SQL) are relational databases, meaning the tables can be related and this is where FK comes in. Let’s say we now want to modify our countries table and introduce a new column, called “primary_language” to denote the main language spoken in the specified country.

Our countries table would now look like:

The “primary_language” column here holds an integer value and is a FK; the value here relates to the ‘id’ of the ‘languages’ table. Therefore the value of ‘1’ in the primary_language column would resolve to be ‘English’, as defined in our ‘languages’ table. Similarly the value of ‘3’ would resolve to German, as also defined in our languages table.

Primary or Foreign Key’s are not naturally/automatically defined. When we create new columns in a table we must specifically declare which column is a PK and which is a FK (if the latter is used). PK’s in almost all cases are mandatory whereas FK’s are not always used.

Now moving onto data types, each column within a table must be defined with a data type. For example in our countries table, we can set the ‘id’, ‘dialing_code’ and ‘primary_language’ columns as the ‘integer’ data type, ‘name’ and ‘iso’ would be a string data type. For the ‘iso’ column we can further set a limit of only 2 characters. By setting data types on our columns we enforce data validation within our database, preventing corruption and inconsistencies.

All the above are the basics of working with SQL databases and there are significantly more we can do with them, such as performing joins, grouping, sorting, calculations and so on. Please don’t feel overwhelmed at this point as SQL is actually a simple language to pick up by starting from the basics, as it is the de facto standard in the industry (worldwide) and as a result there are heaps of knowledge that can be obtained online.

4. Creating a database

I’d like you now to experience and experiment with creating your own database with tables, columns and records. You may use your preferred SQL client (HeidiSQL, phpMyAdmin or your own) to connect to our local XAMPP’s MySQL server. To create the database you will need to execute/run your own queries.

• For understanding of the SQL language and syntax, please see the w3school’s SQL Intro.
• For creating databases, please see the “SQL Create DB” page on w3schools.
• For all the data types, please see the “SQL Data Types” page on w3schools.

Please also see the “SQL Not Null”, “SQL Primary Key” and “SQL Auto Increment” pages (on the left-hand side) of the w3school’s pages to help you with creating tables.

You are free to name the database to your choosing. However the table I’d like you to create is a ‘users’ table. The table should hold information on people, such as their name, address, phone number and etc. You may define your own additional columns too. Then I would like you to add records to your table (you are not required to populate real information, you can add fake data).

Next I’d like you to create a ‘professions’ table with a list of various jobs an individual can have. I would then like you to create a foreign key in your users table which associates a user to a profession (e.g. user A is a developer, User B is a solicitor and etc.).

(You may have noticed the standard convention we follow for naming tables are plural, this is the case as they hold multiple records; e.g. users, transactions, products, invoices and etc.)

We will be making use of the database we create in a later session, when we move onto performing database calls (i.e. SQL queries) using a server-side language, and in our case PHP.