DBMS Tutorial – Learn Database Management System

A database is a collection of interrelated data, such as user data, company data, university data, etc. These collections can store data in a structured format, which allows developers to access and update data easily. Databases are important for storing large amounts of data and retrieving them whenever needed. A system created to manage these data efficiently is called a database management system.

In this DBMS tutorial, you will learn what a database management system is, the key features, characteristics, components, examples, pros, cons, etc. of DBMS. You will also learn different database languages and some advanced concepts like normalisation in DBMS, ACID properties of DBMS, etc.

What is DBMS?

A database management system is a set of tools, software, and programming languages that help to create, control, and manage databases. It is a middleware between the user and data, allowing for easy access, manipulation, and retrieval of information.

Mainly, DBMS is used to insert data in the database, and easily retrieve and update the data. Moreover, DBMS also provides various tools for data administration. These tools also allow control of the access to data. For example, you can give limited access to data to your employee based on their role rather than giving access to the whole database.

The commonly used DBMS software are MySQL, SQLite, PostgreSQL, Microsoft Access, etc.

The concept of database management has evolved significantly over the decades. Initially, in the early 1950s, the data was stored in files and managed by the operating system. For example, to create a database for a university, it was required to create different files for administration tasks, store student and teacher data, etc. This method was prone to data duplication and complex to manage large databases. So, there was a need for a structured database.

The modern era of databases began in the 1960s with the advent of computer technology. In 1960, Charles W. Batchman and his team developed the first database management system called Integrated Data Store (IDS). This IDS is a hierarchical DBMS that stores the data in a tree-like structure. It has a parent-child relationship between multiple data records.

The significant breakthrough came in 1970 when Edgar F. Codd, an IBM researcher, introduced the relational model. This model revolutionised database management by focusing on data retrieval based on content rather than following navigational paths.

Evolution of Database Management Systems

Now, let’s look at the evolution of the database management system every decade starting from 1960.

Key Features of DBMS

A Database Management System (DBMS) streamlines the process of managing and accessing large volumes of data. These features ensure that data is organised, secure, and easily retrievable. Here are some of the key features:

• Data Storage Management: A database management system allows for storing a large amount of data in a structured format. It uses various techniques like data compression and indexing to optimise storage space and retrieval speed.
• Data Security: A DBMS ensures the security of the data, which is crucial to protecting it from various cyber-attacks. This database management system uses user authentication, access control, and encryption to protect data from unauthorised access and breaches.
• Data Integrity: One of the key features of the DBMS is it helps in maintaining the data integrity by enforcing rules and constraints on the data. It ensures the consistency of the data inserted into the database.
• Backup and Recovery: The DBMS helps you backup data. This ensures that data can be restored during hardware failures, system crashes, or other data loss incidents. This system also allows you to take an automated backup of the database and recover it when hardware failure occurs or the database gets corrupted.
• Concurrency Control: Most of the DBMS offer a concurrency control mechanism. It allows multiple users to work on the same database parallelly. Concurrency control mechanisms prevent conflicts that arise when multiple users try to access or modify the same data simultaneously.
• Data Independence: DBMS is separate from the application. So, it allows developers to make changes to the database without affecting the application. Data independence ensures that any changes made in the database don’t affect the application.
• Data Sharing: You can use a single database to share data across multiple applications, and it is also called a shared database.
• Improved performance: The DBMS allows you to write queries to control the data. By executing a few lines of query, you can make changes to the whole database. Furthermore, indexing and other mechanisms help improve database performance by making data access faster.

These features collectively make DBMS an indispensable tool for managing large and complex datasets, ensuring data is accurate, secure, and readily available.

Key Components of DBMS

A Database Management System (DBMS) contains various components to manage and control the data. Here, we have covered some key components of the DBMS.

