On a general note, a Database Management System (DBMS) is known to us as a structured means for data storage and retrieval. However, if you dive slightly into the internals, you will encounter the fascinating realm of a carefully designed system. Comprising several useful subsystems, it ensures the request and retrieval of data leveraging text-based query languages.
So, how does a query written in some query language return the intended result? Understanding DBMS architecture would be a great starting point in this regard.
Database management systems utilize a client-server model, where the application instance is the client and the database system instance takes the role of the server. The four core subsystems of the whole architecture and the overall flow are mentioned as follows:
1. Transport Subsystem: establishes communication between client and server. Requests come in the form of queries written in a query language.
2. Query Processor Subsystem: The query is handed over to the query processor, which comprises a query parser and query optimizer. The former checks, validates, and interprets the query upon parsing, while the latter creates the most efficient execution path, known as the query plan, possible from the available plans by making use of the parsed query.
3. Execution Engine: Thirdly, the execution engine takes the query plan as input and runs it to request the intended data from the database.
4. Storage Engine: Consisting of several necessary components named lock manager, transaction manager, buffer manager, and recovery manager, the storage engine maintains structured storage for data organization and access on disk via heap files or B-trees. In addition, the buffer manager caches data for faster in-memory data access while disk remains the persistent storage. The transaction and lock manager work as the means for concurrency control, while the system state is restored by the recovery manager from the previously maintained logs in case of system crash or failures.
Once the data is successfully retrieved, it passes through the reverse direction, all the way to the transport subsystem and finally reaching the client.
Such enlightenment on the inner workings of a DBMS has shown that every request and response is not merely some query-based operation but an important indication of a data-specific, informed, engineered system.
