Базы данных Киселев Денис Викторович
Базы данных (содержание) 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. Работа с данными. Модели данных. Реляционная модель. Проектирование БД. Нормализация. Нормальные формы. Семантическое моделирование. Модель «сущность-связь» . Целостность данных. Язык SQL. DDL. DML. Оператор выборки SELECT. Выборка данных из нескольких таблиц. Подзапросы. Операторы вставки, обновления, удаления. Представления, хранимые процедуры, функции, триггеры. Транзакции. Блокировки. Архитектура СУБД. Выполнение запроса. Работа СУБД с оперативной памятью. Работа СУБД с внешней памятью. Индексы. Резервное копирование БД. Безопасность БД. Администрирование БД. Распределенные БД. Информационные системы (ИС). Жизненный цикл ИС. Архитектура доступа к БД. OLTP и OLAP. Хранилища данных. Объектно-ориентированные БД.
Базы данных Информационная система – это система, обеспечивающая сбор, хранение и выдачу данных. БД – ядро информационной системы. БД – именованная совокупность данных, отображающая состояние объектов и их отношений в рассматриваемой предметной области (части реального мира). Особенности БД: n Данные должны иметь известный формат (метаданные). n Данные должны храниться, извлекаться и модифицироваться с помощью специального программного обеспечения (СУБД). n Данные подчиняются набору принципов и правил (целостность).
Модель ANSI / SPARC Внешний уровень Логическая независимость Концептуальный уровень Физическая независимость Физический уровень
Реляционная модель Отношение R D 1 D 2 … DN Ф Иванович И О Х Сергеевич Петрович • Отношение не может содержать одинаковых строк. • Кортежи не упорядочены. • Атрибуты не упорядочены. = Иванов Иван Сергеевич Иванов Иван Петрович … … Сергей Сергеевич Петров Сергей Иванович Петров Х Иван … Петров Иванов Сергей Петрович Ф И О Иванов Иван Петрович Петров Сергей Сергеевич Петров Сергей Иванович
Ключи Номер Имя Группа Код_предмета Предмет 111111 Иванов МП-21 1 Мат. Анализ … … … 2 Физика 999999 Петров ЭТМО-47 … … Номер Код_предмета Оценка 111111 1 5 111111 2 3 … … … 999999 1 4 999999 2 4
Операции реляционной алгебры (традиционные) n Объединение – отношение, содержащее множество кортежей, принадлежащих одному или обоим исходным отношениям. R 1 R 2 = {r | r R 1 r R 2}. n Пересечение – отношение, содержащее множество кортежей, принадлежащих обоим исходным отношениям. R 1 R 2 = {r | r R 1 r R 2}. n Разность – отношение, содержащее множество кортежей, принадлежащих первому и не принадлежащих второму отношению. R 1 R 2 = {r | r R 1 r R 2}. n Расширенное декартово произведение (сочетание исходных кортежей) – отношение, содержащее множество кортежей, полученных сцеплением каждого кортежа первого отношения с каждым кортежем второго отношения. R 1 R 2 = {(r, q) | r R 1 q R 2}.
Операции реляционной алгебры (специальные) n Горизонтальный выбор (фильтрация) – отношение, включающее кортежи, для которых истинно заданное условие. n Проекция (вертикальный выбор) – отношение, содержащее часть атрибутов исходного отношения. Кортежи не дублируются. n Условное соединение – отношение, содержащее сочетание исходных кортежей, удовлетворяющих условию – общему значению для одного или нескольких общих атрибутов. n Деление – отношение, содержащее кортежи, включающие значения первого подмножества атрибутов кортежей первого отношения, такие, что множество значений второго подмножества атрибутов совпадает с множеством значений второго отношения.
Номер Предмет 111111 МА 111111 ЛА 111111 ОФ 111112 МА 111112 ЛА 111112 111113 Номер ОФ 111111 А 111112 111113 В JOIN ЛА А ОФ Б МА 111112 Б ЛА 111112 Б ОФ 111113 Б А 111112 = МА 111111 ОФ А 111111 Имя Предмет 111111 Номер Имя В ОФ Предмет / МА ЛА ОФ Номер = 1111112
Реляционное исчисление основано на разделе математической логики – исчислении предикатов. Предикат – логическая функция, возвращающая значения «Истина» или «Ложь» . Формулировка правил записи выражений, определяющих новые отношения на основе исходных (без указания способов их получения). Исчисление кортежей. Переменная кортежа – переменная, область допустимых значений которой кортежи данного отношения. Языки ALPHA, QUEL. Исчисление доменов. Переменная домена – переменная, область допустимых значений которой домен. Языки FQL, DEDUCE, QBE. Язык реляционно полный, если содержит все возможности реляционного исчисления.
Нормализация n Результат логического проектирования реляционной БД – набор взаимосвязанных отношений, в которых определены все атрибуты, ключи и правила целостности. n Нормализация – преобразование исходного отношения к приемлемому набору отношений методом последовательных приближений. Цели нормализации: q Уменьшение избыточности. q Уменьшение числа аномалий. q Проектирование понятного макета БД. q Упрощение наложения ограничений целостности. n n Нормализация проводится методом декомпозиции – разбиения отношений на другие отношения
Нормализация, функциональные зависимости Пусть R – отношение, X, Y – произвольные подмножества атрибутов отношения R. Y функционально зависимо от X (X→Y) тогда и только тогда, когда для любого допустимого значения R любое значение X связано с одним значением Y. Если 2 кортежа совпадают по X, они совпадают и по Y. X – детерминант, Y – зависимая часть. Тривиальная ФЗ – ФЗ, которая не может не выполняться. ФЗ тривиальная, когда Y – подмножество X. (X→Y, Y X) Номер, Фамилия → Фамилия. Каждое отношение обязательно удовлетворяет некоторой тривиальной ФЗ (ключ Y и все атрибуты отношения X). Тривиальные ФЗ в расчет не берутся.
Нормализация, функциональные зависимости ФЗ бесконечно много. Одно и то же состояние предметной области может быть отображено разными множествами ФЗ. Пусть S – множество ФЗ. Замыкание S+ – множество всех ФЗ, подразумеваемых S (выводимых из S). S – подмножество S+. Два множества ФЗ S 1 и S 2 эквивалентны, когда S 1+=S 2+. Пусть S 1, S 2 – множества ФЗ. Если любая ФЗ из S 1 является ФЗ S 2, то S 2 – покрытие для S 1. Правила вывода Армстронга (аксиомы Армстронга). Если B A, то A→B. Если A→B, то AC→BC. Если A→B, B→C, то A→C. A→A. Если A→BC, то A→B, A→C. Если A→B, A→C, то A→BC. Если A→B, C→D, то AC→BD.
Нормализация, функциональные зависимости Множество ФЗ неприводимое, когда: n Зависимая часть содержит один атрибут. n Детерминант – неприводим, т. е. ни один атрибут не может быть опущен без изменения замыкания множества. n Ни одна ФЗ не может быть опущена без изменения замыкания множества. Каждое множество эквивалентно хотя бы одному неприводимому множеству (их замыкания совпадают). Неприводимое множество ФЗ, эквивалентное некоторому множеству ФЗ S – неприводимое покрытие множества S. Неприводимое покрытие используется при нормализации. Нормализованное отношение: n Если X – потенциальный ключ, то все атрибуты функционально зависят от X. Если существует X→Y, X – не потенциальный ключ, отношение R содержит избыточные данные. Надо выделить такие ФЗ и преобразовать структуру так, чтобы избавится от них (провести нормализацию).
Нормализация – сокращение числа ФЗ. Нормализация – постепенный процесс улучшения логической структуры БД. Шаг нормализации – переход к следующей нормальной форме (НФ). Разделение отношения на два и более новых отношения. При этом утрачивается одна ФЗ. n n n Каждая следующая НФ лучше предыдущей. При переходе к следующей НФ свойства предыдущих НФ сохраняются. Переходы между НФ обратимы (информация не утрачивается). 1 НФ, 2 НФ, 3 НФ – Кодд, НФБК – Бойс и Кодд, 4 НФ, 5 НФ – Фейджин. Теорема Хеза. Пусть существует отношение R(A, B, C). Если A→B, то R эквивалентно соединению проекций (A, B) и (A, C). R(A, B, C)=R 1(A, B) JOIN R 2(A, C).
Нормализация Хороший результат – БД в 3 НФ. Отношение в 3 НФ, когда любой кортеж состоит из значения первичного ключа, который идентифицирует некоторую сущность и набора взаимно независимых атрибутов, описывающих эту сущность. 1 НФ – отношение содержит только логически неделимые (скалярные) значения. 2 НФ = 1 НФ + каждый неключевой атрибут полностью (неприводимо) зависит от первичного ключа. 3 НФ = 2 НФ + неключевые атрибуты взаимно независимы (не транзитивно зависят от первичного ключа).
Нормализация (пример) Ф, И, О, Номер, Группа, Факультет, Специальность, Предмет, Семестр, Оценка 1 НФ Ф И О Номер Группа Факультет Специальность 2 НФ Ф И О Номер Предмет Семестр Оценка 3 НФ Номер Ф И О Группа Факультет Номер Предмет Группа Специальность Семестр Оценка Предмет Семестр Оценка
Нормализация, декомпозиция При выборе варианта декомпозиции предпочтительнее декомпозиция с независимыми проекциями. Проекции R 1, R 2 отношения R независимы, когда: n любая ФЗ в R является логическим следствием ФЗ в R 1 и R 2, n общие атрибуты R 1 и R 2 образуют потенциальный ключ по крайней мере в одной из них. Проекции независимы, если их обновление может быть выполнено независимо. Номер 2 НФ 3 НФ Номер Группа Номер Факультет Группа Факультет Номер Группа Факультет
3 НФ и НФБК 3 НФ не подходит для следующих отношений: n Отношение имеет 2 или более потенциальных ключей. n Два потенциальных ключа являются сложными (составными). n Они перекрываются (имеют общие атрибуты). Без этих условий 3 НФ эквивалентно НФБК. Отношение в НФБК, когда каждая нетривиальная и неприводимая слева ФЗ обладает потенциальным ключом в качестве детерминанта. 3 НФ НФБК Паспорт Номер Предмет Оценка Семестр Оценка
3 НФ и НФБК (по Заниоло) Пусть существует отношение R. X – некоторое множество атрибутов, A – некоторый атрибут. R находится в 3 НФ, если для любой ФЗ X→A истинно по крайней мере одно из ниже перечисленного: n A – подмножество X (ФЗ - тривиальна). n X содержит потенциальный ключ (X – суперключ). n A – часть потенциального ключа. R находится в НФБК, если для любой ФЗ X→A истинно по крайней мере одно из ниже перечисленного: n A – подмножество X (ФЗ - тривиальна). n X содержит потенциальный ключ (X – суперключ).
Семантическое моделирование. Модель “Entity Relationship“ (Чен, 1976 ) Недостатки проектирования с помощью нормализации: n В модели недостаточно представлен смысл предметной области. n Трудно обсуждать со специалистами предметной области. n Нет средств для представления ФЗ. n Трудно проектировать, начиная с одной большой таблицы. Сущность – класс однотипных объектов. Сущность имеет уникальное имя. Существует множество экземпляров сущности. Каждый экземпляр сущности должен быть отличим от всех других экземпляров. Сущность имеет свои характеристики – атрибуты. Атрибут уточняет, идентифицирует, классифицирует, характеризует или выражает состояние сущности. Набор атрибутов должен быть таким, чтобы экземпляры сущностей различались. Между сущностями существуют связи – бинарные ассоциации, показывающие, каким образом сущности соотносятся и взаимодействуют между собой. Возможны связи, связывающие одну сущность – рекурсивные связи (иерархии).
Модель «Сущность-связь» Связи бывают: n Один к одному (1: 1). n Один ко многим (1: M). n Многие ко многим (M: M). Связи бывают обязательные и необязательные (полные и частичные). n Обязательная связь – должны участвовать все экземпляры сущности. n n Сильная сущность – это сущность, экземпляры которой могут существовать самостоятельно вне зависимости от существования экземпляров других сущностей. Слабая сущность – зависимая сущность. Экземпляр слабой сущности не может существовать самостоятельно, без существования связанного экземпляра сильной сущности. Супертип – сущность, которая может быть представлена в виде набора сущностей-подтипов. Каждый из подтипов имеет общие атрибуты, которые определяются на уровне супертипа. Подтип содержит атрибуты супертипа и свои атрибуты.
Преобразование ER-модели в реляционную модель. n n n q q n Каждой сущности ставится в соответствие отношение. Каждый атрибут сущности становится атрибутом соответствующего отношения. Первичный ключ сущности становится первичным ключом отношения. В каждое отношение, соответствующее подчиненной сущности, добавляются атрибуты – первичные ключи основных сущностей. Для моделирования необязательных связей у атрибутов, соответствующих внешнему ключу, устанавливается свойство допустимости неопределенных значений. Для обязательных связей – атрибуты получают свойство недопустимости неопределенных значений. Для отображения категоризации сущностей возможны разные подходы: Создается одно отношение для всех подтипов одного супертипа. Создаются отдельные отношения для каждого подтипа и супертипа. Для возможности переходов к подтипам от супертипа в супертип включается идентификатор связи. Для связей типа «многие-ко-многим» создаются специальные связующие отношения, связанные с исходными отношениями «один-комногим» .
Реализация связи «многие-ко-многим» Код специальности Идентификатор предмета Специальность Предмет Факультет Кафедра Код специальности Идентификатор предмета
Целостность БД Целостность – соответствие данных, хранимых в БД (их точность и корректность), объектам реального мира и их взаимосвязям в каждый момент времени. Целостность – набор не нарушаемых правил (ограничений). Целостность, обеспечивающая реляционный подход. 1. Структурная целостность (работа с однородными структурами – реляционными отношениями). 2. Языковая целостность (язык должен поддерживать реляционный подход). 3. Целостность первичных ключей (отсутствие неопределенных значений). 4. Ссылочная целостность (непротиворечивость первичных и внешних ключей).
Целостность БД Целостность состояния данных о предметной области (декларативные, немедленно проверяемые ограничения). 1. Целостность атрибута (значение по умолчанию, допустимость неопределенных значений, условия на допустимость значений, уникальность значений и т. д. ). 2. Целостность домена (домен содержит допустимые значения для многих атрибутов). 3. Целостность отношения (согласованность разных атрибутов и кортежей внутри одного отношения). 4. Целостность базы данных (согласованность данных в разных отношениях). Целостность переходов (откладываемые ограничения).
Язык SQL основывается на реляционной алгебре. Язык SQL делится на четыре части: n операторы определения данных (Data Definition Language, DDL) n операторы манипуляции данными (Data Manipulation Language, DML) n операторы определения доступа к данным (Data Control Language, DCL) n операторы управления транзакциями (Transaction Control Language, TCL) Язык SQL разработан в 70 -х в компании IBM Research. СУБД System R. SEQUEL - Structured English QUEry Language С 1986 существуют стандарты (SQL-86, SQL-89, SQL-92, SQL: 1999, SQL: 2003, SQL: 2006, SQL: 2008) СУБД Язык Inter. Base/Firebird PSQL IBM DB 2 SQL PL MS SQL Server/ Sybase ASE Transact. SQL My. SQL/PSM Oracle PL/SQL Postgre. SQL PL/pg. SQL
SQL: операторы DDL n n n CREATE – создание объекта ALTER – изменение структуры объекта DROP – уничтожение объекта CREATE / ALTER / DROP Table / View / Function / Procedure / Login / User / Role /… CREATE TABLE имя_1 (столбец_1 тип_1, столбец_2 тип_2…); ALTER TABLE имя_1 ADD столбец_3 тип_3; ALTER TABLE имя_1 MODIFY столбец_3 тип_3 A; DROP TABLE имя_1;
SQL: оператор выборки SELECT поля FROM таблицы WHERE условие; SELECT Фамилия, Имя FROM Студенты WHERE Группа = ‘МП-21’; SELECT * FROM Студенты WHERE Группа = ‘МП-21’ And Группа = ‘МП-22’; SELECT * FROM Студенты WHERE Группа = ‘МП-21’ Or Группа = ‘МП-22’; SELECT * FROM Студенты WHERE Not Группа = ‘МП-21’; SELECT * FROM Студенты WHERE Группа In (‘МП-21’, ‘МП-22’); SELECT * FROM Студенты WHERE Группа BETWEEN ‘МП-21’ And ‘МП-29’; SELECT * FROM Студенты WHERE Группа Like ‘МП-2%’; SELECT * FROM Студенты WHERE Группа Like ‘МП-2_’;
SQL: оператор выборки SELECT поля FROM таблицы WHERE условие ORDER BY поля; SELECT * FROM Студенты ORDER BY Фамилия, Имя SELECT * FROM Студенты ORDER BY Фамилия ASC, Имя ASC SELECT * FROM Студенты ORDER BY Фамилия DESC, Имя DESC SELECT * FROM Студенты ORDER BY Фамилия, Имя DESC SELECT Distinct / All поля FROM таблицы WHERE условие; SELECT Группа FROM Студенты; SELECT Distinct Группа FROM Студенты;
SQL: оператор выборки SELECT поле 1, поле 2 As псевдоним_поля FROM таблица 1 As псевдоним_таблицы; SELECT фамилия As Ф 1, Студенты. Фамилия As Ф 2, С. Фамилия As Ф 3 FROM Студенты As С; SELECT (Стипендия*10 + 1 000)/5 As Новая_стипендия FROM Студенты; SELECT Year(Getdate())-Year(Дата_рождения) FROM Студенты; SELECT Datediff(Year, Дата_рождения, Getdate()) FROM Студенты; SELECT Фамилия, Nvl(Дата_рождения, Getdate()), Left(Имя, 1)+’. ’ As Имя FROM Студенты;
SQL: оператор выборки SELECT: группировка, агрегатные функции SELECT поля, агрегатные функции FROM таблица GROUP BY поля; Агрегатные функции: • Count(); • Max(); • Min(); • Sum(); • Avg(); • … SELECT Группа, Count(*) FROM Студенты GROUP BY Группа; SELECT Группа, Sum(Стипендия) FROM Студенты GROUP BY Группа; SELECT Count(*) FROM Студенты; SELECT Группа, Предмет, Avg(Оценка) FROM Студенты GROUP BY Группа, Предмет;
SQL: оператор выборки SELECT: группировка, агрегатные функции SELECT поля, агрегатные функции FROM таблица WHERE условие 1 GROUP BY поля HAVING условие 2; SELECT Группа, Avg(возраст) FROM Студенты WHERE Факультет = ‘МПи. ТК’ GROUP BY Группа HAVING Count(*)>25;
SQL: оператор выборки SELECT: запрос к нескольким таблицам Номер Фамилия Имя Группа Номер Предмет Семестр Оценка 111 Иванов Иван МП-11 111 ОМА 1 3 112 Сидоров Сидор МП-11 111 ОФ 1 3 113 Петров Петр МП-12 112 ОФ 1 4 SELECT * FROM Студенты С, Оценки О WHERE С. Номер=О. Номер Фамилия Имя Группа Номер Предмет Семестр Оценка 111 Иванов Иван МП-11 111 ОМА 1 3 111 Иванов Иван МП-11 111 ОФ 1 3 111 Иванов Иван МП-11 112 ОФ 1 4 112 Сидоров Сидор МП-11 111 ОМА 1 3 112 Сидоров Сидор МП-11 111 ОФ 1 3 112 Сидоров Сидор МП-11 112 ОФ 1 4 113 Петров Петр МП-12 111 ОМА 1 3 113 Петров Петр МП-12 111 ОФ 1 3 113 Петров Петр МП-12 112 ОФ 1 4
SQL: оператор выборки SELECT: запрос к нескольким таблицам SELECT * FROM Студенты С, Оценки О WHERE С. Номер = О. Номер; SELECT * FROM Студенты С Inner Join Оценки О On С. Номер = О. Номер; SELECT * FROM Студенты С Left Outer Join Оценки О On С. Номер = О. Номер; Номер Фамилия Имя Группа Номер Предмет Семестр Оценка 111 Иванов Иван МП-11 111 ОМА 1 3 111 Иванов Иван МП-11 111 ОФ 1 3 112 Сидоров Сидор МП-11 112 ОФ 1 4 113 Петров Петр МП-12 NULL SELECT * FROM Студенты С Right Outer Join Оценки О On С. Номер = О. Номер; SELECT * FROM Студенты С Full Outer Join Оценки О On С. Номер = О. Номер;
SQL: оператор выборки SELECT Номер Имя Старший_номер 1 А 0 2 Б 1 3 В 1 4 Г 1 5 Д 2 6 Е 2 А Б Д Б А В А Г А Д Б Е Б В Г Е SELECT Т 1. Имя, Т 2. Имя FROM Таблица Т 1 Inner Join Таблица Т 2 On Т 2. Номер = Т 1. Старший_номер;
SQL: оператор выборки SELECT: некоррелированные подзапросы SELECT Фамилия, Имя, (SELECT Count(*) FROM Студенты WHERE Группа = ‘МП-21’; SELECT Фамилия, Имя, Оценка FROM Студенты С Left Join (SELECT * FROM Оценки WHERE Предмет = ‘ОМА’) О On С. Номер = О. Номер WHERE Группа = ‘МП-21’; SELECT Фамилия, Имя, СБ FROM Студенты С Left Join (SELECT Номер, Avg (Оценка) As СБ FROM Оценки GROUP BY Номер) О On С. Номер = О. Номер WHERE Группа = ‘МП-21’; SELECT Фамилия, Имя FROM Студенты WHERE Дата_рождения = (SELECT Min (Дата_рождения) FROM Студенты); SELECT Фамилия, Имя FROM Студенты WHERE Номер In (SELECT Номер FROM Оценки WHERE Предмет = ‘ОМА’ And Оценка = 5);
SQL: оператор выборки SELECT: коррелированные подзапросы SELECT Фамилия, Имя, (SELECT Count(*) FROM Студенты WHERE Группа = С. Группа) FROM Студенты С WHERE Группа = ‘МП-21’; Номер Предмет Дата Оценка 111 ОМА 05. 01. 10 2 111 ОМА 20. 01. 10 2 111 ОМА 07. 02. 10 3 SELECT Номер, Предмет, Оценка FROM Оценки О WHERE Дата = (SELECT Max (Дата) FROM Оценки WHERE Номер = О. Номер And Предмет = О. Предмет) SELECT * FROM Оценки О WHERE EXISTS (SELECT * FROM Оценки WHERE Дата < О. Дата And Номер = О. Номер And Предмет = О. Предмет)
SQL: оператор выборки SELECT Объединение: SELECT Фамилия, Имя, Отчество FROM Студенты UNION ALL SELECT Фамилия, Имя, Отчество FROM Сотрудники; Пересечение: SELECT Фамилия, Имя, Отчество FROM Студенты INTERSECT SELECT Фамилия, Имя, Отчество FROM Сотрудники; Исключение: SELECT Фамилия, Имя, Отчество FROM Студенты EXCEPT SELECT Фамилия, Имя, Отчество FROM Сотрудники;
SQL: операторы INSERT, UPDATE INSERT INTO Table_1 (ID, Ф, И, О) VALUES (999999, ‘Иванов’, ‘Иванович’); INSERT INTO Table_1 (ID, Ф, И, О) SELECT Номер, Фамилия, Имя, Отчество FROM Студенты WHERE Группа = ‘МП-21’; UPDATE Table_1 SET Ф=‘Петров’ WHERE Ф=‘Иванов’; UPDATE Table_1 SET Ф=‘Петров’ FROM Table_1 T inner Join Студенты С On T. ID = С. Номер WHERE Группа = ‘МП-21’;
SQL: операторы DELETE, TRUNCATE DELETE FROM Студенты; DELETE FROM Студенты WHERE Группа = ‘МП-21’; DELETE FROM Студенты WHERE Номер In (SELECT Номер FROM Оценки WHERE Оценка = 2); TRUNCATE TABLE Студенты;
Объекты БД: представления Представление – именованная динамически поддерживаемая сервером выборка из одной или нескольких таблиц (виртуальная таблица) : CREATE VIEW MP AS SELECT * FROM Студенты WHERE Факультет = ‘МПи. ТК’; CREATE VIEW M_FM AS SELECT Пол, count(*) FROM Студенты GROUP BY Пол; SELECT * FROM MP; • Актуальные данные. • Простой доступ к сложным данным. • Способ обеспечения безопасности доступа и скрытия структуры.
Объекты БД: процедуры и функции Процедура: CREATE Procedure Dep_List @Dep char(10) AS SELECT * FROM Студенты WHERE Факультет =@Dep; EXECUTE Dep_List ‘МПи. ТК’; Функция: CREATE Function Avg_Mark @Num char(10) AS RETURN ( SELECT Avg(Оценка) FROM Оценки WHERE Номер =@Num ); SELECT Avg_mark (‘ 999999’);
Объекты БД: триггеры Триггер – процедура, автоматически выполняемая при наступлении заданного события. • For, • Instead Of. CREATE TRIGGER Stud_Status ON Оценки FOR INSERT, UPDATE AS IF (SELECT Оценка FROM Inserted) = 2 THEN INSERT INTO Двоечники (Номер) SELECT Номер FROM inserted; CREATE TRIGGER spec_trigg ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT ‘Не надо этого делать!!!’ ROLLBACK;
Транзакции Транзакция – набор операций (изменений), который должная быть выполнен полностью или не выполнен совсем (единый логический блок). Механизм транзакций – основа обеспечения целостности БД. Свойства транзакции (ACID): • Atomicity – атомарность, • Consistency – согласованность, • Isolation – изолированность, • Durability – устойчивость.
Проблемы параллельной обработки SELECT Money FROM Bank WHERE Account = 565488; 1 000 INSERT INTO Pays VALUES (800); UPDATE Bank SET Money = 200 WHERE Account = 565488; SELECT Money FROM Bank WHERE Account = 565488; 1 000 INSERT INTO Pays VALUES (500); UPDATE Bank SET Money = 500 WHERE Account = 565488;
Транзакции • Явные; • Неявные. BEGIN TRANSACTION INSERT… UPDATE… … COMMIT / ROLLBACK BEGIN TRANSACTION INSERT… SAVE TRANSACTION T 1 UPDATE… … ROLLBACK T 1
Уровни изоляции транзакций n n n Serializable – нельзя обращаться к данным, обрабатываемым другой транзакцией. Repeatable Read – нельзя обращаться к обновленным или удаленным данным, но можно к добавленным. Read Committed – можно обращаться к зафиксированным данным. Read Uncommitted – можно обращаться к любым обновленным и не зафиксированным данным. Snapshot – каждая транзакция работает со своей версией данных.
Блокировки n n n n Shared (совмещаемая) Update (обновления) Exclusive (монопольная) Intent (намерения) Schema (схемы) Bulk Update (массового обновления) Key-Range (диапазона ключей)
Архитектура СУБД
Структура ядра СУБД на примере MS SQL Server
Обработка запроса СУБД
Работа СУБД с внешней памятью Организация физического хранения БД – способы размещения данных в среде хранения и способы доступа к данным на физическом уровне. Страница – минимальная единица хранения, которой манипулирует сервер. Работа с данными идет постранично. Экстент – непрерывная область дисковой памяти, состоит из страниц. Файлы: • Файлы данных (data files) • Файлы журналов (log files) Распределение файлов по разным дискам увеличивает производительность.
Индексы Индекс (INDEX) – вспомогательная структура данных, используемая для доступа к данным. Хранит указатели на места хранения записей. Создается для столбцов таблицы. При обращении к индексированному столбцу по значению находит соответствующие записи. Составной индекс – это индекс, определенный более чем по одному столбцу. Scan – сканирование таблицы (перебор всех строк). Seek – поиск с помощью индекса. CREATE INDEX Index_1 ON Table_1 (Column_1, Column_2);
Индекс – сбалансированное дерево (B-tree) Александр Евгений Семен Александр Анна Владимир Александр Алла Алексей Анна Арнольд Борис Евгений Олег Павел Владимир Денис Дмитрий Евгений … Олег … Семен Сергей Яков Павел … Семен … Сергей … Кластеризованный индекс определяет порядок хранения таблицы. Некластеризованный индекс ссылается на ключ кластеризованного. Яков …
Redundant Array of Independent Disks RAID 0 А-1 Б-2 А-3 Б-4 А-1 Б-2 КЧ-3 Б-1 КЧ-2 А-3 КЧ-1 А-2 Б-3 RAID 10 RAID 1 А-2 А-3 А-4 RAID 5 А-1 А-2 А-3 А-4 А-1 Б-2 А-3 Б-4 Б-1 А-2 Б-3 А-4
Сравнение RAID Допустимые потери 0 Чередование 1 Зеркальный набор Один диск Хорошее чтение, приемлемая запись 5 Чередование с контролем четкости Один диск Отличное чтение, медленная запись, экономия места Половина дисков (не парные) Максимальная избыточность и производительност ь 10 Чередование зеркал Без избыточно сти Характеристики Большая производительност ь чтения и записи
Резервное копирование БД (SQL Server) n Полное q n Дифференциальное q n BACKUP DATABASE db 1 TO DISK=‘…’ WITH DIFFERENTIAL Файловое q n BACKUP DATABASE db 1 TO DISK=‘…’ BACKUP DATABASE db 1 FILEGROUP=fg 1 TO DISK=‘…’ Журналов q BACKUP LOG db 1 TO DISK=‘…’
Процесс полного резервного копирования n n n n Блокируется БД и все транзакции. В Log File добавляется маркер. Снимается блокировка БД. Выполняется Backup. Блокируется БД и все транзакции. В Log File добавляется маркер. Снимается блокировка БД. В резервную копию добавляются транзакции между маркерами.
Восстановление БД q n RESTORE DATABASE db 1 FROM DISK=‘…’ WITH NORECOVERY; q n n n Разностная копия: RESTORE DATABASE db 1 FROM DISK=‘…’ WITH NORECOVERY; q n Полная копия: Копии журналов: RESTORE LOG db 1 FROM DISK=‘…’ WITH NORECOVERY; … RESTORE LOG db 1 FROM DISK=‘…’ WITH RECOVERY;
Безопасность БД n Безопасность БД – защита БД от несанкционированного разрушения, изменения и получения данных. n Система безопасна, если пользователь может выполнить только разрешенные действия. n Должна быть разработана, и соблюдаться политика защиты, которая зависит от уровня секретности (конфиденциальности) хранимых данных. n Пользователь – главный путь доступа к данным. n Управление пользователями – один из важнейших элементов защиты БД. n Привилегия – разрешение на выполнение в системе определенного действия. Каждый пользователь имеет набор привилегий. Привилегии бывают: n q q Системные. Объектные.
Политика безопасности
Концепция Microsoft
Уровни защиты информационной системы Защита на уровне рабочей станции Защита на уровне сервера бизнес-логики Защита на уровне сети Защита на уровне СУБД
Организация защиты данных Login Сервер База данных User Схема Объект
Предоставление доступа и привилегий n CREATE LOGIN Sotrudnik 1 WITH PASSWORD=‘…’; n CREATE USER Sotrudnik 1 FROM LOGIN Sotrudnik 1; n GRANT SELECT ON OBJECT: : Schema 1. Table 1 TO Sotrudnik 1; n CREATE ROLE Role 1 AUTHORIZATION User 2; n GRANT INSERT ON OBJECT: : Schema 1. Table 2 TO Role 1; n GRANT CREATE TABLE TO Role 1; n REVOKE SELECT FROM Sotrudnik 1; n DENY UPDATE … FROM Sotrudnik 1;
Угрозы безопасности: SQL injections Инъекции: n Вставка SQL-кода в пользовательские переменные. n Внедрение несанкционированного кода в таблицы или метаданные. Проверка: n Не делать предположений о размере, типе и содержимом получаемых данных. n Проверять размер и тип данных. n Проверять содержимое строковых переменных. n XML-документы проверять на соответствие схеме. n Не создавать инструкции из данных, вводимых пользователем. n Для проверки использовать хранимые процедуры. n Использовать многоуровневую проверку достоверности. n Не допускать использование строк, из которых могут быть созданы имена файлов (AUX, CLOCK$, COM 1, …, COM 8, CONFIG$, LPT 1, …, LPT 8, NUL, PRN).
Угрозы безопасности: вирусы и черви Профилактика вирусов и червей: n Установка антивирусного ПО. n Установка обновлений и исправлений. n Защита Database Mail. n Защита брандмауэром. n Надежные пароли. Реакция на инфекцию: n Изолировать сервер. n Исследовать заражение. n Приостановить работу служб сервера. n Обновить антивирусное ПО и провести полную проверку. n Установить обновления и исправления. n Оценить риск и ущерб. n Убедится в решении проблемы.
Угрозы безопасности: Dos-атаки и внутренние атаки Распознавание Do. S-атаки (Denial of Service): n Огромное количество регистраций. n Рост сетевого трафика. n Снижение производительности. n Увеличение времени задержки при соединении. Реагирование на Do. S-атаку: n Приостановка служб. n Отключение или перезапуск служб с помощью DAC. n Настройка брандмауэра (по IP-адресам). Реагирование на внутренние атаки: n Отключение учетных записей. n Изменение учетных данных приложений. n Смена паролей. n Ужесточение аудита.
Аудит n n Аудит БД – контроль над выполняемыми операциями в БД (их регистрация). Причины проведения аудита: q q n Проведение аудита наносит ущерб производительности и объему. Необходимо ограничивать количество отслеживаемых событий. q q n фиксирование несанкционированного доступа, злонамеренных действий, необходимость определения величины нагрузки, контроля над действиями пользователей. Аудит сессий. Регистрация подключений, удачных и неудачных соединений. Аудит операторов и предложений. Регистрация заданных операторов. Аудит привилегий. Регистрация выдача и снятия привилегий. Аудит объектов. Регистрация действий над объектами. ALTER TABLE Table 1 ENABLE CHANGE_TRACKING;
Администрирование БД Обязанности администратора БД: n Установка программного обеспечения. n Конфигурирование аппаратуры и программного обеспечения. Правильное распределение файлов по дискам. Использование RAID. n Защита данных, управление пользователями. n Аудит системы, настройка и оптимизация производительности, планирование мощности. n Резервное копирование и восстановление данных. n Планирование периодов неработоспособности. n Восстановление после аварий. n Документирование. Документация о конфигурации и структуре. Системный журнал (изменения, события, отказы, резервные копирования и восстановления). Планы технического обслуживания, резервного копирования, восстановления после аварии.
Распределенные БД – данные хранятся в разных физически удаленных БД. Главная задача – обеспечение механизма интеграции, чтобы пользователь в любом узле имел доступ как к единой БД. Больше копий – больше конфликтов. Меньше копий – больше проблем с коммуникациями и аппаратурой. В запросе лучше использовать минимальное число ссылок на удаленные БД. Неэффективный запрос в распределенной системе еще более не эффективен. Центральный офис Филиал 1 Филиал 3 Филиал 2
Требования к распределенным БД (12 правил Дейта) 1. Локальная автономия – фрагмент распределенной БД функционирует как полноценная локальная БД. 2. Децентрализация – все БД являются равноправными поставщиками информации в общее пространство данных (отсутствие узких мест). 3. Непрерывность операций – все данные доступны всегда, операции могут выполняться непрерывно (в том числе и при изменении конфигурации). 4. Прозрачность расположения – для обращения не нужно знать, где реально находятся запрашиваемые данные, передача и обработка происходит встроенными системными средствами. 5. Независимая фрагментация – возможность распределенного размещения данных, логически представляющих собой единое целое. (Горизонтальное и вертикальное разделения отношений. ) 6. Независимое тиражирование – механизм переноса изменений из исходного узла в другие узлы.
Требования к распределенным БД (12 правил Дейта) 7. Обработка распределенных запросов (запросов, при обработке которых используются данные из разных узлов). 8. Обработка распределенных транзакций (возможность обновления данных в разных узлов без нарушения целостности данных). 9. Независимость от оборудования. 10. Независимость от операционных систем. 11. Прозрачность сети – возможен доступ по сети с использованием любых сетевых протоколов. 12. Независимость от СУБД.
Двухфазные транзакции 1. Компиляция запроса 2. Определение плана выполнения запроса. 3. Декомпозиция, отправка в соответствующие узлы. 4. Фиксация транзакций на узлах. 5. Окончательная фиксация или откат на всех узлах. UPDATE… COMMIT
Репликация – копирование данных между БД. Издатель - БД, предоставляющая данные для репликации. Дистрибьютор - БД, управляющая репликацией, хранящая данные о репликации (журнал репликации). Подписчик - БД, получающая и сохраняющая реплицируемые данные. Статья – реплицируемый объект (таблица, представление, процедура). Публикация – набор статей из одной БД. 1. Репликация снимков (Snapshot Replication). Периодическое копирование данных от издателя подписчику. 2. Репликация транзакций (Transaction Replication). Изменения по журналу транзакций передается подписчикам. 3. Репликация слиянием (Merge Replication). Изменения передаются с помощью триггеров. Изменения происходят на всех серверах. Возможны конфликты. Конфигурирование подписки: принудительная подписка и подписка по запросу. Специальный агент на дистрибьюторе или подписчике.
Репликация Центральный издатель и дистрибьютор, подписчики. Центральный подписчик, несколько издателей. Центральный издатель, удаленный дистрибьютор, подписчики. Одноранговая репликация
Информационные системы Автоматизированная система – это система, состоящая из персонала и комплекса средств автоматизации его деятельности, реализующая информационную технологию выполнения установленных функций (ГОСТ). Информационная система (ИС) – совокупность информационных, экономикоматематических методов и моделей, технических, программных, технологических средств и специалистов, предназначенная для сбора, хранения, обработки и выдачи информации и принятия управленческих решений. Жизненный цикл ИС: n Анализ требований. n Проектирование. n Кодирование. n Тестирование (модульное и комплексное). n Внедрение. n Сопровождение. Сложности проектирования и разработки: n Сложности реальной предметной области (не все формализовано). n Низкое качество описания предметной области. n Сложности обеспечения гибкости ИС (все меняется). n Трудности управления процессом разработки.
Распределение времени при разработке АИС
Распределение времени при разработке, эксплуатации и сопровождении АИС
Модель жизненного цикла ИС Модель жизненного цикла – структура, описывающая процессы, действия и задачи, которые осуществляются в ходе жизни ИС. Виды МЖЦ ИС: n Каскадная, n Итерационная, n Спиральная.
Модели жизненного цикла ИС
Архитектура доступа к БД. Технология «Клиент-сервер» . n n Сервер – любая система, процесс, компьютер, владеющие каким-либо вычислительным ресурсом (памятью, временем и т. д. ) Клиент - любая система, процесс, компьютер, пользователь, запрашивающие у сервера какой-либо ресурс, пользующиеся каким-либо ресурсом или обслуживаемые сервером иным способом. Клиент устанавливает соединение с сервером, формирует и отсылает запрос, получает результаты, обрабатывает полученные данные. Клиент и сервер – процессы, которые в принципе могут работать на одной машине. Группы функций: n Функции ввода и отображения данных (Presentation Logic) – формирование экранных изображений, чтение и запись в экранных формах, управление экраном, обработка мыши и клавиатуры. n Прикладные функции, определяющие основные алгоритмы решения задач приложения (Business Logic). n Функции обработки данных внутри приложения (Database Logic). n Функции управления информационными ресурсами (Database Manager System). n Служебные функции (связывающие остальные).
Удаленное управление данными (файловый сервер) Достоинства: • Простота. • Невысокие требования к производительности сервера. Недостатки: • Высокий сетевой трафик. • Узкий спектр возможных операций. • Отсутствие достаточных средств безопасности доступа. Клиент Сервер PL, BL, DMS Файлы данных
Удаленный доступ к данным (RDA-модель) Достоинства: • Используется реляционный способ доступа. • Скорость выше. • Приложение не управляет БД. • Безопасность выше. • Приложение проще. • Сервер разгружен от прикладных задач. • Уменьшена загрузка сети. Клиент Сервер PL, BL DL, DMS Недостатки: • Сеть все-таки сильно загружена. • Бизнес-логика повторяется на каждом клиенте. • Дублирование. • Сложность.
Сервер баз данных (DBS-модель). Достоинство: «Активный» сервер. Достоинство-недостаток: клиента разгрузили, сервер разгрузили. Клиент Сервер PL, BL BL, DMS
Хранилища данных (Data Warehouse) ХД – предметно-ориентированное, интегрированное, поддерживающее хронологию, неизменяемое собрание данных, ориентированных на процессы принятия управленческих решений. Свойства ХД: n Ориентированность на предметную область. n Интегрированность. n Зависимость от времени. n Постоянство. Функции ХД: n Извлечение данных из источников, их трансформация (агрегирование) и загрузка в ХД. n Извлечение из ХД, аналитическая обработка и предоставление пользователям. Системы: n OLTP (On-Line Transactions Processing), n OLAP (On-Line Analytical Processing).
Многомерная модель данных Январь 2010 МПи. ТК 5 34
ROLAP (схема «звезда» ) Список факультетов ИД_факультета ИД_периода ИД_предмета Оценка Количество 1 1 1 2 23 1 1 1 3 43 1 1 1 4 32 1 1 1 5 42 1 1 2 2 62 1 1 2 3 35 Список периодов Список предметов
Объектно-ориентированные БД n n n Объекты – абстракции, обладающие набором признаков (атрибутов). Классы – совокупности однотипных объектов. Наследование – передача свойств класса к производному классу. Инкапсуляция – каждый объект обладает некоторым внутренним состоянием (структура спрятана от пользователя). Идентификация объектов - присвоение объекту системно уникального идентификатора (OID – Object IDentifier), таким образом, каждый объект в СУБД уникален. Полиморфизм - различные объекты могут по разному реагировать на одинаковые внешние события в зависимости от того, как реализованы их методы.
Концепции Объектноориентированной модели данных n n n n n в ООМД сущности реального мира моделируются объектами; каждый объект состоит из атрибутов и набора методов; каждый объект может ссылаться на другой объект или множество объектов; атрибуты и реализации методов скрыты (инкапсулированы) от других объектов; каждый объект идентифицируется уникальным идентификатором объекта (OID), не зависящим от атрибутов объекта. Данный идентификатор присваивается автоматически, существует во время существования объекта и удаляется при удалении этого объекта; схожие объекты группируются в класс, который содержит описание данных (атрибуты и переменные экземпляров) и реализации методов; класс описывает тип объекта; классы организованы в иерархию классов; каждый объект класса наследует все свойства своего суперкласса в иерархии классов.
Преимущества и недостатки ООБД Борется с следующими недостатками реляционной модели: n сложность структуры, вызванная процессом нормализации; n низкая производительность из-за поиска по ключу; n ограниченный набор типов данных (например, отсутствуют формы мультимедиа, геоинформации и т. д. ); n недостаточное естественное представление данных (в виде плоских двумерных таблиц, а не более сложных структур); n невозможность определить набор операторов (методов), связанных с определенным типом данных: приходится задавать операции в конкретном приложении; Недостатки объектно-ориентированной модели: n сложность структуры; n нет полноценного аналога реляционной алгебры; n нет оптимизации запросов; n отсутствие отработанного языка; n недостаточная отработка сложной системы блокировок; n отсутствие поддержки авторизации (разрешения/запрещения доступа пользователя к данным); n сложность добавления атрибута или метода.
XML
Скачать презентацию Базы данных Киселев Денис Викторович Базы данных
БД лекции киселёв.ppt