What is DBMS? (Database Management System)

A Database Management System (DBMS) is a complex software that is used to store, retrieve, and manage data. It is an interface between the raw data and the user or application, providing access to storage and enabling multiple users to interact with the data. Rather than constantly accessing data records one by one, a DBMS handles these tasks automatically, including data manipulations, storage, queries, and reports. It accepts large data, allows multiple users to access and update the stored data, and maintains logic and rules to keep data integrity. Here, in this article, you will learn what is DBMS, its types, and its functionality in detail.  

Table of Contents:

• What is DBMS?
• Why Do We Need to Use DBMS?
• Types of Databases in DBMS
• Understanding DBMS Architecture (3-Tier Model)
• Different Types of DBMS Architectures
• DBMS Architecture Comparison Table (Centralized vs Distributed vs Cloud)
• Database Models in DBMS
• SQL Commands in DBMS (DDL, DML, DQL, TCL)
• Types of Keys in DBMS
• What are ACID Properties in DBMS?
• Best Practices
• Popular DBMS Software in 2025
• Real-World Applications of DBMS
• Best DBMS Software Compared for Different Use-Cases
• Conclusion

What is DBMS?

DBMS is abbreviated as Database Management System. It is software that helps us insert, update, fetch, delete, and modify the data in a structured table. It is used to secure your data and maintain the integrity of the data. It has access control management, which prevents unauthorized access. Concurrency control helps multiple users access and modify the data. It supports backup and recovery mechanisms to prevent data loss.

Why Do We Need to Use DBMS?

A DBMS helps to remove data redundancy. It will store your data efficiently without any duplicate values. By removing the duplicates, it will maintain the consistency of the data in many applications. Duplicate data will reduce the performance of the table while querying and also break the structure of the data. Scalability allows it to handle large datasets efficiently.

Types of Databases in DBMS

Databases are classified based on their structure and the way they store and manage data. 

There are 4 types of databases:

1. Relational Databases (RDBMS) 
2. NoSQL Databases
3. Hierarchical Databases
4. Network Databases

1. Relational Database (RDBMS)

The Relational Database Management System (RDBMS) is a database model that organizes data into a structured format using rows and columns. A row represents records, and a column represents attributes of that record. Using primary keys and foreign keys, the relationship between rows and columns will be established. The primary key will identify the unique record. A foreign key will have a reference to the primary key in another table. 

Syntax:

CREATE TABLE TableName (
    Column1 DataType CONSTRAINTS,
    Column2 DataType CONSTRAINTS,
    PRIMARY KEY (PrimaryKeyColumn),
    FOREIGN KEY (ForeignKeyColumn) REFERENCES OtherTable(PrimaryKeyColumn)
);
INSERT INTO TableName (Column1, Column2, Column3)
VALUES (Value1, Value2, Value3);

To update data:

UPDATE TableName
SET Column1 = NewValue
WHERE Condition;
--To delete data:
DELETE FROM TableName
WHERE Condition;

The RDBMS always depends on SQL – Structured Query Language to help in querying and managing data. It supports the ACID properties of the database, which ensure data reliability. The security in RDBMS has been enhanced with the help of the authentication system and role-based access control. 

RDBMS has servers like MySQL, PostgreSQL, Oracle Database, and Microsoft SQL Server.

2. NoSQL Database

The NoSQL database is used to handle large and unstructured data. This does not follow the schema rule like RDBMS. It has a very flexible schema, which allows users to modify the data easily. NoSQL databases are designed for horizontal scaling, making them ideal for distributed systems. NoSQL has servers like Redis, DynamoDB, MongoDB, Cassandra, and graph databases like ArangoDB. These databases have high performance and real-time analysis as they deal with graphics and large data types.

Redis:

# Store a key-value pair
SET key value            
# Retrieve the value of a key 
GET key                
# Delete a key  
DEL key      
# Check if a key exists      
EXISTS key           

MongoDB:

// Creating a document  
db.CollectionName.insertOne({ field1: "value1", field2: "value2" });
// Retrieve the documents  
db.CollectionName.find({ field1: "value1" });
// Update a document  
db.CollectionName.updateOne(
  { field1: "value1" },
  { $set: { field2: "newValue" } }
);
// Delete a document  
db.CollectionName.deleteOne({ field1: "value1" });

Cassandra:

-- Creating a keyspace  
CREATE KEYSPACE KeyspaceName  
WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 3};
-- Creating a table  
CREATE TABLE TableName (
    id UUID PRIMARY KEY,
    column1 TEXT,
    column2 INT
);
-- Inserting data  
INSERT INTO TableName (id, column1, column2) VALUES (uuid(), 'value1', 123);
-- Retrieving data  
SELECT * FROM TableName WHERE column1 = 'value1';

3. Hierarchical Database

A Hierarchical Database is used to organize data in a tree-like structure that has a single parent with multiple child records. The parent-child relationship makes sure that the data is defined correctly with the parent data. The hierarchical database is widely used in an organization where the application requires strict data monitoring. The fetching or retrieval of data will be faster in a hierarchical database as the relationship has been pre-defined. By using the parent node, you can easily find the child node.

4. Network Database

The network database is an extended version of the hierarchical database that allows many-to-many relationships between the records. But the difference is that hierarchy can have the condition that a child should only have one parent. However, in a network database, a child can have multiple parents. It can be used with complex data. Network databases use records and sets to represent relationships. The multiple nodes help in retrieving data easily. 

Understanding DBMS Architecture (3-Tier Model)

Database Management System architecture defines how the data has to be structured, where to store it, and who should access that data in a database.

Layers of DBMS Architecture 

There is a three-tier DBMS Architecture, which is used to improve system scalability, flexibility, and readability. 

1. Presentation Tier – Client Layer

This is the front end of the application where the users will interact with the system, like mobile apps or web browsers. This layer is responsible for displaying information and collecting input from the user. 

2. Application Tier – Business Logic Layer

This is a service layer that processes the business rules and logic of the application. This layer handles the communication between the client and the database, which reduces the client’s need to deal with the database directly. It provides security, management, and processing of the client’s request.

3. Data Tier – Database Layer

The Data tier is the layer where all the actual data is stored and managed in the database. This layer of architecture is responsible for handling the storage, security, updating, and retrieval of data. The database can be a relational database like MySQL or PostgreSQL, or it can be NoSQL like MongoDB. 

Advantages of Three-Tier Architecture

• Each layer in the database can be modified or upgraded independently, which will not affect the entire system. It also improves security. The application layer will act as a firewall in the system. 
• It prevents unauthorized access. The architecture supports flexibility, which allows multiple clients to interact simultaneously. 

Different Types of DBMS Architectures

1. Centralized DBMS Architecture

The Centralized DBMS Architecture is a system where all the data is stored and managed in a single server. It follows the approach where all clients (workstations or terminals) will connect to the central database server to perform the operations. This model ensures data consistency and integrity. There will be no duplicate data across multiple servers. 

In short, in this database, all client applications will run on a single central server.

Advantages:

• No data duplication across the system ensures consistency. This ensures data integrity. 
• All the data will be stored safely, so backup and recovery of data are easier. 
• It doesn’t need multiple database instances, as it can handle everything on a single database server.

Disadvantages:

• As all the users operate on a single server, if the central server fails, then all the users will lose access. 
• If there is a huge number of users accessing the database, then the performance may degrade.

2. Distributed DBMS Architecture

A Distributed Database Management System (DDBMS) is used to store data in multiple servers at different geographical locations. It improves fault tolerance, availability, and performance. Unlike a centralized DBMS, the data will be stored across multiple locations, so even if one server crashes, the users will be able to access the data with the help of other servers.

There are two types of Distributed DBMS:

• Homogeneous DDBMS: All the database instances will use the same software. 
• Heterogeneous DDBMS: Different database instances use different databases, and types interact. 

Advantages of DDBMS Architecture:

• It uses multiple servers, so if one server fails, another server will help to operate. 
• It has different servers for different geological locations, so the users will be able to process the queries closer to their locations.
• Adding new servers will be easier in DDBMS. 