• Database/Storage Engine: The storage engine is a core component of the DBMS. It is responsible for data storage management, retrieving and updating the data. It is also used to process user queries and interacts with the storage system to fetch the requested data.
• Database Schema: The database schema is a blueprint of the database. It includes the tables, entities, relationships between multiple entities, indexes, and other components. Basically, it is a logical structure for the database.
• Query Processor: The query processor executes database queries written in a query language such as SQL. It parses the query, validates it, and generates the low-language code to interact with the database.
• Transaction Manager: This component ensures that database transactions are processed reliably and adhere to the ACID properties (Atomicity, Consistency, Isolation, Durability). The transaction manager handles the execution of transactions, ensuring that they are completed successfully or rolled back in case of errors and maintaining data integrity.
• Concurrency Control Manager: This component manages the concurrency in the database. It makes sure changes made by multiple users in the database are concurrent and allows multiple transactions to occur simultaneously without interfering with each other.
• Backup and Recovery Manager: It handles all backup and recovery-related tasks.
• User Interface: It provides a graphical user interface(GUI) to manage the database. You can insert, delete, and update data by using the GUI and without writing queries.
• Security Manager: The security manager handles authentication, authorisation, and access control to ensure that only authorised users can access and modify the data.
• Log Manager: Any changes are made in the database, which is called the log. The log manager keeps track of all log records and stores them. These logs can be used in the future to examine database changes or fix errors.

Characteristics of DBMS

Here, we have covered some of the key characteristics of DBMS:

• DBMS uses indexing, query optimisation, and caching to efficiently retrieve the data.
• It can automatically back up data and recover it after a hardware crash or when an error occurs.
• DBMS has ACID properties, which ensure that data is consistent in case of failure.
• It reduces the complexity of managing data.
• It implements role-based access, authentication, etc. for the security purpose of the data.
• It allows for creating customised views for different users without altering the underlying data.
• A graphical user interface (GUI) allows management of data without writing queries.

Applications/Use Cases of DBMS

These days, all businesses, from small to large, are required to store data in a database. The DBMS makes it easy to manage and access the data. Here are some real-time use cases of database management systems.

• Banking: The bank has millions of customers and they need to store the data of each customer. The data can be account details like account number, name, signature, PAN card number, Adhar card, account balance, transactions, etc.
• Social Media Sites: Nowadays, most people have smartphones and accounts on social media sites like Instagram, Facebook, etc. These companies store the user’s data, such as authentication credentials, username, profile info, all friends of each user, posts of the particular user, chat history of the user with other users, etc.
• Medical: In the medical line, DBMS is used to store the details of patients and surgeons. It also manages the inventory of medicine in the hospitals or medical stores.
• Accounting and Finance: Multiple software programs are available for accounting and finance. These programs use a database to store bank details, stock details, other transaction details, etc.

Examples of DBMS

There are multiple DBMS available in the market. Each serves a different purpose, and you can choose based on your organisation’s requirements. Here, we have covered some of the most popular DBMSs with an in-depth overview.

MySQL Database

MYSQL is an open-source relational database management system (RDBMS) that stores structured data in table format. It is written in the C and C++ programming languages.

MySQL offers high performance and reliability and supports the SQL query language. It is mainly used for small—to medium-sized applications to store structured data. It is used by Facebook, Twitter, and some famous E-commerce websites.

PostgreSQL

PostgreSQL is also an open-source object-relational DBMS known for its strong standards of compliance and extensibility. It is mainly used with applications requiring complex queries, such as financial systems.

The main features of PostgreSQL are that it provides support for JSON and XML data types, robust concurrency control, and custom functions.

Oracle Database

Oracle Database is a powerful enterprise-grade relational database management system known for its robust performance, scalability, and extensive feature set. It is mainly used for enterprise-level applications.

Oracle also provides the support for SQL. It also offers high security.

Microsoft SQL Server

Microsoft SQL Server is a Relational Database Management System (RDBMS) developed by Microsoft in 1989. It is widely used with Microsoft products and is known for easy integrations with them. The main benefit of using the Microsoft SQL Server RDBMS is that it is available in multiple languages, such as English, Spanish, German, etc.

The main key features of Microsoft SQL Server are that it provides support for T-SQL, advanced analytics, in-memory processing, and robust security features.

MongoDB

MongoDB is a non-relational database developed in 2009. It stores data in JSON-like documents containing multiple objects. For each schema, you can create a single object to store the data in the MongoDB database. It is widely used to store unstructured data or with applications that require changing the database structure frequently.

