Базы данных Коробецкая А.А. 1 Самара 2014

2 Баллы 10Посещение лекционных занятий 20Самостоятельные работы 40Лабораторный практикум 30Экзамен Опоздание на перекличку приравнивается к пропуску занятия. Пропуск можно закрыть, предъявив конспект лекции. Лабораторный практикум: 1.Проектирование БД 2.Реализация в связке Access + Delphi Сумма баллов Оценка (неуд.) (удов.) (хор.) (отл.)

Основные темы 3 1.Концептуальное проектирование БД. 2.Проектирование реляционных БД. Реляционная алгебра. 3.Структурированный язык запросов – SQL. 4.Физическое проектирование БД. 5.Администрирование БД.

Лекция 1. Введение. Концептуальное (инфологическое) проектирование 4

База данных (БД) 5 Признаки БД: -электронный формат; -структурированность; -наличие схемы данных (метаданных) в составе БД; -большой объем хранимых данных; +общая предметная область. БД представляет собой электронное хранилище, в котором находятся структурированные данные для ускорения их поиска и обработки. БД организованная в соответствии с определёнными правилами и поддерживаемая в памяти компьютера совокупность данных, характеризующая актуальное состояние некоторой предметной области и используемая для удовлетворения информационных потребностей пользователей. БД – совокупность данных (файлов, документов), организованных специальным образом и описывающих определенную предметную область.

Виды БД 6 По модели данных: Реляционная (табличная) Иерархическая Сетевая Объектно-ориентированная Постреляционная Хронологическая (хранилище данных) По способу хранения данных: Локальная (централизованная) Распределенная Файл-серверная Клиент-серверная Двухзвенная Многозвенная

Виды БД 7 По виду хранимой информации: Фактографические БД – БД, хранящие информацию в виде данных- фактов (записи, файлы и т.д.). Данные отражают текущее состояние предметной области. Динамические БД - БД, хранящие данные и время вместе, отображая состояние предметной области в определенный момент времени. Документальные БД- БД, хранящие информацию в виде документов (отчетов, монографий и т.д.). Графические БД – БД, хранящие информацию в виде графических объектов (картинно-графические, база видеоданных, graphics based data base и др.). Интегрированные БД – БД, хранящие информацию в виде данных, документов, графических документов.

Основные средства реализации БД 8 1.Файловая система + собственный пользовательский сервис на языке высокого уровня. 2.СУБД - система управления базами данных, выполняющая все функции файловой системы, средства создания и ведения БД, средства манипулирования данными (добавление, удаление, изменение данных), средства поиска данных (поиск, выбор, сортировка). 3.Машины баз данных – программно-аппаратные средства реализации баз данных. Значительно быстрее СУБД, но менее универсальны.

Язык запросов СУБД 9 Это формальный язык для доступа и редактирования БД. СОЗДАТЬ ТАБЛИЦУ Расписание ( Номер_рейса Целое Дни_недели Текст (8) Пункт_отправления Текст (24) Время_вылета Время Пункт_назначения Текст (24) Время_прибытия Время Тип_самолета Текст (8) Стоимость_билета Валюта ); ВЫБРАТЬ Номер_рейса, Дни_недели, Время_вылета ИЗ ТАБЛИЦЫ Расписание ГДЕ Пункт_отправления = 'Москва И Пункт_назначения = 'Самара И Время_вылета > 17; Де-факто стандартный язык запросов к табличным БД – SQL.

Жизненный цикл БД 10 1.Проектирование 2.Реализация 3.Эксплуатация Основные этапы проектирования БД: 1.Определение требований к системе. 2.Инфологическое проектирование БД – получение семантических (смысловых) моделей данных. 3.Выбор СУБД и других инструментальных средств. 4.Логическое проектирование БД – организация данных в виде структур данных. 5.Физическое проектирование БД – выбор рациональной структуры хранения данных и методов доступа к ним.

Уровни представления данных 11 1.Концептуальный уровень - это семантический уровень представления данных в виде абстрактных понятий, учитывающих особенности предметной области. 2.Логический уровень - уровень представления в виде структуры данных (таблицы, списки, деревья, графы, и т.д.). 3.Физический уровень - способ организации данных на машинном носителе (в виде бит, байт и т.д. и их структур). Абстрактный уровень Конкретный уровень

Концептуальная (инфологическая) модель «сущность – связь» 12 Entity-Relation (ER-модель) была предложена Питером Ченом (P.Chen) в 1976г. Модель описывает: Сущности Атрибуты сущностей Связи между сущностями Модель может быть представлена в графическом (ER-диаграмма) или в текстовом виде. Существует большое число нотаций (способов изображения) ER- диаграмм.

Основные понятия 13 Сущность – любой различимый объект, реальный или мыслимый, (объект, который мы можем отличить от другого), информацию о котором необходимо хранить в базе данных объект предметной области. Обозначается существительным. Необходимо различать тип и экземпляр сущности. Тип сущности: ГОРОД Экземпляр сущности: МОСКВА, САМАРА, КАЛУГА Тип сущности: ФИРМА Экземпляр сущности: ОАО «Сбербанк», ООО «Луч», ИП Удальцов Город

Атрибут 14 Атрибут – поименованная характеристика сущности. Обозначается существительным. Наименование должно быть уникальным для конкретного типа сущности, но может быть одинаковым для различного типа сущностей. Город Название Координаты Население Фирма -Название -Адрес -Телефон -ФИО руководителя Гражданин ФИО паспорта Дата рождения Место рождения

Ключ 15 Атрибут (или атрибуты) однозначно идентифицирующий сущность называется ключом. На схеме ключевой атрибут подчеркивается, или рядом ставится значок ключа. Ключ, состоящий из нескольких атрибутов, называется составным. Товар -Наименование -Артикул -Производитель Книга -Название -Автор -Год -Издательство -Число страниц Составной ключ не надо путать с потенциальными ключами. Любой из потенциальных ключей может быть выбран как первичный. Остлаьные – альтернативные. Студент -ФИО - студенческого билета - паспорта (альтернативный ключ) (первичный ключ)

Связи 16 Связь – соответствие (отображение, ассоциация) между экземплярами сущности. Обозначается обычно глаголом или причастием, но не обязательно. ФирмаГород зарегистрирована в Гражданство СтранаЖитель Владеет ИмуществоСобственник

Типы (кратность) связей 17 1:1Один-к- одному В конкретный момент времени одному экземпляру сущности A соответствует 0 или 1 экземпляров сущности B, и наоборот Студент - Зачетная книжка 1:nОдин- ко- многим Одному экземпляру сущности А соответствуют 0, 1 или более экземпляров сущности В, но одному экземпляру B соответствует 0 или 1 экземпляр A Студент - Группа n:mМногие- ко- многим Каждому экземпляру сущности А соответствуют 0, 1 или более экземпляров сущности В, и каждому экземпляру B соответствует 0, 1 или более экземпляр A Студент - Преподаватель АвтомобильКолесо 14 Можно указать точную кратность связи или диапазон СуммаСлагаемые 12..n

Пример 18 В зависимости от ситуации, одни и те же сущности могут иметь разные типы связи. МужЖена Супруг Брак 11 МужЖена Брак 1n МужЖена Брак n1 МужЖена Брак nm 1 1 моногамия многоженство многомужие полигамия

Сложные связи 19 Множественные связи между одними и теми же сущностями Тернарные связи между тремя сущностями СотрудникФирма Работает в Руководит ВрачПациент Болезнь Диагноз Связи более высоких порядков – между 4-мя и более сущностями Петля между экземплярами одной сущности Аудиозапись Ремикс 1 m 1n m1 nm p

Класс принадлежности 20 Класс принадлежности сущности называется обязательным, если экземпляры данной сущности должны участвовать в связи обязательно. Обозначается закрашенной точкой на конце связи. Класс принадлежности сущности называется необязательным, если экземпляры данной сущности могут не участвовать в связи. Точка не ставится или ставится незакрашенный кружок. ПреподавательЛекция ПреподавательЛекция на практике трудности: обязательно одновременно создавать экземпляры обеих сущностей ЧеловекКошка

Ассоциативная сущность 21 Связь тоже может иметь атрибуты. Такая связь превращается в ассоциативную сущность. ФирмаГород Регистрация Адрес регистрации Дата регистрации

Зависимые сущности 22 Сущность Независимая (стрежневая) Зависимая ХарактеристическаяАссоциативная Зависимая сущность не может существовать без независимых (стрежневых) сущностей. КнигаАвтор Зависимые сущности иногда изображают со скругленными углами. ОтделСотрудникПредприятие

Соотношение основных понятий БД 23 Сущность?Таблица Экземпляр сущности ?Запись (строка) Атрибут?Поле (столбец) -?Тип данных КонцептуальноеРеляционноеФизическое Распространенные ошибки! На концептуальном уровне -не бывает бессмысленных атрибутов, не имеющих значения вне БД (код, ID). -не бывает сущностей без атрибутов (бывают связи без атрибутов) -не бывает связей без кратности

