Getting everything ready for you…

Loading courses, problems and your progress. This takes just a moment.

0s

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.

Details: Concurrency Control, Schedules and Locking.

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 ✗

References