A database management system (DBMS) is a tool we use to create and manage databases. A DBMS requires several components to come together. Firstly, we need data, which is the information stored in a database. Text, numbers, booleans (i.e. true/false statements) and dates typically represent our data. Once we gather an organized collection of data we can refer to it as a database. We can think of the schema as the blueprint of a structured database that explains the type of data stored in a database and how the data is related.
Finally, a database engine is one the most important pieces of software behind a DBMS because it’s responsible for accessing and managing the database.
What Does a Database Management System (DBMS) Do?
A DBMS acts as an interface between users and data stored in a database, thereby allowing users to create, read, update and delete data in a database. A DBMS manages the database engine, policies and schema needed for users to extract any data they may need.
Components of a DBMS
In addition to a database engine, there are several other components and tools normally contained within a DBMS.
- Database engine
- Query language
- Query processor
- Optimization engine
- Metadata catalog
- Monitoring tools
- Quality assurance tools
Database Engine
A database engine is the core of any DBMS. It’s the software that interacts with the data, database users and the query processor.
Query Language
A DBMS requires a database access language in order for users to interact with a database. A query language defines a set of commands and rules that allow a DBMS to know when users want to create databases, as well as insert, update or delete data. The most popular and commonly used database query language is Structured Query Language (SQL). Some database management systems inherit or extend SQL while adding their own features such as PostgreSQL or MySQL.
Query Processor
A query processor uses the established query language to interpret the queries executed by users and converts them into proper, compatible commands for the database. A query processor parses, translates, optimizes and evaluates a query.
Metadata Catalog
In simple terms, a metadata catalog is a collection of all the data about your data. All data stored in a database has some information regarding its lifetime and journey, such as the creation timestamp or the user who inserted the data. All this information is known as metadata and it is stored in the DBMS metadata catalog. A metadata catalog’s main function is providing an overarching view and deeper visibility into all of the data managed by a DBMS.
Monitoring Tools
Monitoring a DBMS helps to optimize performance, protect our databases and reduce downtime. There are a variety of metrics we can monitor in a DBMS such as CPU, memory performance or system downtime. There is a whole market of tools available out there such as SolarWinds, SQL Power Tools and Datadog to do this work within a DBMS.
Quality Assurance Tools
To ensure that our DBMS manages data correctly, it is important to monitor the veracity of the data populating our databases. DBMS software has its own built-in software quality assurance tools for data integrity checks, database repair and data validations.
Advantages of Using a Database Management System
Improved Data Security
A DBMS acts as the middleman between databases and users. The more often we need to access a database, the higher the risk of a security breach. With a DBMS, we can implement security policies that define permissions regarding who can access what and where data is allowed to be stored. A DBMS allows us to have strict control over the data’s journey within our system.
Privacy Compliance
A DBMS provides a framework for compliance with privacy policies, which allows companies to manage privacy and security in a centralized manner. Privacy compliance is all about using data responsibly, whereas data security is simply about protecting all our data from malicious threats. Ultimately data privacy comes down to following regulations to clearly establish what data is protected, how it’s protected, from whom it’s protected and who is responsible for that protection.
Data Integrity
The term data integrity refers to the accuracy and consistency of data. A DBMS imposes policies on its databases that optimize data integrity. One fundamental policy for data integrity is ensuring that correct data types are stored in each column. For example, we don’t want numeric values stored in columns meant for alphabetic data.
Types of Database Management Systems
Databases tend to be relational or non-relational and this is based on the type of data they handle. Relational databases store data in tables whereas non-relational databases store data in JSON documents or key-value pairs. Additionally, graph databases store nodes and their relationships.
Just as we have relational, non-relational and graph databases, in the same way, we can have relational database management systems (RDBMS), document database management systems (DoDBMS) or graph database management systems.
Relational Database Management Systems (RDBMS)
We use relational databases to store structured data, which is based on tables that contain rows and columns. Data is stored in rows that contain a unique ID known as the primary key. We can then link multiple tables with each other based on common values such as product ID or customer name.
We use an RDBMS to manage relational databases — sometimes called SQL databases. This is because we use SQL to interact with relational database management systems. Some popular RDBMSes include MySQL and Microsoft SQL.
It is worth noting that many people may refer to MySQL as a database when, in reality, it is a DBMS.
Another popular example of an RDBMS is a data warehouse. A data warehouse is a relational database management system used to manage databases that store data in columns rather than rows. Columnar databases are a good choice for data analytics. Some examples of cloud-based data warehouses that used columnar data are BigQuery from Google Cloud Platform or Redshift from Amazon Web Services.
Document Database Management Systems (DoDBMS)
Non-relational databases normally contain JSON-like structures that we consider objects or documents. Unlike relational databases, data in non-relational databases have no set structure or relationship and are powered by services such as MongoDB (one of the most popular NoSQL platforms) or DynamoDB.
We use DoDBMSes to manage non-relational databases, which rely on NoSQL languages to interact with its databases. For this reason, we sometimes call non-relational databases NoSQL databases.
Graph Database Management Systems
A graph database management system provides specific support for the deployment and maintenance of graph databases. A graph database is used to store data in the form of nodes and connections. This is a great option to navigate deep hierarchies and to find hidden connections between items of a network. We can use graph databases to model transport networks, recommendation engines and fraud detection systems.
Some popular choices of graph database management systems are Neo4j, ArangoDB, GraphQL and OrientDB.