Обозначения в разных нотациях 24 Атрибут 1 Сущность 1 Ключ 1 -Ключ -Атрибут 2 -Атрибут 3 Сущность 2 -Ключ -Атрибут 2 -Атрибут 3 связана с Сущность 1Сущность 2 Связь Ключ 2 Атрибут 2 Атрибут 3 По Чену Упрощенный вариант Атрибут 4 1n Ассоциация -Атрибут 5 1 n Сущность 1 -Ключ -Атрибут 2 -Атрибут 3 Сущность 2 -Ключ -Атрибут 2 -Атрибут 3 n 1

Последовательность концептуального проектирования 25 1.Выделить сущности. 2.Определить связи между сущностями. 3.Определить тип связей. 4.Перечислить атрибуты каждой сущности. 5.Найти потенциальные ключи среди атрибутов. 6.Выбрать первичные ключи из потенциальных.

Лекция 2. Логическое проектирование реляционных БД 26

Основные понятия реляционной модели данных 27 Концепция реляционной модели предложена в 1970г. Коддом с целью обеспечения независимости представления и описания данных от прикладных программ. Схема отношения - список имен атрибутов отношения. Реляционная БД – это совокупность взаимосвязанных отношений. Отношение1 (атрибут1.1, атрибут1.2, атрибут1.3,... атрибут1.N) Отношение2 (атрибут2.1, атрибут2.2,... атрибут2.M) … Отношение описывает взаимосвязи между N атрибутами этого отношения. Количество атрибутов N – степень или «-арность» отношения (унарное, бинарное, тернарное, N-арное). Более точное определение будет дано в следующей лекции.

Правила перехода от ER-модели к предварительной схеме отношений 28 1.Каждая сущность преобразуется в отношение (таблицу). Исключение – см. правило 1 преобразования связей. 2.Каждый атрибут сущности преобразуется в атрибут отношения (поле, столбец таблицы). Примечание. Имена атрибутов и отношений могут отличаться от концептуальных, но должны иметь тот же смысл. Например, в БД могут быть запрещены русские буквы. 3.Первичный ключ становится ключом отношения. Правила для сущностей и атрибутов

Правила для связей 1: Если степень бинарной связи равна 1:1 и класс принадлежности обеих сущностей является обязательным, то требуется только одно отношение. Первичным ключом этого отношения может быть ключ любой из двух сущностей. 2.Если степень бинарной связи равна 1:1 и класс принадлежности одной сущности является обязательным, а другой – необязательным, то необходимо построение двух отношений. Под каждую сущность необходимо выделение одного отношения, при этом ключи сущностей должны служить первичными ключами для данных отношений. Ключ сущности с необязательным классом принадлежности добавляется в качестве атрибута в отношение, выделенное для сущности с обязательным классом принадлежности. 3.Если степень бинарной связи равна 1:1 и класс принадлежности ни одной сущности не является обязательным, то необходимо использовать три отношения: по одному для каждой сущности, ключи которых служат в качестве первичных в соответствующих отношениях, и одно для связи. Среди своих атрибутов отношение, выделяемое на связь, будет иметь по одному ключу от каждой сущности.

Примеры для связей 1:1 30 СтудентЗачетка 11 Студент (ФИО, Дата рождения, Группа, зачетки, Дата выдачи, Продлена до) -ФИО -Дата рождения -Группа - зачетки -Дата выдачи -Продлена до ГражданинПаспорт Гражданин (ФИО, Дата рождения, Место рождения, паспорта) Паспорт ( паспорта, Дата выдачи, Кем выдан) -ФИО -Дата рождения -Место рождения - паспорта -Дата выдачи -Кем выдан 11 МужчинаЖенщина Мужчина (ФИО, Дата рождения, Место рождения, паспорта) Женщина (ФИО, Дата рождения, Место рождения, паспорта) Брак ( паспорта мужа, паспорта жены, свидетельства о браке) -ФИО -Дата рождения - паспорта -ФИО -Дата рождения - паспорта 11 выдается имеет состоит в браке

Правила для связей 1:n 31 4.Если степень бинарной связи равна 1:n и класс принадлежности n- связной сущности является обязательным, то достаточным является использование двух отношений, по одному на каждую сущность. Ключ каждой сущности используется в качестве первичного ключа для соответствующего отношения. Ключ 1-связной сущности должен быть добавлен как атрибут в отношение для n-связной сущности. 5.Если степ. бин. связи 1:n и класс принад-ти n-связной сущн-ти – необ- ный => три отнош-я: 1 для кажд. сущн-ти и 1 отнош. для связи. Ключ каждой сущн-ти => первичный ключ соотв-щего отнош-я. Отнош-е для связи должно иметь среди своих атр-тов ключи от кажд. сущн-ти. Примечание Для связей 1:n нужны только 2 правила (4 и 5). Определяющим фактором является класс принадлежности n-связной сущности; класс принадлежности 1-связной сущности не влияет на конечный результат.

Примеры для связей 1:n 32 СтудентГруппа n1 - зачетки -ФИО -Дата рождения - группы -Курс Учится в Студент ( зачетки, ФИО, Дата рождения, Группа) Группа ( группы, Курс) ДепутатПартия n1 -ФИО -Дата рождения -Название Состоит в Депутат (ФИО, Дата рождения) Партия (Название) Партбилет ( билета, Партия, Депутат)

Правила для связей m:n 33 6.Если степень связи m:n, то для хранения данных необходимо три отношения: по одному для каждой сущности и одно отношение для связи. Ключ каждой сущности используется в качестве первичного ключа соответствующего отношения. Отношение для связи должно иметь в числе своих атрибутов ключ каждой сущности. КнигаЧитатель nm выдана -Название -Автор -Год -Издательство - читательского -ФИО Книга (Название, Автор, Год, Издательство) Читатель( читательского, ФИО) Выдача (Название, Автор, читательского, Дата выдачи)

Правила для множественных связей 34 7.В случае p-сторонней связи необходимо использовать (p+1) предварительных отношений, по одному для каждой сущности и одно для связи. Ключ каждой сущности должен служить в качестве первичного ключа для соответствующего отношения. Отношение, порождаемое связью, будет иметь среди своих атрибутов ключи от каждой сущности. ВрачПациент Болезнь Диагноз nm s Врач ( паспорта, ФИО, Должность) Пациент ( страх. свид., ФИО, Дата рождения) Болезнь (Название) Диагноз (Врач, Пациент, Болезнь) - паспорта -ФИО -Должность - страх. свид. -ФИО -Дата рождения -Название

Лекция 3. Реляционная алгебра 35

Соотношение основных понятий БД 36 СущностьОтношениеТаблица Экземпляр сущности КортежЗапись (строка) Атрибут Поле (столбец) -ДоменТип данных КонцептуальноеРеляционноеФизическое

Домен 37 Домен D – множество однотипных данных, имеющих общую семантику. Домен Тип данных Домен - более конкретное понятие. Два разных домена могут иметь одинаковый тип данных. Пример ДоменАтрибутТип данных МассаТовар.ОтгруженоВещественное ТемператураМеталл.Т_плавления Пациент.Температура Город.Т_воздуха Вещественное С точки зрения логики некорректно сравнивать данные из разных доменов. Атрибут – именованное подмножество домена.

Декартово произведение множеств 38 Декартово произведение n множеств D 1, D 2, … D n – это множество D 1 D 2 … D n (D 1 *D 2 *…*D n ), состоящее из элементов вида (d 1, d 2, … d n ), где d 1 D 1, d 2 D 2,... d n D n. Иными словами – множество, состоящее из всех сочетаний элементов исходных множеств. Пример A = {1, 2, 3} B = {x, y} A B = {(1, x), (2, x), (3, x), (1, y), (2, y), (3, y)}

Отношение 39 Отношение R – множество упорядоченных наборов данных (кортежей), являющихся подмножеством декартова произведения заданных доменов D 1 D 2 … D n (домены могут повторяться). Кортеж (d 1, d 2, … d n ) – элемент декартова произведения доменов, на котором строится отношение. Схема (заголовок) отношения – перечень имен атрибутов отношения (возможно, с указанием доменов). R1 (A 1, A 2, A 3, … A n ) Атрибуты отношения указываются через точку: R1.A1, R2.A10, Студент.зачетки Мощность отношения M – число кортежей. Степень отношения N – число атрибутов. Отношение имеет: фиксированное число и порядок атрибутов; конечное множество неупорядоченных кортежей; только различные кортежи (никогда не повторяются).

Ключ 40 R – отношение с атрибутами A 1, A 2,..., A n. Подмножество атрибутов K=(A i,A j,...,A k ) отношения R является потенциальным ключом т.т.т.к. удовлетворяются два независимых от времени условия: 1.Уникальность: никакие два различных кортежа R не имеют одного и того же значения для атрибутов A i, A j,..., A k. 2.Минимальность: ни один из атрибутов A i, A j,..., A k не может быть исключен из K без нарушения уникальности. Каждое отношение имеет хотя бы 1 возможный ключ – все атрибуты. Один любой потенциальный ключ выбирается в качестве первичного. Внешний ключ – атрибут или множество атрибутов, значения которого соответствуют первичному ключу некоторого отношения (другого или этого же).

