Database Management System

Computerized data-keeping system

Introduction

A Database Management System (DBMS) is a software system that houses data so that it can be accessed and altered easily by those who are permitted access. DBMS facilitates the management of data in a highly-organized manner. Additionally, DBMS helps secure data and get useful insights from it. Common DBMS software are MySQL, PostgreSQL, Microsoft Access, MariaDB, SQLite and Microsoft SQL Server.

What is Database

The database is a collection of inter-related data which is used to retrieve, insert and delete the data efficiently. It is also used to organize the data in the form of a table, schema, views, reports, etc.

For example: The college Database organizes the data about the admin, staff, students and faculty etc.

Using the database, you can easily retrieve, insert, and delete the information.

Key Features of DBMS

A database management system (DBMS) is a software system that manages and controls the creation, maintenance, and use of databases. Some of the key features of DBMS are as follows:

• Data abstraction and independence: DBMS provides a layer of abstraction between the application program and the database, making it easier to manage data and making the system more flexible.
• Data security: DBMS provides mechanisms for ensuring the security and integrity of the data stored in the database, including user authentication and access control.
• Data consistency: DBMS ensures that the data in the database is consistent by enforcing data validation rules, transaction management, and recovery mechanisms.
• Concurrent access and recovery: DBMS allows multiple users to access the database concurrently while ensuring that the data remains consistent and that data loss is minimized in case of system failures.
• Reduced application development time: DBMS provides an interface for the application program to interact with the database, reducing the time and effort required to develop and maintain the application.
• Backup and recovery: DBMS provides backup and recovery mechanisms to protect against data loss due to system failures or other disasters.
• Data sharing: DBMS enables data sharing and integration across different applications and systems, allowing organizations to make more informed decisions based on the data available.
• Improved performance: DBMS provides query optimization, indexing, and other mechanisms to improve the performance of database operations.

A database is a collection of interrelated data which helps in the efficient retrieval, insertion, and deletion of data from the database and organizes the data in the form of tables, views, schemas, reports, etc. For Example, a university database organizes the data about students, faculty, admin staff, etc. which helps in the efficient retrieval, insertion, and deletion of data from it.

What are the components of a DBMS?

A database management system (DBMS) comprises several components that work together to manage and control the creation, maintenance, and use of databases. Some of the key components of a DBMS are as follows:

• Data definition language (DDL): DDL is used to define the database schema, including tables, columns, constraints, and relationships.
• Data manipulation language (DML): DML is used to manipulate the data stored in the database, including inserting, updating, and deleting records.
• Query language: Query language is used to retrieve data from the database using queries, including select statements and aggregate functions.
• Transaction management: Transaction management ensures that database operations are completed atomically, consistently, and durably, even in the event of system failures.
• Backup and recovery: Backup and recovery mechanisms are used to protect against data loss due to system failures or disasters.
• Security and access control: Security and access control mechanisms are used to ensure that only authorized users have access to the database and that the data remains secure.
• Concurrency control: Concurrency control mechanisms ensure that multiple users can access the database concurrently without interfering with each other’s operations.

There are four types of Data Languages

1. Data Definition Language (DDL)
2. Data Manipulation Language(DML)
3. Data Control Language(DCL)
4. Transactional Control Language(TCL)

DDL is the short name for Data Definition Language, which deals with database schemas and descriptions, of how the data should reside in the database.

• CREATE: to create a database and its objects (table, index, views, store procedure, function, and triggers)
• ALTER: alters the structure of the existing database
• DROP: delete objects from the database
• TRUNCATE: remove all records from a table, including all spaces allocated for the records that are removed
• COMMENT: add comments to the data dictionary
• RENAME: rename an object

DML is the short name for Data Manipulation Language which deals with data manipulation and includes the most common SQL statements such as SELECT, INSERT, UPDATE, DELETE, etc. It is used to store, modify, retrieve, delete and update data in a database.

• SELECT: retrieve data from a database
• INSERT: insert data into a table
• UPDATE: updates existing data within a table
• DELETE: Delete all records from a database table
• MERGE: UPSERT operation (insert or update)
• CALL: call a PL/SQL or Java subprogram
• EXPLAIN PLAN: interpretation of the data access path
• LOCK TABLE: concurrency Control

