Data Bases

2026/2027

Учебный год

ENG

Обучение ведется на английском языке

5

Кредиты

Кто читает:

Департамент социологии

Статус:

Курс по выбору

Когда читается:

2-й курс, 3, 4 модуль

Course Syllabus

Abstract

The “Databases” course develops in second-year students of the “Sociology and Social Informatics” programme applied skills in working with relational and columnar database management systems. The course is built from theoretical foundations - the relational model, normalization and ER modelling - to the practical design and implementation of databases using PostgreSQL, as well as working with the ClickHouse column-oriented DBMS for analytical workloads. The course content has been updated in accordance with modern practice as a data analyst and BI developer. The main DBMS of the course is PostgreSQL - an open, widely used relational DBMS in the industry. A separate significant block is devoted to ClickHouse, a column-oriented DBMS that has become the de facto standard for analytical workloads in Russian and international practice, including in companies such as Yandex, Avito, Cloudflare. NoSQL models (document, key-value, graph) are reviewed in order to form a student’s understanding of which DBMS to choose for the task. The course deliberately does not duplicate the content of the “Applied Software” discipline, in which students master BI tools (Fastboard) and the principles of constructing dashboards. Within the framework of “Databases”, dashboards and the BI front are not considered: the objective of the course is to teach how to correctly design a data schema and effectively extract data from a DBMS, so that this data can then be used in any BI tool. The connection between DBMS and BI tools is reviewed in the final section as a data transfer mechanism (ODBC/JDBC/native connectors). The course is built around an end-to-end project: a small group of students (2-3 people) selects a subject area, carries out its ER modelling, designs and normalizes a relational schema, implements it in PostgreSQL, fills it with data and implements a series of complex analytical queries. The final defence includes a presentation of the scheme, demonstration of requests and justification for the design decisions made. This experience is directly transferable to 3rd year coursework and graduation thesis, which require independent data preparation.

Learning Objectives

• • To develop in students a holistic understanding of the architecture of modern DBMS, the differences between transactional (OLTP) and analytical (OLAP) systems, as well as the criteria for choosing a DBMS class for an analytical task;
• • Provide a solid theoretical foundation for the relational data model, normalization (1NF–3NF), and ER modelling principles;
• • Develop applied skills in writing SQL queries of arbitrary complexity, including joins, window functions and CTEs;
• • Learn to design and implement a relational database in PostgreSQL using indexes, integrity constraints and stored procedures;
• • Give an idea of working with column-oriented DBMS using ClickHouse as an example, including features of storage schemes, sorting keys and partitioning;
• • Develop an overview of NoSQL models and principles of integrating DBMS with BI tools.

Expected Learning Outcomes

• design and implement normalized database structures by creating database tables, queries, triggers, stored procedures, reports, and forms
• model different domain areas using ER approach
• perform typical BI reporting queries using SQL and analytical tools
• produce custom reports and dashboards based on DB data in Tableau and/or Power BI
• understand applications of BI to decision support in modern companies
• Understand the architecture of relational DBMS, describe the differences between transactional (OLTP) and analytical (OLAP) workloads, and reasonably select a DBMS class (relational, columnar, NoSQL) for a specific analytical task.
• Design a relational schema for domain data using ER modelling and normalization (reduction to 3NF), document the schema in a standardized graphical notation.
• Formulate complex SQL queries using joins (INNER, LEFT, FULL OUTER, SELF), aggregate functions, window functions (ROW_NUMBER, RANK, LAG, LEAD, aggregates with OVER) and common table expressions (CTE); correctly interpret query results.
• Create and modify data structures in PostgreSQL using DDL (CREATE/ALTER/DROP, PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE constraints); implement business logic in the form of stored functions in PL/pgSQL.
• Read and analyze the execution plan of a SQL query (EXPLAIN, EXPLAIN ANALYZE), select and create indexes (B-tree, GIN, GiST) to optimize the performance of typical workloads.
• Work with column-oriented DBMS ClickHouse: connect, upload data, understand the features of columnar storage, design schemes taking into account sorting keys (ORDER BY) and partitioning (PARTITION BY) for analytical workloads.
• Describe the principles of operation of NoSQL models (document, key-value, graph) and justify the choice between relational and non-relational DBMS for a specific task.

Course Contents

• Introduction: Databases and Database Management Systems (DBMS)
• Relational data model and normalization
• SQL: Data Definition Language (DDL) and Basic Manipulation (DML)
• Advanced SQL: Joins, Window Functions, CTE
• ER modelling and database design
• PostgreSQL: indexes, EXPLAIN, stored functions, transactions
• OLTP and OLAP. Columnar DBMS using the example of ClickHouse
• NoSQL models overview. DBMS integration with BI tools

Assessment Elements

• Activity at seminars
• Basic SQL Test
  Use of AI: PROHIBITED completely (in-class work without access to the Internet and external tools).
• Advanced SQL Test
  Use of AI: PROHIBITED completely (in-class work without access to the Internet and external tools).
• ER Modelling and Normalization Test
  Use of AI: PROHIBITED completely (in-class work without access to the Internet and external tools).
• Interim project: ER model and DDL
  Use of AI: The use of AI tools (ChatGPT, Claude, YandexGPT, GigaChat and analogues) is ALLOWED to clarify DDL syntax, check normalization and edit explanatory notes. Automatic generation of an ER model and a complete diagram without significant independent modification is PROHIBITED. Declaration of the use of AI is required in an explanatory note: indicate the tool, purposes of use and which parts of the work were generated.
• Final project: database implementation and analytical queries
  The use of AI tools is ALLOWED as an auxiliary tool: clarifying PostgreSQL and ClickHouse syntax, explaining the EXPLAIN plan, checking grammar in texts. It is PROHIBITED to delegate key intellectual steps to AI: choosing indexes, designing sorting keys/partitions in ClickHouse, formulating analytical queries for a meaningful task. Mandatory declaration in the form of the “Use of AI” section in the repository. Concealing the use of AI or submitting work whose key components are generated by AI will result in a “0” grade for the final project.

Interim Assessment

• 2026/2027 4th module
  0.1 * Basic SQL Test + 0.2 * Interim project: ER model and DDL + 0.4 * Final project: database implementation and analytical queries + 0.1 * Advanced SQL Test + 0.1 * Activity at seminars + 0.1 * ER Modelling and Normalization Test

Bibliography

Recommended Core Bibliography

• Churcher, C. (2012). Beginning Database Design : From Novice to Professional (Vol. 2nd ed). New York: Apress. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=1173722
• Foster, E. C., & Godbole, S. (2014). Database Systems : A Pragmatic Approach. [Berkeley, CA]: Apress. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=933677

Recommended Additional Bibliography

• Khan, A. (2016). Jumpstart Tableau : A Step-By-Step Guide to Better Data Visualization. [United States]: Apress. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=1250591
• Laursen, G. H. N., & Thorlund, J. (2010). Business Analytics for Managers : Taking Business Intelligence Beyond Reporting. Hoboken, N.J.: Wiley. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=330345
• Monsey, M., & Sochan, P. (2016). Tableau For Dummies. Hoboken, NJ: For Dummies. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=1082334
• Rockoff, L. (2017). The Language of SQL (Vol. Second edition). Hoboken, NJ: Addison-Wesley Professional. Retrieved from http://search.ebscohost.com/login.aspx?direct=true&site=eds-live&db=edsebk&AN=1601663

Authors

• LYAPIN ILYA VIKTOROVICH