Home

Learning Hub

Articles

Structure of DBMS: Complete Overview with Suitable Examples

When we think about managing data, what worries us most?

Maybe it’s how to store and access large amounts of information quickly. Or maybe it’s keeping everything secure and organised so nothing gets lost or misused.

These are real concerns, and that’s where understanding the structure of DBMS becomes vital.

It is not just a tool; it’s the backbone which bears the load of all data manipulation and makes sure that everything goes perfectly.

But how does it do this? What makes it so effective?

The secret lies in its structure.

The design of the DBMS parallels that of any well-designed building: it separates everything into components, each component having a special purpose, all fitting together in a means of efficiently managing, storing, and retrieving data.

With that said, let’s break this structure down and see how it works practically.

Overview of DBMS Structure: Internal, Conceptual, and External Levels

DBMS structure can be viewed in terms of three levels: Internal, Conceptual, and External.

Consider these three different levels to illustrate the structure as various levels of a building.

These levels work together, each relying on the others to keep the DBMS functioning efficiently. Changes made at one level are automatically reflected across the others, ensuring consistency and accuracy.

Internal Level:

This is the basement, where the physical storage happens.

It deals with how data is actually stored on the disk, using methods like indexing and data compression.

It’s all about making the best use of space and ensuring fast access.

Conceptual Level:

This is the middle floor, where the overall design of the database lives.

Here, we focus on how data is organised and how different pieces relate to one another. This level is independent of any specific DBMS software, making it versatile.

External Level:

The top floor is where users interact with the database. It provides different views for different users, showing them only the data they need to see.

This makes it easier to manage access and keep everything secure.

The complete structure of DBMS can be categorised into three components: Query Processor, Storage Manager, and Disk Storage. Let’s discuss them in detail.

Also Check: What is a Database Management

Detailed Breakdown of the Query Processor Component in DBMS

Now, let’s focus on the Query Processor.

This is the part of the DBMS that handles our requests and makes sure we get the right data.
Imagine asking a librarian for a book.

The librarian checks the catalogue, finds the book, and brings it to you. The Query Processor does something similar but with data.

Query Processor has four components:

• DML Compiler
• DDL Interpreter
• Embedded DML Pre-compiler
• Query Evaluation Engine

How the DML Compiler Translates Data Manipulation Queries

DML stands for Data Manipulation Language, and we use it to insert, update, delete, or retrieve data from the database.

Now, the DML compiler changes these high-level commands into some sort of code the computer will understand; this is called machine code.

• Example:

Assume we want to search for a customer by their e-mail, and we write something like this:

The DML Compiler then translates the data into a series of low-level instructions that fetch it from the database.

Role of the DDL Interpreter in Processing Schema Definitions

DDL stands for Data Definition Language, and it is just another buzzword for the set of instructions that we issue towards the definition of the structure of the database, like creating tables or deleting them.

The DDL Interpreter will process these instructions and upgrade the schema of the database, which is like its blueprint.

• Example:

Suppose we want to create a new table for customer data; then, we would use a DDL command like this:

The DDL Interpreter takes this command, understands it, and adds the new table to the database.

The Function of the Embedded DML Pre-compiler in Query Processing

But what if our DML commands are part of a larger application?

That’s where the Embedded DML Pre-compiler comes in.

It takes DML commands that are embedded within application code and pre-process them so they can run smoothly within the application.

• Example:

In a retail application, we might have a query that updates stock levels after a sale:

The Embedded DML Pre-compiler ensures this query is correctly integrated into the application, so everything works seamlessly.

Importance of the Query Evaluation Engine in DBMS

This is the part that actually executes the query.

After the DML Compiler translates the query into machine code, the Query Evaluation Engine runs it and returns the results.

After processing the earlier SELECT query, the Query Evaluation Engine retrieves the customer’s data and sends it back to the user.

Exploring the Storage Manager Component in DBMS

