DBMS (Database Management Systems) Tutorial
Learn DBMS (Database Management Systems) step by step: 79 lessons in 15 chapters with examples, quizzes, diagrams and projects.
Start learning →
of lessons completed
Introduction to Databases
- What Is a Database? Learn what a database is, how data differs from information, what a DBMS does for you, and run your first queries against a small bookstore database in the browser.
- File Systems vs a DBMS See why storing data in plain files causes redundancy, inconsistency, lost updates and weak security, and how a DBMS solves each problem, with live SQL examples.
- DBMS Architecture and Components Tour the parts of a DBMS (query processor, storage manager, transaction manager, buffer manager, catalog), learn 1-, 2- and 3-tier designs, and read SQLite's own catalog.
- DBA, Developers and Users: Roles Around a Database Meet the people around a database: DBA duties, designers, developers and end users, with live SQL showing each role's view of the same data.
- Schemas, Instances and Data Independence Understand schema vs instance, the three-schema architecture, logical and physical data independence, and the DDL, DML, DCL and TCL sublanguages, with live SQL.
- Database Languages and Database Objects Learn the five families of SQL statements (DDL, DML, DQL, DCL, TCL) and the main database objects: tables, views, indexes, sequences and synonyms, with live examples.
- DBMS Interfaces and a Short History of Databases Tour the user interfaces a DBMS offers (menus, forms, GUIs, parametric, natural language) and six decades of database history, from IMS and CODASYL to SQL and NoSQL.
Data Models and ER Modelling
- Data Models Compared Compare hierarchical, network, relational, object, document, graph and key-value data models, learn why relational won, and see one example in several shapes.
- Object-Oriented and Object-Relational Databases Compare OODBMS and ORDBMS: objects, identity, user-defined types, inheritance and methods, plus how to mimic them in plain SQL with live examples you can run.
- The Entity-Relationship Model Learn the ER model: entities, attributes (simple, composite, multivalued, derived), keys, relationships, cardinality and participation, with ER diagrams.
- Weak Entities, Specialization and Aggregation Go beyond basic ER diagrams: weak entities, identifying relationships, ISA hierarchies with disjoint and total constraints, ternary relationships and aggregation.
- Mapping ER Diagrams to Tables Turn an ER diagram into relational tables: rules for entities, 1:1, 1:N and M:N relationships, weak entities, multivalued attributes and three ISA strategies.
- Design Case Study: A Hospital Database Design a hospital database from requirements to ER diagram to tables: modelling decisions, trade-offs, common mistakes and live queries on the finished schema.
The Relational Model
- Relations, Tuples and Domains Learn the formal vocabulary of the relational model: relations, tuples, attributes, domains, degree, cardinality, schema vs instance, and why SQL tables are bags.
- Keys and Integrity Constraints Understand superkeys, candidate, primary, alternate and foreign keys, entity and referential integrity, domain constraints and ON DELETE actions with live SQL.
- Nulls and Three-Valued Logic Understand what NULL means in a database, how three-valued logic (true, false, unknown) works, and why NULL surprises you in comparisons, aggregates, joins and NOT IN.
- Enforcing Integrity: Constraints, Assertions and Triggers See how a DBMS enforces rules: declarative CHECK, UNIQUE and deferred constraints, SQL assertions, and triggers as the last resort. Live SQLite examples.
Relational Algebra and Calculus
- Selection, Projection and Renaming Learn the unary operators of relational algebra: selection (sigma), projection (pi) and rename (rho), how they compose, and the SQL each one corresponds to.
- Union, Intersection, Difference and Cartesian Product Learn the binary operators of relational algebra: union, intersection, set difference and Cartesian product, union compatibility and their SQL equivalents.
- Joins in Relational Algebra Understand theta, equi, natural, outer, semijoin and antijoin in relational algebra, how a join is a product plus a selection, and the equivalent SQL.
- Division, Aggregation and Extended Operators Master relational division for 'for all' queries, the double NOT EXISTS pattern in SQL, plus the grouping and aggregate operator and generalized projection.
- Build a Relational Algebra Interpreter Implement select, project, rename, union, difference, product, join and division on sets of tuples in Python, then check every result against real SQL.
- Tuple and Domain Relational Calculus Learn tuple and domain relational calculus: tuple variables, the quantifiers exists and for all, safe expressions, Codd's theorem and how queries translate to SQL.
SQL for Database Courses
- DDL: Creating and Altering Schemas Use SQL DDL the way database courses teach it: CREATE, ALTER and DROP, data types and SQLite type affinity, and constraints that turn an ER design into a schema.
- Queries Through the Lens of Theory Read SELECT-FROM-WHERE as relational algebra: bag semantics, the logical evaluation order of a query, how joins and GROUP BY really work, with step-by-step demos.
- Nested Queries, Views and Materialization Correlated subqueries, EXISTS, IN, ANY and ALL, views as virtual tables, updatable views and INSTEAD OF triggers, and materialized views explained as a concept.
- Triggers, Stored Procedures and Cursors Understand event-condition-action rules, row versus statement triggers, live SQLite triggers, and how stored procedures and cursors work in server databases.
Database Design Theory
- Redundancy and Update Anomalies See why storing everything in one table causes redundancy and insertion, deletion and update anomalies, with a live bad schema and the decomposition that fixes it.
- Functional Dependencies Learn functional dependencies: trivial and non-trivial FDs, Armstrong's axioms, derived rules, and how to test an FD on real data in Python and SQL.
- Attribute Closure, Keys and Minimal Cover Compute attribute closure, find every candidate key and build a canonical minimal cover of an FD set, with a complete live Python implementation.
- 1NF, 2NF and 3NF Learn first, second and third normal form through functional dependencies: atomicity, partial and transitive dependencies, with a step-by-step live SQL decomposition.
- BCNF and Lossless Decomposition Learn Boyce-Codd normal form, how it differs from 3NF, the lossless-join chase test, dependency preservation and 3NF synthesis, with live Python tools.
- Multivalued Dependencies, 4NF and Denormalization Understand multivalued dependencies, fourth and fifth normal form, and when to denormalize on purpose, with live SQL showing a 4NF violation and its fix.
- Finding the Highest Normal Form, FD Equivalence and DKNF Find the highest normal form of a relation step by step, test whether two FD sets are equivalent, and learn what domain-key normal form (DKNF) promises.
- Schema Design Strategies and Schema Integration Compare top-down, bottom-up, inside-out and mixed schema design, then learn how to integrate two schemas by resolving naming and structural conflicts.
Transactions and ACID
- What Is a Transaction? Learn what a database transaction is: a unit of work, BEGIN, COMMIT and ROLLBACK, the transaction state diagram, savepoints and autocommit, with live SQLite examples.
- ACID Properties in Depth Learn the four ACID properties, which part of a DBMS enforces each one, what goes wrong when one is missing, and watch atomicity at work in a live SQLite demo.
- Schedules, Serializability and Precedence Graphs Learn serial and concurrent schedules, conflicting operations, the precedence graph test for conflict serializability, view serializability and recoverable schedules.
- Isolation Levels and Concurrency Anomalies Dirty reads, non-repeatable reads, phantoms, lost updates and write skew explained with a runnable interleaving simulator, plus SQL isolation levels and snapshots.
Concurrency Control
- Locks: Shared, Exclusive and Intention Learn shared and exclusive lock modes, the compatibility matrix, lock granularity, intention locks for multiple-granularity locking and how a lock manager works.
- Two-Phase Locking Learn the two-phase locking protocol, the growing and shrinking phases, why it guarantees serializability, strict and rigorous 2PL and lock conversion, with live demos.
- Deadlocks: Detection, Prevention and Recovery Understand deadlocks in a DBMS: wait-for graphs, cycle detection, victim selection, wait-die and wound-wait prevention, timeouts, plus runnable Python demos.
- Timestamp-Based Protocols Learn timestamp-ordering concurrency control: read and write timestamps, the accept or abort rules, the Thomas write rule and starvation, with a live simulator.
- MVCC and Optimistic Concurrency Control Learn multiversion concurrency control: version chains, visibility rules, snapshot isolation, write skew and optimistic validation, with live Python simulations.
Recovery
- Failure Classification and Recovery Goals Learn the three kinds of database failure, the storage types, the steal and no-force buffer policies and why every recovery system needs a log, with live simulations.
- Logging and Write-Ahead Logging Learn log records, the write-ahead logging rule, undo and redo recovery and how SQLite uses a rollback journal or a WAL file, with a live crash simulation.
- Checkpoints, ARIES and Backups Learn fuzzy checkpoints, the three ARIES recovery phases (analysis, redo, undo), LSNs, point-in-time recovery and backup types, with live Python simulations.
- Shadow Paging Recovery Learn shadow paging: current and shadow page tables, copy-on-write updates, commit by switching one pointer, and why most engines still prefer write-ahead logging.
Storage and Indexing
- Disks, Pages and the Buffer Pool Learn the storage hierarchy, HDD vs SSD costs, how rows are packed into pages, slotted-page layout and LRU or clock buffer replacement, with live demos.
- RAID Levels: Striping, Mirroring and Parity Learn how RAID combines disks for speed and fault tolerance: striping, mirroring, XOR parity, RAID 0/1/5/6/10 compared, and a live parity-rebuild simulation in Python.
- File Organization: Heap, Sorted and Clustered Compare heap, sorted and clustered file organizations with an I/O cost model, learn row vs column layout, and see rowid vs WITHOUT ROWID tables in SQLite.
- Indexing Basics: Dense, Sparse, Primary and Secondary Learn how ordered indexes work: dense vs sparse, primary vs secondary, clustering, multilevel and covering indexes, with SQLite query plans and a Python cost demo.
- Multilevel Indexing and ISAM Learn how ISAM builds a static multilevel index over sorted pages, why overflow chains slow it down and how it differs from a B+ tree, with a live simulation.
- B-Trees and B+ Trees Understand B-trees and B+ trees: node structure, search, insert with splits, delete with merges, height and fan-out, and run a live Python B+ tree that prints its nodes.
- Hashing: Static, Extendible and Linear Learn hash-based indexing: static hashing with overflow chains, extendible hashing with a directory, linear hashing, and how hashing compares to B+ trees.
- Bitmap, Composite, Partial and LSM Indexes Go beyond the plain B+ tree: composite index column order, partial and expression indexes in SQLite, bitmap and R-tree indexes, and LSM trees with a tiny Python LSM.
- Inverted Indexes and Text Retrieval Build an inverted index from scratch, answer AND and OR text queries, rank results with TF-IDF and cosine similarity, and see how databases and search engines use them.
Query Processing and Optimization
- The Query Processing Pipeline Follow a SQL query through parsing, algebra trees, logical and physical plans, the optimizer and the executor, with the Volcano iterator model and live EXPLAIN output.
- Join Algorithms Learn how a DBMS executes a join: nested loop, block nested loop, index nested loop, sort-merge and hash join, with I/O cost formulas and runnable Python counters.
- External Sorting and Aggregation See how a DBMS sorts tables bigger than memory with external merge sort, counts passes and I/O, and chooses sort-based or hash-based GROUP BY and DISTINCT.
- Statistics, Cost Estimation and Plan Search How a cost-based optimizer estimates row counts with statistics and histograms, searches join orders with dynamic programming, and how ANALYZE changes SQLite plans.
- Algebraic Equivalence and Heuristic Optimization Learn relational algebra equivalence rules, selection and projection pushdown, subquery rewriting and how to write optimizer-friendly SQL, with live plan comparisons.
Distributed Databases, NoSQL and Analytics
- Replication, Sharding and Distributed Transactions Understand leader-replica replication, horizontal and vertical partitioning, consistent hashing and two-phase commit, with runnable Python simulations of each.
- Distributed Database Types and Fragmentation Compare homogeneous and heterogeneous distributed databases, learn horizontal, vertical and mixed fragmentation with runnable SQL, and see how data placement drives query cost.
- CAP, BASE and Consistency Models Understand the CAP theorem, PACELC, BASE, eventual, causal and strong consistency, quorum reads and writes, and linearizability, with runnable Python simulations.
- NoSQL Databases: Key-Value, Document, Column and Graph Compare the four NoSQL families, learn how their data models differ from tables, try JSON documents and graph traversal in SQLite, and see polyglot persistence.
- HBase, HDFS and Hive Overview Understand the Hadoop data stack from a DBMS view: HDFS storage, HBase regions, HMaster, RegionServers and ZooKeeper, Hive SQL on files, and when HBase beats an RDBMS.
- OLTP vs OLAP, Columnar Storage and Data Warehouses Compare transactional and analytical workloads, build a star schema, see why column stores compress so well with a live run-length demo, and learn ETL, ELT and data lakes.
- Temporal, Spatial and Other Specialized Databases Learn temporal databases (valid time, transaction time, bitemporal tables), spatial data and indexes, plus mobile and multimedia databases, with live demos.
- Data Mining and Knowledge Discovery Overview Learn the KDD process and core data mining tasks: association rules, clustering and classification, with live Python demos of support, confidence, k-means and k-NN.
Security and Administration
- Authentication, Authorization and Access Control Learn users, roles, GRANT and REVOKE, least privilege, row and column security with views, and auditing, plus what SQLite cannot do and how to compensate.
- Injection, Encryption and Data Protection See SQL injection work against string-built queries and fail against parameters, then learn encryption at rest and in transit, password hashing, masking and GDPR basics.
Projects
- Project: Library Management System Design a library database end to end: requirements, ER diagram, relational mapping, constraints, a loan-limit trigger, overdue and availability reports, and a 3NF check.
- Project: University Course Registration Build a course registration database: many-to-many enrollments, prerequisite chains with a recursive CTE, capacity and prerequisite triggers, and atomic enrollment.
- Project: Build a Mini Database Engine Build a tiny database engine in Python: paged heap storage, a B+ tree index, a small SQL parser and a write-ahead log that recovers after a crash.
- Project: A Normalization Toolkit Write a Python toolkit that reads functional dependencies, finds keys, tests 2NF, 3NF and BCNF, decomposes schemas and proves the result lossless.
Practice and Review
- Exercises with Solutions Practice DBMS with nine solved exercises: ER mapping, relational algebra, SQL division, closure and keys, serializability, B+ trees and crash recovery.
- DBMS Interview Questions Prepare for database interviews: concise model answers on ACID, keys, normalization, joins, isolation, deadlocks, indexes, B-trees, CAP and sharding.
- DBMS Cheat Sheet A one-page DBMS cheat sheet: ER mapping rules, relational algebra symbols, normal-form tests, isolation levels, lock matrix, cost formulas and SQL syntax.