2026/2027

Принципы дизайна реляционных баз данных и OLAP-аналитики

Статус: Маго-лего

Кто читает: Международная лаборатория прикладного сетевого анализа

Где читается: Факультет социальных наук

Когда читается: 3 модуль

Охват аудитории: для своего кампуса

Преподаватели: Пашков Станислав Георгиевич

Язык: английский

Кредиты: 3

Контактные часы: 32

Course Syllabus

Abstract

This course focuses on the application of mechanisms for operational analytical processing of heterogeneous data in production and research tasks, implemented using OLAP methodology. The course comprises a series of OLAP analytics topics, including an understanding of the fundamental and applied aspects of developing and implementing database management systems (DBMS), and the use of relational languages ​​(SQL) for writing simple and advanced analytical queries and data processing pipelines. Particular attention is paid to constructing OLAP cubes and managing them using a programming language. Analytical and technological challenges that data analysts may face in analyzing streaming data are discussed. All functional principles and examples, including statistical inference, are implemented using the GNU R language and PostgreSQL/DuckDB DBMS engines.

Learning Objectives

• Master the principles of designing relational databases for storing market and sociological data.
• Master modern methods of analytical data processing in the OLAP paradigm using columnar DBMS.
• Integrate DBMS with statistical analysis environments to build end-to-end analytical pipelines and formulate business conclusions.

Expected Learning Outcomes

• Explain the fundamental differences between sequence files, OLTP and OLAP architectures, and justify the use of relational DBMS for storing market and sociological data.
• Design conceptual models of subject areas (using the Retail/CRM case as an example) using Crow's Foot notation, correctly identifying entities, attributes, and relationship types.
• Analyze business requirements and decompose them into relational structures, defining primary (PK) and foreign (FK) keys to ensure logical data integrity.
• Evaluate the structure of raw tables for data modification anomalies (inserts, updates, deletions) and reasonably apply normal forms (1NF–3NF) to eliminate them.
• Implement a physical database schema in the PostgreSQL environment using DDL language (CREATE, ALTER) and integrity mechanisms (PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE).
• Apply basic data modification operations (INSERT, UPDATE, DELETE) taking into account transaction security and imposed integrity constraints.
• Formulate complex analytical SQL queries (DML) using multi-level joins (JOIN, SELF JOIN), CTE, and nested subqueries to solve business problems.
• Apply window functions (ROW_NUMBER, RANK, LAG, LEAD, aggregations with OVER) to calculate moving averages, rank, and analyze time slices without row collapse.
• Explore the logic of SQL query execution and optimize code syntax to improve the efficiency of data retrieval from a normalized OLTP database.
• Compare architectural approaches to data storage (row-based vs. columnar) and justify the choice of a column-based DBMS (ClickHouse) for analytical processing of large volumes of data.
• Design multidimensional data models (Star and Snowflake schemas), dividing data into fact tables and dimension tables to optimize OLAP queries.
• Deploy and configure the ClickHouse environment, implementing data loading (ETL/ELT) and denormalization processes from a relational OLTP system (PostgreSQL) into analytical data marts.
• Perform basic OLAP cube operations (Roll-up, Drill-down, Slice, Dice) for multidimensional data aggregation and generating summary analytical reports.
• Model the processing of streaming and panel data, using specialized functions for working with timestamps and sliding windows.
• Interpret the results of time series and event log analysis, identifying behavior patterns, anomalies, and retention indicators in the ClickHouse environment.
• Integrate analytical environments with DBMS (PostgreSQL/ClickHouse) by setting up a programmatic connection (via DBI, SQLAlchemy) and implementing end-to-end extraction pipelines
• Perform statistical inference and visualize aggregated data using R/Python to test hypotheses and build dashboards based on OLAP displays.
• Justify business conclusions and architectural decisions based on the results of an end-to-end analytical project, defending the correctness of the chosen approach to processing market and sociological data.

Course Contents

• Architectures of Database Management Systems and Conceptual Data Modeling
• Relational Theory, Data Normalization, and Physical Implementation in PostgreSQL
• Advanced SQL Querying: Analytical Pipelines and Window Functions
• The OLAP Paradigm and Columnar Database Management Systems (ClickHouse)
• Data Pipeline Integration: DBMS Connectivity with R and Statistical Inference
• Multidimensional OLAP Cubes, Streaming Data, and Time-Series Analytics

