DBMS, RDBMS and SQL
Part of the free LogicWiz SQL & Databases course, Module 1: What a database is. The queries and practice run in the app on real Postgres.
At 10:00 AM sharp, two lakh people hit IRCTC for Tatkal tickets, and the last berth on the Rajdhani goes to exactly one of them. Who makes sure of that? Not the app. The DBMS.
You'll learn to
- Name the four jobs a DBMS does: concurrency, integrity, security, recovery
- Explain the three-schema architecture and data independence
- Sort SQL commands into DDL, DML, DCL and TCL
- Describe how a query is processed, and where NoSQL fits
Learn
What a DBMS does for you
A DBMS (Database Management System) is the software that stores data and answers queries: Postgres, MySQL, Oracle. It does four jobs that a folder of files cannot:
- Concurrency: thousands of users at once, and the last Tatkal berth still goes to one person.
- Integrity: rules on every change (no CGPA of 11, no student in a missing department).
- Security: who may see or change what (a student sees their marks, not everyone's).
- Recovery: after a crash, a transfer is either fully done or not done at all.
An RDBMS is a DBMS that keeps data in related tables and speaks SQL.
Integrity: the DBMS says no
UPDATE students
SET cgpa = 11
WHERE student_id = 101;
The table was created with CHECK (cgpa BETWEEN 0 AND 10), so Postgres refuses. Every app that writes to this table gets the same rule for free.
Recovery: all or nothing
BEGIN;
UPDATE students SET cgpa = 10 WHERE student_id = 112;
SELECT name, cgpa FROM students WHERE student_id = 112;
ROLLBACK;
SELECT name, cgpa FROM students WHERE student_id = 112;
BEGIN starts a transaction: a group of changes that succeed or fail together. Inside it Karan has 10.0. ROLLBACK undoes the group, so the last query shows 5.9 again. A crash halfway through is treated like a ROLLBACK. That is how a UPI transfer never leaves money "in the air". Module 13 goes deeper.
The three-schema architecture
| Level | What it describes | In our college database |
|---|---|---|
| External (view level) | What one group of users sees | The exam cell sees names and marks; the hostel office sees names and cities |
| Conceptual (logical level) | All the tables, columns, keys and rules | students, departments, courses, enrollments and their keys |
| Internal (physical level) | How data sits on disk | Files, pages, indexes, compression |
Data independence means changing one level without breaking the one above. Physical: add an index or move files, and no table changes. Logical: add a column to students, and the exam cell's view still works.
Two favourite exam questions:
- Advantages of a DBMS over a file system: less redundancy and inconsistency, easy access by query, integrity rules, atomic updates, safe concurrent access, security, backup and recovery.
- Logical vs physical data independence: physical is easier to achieve (apps never see disk layout); logical is harder (apps depend on tables), which is why views exist.
SQL is four small languages
| Group | Stands for | Commands | Does |
|---|---|---|---|
| DDL | Data Definition Language | CREATE, ALTER, DROP, TRUNCATE | Defines the structure: tables, columns |
| DML | Data Manipulation Language | SELECT, INSERT, UPDATE, DELETE | Reads and changes the rows |
| DCL | Data Control Language | GRANT, REVOKE | Gives and takes away permissions |
| TCL | Transaction Control Language | BEGIN, COMMIT, ROLLBACK, SAVEPOINT | Groups changes into transactions |
Some books put SELECT in its own group, DQL (Data Query Language). Both answers are accepted if you say which convention you use.
TRUNCATE empties a table, so it feels like DELETE. But it is DDL, not DML: it throws away the table's storage instead of removing rows one by one. Also: SQL is the language, not a database. Postgres, MySQL and Oracle are RDBMSs that speak SQL, each with its own small dialect differences.
How a query is processed
EXPLAIN (COSTS OFF)
SELECT name
FROM students
WHERE city = 'Delhi';
SQL is declarative: you say what you want, not how to get it. Postgres then (1) parses the text and checks the tables exist, (2) rewrites and optimises it into a plan, choosing among ways to read the data, and (3) executes the plan. EXPLAIN shows the plan it chose: here a scan of students with a filter. Module 14 reads plans properly.
Where NoSQL fits
Not every database is tables and SQL. NoSQL databases store data in other shapes:
- Documents (MongoDB): one JSON-like document per order, with its items inside.
- Key-value (Redis): a giant fast dictionary, great for sessions and caches.
- Wide-column (Cassandra) and graph (Neo4j).
They trade some of the RDBMS guarantees (joins, strict schemas, transactions across rows) for scale or flexibility. Most companies use both. Your UPI balance lives in an RDBMS. Module 17 compares them, and shows Postgres storing JSON too.
In one line
A DBMS handles concurrency, integrity, security and recovery; three levels (external, conceptual, internal) give data independence; SQL = DDL + DML + DCL + TCL.