Database Management System | DBMS Tutorial

The collection of similar data in one place refers to a database. Let’s not confuse it with data. Data is a collection of information in the form of facts and figures. The database allows users to manipulate data according to their comfort.

This includes retrieval, insertions, and removal of data. It organizes data in tabular, graphical, and many more forms. The database management system is software to help users in managing data. 

It provides an interface for the users to handle data in various forms. This may be during database creating or during the time of update. It also ensures that the data is safe all time while maintaining consistency.

Data Definition Language is the scheme that the system follows to see what data will look like in the database. Some famous database management softwares are – MySQL, Oracle, etc. The task of the database management system includes – 

1. Data Updation – It is where all the changes in data are made. This may be the addition, removal, or modification of data.

2. Data Retrieval – It allows retrieval of the data from the database for application usage.

3. User Administration –  It registers, monitors, maintains and enforces data all the time. This includes securing it, controlling it, managing its performance, and recovering information in case of failure. 

History of Database Management System

• Charles Bachman came up with the first DBMS system in 1960.
• Codd by IBM’S Information Management System enters in 1970.
• Peter Chen introduces the Entity-relationship model in 1976. 
• The Relational Model became a database component in 1980.
• Object-oriented DBMS develops in 1985
• The incorporation of object orientation in DBMS takes place in the 1990s.
• A personal DBMS by Microsoft – MS access came out in 1991.
• The first Internet database applications came out in 1995.
• XML became relevant to database processing in 1997. 

Characteristics of DBMS

1. Real-world entities

This system is very realistic and takes inspiration from real-world entities for its architecture. It includes some features of behavior and attributes too. An example will be a student as an entity and their stream choice as an attribute. 

2. Relation-based tables

There is a relation between the entity and the attribute which comes out in the form of tables. This allows users to understand the architecture by just seeing the table names.

3. Isolation of data and application

It is a different entity all together where data becomes passive on which the database works. It also takes care of metadata making it easier to process.

4. Less redundancy

There is a rule of normalization that allows splitting a relation in case of redundancy in values. It is a rich and scientific process to reduce the redundancy of data.

5. Consistency

The database is always consistent as there are methods and techniques to do so. 

6. Query Language

It has an inbuilt query language to increase the efficiency of data for manipulation and retrieval. The users have access to use as many filters as possible to retrieve data, something which was not possible traditionally. 

7. ACID Properties

There exists the idea of Atomicity, Consistency, Isolation, and Durability. During transactions, this idea comes in place to manipulate data for multi-transactional environments.

8. Multiuser and Concurrent Access

It supports multi-user environments allowing users to access and manipulate parallelly. There are restrictions while transacting but users don’t know about it.

9. Multiple views

The users can view the same file multiple times. This allows them to look at the database with concentration according to their requirements.

10. Security

There are methods to impose restrictions while accessing data for any use. This is because many users from different teams access it, risking their privacy and security. 

DBMS Users

1. Administrators

They are responsible for maintaining the database by seeing who uses it at what time. Administrators create profiles of the users who can access the data and isolate the users without authorization. They also take care of system licenses, required tools, and other maintenance tasks.

2. Designers

They design the entire database and decide the format that suits them the best. This includes designing entities, relations, views, etc.

3. End Users

They are the ones who use it in the end. They can be just viewers who look at it out of curiosity or analysts trying to study market trends.

4. Application Programmers

They are the ones who write in the programming language to create database interaction.

Applications of DBMS

• The banking sector uses it for customer information like their account details, deposits, etc. 
• Airlines use it for managing reservations and schedule information.
• Universities use it for maintaining student records, registrations, grades, etc. 
• The telecommunication sector uses it for maintaining call records, bills, etc. 
• Finance firms use it for stock information, sales, and other income statements. 
• Sales-related teams use it to keep product and sales information. 
• Manufacturing units use it for inventory management. 
• HR Management uses it for managing the employee’s salary and incentives. 

Types of DBMS

1. Hierarchical DBMS

The data organization is in the form of a tree-like structure. There is a hierarchy in the data storage following a parent-child relationship. And just like this relationship, the parents can have many kids but the kid has only one parent.

2. Network Model

The data organization in this has a complex relationship. They are in a graphical form with several paths. To make it easy, the kid can have more than one parent here.

3. Relational model

It is one of the easiest models that normalizes data in rows and columns. It follows a fixed structure using SQL.

4. Object-Oriented Model

This type of model has data in object forms. The structure it follows has data in class form with both values and operations.

Components of a DBMS

1. Storage engine

This is where all the data goes as it ascites as an interface between the database system and the operating system. It sometimes uses more components to store data at the file system level.

2. Metadata catalog

This is the place where DBMS registers all the data at the time of creation. It uses this catalog to verify data requests and then execute them further. The information related to objects, schemas, programs, security, performance, etc. is also available here.

3. Database access language

It also needs an API to access data and database access language acts as one in this system. This allows data modification and creation while securing them from unauthorized users. SQL is an example of this language.

4. Optimization engine

This engine is to execute requests by database access language by making them actionable commands for data access.

5. Lock manager

