Relational Databases and DBMS: Basics and Running MySQL

Picture a forum table where one cell holds “Cycling; Fishing”. How do you find everyone who started a thread about fishing when the values are glued together in a single string? This is exactly the kind of problem relational databases, and the systems that manage them, are designed to solve.

A database is an organized collection of related data stored in one or more files. A DBMS (database management system) is the software that creates the database, stores the data, runs queries and keeps the data consistent. In this lesson you will learn how relational databases work, then run MySQL and execute your first SQL query.

Database vs DBMS: What Is the Difference

A database is the data itself, while a DBMS is the program that works with it: the program does not keep the information “inside itself”, it manages the database files. A Java application never reads those files directly - it sends requests to the DBMS in SQL, and the DBMS returns the result.

Examples of databases are everywhere: the product catalog and orders of an online store, bank accounts and payments, train timetables, forum users and their posts. Each of these databases is run by a DBMS.

The main functions of a DBMS are storing and searching data, executing SQL queries, enforcing data integrity (the data never breaks the rules defined for the tables), handling many users at the same time, managing access rights, and backup and recovery.

In the simplest case the flow looks like this: the application sends an SQL query to the DBMS, the DBMS reads or changes the database files and returns the result.

Types of DBMS with Examples

The type you will meet most often in software development is the relational DBMS (RDBMS), which stores data in tables: MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server and SQLite. Other data models are served by non-relational (NoSQL) systems.

This module uses MySQL: it is free, widely used and a good fit for learning. Almost everything you learn about SQL here also works in other relational databases, with minor differences in dialect.

The Relational Model: Tables, Rows and Columns

A relational database stores data in two-dimensional tables: each row describes one record, and each column describes one attribute of that record. A table in a relational database has the following properties:

• every column has a unique name;
• all values in a column have the same type (number, string, date);
• there are no duplicate rows;
• the order of rows does not matter;
• each cell, at the intersection of a row and a column, holds a single atomic value, not a list of values.

“No duplicate rows” is a property of the model: a real SQL table without constraints will happily accept two identical rows, and it is the primary key that prevents this (keys are covered in a separate lesson of this module). The atomicity requirement is the foundation of normalization, which also has its own lesson in this module.

Example: the city table

Each row of the city table is one city, and each column is one of its attributes: name, country code, district and population.

Why Data Is Split into Tables: a Forum Example

Data is split across several tables so that every cell holds exactly one value and nothing is duplicated. Suppose you need to store forum users and the threads they started. With a single table, you get this:

Bob has two threads in one cell, which breaks atomicity: searching for the “Fishing” thread now means parsing strings. So the table is split in two:

Now every cell holds a single value. What is left is to link rows of different tables reliably (for example, a thread and its author, even if a second user named Bob joins the forum) - relational databases use keys for this. How keys work is covered in a separate lesson of this module.

How to Run MySQL

There are two ways to run MySQL: in a Docker container (one command, nothing is left behind on your system) or by installing it on your computer. This course uses MySQL 8.4, a long-term support (LTS) release. Versions 5.7 and 8.0 have reached end of life (8.0 in April 2026), so do not learn on them.

Since 2026 MySQL has two LTS lines, 8.4 and 9.7, and new Innovation releases use calendar version numbers: 26.7, 26.10 and so on. For the queries in this module, there is no difference between them.

Option 1. Run MySQL in Docker

If Docker is already installed, MySQL starts with a single command:

docker run --name mysql-course -e MYSQL_ROOT_PASSWORD=secret -p 3306:3306 -v mysql_data:/var/lib/mysql -d mysql:8.4

The first run downloads the image and initializes the database, which takes anywhere from a few seconds to a minute. The server is ready when the log shows ready for connections together with port: 3306: on the first run the same line is also printed for the temporary initialization server (with port: 0), and there is no point connecting to that one. Then check the version and connect with the client inside the container:

docker logs mysql-course
docker exec mysql-course mysql --version
docker exec -it mysql-course mysql -uroot -p

Enter the password (secret) and you will see the mysql> prompt. Type exit to leave. You can stop and start the container again with docker stop mysql-course and docker start mysql-course: the data is kept in the volume.

Option 2. Install MySQL on Your Computer

The version you get from a package manager depends on the system: on Ubuntu 26.04 apt installs MySQL 8.4, older releases may give you the end-of-life 8.0, and brew install mysql without a version installs the latest Innovation release 26.x. Any of them works for the practice queries in this module. After installation, check that the client is found and the server responds:

mysql --version
mysql -uroot -p

The first command prints the client version, for example mysql Ver 8.4.8 for Linux on x86_64 (MySQL Community Server - GPL); the second asks for the password and opens the mysql> prompt. If you get command not found or 'mysql' is not recognized as an internal or external command, the MySQL bin folder is not in PATH.

Graphical Clients

You can type queries in the console, but tables are easier to work with in a graphical client. Every client needs the same connection settings: host 127.0.0.1 (or localhost), port 3306 (or the port you mapped), user root and its password. With the console mysql client on Linux and macOS, prefer -h 127.0.0.1 when the server runs in Docker - see the FAQ below.

The lightweight HeidiSQL client and the rest of the course software are listed on the Java Developer Tools checklist.

Your First SQL Query in MySQL

Your first query takes five steps: create a database, select it, create a table, insert rows and read them back. Connect with the mysql client (see above) and run the commands in order; each one ends with a semicolon.

Create the forum database and make it the current one:

CREATE DATABASE forum;
USE forum;

Create the users table and check that it exists:

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
);

SHOW TABLES;

+-----------------+
| Tables_in_forum |
+-----------------+
| users           |
+-----------------+

Insert two rows and read the whole table:

INSERT INTO users (username, email) VALUES
    ('Alice', '[email protected]'),
    ('Bob', '[email protected]');

SELECT * FROM users;

+----+----------+-------------------+
| id | username | email             |
+----+----------+-------------------+
|  1 | Alice    | [email protected] |
|  2 | Bob      | [email protected]   |
+----+----------+-------------------+

You did not fill in the id column - MySQL numbered the rows itself thanks to AUTO_INCREMENT. This column is the table’s primary key; keys are explained in a separate lesson of this module. What will the next query print?

SELECT username, email FROM users WHERE id = 2;

+----------+-----------------+
| username | email           |
+----------+-----------------+
| Bob      | [email protected] |
+----------+-----------------+

The syntax of CREATE, INSERT and SELECT and the column data types (INT, VARCHAR) are covered in detail in the next lessons of this module. Later, the same queries are sent from Java code through JDBC.