Disadvantages of DDBMS Architecture:

•  It will become complex if they handle multiple transactions in a distributed system. 
• Due to data stored at different locations, the synchronization has to be handled properly. 
• Maintaining the database requires more resources and infrastructure. 

3. Cloud DBMS Architecture

The Cloud DBMS Architecture is a cloud-based platform, like AWS, Google Cloud, or Azure platform, that allows the user to access the platform via the internet. Data is stored on remote servers managed by cloud providers, which are managed by cloud providers. The cloud platform works on a pay-as-you-use system, where the user needs to pay for the data storage they are using.

Advantages of Cloud-based DBMS Architecture:

• It is very cost-effective as it doesn’t need to maintain physical servers. 
• It can easily handle the traffic on the server. 
• The database will be managed by the providers, so if there is an update, the providers will do the work.
• It has global access, so users can easily access the data from anywhere. 

Disadvantages of Cloud-based DBMS Architecture:

• The users need to depend on the internet speed and cloud providers for faster processing. 
• Potential security risks exist, as data resides on third-party infrastructure.
• All the sensitive data should have proper regulatory approval before uploading to the server to maintain integrity. 

DBMS Architecture Comparison Table (Centralized vs Distributed vs Cloud)

Database Models in DBMS

A Database Model will determine how the data is stored and who can access the data within the database system. There are several types of database models, which have unique requirements and specifications for managing the data. The database models are the hierarchical model, the object-oriented model, the network model, the NoSQL model, and the relational model. 

1. Relational Model

The Relational Model is the commonly used database model. It arranges data into tables with rows and columns containing values. Each table represents an entity, and relationships between entities are defined using primary and foreign keys. The Relational model follows the ACID properties of the database to ensure safe transactions. 

Let’s see an example:
This is a student table with rows and column values inserted. 

Explanation: Here, Course_ID is the primary key, and the foreign key is Student_Name. 

Advantages of the Relational Model:

• All data is formatted in a table format, so it is easy to manage. 
• It ensures data consistency through relationships between the attributes. 
• SQL supports complex queries.

Usages:

1. It can be used in banking systems.
2.  e-commerce platforms. 

2. Hierarchical Model

The hierarchical Model is used to organize the data in a tree-like structure where each record has one parent node and multiple children. The relationship is built like a parent-child relationship. These relationships will have hierarchical structures. 

Example: To organize files and the directory. 

Explanation: The Root directory is the parent node, and it has the child nodes that are files, users, and windows. 

Advantages of the hierarchical model:

• It can be used for hierarchical data like organizations and file systems. 
• The data can be fetched easily as they have a predefined data structure. 

Disadvantages of the hierarchical model:

• Modifying the tree requires restructuring and may be complex due to rigid parent-child dependencies.
• It may have duplicate data or repetitive parent-child relationships. 

Usages: 

1. Can be used in File system management.
2. In manufacturing or stock management.
3. XML-based data storage. 

3. Network Model

The Network model is an extended model of the hierarchical model by allowing the user to create multiple parent-child relationships. The child can have multiple parents. It can be represented as graphs. It is more flexible than the hierarchical model as it follows many-to-many relationships. 

Example: University database where students select more than one course. 

In a university, the student will enroll in more than one course, and the course may have multiple professors. 

Explanation: Entities such as Student, Course, and Professor are interrelated with many-to-many connections.

Advantages of Network Model: 

• It is more flexible than the hierarchical model.
• Users can retrieve data easily for complex queries. 

Disadvantages of the Network Model:

• It is difficult to maintain as it has a complex structure. 
• It doesn’t need SQL language. Some network models have their own query language.
• IDMS (Integrated Database Management System) is an example of a system that uses the network model.

Usages:

1. Telephone networks
2. Airline system

4. Object-Oriented Model

The Object-Oriented Database Model (OODBMS) uses object-oriented programming principles (OOP) concepts in the DBMS. It stores the data as objects and classes instead of tables like Python, Java, and C++ programming languages. Objects encapsulate both data (attributes) and behavior (methods), similar to objects in OOP languages.

