The MySQL Relational Database Management System

MySQL·8 min read·Jan 1, 2025

A database is a structured collection of data that is organized in a way to facilitate its efficient storage, retrieval, modification, and management.

Databases are used by all sorts of applications to store various types of information, such as customer information, product inventory, student grades, course contents, etc.

A database management system (DBMS) is a software that allows end-users to interact with the database, facilitates the data entry, retrieval, modification, and deletion, and ensures the data integrity and security.

Altogether, the database, the database management system, and the associated applications are referred to as a database system.

The MySQL RDBMS

MySQL is an open-source relational database management system (RDBMS) that relies on the relational model.

It is known for its speed, reliability, and ease of use, making it a popular choice for many applications, from small websites to large-scale enterprise systems.

It is designed to handle large volumes of data, multiple users through access control mechanisms, and concurrent access to data using transactions and locking.

It is composed of several key components including:

  • A server in charge of handling all database operations, such as data storage, retrieval, and modification, as well as user access and security.

  • A client in charge of interacting with the server using commands written in the Structured Query Language (SQL).

  • A storage engine in charge of handling how data is stored and managed on disk.

The relational model

A relational database is a type of database that stores and provides access to categorized data points that are related to one another, based on the relational model.

The relational model is a way of representing data into tables, also called relations, where the rows of these tables are designated as records and the columns as attributes.

In this context:

  • A relation usually represents a specific entity type, such as a customer or a product.

  • A record represents an instance of that entity type.

  • An attribute holds a value of that instance, such as a name, an email address, or a quantity.

For example:

+----+------------------+------------+-----------+
| id | email            | first_name | last_name |
+----+------------------+------------+-----------+
|  1 | johndoe@mail.com | John       | Doe       |
+----+------------------+------------+-----------+

The benefits of the relational model

The relational model allows for a dynamic database structure that can easily be adapted to shifting requirements.

Its structure can be altered by inserting, updating or deleting relations, records or attributes, including the data they contain, without affecting other existing parts of the database.

It improves the accuracy and unicity of data by allowing data points to reference one another using the concept of primary and foreign keys.

It allows you to selectively combine the data from specific columns across multiple tables using join queries and conditional statements so that only the appropriate and relevant data is fetched and aggregated.

Installing MySQL

On Linux

To install the MySQL package on Linux, you can use the following apt commands:

$ sudo apt update
$ sudo apt install mysql-server

Once the installation complete, you can use the service command to start the MySQL server:

$ sudo service mysql start

Or the systemctl command if the service command is not available on your system:

$ sudo systemctl start mysql

On macOS

To install the MySQL package on macOS, you can use the brew install command:

$ brew install mysql

Once the installation complete, you can use the brew services command to start the MySQL server:

$ brew services start mysql

Connecting to the MySQL client

Once the database is up and running, you can connect to the MySQL server using the mysql command:

$ mysql -u root

Where:

  • -u is a flag used to specify the username of the database user you want to connect as.

  • root is the name of the default superuser account created during the installation.

Once logged in, the command prompt will change to mysql>, indicating that you are now interacting with the server:

$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.39-0ubuntu0.22.04.1 (Ubuntu)

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Disconnecting from the MySQL client

To close the current MySQL session and disconnect from the server, you can use the exit command:

mysql> exit
Bye

🗒️ Summary

Here's a summary of what you've learned in this lesson:

  • A database is a structured collection of data organized in a way to facilitate its storage, retrieval, modification, and management.

  • A relational database provides access to categorized data points related to one another based on the relational model.

  • MySQL is an open-source database management system that relies on the relational model.

  • In the relational model, tables are called relations, columns are called attributes, and rows are called records.

  • A relation represents a specific entity type.

  • A record represents an instance of that entity type.

  • An attribute holds a value of that instance.

  • The mysql -u root command is used to connect to the database server as the root user.

NEXT LESSON