- •Установочный модуль
- •Введение
- •Модуль 1
- •Реляционная алгебра
- •Отсутствующие данные
- •Пустые значения
- •Неопределенные значения
- •Интерпретации
- •Правила вычисления выражений
- •Следствия
- •Проверка условий
- •Реляционные объекты данных
- •Формальные определения
- •Домены и атрибуты
- •Схема отношения
- •Именованное значение атрибута
- •Кортеж
- •Отношение
- •Схема базы данных
- •База данных
- •Операции реляционной алгебры
- •Унарные операции
- •Бинарные операции
- •Варианты операции соединения
- •Производные операции
- •Пример построения выражения реляционной алгебры
- •Понятие базовых и виртуальных отношений
- •Понятие полноты реляционной алгебры
- •Формирование запросов на языке SQL
- •Металингвистические символы
- •Реализация операций реляционной алгебры
- •Пример использования подзапросов
- •Группирующие запросы
- •Упорядочение результатов
- •Вопросы для самоконтроля
- •Упражнения
- •Построение выражений реляционной алгебры
- •Модуль 2
- •Базовые и виртуальные отношения
- •Типы данных
- •Базовые типы данных
- •Типы данных, определяемые пользователем
- •Первичные и кандидатные ключи
- •Создание базовых отношений
- •Индексы
- •Модификация базовых отношений
- •Вставка строк
- •Обновление строк
- •Удаление строк
- •Целостность
- •Декларативная поддержка
- •Пример декларативной поддержки целостности
- •Транзакции и блокировки
- •Триггеры
- •Виртуальные отношения
- •Вопросы для самоконтроля
- •Упражнения
- •Декларативная поддержка целостности
- •Модуль 3
- •Нормальные формы
- •Функциональные зависимости (ФЗ)
- •Правила вывода Армстронга
- •Производные правила вывода
- •Независимость правил Армстронга
- •Полнота системы правил Армстронга
- •Нормальные формы
- •Первая нормальная форма (1NF)
- •Вторая нормальная форма (2NF)
- •Третья нормальная форма (3NF)
- •Нормальная форма Бойса-Кодда (Boyce, Codd; NFBC)
- •Пример построения нормализованных схем отношений
- •Вопросы для самоконтроля
- •Модуль 4
- •Проектирование схем баз данных
- •Уровни логической модели
- •Миграция ключей и виды связей
- •Классификация кластеров
- •Иерархическая рекурсия
- •Абстрактная схема
- •Обобщения
- •Пример реализации иерархической рекурсии
- •Сетевая рекурсия
- •Абстрактная схема
- •Сетевая реализация иерархической рекурсии
- •Обобщения
- •Пример реализации сетевой рекурсии
- •Ассоциация
- •Детализация связей многие-ко-многим
- •Обобщения
- •Пример реализации ассоциации
- •Обобщение
- •Абстрактная схема
- •Пример реализации обобщения
- •Композиция
- •Абстрактная схема
- •Пример реализации композиции
- •Агрегация
- •Абстрактная схема
- •Пример реализации агрегации
- •Унификация атрибутов
- •Вопросы для самоконтроля
- •Упражнения
- •Иерархическая рекурсия
- •Сетевая рекурсия
- •Ассоциация
- •Обобщение
- •Композиция
- •Агрегация
- •Дополнительные главы
- •Технологии баз данных
- •Информационные системы
- •Жизненный цикл ИС
- •СУБД и БД
- •Жизненный цикл БД и средства проектирования
- •Модели данных
- •Иерархическая модель данных
- •Сетевая модель данных
- •Реляционная модель данных
- •Постреляционная модель данных
- •Объектно-ориентированные модели данных
- •XML как модель данных
- •Многомерная модель данных (OLAP)
- •Основные функции СУБД
- •Управление данными во внешней памяти
- •Управление буферами оперативной памяти
- •Управление транзакциями
- •Журнализация и восстановление БД после сбоев
- •Поддержка языков баз данных
- •Типовая организация СУБД
- •Модели взаимодействия с БД
- •Модель с централизованной архитектурой
- •Модель с автономными персональными компьютерами
- •Архитектура «файл-сервер»
- •Архитектура «клиент-сервер»
- •Архитектура «клиент-сервер» трехзвенная
- •Распределенные базы данных
- •Технология тиражирования данных
- •Понятие «фрактал»
- •Геометрические фракталы
- •Алгебраические фракталы
- •Стохастические фракталы
- •Системы итерируемых функций
- •Вопросы для самоконтроля
- •Литература
- •Список иллюстраций
- •Список таблиц
заменой ключевого слова inner на left outer, right outer и full outer соответственно.
2.9.3. Пример использования подзапросов
Практическое задание. Имеется следующий фрагмент базы данных:
Предметы(КодП, ИмяП) Студенты(NЗК, Ф, И, О, ...) Сессия(КодП, NЗК, Оценка)
Сформировать SQL-запрос, возвращающий ведомость с указанием номера зачетной книжки (NЗК), фамилии и инициалов студента (Фамилия И.О.) и оценки для предмета с наименованием (ИмяП) 0БД0. Предполагается, что атрибуты Ф, И, О студента не допускают null-значений, не являются пустыми и не содержат начальных пробелов. Атрибут ИмяП является кандидатным ключом, то есть наименования предметов являются уникальными.
Решение. Используем функцию RTrim(строка), которая возвращает строку без концевых пробелов, и функцию Left(строка, число) которая возвращает заданное число символов левой части строки.
select Студенты.NЗК,
RTrim(Ф) + 0 0 + Left(И,1) + 0.0 + Left(О,1) + 0.0 as ФИО,
Оценка
from Студенты inner join
(
select NЗК, Оценка from Сессия
where КодП = (select КодП from Предметы where ИмяП = 0БД0)
) as СессияБД
on Студенты.NЗК = СессияБД.NЗК
Внутренний подзапрос (select КодП from Предметы where ИмяП = 0БД0) может возвращать не более одного значения, так как атрибут ИмяП является кандидатным ключом отношения Предметы. В запросе, которому присвоен псевдоним СессияБД, этот подзапрос позволяет выделить из отношения Сессия те кортежи, которые относятся к рассматриваемому предмету. Внутреннее соединение отношения Студенты с запросом СессияБД по условию равенства номера зачетной книжки добавляет к отношению Студенты оценку. Так как атрибуты Ф, И, О студента не допускают null- значений и не являются пустыми, то формула вычисления возвращаемого атрибута ФИО не требует соответствующих проверок и упрощается.
Примечание. Что произойдет, если внутренний запрос (select КодП from Предметы where ИмяП = 0БД0) будет являться пустым (напрмер, из-за ошибки в наименовании предмета ИмяП: 0бд0 вместо 0БД0)? Тогда формально внутренний подзапрос вернет null-значение, условие выборки КодП = (null) станет тождественно ложным, а запрос с псевдонимом СессияБД, как и результирующий запрос, – пустым
2.9.4. Группирующие запросы
В SQL имеется ряд специальных функций, называемых функциями агрегирования и используемых при формировании списка выбираемых атрибутов в операторе select. Основными из функций явля-
ются
1)count(*)
2){count | sum | avg | min | max}
([all | distinct] выражение_над_столбцами)
Функции применяются к группам строк и возвращают для каждой из групп единственное значение.
Функция count(*) возвращает общее количество кортежей в группе. Остальные функции применяются к выражению над столбцами, то есть к столбцу значений, полученных из группы строк при вычислении выражения для каждой из строк. Null-значения в столбце значений игнорируются. Опция all действует по умолчанию и означает учет дубликатов строк. Если указана опция distinct, то дубликаты строк игнорируются. Эти опции значимы для функций подсчета числа строк, суммирования и осреднения, но не для функций экстремальных значений.
Результат осреднения целочисленных значений округляется до целого. Поэтому в этом случае может потребоваться использование функции явного преобразования типов. Приведем пример вычисления средней оценки по всем предметам в целом:
Сессия(NЗК, Ф, И, О, МнемоП, Оценка), 36Оценка65
select
avg(convert(decimal(5, 2), Оценка)) as ОценкаAvg, count(*) as Сдач
from Сессия
Для определения средней оценки и числа сдач по конкретному предмету пример можно дополнить фразой
where МнемоП = 0БД0
Вложение функций агрегирования не допускается. Однако из них можно составлять любые выражения.
Фраза group by (группировать по) обычно используется в сочетании с функциями агрегирования. Она следует за фразой where в операторе select:
select выбрать такие-то атрибуты from из таких-то отношений
where с таким-то условием выборки кортежей group by [all] выражение_группировки,.. having с таким-то условием выборки групп
Выражение группировки может быть именем столбца или в общем случае выражением над столбцами, не содержащим функций агрегирования. После того, как список выражений группировки определен, можно определить столбцы, выбираемые в операторе select. Каждый выбираемый столбец должен быть
1)либо выражением группировки, указанным во фразе group by,
2)либо выражением с некоторой комбинацией функций агрегирования.
Работу оператора select с фразой группировки можно представить следующим образом.
1.Выбираются строки, соответствующие условию поиска во фразе where.
2.Для каждой из строк вычисляются выражения группировки, после чего создается таблица, состоящая из уникальных сочетаний значений этих выражений. Тем самым создается таблица групп.
3.Затем таблица групп расширяется выбираемыми столбцами, которые не попали в список выражений группировки и для которых, следовательно, должны быть заданы агрегирующие выражения.
4.Если задано ключевое слово all, то шаги 1 и 2 меняются местами. То есть таблица групп создается для всевозможных групп безотносительно к условию поиска во фразе where. Однако при расширении таблицы групп при вычислении агрегирующих выражений условие поиска учитывается, так что дополнительные строки таблицы групп будут дополнены null-значениями.
Приведем пример вычисления средних оценок с группировкой по предметам:
Сессия(NЗК, Ф, И, О, МнемоП, Оценка), 36Оценка65
select МнемоП,
avg(covert(decimal(5, 2), Оценка)) as ОценкаAvg, count(*) as Сдач
from Сессия group by МнемоП