Example of Object-Oriented Model: In the library, they use the object-oriented programming concept where the books will have attributes like title, author, etc. 

class Book {
   String title;
   String author;
   int ISBN;
   void borrowBook() 
   void returnBook() 
}

Explanation: Here, the object is a book and attributes like title, author, and ISBN. This method will return the book and the borrowed book. 

Advantages of the Object-Oriented Model:

• This method is for the user who wants to use OOP principles in their applications.
• It can support complex data types like multimedia, images, and geographic data. 
• It will improve performance by reducing the transaction time between the database and the application structure.
  

Disadvantages of the Object-Oriented Model:

• While less common than relational models, OODBMS is valuable for applications requiring complex data representation.
• Standard query language (SQL) cannot be used.

Usages:

1. CAD can be used for Computer graphics design.
2. For storing images and videos, it can support multimedia databases.

5. NoSQL Model

The NoSQL (Not Only SQL) model is used to handle large datasets, unstructured data, or semi-structured data. It is not like a relational model. The NoSQL databases will not use a table structure for data. It optimizes the data for high performance and flexibility. It has four types. 

1. Key-Value storage: It will store data by keys and values. Can be used in Redis or Amazon DynamoDB.  
2. Document Stores: It will store all the data in JSON or BSON format in Apache or Cassandra.
3. Graph Database: The graphs or images will be optimized using Neo4j and Amazon Neptune.
   

Example: To store e-commerce products in MongoDB.

{
   "key": "value",
   "key": "value",
   "key": {
       "nested_key": "value",
       "nested_key": "value"
   }
}

Explanation: In NoSQL, the data will be stored as a JSON document instead of a table with rows and columns. Keys are represented inside the double quotes (“”). Values can be strings, NULL, booleans, objects, and arrays. Nested objects are represented by curly braces {} within another object. 

Advantages of the NoSQL Model:

• It can handle large datasets well and can be used for real-time applications. 
• It has flexibility and allows users to create dynamic structures.
• It follows BASE (Basically Available, soft state, Eventually consistent). 

Disadvantages of the NoSQL Model:

• Some NoSQL databases will not follow ACID compliance. 
• Its schema-less nature allows developers to design flexible and dynamic data structures. 
• It may be less suitable for complex transactional queries requiring joins or strict consistency.

Usages: 

• It can be used in social media applications like Facebook, X, and Instagram.
• It can be used in big data analytics.
• It can be used in IoT applications like smartwatches and air conditioners. 

SQL Commands in DBMS (DDL, DML, DQL, TCL)

SQL is abbreviated as Structured Query Language, which is categorized into different types of commands based on their functionality. 

1. DDL – Data Definition Language.
2. DML – Data Manipulation Language.
3. DQL – Data Query Language.
4. TCL – Transaction Control Language.

1. DDL (Data Definition Language) in SQL

The DDL commands can define, modify, and manage the structure of the database, like tables, schema (rows and columns), and indexes. The DDL commands will directly affect the database structure. 

1. CREATE: 

This query will create a new table with indexes or views. 

Syntax:

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

2. ALTER: 

It will delete or add new columns or rows to the already existing table. 

Syntax for ALTER:

ALTER TABLE table_name ADD column_name datatype;

3. DROP:

The DROP query will remove or delete the table and its indexes permanently.

Syntax for DROP:

DROP COLUMN column_name;

4. TRUNCATE:

The TRUNCATE query will delete all the data inside a table, but the structure of the table will not be changed. 

Syntax for TRUNCATE:

TRUNCATE TABLE table_name;

Example: 

---To create a table
CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100),
    Author VARCHAR(50),
    PublishedYear INT,
    Genre VARCHAR(30)
);
--To alter the table
ALTER TABLE Books ADD Publisher VARCHAR(50);
--To drop a table
ALTER TABLE Books DROP COLUMN Publisher;
--To truncate the table
TRUNCATE TABLE Books;

