AI curriculum

Course outline for Database Systems for AI Applications

Choose a database from the questions and updates an application must support. Start with inventory records, relationships, queries, transactions, and recovery. Run SQLite queries and compare four data representations. Add ordinary indexes and full-text search, then compare vector search in PostgreSQL with a dedicated vector database. Apply these foundations to assistant documents and agent state without assuming that every application needs a vector store.

Database Systems for AI Applications Course Objectives

  • Model identities and relationships in four common data representations.
  • Execute queries, updates, constraints, and transactions with independently checked results.
  • Compare SQLite scans and indexes using query plans and observed costs.
  • Create and query a full-text index, then test correction and deletion.
  • Demonstrate a concurrent update problem and reject stale application writes.
  • Explain vector similarity, approximate search, filtering, and index lifecycle trade-offs.
  • Compare PostgreSQL with pgvector and a dedicated vector database against workload requirements.
  • Recommend a primary store with recovery, access, and derived-index responsibilities.

Pre-requisites

  • Write small Python functions and read files and structured records.
  • Read a table and express a lookup or update requirement.
  • Run a local program and compare its output with an expected result.
  • No SQL, database server, embedding model, or AI framework experience is required.

Before this course

Complete the required courses or bring equivalent experience. Recommended courses add useful depth; they are not enrolment gates.

No prerequisite course is required. The entry skills above still apply.

Recommended preparation

Readiness checks

  • Write small Python functions and read files and structured records.
  • Read a table and express a lookup or update requirement.
  • Run a local program and compare its output with an expected result.
  • No SQL, database server, embedding model, or AI framework experience is required.

Write small Python functions and read files and structured records.

Find your place in the learning map

Lab Setup

Delivery requirements: the environment, access, and exercise materials must be prepared and verified before delivery. This outline does not certify that a lab package is ready.

Supported stable Python and SQLite with FTS5, tests, two-connection scripts, disposable databases, synthetic inventory/assistant records, representation adapters, independent cases, and backup/restore tooling. Delivery requires a successful FTS5 create/query check, known transaction/build settings, checked plans/ranking, dated provenance-labeled pgvector/Qdrant reference measurements, and cleanup. No PostgreSQL installation, paid embedding/model call, or dedicated vector service is required here.

Detailed Course Outline

Module 1: Give application records durable structure (2 hours)

  • Persistence responsibilities: Persistence, database responsibilities, and inventory access patterns.
  • Relational foundations: Relational tables, identities, keys, relationships, and constraints.
  • SQLite operation: SQLite basics: connect, create, insert, select, join, and update.
  • Transactions and invariants: Transactions, rollback, and meaningful inventory invariants.
  • Independent result checks: Invalid records, missing relationships, and independently checked results.
  • Guided-time allocation: 0.75 hours for explanation and a worked example, 0.75 hours for learner practice, 0.25 hours for feedback, and 0.25 hours for questions and contingency.

Practical outcome: Persist and query an inventory with linked records, transaction rollback, and rejected invalid changes.

Module 2: Compare models by running the questions (2 hours)

  • Document representations: Documents, nesting, duplication, and cross-document consistency.
  • Key-value representations: Key-value lookup, explicit keys, caches, and expiration concepts.
  • Graph representations: Graph nodes, relationships, paths, and relational join alternatives.
  • Executable model alternatives: Executable JSON-document, dictionary, and adjacency-list representations.
  • Matched workload questions: Identical lookup, relationship, and update questions across representations.
  • Representation limits: Representation behavior versus database-server guarantees and performance.
  • Guided-time allocation: 0.75 hours for explanation and a worked example, 0.75 hours for learner practice, 0.25 hours for feedback, and 0.25 hours for questions and contingency.

Practical outcome: Run three workload questions across four representations and explain each representation's update burden.

Module 3: Find rows and words without reading everything (2 hours)

  • Scans and B-tree access: Table scans versus B-tree access for equality and range queries.
  • SQLite query plans: SQLite index creation, query plans, selectivity, and unchanged result sets.
  • Index maintenance cost: Read benefit versus write work, storage, and index maintenance.
  • Inverted text indexes: Full-text inverted indexes: tokens, document references, and phrase positions.
  • SQLite FTS5 operation: SQLite FTS5 creation, MATCH, tokenization, phrases, and BM25 ranking limits.
  • Search correction and deletion: Search correction, deletion, no-match cases, and retained-copy responsibilities.
  • Guided-time allocation: 0.75 hours for explanation and a worked example, 0.75 hours for learner practice, 0.25 hours for feedback, and 0.25 hours for questions and contingency.

Practical outcome: Create ordinary and FTS5 indexes, compare plans and results, and test changed and deleted searchable records.

