DBMS Documentation
DBMS theory on one page — keys, functional dependencies, normal forms, ACID, isolation levels, locking, indexing and NoSQL.
DBMS theory on one page: keys, normalization rules, transactions, isolation levels, locking, indexing and NoSQL — with the comparisons interviewers ask. For SQL syntax see the SQL docs; for worked examples follow the links to the notes; for revision use Top 50 DBMS interview questions.
1. Basic Terms
| Term | Meaning |
|---|---|
| Relation / tuple / attribute | Table / row / column |
| Schema vs instance | Structure vs the data at one moment |
| Degree / cardinality | Number of columns / number of rows |
| Domain | Allowed values of an attribute |
| Three-schema architecture | Physical, logical (conceptual), view (external) levels |
| Physical / logical data independence | Change storage / schema without breaking the level above |
Command groups: DDL (CREATE, ALTER, DROP, TRUNCATE) · DML (SELECT, INSERT, UPDATE, DELETE) · DCL (GRANT, REVOKE) · TCL (COMMIT, ROLLBACK, SAVEPOINT).
2. ER Model
| Symbol | Meaning |
|---|---|
| Rectangle | Entity (double rectangle = weak entity) |
| Ellipse | Attribute (underlined = key, double = multi-valued, dashed = derived) |
| Diamond | Relationship (double = identifying relationship) |
Cardinality 1:1, 1:N, M:N. Converting to tables:
- 1:N → foreign key on the N side.
- M:N → a new junction table with both keys.
- Multi-valued attribute → its own table.
Details: ER Model and Relational Schema Design.
3. Keys
| Key | Definition |
|---|---|
| Super key | Any attribute set that uniquely identifies a row |
| Candidate key | Minimal super key |
| Primary key | Chosen candidate key — unique, NOT NULL |
| Alternate key | Candidate keys not chosen |
| Composite key | Key with 2+ attributes |
| Foreign key | References another table's primary key |
| Surrogate key | Artificial id (auto-increment, UUID) |
CREATE TABLE enrollment (
student_id INT REFERENCES student(id) ON DELETE CASCADE,
course_id INT REFERENCES course(id),
grade CHAR(2) CHECK (grade IN ('A','B','C','D','F')),
PRIMARY KEY (student_id, course_id)
);
4. Functional Dependencies
- X → Y: same X ⇒ same Y.
- Armstrong's axioms: reflexivity (Y ⊆ X ⇒ X → Y), augmentation (X → Y ⇒ XZ → YZ), transitivity (X → Y, Y → Z ⇒ X → Z).
- Closure X⁺: all attributes X determines. If X⁺ = all attributes, X is a super key.
- An attribute never on the right side of any FD must be in every candidate key.
5. Normal Forms
| Form | Rule | Removes |
|---|---|---|
| 1NF | Atomic values, no repeating groups | Lists in cells |
| 2NF | 1NF + no partial dependency on part of a composite key | Part-key facts |
| 3NF | 2NF + no transitive dependency (non-key → non-key) | Indirect facts |
| BCNF | Every determinant is a super key | Remaining anomalies |
| 4NF | No multi-valued dependencies | Independent multi-valued facts |
- Anomalies fixed: update, insert, delete.
- Decomposition must be lossless (common attributes are a key of one part); BCNF may not preserve all dependencies, 3NF always can.
Worked example: Keys and Normalization.
6. Transactions and ACID
| Property | Guaranteed by |
|---|---|
| Atomicity | Undo log / rollback |
| Consistency | Constraints + correct transaction logic |
| Isolation | Locking or MVCC |
| Durability | Write-ahead / redo log flushed before commit |
States: active → partially committed → committed; active → failed → aborted.
START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT; -- or ROLLBACK;
Details: Transactions and ACID.
7. Isolation Levels and Anomalies
| Level | Dirty read | Non-repeatable read | Phantom |
|---|---|---|---|
| Read uncommitted | ✔ possible | ✔ | ✔ |
| Read committed | ✘ | ✔ | ✔ |
| Repeatable read | ✘ | ✘ | ✔ (mostly prevented in InnoDB) |
| Serializable | ✘ | ✘ | ✘ |
Defaults: PostgreSQL / Oracle / SQL Server = read committed; MySQL InnoDB = repeatable read. Lost update fix: SELECT … FOR UPDATE, an atomic UPDATE … SET x = x - 1, or a version column (optimistic locking).
8. Schedules and Concurrency Control
| Term | Meaning |
|---|---|
| Serial schedule | Transactions one after another |
| Conflict serializable | Can be reordered into a serial schedule by swapping non-conflicting ops; check with a precedence graph (no cycle = serializable) |
| Recoverable | A transaction commits only after those it read from commit |
| Cascadeless | Reads only committed data — no cascading rollbacks |
| Conflicting ops | Same item, different transactions, at least one write (R-W, W-R, W-W) |
- Locks: shared (S, read) — many; exclusive (X, write) — one.
- 2PL: growing phase (acquire), then shrinking phase (release) → conflict serializable. Strict 2PL: hold X locks until commit → also cascadeless.
- Timestamp ordering and MVCC (readers don't block writers) are the alternatives.
- Deadlock: detected with a wait-for graph; one transaction is aborted.
9. Indexing
| Index | Notes |
|---|---|
| B+ tree | Default in most RDBMS; data in linked leaves → fast range scans |
| Hash | O(1) equality only, no ranges/sorting |
| Clustered | Table stored in index order; one per table |
| Non-clustered | Separate structure with pointers; many per table |
| Composite (a, b) | Helps a and a AND b, not b alone (leftmost prefix) |
| Covering | Contains all queried columns → no table lookup |
Indexes slow down writes and use space. Functions on a column (YEAR(date) = 2025) and leading wildcards (LIKE '%x') usually prevent index use. Check with EXPLAIN. Details: Indexing, B+ Trees and SQL vs NoSQL.
10. Views, Procedures and Triggers
CREATE VIEW high_earners AS SELECT name, salary FROM employees WHERE salary > 80000;
CREATE TRIGGER log_salary AFTER UPDATE ON employees
FOR EACH ROW INSERT INTO audit(emp_id, old_salary, new_salary) VALUES (OLD.id, OLD.salary, NEW.salary);
Procedure: called explicitly, can do many actions. Function: returns one value, usable inside queries. Details: Views, Stored Procedures and Triggers.
11. Distributed Databases
| Concept | Meaning |
|---|---|
| Replication | Copies on several servers (primary → replicas); scales reads, adds availability |
| Sharding | Rows split across servers by a shard key; scales writes and storage |
| Consistent hashing | Adding a shard moves only a fraction of keys |
| CAP | During a partition, choose consistency or availability |
| BASE | Basically available, soft state, eventually consistent |
| 2PC | Two-phase commit for transactions across databases |
Details: Sharding and Replication, CAP Theorem.
12. SQL vs NoSQL
| SQL | NoSQL | |
|---|---|---|
| Schema | Fixed | Flexible |
| Model | Tables + joins | Document, key-value, wide-column, graph |
| Scaling | Mostly vertical | Horizontal |
| Consistency | Strong ACID | Often eventual (many offer transactions now) |
| Examples | MySQL, PostgreSQL | MongoDB, Redis, Cassandra, Neo4j |
Choose SQL for related data with transactions (banking, orders); NoSQL for flexible schemas, huge scale or naturally document/graph data. See the MongoDB and Redis docs.
13. Comparisons Interviewers Ask
| Question | Short answer |
|---|---|
| DELETE vs TRUNCATE vs DROP | Some rows (rollback-able) vs all rows fast vs the whole table |
| WHERE vs HAVING | Filters rows before grouping vs groups after |
| Primary vs unique key | One, NOT NULL vs many, NULL allowed |
| Clustered vs non-clustered | Physical order (1) vs separate pointer structure (many) |
| 3NF vs BCNF | BCNF requires every determinant to be a super key |
| UNION vs UNION ALL | Removes duplicates vs keeps them (faster) |
| Normalization vs denormalization | Less redundancy vs faster reads |
14. Cheat Sheet & References
Keys and normal forms
Super ⊇ Candidate (minimal) → Primary (chosen, NOT NULL) + Alternate
1NF atomic · 2NF no partial dep · 3NF no transitive dep · BCNF determinant = super key
"The key, the whole key, and nothing but the key"
Transactions
ACID: Atomicity(undo log) Consistency Isolation(locks/MVCC) Durability(WAL)
Dirty read → stopped by READ COMMITTED
Non-repeatable → stopped by REPEATABLE READ
Phantom → stopped by SERIALIZABLE
2PL: growing then shrinking → conflict serializable
Precedence graph without a cycle → conflict serializable
Indexing
B+ tree: data only in leaves, leaves linked → range queries
1 clustered per table, many non-clustered
Composite (a,b): a ✓ a+b ✓ b ✗