Database Management System (DBMS)

• A database is an electronically stored, systematic collection of data
• Databases can contain words, numbers, images, videos, and other types of files

Database Management System (DBMS)

• Databases are managed using specialized software
• A DBMS is a software solution designed to efficiently manage, organize, and retrieve data in a structured manner
• DBMS is a critical component in modern computing
• DBMS enables organizations to store, manipulate, and secure their data effectively
• A DBMS is a system that allows users to create, modify, and query databases
• They ensure data integrity, security, and efficient data access

Example of Database Use

• A university database can store and manage student information
• It can store faculty records, and administrative data
• This will allow seamless retrieval, insertion, and deletion of information as required

Key Features of DBMS

• Data Modeling helps by having tools to create and modify data models, defining the structure and relationships within the database
• Data Storage and Retrieval features efficient mechanisms for storing data and executing queries to retrieve it quickly
• Concurrency Control ensures multiple users can access the database simultaneously without conflicts
• Data Integrity and Security enforces rules to maintain accurate and secure data, including access controls and encryption
• Backup and Recovery protects data with regular backups and enables recovery in case of system failures

Types of DBMS

• Relational Database Management System (RDBMS)
• NoSQL DBMS
• Object-Oriented DBMS (OODBMS)

Relational Database Management System (RDBMS)

• RDBMS organizes data into tables (relations) composed of rows and columns
• Primary keys uniquely identify rows and foreign keys establish relationships between tables
• Queries are written in SQL (Structured Query Language), which allows for efficient data manipulation and retrieval
• Examples of RDBMS are MySQL, Oracle, Microsoft SQL Server and Postgre SQL

NoSQL DBMS

• NoSQL systems are designed to handle large-scale data and provide high performance for scenarios where relational models might be restrictive
• They store data in various non-relational formats, such as key-value pairs, documents, graphs, or columns
• They enable rapid scaling and are well-suited for unstructured or semi-structured data
• Examples of NoSQL DBMS are MongoDB, Cassandra, DynamoDB and Redis

Object-Oriented DBMS (OODBMS)

• OODBMS integrates object-oriented programming concepts into the database environment, allowing data to be stored as objects
• This supports complex data types and relationships, making it ideal for applications requiring advanced data modeling and real-world simulations
• Examples of OODBMS, ObjectDB, db4o

Database Languages

• Database languages are specialized sets of commands and instructions used to define, manipulate, and control data within a database
• Each language type plays a distinct role in database management
• This ensures efficient storage, retrieval, and security of data
• The primary database languages include:
• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)
• Transaction Control Language (TCL)
• Data Query Language (DQL)

Data Definition Language (DDL)

• DDL deals with database schemas and descriptions, of how the data should reside in the database
• CREATE is used to create a database and its objects like (table, index, views, store procedure, function, and triggers)
• ALTER alters the structure of the existing database
• DROP deletes objects from the database
• TRUNCATE removes all records from a table, including all spaces allocated for the records are removed
• COMMENT adds comments to the data dictionary
• RENAME renames an object

Data Manipulation Language (DML)

• DML focuses on manipulating the data stored in the database, enabling users to retrieve, add, update, and delete data
• SELECT retrieves data from a database
• INSERT inserts data into a table
• UPDATE updates existing data within a table
• DELETE deletes all records from a database table
• MERGE provides UPSERT operation (insert or update)
• CALL is uses to call a PL/SQL or Java subprogram
• EXPLAIN PLAN provides interpretation of the data access path
• LOCK TABLE provides concurrency Control

Data Control Language (DCL)

• DCL commands manage access permissions, ensuring data security by controlling who can perform certain actions on the database
• GRANT provides specific privileges to a user (e.g., SELECT, INSERT)
• REVOKE removes previously granted permissions from a user

Transaction Control Language (TCL)

