- •Введение в бд
- •Файловые системы
- •Системы с базами данных
- •Модели данных
- •Альтернативная терминология Терминология, используемая в реляционной модели, порой может привести к путанице, поскольку помимо предложенных двух наборов терминов существует еще один – третий.
- •Сетевая модель данных
- •Иерархическая модель данных
- •Вопросы:
- •Реляционная модель.
- •Реляционная алгебра. Реляционное исчисление.
- •Реляционная модель
- •Упражнения:
- •Реляционные языки
- •Реляционная алгебра
- •Унарные операции реляционной алгебры
- •Операции с множествами
- •Операции соединения
- •Деление
- •Реляционное исчисление
- •Реляционное исчисление кортежей
- •Реляционное исчисление доменов
- •Другие языки
- •Тема 3 Моделирование данных Модель «сущность-связь»
- •Элементы модели «сущность-связь»
- •Сущность
- •Атрибуты
- •Идентификаторы
- •Три типа бинарных связей
- •Диаграммы «сущность-связь»
- •Изображение атрибутов в диаграммах «сущность-связь»
- •Слабые сущности
- •Подтипы сущностей
- •Пример er-диаграммы
- •Диаграммы «сущность-связь» в стиле uml
- •Сущности и связи в uml
- •Представление слабых сущностей
- •Представление подтипов
- •Конструкции ооп, введенные языком uml
- •Семантическая объектная модель
- •Семантические объекты
- •Определение семантических объектов
- •Атрибуты
- •Кардинальное число атрибута
- •Экземпляры объектов
- •Парные атрибуты
- •Объектные идентификаторы
- •Домены атрибутов
- •Представления семантических объектов
- •Создание семантических объектных моделей данных
- •Пример: база данных администрации нтуу «кпи»
- •Спецификация объектов
- •Типы объектов
- •Простые объекты
- •Составные объекты
- •Гибридные объекты
- •Ассоциативные объекты
- •Объекты вида родитель/подтип
- •Объекты вида архетип/версия
- •Переход от семантической объектной модели к модели «сущность-связь»
- •Вопросы:
- •Упражнения:
- •Тема 4 Нормализация
- •Классы отношений
- •Нормальные формы от первой до пятой
- •Тема 5 Методология проектирования баз данных Введение в методологию проектирования баз данных
- •Методология концептуального проектирования базы данных
- •Методология логического проектирования реляционных баз данных
- •Суть состоит в том, что при устранении избыточности очень важно исследовать значение каждой из связей, существующих между сущностями.
- •Методология физического проектирования базы данных
- •Трехуровневая архитектура ansi-sparc
- •Система управления Базами Данных
- •1. Хранение, извлечение и обновление данных
- •2. Каталог доступный конечным пользователям
- •Поддержка транзакций
- •Сервисы управления параллельностью
- •Сервисы восстановления
- •6. Сервисы контроля доступа к данным
- •Поддержка обмена данными
- •8. Вспомогательные службы
- •Преимущества:
- •Недостатки:
- •Вопросы:
- •Упражнения:
- •История языка sql
- •Особая роль языка sql
- •Используемая терминология
- •Запись операторов sql
- •Манипулирование данными
- •Литералы
- •Простые запросы
- •Выборка строк (конструкция where)
- •Сортировка результатов (конструкция order by)
- •Использование агрегирующих функций языка sql
- •Группирование результатов (конструкция group by)
- •Ограничения на выполнение группирования (конструкция having)
- •Подзапросы
- •Ключевые слова any и all
- •Многотабличные запросы
- •Внешние соединения
- •Выполнение соединений
- •Ключевые слова exists и not exist
- •Комбинирование результирующих таблиц (операции union, intersect и except)
- •Изменение содержимого базы данных
- •Добавление новых данных в таблицу (оператор insert)
- •Модификация данных в базе (оператор update)
- •Удаление данных из базы (оператор delete)
- •Скалярные типы данных языка sql
- •Логические данные (тип boolean)
- •Символьные данные (тип character)
- •Битовые данные (тип bit)
- •Точные числовые данные (тип exact numeric)
- •Округленные числовые данные (тип approximate numeric)
- •Дата и время (тип datetime)
- •Интервальный тип данных interval
- •Скалярные операторы
- •Средства поддержки целостности данных
- •Обязательные данные
- •Ограничения для доменов
- •Целостность сущностей
- •Ссылочная целостность
- •Требования данного предприятия
- •Определение данных
- •Создание баз данных
- •Создание таблиц (оператор create table)
- •Модификация определения таблицы (оператор alter table)
- •Удаление таблиц (оператор drop table)
- •Создание индекса (оператор create index)
- •Удаление индекса (оператор drop index)
- •Представления
- •Создание представлений (оператор create view)
- •Удаление представлений (оператор drop view)
- •Замена представлений
- •Ограничения на использование представлений
- •Обновление данных в представлениях
- •Использование конструкции with check option
- •Преимущества и недостатки представлений
- •Преимущества
- •Недостатки
- •Материализация представлений
- •Использование транзакций
- •Немедленные и отложенные ограничения поддержки целостности данных
- •Управление доступом к данным
- •Идентификаторы пользователей и права владения
- •Привилегии
- •Предоставление привилегий другим пользователям (оператор grant)
- •Отмена предоставленных пользователям привилегий (оператор revoke)
- •Приложение
- •Тема 7.3 Хранимые процедуры и функции. Триггеры.
- •Создание хранимых процедур и функций
- •Простые формы выражений
- •Поддержка транзакций
- •Свойства транзакций
- •Архитектура базы данных
- •Управление параллельным доступом
- •Проблема потерянного обновления
- •Проблема зависимости от незафиксированных результатов (или "грязного" чтения)
- •Проблема анализа несогласованности
- •Упорядочиваемость и восстанавливаемость
- •Конфликтная упорядочиваемость
- •Упорядочиваемость по просмотру
- •Восстанавливаемость
- •Методы управления параллельным доступом
- •Методы блокировки
- •Двухфазная блокировка
- •Управление параллельным выполнением при использовании индексных структур
- •Защелки
- •Взаимоблокировка
- •Тайм-ауты
- •Предотвращение взаимоблокировок
- •Обнаружение взаимоблокировок
- •Частота выполнения операции обнаружения взаимоблокировок
- •Возобновление нормальной работы после обнаружения взаимоблокировки
- •Использование временных отметок
- •Правило записи Томаса
- •Сравнение методов
- •Упорядочение временных отметок в случае многих версий
- •Оптимистические методы упорядочения
- •Степень детализации блокируемых элементов данных
- •Иерархия степеней детализации
- •Блокировка с учетом нескольких степеней детализации
- •Восстановление базы данных
- •Необходимость восстановления
- •Транзакции и восстановление
- •Управление буферами базы данных
- •Функции восстановления
- •Механизм резервного копирования
- •Файл журнала
- •Создание контрольных точек
- •Методы восстановления
- •Метод восстановления с использованием отложенного обновления
- •Метод восстановления с использованием немедленного обновления
- •Метод теневого страничного обмена
- •Улучшенные модели транзакций
- •Модель вложенных транзакций
- •Эмуляция механизма вложенных транзакций с помощью точек сохранения
- •Хроники
- •Модель многоуровневых транзакций
- •Динамическая реструктуризация
- •Модели рабочих потоков
- •Общий обзор методов обработки запросов
- •Основные этапы обработки запросов
- •Динамическая и статическая оптимизация запросов
- •Декомпозиция запросов
- •Нормализация
- •Семантический анализ
- •Упрощение
- •Реструктуризация запросов
- •Эвристический подход к оптимизации запросов
- •Правила преобразования операций реляционной алгебры
- •Оценка стоимости операций реляционной алгебры
- •Статистические показатели базы данных
- •Вариант 6. Поиск по равенству значению кластеризующего (вторичного) индекса
- •Вариант 7. Поиск по равенству значению некластеризующего (вторичного) индекса
- •Составные предикаты
- •Конъюнктивная выборка без дизъюнкций
- •Выборки с дизъюнкциями
- •Конвейерная обработка данных
- •Тема 10
- •Основные типы угроз
- •Контрмеры – компьютерные средства контроля
- •Авторизация пользователей
- •Привилегии
- •Права владения и привилегии
- •Представления (подсхемы)
- •Резервное копирование и восстановление
- •Поддержка целостности
- •Шифрование
- •Raid (массив независимых дисковых накопителей с избыточностью)
- •Средства защиты субд Microsoft Access
- •Установка пароля
- •Защита на уровне пользователя
Манипулирование данными
В базе «Учебный процесс» имеются следующие таблицы:
СТУДЕНТ (НГ, НС, ФИО, ГОДР, АДРЕС, ПБАЛЛ)
ГРУППА (НГ, КОЛ, ПБАЛЛ)
КАФЕДРА (ККАФ, НКАФ, ТЕЛ, ЗАВ, ФОТО)
ПРЕПОДАВАТЕЛЬ (ТАБН, ФИО, СТ, ЗВ, ККАФ)
ПРЕДМЕТ (КП, НП, ЧАСЫ, ЛЕК, ПР, ЧС, ПРОГР)
ИЗУЧЕНИЕ (НГ, КП, ТАБН, ВИДЗ, ЧАСЫ, СБАЛЛГР)
УСПЕВАЕМОСТЬ (НГ, НС, КП, ТАБН, ВИДЗ, ОЦЕНКА),
которые заполнены соответствующими данными (см. приложение)
Литералы
Литералы представляют собой константы, которые используются в операторах SQL.
Существуют различные формы литералов для каждого типа данных, которые поддерживаются SQL. Укажем различия между литералами, которые следует заключать в одинарные кавычки, и теми, которые не следует. Все нечисловые значения данных всегда должны заключаться в одинарные кавычки, а все числовые данные не должны заключаться в одинарные кавычки. Ниже приведен пример использования литералов для вставки данных в таблицу.
INSERT INTO СТУДЕНТ (НГ, НС, ФИО, ГОДР, АДРЕС, ПБАЛЛ)
VALUES {'ТМ-31', 26,'Чертов', 1986, NULL, 4,3};
Значение столбцов НС и ГОДР является литералом целочисленного типа, а значение столбца ПБАЛЛ – это десятичный числовой литерал. Ни один из них не должен заключаться в одинарные кавычки. Значения всех остальных столбцов представляют собой символьные строки и обязательно должны быть взяты в одинарные кавычки.
Простые запросы
Назначение оператора SELECT состоит в выборке и отображении данных одной или более таблиц базы данных. Это исключительно мощный оператор, способный выполнять действия, эквивалентные операторам реляционной алгебры выборки, проекции и соединения, причем в пределах единственной выполняемой команды.
Напоминаем. Операция выборки применяется к одному отношению R и определяет результирующее отношение, которое содержит только те кортежи (строки) из отношения R, которые удовлетворяют заданному условию (предикату).
Операция проекции применяется к одному отношению R и определяет новое отношение, содержащее вертикальное подмножество отношения R, создаваемое посредством извлечения значений указанных атрибутов и исключения из результата строк-дубликатов.
Объединение двух отношений R и S определяет новое отношение, которое включает все кортежи, содержащиеся только в R, только в S, одновременно в R и S, причем все дубликаты кортежей исключены. При этом отношения R и S должны быть совместимыми по объединению.
Оператор SELECT является чаще всего используемой командой языка SQL. Общий формат оператора SELECT имеет следующий вид:
SELECT [DISTINCT | ALL] {* | [columnExpression [AS newName]] [ , . . .] }
FROM TableName [alias] [ , . . ]
[WHERE condition]
[GROUP BY columnList] [HAVING condition]
[ORDER BY columnList]
Здесь параметр columnExpression представляет собой имя столбца или выражение из нескольких имен. Параметр TableName является именем существующей в базе данных таблицы (или представления), к которой необходимо получить доступ. Необязательный параметр alias – это сокращение, устанавливаемое для имени таблицы TableName. Обработка элементов оператора SELECT выполняется в следующей последовательности.
FROM. Определяются имена используемой таблицы или нескольких таблиц.
WHERE. Выполняется фильтрация строк объекта в соответствии с заданными условиями.
GROUP BY. Образуются группы строк, имеющих одно и то же значение в указанном столбце.
HAVING. Фильтруются группы строк объекта в соответствии с указанным условием.
SELECT. Устанавливается, какие столбцы должны присутствовать в выходных данных.
ORDER BY. Определяется упорядоченность результатов выполнения оператора.
Порядок конструкций в операторе SELECT не может быть изменен. Только две конструкции оператора – SELECT и FROM – являются обязательными, все остальные конструкции могут быть опущены. Операция выборки с помощью оператора SELECT является замкнутой, в том смысле, что результат запроса к таблице также представляет собой таблицу. Существует множество вариантов использования данного оператора, что иллюстрируется приведенными ниже примерами.
Пример 7.1.1. Выборка всех столбцов и всех строк
Составьте список подробных сведений о каждом из студентов.
Поскольку в приведенном выше запросе не указаны никакие ограничения, в оператор не требуется помещать конструкцию WHERE. Кроме того, необходимо выбрать все существующие в таблице столбцы. Поэтому данный запрос записывается следующим образом:
SELECT НГ, НС, ФИО, ГОДР, АДРЕС, ПБАЛЛ
FROM СТУДЕНТ;
Поскольку выборка всех имеющихся в таблице столбцов выполнятся достаточно часто, в языке SQL определен упрощенный вариант записи значения "все столбцы" – вместо имен столбцов указывается символ звездочки (*). Приведенный ниже оператор полностью эквивалентен первому и представляет собой упрощенный вариант записи того же самого запроса:
SELECT *
FROM СТУДЕНТ;
Таблица 7.1.1. Результат выполнения запроса из примера 7.1.1
Группа |
№ студента |
ФИО |
Год рождения |
АДРЕС |
Проходной балл |
001 |
01 |
Васильев М.В. |
1985 |
|
5 |
001 |
02 |
Носков А.К. |
1976 |
|
4,1 |
101 |
01 |
Аристов Р.П. |
1979 |
|
4,5 |
101 |
02 |
Бондаренко С.А. |
1978 |
|
4,5 |
101 |
03 |
Борисова Е.И. |
1979 |
|
4,25 |
101 |
04 |
Макова Н.В. |
1977 |
|
4,75 |
102 |
01 |
Боярская Н.П. |
1977 |
|
4,5 |
102 |
02 |
Федоров Д.К. |
1977 |
|
4,25 |
102 |
03 |
Сидоров И.Р. |
1977 |
|
4,5 |
103 |
01 |
Андреев Г.М. |
1978 |
|
4,25 |
103 |
02 |
Петров О.К. |
1979 |
|
4,75 |
104 |
01 |
Иванов К.К. |
1977 |
|
4,5 |
104 |
07 |
Григорьева Е.А. |
1979 |
|
4,75 |
104 |
14 |
Самоделкин И.В. |
1978 |
|
3 |
105 |
01 |
Серов И.Г. |
1980 |
|
4,5 |
105 |
12 |
Антоненко К. П. |
1978 |
|
4,5 |
201 |
12 |
Карпов Р.А. |
1984 |
|
3,8 |
201 |
23 |
Сомов Е.В. |
1983 |
|
4 |
403 |
01 |
Кромова В.Ю. |
1987 |
|
4 |
403 |
09 |
Пимонов Г.Р. |
1988 |
|
4 |
ТМ-31 |
07 |
Глушко И.В. |
1986 |
|
4,5 |
ТМ-31 |
12 |
Костюшко Т.А. |
1986 |
|
4,25 |
ТМ-31 |
19 |
Самойленко Ю.А. |
1986 |
|
4,75 |
ТМ-31 |
20 |
Святненко Ю.В. |
1986 |
|
4,75 |
ТМ-31 |
23 |
Терпило Д.А. |
1986 |
|
4,75 |
ТМ-31 |
24 |
Черняк А.Ю. |
1986 |
|
4,25 |
Пример 7.1.2. Выборка конкретных столбцов и всех строк
Создайте отчет о проходном балле всех студентов с указанием только номера студента(НС+НС), его имени и фамилии, а также сведений о балле.
SELECT НГ, НС, ФИО, ПБАЛЛ
FROM СТУДЕНТ
В этом примере на основе таблицы СТУДЕНТ создается новая таблица, включающая только указанные в запросе столбцы НГ, НС, ФИО, ПБАЛЛ, причем именно в этом порядке. Результат выполнения данного запроса приведен в табл. 7.1.2. Обратите внимание, что строки в результирующей таблице могут оказаться неотсортированными, если не дано специальное указание. С другой стороны, в некоторых СУБД сортировка результирующей таблицы выполняется по умолчанию с учетом значений одного или нескольких столбцов (например, в базе данных Microsoft Access сортировка этой результирующей таблицы была бы выполнена по значению первичного ключа НГ+ НС). Способы сортировки строк в результирующей таблице рассматриваются в следующем разделе.
Таблица 7.1.2. Результат выполнения запроса из примера 7.1.2
-
Группа
№ студента
ФИО
Проходной балл
001
01
Васильев М.В.
5
001
02
Носков А.К.
4,1
101
01
Аристов Р.П.
4,5
101
02
Бондаренко С.А.
4,5
101
03
Борисова Е.И.
4,25
101
04
Макова Н.В.
4,75
102
01
Боярская Н.П.
4,5
102
02
Федоров Д.К.
4,25
102
03
Сидоров И.Р.
4,5
103
01
Андреев Г.М.
4,25
103
02
Петров О.К.
4,75
104
01
Иванов К.К.
4,5
104
07
Григорьева Е.А.
4,75
104
14
Самоделкин И.В.
3
105
01
Серов И.Г.
4,5
105
12
Антоненко К. П.
4,5
201
12
Карпов Р.А.
3,8
201
23
Сомов Е.В.
4
403
01
Кромова В.Ю.
4
403
09
Пимонов Г.Р.
4
ТМ-31
07
Глушко И.В.
4,5
ТМ-31
12
Костюшко Т.А.
4,25
ТМ-31
19
Самойленко Ю.А.
4,75
ТМ-31
20
Святненко Ю.В.
4,75
ТМ-31
23
Терпило Д.А.
4,75
ТМ-31
24
Черняк А.Ю.
4,25
Пример 7.1.3. Использование ключевого слова DISTINCT
Составьте список номеров всех номеров групп в которых изучаются какие-нибудь предметы
SELECT НГ
FROM ИЗУЧЕНИЕ;
Результат выполнения этого запроса представлен в табл. 7.1.3. Обратите внимание, что результат выполнения запроса содержит повторяющиеся значения, поскольку, в отличие от операции проекции реляционной алгебры, оператор SELECT не исключает повторяющихся значений при выполнении проекции по значениям одного или нескольких столбцов. Для удаления из результирующей таблицы повторяющихся строк используется ключевое слово DISTINCT. Откорректированный запрос выглядит следующим образом:
SELECT DISTINCT НГ
FROM ИЗУЧЕНИЕ;
Результаты выполнения второго варианта запроса представлены в табл. 7.1.4.
Таблица 7.1.3. Результат выполнения запроса с сохранением повторяющихся значений из примера 7.1.3
Ном. группы |
001 |
001 |
101 |
101 |
101 |
101 |
101 |
101 |
101 |
101 |
101 |
101 |
102 |
102 |
102 |
102 |
105 |
201 |
201 |
202 |
203 |
204 |
403 |
ТМ-31 |
Таблица 7.1.4. Результат выполнения запроса с исключением повторяющихся значений из примера 7.1.3
Ном. группы |
001 |
101 |
102 |
105 |
201 |
202 |
203 |
204 |
403 |
ТМ-31 |
Пример 7.1.4. Вычисляемые поля
Создайте отчет о количестве часов изучения каждого предмета в каждом семестре.
SELECT КП, НП, ЧАСЫ/ЧС
FROM ПРЕДМЕТ;
Результаты выполнения запроса представлены в табл. 7.1.5.
Таблица 7.1.5. Результат выполнения запроса из примера 7.1.4
Код предмета |
Название предмета |
Col3 |
01 |
Информатика |
50 |
02 |
Математика |
50 |
03 |
История |
46,6 |
04 |
Иностранный яз. |
50 |
05 |
Философия |
50 |
06 |
Физкультура |
50 |
07 |
Программирование |
70 |
08 |
Право |
100 |
09 |
Физика |
72,5 |
10 |
Электродинамика |
290 |
11 |
Психология |
90 |
Это пример использования в запросе вычисляемого поля (иногда эти поля называют расчетными, или производными). В общем случае для создания вычисляемого поля в списке SELECT следует указать некоторое выражение языка SQL. В этих выражениях могут применяться операции сложения, вычитания, умножения и деления. При построении сложных выражений могут использоваться круглые скобки. Для получения значения вычисляемого поля могут использоваться значения из нескольких столбцов таблицы, однако тип данных тех столбцов, которые входят в арифметические выражения, обязательно должен быть цифровым.
В таблице, полученной в результате выполнения запроса, четвертый столбец называется соl3. Обычно столбцам результирующей таблицы присваиваются имена соответствующих им столбцов исходных таблиц базы данных. Однако в данном случае это правило неприменимо, поскольку в стандарте SQL не определены правила именования производных столбцов. В одних диалектах языка SQL имена таким столбцам присваивают в соответствии с порядком их расположения в таблице (например, соl3), в других диалектах у подобного столбца имя может вовсе отсутствовать или вместо него может использоваться выражение, находящееся в списке SELECT. Стандарт ISO позволяет явным образом задавать другие имена столбцов результирующей таблицы, для чего применяется конструкция AS. При использовании этой конструкции приведенный выше оператор SELECT может быть переписан следующим образом:
SELECT КП, НП, ЧАСЫ/ЧС AS Кол-во_часов/семестр
FROM ПРЕДМЕТ;
В этом случае третий столбец результирующей таблицы будет называться Кол-во_часов/семестр, а не col3.