2. DML (Data Manipulation Language) in SQL

DML stands for Data Manipulation Language in SQL. The commands of DML will manipulate the data within the tables. 

1. INSERT:

The INSERT command will add new records to the table. 

Syntax:

INSERT INTO table_name (column1, column2, ...) 
VALUES (value1, value2, ...);

2. UPDATE: 

The UPDATE command is used to modify the existing record in the table. 

Syntax:

UPDATE table_name 
SET column1 = value1, column2 = value2 
WHERE condition;

3. DELETE:

The DELETE command will remove or delete records from the table, but the structure remains the same. 

Syntax:

DELETE FROM table_name WHERE condition;

Example:

--To insert a value
INSERT INTO Books (BookID, Title, Author, PublishedYear, Genre) 
VALUES (1, 'The Alchemist', 'Paulo Coelho', 1988, 'Fiction');
--To update the table
UPDATE Books 
SET Genre = 'Philosophical Fiction' 
WHERE BookID = 1;
--To delete a record
DELETE FROM Books WHERE BookID = 1;

3. DQL (Data Query Language) in SQL

The DQL – Data Query Language is used to retrieve or fetch the required column or record from the table. It will also query the data based on the user’s condition.  

Syntax:

SELECT * FROM Table_Name;

Example:

SELECT * FROM Books;

Output:

Explanation: Here, the table has been queried with the help of DDL, DML, and DQL commands. 

If there is more than one value in a table and the user wants to retrieve particular data, then the user can use this query:

Syntax to fetch data by condition:

SELECT column1, column2 
FROM table_name 
WHERE condition;

Syntax for getting the values in ascending or descending order:

SELECT column1, column2 
FROM table_name 
ORDER BY column_name ASC|DESC;

4. TCL (Transaction Control Language) in SQL

The TCL – Transaction Control Language is used to maintain transactions in the database. It ensures data consistency and integrity. 

1. COMMIT: 

Using the COMMIT command will make sure that the transactions made are permanent. 

Syntax:

COMMIT;

2. ROLLBACK:

ROLLBACK will help you undo the changes made during the current transaction. 

Syntax:

ROLLBACK;

3. SAVEPOINT:

It will create a checkpoint that saves the data during the transaction. 

Syntax:

SAVEPOINT savepoint_name;

Example :

BEGIN TRANSACTION;
UPDATE Books SET Genre = 'Philosophical Fiction' WHERE BookID = 1;
SAVEPOINT BeforeUpdate;
ROLLBACK TO BeforeUpdate;
COMMIT;

Output:

Explanation: Here, the savepoint saved the changes to the table. The UPDATE command updated the book genre to philosophical Fiction, but the changes were rolled back.  

Types of Keys in DBMS

There are different keys in DBMS to define a table. Keys play an important role in databases. 

1. Primary Key

The Primary Key is a unique identifier that is used in a table. The primary key makes sure that there are no duplicate values in the table, and it will not allow NULL values in the table. A table should have only one primary key with multiple columns. 

Syntax:

CREATE TABLE TableName (
    Column1 DataType PRIMARY KEY,
    Column2 DataType,
    Column3 DataType
);

2. Foreign Key

A foreign key is a column or multiple columns that are responsible for creating a relationship between two tables in a database. A foreign key is a primary key from another table in a database. NULL values and duplicate values are allowed in a Foreign key (if the database is not restricting the null and duplicate values). 

Syntax:

CREATE TABLE TableName (
    Column1 DataType PRIMARY KEY,
    Column2 DataType,
    Column3 DataType,
    ForeignKeyColumn DataType,
    FOREIGN KEY (ForeignKeyColumn) REFERENCES OtherTableName(PrimaryKeyColumn)
);

3. Candidate Key

A Candidate key is a column or set of columns that will help to identify each row uniquely. A single Primary key will have multiple candidate keys as it represents the column name in a table.

4. Composite Key

A composite key is a primary key that consists of more than one column in a table. A composite key is used when the user cannot identify the primary key (unique identifier) in a table. 

Syntax: 

