Проходил 22 мая в 20:00

День открытых дверей
Всё о курсе «Реляционные СУБД»

День открытых дверей онлайн

День Открытых Дверей — отличная возможность узнать подробнее о программе курса, особенностях онлайн-формата, навыках, компетенциях и перспективах, которые ждут вас после обучения. Также преподаватель расскажет о своём профессиональном опыте и ответит на вопросы участников. Запишитесь на онлайн-трансляцию и задайте свой вопрос преподавателю лично!
Преподаватель
Алексей Цыкунов

Запись

О курсе

Реляционные СУБД
1 ₽
Начало в октябре
Курс обеспечивает глубокое погружение в реляционные СУБД, чтобы проектировать базы данных так, чтобы впоследствии не приходилось тушить пожары в результате не оптимально заложенных основ. Цель курса — научить слушателя эффективно работать с любой реляционной базой данных с помощью языка структурированных запросов SQL. В результате обучения выпускники смогут уверенно работать со структурой СУБД, создавать оптимальные SQL-запросы и разбираться с некорректными, работать …
Программа курса
Модуль 1
Сущности и связи
Модуль 2
SQL
Модуль 3
Оптимизация БД
Модуль 4
Практика: MySQL и PostgreSQL
Сущности и связи
Реляционная модель данных. Реляционная алгебра
познакомиться с реляционной теорией;
понять принципы проектирования реляционных баз данных;
освоить приемы нормализации данных;
построить свою модель
Домашние задания: 1
ДЗ
Строим модель данных. Задача: реализовать модель данных БД, определить сущности, построить связи, выполнить декомпозицию и нормализацию

За основу берем практическую структуру данных

Варианты:
1) интернет магазин (витрина) - иерархическая структура категорий товаров, товары, производители, поставщики, цены, характеристики товаров (у разных категорий товаров - разные характеристики).
1*) расширяем магазин - клиенты, покупки (транзакции)
2*) интеграция магазина с платежными системами - платежные системы, транзакции с платежной системой, аккоунты, счета, транзакции магазина
3) блог: пост. датавремя, заголовок, картинки, автор, лайки, комменты, репосты, просмотревшие. время последнего просмотра/редактирования
или любой свой вариант
4) Персональные данные: сами персональные данные, пользователи и роли имеющие права для выборки и правки, журнал изменений
5 ) любой ваш проект

Описанные сущности не являются полным и конечным ТЗ (как это и бывает в жизни). Вы как архитектор должны предусмотреть необходимые атрибуты и дополнительные сущности по необходимости. И четко представлять бизнес-задачу которую будет решать эта структура данных.

делаем декомпозицию и нормализацию
в качестве сделанной ДЗ принимается pdf с начальной и конечной моделью

Решая данное ДЗ вы тренируете навык проектирования БД, выделения сущностей и их атрибутов, построения связей, нормализации данных
Типы данных
правильно выбирать типы данных для проектирования таблиц;
осознать преимущества и недостатки типов данных
Домашние задания: 1
ДЗ
Корректируем типы данных в модели при необходимости. Взять свою структуру данных из ДЗ-1
и прописать комментарии на каждое поле с пояснением почему выбран данный тип поля и на сколько он удовлетворяет поставленной задаче


При решении данной ДЗ формируем навык документирования, и ревью собственной структуры, перспективной оценки на возможные проблемы из-за некорректного выбора типов данных

варианты как сделать
для Mysql Workbench есть плагины для формирования документации
https://github.com/d1soft/mysql-workbench-html-doc-generator
https://github.com/letrunghieu/mysql-workbench-plugin-doc-generating

то есть вы создаете таблицы
прописывается комментарии на поля
и затем этим плагином формируете документацию

для других инструментов тоже могут быть подобные инструменты
http://dbdoc.sourceforge.net
https://github.com/extronics/dbdoc
https://www.liquibase.org/documentation/dbdoc.html
Транзакции. ACID
изучить особенности работы MVCC, уровни блокировок и изоляций транзакций,
проектировать оптимальные транзакционные модели без взаимных блокировок,
проектировать сложные транзакционные модели на примерах.
Домашние задания: 1
ДЗ
Описываем логику транзакций для своей модели данных. В вашем проекте базы данных опишите
транзакцию, которая будет включать в себя несколько действий
и исключать возможность взаимных блокировок и неоднозначных ситуаций

