Learning Objectives

By the end of this chapter, you will be able to:

  • Define a Database Management System (DBMS) and its purpose.
  • Describe the major functions of a DBMS.
  • Understand the role of SQL as the language of databases, with simple examples.
  • Identify the components and users of a database system.
  • Provide examples of popular DBMS and state the advantages and disadvantages of a DBMS.

What is a Database Management System (DBMS)?

A Database Management System (DBMS) is a software program that enables users to create, maintain, and control access to a database. It acts as an interface between the database and the users or application programs, ensuring that data is consistently organized and remains easily accessible.

flowchart LR
  U1[End users] --> APP[Application programs]
  U2[Analysts] --> DBMS[DBMS]
  APP --> DBMS
  DBA[Database administrator] --> DBMS
  DBMS --> DB[(Database)]

Database vs DBMS: the database is the organized collection of data itself; the DBMS is the software used to create, access, and manage it. Together with the hardware, applications, and users, they form a database system.

Major Functions of a DBMS

A DBMS performs several critical functions:

  1. Data Definition: The DBMS provides a Data Definition Language (DDL) that allows administrators to define the structure of the database, including creating tables, defining fields, and setting rules.

  2. Data Manipulation: The DBMS provides a Data Manipulation Language (DML) that allows users to interact with the data. This includes:
    • Retrieving data (querying).
    • Inserting new data.
    • Updating existing data.
    • Deleting data. The standard DML for relational databases is SQL (Structured Query Language).

  3. Data Security and Control: The DBMS provides a security system to prevent unauthorized access to the database. It allows administrators to grant different levels of permissions to different users.

  4. Concurrency Control: In a multi-user environment, a DBMS ensures that multiple users can access and modify the data at the same time without interfering with each other or causing inconsistencies.

  5. Backup and Recovery: The DBMS provides tools for backing up the database and for recovering the data in case of a system failure.

  6. Data Integrity: The DBMS enforces rules (constraints) such as “every account must have a unique number” or “quantity cannot be negative.”

  7. Data Dictionary: The DBMS keeps “data about data” (metadata): table names, field names, data types, and relationships.

SQL examples

Purpose SQL statement Type
Create a table CREATE TABLE Customers (ID INT PRIMARY KEY, Name VARCHAR(50), City VARCHAR(30)); DDL
Add a record INSERT INTO Customers VALUES (1, 'Sita Sharma', 'Pokhara'); DML
Retrieve records SELECT Name FROM Customers WHERE City = 'Pokhara'; DML
Change a record UPDATE Customers SET City = 'Kathmandu' WHERE ID = 1; DML
Remove a record DELETE FROM Customers WHERE ID = 1; DML
Give permission GRANT SELECT ON Customers TO sales_user; DCL (control)

Concurrency example

Two bank staff try to update the same account at the same moment: one deposits Rs 5,000 and the other withdraws Rs 2,000 from a balance of Rs 10,000. Without control, one update could overwrite the other, leaving Rs 15,000 or Rs 8,000 instead of the correct Rs 13,000. The DBMS locks the record so the updates happen one after another, and each complete operation is a transaction that either fully succeeds or is fully undone.

Users of a Database System

  • Database Administrator (DBA): designs security, manages backups, tunes performance, and controls access.
  • Database designers: decide tables, fields, and relationships.
  • Application programmers: write programs (such as a billing app) that use the database.
  • End users: clerks, managers, and customers who use the data through forms, reports, and apps.
  • MySQL: The world’s most popular open-source relational DBMS.
  • PostgreSQL: A powerful, open-source object-relational DBMS.
  • Microsoft SQL Server: A popular commercial relational DBMS from Microsoft.
  • Oracle Database: A leading commercial relational DBMS from Oracle.
  • Microsoft Access: A desktop DBMS suited to small offices and single users.
  • MongoDB: A popular NoSQL (document) database.

Advantages and Disadvantages of a DBMS

Advantages Disadvantages
Reduced redundancy and better consistency Cost of software, hardware, and skilled staff
Data sharing among many users Complexity of design and administration
Strong security and integrity rules A failure can affect the whole organization
Backup, recovery, and concurrency control Performance tuning needed for large workloads
Easy querying and reporting with SQL Staff training required

Nepal Context

Nepali banks run core banking systems on large commercial DBMS such as Oracle; colleges and hospitals often use MySQL or SQL Server behind their management systems; and many local software firms build billing and inventory apps on MySQL or PostgreSQL because they are free and reliable.

Summary

A Database Management System (DBMS) is the essential software for managing a database. It provides the tools for defining the database structure (DDL) and for manipulating the data within it (DML), most commonly through the language SQL. A DBMS is also responsible for critical administrative tasks, including ensuring data security and integrity, managing concurrent access through locking and transactions, maintaining a data dictionary, and handling backup and recovery. Database administrators, designers, programmers, and end users all rely on it. It is the gatekeeper and manager of an organization’s most valuable data.

Key Takeaways

  • A DBMS is the software used to manage a database.
  • Its key functions include data definition, manipulation, security, integrity, and concurrency control.
  • SQL is the standard language for interacting with relational databases.
  • The DBA is responsible for managing and securing the database.
  • Popular DBMS include MySQL, PostgreSQL, MS SQL Server, and Oracle.

Discussion Questions

  1. What is the difference between a database and a DBMS?
  2. Why is concurrency control important in a multi-user database?
  3. What are some of the security features you would expect to find in a DBMS?

Exam-Style Questions

Short answer

  1. Define DBMS. Give four examples.
  2. Differentiate between DDL and DML.
  3. Who is a DBA? List three responsibilities.

Long answer

  1. Explain the major functions of a DBMS.
  2. What is SQL? Write SQL statements to create a table, insert a record, and retrieve records, and explain each.