Lectures Tehnotreka. Designing a DBMS (Fall 2016)

We continue the publication of our educational materials. This course is dedicated to learning the basics of the SQL language, taking into account the features of the PostgreSQL object-relational database. The program provides an integrated approach to the study of the standardized SQL language on the PostgreSQL platform, including some minimal administration capabilities for users, roles, schemas, base tables, and other database objects. We will cover the basics of working with the PostgreSQL database and some SQL features applied to it. In more detail - under the cut.

The course is taught by Elena Shishkina (head of the platform development group, Mail.Ru Group) and Danil Vagapov (programmer of the internal information development group, Mail.Ru Group).

List of lectures:

• Lecture 1. Introduction
• Lecture 2. SQL: the beginning
• Lecture 3. SQL: data sampling
• Lecture 4. Query Optimization and Indexing
• Lecture 5. Functions and Triggers
• Lecture 6. Configuration and Security
• Lecture 7. Scaling DB Data
• Lecture 8. Overview of NoSQL solutions
• Lecture 9. Advanced SQL

Lecture 1. Designing a DBMS. Introduction

This is an introductory lecture that explains the basic concepts in the field of database design. You will learn what a “relational data model” is, what data operations are performed. The main tasks of designing a DBMS and its key stages: conceptual, logical and physical design are considered.

Lecture 2. Designing a DBMS. SQL: start

The lecture discusses the key concepts when working with SQL: what data types are supported by this language and how they differ from each other, how to create a SQL table, how to modify it. It also describes the main operations with data in the table: insert, select, update, delete.

Lecture 3. Designing a DBMS. SQL: data retrieval

We consider the creation of subqueries and aggregate functions, in particular, UNION. Let's sort the table join operations, as well as the join with the subquery. We will learn the essence of the terms “type” and “materialized type”, and at the end of the lecture such thing as transactions will be considered.

Lecture 4. Designing a DBMS. Query Optimization and Indexing

At the core of this lesson are two topics. In the first part, we consider the basics of indexing, the choice of optimal data types, indexing strategies, normalization and denormalization. Let's get acquainted with the concepts of functional and partial indexes, as well as JSON indexing. At the end of the first part, we will touch on the recommended maintenance work and application and caching problems.

The second part is devoted to scheduling queries, collecting query statistics and their profiling. Consider hints to the scheduler and forcing the use of the index. Let's talk about the physical aspects of sample optimization, about monitoring slow queries and the process of testing a SQL application.

Lecture 5. Designing a DBMS. Functions and Triggers

This lecture focuses on stored procedures and functions, how and where they are applied. Consider simple functions in SQL, as well as immutable, stable, and volatile functions. Let's get acquainted with the pl / pgSQL language. Next, consider the arguments and return values, writing cycles and their conditions. Understand how to work with errors. We will get a general idea about triggers and their application, find out what types of triggers exist.

Lecture 6. Designing a DBMS. Configuration and Security

Let's get acquainted with important concepts - configuration and security. Consider the basics of configuring and setting up the workload. Let's get acquainted with replication and its configuration, find out how it works, get an idea of ​​its topology. Then we will talk about the administration and maintenance of replication, as well as possible problems with replication and their solution. Next, we turn to the issue of data security. Let's discuss the organization of backup and recovery, let's talk about privileges and roles, let's talk about security at the OS level, and finally we will learn what SQL injections are and how to deal with them.

Lecture 7. Designing a DBMS. Database Scaling

We begin the lecture with the study of optimizing data access. Then we will talk about materialized views, about database scaling. Let's get acquainted with the SAR theorem. We discuss table partitioning, distributed transactions, and two-phase commits, and at the end of the lecture we will consider replication and clustering.

Lecture 8. Designing a DBMS. Overview of NoSQL Solutions

In this lecture you will learn what NoSQL-databases are, what NoSQL-solutions exist for working with high-loaded systems. We will touch on the theoretical foundations of NoSQL, and then the advantages and disadvantages of such databases as Tarantool and MongoDB.

Lecture 9. Designing a DBMS. Advanced SQL

At the end of the course we will get acquainted with the storage of trees in the RDBMS. Consider an adjacency list, nested sets, a materialized path, a table of relationships, and mixed structures. Toward the close of a look at the extension PostgreSQL Itree. Next, we move on to optimizing queries using with clause. Let's look at the recursive queries and the dynamic assembly of the materialized path in the recursive query. Then the window functions, the aggregate functions in the windows, and at the end we turn on the analytics.

Playlist of all lectures is on the link . Recall that current lectures and master classes on programming from our IT specialists in Technopark, Technosphere and Tehnotrek projects are still published on Tekhnostrim channel.

Other courses Tehnotreka on Habré:

• Basics of web development (spring 2016)
• Java Development (Spring 2016)