AI InterviewSearch…Search problems, topics, or pages…Ctrl KGet started

CS notes · DBMS

Database Management Systems

ER models, keys, normalization, SQL joins and aggregation, transactions and ACID, concurrency control and indexing.

12 notes · about 3 hours of reading · free, no sign-in needed

Read in this order

basics first

  1. 01Introduction to DBMSWhat a DBMS is and why it beats a file system: data models, the three-schema architecture, data independence, database users and types of databases.Beginner · 11 minBeginner · 11 min
  2. 02ER Model in DBMSThe ER model in DBMS: entities, attribute types, relationships, cardinality, participation, weak entities, generalization and ER diagram to table rules.Beginner · 13 minBeginner · 13 min
  3. 03Keys in DBMSEvery key in DBMS with one worked table: super, candidate, primary, alternate, foreign, composite, surrogate and unique keys, plus integrity constraints.Beginner · 11 minBeginner · 11 min
  4. 04Functional Dependencies in DBMSFunctional dependencies in DBMS: Armstrong's axioms, attribute closure step by step, finding every candidate key, and computing a canonical cover, all worked.Intermediate · 13 minIntermediate · 13 min
  5. 05Normalization in DBMS: 1NF to BCNFNormalization in DBMS on one running example: anomalies, 1NF, 2NF, 3NF and BCNF decompositions, lossless joins, dependency preservation, 4NF, denormalizing.Intermediate · 15 minIntermediate · 15 min
  6. 06SQL Basics: DDL, DML, DCL and TCLSQL 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.Beginner · 11 minBeginner · 11 min
  7. 07SQL JoinsEvery 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.Beginner · 12 minBeginner · 12 min
  8. 08SQL GROUP BY, HAVING and SubqueriesSQL 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.Intermediate · 12 minIntermediate · 12 min
  9. 09Transactions and ACID Properties in DBMSWhat 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.Intermediate · 12 minIntermediate · 12 min
  10. 10Concurrency Control in DBMSConcurrency control in DBMS: schedules, a worked precedence graph, recoverable schedules, anomalies, isolation levels, 2PL, timestamps and deadlocks.Advanced · 16 minAdvanced · 16 min
  11. 11Indexing and B+ Trees in DBMSIndexing in DBMS: clustered vs secondary, dense vs sparse, multilevel, B-tree vs B+ tree, B+ tree insertion with splits worked, hash and composite indexes.Intermediate · 15 minIntermediate · 15 min
  12. 12SQL vs NoSQL DatabasesSQL vs NoSQL databases compared: document, key-value, wide-column and graph stores, ACID vs BASE, the CAP theorem, replication and sharding.Intermediate · 13 minIntermediate · 13 min
Start with Introduction to DBMS

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

  1. 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.
  2. ER Model in DBMS — The ER model in DBMS: entities, attribute types, relationships, cardinality, participation, weak entities, generalization and ER diagram to table rules.
  3. Keys in DBMS — Every key in DBMS with one worked table: super, candidate, primary, alternate, foreign, composite, surrogate and unique keys, plus integrity constraints.
  4. 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.
  5. 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.
  6. 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.
  7. 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.
  8. 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.
  9. 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.
  10. Concurrency Control in DBMS — Concurrency control in DBMS: schedules, a worked precedence graph, recoverable schedules, anomalies, isolation levels, 2PL, timestamps and deadlocks.
  11. 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.
  12. 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

Other subjects