Описание должно включать в себя
- бизнес задачу
- вероятные проблемы
- SQL транзакции или возможно нескольких транзакций

пример описания бизнес задачи:
Представим систему где оператор назначает специалистов ЖКХ на решение определенных проблем

задача:
назначение задачи специалисту
фазы
- выбор задачи
- выбор свободного специалиста
- назначение заданного специалиста на заданную задачу на заданное время

особенности
- назначения ведут несколько операторов
- исключить возможность того что несколько операторов назначают специалиста на одну и ту же задачу
- исключить возможность того что несколько операторов на разные задачи назначают одного и того же специалиста на одно и то же время

SQL
start transaction;
select task_id into @TASK from tasks where status='new' limit 1 for update;
select spec_id into @SPEC from specialists where status='free' limit 1 for update;
UPDATE specialists set status='assigned' where spec_id = @SPEC;
UPDATE tasks set status='assigned' where task_id = @TASK;
INSERT into spec_tasks (spec_id, task_id, created_at, status) VALUES(@SPEC, @TASK, NOW(), 'started')
commit;
В случае проблем с пониманием какую транзакцию можно предоставить решение для вышеописанной задачи
Компоненты современной СУБД
понять и использовать в работе многообразие компонентов и возможностей СУБД: как управлять пользователями, какие типы коннектов бывают, как создавать индексы, как создавать ограничения (constrains), что такое триггеры, зачем нужны последовательности и очереди, для чего использовать хранимые процедуры и функции, что такое DB links, представления (view) и синонимы, Jobы
Домашние задания: 1
ДЗ
Добавляем в модель данных дополнительные индексы и ограничения. 1. Проводим анализ возможных запросов\отчетов\поиска данных.
2. Предполагаем возможную кардинальность поля.
3. Создаем дополнительные индексы - простые или композитные.
4. На каждый индекс пишем краткое описание зачем он нужен (почему по этому полю\полям).

5. Думаем какие логические ограничения в БД нужно добавить - например какие поля должны быть уникальны, в какие нужно добавить условия, чтобы не нарушить бизнес логику. Пример - нельзя провести операцию по переводу средств на отрицательную сумму.
6. Создаем ограничения по выбранным полям.
Проектирование БД
разобрать вопросы и проблемы обслуживания и сопровождения, которые важно учесть на этапе проектирования
Домашние задания: 1
ДЗ
Доработка проекта. С учетом всех пройденных занятий доработать свой проект до финального представления
В проекте должны быть
- схема
- документация
- примеры бизнес-задач которые решает база
- рекомендации к использованию репликации
- рекомендации к резервному копированию

ДЗ автоматически защитывается при защите проекта на последнем занятии модуля
Проектирование БД: OLAP
понять, что такое OLAP;
зачем нужен Cube и его состав;
строить сводную таблицу по данных из куба
Проектирование БД - семинар
презентовать проект;
проанализировать созданные проекты;
обсудить и внести предложения для корректировки
SQL
DML: изменение данных
писать SQL для модификации данных;
изменять данные и сохранять изменения
Домашние задания: 1
ДЗ
Создаем скрипты очистки и первичного заполнения базы. Наполняем свою базу данными!!

1. Запросы на встравку данных INSERT VALUES
2. Запросы на insert с использованием Select
3. Изменение данных UPDATE, UPDATE с использованием JOIN
4. Delete
5. Процедура со вставкой и обновлением блока
6. Merge – потренироваться и прочувствовать

формат сдачи - расширяем докерфайл - который при старте поднимает базу с данными

DDL: создание и редактирование объектов БД
использовать синтаксис SQL для описания структуры БД
Домашние задания: 1
ДЗ
Создаем базу из проектной модели. В рамках этого домашнего задания вы сможете писать скрипт создания БД и разворачивать свой дев проект в докере


Создаем Dockerfile
в котором разворачивается image нужной СУБД
и скриптом разворачивается структура вашего проекта
наружу должен быть прокинут порт для подключения
формат сдачи - github репозиторий
в README должно быть описано как подключится к вашей базе (субд, название базы, логин, пароль, порт)

