How to design a Database Management System

The important components of a DBMS explained

5 min readSep 19, 2023

How can you design a database management system (DBMS)?

And what are the components required to make a DBMS function?

What even is a DBMS? Does Excel Count as a DBMS?

I have no doubt that in a world racked with climate change, inflation, and mass layoffs, these are the burning questions that keep you up at night. The truly important questions in life, that we must all think about at all times.

Worry not, I could hear you crying out for answers. So I have arrived, and we will be covering this very topic today.

Image Source

Join 150K+ tech leaders and get insights on the most important ideas in AI straight to your inbox through my free newsletter- AI Made Simple

Database Management System Essentials

What is a Database Management System- A DBMS is a tool to interact with your data. In technical words, it provides an abstraction that you can use to store and retrieve data. This way, you don’t directly have to deal with your databases.
How a Database Management System works- You can use a Query Language to interact with the DBMS. DBMSs also provide guarantees for performance.
Guarantees provided by a Database Management System- A DBMS can provide guarantees like -

durability -you won’t lose your data if the database server crashes),
strong consistency (for distributed databases — once you write some data to the database, all subsequent reads will return that value and you won’t get “stale” data)
read/write speeds.

Look over the post on the CAP Theorem and tradeoffs for more. Depending on your needs, you would choose different systems.

As mentioned, there is a lot of variation in the different DBMSs. However, there are some recurring motifs and general principles in their design that we can build up from. The rest of this post will be going into more detail on this topic.

Database management systems can serve different purposes: some are used primarily for temporary hot data, some serve as a long-lived cold storage, some allow complex analytical queries, some only allow accessing values by the key, some are optimized to store time-series data, and some store large blobs efficiently.
-Database Internals by Alex Petrov

The Operation of a DBMS

Database Management Systems use a client/server model. To learn more about this, check out the following post. Your app is the client and the DBMS acts as the server.

At the top, we get the Transport system. It is used by the DBMS to accept client requests (queries). The transport subsystem hands the query over to a query processor, which parses, interprets, and validates it. Once our DBMS understands the query, access control checks are performed (these can only be done after the query is interpreted).

The first part of the Query Processor is the Query Parser. It parses the client’s query to ensure that the query is valid. After, the parsed query is passed to the Query Optimizer. This will just find the most efficient way to handle queries. Read the following para to understand how it works-

The parsed query is passed to the query optimizer, which first eliminates impossible and redundant parts of the query, and then attempts to find the most efficient way to execute it based on internal statistics (index cardinality, approximate intersection size, etc.) and data placement (which nodes in the cluster hold the data and the costs associated with its transfer). The optimizer handles both relational operations required for query resolution, usually presented as a dependency tree, and optimizations, such as index ordering, cardinality estimation, and choosing access methods.
-Petrov

The Optimizer creates an Execution Plan that gets passed on to the Execution Engine. The execution can be either Remote (data is stored on a different machine) or Local(data that is stored locally).

The storage engine has several components with dedicated responsibilities:

Transaction manager- Schedules transactions and ensures they cannot leave the database in a logically inconsistent state.
Lock manager- Locks on the database objects for the running transactions, ensuring that concurrent operations do not violate physical data integrity.
Access methods (storage structures)- These manage access and organize data on disk. Access methods include heap files and storage structures such as B-Trees (see “Ubiquitous B-Trees”) or LSM Trees (see “LSM Trees”).
Buffer manager- This manager caches data pages in memory (see “Buffer Management”).
Recovery manager- This manager maintains the operation log and restoring the system state in case of a failure (see “Recovery”).

Together, transaction and lock managers are responsible for concurrency control (see “Concurrency Control”): they guarantee logical and physical data integrity while ensuring that concurrent operations are executed as efficiently as possible.

To anyone interested in learning how to design a database, I would suggest the following video by FreeCodeCamp. It’s a bit long, but you can jump around the timestamps to focus on whatever you want.

That is it for this piece. I appreciate your time. As always, if you’re interested in working with me or checking out my other work, my links will be at the end of this email/post. If you like my writing, I would really appreciate an anonymous testimonial. You can drop it here. And if you found value in this write-up, I would appreciate you sharing it with more people. It is word-of-mouth referrals like yours that help me grow.

Save the time, energy, and money you would burn by going through all those videos, courses, products, and ‘coaches’ and easily find all your needs met in one place at ‘Tech Made Simple’! Stay ahead of the curve in AI, software engineering, and the tech industry with expert insights, tips, and resources. 20% off for new subscribers by clicking this link. Subscribe now and simplify your tech journey!