Relational Databases and DBMS: Basics and Running MySQL - Quiz

A database is the data itself: an organized collection of related data stored in one or more files (for example, forum users and threads). A DBMS (database management system) is the software that creates the database, stores the data, runs queries and keeps the data consistent (MySQL, PostgreSQL, Oracle Database). SQL is the query language an application uses to talk to the DBMS, for example SELECT * FROM users;.

A Java application never reads the database files directly: it sends an SQL query to the DBMS, the DBMS reads or changes the files and returns the result. That is why MySQL and SQL are not the same thing: MySQL is one of the DBMSs that understand SQL.

The main functions of a DBMS:

• 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;
• backup and recovery.

The application does not work with the database files directly — the DBMS does all of this for it.

• Relational (RDBMS) — tables with rows and columns and relationships between tables: MySQL, MariaDB, PostgreSQL, Oracle, SQL Server, SQLite.
• Document — documents, usually JSON: MongoDB.
• Key-value — “key → value” pairs, usually in memory: Redis.
• Graph — nodes and the relationships between them: Neo4j.
• Hierarchical and network — historical models that came before the relational one: IMS, IDMS.

Non-relational systems are grouped under the name NoSQL. In Java development you will meet relational DBMSs most often; this course uses MySQL.

The relational model stores data in two-dimensional tables: each row is one record, each column is one attribute. Properties of a table:

• every column has a unique name;
• all values in a column have the same type;
• 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.

That is why a cell like “Cycling; Fishing” is split up: the threads are moved to a separate table, one row per thread. In a real SQL table, duplicate rows are prevented by the primary key.

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

• -e MYSQL_ROOT_PASSWORD — the root password; without it the container exits with an error right away;
• -p 3306:3306 — port mapping (host_port:container_port);
• -v mysql_data:/var/lib/mysql — the data lives in a volume and survives when the container is removed.

The server is ready when docker logs mysql-course shows ready for connections with port: 3306. Connect with the client inside the container: docker exec -it mysql-course mysql -uroot -p. From your computer, specify the host explicitly: mysql -h 127.0.0.1 -P 3306 -uroot -p.

Five steps: create a database, select it, create a table, insert rows and read them back:

CREATE DATABASE forum;
USE forum;
CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
);
INSERT INTO users (username) VALUES ('Alice'), ('Bob');
SELECT * FROM users;

Each command ends with a semicolon. If you skip USE forum;, MySQL reports ERROR 1046 (3D000): No database selected; if the database already exists, you get ERROR 1007 — then write CREATE DATABASE IF NOT EXISTS forum;.