* для учебной базы сделать скрипт для партиционирования таблицы CDR по месяцам (на выходе SQL  скрипт на модерацию + дамп измененной структуры)
** проанализировать базу voip и создать внешние ключи (скрипт для создания ключей + схема данных)
DML: выборка данных
использовать различные способы выборки данных из одной и нескольких таблиц;
использовать нужный тип JOIN и различные условия в WHERE
Домашние задания: 1
ДЗ
SQL выборка. Задачи на выбор

1) по базе voip
Рассчитываем стоимость звонка в БД voip
Строим Select для выборки всех звонков

поля для связи
CDR.src_ip -> oper_ip.IP_OP
oper_ip.OP_ID -> SITE.ID
SITE.rate_o -> RATES.RATE_ID
RATES.CODE_ID -> DEST_CODE.DEST_ID

RATES.price - цена
DEST_CODE.CODE. - код направления

CDR - запись о звонке
SRC_IP - ип адрес оператора от которого пришел звонок
oper_ip - в этой таблице находим этот ип адрес который действовал в это время
SITE - здесь находим подразделение оператора к которому привязан данный ип адрес
RATES здесь по (SITE.rate_o -> RATES.RATE_ID) находим тариф который действовал в заданный период у данного подразделения оператора
который относится к НАИБОЛЬШЕМУ коду соответвующему номер из CDR.BILL_NUMBER
коды описаны в DEST_CODE (RATES.CODE_ID -> DEST_CODE.DEST_ID)


необходимо учитывать дату звонка, дату действия тарифа и дату действия IP оператора

В oper_ip также могут быть дублирующие IP. в том числе и по дате (есть условия по префиксу номера и ip ceрвера) -
для облегчения задачи можно вытащить уникальные записи в отдельную таблицу oper_ip_tm


* сделать выборку с учетом префикса номера в oper_ip

2) по своей базе (пример для базы продуктов)
Отрисовка списка продуктов с указанием всех уровней категории (например Бытовая техника/холодильники)
и списком параметров в JSON или XML по определенным условием ( например все двухкамерные холодильники)

запрос должен. включать в себя 1 или несколько JOIN и условия WHERE
DML: агрегация и сортировка
группировать и сортировать данные и использовать групповые функции
Домашние задания: 1
ДЗ
Создаем отчетную выборку. Построить статистический запрос и показать группировку по странам (по каждому коду необходимо вычислить страну и сгруппировать по стране)
- по суммарному времени
- по кол-ву звонков
- по кол-ву нулевых звонков

должны быть представлены 3 SQL
* Использовать RollUP для развернутого отчета по странам и направлений внутри стран


- можно использовать свою БД и предоставить следующий результат
- база с данными в докере
- группировки с ипользованием CASE, HAVING, ROLLUP, GROUPING SET
например для магазина к предыдущему списку продуктов добавить максимальную и минимальную цену и кол-во предложений
также сделать выборку показывающую самый дорогой и самый дешевый товар в каждой категории
сделать rollup для оценки продаж по категориям товаров
DML: вложенные запросы и представления
использовать вложенные запросы и сохранять их во view;
использовать конструкцию WITH, операции множеств UNION, EXCEPT, INTERSECT, дополнительные возможности выборки
Домашние задания: 1
ДЗ
Создаем дополнительные представления в БД. Варианты на выбор
1) voip
сохранить запрос из предыдущего ДЗ как представления

решить бизнес-задачу
мы хотим дать оператору Х возможность просматривать свои звонки
он должен видеть данные из таблицы CDR но не должен видеть от кого или куда звонки ушли
за исключением того что он видит что звонки пришли либо от него либо не него
те - он должен видеть свои IP в качестве SRC_IP или DST_IP, а чужие IP он в этих полях видеть не должен
финансовых полей другой стороны оператор также видеть не должен

в качестве решения принимаются одно или несколько представлений

2) своя база
реализовать по страничную выдачу каталога товаров
перестроить демонстрацию иерархии категорий с помощью рекурсивного CTE
DML: аналитические функции
понять, что такое «Окно» в аналитических функциях;
писать запросы с аналитическими функциями;
оптимизировать существующие решения через аналитические функции
Домашние задания: 1
ДЗ
Отчетная PIVOT-выборка + Аналитические функции. Домашнее задание можно опционально выполнить либо по voip либо по Sakilla. Можно при желании сделать и то и другое.