Лекция 4. Реляционная алгебра (продолжение) 41

Операции над отношениями 42 Существует 4 группы операций над отношениями: 1.Операции обновления (все языки). 2.Реляционная алгебра (ISBL). 3.Реляционное исчисление с переменными-кортежами (QUEL). с переменными-доменами (QBE). SQL

Операции обновления 43 Наиболее простые операции, не изменяющие схему отношения, но изменяющие его кортежи: Добавление кортежа ADD (R; A 1 = a 1, A 2 = a 2,… A n = a n ) ADD (R; a 1, a 2, a 3,…a n ) ADD (Автор; Пушкин, Александр, Сергеевич, , bio_PushkinAS.doc) Удаление кортежа DEL(R; a i, a j, …a k ), где (a i, a j, …a k ) K DEL(Читатель; билета = ) Изменение кортежа CH (R; a i, a j,… a k ; A r = e r, A s = e s, … A q = e q ) CH (R; a i, a j,… a k ; e 1, e 2, … e n ) где (a i, a j, …a k ) K, e 1, e 2, … e n – новые значения атрибутов CH (Издательство; Полиграфия; Телефон = )

Ошибки операций обновления 44 Ошибки при добавлении и изменении: 1.Добавляемый кортеж не соответствует схеме отношения. 2.Значения кортежа не принадлежат соответствующим доменам. 3.Добавляемый кортеж совпадает по ключу с кортежем, уже находящимся в отношении. При удалении кортежей ошибок не возникает. Если в операции удаления указано несуществующее значение, отношение не будет изменено.

Операции реляционной алгебры 45 Предложена Э. Коддом в 1972г. Выполняются над отношениями: из 1-го или нескольких исходных отношений получается новое отношение (свойство замкнутости). Отношение – это множество => для него возможны все операции, выполняемые над множествами. Кроме того, -ют специф. операции для отношений. Реляционная алгебра и реляционное исчисление эквивалентны по своей выразительной силе. Существуют правила преобразования запросов между ними. Но не все возможные действия с таблицами можно выразить через реляционную алгебру.

Объединение (UNION) 46 Объединение отношений R1 R2 – отношение, которое содержит кортежи из R1 и R2. Возможно только для отношений с одинаковой схемой (совместимыми отношениями). Пример R1 Отечественная литература АвторКол-во книг Пушкин121 Толстой220 Зощенко45 Гете19 R1 R2 АвторКол-во книг Пушкин121 Толстой220 Зощенко45 АвторКол-во книг Гете19 Пушкин121 Толстой220 R2 Литература 19в.

Пересечение (INTERSECTION) 47 Пересечение отношений R1 R2 – отношение, которое содержит кортежи, которые присутствуют и в R1, и в R2. Только над совместимыми отношениями! Пример АвторКол-во книг Пушкин121 Толстой220 Зощенко45 R1 Отечественная литература АвторКол-во книг Гете19 Пушкин121 Толстой220 R2 Литература 19в. АвторКол-во книг Пушкин121 Толстой220 R1 R2

Разность (SET DIFFERENCE) 48 Разность отношений R1 \ R2 – отношение, которое содержит кортежи, которые присутствуют в R1, но отсутствуют в R2. Только над совместимыми отношениями! Пример АвторКол-во книг Пушкин121 Толстой220 Зощенко45 R1 Отечественная литература АвторКол-во книг Гете19 Пушкин121 Толстой220 R2 Литература 19в. АвторКол-во книг Зощенко45 R1 \ R2R1 \ R2

Декартово произведение 49 Выполняется над любыми отношениями по определению декартова произведения. Пример АвторКол-во книг Пушкин121 Толстой220 Зощенко45 R1 Отечественная литература Фамилиябилета Иванов Петрова R2 Читатель R1 R2 АвторКол-во книгФамилиябилета Пушкин121Иванов Толстой220Иванов Зощенко45Иванов Пушкин121Петрова Толстой220Петрова Зощенко45Петрова

Выборка (SELECTION) 50 Унарная операция. Из отношения R1 выбираются кортежи, для которых выполняется заданное условие F. R = R1 WHERE F Пример R1 Выданные книги за май 2014 АвторДата выдачиЧитательбилета Пушкин Иванов Зощенко Петрова Гете Иванов Пушкин Петрова Пушкин Петрова R = R1 WHERE (Автор = Пушкин) AND (Дата выдачи > ) АвторДата выдачиЧитательбилета Пушкин Петрова Пушкин Петрова

Проекция (PROJECTION) 51 Унарная операция. Из отношения R1 выбираются данные для выбранных атрибутов i 1,i 2,..i r, и из результата удаляются повторяющиеся кортежи. Пример R1 Выданные книги за май 2014 АвторДата выдачиФамилиябилета Пушкин Иванов Зощенко Петрова Гете Иванов Пушкин Петрова Пушкин Петрова АвторФамилия ПушкинИванов ПушкинПетрова ЗощенкоПетрова ГетеИванов

Соединение (JOIN) 52 Бинарная операция по атрибутам с совпадающим доменом (из каждого отношения). Результат – подмножество декартова произведения отношений R1*R2, в котором выполняются ограничения на атрибуты этих отношений. Т.е. соединение = декартово произведение + выборка. Варианты соединения: -тета-соединение -эквисоединение -естественное соединение -внешнее соединение -полусоединение

-соединение 53 Содержит кортежи декартова произведения R1*R2, удовлетворяющие условию F: R1 F R2 Причем условие F имеет вид F = R1.A i R2.A j, - оператор сравнения (=,,,) Пример ФамилияДата_рождения Пушкин Толстой Зощенко R1 Автор АвторНазваниеГод ПушкинК другу стихотворцу1814 ПушкинМетель1830 ТолстойАнна Каренина1876 ГетеФауст1831 R2 Произведение Произведения, написанные авторами после 30 лет R1 F R2, F = (Автор.Фамилия = Произведение.Автор) & (Год(Автор.Дата_рождения)) + 30 > Произведение.Год) ФамилияДата рожденияАвторНазваниеГод Пушкин ПушкинМетель1830 Толстой ТолстойАнна Каренина1876

Эквисоединение 54 Частный случай -соединения с условием на равенство. Пример ЧитательНазвание Любимое Пушкин Для работы R1 Подборка Фамилиябилета Иванов Петрова R2 Читатель Подборки читателей, R1 Подборка.Читатель=Читатель.билета R2 Подборка_ билета НазваниеФамилияЧитатель_ билета ЛюбимоеИванов ПушкинПетрова Для работыПетрова

Естественное (внутреннее) соединение 55 Эквисоединение по совпадающим атрибутам, причем в результат включается только 1 экземпляр атрибутов, по которым ведется соединение. R1 R2 билетаНазвание Любимое Пушкин Для работы R1 Подборка Фамилиябилета Иванов Петрова Кузнецов R2 Читатель Подборки читателей, R1 R2 билетаНазваниеФамилия ЛюбимоеИванов ПушкинПетрова Для работыПетрова Пример

Внешнее соединение 56 Соединение по атрибутам, у которых могут быть несовпадающие значения. Может быть левым, правым и полным. Левое соединение: кортежи R1, у которых нет пары в R2, также войдут в соединение: R1 R2 Правое соединение: кортежи R2, у которых нет пары в R1, также войдут в соединение: R1 R2 Полное внешнее соединение: в результирующее отношение включаются все кортежи, у которых нет пары в R1: R1 R2 Во всех случая для отсутствующих пар используется значение NULL.

Внешнее левое соединение (пример) 57 R1 Произведение КодНазваниеГод 1Код да Винчи2003 2А.С.Пушкин, полное собрание сочинений, т Русская классика1999 R2 Издание КодНазваниеГод 1К другу стихотворцу1814 2Метель1830 3Анна Каренина1876 4Код да Винчи2003 Код_ Произведения Код_ Издания R3 Издание_произведения

Внешнее левое соединение (пример) 58 Код_ Произвед ения Название_ Произведения Год_ Произведения Код_ Издания Название_ Издания Год_ Издания 1К другу стихотворцу 1814NULL 2Метель1830NULL 3Анна Каренина1876NULL 4Код да Винчи20031Код да Винчи2003 R1 F (R2 R3), F = (Произведение.Год = Издание.Год) У каких произведений имеется первое издание?

Полусоединение 59 Содержит только кортежи из отношения R1, такие, которые входят в соединение R1 и R2: R1 F R2 Пример ФамилияДата_рождения Пушкин Толстой Зощенко Гёте R1 Автор АвторНазваниеГод ПушкинК другу стихотворцу1814 ПушкинМетель1830 ТолстойАнна Каренина1876 ГетеФауст1831 R2 Произведение Авторы, произведения которых есть в библиотеке R1 F R2, F = (Автор.Фамилия = Произведение.Автор) ФамилияДата рождения Пушкин Толстой Гёте

