Data Bases

2026/2027

Учебный год

ENG

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

6

Кредиты

Кто читает:

Базовая кафедра группы компаний MERA (Нижний Новгород) (Факультет информатики, математики и компьютерных наук (Нижний Новгород))

Статус:

Курс обязательный

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

2-й курс, 1-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

• экзамен
  Экзамен проводится с использованием асинхронного прокторинга. Экзамен проводится на платформе MS Teams (https://teams.microsoft.com), прокторинг на платформе Экзамус (https://hse.student.examus.net). К экзамену необходимо подключиться за 15 минут. На платформе Экзамус доступно тестирование системы. Компьютер студента должен удовлетворять следующим требованиям: https://elearning.hse.ru/data/2020/05/07/1544135594/Технические%20требования%20к%20ПК%20студента.pdf) Для участия в экзамене студент обязан: заранее зайти на платформу прокторинга, провести тест системы, включить камеру и микрофон, подтвердить личность. Во время экзамена студентам запрещено: общаться (в социальных сетях, с людьми в комнате), списывать. Кратковременным нарушением связи во время экзамена считается прерывание связи до 10 минут. Долговременным нарушением связи во время экзамена считается прерывание связи 10 минут и более. При долговременном нарушении связи студент не может продолжить участие в экзамене. Процедура пересдачи аналогична процедуре сдачи.
• аудиторная работа
• лабораторная работа
• In-class assignment
  ...
• Test
• Экзамен
  ..
• Test

Interim Assessment

• 2026/2027 2nd module
  0.1 * In-class assignment + 0.7 * Экзамен + 0.2 * Test
• 2026/2027 4th module
  0.1 * Test + 0.1 * аудиторная работа + 0.1 * лабораторная работа + 0.7 * экзамен

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