Structure of DBMS: Users and Interfaces with Diagram
Structure of DBMS (Database Management System): Users and Interfaces with Diagram: A DBMS – Database management system is a complex software system that stores a huge amount of data and handles multiple requests (insert, delete, update and retrieve) from users. Internally, various components constitute DBMS and each component is known to perform the unique functionality.
Structure of DBMS: Users and Interfaces with Diagram
In this section, we will discuss all the structure of DBMS and the interaction between them.
Fig. Structure of DBMS
The above figure illustrates the typical DBMS structure. It shows different users of the database environment along with their interfaces. Also it includes the modules of DBMS needed for data storage and processing of transactions.
DBA (Database administrator) – Considering the top part, it shows interfaces of DBA Staff (causal workers) who work with interfaces to formulate the queries. They also work on creating or defining the database structure and handling it by making required changes to its definition using DDL and privileged commands.
DDL Compiler – It processes schema definitions mentioned in DDL and stores the description of schema in the DBMS catalog.
Interactive query – This interface is used when the users need information from the database modules.
Query Compiler – The queries entered into the interactive query interface are parsed, checked for syntax correctness according to SQL configuration which is then compiled into internal form via the Query compiler.
Query Optimizer – The internal query is then passed through query optimizer which performs multiple functions like
- Rearrangement and possible reordering of operations and elimination of redundancies.
- During the execution, query optimizer utilizes efficient search algorithms.
- It checks from the catalog if any statistical or physical information about stored data is needed, and then generates the executable code which performs required operations on the query and also makes calls to runtime processor.
- Also helps in reducing response time.
Application Programmers – These are the programmers who write and submit application programs in host languages like Java, C++, or C to precompiler. Application programs are used to create, update or delete records or even for designing the interface.
Precompiler – It extracts DML statements or commands from the application program submitted in the previous step which is then sent to the DML compiler.
DML compiler – It compiles the DML commands such as update, delete, etc into object code for database access and the remaining program is sent to the host language compiler.
Host compiler – Like DML compiler, it also results in compiled transactions. Compiled transactions low-level file access commands that are produced by high-level queries after being compiled by DML and host compiler.
Parametric Users – Nowadays it is too common for programmers to use scripting languages like python to write database programs. Canned transactions are executed frequently by parametric users via mobile apps or computers where they simply provide the parameters to transactions.
Runtime database processor
- It performs various functions like executing privileged commands, query plans, and canned transactions with runtime parameters.
- Handles data transfer and management of buffer in the main memory.
- It also works with the system catalog and updates the statistics if required.
- Also communicates with stored data manager when necessary.
- It consists of a submodule called integrity checker where it checks for all the keys like primary, unique, and foreign key.
System Catalog / Data dictionary – Used to store all the queries. It is a repository of descriptions of data in the database.
Consists of the following information
- Data – names of tables, attributes in each table, length of attributes, and total rows of a table.
- Contains description of all the tables, views, indexes, and triggers.
- Relationship among the tables
- Constraints on the column – data values allowed.
- Stores the usage statistics like the number of times the query or the transaction has run.
- Access authorization – description of database users and their responsibilities and their rights to access.
- Queries are validated according to the SQL configuration and generate errors if any improper statements are found.
- Catalog also includes information like names, datatypes of items, storing details of all items like files, mapping information among the schemas.
Stored data manager – It is one of the main components of DBMS responsible for the below tasks.
- It utilizes operation system services in order to perform the low-level / input-output (say read/write) operations between disk and main memory.
- Maintains consistency and integrity using constraints. To be more specific, it does not allow entering any duplicate row in the database and inserting/updating if data has child entry.
- Converts requests from query optimizer to machine language code and makes actual calls to the database.
Concurrency control / Backup / Recovery systems – These are integrated into the runtime database processor to perform transaction management. They are used to achieve concurrency control and also work as a backup if anything goes wrong during a transaction.
It is also important to know the terms – Client computer, database server, and application server
The client program in general can access the DBMS running on a separate computer or the same computer. In this case, the former is called a client computer running DBMS client software and the latter one as a Database server. In most cases, the client accesses the application server (middle computer) which in turn accesses the database server.
The DBMS has interaction with the operating system under two conditions
- when disk accesses to the database is needed
- when disk accesses to the catalog is needed.
Also See: Explain Database Schema and its Types
If the computer system is shared by multiple users, then the operating system will schedule access requests, DBMS processing, and other tasks. Otherwise, if a computer is mainly working to run a database server, then DBMS will control the main memory buffering of disk pages.