Переименование (RENAME) 60 В результате применения операции переименования получаем новое отношение, с измененными именами атрибутов. R RENAME A1, A2, … AS B1, B2, … Пример Произведение RENAME Код, Название, Год AS Код_произведения, Название_произведения, Год_произведения Произведение (Код, Название, Год)

Лекция 5. Нормализация отношений 61

Пример плохо нормализованного отношения 62 билета ФИОДата выдачи Форм уляр Название книги АвторИздатель ство Год Иванов А.Е Ф053Понедельник начинается в субботу А. Стругаций, Б. Стругацкий АСТ Смирнова П.А У121Самоучитель испанского -Mochaon Корнеев И.С К245ФаустИ.В. ГётеХуд. литерату ра Жуков С.И К245ФаустГетеХуд. литерату ра Иванов А.Е Ф054Пикник на обочине Б. Стругаций, А. Стругацкий АСТ1999 …

Пример 2 63 БлюдоВидПор ций ПродуктКалори йность Вес (г) ПоставщикГородСтранаВес (кг) Цена ($) Дата пост. ЛобиоЗакуска158Фасоль "Хуанхэ"ПекинКитай /8/94 ЛобиоЗакуска108Лук45040"Наталка"КиевУкраина /8/94 ЛобиоЗакуска108Масло742030"Лайма"РигаЛатвия /8/94 ЛобиоЗакуска108Зелень18010"Даугава"РигаЛатвия /8/94 ХарчоСуп144Мясо166080"Наталка"КиевУкраина /8/94 ХарчоСуп144Лук45030"Наталка"КиевУкраина /8/94 ХарчоСуп144Томаты24040"Полесье"КиевУкраина /8/94 ХарчоСуп144Рис334050"Хуанхэ"ПекинКитай /8/94 ХарчоСуп144Масло742015"Полесье"КиевУкраина /8/94 ХарчоСуп144Зелень18015"Наталка"КиевУкраина /8/94 ШашлыкГорячее207Мясо "Юрмала"РигаЛатвия /8/94 ШашлыкГорячее207Лук45040"Полесье"КиевУкраина /8/94 ШашлыкГорячее207Томаты240100"Полесье"КиевУкраина /8/94 ШашлыкГорячее207Зелень18020"Даугава"РигаЛатвия /8/94 КофеДесерт235Кофе27508"Хуанхэ"ПекинКитай /8/94

Цели и принципы нормализации размер отношений (исключить дублирование информации). 2.Не должно возникать трудностей при выполнении операций добавления, удаления, редактирования. 3.Min разброс времени ответа на запросы в БД. Для этого отношения приводят к нормальным формам (НФ): 1НФ, 2НФ, 3НФ, НФБК, 4НФ, 5НФ. Нормализация отношений это итерационный обратимый процесс декомпозиции начального отношения на несколько более простых отношений. Аппарат нормализации отношений также предложен Э.Коддом.

Функциональная зависимость 65 Функциональные зависимости отражают смысловые связи между атрибутами отношения. Атрибут В функционально зависит от атрибута А, если для каждого значения А существует не более одного, связанного с ним, значения В (если знаем A, то точно знаем B). А В А и В могут быть составными атрибутами. Если А В и В А, то А и В взаимозависимые атрибуты А В зачетки паспорта группы, по_списку Фамилия_студента Артикул Цена Фамилия Имя

Функциональная зависимость 66 Атрибут В функционально полно зависит от атрибута А, если А = А 1 А 2 …А к и А 1 А 2 …А к В, то есть В функционально зависит от всей группы атрибутов А Автор.Фамилия, Автор.Имя, Автор.Отчество Автор.Дата_рождения Атрибут В частично зависит от атрибута А, если А = А 1 А 2 …А к и А i …А j В и i, j < k то есть В функционально зависит от некоторых из атрибутов А Издательство.Название, Издательство.Телефон Издательство.Адрес Атрибут С транзитивно зависит от А, если А В и B C, но С B или A C паспорта Дата_рождения Дата_рождения Знак_зодиака Непустое подмножество атрибутов, от которого функционально полно зависят все остальные атрибуты отношения является потенциальным ключом.

1 нормальная форма (1НФ) 67 Отношение находится в 1НФ если значения всех его атрибутов атомарны (неделимы). Сотрудник (Табельный, ФИО, Дата_рождения, Телефон, Адрес, История_работы, Дети) Книга (Название, Авторы, Произведения, Издательство, Год, Число_страниц) Примеры отношений не в 1НФ:

Приведение отношения к 1НФ 68 a)Разбиение неатомарных (сложных) атрибутов на атомарные. Адрес => Индекс, Город, Улица, Дом, Корпус, Квартира или Индекс, Город, Местн_адрес b)Выделение сложных атрибутов в новые отношения. Дети => Ребенок(Сотрудник, Имя, Дата_рождения)

2 нормальная форма (2НФ) 69 Отношение находится во 2НФ, если оно находится в 1НФ и каждый неключевой атрибут функционально полно зависит от ключа. Проверка: если есть частичная зависимость от ключа – не в 2НФ. Пример Поставка (Поставщик, Товар, Цена) ПоставщикТоварЦена ООО «Василек»Мыло2000 ООО «Василек»Сахар2500 ИП ПетровМыло2000 MyMilk Ltd.Сыр20000 ИП ПетровСыр20000 Товар Цена Примечание В зависимости от конкретных условий, каждый пример может быть или не быть в определенной НФ. Поставщик, Товар Цена Поставщик Цена/ (частичная зависимость от ключа => не 2НФ)

Аномалии 2НФ 70 Аномалия включения: Если появился новый товар для поставки, его можно будет добавить в БД только когда начнутся фактические поставки. Аномалия удаления: Если прекратились поставки товара, и из базы удалены все соотв. записи, то сведения о товаре будут потеряны. Аномалия обновления: Если изменилась цена товара, то придется вручную найти все поставки этого товара и изменить цену. Если этого не сделать, БД будет несогласованной.

Приведение отношения ко 2НФ 71 1.Найти все частичные зависимости неключевых атрибутов от каждого составного потенциального ключа (кроме всего кортежа). 2.Если составных потенциальных ключей нет, то отношение во 2НФ. 3.Каждую найденную зависимость выделить в новое отношение (проекция). Включить в отношение зависимые атрибуты, и ту часть ключа от которой они зависят. Новое отношение будет связано со старым как 1:n. 4.Проверить новое отношение на 2НФ. Поставка (Поставщик, Товар, Цена) Поставка Поставщик Товар Название Цена n 1

3 нормальная форма (3НФ) 72 Отношение находится в 3НФ, если оно находится в 2НФ и отсутствуют транзитивные зависимости неключевых атрибутов от ключа. ФирмаСкладОбъем МЕНАТЕПМКЧ-1200 ИКСДП600 АСКОДП600 ПАРУССК Фирма Склад; Склад Объем Объем Фирма; Склад Фирма; Объем Склад / / / Пример Хранение (Фирма, Склад, Объем) Транзитивная зависимость от ключа => не в 3НФ

Аномалии 3НФ 73 Аномалия включения Если нет фирмы, получающей товар со склада, то добавление склада невозможно. Аномалия удаления Вместе с удалением сведений о фирме будет удалена информация и о складе, если никакая другая фирма на этом складе не хранит товар. Аномалия обновления Если объем склада изменился, то надо просмотреть всё отношение и изменить все кортежи для фирм, связанных с этим складом, иначе БД будет несогласованной.

Приведение отношения к 3НФ 74 1.Найти все транзитивные зависимости неключевых атрибутов от каждого потенциального ключа (кроме всего кортежа). 2.Для транзитивной зависимости нужно еще 2 атрибута, кроме ключа. Если атрибутов меньше, то транзитивной зависимости нет. 3.Каждую найденную зависимость выделить в новое отношение (проекция). Включить в отношение транзитивно зависимые атрибуты, и тот атрибут, через который они зависят от ключа. Новое отношение будет связано со старым как 1:n. 4.Проверить новое отношение на 3НФ. Хранение (Фирма, Склад, Объем) Хранение Фирма Склад Название Объем n 1

Нормальная форма Бойса-Кодда (НФБК) 75 Отношение находится в НФБК, если оно находится в 3НФ и в нем нет зависимостей части ключевых атрибутов от неключевых. Ситуация может возникнуть при наличии нескольких потенциальных ключей, которые имеют общие атрибуты. КвартираДатаПлательщик Дмитриева Орлов Степнов Степнова Дмитриева Пример Платеж (Квартира, Дата, Плательщик) Ключи: (Квартира, Дата), (Дата, Плательщик) 2НФ: неключевых атрибутов нет => в 2НФ 3НФ: в 3НФ НФБК: Квартира, Дата Плательщик, Плательщик Квартира => не в НФБК Плательщик – один из жильцов квартиры.

Аномалии НФБК 76 Аномалия включения Нельзя внести сведения о проживании плательщика в квартире, пока он не совершит первый платеж. Аномалия удаления При удалении сведений о старых платежах, сведения о плательщиках также будут удалены. Аномалия обновления Можно указать «неправильного» плательщика, не проживающего в квартире.

