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

1 1. Транзакции Базы данных: учебный курс Неделимой операцией в БД считается выполнение одного SQL-запроса (может быть, включающего выполнение хранимой процедуры, исполнение каскадных действий и триггеров). На самом деле можно рассматривать три режима исполнения предложений: 1.Автоподтверждение (транзакцией является каждое предложение, как описано выше). 2.Неявное начало транзакций, явное подтверждение. 3.Явное начало и подтверждение транзакций. MS SQL Server: 1.BEGIN TRANSACTION – явное начало транзакции 2.COMMIT TRANSACTION – завершение транзакции 3.ROLLBACK TRANSACTION – отмена транзакции 4.SET IMPLICIT_TRANSACTIONS ON | OFF – включение/выключение режима неявного начала транзакций

2 Примеры Базы данных: учебный курс 1.Нужно однократно исполнить несколько предложений в рамках одной транзакции. BEGIN TRANSACTION INSERT Tab1 (Field1, Field2) VALUES ('Val1', 'Val2') INSERT Tab1 (Field1, Field2) VALUES ('Val3', 'Val4') COMMIT TRANSACTION 2.Хочется явно отмечать конец каждой транзакции, не задавая явно ее начало каждый раз. SET IMPLICIT_TRANSACTIONS ON INSERT Tab1 (Field1, Field2) VALUES ('Val1', 'Val2') INSERT Tab1 (Field1, Field2) VALUES ('Val3', 'Val4') COMMIT TRANSACTION INSERT Tab1 (Field1, Field2) VALUES ('Val5', 'Val6') INSERT Tab1 (Field1, Field2) VALUES ('Val7', 'Val8') COMMIT TRANSACTION SET IMPLICIT_TRANSACTIONS OFF

3 Вложенные транзакции Базы данных: учебный курс BEGIN TRANSACTION {SQL-предложение 1} {SQL-предложение 2} BEGIN TRANSACTION {SQL-предложение 3} IF (условие) THEN {SQL-предложение 4} COMMIT TRANSACTION ELSE ROLLBACK TRANSACTION {SQL-предложение 5} COMMIT TRANSACTION

4 Транзакции в JDBC Базы данных: учебный курс Управление режимом транзакций – методы интерфейса Connection Connection conn = DriverManager.getConnection(...); conn.setAutoCommit(false); for(int i = 0; i < 10; ++i) { insertData(someTable, someData); updateData(someOtherTable, someOtherData); conn.commit(); } conn.setAutoCommit(true);

5 JDBC – отмена транзакции Базы данных: учебный курс Connection conn = DriverManager.getConnection(...); conn.setAutoCommit(false); try { for (int i = 0; i < 10; ++i) { insertData(someTable, someData); } } catch (Exception x) { conn.rollback(); } finally { conn.commit(); } conn.setAutoCommit(true);

6 Механизм «точек состояния» в JDBC Базы данных: учебный курс Savepoint svp1 = conn.setSavepoint("name1"); Вместо вложенных транзакций в JDBC можно использовать «точки состояния». String s = svp1.getSavepointName(); int id = svp1.getSavepointId(); Объекты типа Savepoint обладают числовым идентификатором и могут иметь имя. Savepoint svp1 = conn.setSavepoint(); Savepoint svp2 = conn.setSavepoint(); Создадим внутри выполняемой транзакции две точки сохранения. conn.rollback(svp1); // откат транзакции к svp1; // svp2 "освобождается" conn.commit(); // все точки сохранения освобождаются conn.releaseSavepoint(svp2); // "освобождение" точки svp2 conn.rollback(); // отмена транзакции и освобождение всех svp Тогда можно выполнить следующие действия:

7 Уровни поддержки изолированности транзакций Базы данных: учебный курс int level = conn.getTransactionIsolation(); Запросить уровень поддержки изолированности транзакции можно так: Connection.TRANSACTION_NONE – транзакции не поддерживаются; Connection.TRANSACTION_READ_UNCOMMITTED – транзакции поддерживаются, но при чтении данных одной транзакцией могут быть видны результаты работы незавершенной другой транзакции. Connection.TRANSACTION_READ_COMMITTED – транзакции поддерживаются и результаты работы транзакций не видны извне, пока транзакция не завершена. Connection.TRANSACTION_REPEATABLE_READ Connection.TRANSACTION_SERIALIZABLE conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); Можно попробовать установить желаемый уровень изолированности:

8 2. Права пользователей Базы данных: учебный курс Поддержка прав пользователей ведется только на уровне самой БД, В JDBC нет никаких средств для работы с правами пользователей. Основные понятия: 1.Право на определенное действие с определенным объектом; 2.Роль – совокупность прав; 3.Пользователь (user) – исполнитель некоторой совокупности ролей; 4.Креденциал (login + password) – право на вход в систему под именем того или иного пользователя. Примеры: 1.Право вставлять (INSERT) новые записи в определенную таблицу; право добавлять (CREATE) новые таблицы в базу данных (database). 2.Владелец БД (owner); Администратор БД (admin); Программист. 3.Иванов – программист и руководитель проектов; Петров – тестировщик. 4.sa – имя администратора БД; dbo – стандартное имя владельца.

9 Передача и отзыв прав Базы данных: учебный курс Есть несколько SQL-предложений, позволяющих передать или отозвать определенные права пользователям или ролям. Разумеется, для того, чтобы можно было исполнить такое SQL-предложение, исполнитель должен обладать правом на передачу прав. Примеры: GRANT SELECT ON OBJECT::projects TO vasja WITH GRANT OPTION GRANT EXECUTE ON OBJECT::myproc TO programmer REVOKE EXECUTE ON OBJECT::myproc FROM programmer CASCADE REVOKE ALTER ON OBJECT::projects FROM programmer

