Базы данных (часть 1) Киселев Денис Викторович
Содержание 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. Работа с данными. Модели данных. Реляционная модель. Проектирование БД. Нормализация. Нормальные формы. Семантическое моделирование. Модель «сущностьсвязь» . Целостность данных. Язык SQL. DDL. DML. Оператор выборки SELECT. Выборка данных из нескольких таблиц. Подзапросы. Операторы вставки, обновления, удаления. Представления, хранимые процедуры, функции, триггеры. Транзакции. Блокировки.
Базы данных Отличие задач хранения и обработки данных от вычислительных задач – необходимость надежного внешнего хранения больших объемов данных сложной структуры независимо от электропитания. Информационная система – это система, обеспечивающая сбор, хранение и выдачу данных. БД – ядро информационной системы. БД – именованная совокупность данных, отображающая состояние объектов и их отношений в рассматриваемой предметной области (части реального мира). Особенности БД: n Данные должны иметь известный формат (метаданные). n Данные должны храниться, извлекаться и модифицироваться с помощью специального программного обеспечения (СУБД). n Данные подчиняются набору принципов и правил (целостность).
Проблемы файловых систем n n Структура хранимых данных описывается в обрабатывающих программах. Меняется структура – меняется все программное обеспечение. Данные дублируются. Трудно обеспечить параллельное изменение данных. Безопасность. Главное в переходе к БД – устранение избыточности и противоречивости!
Модель ANSI / SPARC Внешний уровень Логическая независимость Концептуальный уровень Физическая независимость Физический уровень
Системы управления базами данных n СУБД – совокупность языковых и программных средств, предназначенных для создания и использования БД. q q q Язык описания данных. Язык манипулирования данными. Инструментальная среда создания и использования БД.
Поколения СУБД n n n Поколение 1. Большие центральные ЭВМ. СУБД IMS фирмы IBM (1968). Стандарт CODASYL (1975). ЭВМ IBM 360/370, PDP-11. БД – во внешней памяти ЦЭВМ. Доступ – через консольные терминалы без собственных вычислительных ресурсов. Управление ресурсами – средствами операционной системы. Поколение 2. Персональные ЭВМ. Настольные БД. Массовое распространение. Преимущественно локальный монопольный доступ. При сетевом доступе – копирование файлов. Обеспечение целостности с помощью приложений. DBase, Fox. Pro, Clipper, Paradox, Clarion. Поколение 3. Серверы БД. Сетевая работа с данными. Распределенный многопользовательский параллельный доступ. Обеспечение целостности сервером БД. Мощные средства администрирования. Поддержка многоплатформенности.
Виды моделей данных Модель данных – абстракция, описывающая структуру (организацию) данных и отношения между ними. n n n Инфологическая (семантическая) модель данных – описание объектов предметной области, их свойств и взаимосвязей. Данные представлены в виде, независимом от СУБД. Даталогическиая модель – описание структуры данных с учетом конкретной СУБД. Физическая модель – описывание методов размещения данных на носителях информации.
Иерархическая модель данных n n n n СУБД IMS (Information Management System) компании IBM, язык DL/1, 1968. Схема иерархической БД – совокупность деревьев. Поле – минимальная неделимая единица данных. Сегмент – набор полей. Сегменты связаны в ориентированный древовидный граф. Ребра отображают иерархические связи между сегментами. Дерево – физическая БД. В каждой физической БД существует один корневой сегмент. Каждый исходный сегмент может быть связан с произвольным числом подчиненных сегментов. Каждый подчиненный сегмент связан только с одним исходным сегментом.
Сетевая модель данных n n n n n Чарльз Бахман. СУБД IDS (Integrated Data Store) компании General Electric. Стандарт CODASYL (1975). Расширение иерархической модели. Элемент данных – минимальная неделимая единица данных. Агрегат данных – обобщение элементов данных. Запись – совокупность агрегатов или элементов данных, моделирующая класс объектов реального мира. Набор данных – иерархическая связь между двумя типами записей. Для любых двух типов записей может быть задано любое количество наборов. Один тип записи не может быть одновременно и владельцем и членом набора. Нет корневой записи.
Реляционная модель данных Кодд, 1970. Переход от работы с элементами данных к работе с объектами. Реляционная БД – набор плоских таблиц. n Серьезная теоретическая математическая основа (реляционная алгебра и реляционное исчисление). n Работа с множествами, а не с единичными записями (операторами, делающими новые таблицы из исходных таблиц). n Более абстрактно, чем навигационные модели. Описывается логическая структура данных, без дополнительных машинных структур (физических указателей). Навигация автоматическая. n Наглядность и гибкость.
Реляционная модель Отношение R D 1 D 2 … DN Ф Иванов Петров Иван Х Сергей Иванович Х Сергеевич Петрович = И О Иванович Иванов Иван Сергеевич Иванов Иван Петрович … … … Петров Сергей Сергеевич Петров Сергей Петрович Ф И О Иванов Иван Петрович Петров Сергей Сергеевич Петров Сергей Иванович
Реляционная модель n n n n n БД – множество взаимосвязанных отношений. Кортеж – строка отношения. Атрибут – вхождение домена в отношение. Ранг (степень) отношения – количество атрибутов. Кардинальное число отношения – количество кортежей. Ключи – средство идентификации кортежей и связи отношений. Отношение не может содержать одинаковых кортежей. Кортежи не упорядочены. Атрибуты не упорядочены.
Ключи Номер Имя Группа Код_предмета Предмет 111111 Иванов МП-21 1 Мат. Анализ … … … 2 Физика 999999 Петров ЭТМО-47 … … Номер Код_предмета Оценка 111111 1 5 111111 2 3 … … … 999999 1 4 999999 2 4
Реляционная алгебра n n Реляционная алгебра основана на теории множеств. Операции производятся над отношениями. Результат любой операции – отношение (Свойство замкнутости): результат одной операции может использоваться в качестве исходных данных для другой (можно работать с вложенными выражениями). Язык реляционно полный, если содержит все операции реляционной алгебры.
Операции реляционной алгебры (традиционные) 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. Язык реляционно полный, если содержит все возможности реляционного исчисления.
Целостность БД Целостность – соответствие данных, хранимых в БД (их точность и корректность), объектам реального мира и их взаимосвязям в каждый момент времени. Целостность – набор не нарушаемых правил (ограничений). Целостность, обеспечивающая реляционный подход. 1. Структурная целостность (работа с однородными структурами – реляционными отношениями). 2. Языковая целостность (язык должен поддерживать реляционный подход). 3. Целостность первичных ключей (отсутствие неопределенных значений). 4. Ссылочная целостность (непротиворечивость первичных и внешних ключей).
Целостность БД Целостность состояния данных о предметной области (декларативные, немедленно проверяемые ограничения). 1. Целостность атрибута (значение по умолчанию, допустимость неопределенных значений, условия на допустимость значений, уникальность значений и т. д. ). 2. Целостность домена (домен содержит допустимые значения для многих атрибутов). 3. Целостность отношения (согласованность разных атрибутов и кортежей внутри одного отношения). 4. Целостность базы данных (согласованность данных в разных отношениях). Целостность переходов (откладываемые ограничения).
Правила Кодда для реляционных БД 1. Данные представляются в виде 2. 3. 4. 5. 6. таблиц. Данные доступны логически. NULL трактуется как неизвестное значение. БД включает в себя метаданные. Для взаимодействия с СУБД используется единый язык. СУБД обеспечивает альтернативные виды отображения данных.
Правила Кодда для реляционных БД 7. Данные поддерживают операции реляционной алгебры. 8. Обеспечивается независимость от физической организации данных. 9. Обеспечивается независимость от логической организации данных. 10. За целостность данных отвечает СУБД. 11. Целостность данных не может быть нарушена. 12. Поддерживаются распределенные операции.
Проектирование БД – создание структуры БД. n Концептуальное (семантическое). Анализ предметной области. n Логическое. n Физическое. Эффективное размещение БД на внешних носителях.
Нормализация n Результат логического проектирования реляционной БД – набор взаимосвязанных отношений, в которых определены все атрибуты, ключи и правила целостности. n Нормализация – преобразование исходного отношения к приемлемому набору отношений методом последовательных приближений. Цели нормализации: q Уменьшение избыточности. q Уменьшение числа аномалий. q Проектирование понятного макета БД. q Упрощение наложения ограничений целостности. n n Нормализация проводится методом декомпозиции – разбиения отношений на другие отношения
Аномалии в ненормализованной таблице Номер Фамилия Имя Группа Предмет Оценка 111111 Иванов Иван ЭКТ-11 МА 3 111111 Иванов Иван ЭКТ-21 ОФ 4 111112 Петров Петр ЭКТ-11 МА 5
Нормализация, функциональные зависимости Пусть 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 Каждой сущности ставится в соответствие отношение. Каждый атрибут сущности становится атрибутом соответствующего отношения. Первичный ключ сущности становится первичным ключом отношения. В каждое отношение, соответствующее подчиненной сущности, добавляются атрибуты – первичные ключи основных сущностей. Для моделирования необязательных связей у атрибутов, соответствующих внешнему ключу, устанавливается свойство допустимости неопределенных значений. Для обязательных связей – атрибуты получают свойство недопустимости неопределенных значений. Для отображения категоризации сущностей возможны разные подходы: Создается одно отношение для всех подтипов одного супертипа. Создаются отдельные отношения для каждого подтипа и супертипа. Для возможности переходов к подтипам от супертипа в супертип включается идентификатор связи. Для связей типа «многие-ко-многим» создаются специальные связующие отношения, связанные с исходными отношениями «один-комногим» .
Реализация связи «многие-ко-многим» Код специальности Идентификатор предмета Специальность Предмет Факультет Кафедра Код специальности Идентификатор предмета
Язык 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 Date. Diff(Year, Дата_рождения, Getdate()) FROM Студенты; SELECT Фамилия, Is. Null(Дата_рождения, 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 – каждая транзакция работает со своей версией данных. SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE }
Уровни изоляции транзакций Dirty read Serializable Repeatable Read Committed Read Uncommitted Snapshot Nonrepeatable read Phantom
Блокировки n n n n Shared (совмещаемая) Update (обновления) Exclusive (монопольная) Intent (намерения) Schema (схемы) Bulk Update (массового обновления) Key-Range (диапазона ключей)
Блокировки Shared (S) Операции чтения. Не совместима с X-блокировками. При Repeatable Read и выше удерживается все время выполнения транзакции. Exclusive (X) Модификация данных. Не совместима с другими блокировками. Update (U) Ожидается повышение от S-блокировки до X-блокировки. Может получить только одна транзакция. Intent (IS, IX, SIX) Повышение производительности и эффективности. Блокировка на объект высокого уровня (таблицу) перед установкой X/Sблокировки на объект низкого уровня (страницу). Schema (Sch- Блокирует все операции при модификации схемы (Sch-M) и M, Sch-S) компиляции или выполнении запросов (Sch-S) Bulk Update (BU) Массовая вставка. Запрещает доступ к таблицам другим процессам. Key-range Защита диапазона строк от фантомных вставок и удалений. Только на уровне SERIALIZABLE.