Приведение отношения к НФБК 77 1.Выписать все потенциальные составные ключи. 2.Найти все зависимости частей каждого потенциального ключа от неключевых атрибутов. 3.Для каждой зависимости выделить отношение. Проживание Квартира Проживающий Платеж Плательщик Дата n1 Платеж (Квартира, Дата, Плательщик)

Лекции

Неформальное толкование НФ 79 1НФ: атрибуты отношений должны быть простыми, короткими и контролируемыми. 2НФ: неключевые атрибуты должны зависеть от всего ключа целиком, а не от его части. 3НФ: неключевые атрибуты должны зависеть только от ключа, а не друг от друга. НФБК: все атрибуты, в том числе части ключа, зависят только от ключа и ни от чего более.

Общая схема проверки на НФБК 80 Для каждого отношения: 1НФ: проверить атомарность каждого атрибута. Выписать в виде таблицы атрибуты и их домены. Выписать все потенциальные ключи и неключевые атрибуты. 2НФ: Если есть составные ключи, проверить зависимость неключевых атрибутов от части ключа. 3НФ: Если неключевых атрибутов больше 2, проверить наличие транзитивных зависимостей. НФБК: Если есть составные ключи, проверить зависимость части ключа от атрибутов, не являющихся ключом.

Лекция 8. Введение в SQL 81

SQL (Structured Query Language) 82 Structured Query Language язык структурированных запросов. Непроцедурный язык – в отличие от ЯП, команды SQL показывают что хочет получить пользователь, но не показывают как это получить. Существуют различные диалекты и расширения SQL (различных производителей): MySQL TransactSQL (Sybase, MS SQL Server) Jet SQL (Access SQL) PL/SQL (Oracle) и др. Стандарты SQL (за различные годы): SQL-89 SQL-92 (ANSI SQL, SQL 2) SQL:1999 (SQL 3) SQL:2003 SQL:2006 SQL:2008