Module 4: Understand state under change and failure (2 hours)

  • Connection boundaries: Two SQLite connections and observed read/write boundaries.
  • Stale-write protection: Stale application overwrite and a checked-version correction.
  • Deployment responsibilities: Embedded, client/server, and distributed deployment responsibilities.
  • Distributed-data concepts: Replication, partitioning, consistency, and partial failure as distinct concerns.
  • Recovery and access: Backup, restoration, migrations, ownership, and access control.
  • Local-observation limits: Curated distributed traces and limits of local observations.
  • Guided-time allocation: 0.75 hours for explanation and a worked example, 0.75 hours for learner practice, 0.25 hours for feedback, and 0.25 hours for questions and contingency.

Practical outcome: Reproduce and reject a stale application update, restore a backup, and explain the boundary of local evidence.

Module 5: Compare vector indexes and vector databases (2 hours)

  • Vector representations: Embeddings as numeric vectors, compatible dimensions, models, and versions.
  • Similarity metrics: L2 distance, cosine similarity, inner product, and normalization assumptions.
  • Exact and approximate neighbors: Exact nearest neighbors versus approximate HNSW graphs and IVFFlat lists.
  • Vector-index lifecycle: Build, memory, updates, rebuilds, search effort, filtering, and recall trade-offs.
  • pgvector and dedicated databases: PostgreSQL with pgvector versus Qdrant collections, points, payloads, and persistence.
  • Full-text vector and hybrid choices: Full-text, vector, and hybrid retrieval choices against relevance and operating requirements.
  • Guided-time allocation: 0.75 hours for explanation and a worked example, 0.75 hours for learner practice, 0.25 hours for feedback, and 0.25 hours for questions and contingency.

Practical outcome: Interpret supplied reference results, explain a filtered-search miss, and justify an index/store choice without claiming a vector runtime run.

Module 6: Choose the simplest store that meets the need (2 hours)

  • Workload requirements: Workload requirements: query shape, latency, volume, and update correctness.
  • AI application transfer: Conversations, source documents, relationships, and agent-state transfer cases.
  • Authoritative and derived stores: Authoritative records versus derived text indexes, caches, and embedding indexes.
  • Index refresh and deletion: Correction, re-embedding, index refresh, deletion propagation, and retained references.
  • Multi-store responsibilities: Recovery, authorization, synchronization, and avoidable multi-store complexity.
  • Justified storage choice: A documented choice and a rejected alternative with measurable conditions.
  • Guided-time allocation: 0.75 hours for explanation and a worked example, 0.75 hours for learner practice, 0.25 hours for feedback, and 0.25 hours for questions and contingency.

Practical outcome: Recommend a primary store, test a correction/deletion flow, and explain when a secondary retrieval store earns its cost.

