- •Основы систем баз данных
- •Содержание
- •Введение
- •В первом разделе рассматриваются базовые понятия реляционной модели данных. Дается общая характеристика реляционной модели данных
- •Раздел 1. Базовые понятия реляционной модели данных. Общая характеристика реляционной модели данных
- •1.1. Типы данных
- •Простые типы данных
- •Структурированные типы данных
- •Ссылочные типы данных
- •Типы данных, используемые в реляционной модели
- •1.2. Домены
- •1.3. Отношения, атрибуты, кортежи отношения Определения и примеры
- •Свойства отношений
- •Первая нормальная форма
- •1.4. Контрольные вопросы
- •Раздел 2. Реляционная алгебра. Обзор реляционной алгебры
- •2.1. Замкнутость реляционной алгебры
- •2.2. Отношения, совместимые по типу
- •2.3. Оператор переименования атрибутов
- •2.4. Теоретико-множественные операторы Объединение
- •Пересечение
- •Вычитание
- •Декартово произведение
- •Специальные реляционные операторы Выборка (ограничение, селекция)
- •Проекция
- •Соединение
- •Общая операция соединения
- •Тэта-соединение
- •Экви-соединение
- •Естественное соединение
- •Деление
- •2.5. Примеры использования реляционных операторов
- •Невыразимость транзитивного замыкания реляционными операторами
- •Кросс-таблицы
- •2.6. Контрольные вопросы
- •Рздел 3. Нормальные формы отношений. Этапы разработки базы данных
- •3.1. Критерии оценки качества логической модели данных
- •Адекватность базы данных предметной области
- •Легкость разработки и сопровождения базы данных
- •Скорость операций обновления данных (вставка, обновление, удаление)
- •Скорость операций выборки данных
- •Основной пример
- •1Нф (Первая Нормальная Форма)
- •Аномалии обновления
- •Аномалии вставки (insert)
- •Аномалии обновления (update)
- •Аномалии удаления (delete)
- •3.2. Функциональные зависимости
- •Определение функциональной зависимости
- •Функциональные зависимости отношений и математическое понятие функциональной зависимости
- •3.3. 2Нф (Вторая Нормальная Форма)
- •Анализ декомпозированных отношений
- •Оставшиеся аномалии вставки (insert)
- •Оставшиеся аномалии обновления (update)
- •Оставшиеся аномалии удаления (delete)
- •3.4. 3Нф (Третья Нормальная Форма)
- •Алгоритм нормализации (приведение к 3нф)
- •3.5. Анализ критериев для нормализованных и ненормализованных моделей данных Сравнение нормализованных и ненормализованных моделей
- •3.6. Oltp и olap-системы
- •3.7. Корректность процедуры нормализации - декомпозиция без потерь. Теорема Хеза
- •3.8. Контрольные вопросы
- •Раздел 4. Нормальные формы более высоких порядков
- •4.1. Нфбк (Нормальная Форма Бойса-Кодда)
- •4.2. 4Нф (Четвертая Нормальная Форма)
- •4.3. 5Нф (Пятая Нормальная Форма)
- •4.4. Продолжение алгоритма нормализации (приведение к 5нф)
- •4.5. Контрольные вопросы
- •Раздел 5. Элементы модели "сущность-связь"
- •5.1. Основные понятия er-диаграмм
- •5.2. Пример разработки простой er-модели
- •5.3. Концептуальные и физические er-модели
- •5.4. Контрольные вопросы
- •Раздел 6. Элементы языка sql
- •6.1. Операторы sql
- •Операторы ddl (Data Definition Language) - операторы определения объектов базы данных
- •Операторы dml (Data Manipulation Language) - операторы манипулирования данными
- •Операторы защиты и управления данными
- •6.2. Примеры использования операторов манипулирования данными
- •Insert - вставка строк в таблицу
- •Примеры использования оператора select
- •Отбор данных из одной таблицы
- •Отбор данных из нескольких таблиц
- •Использование имен корреляции (алиасов, псевдонимов)
- •Использование агрегатных функций в запросах
- •Использование агрегатных функций с группировками
- •Использование подзапросов
- •Использование объединения, пересечения и разности
- •Синтаксис оператора выборки данных (select)
- •Синтаксис оператора выборки
- •Синтаксис соединенных таблиц
- •Синтаксис условных выражений раздела where
- •Порядок выполнения оператора select
- •Стадия 1. Выполнение одиночного оператора select
- •Стадия 2. Выполнение операций union, except, intersect
- •Стадия 3. Упорядочение результата
- •Как на самом деле выполняется оператор select
- •Реализация реляционной алгебры средствами оператора select (Реляционная полнота sql)
- •6.3. Контрольные вопросы
- •Заключение
- •Библиографический список
Использование агрегатных функций в запросах
Пример 21. Получить общее количество поставщиков (ключевое слово COUNT):
SELECT COUNT(*) AS N
FROM P;
В результате получим таблицу с одним столбцом и одной строкой, содержащей количество строк из таблицы P.
Таблица 82
Полученная таблица
N |
3 |
Пример 22. Получить общее, максимальное, минимальное и среднее количества поставляемых деталей (ключевые слова SUM, MAX, MIN, AVG):
SELECT
SUM(PD.VOLUME) AS SM,
MAX(PD.VOLUME) AS MX,
MIN(PD.VOLUME) AS MN,
AVG(PD.VOLUME) AS AV
FROM PD;
В результате получим следующую таблицу с одной строкой.
Таблица 83
Полученная таблица
SM |
MX |
MN |
AV |
2000 |
1000 |
100 |
333.33333333 |
Использование агрегатных функций с группировками
Пример 23. Для каждой детали получить суммарное поставляемое количество (ключевое слово GROUP BY…):
SELECT
PD.DNUM,
SUM(PD.VOLUME) AS SM
GROUP BY PD.DNUM;
Этот запрос будет выполняться следующим образом. Сначала строки исходной таблицы будут сгруппированы так, чтобы в каждую группу попали строки с одинаковыми значениями DNUM. Потом внутри каждой группы будет просуммировано поле VOLUME. От каждой группы в результирующую таблицу будет включена одна строка.
Таблица 84
Результирующая таблица
DNUM |
SM |
1 |
1250 |
2 |
450 |
3 |
300 |
Замечание. В списке отбираемых полей оператора SELECT, содержащего раздел GROUP BY можно включать только агрегатные функции и поля, которые входят в условие группировки. Следующий запрос выдаст синтаксическую ошибку:
SELECT
PD.PNUM,
PD.DNUM,
SUM(PD.VOLUME) AS SM
GROUP BY PD.DNUM;
Причина ошибки в том, что в список отбираемых полей включено поле PNUM, которое не входит в раздел GROUP BY. И действительно, в каждую полученную группу строк может входить несколько строк с различными значениями поля PNUM. Из каждой группы строк будет сформировано по одной итоговой строке. При этом нет однозначного ответа на вопрос, какое значение выбрать для поля PNUM в итоговой строке.
Замечание. Некоторые диалекты SQL не считают это за ошибку. Запрос будет выполнен, но предсказать, какие значения будут внесены в поле PNUM в результирующей таблице, невозможно.
Пример 24. Получить номера деталей, суммарное поставляемое количество которых превосходит 400 (ключевое слово HAVING…):
Замечание. Условие, что суммарное поставляемое количество должно быть больше 400 не может быть сформулировано в разделе WHERE, т.к. в этом разделе нельзя использовать агрегатные функции. Условия, использующие агрегатные функции должны быть размещены в специальном разделе HAVING:
SELECT
PD.DNUM,
SUM(PD.VOLUME) AS SM
GROUP BY PD.DNUM
HAVING SUM(PD.VOLUME) > 400;
В результате получим следующую таблицу.
Таблица 85
Полученная таблица
DNUM |
SM |
1 |
1250 |
2 |
450 |
Замечание. В одном запросе могут встретиться как условия отбора строк в разделе WHERE, так и условия отбора групп в разделе HAVING. Условия отбора групп нельзя перенести из раздела HAVING в раздел WHERE. Аналогично и условия отбора строк нельзя перенести из раздела WHERE в раздел HAVING, за исключением условий, включающих поля из списка группировки GROUP BY.