A Database Management System (DBMS) is software that manages a structured collection of data, providing mechanisms to store, retrieve, and manipulate it efficiently while maintaining integrity and security. Think of it as a highly disciplined librarian — not just storing books on shelves, but cataloguing them, tracking who borrowed what, ensuring no two books occupy the same slot, and letting multiple people browse the library simultaneously without chaos.
The dominant model today is the Relational DBMS (RDBMS), where data is organized in tables (also called relations). Each table has columns (attributes) and rows (tuples). The magic is in the relationships between tables — a customer table links to an orders table through a shared key, rather than duplicating customer information in every order row.
Here is the vocabulary you must own going into the exam:
The analogy that makes this click: if your DBMS is a factory, the schema is the factory floor plan, the tables are the assembly lines, the keys are the serial numbers on each product, and SQL is the language you use to give instructions on the floor.
SBI PO questions on this topic are not testing whether you know what a database is — they test precision. "Referential Integrity vs Entity Integrity", "2NF vs 3NF", "Hash Join vs Sort-Merge Join" — these distinctions are where marks move.
Entity Integrity Constraint: No primary key attribute can hold a NULL value. The primary key's entire job is identification — a NULL says "I don't know what this is", which defeats the purpose entirely.
Referential Integrity Constraint: A foreign key value must either match an existing primary key in the referenced (parent) table, or be NULL. This rule prevents orphaned records — an order row that references a non-existent customer, for instance. When you delete a parent row, the DBMS will either block the deletion, cascade it to child rows, or set the FK to NULL, depending on how the constraint is configured.
Domain Integrity: Values in a column must fall within the defined domain — an age column accepting only integers between 0 and 150 is domain-constrained.
Normalization is the process of structuring a relational database to reduce redundancy. The normal forms build on each other:
Employee → Department → Department_Head, then Department_Head transitively depends on Employee via Department — a 3NF violation. Fix it by splitting the table.Look — the SBI PO question will always ask about 3NF and transitive dependencies together. Lock that pairing in your memory: 3NF kills transitive dependency.
When a query joins two tables, the query engine picks an algorithm. Three matter for the exam:
Nested Loop Join: For every row in the outer table, scan the entire inner table. Simple, but O(n × m) complexity. Efficient only when the inner table is tiny or indexed.
Sort-Merge Join: Sort both tables on the join key, then merge. Requires O(n log n + m log m) for sorting, but if data is already sorted it becomes O(n + m). Requires sorted input.
Hash Join: Build a hash table from the smaller relation, then probe it with each row from the larger relation. Complexity is O(n + m) without needing sorted input. This is the weapon of choice when one table is much smaller than the other and neither is sorted.
Index Nested Loop Join: Like Nested Loop, but uses an index on the inner table. Fast when an index exists; useless when it doesn't.
A B+ tree of order m has these properties:
m children.⌈m/2⌉ children.m - 1 keys.For order 5: leaf nodes hold at most 5 - 1 = 4 keys. This m - 1 formula is what every question on this topic tests.
Multiple transactions running simultaneously can cause problems: dirty reads (reading uncommitted data), lost updates (one transaction overwrites another's change), non-repeatable reads (same query returns different results in one transaction), phantom reads (new rows appear between two reads).
Two-Phase Locking (2PL): Transactions acquire all locks in a growing phase, then release in a shrinking phase. Ensures serializability.
Timestamp Ordering Protocol: Each transaction gets a unique timestamp at start. Operations execute in timestamp order. If a transaction tries to read data written by a later-timestamped transaction, it is rolled back.
Multiversion Concurrency Control (MVCC): Maintains multiple versions of data so readers don't block writers.
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read | |---|---|---|---| | READ UNCOMMITTED | Allowed | Allowed | Allowed | | READ COMMITTED | Prevented | Allowed | Allowed | | REPEATABLE READ | Prevented | Prevented | Allowed | | SERIALIZABLE | Prevented | Prevented | Prevented |
READ UNCOMMITTED allows dirty reads but still prevents lost updates through basic locking. It is the loosest level, offering maximum concurrency at the cost of data consistency.
In a distributed database, a transaction may need to commit across multiple sites. The Two-Phase Commit Protocol (2PC) guarantees atomicity:
This is distinct from Two-Phase Locking (2PL) — 2PC is about distributed atomicity, 2PL is about concurrency control within a single system. Exam questions routinely exploit this naming confusion.
Caching stores frequently accessed data in faster memory (RAM) closer to the processor, avoiding expensive disk I/O. It is a runtime optimization — transparent to the query writer.
Indexing is a structural optimization — a B+ tree or hash index built on a column so lookups don't require full table scans. Indexing helps at query-execution time; caching helps at data-retrieval time.
Every time you see "transitive functional dependency" in an option, the answer is 3NF. Every time you see "partial dependency", the answer is 2NF. This two-pairing covers roughly 80% of normalization MCQs.
Micro-example: "Which NF removes the dependency Employee → Dept → DeptHead?" — transitive → 3NF. No calculation needed. Standard reasoning: 20 seconds. Pattern recognition: 4 seconds.
When a join question mentions: (1) one table much smaller, (2) neither table is sorted — select Hash Join automatically. The other joins fail one of those conditions: Sort-Merge needs sorted input, Nested Loop is O(n×m) for large tables, Index Nested Loop needs an existing index.
Three-condition check: Small-large pair? Yes. Sorted? No. Index available? No. → Hash Join. Decision time: 6 seconds vs reasoning from scratch: 30 seconds.
For any B+ tree question: leaf node max keys = order - 1. Internal node max keys = order - 1. Internal node max children = order. The confusion is always between keys and children — children = order, keys = order minus 1.
Order 5 → 4 keys in leaf. Order 7 → 6 keys. No derivation needed once you fix this formula. Saves you from the trap option that says "5" for an order-5 tree. Recognition time: 3 seconds.
Two-Phase Commit (2PC) = distributed databases, atomicity across sites, coordinator sends Prepare then Commit/Abort. Two-Phase Locking (2PL) = single-system concurrency, growing phase then shrinking phase.
When the question says "distributed database" + "all sites commit or none", eliminate 2PL immediately. When it says "serializability" + "single database", eliminate 2PC. The word "distributed" is the trigger. Elimination time: 5 seconds.
Only one isolation level allows dirty reads: READ UNCOMMITTED. If the question says "allows dirty reads", pick READ UNCOMMITTED without reading the other options. If it says "prevents dirty reads but allows non-repeatable reads", pick READ COMMITTED. These two filters cover the most common isolation-level question formats. Decision: 4 seconds flat.
When you see a DBMS question in the exam, run this decision tree in your head:
Step 1 — Identify the sub-topic keyword. Does the question contain: "join" → go to join algorithm rules. "normal form" or "dependency" → go to normalization rules. "isolation level" or "dirty read" → go to isolation level table. "distributed" + "commit" → 2PC. "concurrency" + "timestamp" → Timestamp Ordering. "index" + "order" → B+ tree formula.
Step 2 — Apply the single rule for that sub-topic. Each sub-topic has one dominant rule that resolves 80%+ of exam questions. Don't over-analyze; apply the rule, check if an option matches exactly.
Step 3 — Eliminate by naming confusion. DBMS questions love near-identical names — 2PC vs 2PL, BCNF vs 3NF, Caching vs Indexing. After picking your answer, verify the remaining options don't describe a near-synonym. If they do, re-read the question for the distinguishing phrase.
Step 4 — Never calculate when a formula gives a direct answer. B+ tree key count, join complexity, normalization violations — all resolve without arithmetic beyond subtraction.
Target time per DBMS question: under 40 seconds.
Why this question: Tests the ability to match join algorithm to a specific data scenario — a precision question that eliminates students who memorize definitions without understanding when each applies.
Solving path: The two conditions in the question are: one table significantly smaller + both unsorted. Sort-Merge needs sorted input — eliminate. Index Nested Loop needs an index — not mentioned, eliminate. Nested Loop is O(n×m) — inefficient for large tables, eliminate. Hash Join builds a hash table from the smaller relation and probes with the larger, O(n+m), no sorting needed — correct.
Why this question: Tests the B+ tree leaf node formula, the most direct numerical question in the indexing sub-topic. The trap option "5" is placed to catch students who confuse order with max keys.
Solving path: B+ tree order 5 → leaf node max keys = 5 - 1 = 4. The option "5" is the trap (confusing order with keys). The order defines max children for internal nodes; max keys = order - 1 for both leaf and internal nodes.
Why this question: Tests whether you can distinguish between four similarly-named concurrency control mechanisms. The distinguishing word is "timestamps".
Solving path: The question asks specifically about timestamps ordering transactions. Two-Phase Locking uses locks, not timestamps — eliminate. MVCC maintains versions — eliminate. Optimistic CC validates at commit time — eliminate. Timestamp Ordering Protocol assigns timestamps at transaction start and executes operations in that order — correct.
Why this question: Tests the isolation level table. The phrase "allows dirty reads but prevents lost updates" is the exact definition of READ UNCOMMITTED.
Solving path: "Allows dirty reads" — only READ UNCOMMITTED allows dirty reads. All other levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE) prevent dirty reads. The answer is READ UNCOMMITTED immediately after reading "allows dirty reads". The "prevents lost updates" clause is additional detail confirming basic locking still applies.
Why this question: Tests 2PC vs 2PL confusion in a distributed context — the most common naming-trap question in DBMS.
Solving path: The question says "distributed database system" + "commits at all sites or aborts at all sites" — this is atomicity across distributed sites. Two-Phase Locking (2PL) handles concurrency in a single system — eliminate. Timestamp Ordering and Optimistic CC are concurrency control protocols — eliminate. Two-Phase Commit Protocol (2PC) is specifically designed for distributed atomicity, using a coordinator's prepare-then-commit cycle — correct.
Why this question: Tests normalization knowledge with the classic transitive dependency scenario.
Solving path: The question asks which NF addresses transitive functional dependencies. 4NF handles multi-valued dependencies — eliminate. BCNF handles cases where a determinant is not a candidate key — eliminate. 2NF handles partial dependencies — eliminate. 3NF specifically eliminates transitive dependencies where a non-prime attribute depends on another non-prime attribute — correct.
Why this question: Tests the distinction between Referential Integrity and Entity Integrity — two constraints with overlapping names that are frequently confused.
Solving path: Entity Integrity says PK cannot be NULL — not about foreign keys. Check Constraint validates column values against a condition. Domain Integrity enforces data type and range. Referential Integrity specifically governs foreign key values: they must either match a PK in the parent table or be NULL. The phrase "foreign key value either matches a primary key or is NULL" is the definition of Referential Integrity — correct.
Why this question: Tests the distinction between Caching and Indexing — both are performance optimizations but at different layers.
Solving path: Indexing creates a data structure (B+ tree/hash) for faster lookup — it is a structural optimization. Normalization reduces redundancy — not a performance-storage technique. Partitioning splits data across storage devices. Caching stores frequently accessed data in faster memory (RAM) closer to the processor, reducing disk I/O — matches "faster storage media closer to the processor" exactly — correct.
Confusing 2PC with 2PL. Two-Phase Commit is for distributed databases (atomicity across sites). Two-Phase Locking is for concurrency control in a single system. The word "distributed" in the question is your disambiguating signal — train yourself to look for it first.
Selecting "order" as the max keys in a B+ tree leaf node. For order m, max keys = m - 1. The option equal to the order value is always a trap. Internal nodes also store at most m - 1 keys (and at most m children).
Confusing Referential Integrity with Entity Integrity. Entity Integrity = PK cannot be NULL (about the table itself). Referential Integrity = FK must match a PK or be NULL (about the relationship between tables). When the question mentions foreign keys, entity integrity is never the answer.
Applying 3NF logic to 2NF questions. 2NF removes partial dependencies (only relevant with composite keys). 3NF removes transitive dependencies. A question about "non-key attribute depending on part of a composite key" is 2NF — do not select 3NF.
Choosing Sort-Merge Join when tables are unsorted. Sort-Merge requires sorted input. If the question says "unsorted", Sort-Merge is immediately disqualified — even if the size condition (one table much smaller) would otherwise favor it. Hash Join handles both conditions.
Treating Caching and Indexing as synonyms. Both improve performance, but Caching is a runtime, memory-layer optimization (stores data in RAM to avoid disk reads). Indexing is a persistent structural optimization (creates a B+ tree for faster lookup). The phrase "faster storage media closer to the processor" always points to Caching, not Indexing.