Database Management Systems (DBMS)

Besides storing and retrieving information, Databases also allow us to add, change and delete information. These operations are managed by Database Management Systems (DBMS). A Database Management Systems (DBMS) is a set of software designed to create maintain a database. The main purpose is to provide access to the database data and facilitate a efficient way to define, store, query and manipulate the data. Additionally it also protects the database from unauthorized access and ensures that data can be recovered if the system fails, It also allows multiple users to access the database simultaneous by managing Database Concurrency

In addition to it’s role in centralized database management it also provides several other advantages.

1. Program Data Independence it is often referred to as data Independence, it is a very crucial feature of the Database Management Systems (DBMS) which facilitates modification of that database structure without making any changes to applications using the database. To achieve a high level of Data Independence the DBMS stores the description of the database structure and it’s various data constraints in it’s catalog, the catalog houses the metadata, which is information about the data. This is possible through the a three-level DBMS Architecture
2. Data Abstraction Data Abstraction is the concept that underlies the Program-Data Independence , it enables the DBMS system to present an abstract view of the data without going in details on how the data is stored internally and the physical storage aspects of it.
3. Supports Multiple Views of the Data In a shared database environment with multiple people working on the database, everyone has their own perspective/view of the database depending on their understanding and their business use case. Database Management Systems (DBMS) offers the capability to define multiple views in this regard. A Database View represents a subset of the database that contains virtual data derived from the database files. Unlike physical files, Database Views do not exist in a tangible, physical form, instead only the view’s definition is stored. It’s main focus on defining how a view should be constructed based on the underlying data.

Popular DBMS

There are several types of Database Management Systems (DBMS) depending on how they store data and the format the data is stored, as well as the query mechanisms provided to applications and users.

• Relational Database: Uses a table-based structure where data is organized into rows and columns. Examples includeMySQL, PostgreSQL, Microsoft SQL Server, and Oracle. This is the most common database type and preferred by organizations for transactional data.
• NoSQL Database: Designed for specific data models with flexible schemas, including document stores (e.g., MongoDB), key-value stores (e.g., Redis), wide-column stores (e.g., Apache Cassandra), and graph databases (e.g., Neo4j). These are often used in web and mobile applications or in large-scale data mining.
• In-Memory Database: Stores data in main memory for faster read and write operations. Examples include Redis and Memcached.
• Distributed Databases: Distributes data across multiple nodes or locations to achieve high availability and scalability. Examples include Apache Cassandra and Google Spanner.
• Cloud DBMS: Operates in a cloud environment, offering flexibility, scalability, and managed services. Examples include Amazon RDS, Microsoft Azure SQL Server, and Google Cloud Firestore, among others.

Benefits of DBMS

A Database Management Systems (DBMS) is a software that interacts with user, applications and the database itself to capture and analyze the data, it offers an interface to perform operations such as data insertion, query, update and deletion.

Key Functions of Database Management Systems (DBMS):

• Data Definition - Defining the Database structure including tables, columns, SQL Data Types and Database Relationships
• Data Manipulation - Inserting, updating, deleting, and querying data within the database.
• Data Security - Managing user access and permissions to ensure data privacy and protection.
• Data Integrity - Enforcing Rules and Constraints to maintain data accuracy and consistency
• Transactions Management - Ensuring that all database transactions adhere to ACID properties and that data is not corrupted when accessed by multiple users simultaneously.
• Database - Backup and Recovery - Provide mechanisms for data backup and recovery to prevent data loss and ensure business continuity.

Database Interactions

The primary objective of a Database Management System (DBMS) is to empower its users to perform various operations on the database, such as inserting, deleting, and retrieving data, all in abstract terms. Users interact with the database without needing to concern themselves with the physical representation of the data. To facilitate these operations and cater to different user requirements, DBMSs typically offer specialized programming languages known as Database Languages or DBMS Languages.

In addition to Data Definition Language (DDL) and Data Manipulation Language (DML), DBMSs also provide two other essential languages: Data Control Language (DCL) and Transaction Control Language (TCL).

Data Control Language (DCL): DCL is primarily responsible for managing access control and security within the database. It is used to create user roles, grant permissions, and exert control over database access, thus ensuring data security. DCL is instrumental in safeguarding the database from unauthorized access and manipulation.

Transaction Control Language (TCL): TCL, on the other hand, focuses on managing Transactions that occur within the database. It includes commands for initiating transactions, committing or rolling back transactions, and ensuring the consistency and integrity of data during transactional operations.

The core of a DBMS comprises two fundamental database languages:

Data Definition Language (DDL): DDL serves as the foundation for defining the structure of the database schema. It enables users to specify the layout of the database, including data types, relationships between tables, and constraints. The DDL compiler processes these schema definitions and stores them in a specialized catalog within the DBMS, known as the data dictionary.

Data Manipulation Language (DML): DML, as the name suggests, is used for manipulating the data within the database. It allows users to perform operations such as inserting new records, deleting existing records, and modifying data. DML commands can be executed both in planned queries, embedded within application programs, and in ad-hoc, unplanned queries or interactive queries.

In some DBMSs, particularly those where a strict separation between the conceptual and internal database levels is maintained, the DDL is primarily used to define the conceptual schema. In these cases, a distinct language, the Storage Definition Language (SDL), is employed to define the internal schema. Furthermore, in DBMSs following the true three-schema architecture, a third language called the View Definition Language (VDL) is utilized to define the external schema, ensuring that different user groups can access customized views of the database.

DDL statements not only define the schema but also specify integrity rules and constraints to maintain the database’s integrity. These integrity constraints encompass domain constraints, referential integrity, assertions, and authorization rules, all of which are essential to preserving the quality and consistency of data. DDL processes these instructions and stores the resulting schema description within the data dictionary a specialized table containing metadata. Importantly, only the database system itself can modify the data dictionary, preventing users from making direct updates.

A database system, being a complex software entity, comprises various software components, each responsible for distinct tasks:

Data Definition: This component focuses on defining the data’s structure, encompassing the definition and modification of record structures, field data types, and constraints. Database administrators wield this component to define and modify the database structure using DDL and privileged commands. The DDL compiler within the DBMS processes these schema definitions and stores them in the data dictionary for reference by other DBMS components.

Data Manipulation: Once the data structure is defined, data manipulation becomes paramount. This component manages operations like insertion, deletion, and modification of records. It handles both planned queries, embedded within application programs and submitted to precompilers, and unplanned queries or interactive queries. The DML compiler processes these queries, optimizing them for execution.

Data Security and Integrity: Security and data integrity functions are embedded within the DBMS, alleviating the need for application programmers to implement these functions explicitly. They ensure data security by controlling access and safeguarding the integrity of the stored data.

Concurrency and Data Recovery: The DBMS incorporates functions to manage concurrent access by multiple users and to recover data after system failures. Concurrency control techniques and database recovery mechanisms are critical aspects of this component.

Performance Optimization: To optimize query performance, the DBMS offers functions for evaluating various execution plans for a query and selecting the most efficient one. These optimizations enhance the speed and efficiency of data retrieval.

In short, a DBMS is a complex system composed of various software components, each fulfilling a distinct role in managing and maintaining the database. Understanding these components is essential for effectively designing, implementing, and optimizing database systems, making them a crucial subject for graduate-level computer science students.