Database Management Systems
DBMS - the layer that turns raw data into something trustworthy
A Database Management System is the software layer that stores, organizes, secures, and serves data - reliably, for many users at once - so applications never have to reinvent storage, locking, or recovery from scratch. This page is a complete reference: concepts, formulas, architecture, cheat sheets, and downloadable notes.
Happy Learning ..
ER Diagram — a minimal example
Why it exists
Why DBMS is needed — and what breaks without it
Every core DBMS concept exists to solve a real problem that plain files can't.
Data redundancy & inconsistency — the same fact duplicated across files can drift out of sync.
Difficulty in accessing data — every new query needs new application code; no ad-hoc querying.
Data isolation — data scattered across formats/files is hard to combine.
Integrity problems — constraints (e.g. 'balance ≥ 0') must be hand-coded in every program.
Atomicity problems — a crash mid-update can leave files half-updated with no automatic rollback.
Concurrent access anomalies — two users editing the same record simultaneously can corrupt it.
Security problems — file-level permissions can't express 'this user may read but not write column X'.
If there were no DBMS…
- Every application would re-implement its own storage, locking, and recovery logic from scratch.
- No standard query language — every data request needs custom code.
- No transaction guarantees — a power cut mid-transfer could lose or duplicate money.
- No concurrent-access safety — simultaneous writes could silently corrupt data.
- No fine-grained security — you could only lock entire files, not rows or columns.
- Backups & recovery would be manual, error-prone, and inconsistent across teams.
Is it actually helpful? — Core features
- Minimizes data redundancy through normalization
- Enforces data integrity via constraints (PK, FK, CHECK, NOT NULL)
- Supports concurrent multi-user access safely
- Provides backup & recovery after crashes
- Offers a standard query language (SQL) for ad-hoc access
- Implements security through authentication & fine-grained authorization
- Achieves data independence — physical storage can change without breaking apps
- Manages transactions with ACID guarantees
Taxonomy
Types of DBMS
From rigid tree structures to horizontally-scaled cloud systems — each generation solved the limits of the one before it.
Hierarchical DBMS1960s
Data organized as a tree — each child record has exactly one parent. Fast for fixed, predictable relationships.
Examples: IBM IMS
+ One-to-many links, fixed structure
– Rigid schema, no many-to-many support
Network DBMS1970s
A graph structure — a record can have multiple parents and children, connected via explicit pointers (sets).
Examples: IDMS (CODASYL model)
+ Models many-to-many naturally
– Complex navigation, hard to redesign
Relational DBMS (RDBMS)1970 – present
Data stored in 2-D tables (relations) of rows & columns, linked by keys. Backed by relational algebra & SQL.
Examples: MySQL, PostgreSQL, Oracle, SQL Server
+ Strong consistency, mature tooling, SQL standard
– Vertical scaling limits, rigid schema changes
Object-Oriented DBMS1980s – 90s
Stores data as objects (like in OOP) — combining attributes and behaviour, with inheritance support.
Examples: db4o, ObjectDB
+ Natural fit for OOP applications
– Smaller ecosystem, steep learning curve
NoSQL DBMS2009 – present
Non-relational, schema-flexible stores built for horizontal scale. Four common families: document, key-value, column-family, graph.
Examples: MongoDB, Redis, Cassandra, Neo4j
+ Horizontal scale, flexible schema, high throughput
– Weaker consistency guarantees (varies by system)
NewSQL DBMS2010s – present
Aims for RDBMS-style ACID consistency with NoSQL-style horizontal scalability, often distributed by design.
Examples: Google Spanner, CockroachDB, TiDB
+ Scale + strong consistency together
– Operationally more complex to run
Block diagrams
Architecture & how a DBMS works
The 3-level ANSI/SPARC architecture separates how users see data, how it's logically organized, and how it's physically stored.
3-Level Schema Architecture
Level 1closest to user
External Level
User views — View A, View B, View C
Level 2central schema
Conceptual Level
Full logical schema — all tables, relationships, constraints
Level 3closest to disk
Internal Level
Physical storage — files, blocks, indexes on disk
How a query flows through a DBMS
1SQL Query
2Parser (syntax + semantic check)
3Query Optimizer (choose best plan)
4Execution Engine
5Storage Manager
6Result Set
Every SQL statement is parsed, optimized into an efficient execution plan, run by the execution engine, and served from/to the storage manager — which handles buffering, indexes, and disk I/O underneath.
Quick math
Key formulas you need
The recurring calculations across keys, normalization, indexing, and storage.
Attribute Closure
X+ = X ∪ { all attributes derivable from X using the FD set F }
Used to test whether X determines every attribute of the relation, i.e. whether X is a (super)key.
Candidate Key Test
X is a candidate key ⇔ X+ = R AND no proper subset of X has closure R
Superkey + minimality (no redundant attribute) = candidate key.
Number of Superkeys
Superkeys = 2^(N − K)
N = total attributes, K = attributes in one minimal candidate key (assuming a single known candidate key).
Normalization — 1NF
∀ attribute A ∈ R : A is atomic (no repeating groups / multi-valued cells)
First Normal Form removes composite & multi-valued attributes.
Normalization — 2NF
R is in 1NF AND no non-prime attribute is partially dependent on a candidate key
Only relevant when the primary key is composite.
Normalization — 3NF
R is in 2NF AND no non-prime attribute is transitively dependent on a candidate key
For every FD X→Y, either X is a superkey OR Y is a prime attribute.
Normalization — BCNF
∀ non-trivial FD X → Y in R : X is a superkey of R
Stricter than 3NF — no exception for prime attributes.
Blocking Factor (file organization)
bfr = ⌊ Block size / Record size ⌋
Number of records that fit in one disk block.
Number of Blocks Needed
b = ⌈ r / bfr ⌉
r = total number of records in the file.
Index Search Cost (binary search on sparse index)
Cost = ⌈ log2(b_index) ⌉ + 1
b_index = number of blocks in the index; the +1 accesses the actual data block.
B+ Tree — Approx. Height
h ≈ ⌈ log⌈p/2⌉ (n) ⌉
p = order (max children per node), n = number of leaf-level search-key entries.
RAID 5 Storage Efficiency
Usable capacity = ((n − 1) / n) × total disk capacity
n = number of disks; one disk's worth of space is used for distributed parity.
Serializability Check
A schedule is conflict-serializable ⇔ its precedence graph is acyclic
Draw an edge Ti → Tj if Ti's operation conflicts with and precedes Tj's on the same data item.
Detailed notes
Full DBMS notes — every topic, with examples
Click a topic to expand it. This is the same content bundled into the downloadable notes file.
A Database Management System (DBMS) is software that creates, stores, manages, and lets users retrieve data from a database in a controlled, efficient, and secure way, instead of dealing with raw files directly.
- Acts as an interface between the user/application and the physical database.
- Provides languages to define data (DDL), manipulate data (DML), and control access (DCL/TCL).
- Ensures data is stored once and accessed consistently by many users and programs.
- Examples: MySQL, PostgreSQL, Oracle, SQL Server, MongoDB, SQLite.
Example
Instead of every bank branch keeping its own text file of accounts, a DBMS stores all accounts in one governed system that every branch queries safely and consistently.
Quick reference
DBMS / SQL cheat sheet
A fast lookup table for the night before an exam or interview.
DDL
CREATE TABLE
ALTER TABLE
DROP TABLE
TRUNCATE TABLE
DML
SELECT
INSERT INTO
UPDATE ... SET
DELETE FROM
DCL
GRANT
REVOKE
TCL
COMMIT
ROLLBACK
SAVEPOINT
Clauses
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT
Joins
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN
SELF JOIN
Aggregates
COUNT()
SUM()
AVG()
MIN()
MAX()
Normal Forms
1NF: atomic values
2NF: no partial dep.
3NF: no transitive dep.
BCNF: LHS is superkey
Keys
Super Key
Candidate Key
Primary Key
Foreign Key
Composite Key
ACID
Atomicity
Consistency
Isolation
Durability
Exam-ready
Important questions (IMP)
Frequently asked DBMS questions in exams and interviews, answered concisely.
Q1. What is the difference between DELETE, TRUNCATE and DROP?
DELETE removes rows (can be rolled back, fires triggers, keeps structure), TRUNCATE removes all rows fast (resets identity, minimal logging), DROP removes the whole table structure.
Q2. What is the difference between a Primary Key and a Unique Key?
A table can have only one Primary Key (no NULLs allowed) but multiple Unique Keys (one NULL typically allowed).
Q3. What is normalization and why is it needed?
Normalization organizes data to remove redundancy and prevent insert/update/delete anomalies by satisfying progressively stricter normal forms.
Q4. What is denormalization?
The deliberate introduction of redundancy (merging tables) to improve read performance, trading off some normalization for speed.
Q5. Difference between 2-tier and 3-tier architecture?
2-tier: client talks directly to the database. 3-tier: client → application server → database, adding a business-logic layer in between.
Q6. What is a view?
A virtual table defined by a stored SQL query; it doesn't store data itself but presents a customized view of underlying tables.
Q7. What is the difference between clustered and non-clustered index?
A clustered index determines the physical order of data rows (one per table); a non-clustered index is a separate structure with pointers back to the data (many allowed per table).
Q8. What are ACID properties?
Atomicity, Consistency, Isolation, Durability — the four guarantees a transaction provides.
Q9. What is a deadlock and how is it resolved?
A cycle of transactions each waiting on the other's locks; resolved via detection (wait-for graph + abort), prevention, or timeouts.
Q10. What is the CAP theorem?
A distributed system can guarantee at most two of Consistency, Availability, and Partition tolerance at the same time.
Real world
Where DBMS is used most
Almost every serious application relies on a DBMS somewhere in its stack.
Banking & Finance
Account balances, transaction ledgers, fraud detection — needs strict ACID guarantees.
E-Commerce
Product catalogs, orders, inventory, recommendation data — mixes RDBMS + NoSQL.
Social Media
Massive-scale user graphs and feeds — often graph & column-family databases.
Healthcare
Patient records, prescriptions, lab results — high integrity & strict access control.
Airlines & Travel
Seat inventory, bookings, real-time pricing — needs high concurrency handling.
Education
Student records, grades, course enrollment (classic ER-model example).
Telecom
Call detail records at huge volume/velocity — often NoSQL / distributed DBMS.
Government & Public Records
Identity, land, tax records — needs auditability, durability, and security.
Evolution
How DBMS changed the generations — and what's next
From rigid hierarchical trees to AI-native, self-tuning cloud databases.
1960s
Flat Files & Hierarchical Model
IBM's IMS introduces tree-structured data — first real step beyond raw files.
1970
The Relational Model
Edgar F. Codd publishes the relational model, laying the foundation for modern RDBMS and SQL.
1980s–90s
Commercial RDBMS Boom
Oracle, DB2, Sybase, and later MySQL & PostgreSQL bring relational databases into mainstream business.
2000s
Web Scale & Object-Relational
The web era pushes databases to handle far more concurrent users; object-relational features emerge.
2009+
The NoSQL Movement
MongoDB, Cassandra, Redis, Neo4j — trading strict schemas/consistency for horizontal scale & flexibility (Big Data).
2010s
NewSQL & Cloud Databases
Spanner, CockroachDB — distributed scale with SQL guarantees; databases move to fully managed cloud services.
2020s →
AI-Native & Autonomous Databases
Vector databases for AI embeddings, self-tuning/autonomous DBMSs, and serverless, usage-billed database platforms.
Take these notes with you
Everything on this page - types, formulas, full notes, cheat sheet, and important questions — bundled into one downloadable file.