We tell you how DBMSs are structured and what they are, and we share a selection of the five most popular ones among database developers.
Today, databases store everything- your correspondence, website passwords, videos on web hosting sites, tracks on music platforms literally, and essay writer free online store your academic work on various databases. But for all this information not to be lost and for the data to be conveniently managed, they invented DBMS.
What is a DBMS?
To understand a DBMS, you must first know what a database (DB) is.
A database collects ordered and structured data stored on a particular computer. The easiest way to think of it is as a large Excel table where each element (row) has certain properties (columns).
If you create a database for a grocery store, each item - for example, gum, chocolate bars, and bottles of water - will have properties: price, number of items in stock, and expiration date.
Databases are files on a computer disk where you can write new items. But the databases themselves don't know how to do anything, and you have to write your methods to manage them, for example, to add a new item or search for a desired entry. To make the work of programmers easier, a DBMS was invented.
A database management system (DBMS) is a set of tools that make it easy to manage databases: delete, add, filter, and find items, change their structure, and create backups.
A DBMS can be considered a layer between a database and user queries against it.
When a user clicks a button on a Web site -- for example, to download a picture -- the site makes a special query to the database and sends it to the DBMS. It parses it, searches the database for the requested information, and then sends it back to the site. The site then converts it to a human-readable form and returns it to the user.
Without an RDBMS, developers must search the database files for the needed information. You could say that a database without a DBMS is like a car without a body. Theoretically, it is a car: you can fill it up and change oil and parts. But you can't drive it properly. You must bolt everything on first, make the body, and only then drive it.
DBMS is needed to simplify programmers' lives and give them all the tools to work with databases.
Why Do You Need a DBMS?
DBMSs are needed for all manipulations with databases, namely to:
• create databases and administer them: delete, modify, and merge;
• to keep the data in a structured form and the necessary format;
• protect data from unwanted changes and hacking attempts;
• download and sort data with the help of filters;
• make backups, restore databases after failures, and maintain overall integrity.
With a DBMS, developers keep track of all changes to the databases. And databases store user information: their transactions and actions.
For example, to create a database with a catalog of all products on the marketplace, you need to have a huge table with rows where each will have certain properties. These can be as follows:
• product name;
• cost;
• the number of items in stock;
• supplier.
To keep such a table in order and to constantly add new elements to it, you need to ensure reliability and high speed. This is what DBMSs are for.
What is a DBMS?
A DBMS is a collection of tools, each capable of performing certain actions on the database: read it, delete items or process requests from the user. And for all these tools to work properly, the DBMS must have a well-defined architecture.
The main elements of a DBMS are the kernel, the processor, the software tools, and the databases. Let's talk about each of them in more detail.
Core. It is responsible for the work of the system as a whole. All data processing and storage processes pass through it. Kernel monitors all changes in databases and fixes them.
A processor or a compiler. It processes the queries from the users. Its main task is to convert SQL queries into commands understandable for a computer and then return the results.
Software tools or utilities. They are needed so the user can enter queries and administrators can configure access and other necessary parameters.
Databases. A place where data is stored in an organized and sometimes encrypted form. Databases can vary in structure and types of data representation.
Types of DBMSs
DBMSs are divided into different types and by different parameters. We will look at how they differ in four classifications: by database location, by data storage and processing, by query language, and by data structure and organization.
According to the location of the databases
DBMSs can be local or distributed.
Local. This is when the entire database content is located on one computer, usually on the company's server.
Distributed. This is when the database is partially located on different computers - for example, in the cloud.
Modern DBMS may be both local and distributed.
Through data storage and processing
Here DBMS are divided into client-server, file-server, and embedded.
Client-server. This is when the DBMS and the database are located on the same computer, to which users make queries. Such databases may be accessed from any computer.
Many online stores start just such a database so that users can quickly get up-to-date information about their products.
File-Server. This is when the database is located on a single server, and DBMS is on the devices from which queries are sent to the database. The user must have the DBMS installed and configured to retrieve the data.
File-server DBMSs are used for local services of companies - for example, CRM systems, where data on clients and document flow are located.
Embedded. These are local DBMSs, separate modules for managing data inside applications. They are usually written as libraries for different programming languages.
By query language
DBMS supports structured query language and unstructured.
SQL. This is a language for creating structured queries for databases. Such DBMSs are the most popular to use.
NoSQL. This is a language for queries that are based on another programming language - such as Python or JavaScript. DBMS with NoSQL is usually used when working with big data.
By structure and organization of data
And the last property by which DBMSs are divided is how they present information within databases.
Relational. Data in the form of tables are connected to each other through end-to-end parameters. This architecture provides line-by-line data storage and is needed to create a rigorous structure. One more peculiarity: per one access to the database user can get only a small number of elements.
Each line has a unique identifier or key. Therefore, finding the necessary data and linking them together in such a database is easy.
Relational databases are used when the volume of data does not exceed several terabytes. This makes them suitable for almost any project.
PostgreSQL, Microsoft SQL Server, MySQL, and Oracle are the most popular relational databases.
Relational databases are used when the following characteristics are important:
• Transactivity. To perform a transaction, you need to make several queries to the database. This can be, for example, a transfer of money from one card to another through a bank application.
• Frequent data changes. Since relational databases have a strict structure.
• Search by indexes. Each item in such a database is a string, and searching for items by strings is convenient.
• Queries a small number of records at a time. Relational databases work just on this principle.
• The volume of data does not exceed several terabytes. If it is larger, such a database will be slower.
The key is value. Each database item uses a unique identifier for storage, which consists of a key and a value. This method of storage is similar to the "dictionary" data type in programming languages.
Key-value databases look like hash tables, where each record has only one index. In this case, there are no strict restrictions on the structure of elements.
Such databases are used according to the principle of logging when new entries are put at the end of the repository. Items are deleted by adding a special record.
A few more ways to use key-value databases are queues, cache, and logging.
Popular key-value databases are Redis, DynamoDB, and Aerospike.
Document databases. Unlike relational databases, document databases store information as "documents" rather than tables and rows. The main advantage of document databases is the ability to store information without strict structure limitations.
Document databases know how to store information in its entirety and retrieve it with a single query. Even if the structure of elements is different, document databases also allow you to use links to other records in the database, something reminiscent of the relational model.
Popular document databases are MongoDB, CouchDB, and Amazon DocumentDB.
Graph databases. In these, elements have interrelationships in a graph, where each node has many links with other nodes. This kind of database resembles a spider's web. Usually, these databases are used in social networks or recommendation services.
Each node of a graph database contains data, and they use edges to make relationships between nodes. They store the start node, end node, direction, and type. The edges describe the relationships between two nodes - for example, parent-child - and the actions on them.
You can traverse the entire graph quickly because the relationships between nodes are not counted during the query but are stored immediately in the database. This is why graph databases are used in social networks, recommendation services, and fraud detection systems when you need to build relationships between data and query it.
Popular graph databases are Amazon Neptune, Neo4j, and InfiniteGraph.
Columnar. These store data sequentially as a single column and assume that each column holds items that relate to the same row at the same location. This allows the data to be efficiently compressed and analyzed - to find the sum, quantity, or average value.
Popular DBMS
There are many DBMSs, but programmers often use the seven most popular - PostgreSQL, Microsoft SQL Server, MySQL, SQLite, MongoDB, Redis, and Oracle.
PostgreSQL
Type. Relational DBMS of client-server type.
Cost. Free.
License. PostgreSQL License, Open Source.
For whom. Suitable for projects of any complexity and volume.
Benefits. DBMS has great functionality and high performance - for example, it can work with large data under high load without any problems.
The query language is SQL, but it can be changed through extensions to PL/Python, PL/Java, and PL/Perl. And another advantage of PostgreSQL - it has no limit on the size of databases and the number of records in the tables.
MySQL
Type. Relational DBMS client-server type.
Cost. Free.
License. GPLv2, proprietary.
For whom. Suitable for small and mid-sized teams and projects.
Advantages. The program interface allows you to work with tables of different formats. MySQL works online and can hold up to 50 million items. But in terms of functionality, it is inferior to PostgreSQL. However, it can be integrated with other databases.
Companies such as Twitter, Alibaba, Facebook*, and Wikipedia used MySQL for websites and online stores.
After MySQL was bought by Oracle, users were a little worried that soon the database could become a fee. But for now, it remains free.
Microsoft SQL Server
Type. Relational DBMS.
Cost. Free.
License. Proprietary.
For whom. The paid version is used in large companies, while the free version can be easily used in projects with up to 10 GB of data.
Benefits. This DBMS adds automation of tasks - for example, you can specify a script that will manage the memory. Microsoft SQL Server also allows you to easily store complex data structures and quickly search for them.
The DBMS is compatible with other Microsoft programs - for example, Excel and Access. You can integrate with them and download data from there, as well as modify them online.
Microsoft SQL Server uses the SQL language as a query language.
SQLite
Type. A single-file DBMS in the form of a library for programming languages.
Cost. Free.
License. GPLv2, proprietary.
For whom. Suitable for small projects.
Advantages. SQLite is a very compact DBMS that uses no servers or other utilities. All data is stored on a single device.
You can write a simple website or application on SQLite, which will have limited traffic and amount of data stored. The DBMS works on any device-smartphones, computers, televisions, and others-where the library can be downloaded. It needs no administration, and its query language is C.
MongoDB
Type. Document DBMS with NoSQL management in which data is stored as JSON files.
Cost. Free.
License. Server Side Public License.
For whom. Suitable for those who need to work with large amounts of data from multiple sources.
Benefits. The main feature of this DBMS is that data is presented as text documents that are written in JSON format.
Instead of tables, the data is presented in the form of collections - groups of documents. It is optimized for distributed work but also supports local data storage.
MongoDB is used by companies such as Facebook, Google, Twitter, Forbes, IBM, and many online stores.
Redis
Type. Key-value database type.
Cost. Free.
License. BSD, Open Source.
Who for? Redis is used for travel sites, forums, and social networks. It is also suitable for SaaS applications and e-commerce services.
Benefits. Redis can be used in the cloud - fully operational and optimally configured. It's easy to scale and manage.
Redis can migrate data from another database using an automated service.
Oracle Database
Type. Object-relational database.
Cost. Free.
License. Proprietary.
For whom. For projects of any complexity.
Benefits. Oracle DB works as a client-server. This means that it resides on the server along with the database. Therefore, to work with it, you need a special application-client interface. The user manages to send and receiving data from the service.
Oracle DB provides high security and easy access for users. It also reduces the load on client computers. However, the server for the DBMS must be more powerful.