Using the MongoDB database, you can easily perform horizontal scaling, as it allows you to add new attributes in JSON schema.

Amazon DynamoDB

Amazon DynamoDB is a NoSQL database service. It is fully managed by Amazon Web Services(AWS). This web service runs in the cloud and facilitates database management. It is well-known for its high availability and scalability of databases.

Amazon DynamoDB is mainly used with applications that require real-time data processing. Amazon fully manages it, so users don’t need to worry about database hosting,  managing security, and taking the database backup.

Also read: Difference Between DBMS and RDBMS

Benefits of Using a DBMS

The main advantage of using a DBMS is that it allows you to store data in a structured manner and easily access it using queries. However, there are some more advantages of using a DBMS.

• Data Concurrency: The DBMS allows multiple users to work on a single database simultaneously and ensures that data are concurrent even if it is changed by multiple users.
• Data Integrity: The DBMS allows you to enforce data integrity constraints, such as validating constraints on values that are inserted in the database.
• Data Security: A DBMS provides data security by encrypting it and controlling who can access it.
• Backup and Recovery: A DBMS has a mechanism for automatically backing up the database, which can be recovered in case of system failure.
• Data Sharing: The DBMS allows you to create different views, allow role-based access, and share data with multiple users to work in a collaborative environment.
• Easy Maintenance: It allows you to maintain the database easily.

Drawbacks of DBMS

While DBMS offers various advantages, it also comes with drawbacks. Here, we have covered some of the disadvantages of the DBMS.

• Complexity: It can be difficult to set up and manage a database if you lack the necessary skills. Developers need knowledge of query languages like SQL, PostgreSQL, etc., to do so.
• Cost: The cost of managing the DBMS hardware can be high, especially for complex systems. However, small—to medium-sized businesses can use the shared database and pay as they use it.
• Impact of Failure: If companies have their database servers instead of using cloud databases, it is very important to take regular backups for quick recovery from a system failure.
• Training and Support: Effective use of a DBMS requires training for database administrators, developers, and end-users. This training can be time-consuming and costly.

Advantages of DBMS Over File System

The file system is a traditional database management system in which data is stored in the form of files. There were many issues with using the file system, which were overcome by introducing the DBMS. Here are some of the advantages of DBMS over file systems.

• Data Redundancy: The redundant data means duplicate data. In the file systems, it is possible that multiple files have the same data, which increases the storage. This issue is resolved with DBMS.
• Data inconsistency: When using the file system, if the same data is changed by multiple users, it might be possible that changes are not reflected in all files, but DBMS ensures that all related data are changed consistently.
• Data Searching: Developers are required to write a file-handling program to search for particular data in the file system. With DBMS, searching, filtering, and sorting data can be easily done with the Query languages.
• Maintenance: DBMS is easy to maintain over the file system.

Types of Data Models in DBMS

Data models in a Database Management System (DBMS) define how data is structured, stored, and accessed. A DBMS can support the following types of database models.

Hierarchical Model

In a hierarchical database model, data are organised in a tree-like structure, where each record has a single parent element and can have multiple children. So, it represents the parent-child relationship.

The one-to-many relationship is also a hierarchical database model. This model is mainly used in file systems or classifications. It is simple and efficient for storing hierarchical data, but it can be complex to access and change data.

Network Model

The network model is an expanded version of the hierarchical model. It stores the data in the graph format, where the node represents the data record and the edges represent the relationship between the data records.

The network model is ideal for situations where we need to imply many-to-many relationships between data. In this model, a single parent can have multiple children, and a single child can have multiple parents. Also, there can be several paths to reach a particular record.

Relational Model

The relational model is one of the most used database models. It stores data in a table in a row and column format. Here, rows are called tuples, and columns are called attributes. Multiple tables can be linked using primary keys and foreign keys.

Oracle, MySQL, Microsoft SQL Server, etc., work based on the relational database model (DBMS). This DBMS provides the flexibility to use query languages like SQL to process the data.

No-SQL Data Model

The No-SQL data model stores the data in the key-value pairs and unstructured format. Mainly, it uses the JSON or XML format to store the data. It is ideal where horizontal scaling of the database is required.

