DBMS Notes for Placements and Interviews
DBMS notes for placement interviews: ER model, keys, normalization up to BCNF, SQL joins, transactions and ACID, concurrency control and B+ tree indexing.
- Notes: 12
- Reading time: about 3 hours
- Cost: Free, no sign-in needed
ER models, keys, normalization, SQL joins and aggregation, transactions and ACID, concurrency control and indexing.
Read in this order
- Introduction to DBMS — What a DBMS is and why it beats a file system: data models, the three-schema architecture, data independence, database users and types of databases.
- ER Model in DBMS — The ER model in DBMS: entities, attribute types, relationships, cardinality, participation, weak entities, generalization and ER diagram to table rules.
- Keys in DBMS — Every key in DBMS with one worked table: super, candidate, primary, alternate, foreign, composite, surrogate and unique keys, plus integrity constraints.
- Functional Dependencies in DBMS — Functional dependencies in DBMS: Armstrong's axioms, attribute closure step by step, finding every candidate key, and computing a canonical cover, all worked.
- Normalization in DBMS: 1NF to BCNF — Normalization in DBMS on one running example: anomalies, 1NF, 2NF, 3NF and BCNF decompositions, lossless joins, dependency preservation, 4NF, denormalizing.
- SQL Basics: DDL, DML, DCL and TCL — SQL basics with results: DDL, DML, DCL and TCL commands, constraints, DELETE vs TRUNCATE vs DROP, SELECT with WHERE and ORDER BY, NULL logic and LIKE.
- SQL Joins — Every SQL join on two small tables with exact results: inner, left, right, full outer, cross, self and natural joins, duplicate rows, and join vs subquery.
- SQL GROUP BY, HAVING and Subqueries — SQL aggregation worked on one table: aggregates and NULLs, GROUP BY, HAVING vs WHERE, subqueries, RANK vs DENSE_RANK, and the Nth highest salary three ways.
- Transactions and ACID Properties in DBMS — What a transaction is, its states, and each ACID property on a bank transfer, with how a DBMS provides it: logs, recovery, locking, savepoints and WAL.
- Concurrency Control in DBMS — Concurrency control in DBMS: schedules, a worked precedence graph, recoverable schedules, anomalies, isolation levels, 2PL, timestamps and deadlocks.
- Indexing and B+ Trees in DBMS — Indexing in DBMS: clustered vs secondary, dense vs sparse, multilevel, B-tree vs B+ tree, B+ tree insertion with splits worked, hash and composite indexes.
- SQL vs NoSQL Databases — SQL vs NoSQL databases compared: document, key-value, wide-column and graph stores, ACID vs BASE, the CAP theorem, replication and sharding.
Test yourself
- SQL · Basic proctored skill test with a verifiable credential
- SQL · Intermediate proctored skill test with a verifiable credential