Unit 8 · Session 3

Database Management Systems

IT 231: IT and Applications (BBA)

🎯 Learning Objectives

By the end of this session, 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.
✅ Provide examples of popular DBMS.

Database Management Systems (DBMS) Explained

What is a DBMS?

A Database Management System (DBMS) is a software program that enables users to create, maintain, and control access to a database.

Think of it like a librarian for a huge library:

The Library is the Database (stores information).
The Books are the Data.
The Librarian is the DBMS (manages, organizes, and controls access).

🤔 "Can't I Just Use Excel?"

	Spreadsheet (Excel)	DBMS (e.g. MySQL)
Size	Max 1,048,576 rows per sheet	Billions of rows
Many users at once	Edit conflicts, "file is locked"	Thousands at the same time, safely
Rules on data	Easy to type "twenty" in an Age column	Data types and constraints reject bad data
Security	Whole file: open or not	Permissions per table, even per column
Recovery	Hope you saved a copy	Automatic backups and logs

Excel is great for analysis. A DBMS is built for shared, reliable storage.

The DBMS: Your Data Gatekeeper

It acts as an interface between users/applications and the physical database.

Users & Applications

e.g., Website, Mobile App, Analyst

➡️ DBMS ⬅️

(The Manager)

Database

e.g., Tables, Records, Files

The DBMS ensures data is consistently organized and easily accessible, preventing chaos.

Major Functions of a DBMS ⚡

A DBMS handles several critical tasks to manage the database effectively:

📜 Data Definition: Defining the database structure.
✍️ Data Manipulation: Interacting with the data.
🔐 Data Security & Control: Protecting the data.
🔄 Concurrency Control: Managing simultaneous access.
💾 Backup & Recovery: Ensuring data safety.

Function 1: Data Definition (The Blueprint) 📜

The DBMS uses a Data Definition Language (DDL) to build the database's structure.

DDL in Action

This is how an administrator defines the "rules" for the data.

CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  EnrollmentDate DATE
);

DDL is used to create tables, define fields, and set constraints.

📜 Worked Example: Rules That Reject Bad Data


CREATE TABLE Orders (
  OrderID    INT PRIMARY KEY,
  Phone      CHAR(10) NOT NULL,
  Amount     DECIMAL(10,2) CHECK (Amount > 0),
  OrderDate  DATE NOT NULL
);

Which of these inserts will the DBMS reject?

1. (1, '9841000000', 850.00, '2026-10-15') ✅ Accepted

2. (1, '9851000000', 500.00, '2026-10-15') ❌ OrderID 1 already exists (primary key)

3. (2, '9861000000', -200.00, '2026-10-15') ❌ The amount must be greater than 0 (CHECK)

4. (3, NULL, 300.00, '2026-10-15') ❌ The phone number can't be empty (NOT NULL)

Function 2: Data Manipulation (The Tools) ✍️

A Data Manipulation Language (DML) allows users to work with the data.

For relational databases, the standard DML is SQL (Structured Query Language).

Core DML Operations:

Retrieve (Read)
Insert (Create)
Update (Modify)
Delete (Remove)

Simple SQL Examples:

-- Retrieve data
SELECT * FROM Students;

-- Insert a new record
INSERT INTO Students VALUES (...);

-- Delete a record
DELETE FROM Students WHERE StudentID=101;

🧪 Activity: Write the SQL

Table Products(ProductID, Name, Price, Stock)

1. Show all products cheaper than Rs 1,000.
SELECT * FROM Products WHERE Price < 1000;

2. Add a new product: ID 5, "Dhaka Topi", Rs 1,200, 30 in stock.
INSERT INTO Products VALUES (5, 'Dhaka Topi', 1200, 30);

3. Change the price of product 2 to Rs 950.
UPDATE Products SET Price = 950 WHERE ProductID = 2;

4. Remove every product that is out of stock.
DELETE FROM Products WHERE Stock = 0;

⚠️ If you forget the WHERE in #3 or #4, the change applies to every row!

🎬 Forgetting WHERE

Functions 3 & 4: Control & Consistency

🔐 Data Security & Control

The DBMS acts as a security guard.

Prevents unauthorized access.
Administrators can grant or revoke permissions.
Example: A student can view their own grades, but a registrar can view all grades.

🔄 Concurrency Control

Ensures smooth operation in multi-user environments.

Prevents conflicts when multiple users try to change the same data at the same time.
Imagine two bank tellers trying to update the same account balance simultaneously. The DBMS prevents this from causing errors!

🧮 Worked Example: The Lost Update

Hari's balance is Rs 10,000. At the same moment, two transactions run:

Teller A: withdraw Rs 2,000

Writes 8,000

ATM B: deposit Rs 5,000

Writes 15,000 (overwrites A!)

Final balance: Rs 15,000. The correct answer is Rs 13,000, so the bank just lost Rs 2,000. A DBMS prevents this with locking: B must wait until A finishes.

🔒 Transactions: All or Nothing (ACID)

Sending Rs 5,000 from Sita to Ram takes two steps: subtract from Sita, then add to Ram. What if the power fails in between?

A: Atomicity. Both steps happen, or neither does.

C: Consistency. The rules still hold, so the total money stays the same.

I: Isolation. Transactions running at the same time don't interfere (no lost updates).

D: Durability. Once confirmed, it survives a crash.