Примеры SQL-запросов 83 CREATE TABLE Подборка (Читатель CHAR(8) DEFAULT , Название VARCHAR(20) DEFAULT '', Код_издания INT DEFAULT 0) INSERT INTO TABLE Подборка VALUES ( , Любимые книги', ) INSERT INTO TABLE Подборка DEFAULT VALUES SELECT Название FROM Подборка WHERE Читатель = '

Рекомендуемые источники 84 М. Флёнов. Transact SQL Очень подробный справочник С. Моисеенко. SQL. Задачи и решения. Учебник. sql-tutorial.ru Много примеров, типовые ошибки (Transact SQL) Упражнения по SQL sql-ex.ru Упражнения с онлайн-проверкой на готовых примерах БД (Transact SQL) А.В. Маркин. Построение запросов и программирование на SQL Учебник по серверу Firebird

Типы команд SQL 85 Определение структур базы данных (Data Definition Language, DDL) Создание, изменение и удаление объектов БД (таблиц). Манипулирование данными (Data Manipulation Language, DML) Добавление, удаление, изменение кортежей. Выборка данных (Data Query Language, DQL) Команда SELECT с многочисленными опциями и расширениями. Язык управления данными (Data Control Language, DCL) Команды создания объектов, связанных с доступом к данным, и контроля над распределением привилегий между пользователями. Команды администрирования данных Контроль за выполняемыми действиями и анализ операций БД. Администрирование данных < администрирование БД Команды управления транзакциями Подтверждение, сохранение, откат действий в БД (транзакций).

Форма Бэкуса-Наура (расширенная) 86 СимволОбозначение ::=«по определению», «это» |«или» выбор одного из нескольких приведенных значений описание структуры языка […]факультативный элемент (необязательная часть), конструкция в скобках может присутствовать или отсутствовать {…}множественный элемент, конструкция внутри скобок повторяется несколько раз (0 или более) {/…/}{/…/}множественный элемент, повторяется 1 или более раз (...)для ограничения альтернативных конструкций

Примеры 87 поезд ::= локомотив вагоны

Основные элементы языка 88 ЭлементОпределениеПример операторКлючевое слово, команда SQL. Нечувствительны к регистру, но традиционно пишутся заглавными буквами. SELECT AS + - * / > < = LIKE BETWEEN идентификаторИмя объекта БД (таблицы, поля и др.). В Access можно использовать почти любые символы кроме. ! [] Традиционно пишутся маленькими буквами. Клиенты.[Номер Телефона] константаНеизменяемое значение: Число Дата/время Текст (ANSI SQL в, Access в ) Булевы (TRUE|FALSE) NULL 42.5 Иванов TRUE выражениесочетания других элементов для вычисления нового значения 0,1 * Цена >=[Кол-во]

Типы данных SQL 89 Тип данныхОбъявления СимвольныеCHAR | VARCHAR ЧисловыеINTEGER | SMALLINT | NUMERIC | DECIMAL | FLOAT | REAL | DOUBLE КалендарныеDATE | TIME | TIMESTAMP | INTERVAL Двоичные данныеBIT | BIT VARYING Тип данныхОбъявления ЛогическийBOOLEAN | BOOL | YESNO СчетчикCOUNTER | AUTOINCREMENT Внедренное изображение IMAGE ДенежныйMONEY | CURRENCY Особые типы Access (и др. диалекты)

Символьные данные 90 CHAR (n) – фиксированная длина, пустое пространство заполняется пробелами VARCHAR (n) – переменная длина Access: TEXT(n) = CHAR(n) ::= [CHAR | VARCHAR] [( )]

Числовые данные 91 SMALLINT2 байта или UNSIGNED – без знака INTEGER | INT4 байта-2,14 млн... 2,14 млн. или 0.. 4,24 млн. NUMERIC | DECIMAL | DEC (M, D) M + 2 байта Зависит от M, DM – общее число знаков (считая зпт), D – знаков после запятой Пример: DECIMAL (5,2) – от -99,99 до 99,99. FLOAT | REAL4 байта+(-) 1.18* (-) 3. 40*10 38 UNSIGNED не влияет на диапазон значений DOUBLE PRECISION 8 байт+(-) 2.23* (-) 1.80 * Access: BIT, TINYINT, SMALLINT, INT, BIGINT, DECIMAL, REAL, FLOAT

Календарные типы 92 DATE – дата, включая YEAR (год), MONTH (месяц) и DAY (день) TIME – время, включая HOUR (часы), MINUTE (минуты) и SECOND (секунды) TIMESTAMP – для совместного хранения даты и времени + наносекунды. INTERVAL период времени. Access: DATETIME – для совместного хранения даты и времени, дней с INTERVAL не поддерживается

Определение доменов 93 ::= CREATE DOMAIN [AS] [ DEFAULT ] [ CHECK ( )] Домен в SQL определяет все потенциальные значения, которые могут быть присвоены атрибуту. ::= DROP DOMAIN [ RESTRICT | CASCADE ] RESTRICT – если есть поля этого домена, то удаление не выполнится и возникнет ошибка CASCADE – если есть поля этого домена, то заменить на соответствующий тип данных (по умолч.) - условия на значение VALUE CREATE DOMAIN PhoneN AS CHAR(10) DEFAULT ' ' CREATE DOMAIN Vozrast SMALLINT UNSIGNED DEFAULT 0 CHECK (VALUE < 150) DROP DOMAIN Vozrast RESTRICT

Лекции Запрос SELECT Язык выборки данных 94

Запрос SELECT 95 Наиболее часто используемый и сложный запрос с большим количеством опций. Реализует операции: -проекция -выборка -переименование -Декартово произведение -соединения различных видов -сортировка -Выборка Различают запросы: -однотабличные -многотабличные -вложенные

Полный синтаксис запроса SELECT 96 SELECT [DISTINCT | ALL] [FIRST m] [SKIP n] * | FROM [WHERE ] [GROUP BY {, }] [HAVING ] [ORDER BY {, }] [ROWS k [TO r ]] Порядок записи менять нельзя!

Обязательная часть запроса 97 SELECT * | FROM * - все поля ::= {, } ::= [.] | ::= } SELECT [DISTINCT | ALL] * | FROM DISTINCT – удалить повторяющиеся записи ALL – показать все (по умолч.) Соответствует операции проекции: Но по умолчанию повторы из списка не удаляются, это нужно указывать явно: Записи из разных таблиц соединяются через де >{, картово произведение. SELECT DISTINCT ГодFROM Произведение SELECT НазваниеFROM Произведение Примеры: Вывести перечень названий всех произведений. За какие годы имеются произведения?

Переименование 98 ::= [.] | [AS ] ::= [ ] Переименовывать можно и поля, и таблицы SELECT Объем AS Число_страниц FROM Издание Книга В результате получим таблицу Книга со столбцом Число_страниц SELECT ФИО, (YEAR(NOW())- YEAR(Дата_рождения)) AS Возраст FROM Читатель Часто используется для вычисляемых полей

Отбор строк 99 Если результат запроса очень большой, имеет смысл выводить не все строки, а только часть («постраничный» вывод). Способ 1 SELECT [FIRST m] [SKIP n] FROM m – сколько строк вывести n – сколько строк пропустить SELECT FROM [ROWS k [TO r ]] k – начиная с какой строки r > k – по какую строку (включительно) Способ 2 (приоритетный) SELECT FIRST 25 SKIP 49 Фамилия, Имя, Отчество FROM Автор Пример SELECT Фамилия, Имя, Отчество FROM Автор ROWS 50 TO 74 SELECT FIRST 0... Результат всегда пустой

Сортировка 100 SELECT FROM [ORDER BY {, }] ::= [DESC] DESC – в обратном порядке (от Я до А, от большего к меньшему, от новых к старым) Порядок сортировки задается порядком перечня элементов SELECT ФИО, YEAR(Дата_рождения) AS Год_рождения FROM Читатель ORDER BY Год_рождения DESC, ФИО Сначала сортировка по возрасту (от молодых к старым, т.е. от большего года рождения к меньшему), потом по алфавиту по ФИО

Запрос на выборку 101 SELECT * | FROM [WHERE ] - логическое выражение, равное TRUE/FALSE Операторы сравнения: >= Сравнение строк – по алфавиту TRUE > FALSE Логические операторы (в порядке приоритета): NOTANDOR (NOT a > b) AND b < c a

Оператор BETWEEN 102 [NOT] BETWEEN AND Начальное и конечное значения могут быть вычисляемыми. ANSI SQL: начальное значение

Оператор LIKE 103 [NOT] LIKE [ESCAPE ] ANSI SQLAccess SQL _? Заменяет 1 любой символ %* Заменяет любую последовательность символов ESCAPE указывает символ, «гасящий» подстановочные символы Трафаретные (подстановочные) символы Издание.Название LIKE "/*" ESCAPE "/" Автор.Фамилия NOT LIKE 'Т%' Издательство.Телефон LIKE '846333____' Поиск издания, название которого содержит звездочку Автор.Фамилия NOT LIKE "Т*" Издательство.Телефон LIKE "846333????"

Оператор IN 104 [NOT] IN ( )| ( {, }) CREATE DOMAIN Gender CHAR(1) CHECK (VALUE IN ('м','ж')) SELECT Название FROM Издательство WHERE (Город IN ('Москва','Санкт-Петербург'))

Подзапросы (вложенные запросы) 105 Вложенный запрос используется в условии другого запроса. Он обязательно содержит только 1 столбец. Чаще всего применяется для оператора IN, но могут быть и другие варианты. SELECT НазваниеFROM Издательство WHERE (Город IN ( SELECT НазваниеFROM Город WHERE Страна = Германия ) SELECT НазваниеFROM Произведение WHERE (Год < ( SELECT YEAR(Дата_Рождения) [AS Год_рождения] FROM Автор WHERE Код = ( SELECT Код_автораFROM Автор_произведения WHERE Код_произведения = ( SELECT КодFROM Произведение WHERE Название = 'Муму' ))) Число уровней вложенности может быть разным, но рекомендуется не более 3.

Группировка 106 SELECT FROM [GROUP BY {, }] SELECT Издательство, MAX(Год) FROM Издание GROUP BY Издательство Агрегатные (статистические) функции : COUNTКоличество значений SUMСумма значений AVGСреднее MAXМаксимум MINМинимум STDEVСтандартное отклонение VARДисперсия FIRSTПервая запись LASTПоследняя запись

Условия на результаты группировки 107 SELECT FROM [GROUP BY {, }] [HAVING ] SELECT Категория, AVG(Цена) AS Средняя_цена FROM Товар GROUP BY Категория HAVING (Средняя_цена > 100) AND (COUNT(Цена) >= 10) Условие WHERE проверяется для полей до группировки. Для проверки условий после группировки используется оператор HAVING. Агрегатные функции можно использовать только в HAVING. Вывести товарные категории, в которых средняя цена товара больше 100, причем в категории должно быть не менее 10 товаров.

Соединение таблиц с помощью WHERE (неявное соединение) 108 SELECT Автор.Фамилия, Автор.Имя, Автор.Отчество, Произведение.Название FROM Автор, Произведение, Автор_произведения WHERE (Автор.Код = Автор_произведения.Код_автора) AND (Произведение.Код = Автор_произведения.Код_произведения) Порядок действий СУБД: 1.Выбрать все записи исходных таблиц. 2.Создать их декартово произведение. 3.Из декартова произведения отобрать записи по условию. 4.Выполняется проекция по заданным столбцам. Это долго и декартово произведение занимает много места в памяти. Правильнее сразу отбирать из таблиц только нужные (связанные) записи.

Явное соединение. Оператор JOIN 109 FROM { [ ON | USING ( ) ] } { ::= CROSS JOIN | ([NATURAL] [[INNER] | (LEFT | RIGHT | FULL [OUTER])}] JOIN) CROSS JOIN – декартово произведение, аналогично записи через запятую NATURAL JOIN – естественное соединение (по связанным полям) (не поддерживается в Access) [INNER] JOIN – внутреннее соединение LEFT [OUTER] JOIN – левое соединение (все строки таблицы слева и соответствующие им из таблицы справа, если они есть, иначе NULL) RIGHT [OUTER] JOIN – правое соединение (наоборот) FULL [OUTER] JOIN – полное соединение (все строки обеих таблиц) ON – проверка условия для значений столбцов USING – можно использовать для одноименных столбцов

Примеры соединений 110 SELECT DISTINCT Читатель.ФИО, Издание.Название FROM Читатель LEFT JOIN Выдача ON Читатель.билета = Выдача.Читатель INNER JOIN Издание ON Издание.Код = Выдача.Код_издания ORDER BY Читатель.ФИО Какие издания брали все читатели за все время? SELECT Автор.Фамилия, Автор.Имя, Автор.Отчество FROM (SELECT Дата_рождения FROM Автор WHERE Фамилия LIKE 'Булгаков') AS Булгаков INNER JOIN Автор ON Булгаков.Дата_рождения < Автор. Дата_рождения Какие авторы родились позже Булгакова?

Объединение результатов запросов 111 { UNION [{DISTINCT | ALL}] } [ORDER BY {, }] "Склеивает" результаты двух запросов с одинаковой структурой. Исходные запросы формируются независимо друг от друга и не сортируются. SELECT Название FROM Категория UNION SELECT Название FROM Подборка DISTINCT – удалить дубликаты (по умолч.) ALL – показать все SELECT ФИО FROM Читатель WHERE Срок_действия_билета < NOW() UNION ALL SELECT ФИО FROM Читатель WHERE Срок_выдачи_билета > NOW()

UNION для условия на значение 112 (SELECT билета, ФИО, 'просрочен' AS Билет FROM Читатель WHERE Срок_действия_билета < NOW()) UNION (SELECT билета, ФИО, 'действителен' AS Билет FROM Читатель WHERE Срок_действия_билета >= NOW()) ORDER BY Билет DESC Используя UNION для одной таблицы и запросы с вычисляемым полем, можно выводить различные значения вычисляемого поля для различных условий. (SELECT Товар, Цена*Кол-во AS Стоимость, 0,1*Стоимость AS Скидка FROM Покупка WHERE Стоимость >= 2000) UNION (SELECT Товар, Цена*Кол-во AS Стоимость, 0,05*Стоимость AS Скидка FROM Покупка WHERE (Стоимость >= 1000) AND (Стоимость < 2000)) UNION (SELECT Товар, Цена*Кол-во AS Стоимость, 0 AS Скидка FROM Покупка WHERE Стоимость < 1000)

Пересечение и разность 113 { INTERSECT [{DISTINCT | ALL}] } { EXCEPT [{DISTINCT | ALL}] } Аналогично UNION. Пересечение: Разность: Примеры: SELECT Название FROM Произведение INTERSECT SELECT Название FROM Издание SELECT Название FROM Издательство EXCEPT SELECT Название FROM Издательство WHERE Город IN ( SELECT Название FROM Город WHERE Страна = 'Россия )

Лекция 13. Язык определения данных (DDL) 114

Создание таблиц 115 CREATE TABLE ( {, }) [ ] ::= [NOT NULL] [DEFAULT ] [ ] ::= [(размер)] | Особенности зависят от диалекта. Access не поддерживает домены. CREATE TABLE Произведение (Код COUNTER, Название CHAR(150), Год INTEGER) NOT NULL – для данного столбца запрещены пустые значения. Обязательно указывать для ключевых полей. Пример:

Ограничения на значение поля или таблицу 116 ::= [CONSTRAINT ] { UNIQUE | PRIMARY KEY [( )] | CHECK ( ) | FOREIGN KEY ( ) | REFERENCES [( )] [ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}] [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]} Имя_ограничения желательно задавать, чтобы потом его можно было изменить или удалить. список_столбцов и FOREIGN KEY задаются только для таблицы в целом. UNIQUE – запрет на повторяющиеся значения. PRIMARY KEY – первичный ключ. REFERENCES – для внешнего ключа: с каким полем какой таблицы связано. ON DELETE | ON UPDATE – при удалении|обновлении (для первичного ключа). В ограничениях на поле можно задать только простые ключи, составные задаются через ограничение таблицы.

Примеры использования ограничений 117 CREATE TABLE Произведение( Код COUNTER CONSTRAINT Код_значение NOT NULL PRIMARY KEY ON DELETE NO ACTION ON UPDATE CASCADE, Название CHAR(150), Год INTEGER CONSTRAINT Год_значение CHECK (VALUE BETWEEN 0 AND YEAR(NOW())) ) CREATE TABLE Выдача (Читатель INTEGER REFERENCES Читатель, Код_издания INTEGER REFERENCES Издание, Дата_выдачи DATETIME CHECK (VALUE < NOW())) PRIMARY KEY (Читатель, Код_издания, Дата_выдачи)

Изменение полей таблицы 118 ALTER TABLE {, } ::= {ADD | ADD | ALTER [COLUMN] | DROP | DROP CONSTRAINT } ::= { TO | TYPE { | } | POSITION | SET DEFAULT | DROP DEFAULT} Менять тип можно только на совместимый, если в таблице есть значения. Нельзя изменять таблицу, которая в данный момент используется.

Примеры изменения таблицы 119 ALTER TABLE Читатель ALTER ФИО POSITION 0, ADD Город CHAR(50) FOREIGN KEY Город(Название), ADD Улица CHAR(50) NOT NULL, ADD Дом INTEGER NOT NULL, ADD Квартира INTEGER, DROP Адрес ALTER TABLE Издание ALTER Объем TO Число_страниц, ADD Иллюстрации BOOLEAN ALTER TABLE Произведение DROP CONSTRAINT Год_значение

Удаление таблицы 120 DROP TABLE Нельзя удалить таблицу : -если на ее столбцы ссылаются внешние ключи других таблиц; -если она используется другими объектами БД ; -если она используется в данный момент. DROP TABLE Категория DROP TABLE Категория_произведения В каком порядке нужно выполнить эти запросы?

Представления (виды) 121 Результаты любого запроса на выборку можно сохранить в виртуальной таблице – представлении (виде, view). CREATE VIEW [( {, })] AS CREATE VIEW Детали_выдачи AS SELECT Читатель.билета, Издание.Название, Читатель.ФИО, Выдача.Дата FROM... Для пользователя представление выглядит как обычная таблица. С помощью представлений можно: -сохранять запросы в БД, чтобы не писать их каждый раз заново; -разбивать сложные запросы на части: промежуточный результат сохраняется в представление; -переименовывать столбцы: часто для названий столбцов таблицы разрешены лишь латинские буквы, а для представления – любые; -ограничивать доступ пользователей к БД: пользователь вообще не увидит столбцы и таблицы, доступ к которым ему запрещен.

Представления - примеры 122 CREATE VIEW Число_подборок AS SELECT ФИО, COUNT(Название) AS Кол_во FROM Читатель JOIN Подборка ON (Читатель.билета = Подборка.Читатель) GROUP BY ФИО; -- SELECT * FROM Число_подборок GROUP BY ФИО HAVING (Кол_во = MAX(Кол_во)) OR (Кол_во = MIN(Кол_во));

Лекция 14. Язык манипулирования данными (MDL) 123

Запрос на добавление строк 124 INSERT INTO { | } [( {, })] VALUES ( {, }) | Если указать значения, то добавится только 1 строка в конец таблицы или представления. Значения указываются только для перечисленных столбцов в том же порядке. Пропущенные столбцы заполняются значениями по умолчанию, а если их нет – NULL. Если добавляются значения всех столбцов в правильном порядке, их перечисление в запросе можно пропустить. INSERT INTO Автор (Фамилия, Имя, Дата_рождения) VALUES ("Хлебников", "Велимир", ) INSERT INTO Город VALUES ("Самара", "Россия") При наличии любой ошибки в запросе (неверные столбцы, неверный порядок или тип значений), не добавится вся строка.

Многострочный INSERT 125 Если вместо конкретных значений указать подзапрос на выборку (SELECT), то можно добавить сразу несколько строк. INSERT INTO Author_Old(Фамилия, Имя, Отчество, Дата_рождения) SELECT Фамилия, Имя, Отчество, Дата_рождения – 13 FROM Автор INSERT INTO Издание (Название, Год) SELECT Название, Год FROM Произведение WHERE Год > 1995 Чаще используется для заполнения временных, вспомогательных таблиц и представлений:

Запрос на обновление (изменение) 126 UPDATE { | } SET = {, = } [WHERE ] значение может быть NULL или вычисляемым UPDATE Издательство SET Адрес = "ул. Галактионовская, 35, оф. 104", Телефон = " " WHERE Название = "СамараИздат" UPDATE Читатель SET Срок_действия_билета = Срок_действия_билета + 365, Телефон = NULL WHERE билета = "034921"

Обновление или удаление 127 UPDATE OR INSERT INTO { | } [( {, })] VALUES ( {, }) [MATCHING {, }] Если такая строка уже есть, она обновится, если нет – добавится. После MATCHING указываются столбцы, для которых значения сравниваются. Если MATCHING отсутствует, проверяется первичный ключ. UPDATE OR INSERT INTO Издательство (Название, Город, Телефон) VALUES ("Ювента", "Москва", " ") UPDATE OR INSERT INTO Произведение (Название, Год) VALUES ("Мцыри", 1839) MATCHING (Название)

Запрос на удаление строк 128 DELETE { | } [WHERE ] С помощью DELETE можно удалять только целые строки, а не отдельные значения полей. В результате может быть удалено любое кол-во строк: 0, 1, N, все. В предложении WHERE запроса DELETE можно использовать вложенные запросы, которые могут быть как простыми, так и коррелированными (с использованием редактируемой таблицы). Но нельзя указывать редактируемую таблицу в поле FROM подзапроса. DELETE Выдача WHERE Читатель LIKE "600011" DELETE Подборка DELETE Выдача WHERE Читатель IN (SELECT билета FROM Читатель WHERE Выдача.Дата > Читатель.Срок_действия_билета)

Задание 129 Для любой таблицы из своего варианта написать запросы: 1.На создание таблицы. 2.На добавление строки. 3.На обновление строки. 4.На удаление строки.

Процедурный язык 130

Способы выполнения запросов Ручной ввод администратором БД. 2.Сохранение в виде текстового файла (скрипта, сценария) с расширением.sql и запуск из файла. 3.Генерация программой-клиентом. 4.Сохранение на стороне СУБД на процедурном языке. В Access процедурный язык не реализован, его заменяют макросы.

Хранимая процедура 132 Набор инструкций, хранимый в БД и выполняемый на стороне СУБД. Как и в обычных ЯП, процедура задается именем и списком параметров. Может содержать: любые SQL-запросы; переменные; оператор ветвления IF; цикл WHILE; встроенные функции; курсоры для хранения результатов запросов. Хранимые процедуры условно подразделяются на два типа: 1.Процедуры выборки (select procedure) возвращают результирующий набор строк, включающих столбцы, выбранные из одной или нескольких таблиц. Позволяет объединять данные, получаемые несколькими запросами. 2.Выполняемые процедуры (executable procedure) производят некоторые действия с БД и могут не возвращать результирующий набор строк. С помощью вычислений в хранимых процедурах можно получить многие результаты, недоступные через обычные запросы. Пример. Для каждого произведения указать список авторов через запятую.

Пример процедуры 133 CREATE PROCEDURE ShowAuthors( ) AS BEGIN DECLARE Count INT; DECLARE F, I, O CHAR; Count = SELECT COUNT(1) FROM Автор; CREATE VIEW Авторы20 AS SELECT Фамилия, Имя, Отчество FROM Автор FIRST 20; IF Count > 20 THEN ADD Авторы20 VALUES "...", "...", "..."; END. Вывести первых 20 авторов. Если авторов больше 20, добавить в конец …

Курсор 134 Курсор позволяет построчно обрабатывать результаты запроса SELECT. Неявный курсор – перебирает в цикле все строки запроса, сохраняет значения столбцов в каждой строке в переменные и обрабатывает их. FOR INTO : [, : ] [AS CURSOR ] DO Явный курсор – похож на работу с файлами. Необходимо связать курсор с запросом SELECT, открыть его, в конце закрыть. Во время работы можно произвольно перемещаться по строкам. DECLARE CURSOR FOR ( ); OPEN ; FETCH [NEXT | PRIOR | FIRST | LAST | ABSOLUTE | RELATIVE ] [FROM] INTO : [, : ]; CLOSE ;

Триггеры 135 Триггер – частный случай хранимой процедуры, которая срабатывает при выполнении для таблицы какого-либо оператора языка манипулирования данными (DML). триггер добавления (ADD TRIGGER); триггер обновления (UPDATE TRIGGER); триггер удаления (DELETE TRIGGER). Причем триггер может сработать как до выполнения операции, так и после, и вместо. Триггеры обычно используются для: проверки целостности данных, сложных (вычисляемых) значений по умолчанию, выдачи предупреждений пользователю, для отката транзакций.

Лекция 15. Защита данных 136

Безопасность БД 137 Безопасность БД включает обеспечение следующих свойств: 1.Целостность данных – непротиворечивость, соответствие заданной структуре и ограничениям. 2.Безопасность и секретность данных – защита от несанкционированного доступа, модификации и разрушения данных. 3.Восстанавливаемость данных – возможность возврата данных после любого сбоя. 4.Согласованность БД – в любой момент времени БД реагирует на запросы одинаковых пользователей одинаковым образом. 5.Эффективность БД – выполнение любых запросов пользователя за приемлемое время, используя минимальное количество вычислительных ресурсов и памяти. Наибольшая сложность для многопользовательских распределенных БД.

Защита данных 138 Защита данных средствами СУБД: 1.Управление доступом к данным. 2.Механизм транзакций. 3.Архивация и восстановление БД. 4.Журналирование и оповещения для контроля и фиксации нарушений. 5.Шифрование данных. Основные причины порчи и потери данных: 1.Намеренные или ненамеренные действия пользователя. 2.Аварийное завершение работы СУБД и прикладных программ при системном сбое и т.п. (БД остается в непредсказуемом состоянии). 3.Конфликты при работе двух и более пользователей с одними и теми же данными.

Управление доступом к данным 139 Схема доступа базируется на 3 понятиях: 1.Пользователь – действующее лицо, желающее получить доступ к данным (человек или программа). 2.Объект доступа – элементы БД, доступом к которым можно управлять (таблицы, представления, хранимые процедуры, отдельные столбцы и строки и др.). 3.Привилегия – системный признак (правило), определяющий операции, которые разрешено выполнять пользователю над конкретными объектами данных.

Пользователь 140 Управление пользователями выполняет администратор БД средствами СУБД. SQL не содержит средств создания, изменения и удаления пользователей. Каждому пользователю задается имя и пароль. Они записываются в специальную системную БД. Пароли обычно не хранятся в явном виде. По умолчанию, после создания пользователя ему не доступны никакие действия с БД. Администратор назначает пользователю привилегии на определенные действия, или может открыть полный доступ к БД.

Привилегии 141 Привилегии создаются и удаляются с помощью запросов SQL или менеджера СУБД. Запрос GRANT – создание привилегии. Указывается: кто (пользователь, группа пользователей) что (действие – добавление, изменение, чтение, удаление, выполнение) с чем (объект БД – таблица, представление, хранимая процедура и т.п.) может делать. Примеры: GRANT ALL ON Читатель TO Bibl_SergeevaAV GRANT UPDATE(Название, Издание) ON Подборка TO ЮсуповаЕ GRANT SELECT ON Издание TO PUBLIC PUBLIC – любой пользователь (общедоступная привилегия) Также пользователю можно назначить привилегию на выдачу привилегий другим пользователям (т.е. с правом последующей передачи). Запрос REVOKE – отмена привилегии. Указываются те же параметры. Одна и та же привилегия может быть назначена разными пользователями. Отменить можно только свои привилегии.

Роли 142 Роли позволяют управлять группами пользователей с одинаковыми привилегиями. Это упрощает управление доступом к данным. Роли можно создавать и удалять, назначать пользователям. CREATE ROLE DROP ROLE GRANT TO У одного пользователя может быть много ролей. Пользователь получает все привилегии всех своих ролей. Привилегии назначаются ролям так же, как и отдельным пользователям. Таблица 1 Представление 2 Роль Пользователь 1 Пользователь 2 Пользователь 3 SELECT, UPDATE ALL

Транзакции 143 Транзакция – это последовательность операций обработки данных, выполняемых как неделимое действие над БД, осмысленное с точки зрения пользователя. Свойства транзакции (ACID – Atomicity, Consistency, Isolation, Durability): 1.Атомарность: транзакция должна быть выполнена в целом или не выполнена вовсе. 2.Согласованность: при выполнении транзакций данные переходят из одного согласованного состояния в другое, т.е. транзакция не разрушает взаимной согласованности данных. 3.Изолированность: конкурирующие за доступ к базе данных транзакции физически обрабатываются последовательно, изолированно друг от друга, но для пользователей это выглядит так, как будто они выполняются параллельно. 4.Долговечность: если транзакция завершена успешно, то изменения в данных, произведенные в ней, не должны быть потеряны ни при каких обстоятельствах (даже в случае последующих ошибок или сбоя системы).

Выполнение транзакций 144 Любая транзакция может завершиться только одним из двух действий: фиксация (подтверждение) транзакции; откат (отмена) транзакции. Фиксация выполняется командой COMMIT при успешном выполнении всех операций. В результате фиксации все изменения становятся постоянными и видимыми для других транзакций. Откат выполняется командой ROLLBACK при возникновении ошибок или отмене действия пользователем. В результате отката все изменения, внесенные транзакцией, отменяются, БД возвращается к предыдущему состоянию. Пока не выполнена фиксация, все изменения в БД считаются вре́менными. Они не видны другим пользователям, будут потеряны при сбое системы. Если транзакция очень большая, из-за отката пользователю придется вводить все данные заново, что не очень удобно. Для этого в некоторых СУБД существуют «точки сохранения» (SAVEPOINT). Тогда транзакцию можно откатить не к началу, а к последней точке сохранения.

Журнал транзакций 145 Для отката транзакций и восстановления системы необходимо записывать выполненные транзакции, т.е. выполнять журналирование. Когда пользователь выполняет запрос на изменение БД, СУБД автоматически вносит в журнал транзакций запись для каждой измененной строки с двумя ее копиями: до изменения и после. Только после этого СУБД изменит физическую строку в БД. Затем, если пользователь выполнит оператор COMMIT, в журнале отмечается конец транзакции. При выполнении ROLLBACK СУБД обращается к журналу и извлекает из него «исходные» копии строк. В случае системного сбоя проверяются: транзакции, которые не успели завершиться к моменту сбоя, должны быть отменены; транзакции, которые успели завершиться к моменту сбоя, но информация из них еще не переписалась в саму БД, должны быть выполнены повторно.

Параллелизм 146 В многопользовательских БД возникают проблемы при одновременном доступе пользователей к одним и тем же данным (параллельно). Без контроля доступа к данным возможны ошибки (несогласованность данных): потерянные или "скрытые" обновления – при одновременной записи данных сохранится только одно из изменений, а остальные будут потеряны и пользователи об этом не узнают; "грязное" чтение – чтение результатов незафиксированной транзакции; несогласованный анализ (неповторяемое чтение) – один пользователь составляет итоговый отчет, а в это время другой пользователь меняет исходные данные и фиксирует транзакцию; чтение фантомов – возникают, когда один пользователь просматривает информацию, а другой вставляет или удаляет строки.

Блокировка данных 147 Основным решение является механизм блокировок. Уровни блокировки: 1)вся БД целиком; 2)целая таблица; 3)часть таблицы; 4)отдельная строка; 5)отдельное поле отдельной строки (на практике обычно отсутствует). Транзакция захватывает блокируемый объект, запрещая доступ к нему для других (конкурирующих) транзакций. Это замедляет работу БД. Схема блокировки может быть полной или «только для чтения». Но неподтвержденные (временные) изменения никогда не видны другим пользователям.