Assessment Elements

• Relational Database Design (Conceptual Map, ER, Physical Management)
  This individual homework assignment serves as the foundational "primer" of the course, introducing students to the full cycle of relational database design. Issued at the end of Week 1 and due at the end of Week 2, the assignment requires students to translate a textual business-domain description into a properly designed relational database in PostgreSQL. Each student receives a personalized dataset variant (seeded by their student ID) to prevent cross-copying and LLM-generated generic solutions. The deliverables include: (1) a hand-drawn Entity-Relationship diagram in Crow's Foot notation (scanned/photographed), (2) a written normalization rationale (1NF → 3NF) with identified data anomalies, (3) a complete DDL script with primary keys, foreign keys, and at least three CHECK constraints, and (4) test INSERT statements populating each table with 3–5 referentially consistent rows. The assignment is intentionally lightweight to provide a gentle on-ramp for students with no prior SQL background, while laying the OLTP foundation that will be reused in Homework №2, the Laboratory Work, and the Final Project. No late-submission penalties apply; one voluntary revision is accepted before the Homework №2 deadline to incorporate instructor feedback.
• Advanced SQL Querying for Business Intelligence
  This individual homework assignment builds directly on the Pagila schema that students designed and implemented in Homework №1. Each student writes a coherent analytical SQL script (10 numbered queries) against their own PostgreSQL instance populated with a uniquely seeded dataset (generated from their student ID), ensuring that result sets are idiosyncratic and LLM-generated generic solutions fail to execute. The assignment focuses on advanced analytical constructs: Common Table Expressions (CTEs) for building readable data pipelines, window functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE, running/rolling aggregates with SUM() OVER, RANGE vs ROWS frames), complex multi-table JOINs (including SELF JOINs and cross-temporal joins), and correlated subqueries. Each query must be accompanied by a brief business interpretation (2–3 sentences), extensive inline code comments explaining design choices, and a written reflective section answering four questions tied to the student's specific implementation. Reflective questions embedded in the report ensure that students understand why specific constructs were chosen.
• OLAP Analytics with R/Python Integration
  This individual laboratory work represents the most technically demanding form of control before the Final Project, requiring students to execute a complete OLAP analytical pipeline by migrating their normalized PostgreSQL schema into an analytical database system of their choice. Students may select one of three supported platforms based on their technical environment and preferences: (a) PostgreSQL with analytical extensions, (b) DuckDB, or (c) ClickHouse. The choice of platform must be justified in the reflective section of the report (info provided by Tutor). Students must design and implement a multidimensional Star or Snowflake schema, execute a reproducible ETL/ELT process, and perform the full spectrum of OLAP cube operations (Roll-up, Drill-down, Slice, Dice) using the SQL dialect of their chosen platform. The second component addresses streaming and time-series analytics: students analyze event logs with temporal windows (tumbling, sliding, sessionization) and retention patterns using a uniquely seeded synthetic dataset (generated from their student ID to prevent cross-copying and ensure idiosyncratic result sets). The third component requires programmatic integration with R (via DBI/duckdb/RClickhouse/RPostgres) or Python (via SQLAlchemy/duckdb/clickhouse-connect/psycopg2): students extract OLAP-aggregated data into a DataFrame, perform statistical inference or cohort analysis, and produce publication-quality visualizations with business interpretation. This laboratory work consolidates the OLAP competencies required for the Final Project and simulates real-world Analytics Engineer workflows, where the choice of analytical platform is itself an architectural decision. Reflective questions embedded in the report ensure that students understand platform-specific trade-offs.