CREATE TABLE TableName (
    Column1 DataType,
    Column2 DataType,
    Column3 DataType,
    PRIMARY KEY (Column1, Column2)
);

5. Super Key

A super key is any set of columns that helps to identify the rows in a table uniquely. The super key may have extra columns that are not necessary in a table for uniqueness.

Syntax:

CREATE TABLE TableName (
    Column1 DataType PRIMARY KEY,
    Column2 DataType UNIQUE
);

Example:

-- Creating the Patients table with Primary Key
CREATE TABLE Patients (
    PatientID INT PRIMARY KEY, 
    Name VARCHAR(100) NOT NULL,
    DOB DATE,
    Phone VARCHAR(15) UNIQUE,  
    Address VARCHAR(255)
);
-- Creating the Doctors table with Primary Key and Candidate Key
CREATE TABLE Doctors (
    DoctorID INT PRIMARY KEY,  
    Name VARCHAR(100) NOT NULL,
    Specialization VARCHAR(50),
    LicenseNumber VARCHAR(20) UNIQUE  
);
-- Creating the Appointments table with Composite Key and Foreign Keys
CREATE TABLE Appointments (
    PatientID INT,
    DoctorID INT,
    AppointmentDate DATE,
    Notes TEXT,
    PRIMARY KEY (PatientID, DoctorID, AppointmentDate),  
    FOREIGN KEY (PatientID) REFERENCES Patients(PatientID),
    FOREIGN KEY (DoctorID) REFERENCES Doctors(DoctorID)  
);
-- Creating the Bills table with Primary Key, Foreign Key, and Super Key
CREATE TABLE Bills (
    BillID INT PRIMARY KEY,  
    PatientID INT,
    Amount DECIMAL(10,2),
    BillingDate DATE,
    PaymentStatus VARCHAR(20),
    CONSTRAINT FK_Patient FOREIGN KEY (PatientID) REFERENCES Patients(PatientID) 
);

Learn what a secondary key is in DBMS and how it is used in this blog.

What are ACID Properties in DBMS?

ACID properties in DBMS are used in database transactions. A transaction is a sequence of operations performed as a single logical unit of work on a database. This will edit or modify the data using read and write access. While modifying the content, there may be a chance that there will be some compromise in the consistency and integrity of the data. To prevent this, DBMS follows the ACID properties. 

1. Atomicity 

In ACID properties, A stands for ATOMICITY. Where the Atomicity follows, either all transactions will happen at once, or none of the transactions will happen. Which is ALL or Nothing. During transactions, if there is any error, then the whole transaction will be undone. 

Example: A bank transfer. If the transaction is denied or any unforeseen condition disrupts the transaction, then the whole transaction will be cancelled to maintain the database integrity. 

2. Consistency

The C in ACID stands for CONSISTENCY. The consistency will maintain the correct order or valid state of the database before and after transactions. This will maintain the integrity of the data concerning primary and foreign key constraints. 

Example: In a fund transfer, consistency ensures that the total amount remains unchanged—if money is added to one account, it must be deducted from the other.

3. Isolation 

The I in ACID refers to ISOLATION. Isolation ensures that concurrent transactions do not interfere with each other, preserving data correctness as if transactions were executed serially. There are different levels of isolation.

1. Read Uncommitted
2. Read Committed
3. Repeatable Read
4. Serializable

These levels will help to manage the concurrency. 

4. Durability 

The D in ACID is DURABILITY. The durability ensures that the data is saved permanently. So that even if the system crashes, the data will be saved. The changes are permanent, which is non-volatile, so a system crash will not delete the data as it is saved in disk logs, or it will have backups.

Best Practices

1. Automatic Backups: It has automatic backups, which ensure data is backed up automatically whenever changes happen.
2. Multi-location Storage: It can store data from various geographical locations with the help of cloud platforms.
3. Data Encryption: With the help of an encryption method, the user can prevent the data from unauthorized access.
4. Disaster Recovery Plan (DRP): It has a DRP, which can be used to recover data that might have been lost during a large-scale crash.
5. Scalability: The system can easily scale storage and resources to handle increasing amounts of data efficiently.