DBMS like MongoDB, DynamoDB, CouchDB, etc. use the NO-SQL data model. This kind of data model is useful for content management systems and e-commerce websites, where you need to create dynamic schemas.

Object-oriented Model

The object-oriented database model integrates the object-oriented programming concepts with the database technology. Here, data is represented as objects. It is suitable for applications requiring complex data representations, such as computer-aided design (CAD), multimedia applications, and software engineering.

DBMSs like ObjectDB and db4o use the object-oriented model. The main benefit of this model is that it supports complex data types. However, it has a steeper learning curve, which makes it complex to learn.

Entity-relationship (ER) Model

The entity-relationship model is used to define the logical structure of the database. It uses the ER diagram to develop the database structure. In the ER diagram, the main object is represented as an entity, and each entity can have multiple attributes of different types. Later, the ER diagram can be converted into a table format. Each table represents a single entity, and it has a number of columns equal to the number of attributes of the entity. Moreover, each table is connected using the foreign key, which represents the relationship between two entities.

Float data Model

The float data model uses a single two-dimensional array to store the data, where the single record is stored in a single row. To perform any operation like accessing, modifying, etc., the data is required to read the whole array, which is very inefficient. So, the float data model is not widely used with real-time applications.

Database Languages

Database languages are special kinds of programming languages that are used to create, manage, access, and manipulate databases. They are also used to create database structures, control access to the database, and handle database transactions. Here are the four primary database languages.

Data Definition Language

Data Definition Language (DDL) is used to define and manage database schema and structure. DDL commands help create, alter, and delete database objects such as tables, indexes, and views.

• CREATE: It is used to create a database.
• ALTER: It is used to change the structure of the database.
• DROP: To delete the database.
• TRUNCATE: To remove all records, excluding the table from the database.
• COMMENT: To add comments with data.
• RENAME: To rename the database record.

Data Manipulation Language

The data manipulation language is used to manipulate the database like selecting, inserting, updating, etc., data in the database table. Here are some common commands of SQL which are used to manipulate the database:

• SELECT: To fetch the data from the database.
• INSERT: To insert new records in the database.
• UPDATE: To update existing records.
• DELETE: For deleting the particular records from the table.

Data Control Language

Data Control Language (DCL) is used to control access to data in the database. It includes commands to grant and revoke permissions to users and roles.

• GRANT: To grant different kinds of access to different users.
• REVOKE: To remove the permission from the user.

Transactional Control Language

The transactional control language is used to manage transactions like commits and rollbacks to the database. Here are some common SQL commands that are used to manage transactions in the database.

• Roll Back: To cancel or undo changes in the database.
• Commit: To apply changes in the database.
• Save Point: To save the data temporarily in the database.

Normalisation in DBMS

Normalisation is a very important concept in this DBMS tutorial. It is a way of arranging data in a database so that it has no redundancy as well as dependency. It includes breaking up a database into two or more tables and establishing links between them to maintain data integrity and efficiency. The main purpose is to reduce data duplication and increase data reliability. Here are two objectives of normalisation in DBMS.

• Removing Duplicate Data: The normalisation of the database ensures that redundant data are removed.
• Ensure Data Dependencies Maintained: Keeps related data together, ensuring logical data relationships are maintained.

Normalisation of the database can be done using the normal forms. Here, we have covered the first three normal forms.

First Normal Form (1NF)

The table contains only atomic values if it is in its first normal form. It means the table can’t contain attributes having multiple values. If it does, you need to separate the record into two different records.

For example, consider the below table. It contains the student_phone multi-valued attribute.

+91 45345345

After converting the above table in 1NF is:

Second Normal Form (2NF)

To convert the table in the 2NF, it should be in 1NF. Furthermore, all non-key attributes should be fully functional and dependent on the primary key. To achieve 2NF, you can create multiple tables from a single table.

For example, in the below table, each teacher can teach multiple subjects.

Here, we have converted the above table into 2NF.

Here, a duplicate phone number is removed from the database.

Third Normal Form (3NF)

A relation will be called in the third normal form if it is in the 2NF and doesn’t contain any transitive partial dependency.