• Final Project
  The Final Project is the capstone assessment of the course, requiring each student to independently design, implement, and present a complete analytical data pipeline that integrates all core competencies developed throughout the module: conceptual data modeling, relational database design in PostgreSQL, advanced analytical SQL, migration to a columnar OLAP system (ClickHouse or alternative), dimensional modeling, streaming/time-series analytics, and programmatic integration with R or Python for statistical inference and visualization. Each student selects one of two business domains (a Pagila+ streaming extension or a longitudinal panel survey infrastructure) and delivers a reproducible Git repository containing the full DDL/ETL/SQL codebase, an analytical RMarkdown/Jupyter notebook, and a concise PDF executive report. The project culminates in a structured presentation (maximum 15 slides) in which the student documents the architectural decisions, demonstrates key query results and visualizations, and articulates data-driven business recommendations. This form of control simulates the end-to-end workflow of an Analytics Engineer and produces a portfolio-grade artifact suitable for job-market signaling.

Interim Assessment

• 2026/2027 3rd module
  0.4 * Final Project + 0.15 * Relational Database Design (Conceptual Map, ER, Physical Management) + 0.25 * OLAP Analytics with R/Python Integration + 0.2 * Advanced SQL Querying for Business Intelligence

Bibliography

Recommended Core Bibliography

• Beaulieu, A. (2009). Learning SQL : Master SQL Fundamentals: Vol. 2nd ed. O’Reilly Media.
• Celko, J. (2006). Joe Celko’s Analytics and OLAP in SQL. San Francisco, Calif: Morgan Kaufmann. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=195632
• Date, C. J. (2015). SQL and Relational Theory : How to Write Accurate SQL Code (Vol. Third edition). Sebastopol, CA: O’Reilly Media. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=1099367
• Garcia-Molina, H., Ullman, J. D., Dawson Books, & Widom, J. (2014). Database Systems: Pearson New International Edition : The Complete Book (Vol. Second edition). Harlow, Essex: Pearson. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=1418178
• Gillespie, C., & Lovelace, R. (2016). Efficient R Programming : A Practical Guide to Smarter Programming. Sebastopol, CA: O’Reilly Media. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=1435808
• Juba, S., & Volkov, A. (2019). Learning PostgreSQL 11 : A Beginner’s Guide to Building High-performance PostgreSQL Database Solutions, 3rd Edition (Vol. Third edition). Birmingham, UK: Packt Publishing. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=2023612
• Jukic, N., Vrbsky, S., & Nestorov, S. (2017). Database Systems : Introduction to Databases and Data Warehouses. Burlington, Virginia: Prospect Press. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=1562389
• Malik, U., Goldwasser, M., & Johnston, B. (2019). SQL for Data Analytics : Perform Fast and Efficient Data Analysis with the Power of SQL. Packt Publishing.
• Medeiros, K. (2018). R Programming Fundamentals : Deal with Data Using Various Modeling Techniques. Birmingham: Packt Publishing. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=1904978
• Ren, K. (2016). Learning R Programming. Birmingham: Packt Publishing. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=1409189

Recommended Additional Bibliography

• 45388 - PostgreSQL. Профессиональный SQL - Е.Моргунов - ДМК Пресс - 9785937003737 - 2025 - https://hse.alpinadigital.ru/document/45388 - Alpina
• SQL. Pocket guide. 4-е изд. - 978-601-08-3728-7 - Жао Элис - 2024 - Астана: Спринт Бук - https://ibooks.ru/products/397506 - 397506 - iBOOKS
• Погодаев, А. К. Обработка данных на языке SQL в реляционных системах : учебное пособие / А. К. Погодаев, Р. В. Батищев. — Липецк : Липецкий ГТУ, 2024. — 84 с. — ISBN 978-5-00175-247-9. — Текст : электронный // Лань : электронно-библиотечная система. — URL: https://e.lanbook.com/book/434576 (дата обращения: 00.00.0000). — Режим доступа: для авториз. пользователей.
• Соймина, Е. Я. Сводные таблицы как средство разработки OLAP-кубов в MS Excel : учебно-методическое пособие / Е. Я. Соймина. — Москва : РУТ (МИИТ), 2019. — 48 с. — Текст : электронный // Лань : электронно-библиотечная система. — URL: https://e.lanbook.com/book/175585 (дата обращения: 00.00.0000). — Режим доступа: для авториз. пользователей.

Authors

• PASHKOV STANISLAV GEORGIEVICH
• PAVLOVA IRINA ANATOLEVNA