Learn the key differences between File System and DBMS in simple terms.

Popular DBMS Software in 2025

There are many software programs in DBMS that can be used to build applications. There are MySQL, PostgreSQL, SQL Server, and Oracle databases. These databases are used based on the user’s requirements. 

1. MySQL Server

MySQL is an open-source relational database that is known for its readability and flexibility. MySQL is widely used in many applications. 

Example: To retrieve the data that was monitored by smart devices (IoT). 

CREATE TABLE Sensors (
    SensorID INT AUTO_INCREMENT PRIMARY KEY,
    Location VARCHAR(50),
    SensorType VARCHAR(30),
    InstalledDate DATE
);
CREATE TABLE SensorData (
    DataID INT AUTO_INCREMENT PRIMARY KEY,
    SensorID INT,
    Temperature FLOAT,
    Humidity FLOAT,
    RecordedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (SensorID) REFERENCES Sensors(SensorID)
);

-- Insert values
INSERT INTO Sensors (Location, SensorType, InstalledDate)
VALUES ('Main Street', 'Temperature', '2024-01-10'),
       ('City Park', 'Humidity', '2024-02-15');

-- Insert sample readings
INSERT INTO SensorData (SensorID, Temperature, Humidity)
VALUES (1, 22.5, NULL),
       (2, NULL, 60.2);

-- Retrieve latest sensor readings
SELECT s.Location, sd.Temperature, sd.Humidity, sd.RecordedAt
FROM SensorData sd
JOIN Sensors s ON sd.SensorID = s.SensorID
ORDER BY sd.RecordedAt DESC;

Output:

Explanation: The commands retrieved the data from the IoT devices with their locations, temperature, humidity, and the time the data were recorded. 

2. PostgreSQL 

PostgreSQL is an open-source RDBMS that includes features like JSON support, flexibility, and full ACID compliance for performance and reliability.

Example: 

CREATE TABLE Patients (
    PatientID SERIAL PRIMARY KEY,
    Name VARCHAR(100),
    DateOfBirth DATE,
    Contact VARCHAR(15)
);
CREATE TABLE Appointments (
    AppointmentID SERIAL PRIMARY KEY,
    PatientID INT,
    DoctorName VARCHAR(100),
    AppointmentDate TIMESTAMP,
    Notes TEXT,
    FOREIGN KEY (PatientID) REFERENCES Patients(PatientID)
);

-- Insert sample patients
INSERT INTO Patients (Name, DateOfBirth, Contact)
VALUES ('Alice Johnson', '1985-08-25', '555-1234'),
       ('Bob Smith', '1990-02-12', '555-5678');

-- Insert values
INSERT INTO Appointments (PatientID, DoctorName, AppointmentDate, Notes)
VALUES (1, 'Dr. Williams', '2025-04-10 09:00:00', 'Routine checkup'),
       (2, 'Dr. Roberts', '2025-04-12 14:30:00', 'Follow-up on test results');

-- Retrieve appointment dates
SELECT p.Name, a.DoctorName, a.AppointmentDate, a.Notes
FROM Appointments a
JOIN Patients p ON a.PatientID = p.PatientID
WHERE a.AppointmentDate > NOW()
ORDER BY a.AppointmentDate;

Output:

Explanation: PostgreSQL first counts and inserts the rows in a database and then retrieves the required data. Here, the future appointments of the patients have been fetched by specifying the condition to get the appointment dates.  

3. Microsoft SQL Server

Microsoft SQL Server is a relational database management system developed by Microsoft, known for its robustness and tight integration with Windows.

Example:

CREATE TABLE Vehicles (
    VehicleID INT IDENTITY PRIMARY KEY,
    PlateNumber VARCHAR(20) UNIQUE,
    Model VARCHAR(50),
    Year INT
);
CREATE TABLE MaintenanceRecords (
    RecordID INT IDENTITY PRIMARY KEY,
    VehicleID INT,
    ServiceDate DATE,
    Description TEXT,
    FOREIGN KEY (VehicleID) REFERENCES Vehicles(VehicleID)
);