When handling data, what keeps everything organised, safe, and easily accessible?

That’s the job of the Storage Manager in the structure of DBMS.

It’s like the operations manager in a big warehouse, making sure that every piece of data is stored correctly and retrieved without a hitch.

The Storage Manager is responsible for ensuring that the database runs smoothly. It’s the backbone of the system, managing everything from security to memory allocation.

Storage manager can also be divided into four components:

• Authorisation and Integrity Manager
• Transaction Manager
• File Manager
• Buffer Manager

How the Authorisation and Integrity Manager Secures and Validates Data

First, let’s talk about security and accuracy.

The Authorisation and Integrity Manager is part of the Storage Manager and is responsible for ensuring the data is accessible and only given to duly qualified people.

It checks the user permissions and implements rules so that data remains correct and consistent.

Assume the case of a hospital database:

• The Authorisation Manager will ensure that only doctors can view patient records.
• Integrity Manager ensures that every modification to these records occurs by stringent rules, avoiding accidental or malicious errors.

This layer is crucial in environments where data sensitivity is required, such as in health care, finance, and personal data management.

Transaction Manager: Ensuring Consistent Transactions

By the time we perform changes to the database, adding records or updating some, we would want to be sure everything is kept consistent.

This is managed by the Transaction Manager, ensuring that all transactions are completed fully or not at all.

Think of an online shopping cart, when a customer checks out:

• Transaction Manager ensures that the stock levels are updated, payment is processed, and the order is confirmed.
• If something goes wrong at any step, the Transaction Manager rolls back the entire process so nothing gets stuck in an incomplete state.

This guarantees that the database remains reliable, even if there’s a system crash or other failure during a transaction.

Functions of the File Manager in Managing Database Files

File Manager allocates the space on the disk as well as manages data structures that store the information.

Like how we sort out our Files on our computer in folders, the file manager does this on a larger scale and makes sure that every file is in its right place and in the right folder and also makes it easier for it to be found when needed.

This organisation assists in the quick and efficient accessibility and space allotment for disks, which is very useful in optimising large database performances.

How the Buffer Manager Optimizes Memory Usage and Data Retrieval

Buffer Manager plays an important role in maintaining the transfer of data from disk to main memory.

It determines which data can stay in memory for fast access and which can safely be written back to disk.

When running a report, the Buffer Manager ensures that frequently accessed data is loaded into memory so the report runs quickly. If there’s not enough memory, it smartly swaps out less-used data to make room.

By managing this balance, the Buffer Manager helps reduce the time it takes to access data, improving the overall speed of the DBMS.

So, how is all this data physically stored?

That’s where Disk Storage comes into play. It is the foundation that holds all the data, like the shelves in our warehouse.

Disk storage has four components that we need to understand separately:

• Data Files
• Data Dictionary
• Indices
• Statistical Data

The Role of Data Files in Storing Database Information

At the core of Disk Storage are the Data Files. It holds the actual data, everything from customer records to transaction logs.

The data files play the role of the library bookshelves, which store all the books of data. Each book has its proper allocated space, which can be located at any time it is required.

The files are well organised so that data can be retrieved quickly and efficiently.

Importance of the Data Dictionary for Metadata Management

But how do we keep track of where everything is and what it means?

That’s the job of the Data Dictionary. It stores metadata, which is data about data.

It keeps track of the structure of the database, including table definitions, data types, and relationships between tables.

As a comparison, in a library, it would be like the card catalogue that gives us the details on where each book is and what it is about.

The information in the data dictionary is essential to the DBMS in order to understand the organisation and structure of the data maintained.

How Indices Improve Data Retrieval Efficiency

To make finding data faster, we use Indices.

It is like an index in a book. An index points directly to the location of the data.

This is especially useful when trying to find, say, customer ID; an index will aid the DBMS in scanning for that particular customer’s data without having to scan through the whole database.

