СУБД. Лекция 1

Знакомство и основы проектирования БД

• Обзор курса
• Что такое базы данных?
• Проектирование БД.
• Нормализация и нормальные формы.
• Реляционная алгебра.
• Типы данных.

Интересные факты

• База данных Yahoo > 2 петабайт
• Отсутствие готовых решений
• Архив с базой данных OpenStreetMap занимает порядка 33Гб
• Практически ни одно приложение не обходится без БД
• Установочные .msi файлы для Windows - это БД
• В большинстве вакансий разработчика упоминается SQL, по данным hh.ru

Стоимость типовых операций

Задача курса

• Проектирование реляционных баз данных.
• Язык запросов SQL.
• Оптимизация запросов и схем данных.
• High-Load и Big Data.
• Администрирование и настройка серверов БД.
• noSQL и его использование в WEB.

Проектирование и диаграммы

• Нормализация
• DDL

SQL

• CRUD
• Транзакции
• Хранимые процедуры
• Триггеры

Оптимизация

“Ну и запросы у вас”, - сказала база данных и повисла.

• EXPLAIN, slow_log
• Индексы
• Денормализация

Администрирование

• Конфигурирование
• Репликация
• Резервное копирование
• Пользователи и права
• SQL-injections

Реляционные БД

NoSQL

• Концепция Map-Reduce
• Краткий обзор существующих решений
• Tarantool

Контроль знаний

• Рубежные контроли
• Разработка проекта
• Нагрузочное тестирование
• Защита

Литература

• Дейт К. Дж. Введение в системы баз данных
• Бьюли А. Изучаем SQL
• Молинаро Э. SQL – сборник рецептов
• Заводны Дж, Шварц Б., Зайцев П.,
  Ткаченко В., Ленц А. MySQL.
  Оптимизация производительности
• Саймон А. Р. Стратегические технологии баз данных
• http://www.intuit.ru/catalog/database/
• http://citforum.ru/database/
• http://www.sql.ru/
• http://www.mysql.com/

Определение БД

База данных (БД)

Зто взаимосвязанная информация (данные) об объектах, которая организованна специальным образом и хранится на каком-либо носителе.

Реляционная модель данных

Терминология

Домен

Тип данных, то есть допустимое множество значений.

Кортеж

Множество пар {имя атрибута, значение}, которое содержит одно вхождение каждого имени атрибута, принадлежащего схеме отношения.

Отношение

Множество кортежей (не упорядоченное).

Целостность базы данных

Соответствие имеющейся в базе данных информации её внутренней логике, структуре и всем явно заданным правилам.

Реляционная модель данных

Реляционная алгебра

• Выборка
• Проекция
• Объединение
• Пересечение
• Разность
• Произведение
• Деление
• Соединение

Выборка (A WHERE c)

Персоны

`sigma_("Возраст" >= 34)("Персоны")`

Проекция (PROJECT A {x, y, …, z})

Персоны

`Pi_("Возраст", "Вес")("Персоны")`

Объединение (A UNION B)

`"Персоны" uu "Персонажи"`

Пересечение (A INTERSECT B)

`"Персоны" nn "Персонажи"`

Разность (A MINUS B)

`"Персоны" \\ "Персонажи"`

Произведение (A TIMES B)

`"Мультфильмы" xx "Каналы"`

Деление (A DIVIDEBY B)

`"Мультфильмы" -: "Каналы"`

Соединение ((A TIMES B) WHERE P)

`"Мультфильмы" ⋈ "Каналы"`

Деление (A DIVIDEBY B)

`"Мультфильмы" -: "Каналы"`

Проектирования БД

Проектирование баз данных

Процесс создания схемы базы данных и определения необходимых ограничений целостности.

Основные задачи

• Обеспечение хранения в БД всей необходимой информации.
• Обеспечение возможности получения данных по всем необходимым запросам.
• Сокращение избыточности и дублирования данных.
• Обеспечение целостности данных (правильности их содержания): исключение противоречий в содержании данных, исключение их потери и т.д.

