Skip to content
Tutorials Center
My learning

Topics

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

  1. 1 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.
  2. 2 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.
  3. 3 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.
  4. 4 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.
  5. 5 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.
  6. 6 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.
  7. 7 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

  1. 8 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.
  2. 9 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.
  3. 10 The Entity-Relationship Model Learn the ER model: entities, attributes (simple, composite, multivalued, derived), keys, relationships, cardinality and participation, with ER diagrams.
  4. 11 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.
  5. 12 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.
  6. 13 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

  1. 14 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.
  2. 15 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.
  3. 16 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.
  4. 17 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

  1. 18 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.
  2. 19 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.
  3. 20 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.
  4. 21 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.
  5. 22 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.
  6. 23 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

  1. 24 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.
  2. 25 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.
  3. 26 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.
  4. 27 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

  1. 28 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.
  2. 29 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.
  3. 30 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.
  4. 31 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.
  5. 32 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.
  6. 33 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.
  7. 34 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.
  8. 35 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

  1. 36 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.
  2. 37 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.
  3. 38 Schedules, Serializability and Precedence Graphs Learn serial and concurrent schedules, conflicting operations, the precedence graph test for conflict serializability, view serializability and recoverable schedules.
  4. 39 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

  1. 40 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.
  2. 41 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.
  3. 42 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.
  4. 43 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.
  5. 44 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

  1. 45 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.
  2. 46 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.
  3. 47 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.
  4. 48 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

  1. 49 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.
  2. 50 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.
  3. 51 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.
  4. 52 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.
  5. 53 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.
  6. 54 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.
  7. 55 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.
  8. 56 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.
  9. 57 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

  1. 58 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.
  2. 59 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.
  3. 60 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.
  4. 61 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.
  5. 62 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

  1. 63 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.
  2. 64 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.
  3. 65 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.
  4. 66 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.
  5. 67 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.
  6. 68 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.
  7. 69 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.
  8. 70 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

  1. 71 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.
  2. 72 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

  1. 73 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.
  2. 74 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.
  3. 75 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.
  4. 76 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

  1. 77 Exercises with Solutions Practice DBMS with nine solved exercises: ER mapping, relational algebra, SQL division, closure and keys, serializability, B+ trees and crash recovery.
  2. 78 DBMS Interview Questions Prepare for database interviews: concise model answers on ACID, keys, normalization, joins, isolation, deadlocks, indexes, B-trees, CAP and sharding.
  3. 79 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.