MySQL + SQL · Lesson 5

Database Management System (DBMS) Tutorial

What is DBMS?

DBMS (Database Management System) is a software system that enables users to define, create, maintain and control access to a database. It acts as an interface between the user/application and the actual data stored on disk.

Without DBMS, data is stored in flat files — no structure, no security, no sharing. DBMS brings order, control and intelligence to data management.

Popular DBMS Software: MySQL, Oracle Database, Microsoft SQL Server, PostgreSQL, SQLite, MongoDB (NoSQL).

Real analogy: Think of DBMS as a librarian. The library (database) has thousands of books (data). The librarian (DBMS) knows where every book is, who can take it, tracks issued books, and recovers lost books — you don't need to search the entire library yourself.

Components of DBMS

Component	Role
Hardware	Physical devices — server, hard disk, RAM where database is stored
Software	The DBMS software itself (MySQL, Oracle) + OS + application programs
Data	Actual data stored in tables, plus metadata (data about data — structure info)
Procedures	Rules and instructions for using and managing the database
Users	Database Administrator (DBA), End Users, Application Programmers
Query Language	SQL (Structured Query Language) — used to interact with database

Functions of DBMS

Data Definition: Creating and modifying database structure — tables, columns, constraints (using DDL: CREATE, ALTER, DROP).
Data Manipulation: Inserting, updating, deleting and retrieving data (using DML: INSERT, UPDATE, DELETE, SELECT).
Data Security: Controlling who can access what data — GRANT and REVOKE privileges.
Data Integrity: Enforcing constraints (PRIMARY KEY, FOREIGN KEY, CHECK) to keep data correct.
Concurrency Control: Allowing multiple users to work simultaneously without conflicts — using locks and transactions.
Backup and Recovery: Saving copies of data and restoring after failure — mysqldump, binary logs.
Transaction Management: Ensuring ACID properties — Atomicity, Consistency, Isolation, Durability.

Types of DBMS

Type	Structure	Example
Hierarchical	Data organized in tree structure (parent-child). Fast but rigid — no many-to-many relationships.	IBM IMS — used in old banking systems
Network	Data organized as graph — records linked with pointers. More flexible than hierarchical.	IDMS — handles complex relationships
Relational (RDBMS)	Data stored in tables (rows and columns). Tables linked using keys. Uses SQL.	MySQL, Oracle, PostgreSQL, MS SQL Server
Object-Oriented	Data stored as objects (like OOP). Stores complex data types.	db4o, ObjectDB
NoSQL	Non-relational. Stores data as documents, key-value, graph or column stores. Handles big unstructured data.	MongoDB (document), Redis (key-value), Cassandra

For Class 12 and most college courses: Focus on RDBMS — it is the most widely used and MySQL is the standard example.

What is RDBMS?

RDBMS (Relational Database Management System) is a type of DBMS where data is organized in tables (relations). Tables are linked using primary keys and foreign keys. All operations are done using SQL.

Feature	DBMS	RDBMS
Data Storage	Files or tables (not necessarily related)	Tables that are always related using keys
Relationships	Not mandatory	Central concept — foreign key links tables
Normalization	Not required	Strongly followed (1NF, 2NF, 3NF)
Query Language	May vary	Always SQL
Examples	MS Access (basic), file-based systems	MySQL, Oracle, PostgreSQL, SQL Server

SQL Demo – DBMS in Action

-- DBMS Function 1: Data Definition (DDL)
CREATE DATABASE school_db;
USE school_db;
CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  class_name VARCHAR(10)
);

-- DBMS Function 2: Data Manipulation (DML)
INSERT INTO students VALUES (1, 'Aarav Sharma', 'XII-A');
INSERT INTO students VALUES (2, 'Priya Verma', 'XII-B');
UPDATE students SET class_name = 'XII-C' WHERE roll_no = 2;
SELECT * FROM students;

-- DBMS Function 3: Security (DCL)
-- (Run as root user)
CREATE USER 'teacher'@'localhost' IDENTIFIED BY 'pass123';
GRANT SELECT ON school_db.students TO 'teacher'@'localhost';
-- Teacher can only SELECT — cannot INSERT/DELETE

-- DBMS Function 4: Transaction Management (TCL)
START TRANSACTION;
INSERT INTO students VALUES (3, 'Ravi Singh', 'XII-A');
-- If something goes wrong:
ROLLBACK;   -- undo the insert
-- If everything is fine:
COMMIT;     -- permanently save

Common Mistakes

Confusing Database with DBMS: MySQL is the DBMS (software); school_db is the Database (data). They are different.
Thinking all DBMS are RDBMS: MongoDB is a DBMS but NOT an RDBMS. RDBMS specifically uses tables and SQL.
Underusing DBMS features: Using MySQL just for storage without transactions, constraints or user privileges wastes its full power.
No DBA role defined: Every project needs someone responsible for backup, user access and performance — even in small school projects.

Practice Tasks

Draw a diagram showing DBMS components (Hardware, Software, Data, Users, Query Language) with arrows showing how they interact.
Create school_db from the SQL demo. Add a teacher user with SELECT-only access. Try INSERT as teacher — observe the error.
Write one example of each DBMS function (DDL, DML, DCL, TCL) using a library database.
Viva Q: "What is the difference between DBMS and RDBMS?" — Write 4 differences.

