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

1. ЦЕЛЬ РАБОТЫ

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

1. ЗАДАНИЕ НА РАБОТУ

1. Опишите, для чего в вашей предметной области могла бы понадобиться древовидная структура.
2. Создайте древовидную структуру (таблицу, содержащую внешний ключ, ссылающийся на эту же таблицу). Наполните ее данными. Создайте рекурсивный вопрос, выводящий данные в соответствии с их иерархией. Проанализируйте зависимость времени выполнения запроса от количества записей в таблице (определите для вашего компьютера, при каком количестве записей запрос будет выполняться 0,1 секунду, 1 секунду и 10 секунд).
3. Создайте структуру для хранения древовидных данных типа Nested Sets и все необходимые триггеры. Покажите на примерах, как работает эта структура.
4. Проанализируйте зависимость времени добавления новых данных от числа строк в таблице (определите для вашего компьютера, при каком количестве записей добавление новых данных будет выполняться 0.1 секунду, 1 секунду и 10 секунд).

1. ХОД РАБОТЫ

1. Создал таблицу сотрудников отдела. В созданной таблицы хранится информация о сотруднике отдела и его руководителе (рисунок 1). Заполнил созданную таблицу данными (рисунок 2).

[pic 1]

Рисунок 1 – Создание таблицы Сотрудники_отдела

[pic 2]

Рисунок 2 – Заполнение таблицы Сотрудники_отдела

На рисунке 3 представлен рекурсивный запрос, обходящий таблицу «Сотрудники_отдела». Запрос начинается с ключевых слов WITH RECRUSIVE, нужно задать начальные значения рекурсии в виде tree, после объединить с дальнейшими результатами обхода командой UNION. Далее выбрать из таблицы те же значения и соединить их с tree командой JOIN. После все результаты заносятся в t и для их получения нужно использовать команду SELECT.

[pic 3]

Рисунок 3 – Рекурсивный вопрос для получения дерева

После создал таблицу для хранения дерева типа Nested sets, которая содержит номер, левую и правую границу диапазона, уровень, номер родителя, два поля для избежания зацикливания (рисунок 4).

[pic 4]

Рисунок 4 – Создание таблицы ns_tree

Далее создал функцию, блокирующую дерево на изменение данных, пока не закончена транзакция (рисунок 5).

[pic 5]

Рисунок 5 – Создание функции

После создания функции для блокировки создал триггер, который активируется перед командой INSERT (рисунок 6). Созданный триггер реализует три варианта вставки узла: добавление в подчинение определенному узлу, передаёт parent_id; добавляет в определенную точку дерева, передает leftk; добавление в конец дерева (ничего дополнительно передавать не нужно).