Бакалавриат
2024/2025
Базы Данных
ID 855761
Лучший по критерию «Полезность курса для расширения кругозора и разностороннего развития»
Лучший по критерию «Новизна полученных знаний»
Статус:
Курс по выбору (Социология и социальная информатика)
Направление:
39.03.01. Социология
Кто читает:
Департамент социологии
Где читается:
Санкт-Петербургская школа социальных наук
Когда читается:
2-й курс, 3, 4 модуль
Формат изучения:
без онлайн-курса
Охват аудитории:
для всех кампусов НИУ ВШЭ
Язык:
английский
Кредиты:
5
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.
- 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.
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
Assessment Elements
- BI Project
- In-class test on SQL queriesIf the student was not able to attend the in-class test without a valid excuse, the in-class test can be retaken in the next two days with its score reduced by 1 point. If the student was not able to attend the in-class test with a valid excuse, the in-class test can be retaken without any penalties. Confirmation of the valid excuse is required. Retake time is negotiated individually.
- In-class test on ER diagrams and Relational SchemasIf the student were not able to attend the in-class test with a valid excuse, the in-class test can be retaken. Confirmation of the valid excuse is required. Retake time is negotiated individually. Retake without a valid excuse is not possible.
- DB ProjectStudents will work in groups to complete a series of tasks writing SQL queries and answering given questions.
- Seminar participationSeminar participation can be replaced with extra homework.
Interim Assessment
- 2024/2025 4th module0.1 * In-class test on SQL queries + 0.1 * Seminar participation + 0.35 * BI Project + 0.35 * DB Project + 0.1 * In-class test on ER diagrams and Relational Schemas
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