It is responsible to manage concurrent access to the same data to ensure that users cannot modify it at the same time.

6. Log manager

It maintains all the records of change in data in the form of a log. This is an efficient and accurate way of maintaining the records as they help in backups and run recoveries.

7. Data utilities

There are a set of utilities to manage database activities like run stats, backup and copy, recovery, load data, etc.

Advantages of DBMS

• It controls database redundancy by storing all the data in one file placed in the database.
• It enables data sharing by giving access to authorized users of the same company.
• It is easy to maintain because of its centralized nature.
• It reduces the time for development and maintenance.
• It always keeps a backup in case of hardware and software failures.
• It supports multiple user interfaces by giving access to many people at a time. 

Disadvantages of DBMS

• The cost of hardware and software is high as it needs speed and large memory to work. 
• It takes up a large size of disks to run efficiently.
• There are more complexity and requirements.
• There is a higher impact of failure as the database is usually in a single system. 

Architecture of DBMS

The architecture of a DBMS system can be central, decentral, or hierarchical. They look at DBMS in different tiers starting from one and ending at three. The architecture without any tier has independent n modules who modify, alter, change and replace by themselves. 

1. 1-tier architecture

The user can have only one entity to access the database and use it. Any changes here will automatically update the data itself. There are no handy tools for the users and this is generally ideal for database designers and programmers.

2. 2-tier architecture

It has an application to access DBMS and it works independently. It manages operation, design, and programming on itself.

3. 3-tier Architecture

It is a very complex design as each tier is different in the database. But it is the most common architecture design of DBMS. The tiers are –

a. Database (Data) Tier

This tier is for query processing languages. The relations between the data and its constraints are also present here.

b. Application (Middle) Tier

The application server and program accessing the data are present at this tier. The user and the data are not aware of each other beyond this tier. This is why this tier becomes a mediator between them.

c. User (Presentation) Tier

The users are operating through this tier and know nothing above this. The multiple views option is also present at this tier generated by the application.

Multiple-tier database architecture allows more modification and independence in terms of functioning.

Popular types of DBMS technologies

• RDBMS is adaptable but is quite expensive.
• NoSQL DBMS works well for loosely defined data structures but needs more application involvement.
• In-memory database management system gives a faster response but takes up more resources. 
• Columnar database management system is best for inventory management with similar data.
• Cloud-based database management system is by a service provider responsible to maintain DBMS. 

Popular DBMS Software

• MySQL
• Microsoft Access
• Oracle
• PostgreSQL
• dBASE
• FoxPro
• SQLite
• IBM DB2
• LibreOffice Base
• MariaDB
• Microsoft SQL Server 

Important DBMS Terms

• Data Manipulation Languages – A programming language to modify and add data in the existing database. SQL and DDL are the two types of data manipulation languages that users use commonly.
• Structured Query Language (SQL) – A type of programming language relevant for tabular database management that is relational in nature.
• Data Definition Language (DDL) – Again a programming language for modifying existing data in the database, mainly in tabular form.
• Primary Key – A unique key to identify a particular file. 
• Foreign Key – A key that the primary key uses to identify the relationship between field and component in the table.

Basic DBMS Commands

• CREATE – Create a database 
• ALTER – Alters the structure of the database
• DROP – Delete objects 
• TRUNCATE – Remove all records
• COMMENT – Add comments to the dictionary
• RENAME – Rename an object
• SELECT – Retrieve data 
• INSERT – Insert data in the table
• UPDATE – Updates existing data 
• DELETE – Delete all records 
• MERGE – Upsert operation 
• CALL – Call a PL/SQL 
• EXPLAIN PLAN – Interpretation of the data 
• LOCK TABLE – Concurrency Control

DBMS vs. Flat File

Paradigm Shift from File System to DBMS

Apart from the differences above, there were many reasons why the file system did not work –

• The redundancy in data was tough here as copying of data did not lead to an update at various sections. 
• There was inconsistency of data because of typing errors and irregular updates.
• It was difficult to access data as users found it difficult to know the exact file location. 
• The unauthorized access was easier due to fewer security measures. 
• There was no concurrent access as only one user could access it at a time. 
• No backup and recovery were leading to files being lost forever.

Database Schema

A schema is a structure that gives us an overview of the entire database logically. It mentions the data organization technique and how the relationship between the component is formed.

It has a database description which the users see through a schema diagram. But all elements cannot be part of this diagram. It is of two types –

1. Physical Database Schema has the information regarding the actual storage of data and how secondary storage will make space for them.

2. Logical Database Schema has information regarding logical constraints of the data in the form of tables, views, and integrity constraints.

The database designers are the ones designing the schemas for programmers. It is before the database is put in place and thus doesn’t have any content in it.

The logical database schema is the database instance that has all information regarding the operational database. It is flexible and changes with a database ensuring that the users see the most updated version.

Conclusion

These were some of the important points about database management systems. This might look hard to understand at first but is quite easy if you read carefully. RRB, Insurance exams, Bank exams, etc. have computer aptitude subjects in which basic concepts are present.

This topic is also a part of the syllabus making it important for all the applicants. The concept has many subheadings under it to give you a complete understanding of the topic.