10 3. Нормализация данных Базы данных: учебный курс Проблемы: При изменении одной записи может потребоваться внести изменения и в другие, вообще говоря, независимые от нее записи. При удалении данных может быть удалена информация, которую, вообще говоря, желательно было оставить. При добавлении данных может потребоваться вводить неопределенные значения, необходимость которых сомнительна. Говорят, что в БД могут иметь место «аномалии изменения», «аномалии добавления» и «аномалии удаления». Основная задача – облегчить поддержание целостности данных. Дополнительные цели, преследуемые нормализацией: 1.Уменьшить объем таблиц, исключив дублирование данных. 2.Обеспечить быстрый доступ к данным, упростив их структуру.

11 Пример аномалий при изменении данных Базы данных: учебный курс Рассмотрим таблицу участия программистов в проекте: ПроектУчастникРольПроцентДата началаРуководитель P1ВасяИнженер Иванов P1ПетяТестер Иванов P2СережаИнженер Петров P2ВасяИнженер Петров Аномалия изменения: При изменении «Вася» на «Василий» требуется отследить все вхождения «Васи» в атрибуте «Участник» и, возможно, «Руководитель». Аномалия добавления: При добавлении нового проекта необходимо определить хотя бы одного участника проекта или оставить некоторые поля неопределенными. Аномалия удаления: При удалении «Пети» как участника проекта «P1» исчезает информация и о самом сотруднике и о роли «Тестер».

12 Нормальные формы и разделение таблиц Базы данных: учебный курс «Без потери информации» означает, что исходную таблицу всегда можно восстановить, выполнив некоторое «соединение» (JOIN) получившихся В результате разделения меньших таблиц. Процесс нормализации – это, в основном, процесс разделения таблиц на независимые таблицы без потери информации. Разделение таблиц приводит к: уменьшению зависимости между элементами одной записи; уменьшению размеров самих таблиц; уменьшению количества дублирующей информации; увеличению гибкости в формировании результатов запросов. Различают несколько «нормальных форм» данных, которые определяются, прежде всего, уровнем зависимости между значениями атрибутов в одной таблице (или записи). 1НФ; 2НФ; 3НФ; НФБК – традиционные, хорошо изученные НФ; 4НФ; 5НФ – формы, введенные для того, чтобы избежать некоторых достаточно редких аномалий; 6НФ; 7НФ – крайне редко используемые нормальные формы.

13 Первая нормальная форма Базы данных: учебный курс Первая нормальная форма: атрибуты атомарны и каждая таблица имеет по крайней мере один потенциальный ключ. Формально в реляционных БД данные всегда находятся в 1НФ, однако, на практике возможны следующие ситуации, когда все же требуется некоторое «приведение» к 1НФ: в таблице имеются повторяющиеся записи (надо либо удалить дубликаты, либо правильно проидентифицировать их. ФамилияДолжность ИвановИнженер ПетровИнженер ИвановИнженер ФамилияДолжность ИвановИнженер ПетровИнженер НомерФамилияДолжность 1ИвановИнженер 2ПетровИнженер 3ИвановИнженер

14 Первая нормальная форма Базы данных: учебный курс Еще одна ситуация, когда требуется приведение к 1НФ: в таблице имеются составные атрибуты. ФамилияТелефон Иванов Петров ФамилияТелефон Иванов Петров Петров На практике это не всегда «правильное» преобразование. В данном случае разделение атрибутов привело к дублированию данных.

15 Вторая нормальная форма Базы данных: учебный курс ФамилияДолжностьЗарплата ИвановПрограммист4000 ПетровРуководитель5000 СидоровДворник3000 ПетровПрограммист4000 ФамилияДолжность ИвановПрограммист ПетровРуководитель СидоровДворник ПетровПрограммист Данные находятся во второй НФ, если они: находятся в 1НФ; никакой атрибут не находится в полной функциональной зависимости от первичного ключа. Первичный ключ – Фамилия + Должность; Зависимость: Должность Зарплата ДолжностьЗарплата Программист4000 Руководитель5000 Дворник3000

16 Третья нормальная форма Базы данных: учебный курс IDФамилияКомнатаТелефон 1Иванов Петров Сидоров IDФамилияКомната 1Иванов13 2Петров13 3Сидоров15 Данные находятся в третьей НФ, если они: находятся во 2НФ; Всякий неключевой атрибут находится в функциональной зависимости только от первичного ключа. Первичный ключ – ID; Зависимость: Комната Телефон КомнатаТелефон

17 Нормальная форма Бойса-Кодда Базы данных: учебный курс IDФамилияПроектРоль 1Иванов13Руководитель 2Петров13Программист 1Иванов15Программист IDФамилия 1Иванов 2Петров Данные находятся в НФБК, если они: находятся в 3НФ; нет неключевых атрибутов, находящихся в функциональной зависимости от отдельных полей первичного ключа. Потенциальный ключ – ID + Проект Зависимость: ID Фамилия IDПроектРоль 113Руководитель 213Программист 115Программист

18 Заключение Базы данных: учебный курс Обычно полагают, что данные «хорошо нормализованы», если они находятся в НФБК (которую часто называют третьей НФ); Иногда требуется приведение к нормальным формам более высокого порядка (удаление более сложных зависимостей для облегчения поддержания целостности данных); Часто носле нормализации БД разбивается на большое количество таблиц с маленьким числом атрибутов. Это не всегда удобно, так как требует составления сложных запросов для извлечения данных. Если поддерживать целостность не слишком сложно (например, путем определения подходящих триггеров), то иногда имеет смысл провести денормализацию данных.