📌 Important:
The weekly topics may be subject to minor adjustments. Some topics might be added, removed, or shifted slightly during the course based on class progress and feedback.

Introduction to Databases and Data Concepts (1 week)

  • Overview of course structure, grading, and final project.
  • DIKW pyramid (Data → Information → Knowledge → Wisdom).
  • Ethical considerations and bias in data.
  • Data types: structured, semi-structured, unstructured.
  • Levels of data analytics: descriptive, diagnostic, predictive, prescriptive.

Overview of Database Models and ER Design (1–2 weeks)

  • Introduction to Entity-Relationship (ER) modeling).
  • Entities, attributes, relationships, and cardinality.
  • Weak entities and composite attributes.
  • Multi-valued attributes and derived attributes.

Relational Model and Mapping ER to Relational Schema (1 week)

  • Introduction to the relational model (tables, primary keys, foreign keys).
  • Mapping ER diagrams to relational tables.
  • Constraints and integrity rules.
  • Example: converting an ER diagram of a sample database into a relational schema.

Introduction to PostgreSQL and Basic SQL (1 week)

  • Setting up PostgreSQL and connecting via GUI/CLI.
  • Creating databases and tables.
  • Inserting, updating, and deleting records.
  • Simple queries: SELECT, WHERE, ORDER BY, LIMIT.

Intermediate SQL Queries (2-3 weeks)

  • Aggregate functions: COUNT, SUM, AVG, MIN, MAX.
  • Grouping data with GROUP BY and filtering with HAVING.
  • Sorting and combining data with JOIN.
  • Practical exercises on real datasets.

Advanced SQL Queries (2-3 weeks)

  • Nested queries and subqueries.
  • Set operations: UNION, INTERSECT, EXCEPT.
  • Window functions.
  • Query performance basics (indexes – overview only).

Introduction to Analytical Databases and DuckDB (1 week)

  • Concept of analytical databases and how they differ from transactional ones (OLAP vs OLTP).
  • Introduction to DuckDB and why it’s useful for local analytics.
  • Setting up DuckDB and basic commands.
  • Importing CSV and Parquet files directly into DuckDB.

Data Exploration with DuckDB (1 week)

  • Combining DuckDB with Pandas for hybrid workflows.
  • Exploratory Data Analysis (EDA): summary statistics, filtering, grouping.
  • Hands-on: analyzing a sample dataset

Data Cleaning and Transformation (1 week)

  • Handling missing data and outliers.
  • Converting data types and formatting.
  • Creating computed columns and derived tables.
  • Practical: preparing a dataset for analysis.

Data Visualization and Reporting (1 week)

  • Basics of visualization tools: Power BI and Streamlit.
  • Connecting visualization tools to DuckDB/Postgres.
  • Building dashboards for interactive data reporting.
  • NoSQL databases and their use cases (document, key-value, graph).
  • Vector databases and their role in semantic search and AI workflows.
  • Overview of query optimization and indexing (analytical perspective).
  • Future trends in analytical data management.