This makes the process of searching faster, and it retrieves data much quicker.

This is especially useful in large databases where it would take ages to look at each record.

Use of Statistical Data for Optimising Query Execution

Finally, Statistical Data helps in optimising how queries are executed.

The DBMS collects stats on how data is used, like which queries are most common and which data is accessed the most.

Let’s say we frequently run a report on sales data. The DBMS can use statistics to optimise how it fetches this data, ensuring that the report runs as quickly as possible.

This use of statistical data is another way the DBMS improves efficiency, making sure that we get the data we need when we need it.

Interaction Between Different DBMS Components for Seamless Operation

Now that we’ve explored the different components, how do they work together? The answer lies in their seamless interaction.

Each component of the structure of DBMS relies on the others to keep the system running smoothly.

How Query Processor and Storage Manager Work Together

The Query Processor and Storage Manager are like partners in a relay race. When the Query Processor receives a request, it passes the baton to the Storage Manager, which retrieves the necessary data.

This teamwork ensures that data is retrieved and processed efficiently, with minimal delay.

When we run a query to find all customers in a specific city, the Query Processor translates that request, and the Storage Manager fetches the relevant records from the database.

Coordination Between Storage Manager and Disk Storage Components

The Storage Manager also coordinates closely with Disk Storage components like Data Files and the Buffer Manager.

This coordination ensures that data is stored correctly and can be retrieved quickly when needed.

If we update a record, the Storage Manager ensures that the new data is written in the correct data file and that any necessary indices are updated. The Buffer Manager might also keep the updated data in memory for faster access.

This level of coordination is what makes the DBMS a powerful tool for managing complex data systems.

Real-World Examples Illustrating the Practical Application of DBMS Structure

Let’s bring the Structure of DBMS to life with some real-world examples.

Example 1: Processing DDL and DML Statements in a Retail Database System

Suppose you run an online store. Your database tracks all of the products, your customers, and orders. One day, you want to add a new product to your inventory.

That’s where DDL (Data Definition Language) and DML (Data Manipulation Language) come in.

• Adding a Product:

You’d use a DDL command to create a new table or alter an existing one if needed. Then, a DML statement inserts the product details into the database:

• Updating Stock Levels:

When a customer buys the mouse, a DML command updates the stock:

Example 2: The Buffer Manager’s Role in a Financial Transaction System

Banks handle thousands of transactions every second, from deposits to withdrawals. Speed and accuracy are crucial here, and the buffer manager plays a key role.

When processing a withdrawal, the Buffer Manager ensures that account balances are quickly fetched and updated. It keeps frequently accessed data in memory, reducing the time it takes to complete each transaction.

If a customer withdraws £100, the Buffer Manager ensures the account balance is updated immediately, reflecting the new balance.

This reduces delays and helps the bank process transactions in real time.

Example 3: The Data Dictionary in an Educational Institution’s Database

The database in a university manages student records, course details, and grades. The data dictionary is essential for keeping everything organised.

Data Dictionary will record all tables, including but not limited to student, course, and grade.

It defines how these tables are related to each other so that the linking is proper.

Upon adding a new student, the system refers back to the Data Dictionary for the authenticity of the student’s ID and attachment to courses assigned.

That way, no mistakes can occur, and it is guaranteed that all records will be accurate and up to date.

Conclusion

The structure of DBMS involves more than just managing data; rather, it must be efficient, secure, and reliable.

It provides smooth integration among the Query Processor, Storage Manager, and Disk Storage to handle data correctly in an efficient, secure, and reliable manner.

From query processing and transaction management down to storage optimisation and retrieval, everything in the DBMS is crucial to how well our data systems function.

Knowing the structure of DBMS, from retail to banking and education, enables us to take care of high-performance systems that can meet the demanding requirements of today’s data-driven world.

It is a structure that provides the guarantee that data would not simply be stored but managed in such a way that it would allow accuracy, speed, and access.