How SQL Database Engines Works (RDBMS)?

So in this today article, i will talk about database, SQL, DBMS, RDBMS(SQL Database engine) and how works SQL Database Engines….(as simple as possible).

I want to explain firstly, what is a data and what is a database :

Data is simply raw informations about something as age, nationality, that can be also statistics… A database is where you will store all these informations in order to save them and retrieve them when you want. In this article i will talk only about relational database which is a type of database that organizes data into tables which can be linked or related based on data common to each.

Now, a DBMS is a sotfware used to store and manage data from the database, we can insert, delete and update data, create new database... A RDBMS is likely the same as DBMS but more advanced one and more recent (1970 for RDBMS and DBMS 1960), it will not store into a file(XML..) but in the form of tables (see image below), with RDBMS you can have multiple Users that can manipulate the database. Examples of RDBMS (Mysql, SQLite, Oracle). RDBMS perform only with relational database.

example of tables with RDBMS

Tables are organized into columns, and each columns stores one type of data(integer, character string, date..). The data for a single instance of a table is stored as a row.

What is SQL ?

SQL stand for Structured Query Language, is a programming language used to interact with data/database in an RDBMS. A lot of RDBMS use their own SQL. SQL is basic and simple in his form of writing but in fact a simple command as :

‘’ SELECT * FROM table_1”

That means select everything from the table_1 (in a database) and return every data in this table. This simple line, in fact has hundred of line behind it, see image below (and a lot of process for the RDBMS) :

Line of code behind “SELECT * FROM table_1”

The SQL engine from a RDBMS is a kind of software that collect and interprets the SQL line commands to do the appropriate operations to perform specific action asked by the SQL command line on the relational database.

All the process will perform the SQL engine from the SQL line enter by the user to the give data :

In and out of SQL line process in an SQL engine

Every SQL database engine contain two main components in the process COMPILER and VIRTUAL MACHINE to execute the query :

In and out process SQL database engine

The compiler part will check the validation of that statement and convert the query commands (your SQL line) into an appropriate byte-code, then the byte-code will be sent to the server/virtual machine in order to get optimized, the byte code will be optimize with the best algorithm (known as Query Optimizer)and will be interpreted and finally run.

To conclude

So a RDBMS use SQL to interact with the user and the data from a database, the simple query in SQL as SELECT is not simple as we think at first, it has more than one line of code to perform this specific operation, it will be compiled, converted, optimize and will be run by the SQL Engine in order to get our informations that we requested but that’s beautiful to see that with just one line we can perform a lot of process even without knowing it.

That’s all, i hope you understand something, if you have more informations about this, send me a message it will be appreciate :)

Software engineer student at HolbertonSchool 👉https://github.com/ThibautBernard 👉https://twitter.com/__ThibautDev