Особенности концептуального проектирования

• Максимум командной работы;
• Привлечение наиболее опытных специалистов;
• Заглядываем в будущее.

Этапы проектирования

Внешнее представление (внешняя схема) данных

Совокупностью требований со стороны конкретной функции, выполняемой пользователем.

Концептуальная схема

Полная совокупностью всех требований к данным, полученной из пользовательских представлений о реальном мире.

Внутренняя схема

Сама база данных.

Инфологическое проектирование

Концептуальное (инфологическое) проектирование

Построение семантической модели предметной области, то есть информационной модели наиболее высокого уровня абстракции.

• сущности
• атрибуты
• связи

Логическое проектирование

Логическое (даталогическое) проектирование

Создание схемы базы данных на основе конкретной модели данных, например, реляционной модели данных.

• записи
• элементы данных
• связи между записями

Физическое проектирование

Физическое проектирование

Создание схемы базы данных для конкретной СУБД.

• группирование данных
• индексы
• методы доступа

Почему проект БД может быть плохим?

1. Избыточность
2. Потенциальная противоречивость
   (аномалии обновления)
3. Аномалии включения
4. Аномалии удаления

Концептуальное проектирование

Выявление сущностей и связей

Первичный ключ

Потенциальный ключ

В реляционной модели данных — подмножество атрибутов отношения, удовлетворяющее требованиям уникальности и минимальности (несократимости).

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

Минимальность (несократимость) означает, что в составе потенциального ключа отсутствует меньшее подмножество атрибутов, удовлетворяющее условию уникальности. Иными словами, если из потенциального ключа убрать любой атрибут, он утратит свойство уникальности.

Первичный ключ (англ. primary key)

В реляционной модели данных один из потенциальных ключей отношения, выбранный в качестве основного ключа (или ключа по умолчанию).

Если в отношении имеется единственный потенциальный ключ, он является и первичным ключом. Если потенциальных ключей несколько, один из них выбирается в качестве первичного, а другие называют «альтернативными».

Структура таблицы тем

Первичный ключ

Первичный ключ выбирается исходя из соображений удобства и сохранения уникальности. Если такого ключа нет, то имеет смысл добавить специальное поле.

Справочник пользователей

Нормальная форма

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

1-ая нормальная форма

Переменная отношения находится в первой нормальной форме (1НФ) тогда и только тогда, когда в любом допустимом значении отношения каждый его кортеж содержит только одно значение для каждого из атрибутов.

1-ая нормальная форма

Переменная отношения находится в первой нормальной форме (1НФ) тогда и только тогда, когда в любом допустимом значении отношения каждый его кортеж содержит только одно значение для каждого из атрибутов.

2-ая нормальная форма

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

2-ая нормальная форма

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

3-я нормальная форма

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

3-я нормальная форма

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

4-ая нормальная форма

Переменная отношения находится в четвёртой нормальной форме, если она находится в третьей нормальной форме и не содержит нетривиальных многозначных зависимостей.

4-ая нормальная форма

Переменная отношения находится в четвёртой нормальной форме, если она находится в третьей нормальной форме и не содержит нетривиальных многозначных зависимостей.

Суррогатный ключ

Даже при наличии естественного ключа добавление суррогатного в большинстве случаев оправдано.

Суррогатный ключ

Даже при наличии естественного ключа добавление суррогатного в большинстве случаев оправдано.

Реляционная часть

• один-к-одному (1:1)
• один-ко-многим (1:M)
• многие-к-одному (M:1)
• многие-ко-многим (M:N)

Типы данных MySQL

• Числовые
• Строковые
• Календарные
• NULL

NULL

SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;

SELECT 1 IS NULL, 1 IS NOT NULL, NULL IS NULL, NULL IS NOT NULL;