Домашнее задание по voip:
1. Выберите id и имя оператора из таблицы операторов; пронумеруйте записи по названию оператора, так чтобы при изменении буквы алфавита нумерация начиналась заново
Также добавьте в выборку:
- общее количество операторов
- общее количество операторов в зависимости от первой буквы названия фильма
- следующий ид оператора
- предыдущий ид оператора
- название оператора 2 строки назад

2. Гос дума выпустила новый закон, по которому все компании занимающиеся телефонией должны подписать с операторами, с которыми есть взаимодействие, дополнительное соглашение о конфиденциальности.
У нас всего 3 менеджера, помогите сформировать 3 группы операторов, чтобы каждый мог связаться с оператором оперативно.
В результатах должен быть номер группы, ид оператора, его название, название организации и телефон

3. По каждому оператору выберите 2 самых больших платежа
в запросе должно быть ид оператора, название, сумма платежа, дата платежа, является ли он самым большим для этого оператора.
Этот запрос нужно сделать в 2х вариантах - без аналитических функций и с ними.

4. Посчитайте платежи в системе в разрезе год (строка) - месяц (колонка), на пересечении должна быть сумма платежей за этот год и месяц.

Если у вас есть проблемы с БД voip и оконными функциями, которые есть только с MySQL 8.0, ниже альтернативное ДЗ по БД Sakila - dvdrental
https://github.com/ivanceras/sakila

1. Введение в функции
Посчитайте по таблице фильмов, в вывод также должен попасть ид фильма, название, описание и год выпуска
пронумеруйте записи по названию фильма, так чтобы при изменении буквы алфавита нумерация начиналась заново
посчитайте общее количество фильмов и выведете полем в этом же запросе
посчитайте общее количество фильмов в зависимости от буквы начала называния фильма
следующий ид фильма на следующей строки и включите в выборку
предыдущий ид фильма
названия фильма 2 строки назад
Для этой задачи не обязательно писать аналог без функций

2. Вахтер Василий очень любит кино и свою работу, а тут у него оказался под рукой ваш прокат (ну представим что действие разворачивается лет 15-20 назад)
Василий хочет посмотреть у вас все все фильмы при этом он хочет начать с самых коротких и потом уже смотреть более длинные
сделайте группы фильмов для Василия чтобы в каждой группе были разные жанры и фильмы сначала короткие, а потом более длинные
В результатах должен быть номер группы, ид фильма, название, и ид категории (жанра), продолжительность фильма.

3.По каждому работнику проката выведете последнего клиента, которому сотрудник сдал что-то в прокат
В результатах должны быть ид и фамилия сотрудника, ид и фамилия клиента, дата аренды
Для этого задания нужно написать 2 варианта получения таких данных - с аналитической функцией и без нее.

4.Нужно выбрать последний просмотренный фильм по каждому актеру
В результатах должно быть ид актера, его имя и фамилия, ид фильма, название и дата последней аренды.
Для этого задания нужно написать 2 варианта получения таких данных - с аналитической функцией и без нее.
Данные в обоих запросах (с оконными функциями и без) должны совпадать.
Семинар: практический SQL
читать план запроса,
определять, что нужно оптимизировать
Оптимизация БД
Внутренняя архитектура СУБД
настроить СУБД, влияющие на ее производительность
26 июля, 20:00 — 21:30
Лектор: Алексей Цыкунов
Индексы
разобраться в типах индексов, плюсах и минусах,
создавать, удалять и мониторить индексы для оптимизации БД
30 июля, 20:00 — 21:30
Лектор: Алексей Цыкунов
Домашние задания: 1
ДЗ
Индексы. В материалах приложен репозиторий на учебную базу с продуктами и запрос

задача - оптимизировать запрос с помощью создания нужных индексов
результат - запрос с индексами выполняется быстрее

формат сдачи -
сделать форк репозитория к себе
добавить в докерфайл создание индексов
Хранимые процедуры и триггеры
использовать хранимые процедуры и функций для оптимизации работы с БД
2 августа, 20:00 — 21:30
Домашние задания: 1
ДЗ
Добавляем в базу хранимые процедуры и триггеры. создаем в базе voip