Двухфазная фиксация 148 Используется в распределенных БД, когда транзакция сначала выполняется на локальном сервере, а потом должна быть выполнена на других серверах. Фаза 1: в транзакции встретился оператор COMMIT. Центральный сервер распределенной БД направляет уведомление «подготовиться к фиксации» всем серверам локальных БД, выполняющим распределенную транзакцию. Если все серверы приготовились к фиксации (откликнулись на уведомление, и их отклик был получен), сервер распределенной БД принимает решение о фиксации. Серверы локальных БД остаются в состоянии готовности и ожидают от него команды «зафиксировать». Если хотя бы один из серверов не откликнулся, то центральный сервер откатывает локальные транзакции на всех узлах. Фаза 2: центральный сервер направляет команду «зафиксировать» всем узлам, затронутым транзакцией. Если связь с локальной БД потеряна, то сервер продолжает попытки завершить транзакцию, пока связь не будет восстановлена.

Архивация и восстановление 149 Никакие средства защиты не могут на 100% гарантировать безопасность данных, поэтому обязательно нужно делать резервные копии. Цели архивации: a)создать избыточность данных для возможности их восстановления; b)сократить размер рабочей БД (устаревшие данные помещаются в архив, а из основной БД удаляются). Архивирование может быть: 1.Полным (вся БД, журнал транзакций и системная информация). 2.Отдельных таблиц. 3.Пошаговым (только журнал транзакций). Во время архивации БД остается доступной пользователям, но ее работа может быть замедлена. Поэтому важно выбрать оптимальную частоту и тип архивации. Обычно полную архивацию выполняют изредка (раз в неделю, месяц, год), а пошаговую – часто (раз в день, в час). Во время восстановления БД она не доступна пользователям. Восстановление из полной резервной копии подразумевает удаление поврежденной БД и полный перенос данных из резервной копии. Восстановление из пошаговой копии – повторение действий из заархивированного журнала.