Every eSewa transfer or bank payment you make is a transaction.

🎬 All or Nothing

Function 5: Backup & Recovery (The Safety Net) 💾

Protects data from loss due to system failures, errors, or disasters.

Backup

The DBMS provides tools to create periodic copies (backups) of the database.

Recovery

If the system crashes, the DBMS can use the backups and transaction logs to restore the database to a consistent state.

This function is absolutely critical for business continuity.

🌍 Real Case: GitLab's Five Backups That Failed (2017)

31 January 2017: a tired engineer at GitLab, a code-hosting company, ran a delete command on the wrong database server. It erased about 300 GB of live data.
GitLab had five backup or replication methods. When they tried them, none worked as expected.
They restored from a copy that happened to be about 6 hours old, so roughly 6 hours of users' work was lost. They even livestreamed the recovery.

Lesson: A backup you've never tested isn't a backup. Follow the 3-2-1 rule: keep 3 copies, on 2 types of media, with 1 off-site.

👩‍💻 The Database Administrator (DBA)

The DBMS is the gatekeeper software. The DBA is the person who runs it.

🏗️ Build

Install and upgrade the DBMS, and design tables with the developers.

🔐 Protect

Create user accounts and permissions, and run and test backups.

⚡ Tune

Monitor performance, speed up slow queries, and plan storage growth.

Every Nepali bank, telecom and wallet company employs DBAs. It's a well-paid IT career.

Popular DBMS in the Wild 📊

Different systems are chosen based on cost, scale, and features.

Open-Source

MySQL: The world's most popular open-source relational DBMS. Powers much of the web (WordPress, Facebook).
PostgreSQL: A powerful, advanced open-source object-relational DBMS known for its reliability and feature set.

Commercial

Microsoft SQL Server: A popular and comprehensive commercial DBMS from Microsoft, widely used in enterprise environments.
Oracle Database: A leading commercial relational DBMS from Oracle, known for its high performance and scalability in large corporations.

🧪 Activity: Choose the DBMS

1. A small Pokhara homestay wants a cheap booking website. → MySQL or PostgreSQL (free, open-source)

2. A large bank wants its core system with 24/7 vendor support. → Oracle or SQL Server (commercial, supported)

3. A company that already runs everything on Microsoft products. → SQL Server

4. A mobile app that must keep data on the phone while offline. → SQLite, a tiny database built into every Android and iPhone

DBMS in Action: A Nepali Context

Scenario: A Digital Wallet like eSewa or Khalti

How does a DBMS (like PostgreSQL or MySQL) power these services?

Data Definition: Defines tables for `Users`, `Transactions`, `Merchants`, and `BankAccounts`.
Data Manipulation: When you send money, an `INSERT` statement adds a record to the `Transactions` table, and `UPDATE` statements change the balances in user accounts.
Security: Manages your login credentials and ensures you can only access your own transaction history.
Concurrency: Safely processes thousands of payments and transfers simultaneously across Nepal without data corruption.
Backup: Regularly backs up all transaction data to prevent loss in case of a server failure.

QR Payment, Like Fonepay

⚡ Quick Quiz: DDL or DML?

CREATE TABLE DDL

SELECT DML

ALTER TABLE DDL

UPDATE DML

DROP TABLE DDL

DELETE DML

Bonus: GRANT and REVOKE belong to a third group, DCL (Data Control Language), which manages who can access what.

Summary & Key Takeaways

A DBMS is the essential software used to create, manage, and control a database.
Its key functions are Data Definition (DDL), Data Manipulation (DML), Security, Concurrency Control, and Backup/Recovery.
SQL is the standard language for interacting with relational databases, covering both DDL and DML operations.
Popular DBMS include open-source options like MySQL and PostgreSQL, and commercial systems like MS SQL Server and Oracle.

💬 Discussion Questions

Your eSewa transfer shows "failed", but the money left your account. Which ACID property should protect you, and what should happen next?
Why would a bank pay for Oracle when MySQL is free?
After the GitLab story, how would you check that your own phone and laptop backups actually work?

Exam Practice 📝

Short answer

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

Long answer

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

Revise the answers in the full note linked on the last slide.

Image & Video Credits

Images

LOC Main Reading Room: Carol M. Highsmith, public domain, via Wikimedia Commons
ATM in Mizuho bank: Syced, CC0, via Wikimedia Commons
The architecture of memory, inside the StorageTek tape library: Jorge Franganillo, CC BY 4.0, via Wikimedia Commons
NOIRLab HQ Server Racks: NOIRLab/NSF/AURA/T. Slovinský, CC BY 4.0, via Wikimedia Commons
BalticServers data center: BalticServers.com, CC BY-SA 3.0, via Wikimedia Commons
GCash QR: Reyrefran, CC BY-SA 4.0, via Wikimedia Commons

Videos (YouTube)

Bro Code, "MySQL: How to UPDATE and DELETE data from a TABLE" (136–203 s)
Be A Better Dev, "What is a Database Transaction? Be ACID compliant!" (115–205 s)

Thank You

Questions before we wrap this hour?

Next: Unit 8 · Session 4 — Architectures, Applications, and Cloud Databases

Course Home · Next session

📖 Read the full note

📖 Related chapter notes

Read first, then revise with this deck:

u8-c5-database-management-systems

Unit 8 Session 3: Database Management Systems | IT 231 BBA Slides