хранимые функции для определения стоимости звонка

хранимую процедуру для рассчета данных за один день
- берется последний час которые есть в DAYSTAT
- рассчитываются данные за следующий день
- заносятся в DAYSTAT
- при следующем запуске считается следующий день
- если при первом запуске в DAYSTAT нет данных - считаются данные с того дня который первый присутствует в CDR

Оптимизация на практике
определять узкие места базы, оптимизировать и профилировать запросы
9 августа, 20:00 — 21:30
Лектор: Алексей Цыкунов
Домашние задания: 1
ДЗ
Оптимизация сложного запроса. В материалах предоставлена ссылка на git репозиторий с базой с продуктами и учебным запросом

Задача - ускорить запрос
Опции хранения
спрогнозировать рост данных и количества запросов
и предложить варианты решения
6 августа, 20:00 — 21:30
Лектор: Алексей Цыкунов
Домашние задания: 1
ДЗ
Спрогнозировать рост данных и спроектировать модель хранения и архивации. Формируем документ в котором описываем
- прогноз по возможному росту базы
* рост данных
* рост количества пользователей
* всплески одновременных соединений

- описываем возможные угрозы и методы защиты от них
* предлагаем стратегии бэкапа
- репликации
- кластеризации
Проблемы миграции
разобраться в видах миграции;
узнать основные проблемы миграции;
решить проблемы, возникшие при миграции
13 августа, 20:00 — 21:30
Лектор: Кристина Кучерова
Управление СУБД
делать бэкап и затем восстанавливаться с него
16 августа, 20:00 — 21:30
Лектор: Алексей Цыкунов
NoSQL: Redis и MongoDB
понимать, когда и для каких целей имеет смысл использовать NoSQL;
применять Redis и Mysql в качестве кешей
20 августа, 20:00 — 21:30
Лектор: Алексей Цыкунов
Обсуждение проектов
защитить проект;
ознакомиться с результатами проектов одногруппников
23 августа, 20:00 — 21:30
Лектор: Алексей Цыкунов
Домашние задания: 1
ДЗ
Проектная работа.
Практика: MySQL и PostgreSQL

Преподаватель

Алексей Цыкунов
Системный архитектор, Oracle DBA, разработчик perl/python, опыт в отрасли - 20 лет
В настоящее время работает на аутсорсе. Работал в интернет-провайдерах и телекоме. Имел опыт внедрения и разработки продуктов в госструктурах, таких как минздрав и соцфонд. Был главным разработчиком стартап проекта Ipstudio AMBS (биллинг для VoIP). Участвовал в проектировании и разработке OLTP систем. Проектировал и развертывал серверные системы в датацентрах.

Закончил Кыргызско-Российский Государственный Университет, 2001, Инженер автоматизированных систем.
Собственный технический блог на - dbadmins.ru
Преподаватель курсов:
Реляционные СУБД

F.A.Q.: Наиболее часто задаваемые вопросы:

Нужно ли оплачивать обучение до Дня открытых дверей, если я хочу гарантированно попасть в группу?
Да, мы рекомендуем заранее оплатить обучение, чтобы гарантированно попасть в группу. В период проведения Дня открытых дверей резко возрастает количество желающих обучаться, поэтому может случиться так, что к окончанию Дня открытых дверей мест в группе не останется
Кто будет проводить День открытых дверей?
Проводить День открытых дверей будет преподаватель курса.
Как принять участие в Дне открытых дверей?
Для того, чтобы принять участие в Дне открытых дверей, оставьте свой e-mail в поле регистрации на мероприятие. Перед началом Дня открытых дверей мы пришлем вам ссылку, пройдя по которой, вы сможете присоединиться к вебинару.
Какие вопросы будем обсуждать на Дне открытых дверей?
На дне открытых дверей мы поговорим о проекте OTUS (о программе курса, почему мы не принимаем в группы новичков, почему учиться у нас интересно, но сложно), карьерных перспективах выпускников (почему вероятность карьерного роста у лучших студентов стремится к 100%), учебном процессе (оплатах, оценке знаний, сертификатах и прочих аспектах). Также ведущий мероприятия с радостью ответит на все ваши вопросы.