DBMS Introduction
A database is an organised collection of related data. A DBMS (database management system) is the software that stores it, answers queries, keeps it correct, and lets many users work on it at once. MySQL, PostgreSQL, Oracle and SQL Server are DBMSs.
Why not just files?
Before DBMSs, every application kept its own files. That caused:
| Problem | Example |
|---|---|
| Redundancy and inconsistency | A student’s phone number in the library file and the exam file, one of them outdated |
| Hard to query | “Students with CGPA > 8 in CSE” needs a new program each time |
| Data isolation | Data scattered in different files and formats |
| Integrity | Nothing stops a negative fee or a marks value of 900 |
| Atomicity | A crash halfway through a transfer leaves money debited but not credited |
| Concurrent access | Two clerks book the last seat at the same moment |
| Security | Hard to let staff see salaries but not students |
A DBMS solves each of these: one shared copy of the data, a query language (SQL), constraints, transactions, concurrency control and access control.
What a DBMS gives you
flowchart TB A[Applications and users] -->|SQL| Q[Query processor: parse, optimise, execute] Q --> T[Transaction manager: concurrency control, recovery] Q --> S[Storage manager: buffers, files, indexes] S --> D[(Data on disk)] T --> L[(Write-ahead log)]
Three-schema architecture
| Level | Describes | Example |
|---|---|---|
| External (view) | What one user or app sees | The placement cell sees name, branch, CGPA |
| Conceptual (logical) | All entities, attributes, relationships, constraints | Student(roll, name, branch, cgpa, phone, …) |
| Internal (physical) | How data is stored | B+ tree index on roll, file layout, compression |
This separation gives data independence:
- Physical data independence: change storage (add an index, move to SSD) without changing the logical schema. Easy and common.
- Logical data independence: change the logical schema (split a table, add a column) without breaking external views and apps. Harder.
Key vocabulary
- Schema: the structure (tables, columns, types, constraints). Changes rarely.
- Instance: the data in the database at a given moment. Changes constantly.
- Data model: how data is represented: relational (tables), ER (design), document, key-value, graph.
- DBA: the database administrator, who manages schemas, access, backups and performance.
Relational vs NoSQL
| Relational (SQL) | NoSQL | |
|---|---|---|
| Model | Tables with fixed schemas, joins | Documents, key-value, wide-column, graph |
| Transactions | Strong ACID across rows and tables | Often per-document, or eventual consistency |
| Scaling | Mostly vertical, with read replicas and sharding | Designed to scale horizontally |
| Good for | Money, orders, anything relational and consistency-critical | Huge volumes, flexible schemas, caching, feeds |
| Examples | PostgreSQL, MySQL, Oracle | MongoDB, Redis, Cassandra, Neo4j |
Questions interviewers ask
- What is a DBMS? Why is it better than a file system? Use the table above; mention redundancy, integrity, atomicity and concurrency.
- What is data independence? Which kind is harder? Logical.
- Schema vs instance?
- When would you choose NoSQL over SQL? When you need horizontal scale or a flexible schema and can accept weaker cross-record consistency.
Check yourself
A customer's address is stored in both the orders file and the customers file, and the two copies differ. Which file-system problem is this?
You add an index and reorganise how a table is stored on disk, and no application query changes. That's…
Which level of the three-schema architecture describes what a particular user or application sees?
Which workload is the strongest reason to pick a relational database?
Item 8 of 28 · updated 2026-10-13