Summary

DBMS = software to create, manage, control and access a database.
Components: Hardware, Software, Data, Procedures, Users, Query Language.
Functions: Data Definition, Manipulation, Security, Integrity, Concurrency, Backup, Transactions.
Types: Hierarchical, Network, Relational (RDBMS), Object-Oriented, NoSQL.
RDBMS is the most important — MySQL, Oracle, PostgreSQL use tables + SQL + keys.

DBMS क्या है?

DBMS (Database Management System) एक software system है जो users को database define, create, maintain और control करने की सुविधा देता है। यह user/application और disk पर stored data के बीच interface का काम करता है।

DBMS के बिना data flat files में होता है — कोई structure नहीं, कोई security नहीं, कोई sharing नहीं।

Popular DBMS: MySQL, Oracle, Microsoft SQL Server, PostgreSQL, SQLite, MongoDB.

Real analogy: DBMS एक librarian की तरह है। Library (database) में हज़ारों books (data) हैं। Librarian (DBMS) जानता है हर book कहाँ है, कौन ले सकता है, issued books track करता है।

DBMS के Components

Component	Role
Hardware	Physical devices — server, hard disk, RAM
Software	DBMS software (MySQL) + OS + application programs
Data	Actual data + metadata (structure की information)
Procedures	Database use करने के rules और instructions
Users	DBA, End Users, Application Programmers
Query Language	SQL — database से interact करने के लिए

DBMS के Functions

Data Definition: Tables, columns, constraints बनाना और modify करना (DDL: CREATE, ALTER, DROP)।
Data Manipulation: Data insert, update, delete और retrieve करना (DML: INSERT, UPDATE, DELETE, SELECT)।
Data Security: GRANT और REVOKE से control — कौन क्या access कर सकता है।
Data Integrity: PRIMARY KEY, FOREIGN KEY, CHECK constraints से data सही रखना।
Concurrency Control: Multiple users को एक साथ काम करने देना — locks और transactions से।

Backup & Recovery:

Transaction Management: ACID properties ensure करना।

DBMS के Types

Type	Structure	Example
Hierarchical	Tree structure (parent-child)। Fast लेकिन rigid।	IBM IMS — पुराने banking systems
Network	Graph structure — pointers से records linked।	IDMS
Relational (RDBMS)	Tables (rows और columns)। Keys से linked। SQL use।	MySQL, Oracle, PostgreSQL
Object-Oriented	Data objects के रूप में store।	db4o, ObjectDB
NoSQL	Non-relational। Documents, key-value, graph।	MongoDB, Redis, Cassandra

Class 12 और college के लिए: RDBMS पर focus करें — MySQL standard example है।

RDBMS क्या है?

RDBMS (Relational DBMS) में data tables (relations) में organize होता है। Tables primary keys और foreign keys से linked होती हैं। सभी operations SQL से होते हैं।

Feature	DBMS	RDBMS
Data Storage	Files या tables (related नहीं ज़रूरी)	हमेशा related tables
Relationships	Mandatory नहीं	Central concept — foreign key
Query Language	Vary कर सकती है	हमेशा SQL
Examples	MS Access (basic)	MySQL, Oracle, PostgreSQL

SQL Demo

-- DDL: Structure बनाना
CREATE DATABASE school_db;
CREATE TABLE students (roll_no INT PRIMARY KEY, name VARCHAR(80) NOT NULL);

-- DML: Data add/update करना
INSERT INTO students VALUES (1, 'Aarav Sharma');
UPDATE students SET name = 'Aarav Kumar' WHERE roll_no = 1;
SELECT * FROM students;

-- DCL: Security
GRANT SELECT ON school_db.students TO 'teacher'@'localhost';

-- TCL: Transaction
START TRANSACTION;
INSERT INTO students VALUES (2, 'Priya Verma');
COMMIT;  -- permanent save

Common Mistakes

Database और DBMS confuse करना: MySQL = DBMS (software); school_db = Database (data)।
सभी DBMS को RDBMS मानना: MongoDB DBMS है लेकिन RDBMS नहीं।
DBMS features को ignore करना: Transactions, constraints, user privileges use न करना DBMS का पूरा फायदा नहीं लेना।

Practice Tasks

DBMS components का diagram बनाएं — arrows से interactions दिखाएं।
SQL demo से school_db बनाएं। Teacher user को SELECT-only access दें। INSERT try करें — error note करें।
Library database के लिए DDL, DML, DCL, TCL के examples लिखें।
Viva: "DBMS और RDBMS में 4 अंतर।"

Summary

DBMS = database create, manage, control और access करने का software।
Components: Hardware, Software, Data, Procedures, Users, Query Language।
Functions: Data Definition, Manipulation, Security, Integrity, Concurrency, Backup, Transactions।
Types: Hierarchical, Network, Relational, Object-Oriented, NoSQL।
RDBMS सबसे important — MySQL, Oracle tables + SQL + keys use करते हैं।