Ensuring that no transitive dependency exists, meaning non-key attributes should not depend on other non-key attributes.

ACID Properties in DBMS

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties are essential for ensuring reliable processing of database transactions. Let’s understand each property one by one.

Atomicity

Atomicity ensures that each transaction is treated as a single unit. Either all transactions are completed, or none of them is. It also ensures that partial updates do not occur.

For example, in the banking system, if a transaction fails, the system ensures that neither money nor data is credited or debited but that the data remains in its original state.

Consistency

Consistently ensure that a transaction brings the database from one valid state to another valid state, preserving the integrity constraints specified by the database after completing the transaction.

For example, in the baking system, the total amount of money should be constant even after making a transaction.

Isolation

Isolation ensures that each transaction occurs independently without affecting the other.

For example, if person A transfers money to person B and person B transfers the money to person C concurrently, each transaction occurs independently, preventing data inconsistency.

Durability

Durability ensures that after completing the transactions, the final data is stored in the database permanently. This guarantees that the results of the transaction are never lost.

For example, after a successful money transfer, an updated balance is stored in the database, and it can’t be undone due to events like system or power failure.

Future of DBMS

The DBMS is continuously evolving by integrating AI, ML, blockchain, etc. with it. Several key trends and developments are expected to drive the future of DBMS, making them more robust, flexible, and efficient.

• Cloud-Based Databases: Cloud-based databases are becoming more popular due to their cost-effectiveness and scalability. In most cases, cloud-based databases are managed by the service providers. Amazon RDS, Microsoft Azure, etc., are good examples of cloud-based databases.
• AI and Machine Learning Integration: AI-driven databases, such as Oracle Autonomous Database, are capable of self-managing, self-securing, and self-repairing. These capabilities allow for automated performance tuning, predictive maintenance, and advanced data analytics, reducing the need for human intervention and improving overall efficiency.
• Distributed Databases: They are the most popular and widely used due to their high availability and fault tolerance. Systems like Google Spanner and Amazon Aurora offer globally distributed solutions that can handle large-scale applications across multiple regions.
• Multi-Model Databases: The multi-model database allows the use of multiple data models like relational, non-relational, etc., within a single database. It can be used in complex databases.

Why Learn DBMS?

Learning DBMS is very important as each application uses the database. So, it is always important to understand the system that helps in managing and manipulating the data.

DBMS is fundamental for managing and organising large volumes of data efficiently. It ensures data integrity, security, and availability, which are critical for any organisation. Learning DBMS equips you with the skills to design, implement, and manage databases that support business operations and decision-making processes.

Furthermore, learning DBMS is also a strategic investment in your career, as job openings for database management-related roles are continuously increasing.

Career Opportunity of Learning DBMS

The DBMS opens up the door for a wide range of career opportunities. Here are some high-paying job roles, and you can choose any of them as your career.

• Database Administrator (DBA): DBAs are responsible for managing the whole database, including upgrading it, handling its security, and so on.
• Data Architect: The data architect is responsible for designing the database, including creating the database structure and designing the database schemas.
• Data Engineer: The data engineer is responsible for developing the database designed by the data architect.
• Database Manager: They maintain the database, and perform operations like normalising the database.

Conclusion

Understanding and mastering Database Management Systems (DBMS) is essential for anyone involved in managing data. DBMS simplifies data management by providing structured storage, ensuring data integrity, and offering secure access and retrieval methods. It is a critical tool in various industries, from banking and social media to healthcare and finance, where data plays a vital role in operations and decision-making. As we’ve discussed in this DBMS tutorial, learning about DBMS equips you with valuable skills that are highly sought after in the job market, providing numerous career opportunities such as Database Administrator, Data Architect, and Data Engineer.

The future of DBMS looks promising with advancements in cloud computing, AI integration, and distributed databases. These technologies will continue to evolve, offering more robust, scalable, and efficient ways to manage data. Whether you are a student, a professional looking to enhance your skills, or someone interested in data management, learning DBMS is a strategic investment. For this, you can pursue the Accelerator Program in Business Analytics and Data Science at Hero Vired.