DCL is short for Data Control Language which acts as an access specifier to the database. (basically to grant and revoke permissions to users in the database)

• GRANT: grant permissions to the user for running DML(SELECT, INSERT, DELETE,…) commands on the table
• REVOKE: revoke permissions to the user for running DML(SELECT, INSERT, DELETE,…) command on the specified table

TCL is short for Transactional Control Language which acts as a manager for all types of transactional data and all transactions. Some of the commands of TCL are

• Roll Back: Used to cancel or Undo changes made in the database
• Commit: It is used to apply or save changes in the database
• Save Point: It is used to save the data on a temporary basis in the database

Database Management System

The software which is used to manage databases is called Database Management System (DBMS). For Example, MySQL, Oracle, etc. are popular commercial DBMS used in different applications. DBMS allows users the following tasks:

• Data Definition: It helps in the creation, modification, and removal of definitions that define the organization of data in the database.
• Data Updation: It helps in the insertion, modification, and deletion of the actual data in the database.
• Data Retrieval: It helps in the retrieval of data from the database which can be used by applications for various purposes.
• User Administration: It helps in registering and monitoring users, enforcing data security, monitoring performance, maintaining data integrity, dealing with concurrency control, and recovering information corrupted by unexpected failure.

Why did we Shift from File System to DBMS?

File System manages data using files on a hard disk. Users are allowed to create, delete, and update the files according to their requirements. Let us consider the example of a file-based University Management System. Data of students is available to their respective Departments, Academics Section, Result Section, Accounts Section, Hostel Office, etc. Some of the data is common for all sections like Roll No, Name, Father Name, Address, and Phone number of students but some data is available to a particular section only like the Hostel allotment number which is a part of the hostel office. Let us discuss the issues with this system:

• Redundancy of data: Data is said to be redundant if the same data is copied at many places. If a student wants to change their Phone number, he or she has to get it updated in various sections. Similarly, old records must be deleted from all sections representing that student.
• Inconsistency of Data: Data is said to be inconsistent if multiple copies of the same data do not match each other. If the Phone number is different in Accounts Section and Academics Section, it will be inconsistent. Inconsistency may be because of typing errors or not updating all copies of the same data.
• Difficult Data Access: A user should know the exact location of the file to access data, so the process is very cumbersome and tedious. If the user wants to search the student hostel allotment number of a student from 10000 unsorted students’ records, how difficult it can be.
• Unauthorized Access: File Systems may lead to unauthorized access to data. If a student gets access to a file having his marks, he can change it in an unauthorized way.
• No Concurrent Access: The access of the same data by multiple users at the same time is known as concurrency. The file system does not allow concurrency as data can be accessed by only one user at a time.
• No Backup and Recovery: The file system does not incorporate any backup and recovery of data if a file is lost or corrupted.

Advantages of DBMS

• Controls database redundancy: It can control data redundancy because it stores all the data in one single database file and that recorded data is placed in the database.
• Data sharing: In DBMS, the authorized users of an organization can share the data among multiple users.
• Easily Maintenance: It can be easily maintainable due to the centralized nature of the database system.
• Reduce time: It reduces development time and maintenance needs.
• Backup: It provides backup and recovery subsystems which create an automatic backup of data from hardware and software failures and restores the data if required.
• multiple user interfaces: It provides different types of user interfaces like graphical user interfaces and application program interfaces.

Disadvantages of DBMS

• Cost of Hardware and Software: It requires a high-speed data processor and large memory size to run DBMS software.
• Size: It occupies a large space of disks and large memory to run them efficiently.
• Complexity: Database system creates additional complexity and requirements.
• Higher impact of failure: Failure has highly impacted the database because in most organizations, all the data is stored in a single database and if the database is damaged due to electric failure or database corruption then the data may be lost forever.

Several types of DBMS

1. Relational DBMS (RDBMS): An RDBMS stores data in tables with rows and columns, and uses SQL (Structured Query Language) to manipulate the data.
2. Object-Oriented DBMS (OODBMS): An OODBMS stores data as objects, which can be manipulated using object-oriented programming languages.
3. NoSQL DBMS: A NoSQL DBMS stores data in non-relational data structures, such as key-value pairs, document-based models, or graph models.