• TCL commands oversee transactional data to maintain consistency, reliability, and atomicity
• ROLLBACK undoes changes made during a transaction
• COMMIT saves all changes made during a transaction
• SAVEPOINT sets a point within a transaction to which one can later roll back

Data Query Language (DQL)

• DQL is a subset of DML, specifically focused on data retrieval
• SELECT is the primary DQL command, used to query data from the database without altering its structure or contents

Characteristics of DBMS

• Real World Entity makes the DBMS one of the most important and easily understandable characteristics
• DBMS is developed in such a way that it can manage huge business organizations and store their business data with security
• The Database can store information such as the cost of vegetables, milk, bread, etc
• Entities look like real-world entities
• If creating a student database, the database needs some entity, where each student stores their data
• Commonly used properties in the student database include name, age, gender, roll number, etc
• Self-explaining nature means that the Database contains another database, and another database also contains metadata
• Metadata means data about data
• For example, in a school database, the total number of rows and the table's name are examples of metadata
• The self-explaining nature means the Database explains all the information automatically
• This is because all the data are stored in a structured format
• Atomicity of Operations (Transactions) dictates that either the operation should be performed or not performed, meaning it should complete on 0% or 100%
• DBMS provides atomicity as a characteristic, making it the most important and useful characteristic; every bank has its own Database
• Transaction is the most common atomic operation of the bank
• For example, if someone wants to transfer 1000 rupees to another account and there is a problem in the destination account, then the money will be deducted, but not credited to the destination account
• Concurrent Access without Anomalies means that multiple users can access the Database and fetch the information without any problem
• If one person gives their ATM card to someone and tells them to withdraw 5000 from the ATM, and at the same time the card holder transfers 2000 rupees to his brother, both operations can perform successfully
• If the card holder checks their bank balance after both transactions, it will show as the correct updated amount
• This error-free updatation of bank balance is possible with the help of the concurrent feature of the Database
• A characteristic of the Databases ability is to store any kind of Structured Data
• Databases have the ability to store data in a structured format
• DBMS systems have the ability to store any type of data that exists in the real world, and these data are structured way
• The term integrity dictates the data should be correct and consistent in nature
• For example, if there is a bank named ABC bank, and ABC bank has its own Database for the storage of its customer data and the account details are not available in the bank, then the Database gives the incorrect output
• Data inconsistency exists if a customer changes their address but the new address is not updated in the Database
• If someone's account has zero balance and later the customer deposits 6000 rupees in his account, then the new account balance must be updated in the Database
• Ease of Access (The DBMS Queries) dictates that searching for any data is very easy to do
• In DBMS, the search operation becomes much faster than manual searching
• In DBMS, there is a CRUD which means Create, Read, Update & Delete, so all types of query can be implemented
• SQL and No-SQL Databases have the ability to use different methods of data storage
• SQL databases store the data in the form of Tables, i.e., rows and columns
• The No-SQL databases can store data in any form other than a table
• One of the most popular MongoDB stores data in the form of JSON (JavaScript Object Notation)
• With DBMS you can run queries and operations on SQL as well as No-SQL databases
• ACID Properties are followed to maintain consistency in the Database
• ACID stands for Atomicity, Consistency, Isolation, and Durability
• Atomicity means the transaction should either be 0% or 100% completed
• Consistency means that the change in data should be reflected everywhere in a database
• Isolation means that multiple transactions can occur independently without the interference of some other transactions
• Durability means that successful atomic transactions should reflect in the Database
• Security dictates the Database should be accessible to the users in a limited way
• Access to make changes to a database should be limited, and the users must not be given complete access to the entire Database
• Unauthorized users should not be allowed to access the Database
• The DBMS uses Authentication for various users
• Authentication refers to the limit to which the user can access the Database
• Authentication means the process of laughing in of the user only with the rights that he/she has been authorized to
• An admin has access to make changes to the Database but the employees have access only to their personal profiles and can make changes to them only
• Employees cannot access the Database of any other employee or the organization