- •Введение
- •Глава 1 информационные системы
- •1.1 Информация как ресурс
- •1.2 Файловые системы
- •1.3 Информационные системы, использующие базы данных
- •1.3.1 Иерархические и сетевые модели данных
- •1.3.2 Реляционные системы управления базами данных
- •1.4.2 Программное обеспечение
- •1.4 Компоненты информационных систем
- •1.4.1 Технические средства
- •1.4.3 Данные
- •1.4.4 Пользователи
- •1.4.5 Организационное обеспечение
- •1.4.6 Отношения между компонентами системы
- •1.5 Основы проектирования информационных систем
- •1.5.1 Жизненный цикл программного обеспечения
- •1.5.2 Модели жизненного цикла по
- •1.5.3 Подходы к проектированию ис
- •1.6 Задания и вопросы для повторения
- •2.2 Подходы к проектированию баз данных
- •2.3 Создание базы данных
- •2.4 Основы концептуального проектирования баз данных
- •Объекты и отношения
- •2.3.2. Атрибуты
- •2.3.3 Ключи
- •2.3.4 Наследование
- •2.3.5 Составные объекты
- •2.3.6 Моделирование концептуальных и физических объектов
- •2.4 Реляционная модель данных
- •2.4.1 Поддержка целостности данных
- •Процесс нормализации таблиц
- •2.4.3 Пример построения нормализованной базы данных
- •2.4.4 Преобразование концептуальной модели в реляционную
- •2.5 Элементы er-моделирования
- •2.5.1 Основные понятия модели «сущность-связь»
- •2.5.2 Основные графические обозначения элементов модели
- •2.6 Заключительный этап проектирования
- •2.7 Сравнение концептуального и реляционного моделирования
- •2.8 Вопросы и задания для повторения
- •2.9 Упражнения и задачи
- •2.10 Проекты и профессиональные вопросы
- •Глава 3 реляционная алгебра и реляционное исчисление
- •3.1 Реляционная алгебра
- •3.1.1 Обзор реляционной алгебры
- •3.1.2 Теоретико-множественные операторы
- •3.1.3 Специальные реляционные операторы
- •3.1.4 Зависимые реляционные операторы
- •3.1.5 Примитивные реляционные операторы
- •3.2 Реляционное исчисление
- •3.2.1 Целевой список и определяющее выражение
- •3.2.2 Квантор существования
- •3.2.3 Квантор всеобщности
- •3.3 Заключение
- •3.4 Вопросы на повторение
- •3.5 Упражнения и задачи
- •Глава 4 управление реляционной базой данных с помощью sql
- •4.1 Элементы Transact-sql
- •Комментарии
- •4.1.2 Алфавит
- •4.1.3 Идентификаторы
- •Выражения
- •4.1.5 Ключевые слова
- •Операторы
- •4.1.7 Логические операторы
- •Типы данных
- •- Функции Transact-sql
- •4.2 Выборка данных из таблиц
- •4.2.1 Структура команды select
- •Результаты выборки
- •Отбор столбцов
- •Select Фамилия, Город from Гостиница.Dbo.Клиент
- •4.2.4 Определение заголовков столбцов
- •Выражения в выборках
- •Отбор записей
- •Порядок вывода данных
- •Котов Кузьма Кузьмич
- •Группировка данных
- •Отбор данных для групп
- •4.2.10 Директива compute
- •Выборка данных из нескольких таблиц
- •Объединение с помощью предложения where
- •Внутреннее объединение
- •4.2.14 Объединение и опция join
- •Оператор union
- •Подзапросы и структурированные запросы
- •Создание таблицы на основе выборки
- •Предложение for browse
- •4.3 Модификация данных
- •Добавление данных
- •Изменение данных
- •Удаление строк
- •Управляющие конструкции
- •Создание таблиц базы данных
- •4.6 Транзакции и блокировки
- •4.6.1 Понятие транзакций и блокировок
- •Управление блокировками
- •Управление транзакциями
- •Явные транзакции
- •Автоматические транзакции
- •Неявные транзакции
- •4.7 Хранимые процедуры
- •4.7.1 Типы хранимых процедур
- •Создание хранимых процедур
- •4.8 Триггеры
- •Создание триггера
- •Ограничения при создании триггеров
- •Использование триггеров
- •Вопросы на повторение
- •4.10 Упражнения и задачи
- •4.11 Проекты и профессиональные вопросы
- •Заключение
- •Приложение а sql скрпит, для создания таблиц согласно модели бд "Университет"
- •Литература
3.1.3 Специальные реляционные операторы
Выборкой (ограничением, селекцией) на отношении A с условием c называется отношение с тем же заголовком, что и у отношения A, и телом, состоящем из кортежей, значения атрибутов которых при подстановке в условие c дают значение ИСТИНА. c представляет собой логическое выражение, в которое могут входить атрибуты отношения A и (или) скалярные выражения.
В простейшем случае условие c имеет вид XY, где - один из операторов сравнения (=, <>, <, <=, >, >= и т.д.), а X и Y - атрибуты отношения A или скалярные значения. Такие выборки называются -выборки (тэта-выборки) или -ограничения, -селекции.
Синтаксис операции выборки: A WHERE c, или A WHERE XY
Пример 6. Пусть дано отношение A с информацией о сотрудниках:
Результат выборки A WHERE Стаж>1 будет иметь вид, представленный в таблице 3.10.
Таблица 3.10 - Результат тэта-выборки из отношения A
ИД_Сотр |
Фамилия |
Стаж |
1 |
Иванов |
3 |
3 |
Сидоров |
4 |
Смысл операции выборки очевиден - выбрать кортежи отношения, удовлетворяющие некоторому условию. Таким образом, операция выборки дает "горизонтальный срез" отношения по некоторому условию.
Проекцией отношения A по атрибутам X, Y,…, Z, где каждый из атрибутов принадлежит отношению A, называется отношение с заголовком (X, Y,…, Z) и телом, содержащим множество кортежей вида (x, y,…, z), таких, для которых в отношении A найдутся кортежи со значением атрибута X равным x, значением атрибута Y равным y, …, значением атрибута Z равным z.
Синтаксис операции проекции: A[X, Y,…, Z]
Примечание: операция проекции дает "вертикальный срез" отношения, в котором удалены все возникшие при таком срезе дубликаты кортежей.
Пример 7. Пусть дано отношение B с информацией о должностях (таблица 3.8)
Проекция B[Должность] будет иметь вид, показанный в таблице 3.11.
Таблица 3.11 - Результат операции проекции над таблицей B (таблица 3.8)
Должность |
Профессор |
Доцент |
Ассистент |
Операция соединения отношений является одной из наиболее важных реляционных операций и используется наиболее часто для извлечения тех или иных данных из исходных отношений. Обычно рассматривается несколько разновидностей операции соединения:
Общая операция соединения
-соединение (тэта-соединение)
Экви-соединение
Естественное соединение
Наиболее важным из всех разновидностей является операция естественного соединения. Все разновидности соединения являются частными случаями общей операции соединения.
Общая операция соединения
Соединением (общей операцией соединения) отношений A и B по условию c называется отношение
(A TIMES B) WHERE c,
где c представляет собой логическое выражение, в которое могут входить атрибуты отношений A и B, также могут присутствовать скалярные выражения.
Поэтому операция соединения есть результат последовательного применения операций декартового произведения и выборки. Если в отношениях A и B имеются атрибуты с одинаковыми наименованиями, то перед выполнением соединения такие атрибуты необходимо переименовать.
Тета-соединение
Пусть отношение A содержит атрибут X, отношение B содержит атрибут Y, а - один из операторов сравнения (=, <>, <, <=, >, >= и т.д.). Тогда -соединением отношения A по атрибуту X с отношением B по атрибуту Y называют отношение:
(A TIMES B) WHERE XY
Это частный случай операции общего соединения. Иногда, для операции -соединения применяют следующий, более короткий синтаксис: A[XY]B
Пример 8. Рассмотрим учебное заведение, в котором имеются данные о преподавателях и перечне дисциплин. За каждой дисциплиной закреплен определенный объем часов, а каждый преподаватель имеет определенную нагрузку в часах. Идея в том, что каждый преподаватель не может читать дисциплины, объем которых превышает его возможную нагрузку. Для рассматриваемого примера в таблицах 3.12 и 3.13 приведены отношения с соответствующими записями для сотрудников и дисциплин.
Таблица 3.12 Отношение A (Сотрудники) Таблица 3.13 Отношение B (Дисциплины)
ИД_Сотр |
Фамилия |
X (Нагрузка, часов) |
|
ИД_Дис |
Название дисциплины |
Y (Объем, часов) |
1 |
Иванов |
50 |
|
1 |
ЗИ |
36 |
2 |
Костров |
72 |
|
2 |
УД |
54 |
3 |
Сидоров |
34 |
|
3 |
ОС |
18 |
Ответ на вопрос "кто из сотрудников имеет право читать соответствующие дисциплины исходя из своей нагрузки?" дает -соединение A[X>=Y]B, результат выполнения которого представлен в таблице 3.14
Таблица 3.14 - Результат тэта-соединения отношения A и B (таблицы 3.12 и 3.13)
ИД_Сотр |
Фамилия |
X (Нагрузка, часов) |
ИД_Дис |
Название дисциплины |
Y (Объем часов) |
1 |
Иванов |
50 |
1 |
ЗИ |
36 |
1 |
Иванов |
50 |
2 |
ОС |
54 |
2 |
Костров |
72 |
1 |
ЗИ |
36 |
2 |
Костров |
72 |
2 |
УД |
54 |
2 |
Костров |
72 |
3 |
ОС |
18 |
3 |
Сидоров |
34 |
3 |
ОС |
18 |
Экви-соединение
Наиболее важным частным случаем -соединения является случай, когда есть просто равенство. Синтаксис экви-соединения:
A[X=Y]B
Пример 9. Пусть имеются отношения P, D и PD, хранящие информацию о лекторах, дисциплинах и количестве вычитываемых часов (для краткости записи операций были введены сокращенные наименования атрибутов), таблицы 3.15, 3.16 и 3.17 соответственно.
Таблица 3.15 - Отношение P (Сотрудники) Таблица 3.16-Отношение D (Дисциплины)
-
ИД_Сотр
PNAME (Фамилия)
ИД_Дис
Название дисциплины DNAME
1
Иванов
1
ЗИ
2
Костров
2
УД
3
Сидоров
3
ОС
Таблица 3.17 - Отношение PD (Выработка)
ИД_Сотр |
ИД_Дис |
Общий объем в часах VOLUME |
1 |
1 |
36 |
1 |
2 |
54 |
1 |
3 |
18 |
2 |
1 |
36 |
2 |
2 |
54 |
3 |
1 |
36 |
Ответ на вопрос, какие дисциплины вычитываются какими лекторами, дает экви-соединение P[ИД_Сотр=ИД_Сотр]PD. На самом деле, т.к. в отношениях имеются одинаковые атрибуты, то сначала необходимо переименовать атрибуты, а потом выполнить экви-соединение. Запись становится более громоздкой:
(P RENAME ИД_Сотр AS ИД_Сотр1)[PNUM1=PNUM2](PD RENAME PNUM AS ИД_Сотр 2)
Обычно, такой сложной формой записи не пользуются. Но как бы то ни было, в результате получится отношение, представленное в таблице 3.18
Таблица 3.18 - Результат операции экви-соединения
ИД_Сотр1 |
Фамилия |
ИД_Сотр2 |
ИД_Дис |
Общий объем в часах VOLUME |
1 |
Иванов |
1 |
1 |
36 |
1 |
Иванов |
1 |
2 |
54 |
1 |
Иванов |
1 |
3 |
18 |
2 |
Петров |
2 |
1 |
36 |
2 |
Петров |
2 |
2 |
54 |
3 |
Сидоров |
3 |
1 |
36 |
Недостатком экви-соединения является то, что если соединение происходит по атрибутам с одинаковыми наименованиями (а так чаще всего именно так и происходит), то в результирующем отношении появляется два атрибута с одинаковыми значениями. В нашем примере атрибуты ИД_Сотр1 и ИД_Сотр2 содержат дублирующие данные. Избавиться от этого недостатка можно, взяв проекцию по всем атрибутам, кроме одного из дублирующих. Именно так действует естественное соединение.
Естественное-соединение
Пусть даны отношения A(A1, A2,…,An; X1, X2, …, Xp) и B(X1, X2,…,Xp; B1, B2,…Bm), имеющие одинаковые атрибуты X1, X2,…,Xp (т.е. атрибуты с одинаковыми именами и определенные на одинаковых доменах).
Тогда естественным соединением отношений A и B называется отношение с заголовком (A1, A2, …, An, X1, X2, …, Xp, B1, B2, …, Bm) и телом, содержащим множество кортежей (a1, a2, …, an, x1, x2, …, xp, b1, b2, …, bm,), таких, что (a1, a2, …, an, x1, x2, …, xp)A и (x1, x2, …, xp, b1, b2, …, bm,)B.
Естественное соединение настолько часто используется в реляционной алгебре и в языке SQL, что для него применяют специальное обозначение: A JOIN B
Примечание: в синтаксисе естественного соединения не указываются, по каким атрибутам производится соединение, т.к соединение производится по всем одинаковым атрибутам.
Примечание: естественное соединение эквивалентно следующей последовательности реляционных операций:
переименовать одинаковые атрибуты в отношениях;
выполнить декартово произведение отношений;
выполнить выборку по совпадающим значениям атрибутов, имевших одинаковые имена;
выполнить проекцию, удалив повторяющиеся атрибуты;
переименовать атрибуты, вернув им первоначальные имена.
Примечание: можно выполнять последовательное естественное соединение нескольких отношений. Нетрудно проверить, что естественное соединение (как, впрочем, и соединение общего вида) обладает свойством ассоциативности, т.е. (A JOIN B) JOIN C=A JOIN (B JOIN C), поэтому такие соединения можно записывать опуская скобки: A JOIN B JOIN C
Пример 10. В предыдущем примере ответ на вопрос "какие дисциплины читаются лекторами", более просто записывается в виде естественного соединения трех отношений P JOIN PD JOIN D (для удобства просмотра порядок атрибутов изменен, это является допустимым по свойствам отношений). Результат выполнения такой операции представлен в таблице 3.19
Таблица 3.19 - Результат операции естественного-соединения
ИД_Сотр |
Фамилия |
ИД_Дис |
Название дисциплины DNAME |
Общий объем в часах VOLUME |
1 |
Иванов |
1 |
ЗИ |
36 |
1 |
Иванов |
2 |
УД |
54 |
1 |
Иванов |
3 |
ОС |
18 |
2 |
Петров |
1 |
ЗИ |
36 |
2 |
Петров |
2 |
УД |
54 |
3 |
Сидоров |
1 |
ЗИ |
36 |
Делением отношений A(X1, X2,…,Xn; Y1, Y2,…Ym) на B(Y1, Y2,…Ym), для которых атрибуты Y1, Y2,…Ym являются общими, называется отношение с заголовком (X1, X2,…,Xn) и телом, содержащим множество кортежей (x1, x2,…xn), таких, что для всех кортежей (y1, y2, …, ym)B в отношении A найдется кортеж (x1, x2, …, xn, y1, y2, …, ym).
Отношение A выступает в роли делимого, отношение B выступает в роли делителя. Деление отношений аналогично делению чисел с остатком.
Синтаксис операции деления: A DEVIDEBY B
Примечание: самые традиционные запросы, выражаемые с помощью операции деления, обычно в своей формулировке имеют слово "все", например: "какие сотрудники вычитывают все лекции?".
Пример 11. В примере с сотрудниками, дисциплинами и читаемыми дисциплинами ответим на вопрос, "какие сотрудники читают все дисциплины?".
В качестве делимого возьмем проекцию X=PD[ИД_Сотр, ИД_Дис], содержащую номера сотрудников и номера читаемых ими дисциплинами, см. таблицу 3.20.
Таблица 3.20 - Проекция отношения PD по атрибутам ИД_Сотр, ИД_Дис
ИД_Сотр |
ИД_Дис |
1 |
1 |
1 |
2 |
1 |
3 |
2 |
1 |
2 |
2 |
3 |
1 |
В качестве делителя возьмем проекцию Y=D[ИД_Дис], содержащую список номеров всех дисциплин (не обязательно вычитываемых кем-либо), см. таблицу 3.21.
Таблица 3.21 - Проекция отношения D по атрибутам ИД_Дис
ИД_Дис |
1 |
2 |
3 |
Деление X DEVIDE Y дает список идентификаторов (атрибут ИД_Сотр) сотрудников, которые читают все. Для рассматриваемого примера результатом деления отношения, представленного в таблице 3.20 на отношение, представленное в таблице 3.21 будет отношение в таблице 3.22.
Таблица 3.22 - Проекция отношения D по атрибутам ИД_Дис
ИД_Сотр |
1 |
Оказалось, что только сотрудник с номером 1 читает все виды лекций.
Примеры использования реляционных операторов
Пример 12. Получить имена сотрудников, читающих лекцию 2.
Решение: ((DP JOIN P) WHERE ИД_Дис=2)[PNAME]
Пример 13. Получить имена сотрудников, читающих по крайней мере лекцию «ЗИ».
Решение: (((D WHERE DNAME=ЗИ) JOIN DP) JOIN P)[PNAME]
Ответ на этот запрос можно получить и иначе:
(((D JOIN DP) JOIN P) WHERE DNAME=ЗИ)[PNAME]
Пример 14. Получить имена сотрудников, читающих все лекции.
Решение: ((DP[ИД_Сотр, ИД_Дис] DEVIDEBY D[ИД_Дис]) JOIN P)[PNAME]
Пример 15. Получить имена сотрудников, не читающих лекцию 2.
Решение: ((P[ИД_Сотр] MINUS((P JOIN DP) WHERE ИД_Дис=2)[ИД_Сотр] JOIN P)[PNAME]
Ответ на этот запрос можно получить и пошагово:
T1=P[ИД_Сотр] - получить список номеров всех сотрудников;
T2= P JOIN DP - соединить данные о сотрудниках и читаемых лекциях
T3=T2 WHERE ИД_Дис=2 - в данных о сотрудниках и читаемых лекциях оставить только данные о лекции 2.
T4=T3[ИД_Сотр] - получить список номеров сотрудников, читающих лекцию 2.
T5=T1 MIBUS T4 - получить список номеров сотрудников, не читающих лекцию 2.
T6=T5 JOIN P - соединить список номеров сотрудников, не читающих лекцию 2 с данными о сотрудниках (получатся полные данные о сотрудниках, не читающих лекцию 2).
T7=T6[P[NAME]] - искомый ответ (имена сотрудников, не читающих лекцию 2).