-- Insert sample vehicles
INSERT INTO Vehicles (PlateNumber, Model, Year)
VALUES ('ABC-1234', 'Ford Transit', 2020),
       ('XYZ-5678', 'Mercedes Sprinter', 2021);

-- Insert sample maintenance records
INSERT INTO MaintenanceRecords (VehicleID, ServiceDate, Description)
VALUES (1, '2025-03-15', 'Oil change and tire rotation'),
       (2, '2025-03-18', 'Brake pad replacement');

-- Retrieve recent maintenance records
SELECT v.PlateNumber, v.Model, m.ServiceDate, m.Description
FROM MaintenanceRecords m
JOIN Vehicles v ON m.VehicleID = v.VehicleID
ORDER BY m.ServiceDate DESC;

Output:

Explanation: The SQL Server fetched the data of the car’s model, plate number, and service date, and then the description given by the service center. 

4. Oracle Database 

Oracle database is a premium RDBMS database that is used for large applications. This database has very strong security and performance. It will also provide solutions for the cloud. 

Example: 

CREATE TABLE Spacecraft (
    SpacecraftID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    Name VARCHAR2(50),
    LaunchDate DATE
);
CREATE TABLE Missions (
    MissionID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    SpacecraftID NUMBER,
    Destination VARCHAR2(100),
    MissionStatus VARCHAR2(20),
    FOREIGN KEY (SpacecraftID) REFERENCES Spacecraft(SpacecraftID)
);
INSERT INTO Spacecraft (Name, LaunchDate)
VALUES ('Orion', TO_DATE('2025-06-10', 'YYYY-MM-DD')),
       ('Endeavour', TO_DATE('2025-09-22', 'YYYY-MM-DD'));

-- Insert values
INSERT INTO Missions (SpacecraftID, Destination, MissionStatus)
VALUES (1, 'Mars Research Station', 'Planned'),
       (2, 'Lunar Base Alpha', 'In Progress');

--To format the commands
SET LINESIZE 100
SET PAGESIZE 100
COLUMN Name FORMAT A15
COLUMN Destination FORMAT A25
COLUMN MissionStatus FORMAT A15

-- To run the query
SELECT 
    s.Name, 
    m.Destination, 
    m.MissionStatus
FROM 
    Missions m
JOIN 
    Spacecraft s ON m.SpacecraftID = s.SpacecraftID
WHERE 
    m.MissionStatus != 'Completed';

Output: 

Explanation: The Oracle database fetched the data of the space station and retrieved the data, which is not complete, by using the help of a subquery. 

Real-World Applications of DBMS

1. Banking and Finance Systems
In banking, a DBMS is essential for managing large volumes of transactions. It ensures data integrity through the ACID properties of the database, which are crucial for handling funds, customer information, and transaction logs. Most financial institutions rely on RDBMS, showcasing a practical difference in DBMS vs RDBMS discussions.

2. E-commerce Platforms
Online retail websites use DBMS architecture to handle user accounts, inventory, orders, and payments. These systems often deal with structured vs unstructured databases, such as customer profiles (structured) and reviews or images (unstructured). Learning how these systems operate can be part of a hands-on DBMS tutorial for beginners.

3. Healthcare Management
Hospitals and clinics utilize different DBMS types with examples such as patient databases (relational) and medical image storage (non-relational). Understanding the types of DBMS helps in selecting the right system for secure and efficient data storage.

Best DBMS Software Compared for Different Use-Cases 

Conclusion

A Database Management System (DBMS) is the fundamental way of storing, managing, and securing data in a database efficiently. A well-designed DBMS reduces data redundancy through normalization and enforces data integrity through constraints and rules. Many types of databases work for the different needs of the users. ACID properties of the database will protect the data and architecture. DBMS ensures reliability and gives high performance in managing the data. 

You can learn more about SQL in the SQL Course and also explore SQL Interview Questions prepared by industry experts.

Introduction to DBMS – FAQs