Целые числа

Числа с плавающей точкой

Бинарные

Строки

Дата и время

TIMESTAMP и DATETIME по разному работают с временными зонами.

TIMESTAMP по-умолчанию автоматически обновляется при изменении кортежа.

Версионирование схемы БД

Хотелки:

• Любую версию базы данных можно обновить до любой (обычно, самой последней) версии.
• Набор SQL-запросов, реализующих миграцию между любыми двумя версиями, можно было получить как можно быстрее и проще.
• Всегда можно создать с нуля базу данных со структурой самой последней версии.
• В случае работы над разными ветками, при последующем их слиянии ручное редактирование файлов БД было сведено к минимуму.
• Откатить БД на более раннюю версию так же просто, как и обновить на более новую.

Метод инкрементных изменений

Database
|- Baseline.sql
|- 0001.03.01.sql
|- 0002.03.01.sql
|- 0003.03.01.sql
|- 0004.03.02.sql
|- 0005.03.02.sql
|- 0006.03.02.sql
|- 0007.03.02.sql

CREATE TABLE MigrationHistory (
    Id INT AUTO_INCREMENT PRIMARY KEY,
    MajorVersion VARCHAR(2),
    MinorVersion VARCHAR(2),
    FileNumber   VARCHAR(4),
    Description  VARCHAR(255),
    DateApplied  TIMESTAMP
);

INSERT INTO MigrationHistory (MajorVersion, MinorVersion, FileNumber, Description, DateApplied)
VALUES ('03', '01', '0000', 'Baseline', NOW());

INSERT INTO MigrationHistory (MajorVersion, MinorVersion, FileNumber, Description, DateApplied)
VALUES ('03', '01', '0001', 'Update users table', NOW());

Метод инкрементных изменений

• Быстрое и удобное выполнение миграции до последней версии;
• Механизм нумерации версий. Номер текущей версии хранится прямо в БД;
• Для максимального удобства нужны средства автоматизации выполнения миграций;
• Неудобно добавлять комментарии к структуре БД;
• Если хранить только "версию базы", возникают проблемы в процессе параллельной разработки в нескольких ветках репозитория.

Метод идемпотентных изменений

Database
 |- 3.01
 |   |- Baseline.sql
 |   | - Changes.sql
 |
 | - 3.02
     |- Baseline.sql
     |- Changes.sql

IF NOT EXISTS
(
    SELECT *
    FROM information_schema.tables
    WHERE table_name = 'myTable'
        AND table_schema = 'myDb'
)
THEN
    CREATE TABLE myTable
    (
        id INT(10) NOT NULL,
        myField VARCHAR(255) NULL,
        PRIMARY KEY(id)
    );
END IF;

Метод идемпотентных изменений

• Очень удобное выполнение миграций с любой промежуточной версии до последней — нужно всего лишь выполнить на базе данных один файл (Changes.sql)
• Потенциально возможны ситуации, в которых будут теряться данные, за этим придется следить.
• Для того, чтобы изменения были идемпотентными, нужно потратить больше времени (и кода) на их написание.

Генерация БД на базе исходного кода

• Удобно наблюдать изменения в структуре между версиями при помощи средств системы контроля версий;
• Как и любой исходный код, структуру БД удобно комментировать;
• Для того, чтобы с нуля создать чистую базу данных последней версии, нужно выполнить всего лишь один файл;
• Скрипты-миграции более надежны, чем в других методах, так как генерируются автоматически;
• Мигрировать с новых версий на старые почти так же просто, как со старых на новые (проблемы могут возникнуть только с пресловутыми изменениями данных);
• В случае слияния двух веток репозитория, merge структуры БД осуществляется проще, чем при использовании других подходов;
• Изменения данных придется хранить отдельно, и затем вручную вставлять в сгенерированные скрипты-миграции;
• Вручную выполнять миграции очень неудобно, необходимы автоматизированные средства.
`