To manage the data in a database, users need a database management system. This tool is indispensable for accessing, processing and reading the information contained in the database. But how does it work? What are the main DBMSs on the market? DataScientest answers your questions.
What is a DBMS?
DBMS definition
A DBMS (or database management system) is software that enables end users to manipulate data in a database. For example, to retrieve, add, modify, compress, extract or delete data. Without a DBMS, the database is not functional.
In addition to data management, the DBMS provides a centralized view of this data.
Be careful not to confuse database, database schema and database management system.
- The database is the structure that holds all the data.
- The database schema defines the logical and/or physical structure of the database. It’s a blueprint.
- The DBMS is the software used to operate the database.
Together, they form the database system (commonly referred to as a database, although this is a semantic error).
Database management system features
As the central element of a database system, a good DBMS must meet a number of requirements characterized by the acronym ACID, for :
- Atomicity: which can be translated as the “all-or-nothing” principle. This means that a transaction must be executed correctly (respecting syntax and order rules) or not at all.
- Consistency: the database must remain stable, no matter what modifications are made. This implies constant checking.
- Isolation: queries must be independent of each other.
- Durability: all data must be saved, especially in the event of errors or bugs.
DBMS components
The database management system is made up of a number of fundamental elements that are essential to the software’s smooth operation:
- A data dictionary: this groups together the metadata that gives context to the information stored. For example, this metadata can tell you the date the data was created, the authorizations for use, the author, etc.
- Data definition language: LDD (or Data Definition Language -DDL) structures the objects contained in the database, such as references, relationships or user rights.
- Data manipulation language: LMD (or Data Manipulation Language – DML) is used to interact with data.
The LMD is one of the most important elements of the database management system. Indeed, to manipulate data via the DBMS, you need to give orders. But not just any commands. Computer resources have a very specific language.
SQL (Structured Query Language) is often the most widely used language for interacting with databases. This language brings together a set of universal standards for interacting with data. However, each database management system has its own SQL syntax. And for other DBMS, a completely different language is used.
What are the main database management systems?
MySQL
It’s the most widely used DBMS (notably by the WordPress CMS, which is used to create the vast majority of websites). However, it’s a bit of a bad boy when it comes to SQL. Yes, of all database management systems, it’s the one whose syntax is the furthest removed. It doesn’t follow SQL recommendations and is very permissive when it comes to SQL queries.
But MySQL is so popular because it has many strings to its bow. Not least its robustness and ability to handle huge volumes of data.
Good to know: MySQL was originally an open source DBMS. However, since its takeover by Oracle Corporation, it is no longer open source. That said, if you want the open source version, you can always use its copy, MariaDB, which uses the same SQL syntax.
Oracle Database
Mainly used by large enterprises, Oracle Database is also capable of processing large quantities of data. This relational database management system (RDBMS) is particularly appreciated for its compatibility with numerous platforms and applications, its automation features and its optimum level of security.
PostgreSQL
This open source software has long been the DBMS of experts, as it was confined to Linux operating systems. But PostgreSQL is gradually gaining market share.
How is it doing so?
- It’s evolving very rapidly;
- It follows to the letter (or almost to the letter) the recommendations of the MySQL language;
- It is used by innovative companies such as Instagram and Spotify.
MongoDB
MongoDB is a document-oriented SGDB. It is therefore not transposable to relational databases, which store their data in structured tables. Instead, MongoDB stores its data in flexibly structured collections.
Another special feature: MongoDB doesn’t use SQL, but rather the JavaScript-based MongoDB Query Language (MQL).
SQLite
SQLite works a little differently from other DBMSs, since it stores all databases in the same place.
For this reason, it is not capable of handling huge volumes of data. This database management system is recommended for local databases. In fact, it’s used to store data for Android applications.
Conversely, on an enterprise scale, SQLite lacks the power to handle all server data.
Master DBMS with DataScientest
Even if many data experts or developers specialize in one DBMS, it’s best to know several. And, above all, to master all the basics of the SQL language so as to be able to interact with data from any DBMS.
DataScientest makes it possible. As well as introducing you to the main database management systems, we teach you the different SQL queries so that you’ll be up and running by the end of the course. Find out more about our program.