Practical exercise

  • Build a small inventory in SQLite and represent the same entities as JSON documents, key-value records, and an adjacency graph. Extend prepared adapters to answer item lookup, supplier relationship, and low-stock questions. Compare actual result sets against independently specified expected identities. Test an invalid relationship and rollback. Lightweight representations are not four database-server installations, and their timings are not vendor benchmarks.
  • Extend the inventory with product descriptions and maintenance notes. Create and drop a SQLite B-tree index on a non-primary-key filter column. Predict the access path, run EXPLAIN QUERY PLAN before and after, and compare identical equality/range-query results with a separately checked answer set. Include a low-selectivity query where a scan can remain sensible. Record observed plans, repeat read and update measurements on matched disposable copies, and compare allocated database pages after equivalent committed writes. State dataset size, cache conditions, repetitions, and measurement limits. Do not require an index to win or equate planner text with a measured speedup. Account for existing primary-key indexes when defining the baseline.
  • Create a normal content-storing FTS5 virtual table linked by stable record IDs. Insert the searchable notes, run token and phrase MATCH queries, and inspect BM25 ordering with a stable ID tie-break. Explain why smaller FTS5 BM25 scores sort first. Tokenization changes matching; a rank is not a truth probability. Contrast a word match with a substring LIKE query and a synonym absent from the text. Full-text search is neither SQL LIKE nor embedding similarity. Use unicode61 for the core task; optional stemming is not automatic synonym understanding. Change and delete records in both the authoritative table and the FTS5 table through a learner-authored transactional update function. Deliberately omit one refresh in a disposable failure case, detect the stale match, and repair it. Check old-term absence, new-term presence, deleted-ID absence, phrase order, and a no-match query independently. This explicit copy does not synchronize itself. External-content triggers and rebuild mechanics are an optional extension, not another required implementation.
  • Use two actual SQLite connections to read a version and submit competing edits. Reproduce a stale application overwrite across separate read/write transactions, then reject it with a checked-version update. Query final state independently. SQLite serializes writers; this is not proof of distributed isolation behavior. Restore a disposable backup and compare its records with the expected snapshot.
  • For the vector comparison, use a small labeled vector illustration and a dated reference packet with the actual measurement source, dataset, embedding model/version, metric, dimensions, filters, exact top-k baseline, approximate top-k IDs, settings, latency samples, and build/storage observations. A performance comparison requires provenance-labeled reference measurements; no unsourced speed claim is justified. Learners calculate recall at k against the same eligible exact baseline and interpret a filtered miss and changed search effort. Recall measures neighbor recovery, not semantic usefulness. Tiny hand-selected vectors explain distance only; they are not evidence of embedding quality or real speed gains. Invalid dimension/model combinations and missing eligible results need explicit diagnosis. This is supplied-reference interpretation, not learner vector operation.
  • Compare pgvector as a PostgreSQL extension with Qdrant as a dedicated vector database. Explain HNSW's graph build/memory burden and IVFFlat's representative data, trained lists, probes, and rebuild considerations under changed data. Approximate post-scan filtering can reduce returned candidates in pgvector; more search effort can help but is not an unlimited recall guarantee. Qdrant's payload indexes and filter-aware planning differ; do not generalize one engine's filter execution to all vector stores. Compare collections, point IDs, payload metadata, persistent storage, backup ownership, access enforcement, and cross-store correction/deletion costs. A payload filter does not by itself enforce authorization. Neither choice guarantees better speed, lower cost, or transactional equivalence.
  • Choose full-text for a supplied exact-name question and consider vectors for a paraphrase question, checking failures in each. Compare a hybrid result list with the separate lists and explain a fusion choice such as reciprocal rank fusion. Raw text ranks and vector distances have different meanings and scales; simple score addition is not an equivalent or automatically valid fusion rule. Training, embedding API calls, live Qdrant operation, and hybrid implementation are not required. Actual PostgreSQL full-text and pgvector practice follows in PostgreSQL Development; Vector and AI Data Infrastructure develops deeper retrieval evaluation and integration.
  • Finally transfer the inventory model to assistant documents and agent state. Implement a small mock derived-index adapter, correct and delete source records, and inspect retained references. Mark incomplete propagation. A mock is not vector index implementation, and a removed search hit is not complete erasure from logs or backups. Recommend one primary store, justified indexes, recovery ownership, and a measurable trigger for reconsideration. Complete changed-query, note, competing-edit, and workload tasks independently before opening feedback.

Delivery and timing

  • Guided hours include teaching, demonstrations, learner practice, feedback, questions, and contingency as allocated above. Breaks, bulk downloads, and prerequisite setup are additional. Allocations need a timed learner pilot before delivery; they are not measured completion times.

What you will produce

Operate database foundations and text indexes, then choose storage and vector-search approaches from explicit requirements.

How the work is checked

  • Four executable representations preserve identities and relationship meanings.
  • Queries and constraint/rollback cases have independently checked actual results.
  • Before/after plans and matched observations justify an index decision without universal speed claims.
  • Learner-authored FTS5 queries distinguish token, phrase, substring, and absent-synonym behavior.
  • Actual FTS5 mutation tests expose stale data and verify correction and deletion.
  • Competing SQLite edits reproduce a stale overwrite and then reject it.
  • A restored backup contains the checked snapshot records.
  • Vector explanations distinguish exact recall, semantic relevance, filtering, and approximation.
  • A new store choice distinguishes a PostgreSQL extension from a dedicated vector database.
  • Reference-result interpretation is labeled separately from actual learner execution.
  • The final decision assigns authority, access, recovery, refresh, deletion, and retained-copy responsibilities.

12 guided hours. General database foundations precede AI transfer. SQLite ordinary indexes and FTS5 are hands-on. Document, key-value, and graph models use executable representations, not four product installations. Distributed systems, pgvector, and Qdrant are sourced comparisons using labeled reference evidence; no learner vector-service operation is claimed here. PostgreSQL full-text and pgvector practice follows in PostgreSQL Development; deeper retrieval integration follows in Vector and AI Data Infrastructure. No production sizing, vendor benchmark, or complete deletion guarantee is claimed.

When another approach is better

  • Do not add a graph or distributed store when simple relational queries meet the requirements.
  • Avoid a separate read model when synchronization and stale-data cost exceed its query benefit.

How we train

Contact us for full course details, including duration, delivery options and lab requirements.

Ask about exercises, instructor feedback and the prior knowledge you need. Tool-specific courses marked provisional may become modules in a broader course.

Discuss your learning goals