Introduction to DBMS

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. A DBMS 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, the user will learn what a DBMS is, its types, and its functionality in detail.  

Table of Contents:

• What is a DBMS?
• Why Do We Need to Use DBMS?
• Types of Databases
• DBMS Architecture
• Types of DBMS Architectures
• Comparison of DBMS Architectures
• Database Models
• SQL Commands
• Database Keys in DBMS
• ACID Properties in DBMS
• DBMS Security Best Practices
• Best Practices
• Different DBMS Software
• Comparison of Each DBMS Software
• Conclusion

What is a 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 the data from 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 DBMS to handle large datasets efficiently. These features make DBMS essential for developing scalable and reliable applications.

Types of Databases

The Databases are classified based on their structure and their way to 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 ACID properties, 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 in a database. 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. NoSQL is often used where traditional relational databases fall short, such as with large-scale unstructured data or flexible schema requirements.

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 their 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 of that. Each child will only have one parent, but every parent will have many child nodes. It is widely used in Windows Registry, XML databases, and GIS. 

4. Network Database

The network database is an extended version of the hierarchical database that allows the 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. But in a network database, a child can have multiple parents. It can be used in complex data. Network databases use records and sets to represent relationships. The multiple nodes help in retrieving data easily. 

These databases are very useful in applications like telecommunications and financial transactions. Network databases like Integrated Data Store (IDS), CA-IDMS and TurboIMAGE. These databases are used in transactions where the data changes dynamically. Because of their flexibility and performance. They are suited for applications requiring complex relationships and high-speed transactional processing.

DBMS Architecture 

Database Management System (DBMS) 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. It uses web servers like Apache, application servers like Tomcat and Node.js, and APIs.

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 retrieving 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. 

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 DBMS software. 
• Heterogeneous DDBMS: Different database instances use different databases, and DBMS 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. It will automatically back up all the data in a system.

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. 

Comparison of DBMS Architectures

Database Models

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 ACID properties 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. 

Root (C:/)

│

├── Program Files

│   ├── Microsoft Office

│   ├── Adobe

│

├── Users

│   ├── John

│   │   ├── Documents

│   │   ├── Pictures

│   │   ├── Downloads

│

├── Windows

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. 

      Student

       /   

      /     

   Course  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. The methods will be the return book and the borrow 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 

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 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.  

Database Keys in DBMS

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

1. Primary Key

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. A candidate key must be unique and should not contain any NULL values.  

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. Super key may have extra columns that are not necessary in a table for uniqueness. Super keys are a superset of candidate keys because if any column is removed from a table, that will make the table non-unique. 

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) 

);

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: TIn 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.

• Read Uncommitted
• Read Committed
• Repeatable Read
• 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.

DBMS Security Best Practices 

The DBMS plays an important role in protecting sensitive data from third parties, cyberattacks, and breaches. A secure DBMS will maintain the confidentiality, integrity, and availability (CIA)  triad of data. The best practices include authentication, role-based access control, cryptographic methods like encryption and decryption, and backups to prevent data loss. 

1. Authentication & Authorization

Authentication is the process of verifying the user’s identity before giving access to the database. 

Authorization techniques:

• Having a strong username and password is the basic objective of securing the file. The password can have some hashing and salting.
• Enabling Multi-Factor Authentication (MFA) will add extra security to the data by asking for OTPs or biometric authentication for accessing the data.
• Using Single Sign-On (SSO) will help the user to authenticate once and then access the other services with secure identity providers like OAuth or SAML.

Authorization: 

Authorization will help the user define what actions an authenticated user can perform in the database.

• The authorization is implemented by Access Control Lists (ACLs), levels of privileges, and policies. 
• Use DAC (Discretionary Access Control) or MAC (Mandatory Access Control) models to restrict unauthorized access. 
• Every database will have a principle of least privilege access for the user, which will give the minimum access to perform their operations.  

2. Role-Based Access Control (RBAC)

It is a structured security model that will give access rights to the user based on their roles. This model will not depend on the individual user to give the privileges. This will reduce the work of the administrator by giving limited access to the user based on their roles. 

Example: In an organization:

The Admin Role: The admin role will have full access to the database. They can read, write, update, and delete the data in the database. 

Developer: The developer can only have read and update access to the database. They won’t have the delete privilege. 

Manager: The manager will only have read-only access to the file or reports. 

This way, the user can protect the integrity of the data. 

Separation of Duties (SoD): 

The separation of duties (SoD) will be used to prevent any single user from having full access to a file during the operation. 

Example: If a user enters financial transactions, they can only start them. Only the authorized person can approve the transaction. 

3. Data Encryption

Data encryption is a fundamental method to protect the data in a database. The encryption method is used to secure the data using cryptographic methods so that even if the data is stolen, the attacker cannot access the files.

Types of Encryption:

1. Methods like AES-256 are used to protect stored data. This is called At-Rest encryption. 
2. To protect the data from unauthorized persons, the database has end-to-end encryption, which encrypts the data from the sender to the receiver. 
3. To encrypt a specific field in a database, particularly protecting a part of the data, is called column-level encryption. For example, it can secure credit card details, or in social media login, it will hide passwords and other personal information. 

Syntax: 

CREATE TABLE Users (

 ID INT PRIMARY KEY,

 Name VARCHAR(100),

 -- Encrypted column

 SSN VARBINARY(256)

); 

4. Some DBMS servers, like TDE, encrypt the entire database at rest without requiring changes to the application.

4. Backup & Recovery:

The backup and recovery option will help the user to retrieve the data when they accidentally delete it, any corrupted file is used, or cyberattacks like ransomware and hardware failures happen. 

Types of Backups:

• Full backup – This will backup all the files simultaneously and will send the copy to the database. The full backup will take a snapshot of the full data at a point. 
• Incremental Backup – It won’t backup the full data. Instead, it will back up only the new data that was added. 
• Differential Backup – It will back up the data and save it from the last full backup.

Best Practices

1. It has automatic backups, which ensure data is backed up automatically whenever changes happen.
2. It can store data from various geological locations with the help of cloud platforms.
3. With the help of an encryption method, the user can prevent the data from unauthorized access. 
4. It has a Disaster Recovery Plan (DRP), which can be used to recover data that might have been lost during a large-scale crash. 

Different DBMS Software

There are many software 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. 

Comparison of Each DBMS Software 

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 different needs of the users. They also have many Servers like Microsoft SQL servers, Oracle databases, PL/SQL Language, PostgreSQL, and MySQL, to work on different applications effectively. ACID properties will protect the data in the database 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