Создание баз данных. Учебное пособие

Министерство образования и науки РФ Национальный исследовательский ядерный университет «МИФИ»

К. Я. Кудрявцев

СОЗДАНИЕ БАЗ ДАННЫХ Учебное пособие

Москва 2010

УДК (004.738.5(075) ББК (32.973.202я7) К88 Кудрявцев К.Я. Создание баз данных. Учебное пособие. М.: НИЯУ МИФИ, 2010.– 155 с. Рассмотрены теоретические вопросы проектирования баз данных, особенности применения редактора ERWin, приведено большое количество практических примеров построения концептуальных моделей (ER-диаграмм) различных предметных областей. Представлены реляционная модель данных, нормализация отношений, математический аппарат реляционной алгебры и реляционного исчисления. Описаны подходы к созданию сложных интерфейсов (форм) для работы с данными, а также структура семейств и объектов для манипулирования данными с помощью VBA в СУБД MS Access. Рассмотрены особенности архитектуры, системы безопасности и механизмы аутентификации и авторизации в клиент-серверных СУБД MySQL и PostgreSQL. Дано описание программного интерфейса для соединения и доступа к серверу базы данных. Приведены примеры сеансов работы по созданию базы данных. Описана структура языка SQL, подробно рассматривается построение сложных SQL-запросов. Предназначено для студентов, обучающихся по специальности 010501 – «Прикладная математика и информатика», изучающих курс «Базы данных и экспертные системы» (ОПД.Ф.11), а также всех желающих узнать больше о технологии разработки баз данных.

Рецензент В.С. Лаврентьев ISBN 978-5-7262-1302-6

Рекомендовано к изданию редсоветом НИЯУ МИФИ в качестве учебного пособия

© Национальный исследовательский ядерный университет «МИФИ», 2010 г.

2

ОГЛАВЛЕНИЕ ВВЕДЕНИЕ ...................................................................................................... 7 Глава 1. КОНЦЕПТУАЛЬНОЕ МОДЕЛИРОВАНИЕ .......................... 10 ТИПЫ МОДЕЛЕЙ ДАННЫХ ....................................................................... 10 Иерархическая модель данных ........................................................ 12 Сетевая модель данных ................................................................... 14 Реляционная модель данных ............................................................ 15 ПОСТРОЕНИЕ КОНЦЕПТУАЛЬНОЙ МОДЕЛИ ............................................ 15 CASE-СРЕДСТВА ПОСТРОЕНИЯ КОНЦЕПТУАЛЬНЫХ МОДЕЛЕЙ .............. 18 СОЗДАНИЕ ER-ДИАГРАММ С ПОМОЩЬЮ ERWIN .................................. 21 РЕЛЯЦИОННЫЕ СХЕМЫ И ER-ДИАГРАММЫ ........................................... 29 ПРИМЕРЫ ER-ДИАГРАММ ...................................................................... 34 ER-диаграмма «Обменный пункт валюты» .................................. 34 ER-диаграмма «Родственные отношения» ................................... 35 ER-диаграмма «Теннисный турнир» .............................................. 36 ER-диаграмма «Библиотека» ......................................................... 37 КОНТРОЛЬНЫЕ ВОПРОСЫ ....................................................................... 39 Глава 2. РЕЛЯЦИОННАЯ МОДЕЛЬ ДАННЫХ .................................. 40 БАЗОВЫЕ ПОНЯТИЯ РЕЛЯЦИОННОЙ МОДЕЛИ ДАННЫХ .......................... 41 Тип данных ........................................................................................ 42 Связанные отношения ..................................................................... 46 Внешние ключи отношения ............................................................. 47 Условия целостности данных ......................................................... 48 Типы связей между таблицами ...................................................... 50 РЕЛЯЦИОННАЯ АЛГЕБРА И РЕЛЯЦИОННОЕ ИСЧИСЛЕНИЕ ....................... 51 РЕЛЯЦИОННАЯ АЛГЕБРА ......................................................................... 52 Операции реляционной алгебры ...................................................... 52

3

Теоретико-множественные операции реляционной алгебры ...... 54 Специальные реляционные операции .............................................. 55 РЕЛЯЦИОННОЕ ИСЧИСЛЕНИЕ.................................................................. 58 Целевые списки и выражения реляционного исчисления .............. 62 Реляционное исчисление доменов .................................................... 62 НОРМАЛИЗАЦИЯ ОТНОШЕНИЙ ............................................................... 64 Аномалия обновления ....................................................................... 64 Аномалия удаления ........................................................................... 65 Аномалия ввода ................................................................................. 65 Первая нормальная форма (1НФ) ................................................... 65 Функциональные зависимости ........................................................ 66 Вторая нормальная форма (2НФ) .................................................. 68 Третья нормальная форма (3НФ) .................................................. 69 Многозначные зависимости ............................................................ 71 КОНТРОЛЬНЫЕ ВОПРОСЫ ....................................................................... 73 Глава 3. РАБОТА с MS ACCESS ............................................................... 74 АРХИТЕКТУРА ПРИЛОЖЕНИЯ MS ACCESS.............................................. 74 Обобщенная модель ядра приложения ........................................... 74 Модель Data Access Object ............................................................... 76 Ссылки на семейства, объекты и свойства .................................. 78 Работа с объектными переменными ............................................. 79 ПРАКТИЧЕСКИЕ АСПЕКТЫ РАБОТЫ С MS ACCESS.................................. 80 Динамическое изменение «Поля со списком» ................................ 80 Динамическое изменение «Текстового поля» ............................... 83 Параметрические запросы в формах. ............................................ 84 Отображение взаимосвязанной информации ............................... 85 Многопараметрические запросы .................................................... 89 КОНТРОЛЬНЫЕ ВОПРОСЫ ....................................................................... 92

4

Глава 4. КЛИЕНТ-СЕРВЕРНЫЕ СУБД .................................................. 93 СУБД MYSQL ....................................................................................... 95 Запуск и работа с MySQL ................................................................ 95 Структура хранения данных в MySQL........................................... 97 Система безопасности MySQL ...................................................... 98 СУБД POSTGRESQL ............................................................................. 103 Запуск и работа с PostgreSQL ...................................................... 104 Структура хранения данных в PostgreSQL ................................. 105 Система безопасности PostgreSQL ............................................. 106 ПРОГРАММНЫЙ ИНТЕРФЕЙС ДЛЯ РАБОТЫ С СЕРВЕРОМ БД ................. 108 API для MySQL ................................................................................ 108 API для PostgreSQL......................................................................... 110 ТРАНЗАКЦИИ ........................................................................................ 113 ХРАНИМЫЕ ПРОЦЕДУРЫ ....................................................................... 117 ТРИГГЕРЫ ............................................................................................. 121 КОНТРОЛЬНЫЕ ВОПРОСЫ ..................................................................... 123 Глава 5. ЯЗЫК SQL.................................................................................... 124 CТРУКТУРА ЯЗЫКА SQL ....................................................................... 124 ОПРЕДЕЛЕНИЕ СХЕМЫ ДАННЫХ. ОПЕРАТОР CREATE ........................ 126 ВЫБОРКА ДАННЫХ. ОПЕРАТОР SELECT ............................................. 129 ТАБЛИЧНОЕ ВЫРАЖЕНИЕ ОПЕРАТОРА SELECT .................................. 131 Раздел FROM .................................................................................. 132 Раздел WHERE ................................................................................ 132 Раздел GROUP BY .......................................................................... 136 Раздел HAVING ............................................................................... 136 Агрегатные функции ...................................................................... 137 ПРИМЕРЫ ПОСТРОЕНИЯ SQL-ЗАПРОСОВ ............................................. 139 База данных туристических поездок ........................................... 139

5

Запрос 1 ........................................................................................... 140 Запрос 2 ........................................................................................... 142 Запрос 3 ........................................................................................... 144 Запрос 4 ........................................................................................... 145 Запрос 5 ........................................................................................... 145 Запрос 6 ........................................................................................... 146 Запрос 7 ........................................................................................... 148 Запрос 8 ........................................................................................... 149 Запрос 9 ........................................................................................... 150 КОНТРОЛЬНЫЕ ВОПРОСЫ ..................................................................... 153 СПИСОК РЕКОМЕНДУЕМОЙ ЛИТЕРАТУРЫ ................................. 154

6

ВВЕДЕНИЕ Развитие компьютерных технологий, связанных с хранением и обработкой данных, привело к появлению с середины 1960-х гг. понятия базы данных и специализированного программного обеспечения, получившего название систем управления базами данных (СУБД) — DataBase Management Systems, (DBMS) [1]. База данных (БД) — это поименованная совокупность структурированных данных, относящихся к определенной предметной области. По технологии обработки данных базы данных подразделяются на централизованные и распределенные (клиентсерверные). СУБД — это комплекс программных и языковых средств, необходимых для создания баз данных, поддержания их в актуальном состоянии и организации поиска в них необходимой информации. СУБД позволяют структурировать, систематизировать и организовывать данные для их компьютерного хранения и обработки. Именно системы управления базами данных являются основой практически любой информационной системы. Основные функции СУБД: ▪ физическое размещение в памяти данных и их описаний; ▪ реализация механизмов поиска запрашиваемых данных; ▪ разрешение проблем, возникающих при одновременном запросе одних и тех же данных многими пользователями (прикладными программами); ▪ обеспечения защиты данных от некорректных обновлений и (или) несанкционированного доступа; ▪ поддержания баз данных в актуальном состоянии; ▪ обеспечение целостности и непротиворечивости данных. СУБД можно определить как некую систему управления данными, обладающую следующими свойствами: ▪ поддержание логически согласованного набора файлов; ▪ обеспечение языка манипулирования данными; ▪ восстановление информации после разного рода сбоев; ▪ обеспечение одновременной работы нескольких пользователей. В истории развития и совершенствования систем управления базами данных можно выделить три основных этапа. 7

Иерархические и сетевые СУБД Первый этап был связан с созданием первого поколения СУБД, опиравшихся на иерархическую и сетевую модели данных (на основе спецификаций CODASYL). В этот период времени на рынке вычислительной техники доминировали большие вычислительные машины (mainframe), такие как система IBM 360/370, которые в совокупности с СУБД первого поколения составили аппаратнопрограммную платформу больших информационных систем. Реляционные СУБД Началом второго этапа в эволюции СУБД можно считать публикации в начале 70-х годов ряда статей Э. Кодда, в которых выдвигались, по сути, революционные идеи, существенно изменившие устоявшиеся представления о базах данных. Кодд предложил использовать для обработки данных аппарат теории множеств (реляционную алгебру и реляционное исчисление). Одна из главных идей Кодда заключалась в том, что связь между данными должна устанавливаться в соответствии с их внутренними логическими взаимоотношениями, в отличие от иерархических и сетевых СУБД, где для связи записей из разных файлов использовались физические указатели. Это означало, что в том случае, когда в разных файлах хранится логически связанная информация, а физическая связь между этими файлами отсутствует, то для получения выборки (извлечения информации) из такой базы данных необходимо использовать низкоуровневые средства работы с файлами. В настоящее время реляционные базы данных получили очень широкое распространение, и фактически их можно рассматривать как стандарт СУБД для современных информационных систем. Объектно-ориентированные СУБД Развитие реляционных баз данных и обеспечение возможностей решения более сложных задач привели к появлению объектноориентированных баз данных. Для них характерны использование идей объектно-ориентированного подхода, управления распределенными базами данных, активного сервера базы данных, языков программирования четвертого поколения, фрагментации и парал8

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

9

Глава 1. КОНЦЕПТУАЛЬНОЕ МОДЕЛИРОВАНИЕ Типы моделей данных В широком смысле слова база данных — это совокупность сведений о конкретных объектах реального мира в какой-либо предметной области [1]. Под предметной областью принято понимать часть реального мира, подлежащего изучению для организации управления и, в конечном счете, автоматизации, например, предприятие, вуз и т.д. Создавая базу данных, пользователь стремится упорядочить информацию по различным признакам и быстро извлекать данные с произвольным сочетанием признаков. Сделать это возможно, только если данные структурированы. Структурирование — это введение соглашений о способах представления данных. Неструктурированными называют данные, записанные, например, в текстовом файле. На рис. 1.1 представлен пример неструктурированных данных, содержащих сведения о студентах (номер личного дела, фамилию, имя, отчество и год рождения). Легко убедиться, что сложно организовать поиск необходимых данных, хранящихся в неструктурированном виде, а упорядочить подобную информацию практически не представляется реальным. Личное дело № 16493, Сергеев Петр Михайлович, дата рождения 1 января 1986 г. Л/д № 16593, Петрова Анна Владимировна, дата рожд. 15 марта 1985 г. № личн. дела 16693, д.р. 14.04.86, Анохин Андрей Борисович. Рис.1.1. Пример неструктурированных данных

10

Чтобы автоматизировать поиск и систематизировать эти данные, необходимо выработать определенные соглашения о способах представления данных, т.е. дату рождения нужно записывать одинаково для каждого студента, она должна иметь одинаковую длину и определенное место среди остальной информации. Эти же замечания справедливы и для остальных данных (номер личного дела, фамилия, имя, отчество). После проведения несложной структуризации информации, указанной на рис. 1.1, она будет выглядеть так, как это показано на рис. 1.2. № Личного дела 16493 16593 16693

Фамилия

Имя

Отчество

Сергеев Петрова

Петр Анна

Михайлович Владимировна

Дата рождения 01.01.86 01.03.85

Анохин

Андрей

Борисович

14.04.86

Рис. 1.2. Пример структурированных данных Ядром любой базы данных является модель данных. Модель данных представляет собой множество структур данных, ограничений целостности и операций манипулирования данными. С помощью модели данных могут быть представлены объекты предметной области и взаимосвязи между ними. Модель данных — совокупность структур данных и операций их обработки. СУБД основывается на использовании сетевой, иерархической или реляционной модели, на комбинации этих моделей или на некотором их подмножестве. Наиболее распространенными являются три типа моделей данных: ▪ иерархическая; ▪ сетевая; ▪ реляционная. Иерархические и сетевые СУБД наиболее эффективны в производственных системах, где структура данных известна заранее и не подлежит существенным изменениям. В то же время в системах с произвольной структурой запросов целесообразно применять реляционные СУБД. 11

Иерархическая модель данных Иерархическая модель данных [1, 2] – модель данных, в которой все отношения структурированы в виде деревьев. Иерархическая структура представляет совокупность элементов, связанных между собой по определенным правилам. Объекты, связанные иерархическими отношениями, образуют ориентированный граф (перевернутое дерево), вид которого представлен на рис. 1.3.

Рис. 1.3. Графическое изображение иерархической модели данных Среди реализаций иерархической модели преобладает система IMS (Information Management System), которая появилась в результате совместной работы фирм IBM и Rockwell по созданию СУБД для поддержки лунного проекта Аполлон. В рамках данного проекта необходимо было хранить информацию о миллионах деталей космического аппарата связанных друг с другом иерархическим образом. К основным понятиям иерархической структуры относятся: ▪ уровень; ▪ элемент (узел); ▪ связь. Узел (элемент) — это совокупность атрибутов данных, описывающих некоторый объект. На схеме иерархического дерева узлы представляются вершинами направленного графа. Каждый узел на 12

более низком уровне связан только с одним узлом, находящимся на более высоком уровне. Иерархическое дерево имеет только одну вершину (корень дерева), не подчиненную никакой другой вершине и находящуюся на самом верхнем (первом) уровне. Зависимые (подчиненные) узлы находятся на втором, третьем и т.д. уровнях. Количество деревьев в базе данных определяется числом корневых записей. Узел, у которого нет потомков, называется листовым. К каждой записи базы данных существует только один (иерархический) путь от корневой записи. Факультет (специальность, название, декан) 010501 Кибернетики Панферов В.В.

Группа (номер, староста) К6-361 Петров И.Т.

К7-368 Зайцев Р.В.

К7-361 Никулин К.Л.

Студент (номер зачетной книжки, фамилия, имя, отчество)

98795

97695

98495

Сидоров Андрей Петрович

Черняева Юлия Николаевна

Дроздов Константин Иванович

Рис. 1.4. Пример иерархической структуры БД

13

Сетевая модель данных В сетевой структуре при тех же основных понятиях (уровень, узел, связь) каждый элемент может быть связан с любым другим элементом. На рис. 1.5 изображена сетевая структура базы данных в виде графа. Примером сложной сетевой структуры может служить структура базы данных, содержащей сведения о студентах, участвующих в научно-исследовательских работах (НИРС). Возможно участие одного студента в нескольких НИРС, а также участие нескольких студентов в разработке одной НИРС.

Рис. 1.5. Графическое изображение сетевой структуры Студент (номер зачетной книжки, фамилия, группа) 87695 Иванов 111

1006 Сергеев П.И. Информатика

85495 Петров 112

1009 Некрасова Г.П. Математика

87495 Сидоров 113

1008 Кириллов В.П. Физика

Работа (шифр, руководитель, область) Рис. 1.6. Пример сетевой структуры БД 14

1005 Павлова И.М. Химия

Реляционная модель данных В реляционной модели данные представляются в виде совокупности двумерных таблиц. Связь между данными устанавливается в соответствии с их внутренними логическими взаимоотношениями, а не с помощью физических указателей или адресов на диске. Извлечение информации из базы данных выполняется с помощью специального математического аппарата на основе логических связей, и при работе с базой данных нет необходимости напрямую программировать работу с файлами. Более подробно реляционная модель данных будет рассмотрена во второй главе.

Построение концептуальной модели Базы данных и программные средства для их создания и ведения (СУБД) имеют многоуровневую архитектуру (рис. 1.7). В соответствии с рекомендациями ANSI/X3/SPARC [9] различают внутренний, концептуальный и внешний уровни представления данных баз данных [1, 2]. Концептуальный уровень соответствует логическому аспекту представления данных предметной области в интегрированном виде. Концептуальная модель состоит из множества экземпляров различных типов данных, структурированных в соответствии с требованиями СУБД к логической структуре базы данных. Внутренний уровень отображает требуемую организацию данных в среде хранения и соответствует физическому аспекту представления данных. Внешний уровень поддерживает частные представления данных, требуемые конкретным пользователям. Внешняя модель является подмножеством концептуальной модели. С помощью внешних моделей поддерживается санкционированный доступ к данным БД приложений (ограничен состав и структура данных концептуальной модели БД, доступных в приложении, а также заданы допустимые режимы обработки этих данных: ввод, редактирование, удаление, поиск).

15

Приложение

Приложение

Приложение

1

2

3

Внешняя модель А

Внешняя модель В

Логический уровень представления данных

Концептуальная модель БД

Внутренняя модель БД Физический уровень представления данных

Рис. 1.7. Многоуровневое представление данных БД Проектирование базы данных состоит в построении комплекса взаимосвязанных объектов данных [10]. Проект базы данных надо начинать с анализа предметной области и выявления требований к ней отдельных пользователей (сотрудников организации, для которых создается база данных). Разработчик базы данных сначала создает обобщенное неформальное описание создаваемой базы данных. Это описание, выполненное с использованием естественного языка, математических формул, таблиц, графиков и других средств, понятных всем людям, работающих над проектированием базы данных, называют концептуальной (инфологической) моделью данных. В концептуальной модели средствами структур данных в интегрированном виде отражают состав и структуру данных, а также информационные потребности приложений (задач и запросов). Такая модель полностью независима от физических параметров среды хранения данных. 16

Концептуальная модель не должна изменяться до тех пор, пока какие-то изменения в реальном мире не потребуют изменения в ней некоторого определения, чтобы эта модель продолжала отражать предметную область. Концептуальная (инфологическая) модель предметной области отражает предметную область в виде совокупности информационных объектов и их структурных связей. На рис. 1.8 условно отображены этапы процесса проектирования базы данных. Анализ предметной области Проектирование базы данных Концептуальное

Физическое

Проектирование

проектирование

проектирование

представлений данных для приложений

Рис. 1.8. Этапы процесса проектирования базы данных Предварительная концептуальная модель строится на предпроектной стадии и затем уточняется на более поздних стадиях проектирования баз данных. На основе концептуальная модели строятся внутренняя (физическая) и внешняя модели представления данных. Рассмотрим в качестве примера предметную область, посвященную экзаменам, которые сдают студенты. Как известно, экзамены сдаются по разным предметам, их принимают разные преподаватели, и результаты экзамена заносятся в экзаменационную ведомость. На основе вышеизложенного можно выделить следующие объекты («сущности»), участвующие в данной предметной области: ▪ студент; ▪ преподаватель; ▪ экзаменационный предмет; ▪ собственно экзамен (экзаменационная ведомость). Следует отметить, что в данном случае не учитываются вопросы, на которые отвечал студент, длительность подготовки по билету и т.п. Таким образом, предметная область ограничена только конечными итогами экзамена. Удобно представлять объекты 17

предметной области и их взаимосвязи в графическом виде. Так на рис. 1.9 представлена графическая форма концептуальной модели, связывающей информационные объекты (сущности): Экзамен, Студент, Предмет, Преподаватель. Экзамен

Студент

Предмет

Преподаватель

Рис. 1.9. Пример графического представления инфологической модели Существуют специальные программные средства (CASE средства) моделирования предметных областей и автоматической генерации модели данных в зависимости от выбранной СУБД.

CASE-средства построения концептуальных моделей Одной из наиболее популярных форм представления концептуальных моделей данных является модель «сущность—связь» (часто называемая также ER-моделью (ER-диаграммой) — по первым буквам английских слов Entity (сущность) и Relation (связь)). Модель была предложена Ченом в 1976 г. Кроме модели Чена известны другие модели: семантическая модель данных Хаммера и Маклеода; функциональная модель Шипмана и др. Моделирование предметной области базируется на использовании графических диаграмм, включающих небольшое число разнородных компонентов. В связи с наглядностью представления концептуальных моделей, ER-диаграммы получили широкое распространение в CASE (Computer-Aided System Engineering)-средствах, предназначенных для автоматизированного проектирования реляционных баз данных. Для моделирования структуры данных используются ERдиаграммы (диаграммы «сущность—связь»), которые в наглядной 18

форме представляют связи между сущностями. Наиболее популярными являются CASE-системы ERwin, Design/IDEF, Power Designer, в которых диаграммы создаются в соответствии со стандартом IDEF1X. Основные понятия ER-диаграммы: ▪ сущность; ▪ атрибут; ▪ связь. Построение ER-диаграммы предполагает определение сущностей предметной области, атрибутов этих сущностей и установление связей между ними. Сущность – любой различимый объект (объект, который мы можем отличить от другого), информацию о котором необходимо хранить в базе данных. Сущность — это реальный или виртуальный объект, имеющий существенное значение для рассматриваемой предметной области, информация о котором подлежит хранению. Сущностями могут быть люди, места, самолеты, рейсы, вкус, цвет и т.д. Необходимо различать такие понятия, как «тип сущности» и «экземпляр сущности». Понятие «тип сущности» относится к набору однородных личностей, предметов, событий или идей, выступающих как целое. Экземпляр сущности относится к конкретной вещи в наборе. Например, типом сущности может быть ГОРОД, а экземпляром – Москва, Киев и т.д. Каждая сущность должна обладать следующими свойствами: ▪ иметь уникальный идентификатор; ▪ содержать один или несколько атрибутов, которые либо принадлежат сущности, либо наследуются через связь с другими сущностями; ▪ содержать совокупность атрибутов, однозначно идентифицирующих каждый экземпляр сущности. Любая сущность может иметь произвольное количество связей с другими сущностями. Атрибут является характеристикой сущности, значимой для рассматриваемой предметной области. Атрибут – поименованная характеристика сущности. Следует отметить, что определяя атрибуты сущности мы наполняем ее смыслом и тем самым уточняем определение сущности. Сущности должны иметь наименование с четким смысловым зна19

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

называемой сущностью-потомком, а каждый экземпляр сущностипотомка ассоциирован в точности с одним экземпляром сущности-родителя. Связь является логическим соотношением между сущностями и должна именоваться глаголом или глагольной фразой. Каждая связь имеет определенную мощность, которая определяет максимальное количество элементов одного объектного множества (сущности), связанных с одним элементом другого объектного множества (сущности).

Создание ER-диаграмм с помощью ERWin В [3] дано подробное описание работы с редактором ERWin. Рассмотрим основные моменты работы с редактором на примере создания базы данных учета успеваемости студентов. При запуске ERWin (версия 7.2) появляется окно (рис. 1.10)

Рис. 1.10. Начальное окно запуска ERWin 7.2 Выбрав пункт меню FileNew или пиктограмму «Create model», появится окно выбора типа модели (рис. 1.11). Рекомендуется выбрать тип Logical/Physical и в качестве целевой СУБД указать Access версии 2000/2002/2003. 21

Рис. 1.11. Окно выбора типа модели Указание типа Logical/Physical позволит в последствии переключаться между различными представлениями ER-диаграмм. Логический тип дает более общее представление (не учитывается особенность задания типа данных, разрешаются связи «много ко многим»), и на начальном этапе разработку ведут на логическом уровне и только потом переходят на физический более детальный уровень. Следует отметить, что в последствии можно будет легко изменить целевую СУБД. ERWin поддерживает большое количество СУБД и чем выше версия ERWin, тем большее количество СУБД поддерживается. Нажатие на кнопку «Ok» приводит к появлению главного окна ERWin, в котором собственно и осуществляется построение ER-диаграммы. На рис. 1.12 специально выделено окно элементов управления ERwin Toolbox, с помощью которого и происходит построение диаграмм. В данном Toolbox основными элементами являются «Сущность» (Entity), представляющая собой прямоугольник с разделительной горизонтальной чертой и два типа связей (Relationship) «один ко многим» и «много ко многим». Итак, начнем создание ER-диаграммы с сущности «Student» (СТУДЕНТ). 22

Рис. 1.12. Главное окно ERwin (с выделенным ERwin Toolbox) Для этого выделим мышью пиктограмму «Сущность» (Entity), поместим ее на рабочее поле и вместо названия по умолчанию E/1 дадим название Student. Результат изображен на рис. 1.13. Горизонтальная черта внутри сущности предназначена для отделения ключевых атрибутов от не ключевых. Далее необходимо перейти к наполнению сущности атрибутами, причем для каждого атрибута требуется выбрать его тип, в зависимости от типа данных, которые содержит атрибут. В левой части экрана имеется вкладка Model содержащая группу базовых типов данных (Domains): ▪ <default>; ▪ Blob; ▪ Datetime; ▪ Number; ▪ String. В качестве ключевого атрибута выберем № студенческого билета (N_Student), а в качестве типа данных – Number. Для того чтобы создать такой атрибут, укажем мышью на тип Number и перетащим его в верхнюю часть сущности Student.

23

Рис. 1.13. Создание сущности Student Далее следует переименовать этот атрибут, так как по умолчанию он будет иметь имя Number. Для этого необходимо дважды щелкнуть мышью по имени атрибута, и появится окно для редактирования свойств атрибутов (рис. 1.14).

Рис. 1.14. Окно редактирования свойств атрибутов 24

Для именования сущностей и атрибутов лучше использовать латинские буквы. Аналогичным образом поместим атрибуты Фамилия Имя Отчество (FIO) типа String и Дата рождения (Date_of_birth) типа Datetime. В результате получим сущность Student, изображенную на рис. 1.15.

Рис. 1.15. Создание сущности Student Далее следует создать сущности Exam (Экзамен), Teacher (Преподаватель) и Subject (Предмет). Располагать сущности в окне редактора можно в произвольном порядке. Результат представлен на рис. 1.16. После создания сущностей необходимо установить связи между ними. Мощность связи определяется на основе логического анализа соотношений элементов сущностей. Рассмотрим связь между сущностями Student и Exam. Каждую связь необходимо анализировать в двух направлениях, сначала от Student к Exam и затем от Exam к Student. Итак, выберем отдельный элемент из сущности (множества) Student и определим, сколько элементов из сущности Exam могут быть с ним связаны. Другими словами — сколько экзаменов может сдавать один студент. Так как каждый студент может сдавать много экзаменов (в том числе пересдавать некоторые экзамены), то мощность связи в направлении Student  Exam равна «много».

25

Проанализируем мощность связи в направлении Exam  Student. Выбрав один элемент из сущности (множества) Exam, который можно представить как строку из экзаменационной ведомости,

Рис. 1.16. Создание сущностей Student, Exam, Subject, Teacher нетрудно установить, что он связан только с одним элементом из сущности (множества) Student. Таким образом, мощность связи в направлении Exam  Student равна «один». Для установления указанной связи необходимо нажать на кнопку на панели инструментов, которая соответствует идентифицирующей связи «один ко многим». Далее следует указать мышью на сущность с мощностью связи равной «один» и затем на сущность с мощностью связи равной «много». Результат представлен на рис. 1.17. Следует обратить внимание, что при установке связи в сущности Exam автоматически появляется новый атрибут N_Student(FK), который отражает логическую взаимосвязь этих сущностей и называется «внешним ключом» (Foreign Key). Внешний ключ появляется в списке атрибутов сущности над горизонтальной чертой, т.е. в области ключевых атрибутов. Внешний ключ может принимать только значения равные значениям ключевого атрибута связанной сущности или быть пустым. Кроме того, сущность Exam теперь изображается прямоугольником со скругленными углами, что го26

ворит о том, что она является дочерней, зависимой сущностью. Различают зависимые и независимые сущности. Тип сущности определяется ее связью с другими сущностями. В данном случае Student является независимой родительской сущностью, а Exam — зависимой дочерней.

Рис. 1.17. Установка связи между Student и Exam Аналогично анализируем и устанавливаем связи между сущностями Exam и Subject, Exam и Teacher. Результат представлен на рис. 1.18. Каждая связь именуется глаголом или глагольной фразой. Например, связь Exam — Student может быть описана так: «экзамен сдается студентом» (при анализе в направлении Exam  Student) и «студент сдает экзамен» (при анализе в направлении Student  Exam). Для именования связи следует дважды щелкнуть по ней мышью, в результате чего появится окно (рис. 1.19), в котором можно ввести глаголы или глагольные фразы, а также уточнить мощность связи с помощью группы селективных кнопок Cardinality. Для отображения глагольных фраз на диаграмме следует щелкнуть правой кнопкой мыши в любом свободном месте диаграммы и из контекстного меню выбрать: 27

Relationship Display Verb Phrase. Однако на практике глагольные фразы отображаются редко, поскольку загромождают ERдиаграмму.

Рис. 1.18. Установка связей между всеми сущностями

Рис. 1.19. Окно именования и установки мощности связи 28

Неидентифицирующая связь «один ко многим» отличается от идентифицирующей тем, что при ее установке новый атрибут появляется в списке атрибутов сущности под горизонтальной чертой, т.е. в области неключевых атрибутов. Следовательно, этот атрибут может принимать любые значения, а не только равные значениям ключевого атрибута связанной сущности. Сущности изображаются обычными прямоугольниками (без скругленных углов), что говорит о том, что они являются независимыми. Связь «много ко многим» требуется преобразовать к двум связям «один ко многим» с использованием специальной «ассоциированной сущности» («association entity»). Для преобразования требуется выделить связь «много ко многим», нажать правую кнопку мыши и выбрать пункт меню «Create Association Entity». Будет запущен мастер, который предложит ввести имя «ассоциированной сущности» и выполнит все необходимые действия. После того как ER-диаграмма построена, она легко преобразуется в реляционную схему базы данных, причем учитываются особенности задания типов данных выбранной СУБД.

Реляционные схемы и ER-диаграммы Процесс преобразования типовых элементов ER-диаграмм к схеме реляционной базы данных носит довольно формальный характер. Каждая сущность (объектное множество) преобразуется в реляционное отношение (реляцию), причем атрибутами реляции будут атрибуты сущности. Ключевые атрибуты сущности становятся ключами реляционной таблицы, типы атрибутов сущностей преобразуются в соответствующие типы реляции. Логические взаимосвязи между сущностями обеспечиваются с помощью внешних ключей (Foreign Key). Кроме того, задаются ограничения на таблицы с целью поддержки целостности данных. Значения по умолчанию учитываются при описании реляционной схемы базы данных, а правила задания некоторых атрибутов используются для создания триггерных процедур. Рассмотрим процедуру генерации реляционной схемы данных на примере СУБД MS Access 2003. Данную процедуру можно представить в виде последовательности нескольких этапов. 29

Этап 1. Создать пустую базу данных (Exams.mdb) и выбрать пункт меню: Сервис  Защита  Пользователи и группы… При этом появится окно, изображенное на рис. 1.20. Следует убедиться в наличие пользователя Admin (он должен быть по умолчанию). Если данного пользователя нет, то следует его добавить. Далее следует выбрать пункт меню: Сервис  Защита  Задать пароль базы данных… При этом появится окно задания пароля базы данных. Можно задать любой, в том числе и пустой пароль. После этого базу данных Exams.mdb следует обязательно закрыть (собственно MS Access 2003 можно не закрывать).

Рис. 1.20. Окно управления пользователями и группами в MS Access 2003 Этап 2. Открыть в ERWin созданную ранее ER-диаграмму учета успеваемости студентов и перейти на «физический» уровень представления данных (рис. 1.21).

30

Рис. 1.21. Переход на «физический» уровень представления данных При выборе пункта меню: Database  Choose database… появится окно выбора целевой СУБД (рис. 1.22), в качестве которой используется MS Access 2000/2002/2003.

Рис. 1.22. Выбор целевой СУБД Этап 3. Создать реляционную схему данных путем выбора пункта меню: Tools  Forward Engineer  Shema Generation… В этом случае появляется окно генерации реляционной схемы (рис. 1.23). 31

Рис. 1.23. Окно генерации реляционной схемы При нажатии на кнопку «Generate…» появится окно соединения с ядром СУБД MS Access, в котором следует указать (рис. 1.24): User Name: Admin Password: <Пусто> Database: D:\Books\Exams.mdb System Database: D:\Books\Exams.mdb (место расположения на диске базы данных Exams.mdb)

Рис. 1.24. Окно соединения с ядром СУБД MS Access

32

После заполнения всех указанных полей следует нажать на кнопку «Connect» и произойдет генерация реляционной схемы данных. Результаты генерации выводятся в виде отчета, который можно впоследствии проанализировать. Этап 4. Открыть в MS Access файл Exams.mdb и убедиться в том что там появились четыре таблицы соответствующие четырем сущностям ER-диаграммы (рис. 1.25).

Рис. 1.25. База данных Exams.mdb На рис. 1.26 представлена схема данных, используемая в MS Access. Сравнивая ее с ER-диаграммой на рис. 1.21, легко обнаружить полную аналогию.

Рис. 1.26. Схема данных Exams.mdb Таким образом, описан полный цикл проектирования структуры базы данных, начиная от анализа предметной области, создания инфологической модели, ER-диаграммы и заканчивая генерацией реляционной схемы данных.

33

Примеры ER-диаграмм Рассмотрим примеры построения ER-диаграмм для некоторых предметных областей. ER-диаграмма «Обменный пункт валюты» Рассмотрим обменный пункт валюты крупного банка, который обеспечивает обмен разного вида валют (доллары США, евро, фунты стерлингов, японские йены, рубли и т.д.). В базе данных должна храниться информация: кто и когда произвел обмен одной валюты на другую и в каком количестве. Анализ предметной области показывает, что должны быть следующие сущности: ▪ клиент; ▪ валюта; ▪ валютная операция; ▪ курсы валют. ER-диаграмма представлена на рис. 1.27.

Рис. 1.27. ER-диаграмма обменного пункта валют

34

Здесь следует обратить внимание на то, что между сущностями «Валютная операция» и «Валюта» установлено два отношения «один ко многим», так как в валютной операции участвует два вида валют. Для того чтобы отличить идентификатор входящей валюты от идентификатора исходящей, используется понятие «имя роли», которое является синонимом атрибута внешнего ключа. Для задания имени роли следует дважды щелкнуть соответствующей связи, в результате чего появится окно аналогичное рис. 1.19, в котором следует перейти на вкладку Rolename и ввести в соответствующее поле имя роли (на рис. 1.28 Rolename: ID_Valute_OUT). Для отображения имени роли на диаграмме следует щелкнуть правой кнопкой мыши в любом свободном месте диаграммы и из контекстного меню выбрать: Entity Display  Rolename Attribute. В сущности «Курс валют» хранится информация о изменениях курсов валют по отношению к некоторой базовой валюте, как правило, к доллару США.

Рис. 1.28. Задание имени роли внешнего ключа ER-диаграмма «Родственные отношения» Рассмотрим генеалогическое дерево большой семьи, например династии Романовых. 35

В базе данных должна храниться информация о родственных отношения членов большой царской семьи. Каждый из членов семьи может выступать в разных ипостасях, будучи, например, одновременно сыном, мужем, отцом, братом и т.д. Анализ предметной области показывает, что должны быть следующие сущности: ▪ Родственник (член семьи); ▪ Тип родства; ▪ Родственное отношение. ER-диаграмма представлена на рис. 1.29. Для учета родственных отношений используются два поля ID_Type_Young_Old и ID_Type_Old_Young, в которых содержится отношение между младшим и старшим родственниками (например, сын) и между старшим и младшим родственниками (например, отец). Здесь, как и в предыдущем примере, используется понятие «имя роли».

Рис. 1.29. ER-диаграмма родственных отношений ER-диаграмма «Теннисный турнир» Рассмотрим теннисный турнир, например «Кубок Кремля». В базе данных должна храниться информация о результатах сыгран36

ных матчей. Для простоты будем учитывать только итоговый результат, а результаты по сетам и тем более по геймам не учитываем. Анализ предметной области показывает, что должны быть следующие сущности: ▪ Игрок (теннисист); ▪ Игра; ▪ Стадия игры (1/8, 1/4, 1/2, Финал). ER-диаграмма представлена на рис. 1.30. Для учета пары игроков используются атрибуты ID_Gamer1 и ID_Gamer2, а для учета результата — Ball1 и Ball2. Кроме того, существует атрибут Winner, который принимает значение или 1 или 2, в зависимости от того, кто выиграл: первый или второй игрок. Однако это поле является избыточным, так как победитель определяется по анализу пары атрибутов Ball1 и Ball2, но в ряде случаев оно оказывается полезным.

Рис. 1.30. ER-диаграмма теннисного турнира ER-диаграмма «Библиотека» Рассмотрим учет книжного фонда и выдачу книг в библиотеке, например в библиотеке МИФИ. В базе данных должна храниться информация о так называемых концептуальных книгах и физиче37

ских книгах (томах). Кроме того, требуется иметь информацию о выдаче и возврате книг. Анализ предметной области показывает, что должны быть следующие сущности: ▪ Читатель; ▪ Концептуальная книга (книга); ▪ Физическая книга (том); ▪ Формуляр; ▪ Операция (выдача, возврат, списание, приход); ▪ Автор; ▪ Издательство. ER-диаграмма представлена на рис. 1.31. Так как у книги может быть несколько авторов, то между сущностями «Автор» и «Книга» устанавливается связь «многие ко многим». Для преобразования ее к типу «один ко многим» можно воспользоваться средствами ERWin.

Рис. 1.31. ER-диаграмма учета книг в библиотеке Для этого необходимо: 1. Перейти на физический уровень представления, 2. Выделить связь «многие ко многим» и нажать правую кнопку мыши, 38

3. Выбрать пункт меню ―Create Association Entity‖. В результате запустится мастер создания ассоциированной сущности, который предложит дать название ассоциированной сущности (в данном случае был дано название «Автор_Книга»), и появится соответствующая сущность. Результаты представлены на рис. 1.32.

Рис. 1.32. ER-диаграмма учета книг в библиотеке с ассоциированной сущностью «Автор_Книга»

Контрольные вопросы 1. 2. 3. 4. 5. 6.

Что позволяет обеспечить трехуровневая модель системы управления базой данных, предложенная ANSI? Что собой представляет функциональный подход к выбору состава и структуры предметной области? Каким образом отражаются связи между объектами в иерархической модели данных? Что означает связь «многие ко многим»? Чем отличается логический уровень представление ER-диаграммы от физического уровня в редакторе ERWin? Построить концептуальную модель предметной области «Бронирование авиабилетов».

39

Глава 2. РЕЛЯЦИОННАЯ МОДЕЛЬ ДАННЫХ Реляционная модель данных была предложена Е. Коддом, известным американским специалистом в области баз данных. Основные концепции этой модели были впервые опубликованы в 1970 г. в статье «A Relational Model of Data for Large Shared Data Banks» (CACM, 1970, Vol. 13, № 6). Реляционная модель позволила решить одну из важнейших задач в управлении базами данных — обеспечить независимость представления и описания данных от прикладных программ. Поэтому после опубликования работ Кодда начались активные исследования по созданию реляционной системы управления базами данных. К основным достоинствам реляционного подхода к управлению базой данных следует отнести: ▪ наличие небольшого набора абстракций, которые позволяют сравнительно просто моделировать бóльшую часть распространенных предметных областей и допускают точные формальные определения, оставаясь интуитивно понятными; ▪ наличие простого и в то же время мощного математического аппарата, опирающегося главным образом на теорию множеств и математическую логику и обеспечивающего теоретический базис реляционного подхода к организации баз данных; ▪ возможность манипулирования данными без необходимости знания конкретной физической организации баз данных во внешней памяти. В настоящее время реляционные СУБД остаются одними из наиболее распространенных, несмотря на некоторые присущие им недостатки. В качестве первого недостатка можно отметить ограниченность таких систем при использовании в так называемых нетрадиционных областях, в которых требуются предельно сложные структуры данных. Причем эта ограниченность реляционных СУБД является прямым следствием их простоты и проявляется лишь в отдельных предметных областях. Вторым, часто отмечаемым недостатком реляционных баз данных является невозможность адекватного отражения семантики предметной области. Возможности представления знаний о семан40

тической специфике предметной области в реляционных системах очень ограничены. На устранение именно этих недостатков в основном и направлены исследования по созданию объектно-ориентированных баз данных.

Базовые понятия реляционной модели данных Термин «реляционный» (от англ. relation — отношение) указывает, прежде всего, на то, что такая модель хранения данных построена на взаимоотношении составляющих ее частей, которые удобно представлять в виде двумерной таблицы. Кодд показал, что набор отношений (таблиц) может быть использован для хранения данных об объектах реального мира и моделирования связей между ними. Таким образом, реляционная модель данных представляет информацию в виде совокупности взаимосвязанных таблиц, которые принято называть отношениями, или реляциями. Основными понятиями реляционной модели данных являются: ▪ тип данных; ▪ домен; ▪ атрибут; ▪ кортеж; ▪ ключ. Рассмотрим смысл этих понятий на примере отношения (табл. 2.1) СТУДЕНТ, содержащего информацию о студентах некоторого вуза. Таблица 2.1 Отношение СТУДЕНТ реляционной базы данных №_студ_билета 23980282 22991380 22657879 24356783 24350283 23125681

Имя Алексеев Д.А. Яковлев Н.В. Михайлов В.В. Афанасьев А.В. Кузнецов В.И. Кузнецов В.И.

Дата рождения 12.03.1992 25.12.1989 29.02.1989 19.08.1993 03.10.1992 26.03.1991

41

Курс 2 4 5 1 1 3

Специальность Биология Физика Математика Иностранный яз. Физика История

Тип данных Понятие типа данных в реляционной модели данных полностью эквивалентно соответствующему понятию в алгоритмических языках. Набор поддерживаемых типов данных определяется СУБД и может сильно различаться в разных системах. Однако практически все СУБД поддерживают следующие типы данных: ▪ целочисленные; ▪ вещественные; ▪ строковые; ▪ специализированные типы данных для денежных величин; ▪ специальные типы данных для величин типа дата и/(или) время; ▪ типы двоичных объектов (данный тип не имеет аналога в языках программирования; обычно для его обозначения используется аббревиатура BLOB — Binary Large OBject). В рассматриваемом примере используются три типа данных — строковый (столбцы «Имя» и «Специальность»), тип дата (столбец «Дата_рождения») и целочисленный тип («Курс» и «№_студ_билета»). Домен Наименьшая единица данных реляционной модели — это отдельное атомарное (неразложимое) для данной модели значение данных. Доменом называется множество атомарных значений одного и того же типа. В нашем примере можно для каждого столбца таблицы определить домены: «Имена», «Специальности», «Даты_рождения», «Номера_курсов» и «Номера_студ_билетов». Следует отметить, что данные считаются сравнимыми только в том случае, когда они относятся к одному домену. Если же значения двух атрибутов берутся из различных доменов, то их сравнение лишено смысла. Атрибуты, схема отношения, схема базы данных Столбцы отношения называют атрибутами, им присваиваются имена, по которым к ним затем производится обращение. 42

Список имен атрибутов отношения с указанием имен доменов (или типов, если домены не поддерживаются) называется схемой отношения. Степень отношения — это число его атрибутов. Отношение степени один называют унарным, степени два — бинарным, степени три — тернарным, …, а степени n — арным. Схемой базы данных называется множество именованных схем отношений. Кортеж Кортеж, соответствующий данной схеме отношения, представляет собой множество пар {имя атрибута, значение}, которое содержит одно вхождение каждого имени атрибута, принадлежащего схеме отношения. «Значение» является допустимым значением домена данного атрибута (или типа данных, если понятие домена не поддерживается). Тем самым степень кортежа, т.е. число элементов в нем, совпадает со степенью соответствующей схемы отношения. Иными словами, кортеж — это набор именованных значений заданного типа. Схему отношения иногда называют также заголовком отношения, а отношение как набор кортежей — телом отношения. Кардинальным числом или мощностью отношения называется число его кортежей. Мощность отношения СТУДЕНТ равна 6. В отличие от степени отношения кардинальное число отношения изменяется во времени. Пустые значения В некоторых случаях какой-либо атрибут отношения может быть неприменим. Например, в рассматриваемом в качестве примера отношении СТУДЕНТ может также храниться информация о потенциальных абитуриентах, посещающих подготовительные курсы вуза. Следует понимать, что пустое значение — это не ноль и не пустая строка, а неизвестное значение атрибута, которое не определено в данный момент времени и в принципе может быть определено позднее. Для обозначения пустых значений полей используется слово NULL. 43

Ключи отношения Поскольку отношение с математической точки зрения является множеством, а множества по определению не содержат совпадающих элементов, то никакие два кортежа отношения не могут быть дубликатами друг друга в любой произвольно заданный момент времени. Таким образом, в отношении всегда должен присутствовать некоторый атрибут (или набор атрибутов), однозначно определяющий каждый кортеж отношения и обеспечивающий уникальность строк таблицы. Такой атрибут (или набор атрибутов) называется первичным ключом отношения. Более строго определить понятие первичного ключа можно следующим образом: если R — отношение с атрибутами A1 A2..., An, то множество атрибутов К = (Ai, Аj, ..., Ak) отношения R является первичным ключом этого отношения тогда и только тогда, когда удовлетворяются два независимых от времени условия: ▪ уникальность: в произвольный момент времени никакие два различных кортежа отношения R не имеют одного и того же значения для Аi, Аj, ..., Аk. ▪ минимальность: ни один из атрибутов Аi Аj ..., Аk не может быть исключен из К без нарушения уникальности. В зависимости от количества атрибутов, входящих в ключ, различают простые и сложные (или составные) ключи. Простой ключ — ключ, содержащий только один атрибут. В общем случае операции объединения выполняются быстрее в том случае, когда в качестве ключа используется самый короткий и самый простой из возможных типов данных. С этой точки зрения наилучшим образом подходит целочисленный тип, который имеет аппаратную поддержку для выполнения над ним логических операций. Сложный (или составной) ключ состоит из нескольких атрибутов. Набор атрибутов, обладающий свойством уникальности, но не обладающий минимальностью, называется суперключом. Суперключ — сложный (составной) ключ с бóльшим числом столбцов, чем необходимо для того, чтобы быть уникальным идентификатором.

44

В зависимости от того, содержит ли атрибут, являющийся первичным ключом, какую-либо информацию, различают искусственные и естественные ключи. Искусственный или суррогатный ключ — ключ, созданный самой СУБД или пользователем с помощью некоторой процедуры и сам по себе не содержащий информации. Искусственный ключ используется для создания уникальных идентификаторов строк, когда сущность должна быть описана полностью, чтобы однозначно идентифицировать конкретный элемент. Искусственный ключ часто применяется вместо значимого сложного ключа, который является слишком громоздким, чтобы использоваться в реальной базе данных. Система поддерживает искусственный ключ, но он никогда не показывается пользователю. Естественный ключ — ключ, в который включены значимые атрибуты и который, таким образом, содержит информацию. В рассматриваемом нами примере в качестве первичного ключа отношения СТУДЕНТ можно рассматривать атрибут №_студ_билета. Причем данный ключ будет естественным, так как несет вполне определенную информацию. Основными достоинствами естественных ключей является то, что они несут вполне определенную информацию и их использование не приводит к необходимости добавлять в таблицы атрибуты, значения которых не имеют никакого смысла и используются лишь для связи между отношениями. Основным же недостатком естественных ключей является то, что их использование весьма затруднительно в случае изменчивости предметной области. Следует понимать, что значения атрибутов первичного ключа не должны изменяться. То есть однажды заданное значение первичного ключа для кортежа не может быть позже изменено. Такое требование ставится в основном для поддержания целостности базы данных. Связь между отношениями обычно устанавливается именно по первичному ключу, и его изменение приведет к нарушению этих связей или к необходимости изменения записей в нескольких таблицах. Второй, довольно существенный недостаток естественных ключей состоит в том, что, как правило, уникальные естественные ключи являются составными и содержат строковые атрибуты. Как 45

уже отмечалось выше, максимальная скорость выполнения операций над данными обеспечивается при использовании простых целочисленных ключей. Таким образом, с точки зрения быстродействия системы естественные ключи часто оказываются неоптимальными. Оба недостатка естественных ключей можно преодолеть, определив в отношениях суррогатные ключи, представляющие собой некоторый универсальный атрибут, как правило целочисленного типа, который не зависит ни от предметной области, ни, тем более, от структуры отношения, которое он идентифицирует. Таким образом, можно обеспечить уникальность и неизменность ключа (раз он никаким образом не зависит от предметной области, то никогда не возникнет необходимость изменять его). Однако за это приходится платить избыточностью данных в таблицах. В любой из таблиц может оказаться несколько наборов атрибутов, которые можно выбрать в качестве ключа. Такие наборы называются потенциальными или альтернативными ключами. Связанные отношения В реляционной модели данные представляются в виде совокупности взаимосвязанных таблиц. Подобное взаимоотношение между таблицами называется связью (relationship). Таким образом, еще одним важным понятием реляционной модели является связь между отношениями. Для рассмотрения связанных отношений воспользуемся рассмотренным ранее примером — отношением СТУДЕНТ. Данное отношение может быть связано с отношением ЭКЗАМЕН, в котором содержатся сведения об успеваемости студентов по разным предметам. Фрагмент такого отношения может иметь вид, приведенный в табл. 2.2.

46

Таблица 2.2 Фрагмент отношения ЭКЗАМЕН, связанного с отношением СТУДЕНТ № студ_билета

Предмет

Оценка

23980282 23980282 22991380 22991380 22657879 24356783

Высшая математика Общая физика Философия Общая физика Высшая математика Философия

4 5 NULL 5 3 NULL

Атрибут «№_студ_билета» таблицы ЭКЗАМЕН содержит идентификатор студента (в данном примере в качестве такого идентификатора используется номер студенческого билета). Если нужно узнать имя студента, соответствующее строкам в таблице ЭКЗАМЕН, то следует поискать это же значение идентификатора студента в поле «№_студенческого_6илета» таблицы СТУДЕНТ и в найденной строке прочесть значение поля «Имя». Таким образом, связь между таблицами СТУДЕНТ и ЭКЗАМЕН устанавливается по атрибуту «№_студ_билета». При рассмотрении связанных таблиц важное значение имеет понятие внешнего ключа. Внешние ключи отношения В базах данных одни и те же имена атрибутов часто используются в разных отношениях. В рассматриваемом примере атрибут «№_студ_билета» присутствует как в отношении СТУДЕНТ, так и в отношении УСПЕВАЕМОСТЬ. В этом примере атрибут «№_студ_билета» иллюстрирует понятие внешнего ключа (foreign key). Внешний ключ — это атрибут (или множество атрибутов) одного отношения, являющийся ключом другого (или того же самого) отношения. Внешние ключи используются для установления логических связей между отношениями. Связь между двумя таблицами устанавливается путем присваивания значений внешнего ключа одной таблицы значениям ключа другой. 47

Так же как и любые другие ключи, внешние ключи могут быть простыми либо составными. Часто связь между отношениями устанавливается по первичному ключу, т.е. значениям внешнего ключа одного отношения присваиваются значения первичного ключа другого отношения. Однако это не является обязательным — в общем случае связь может устанавливаться также и с помощью вторичных ключей. Кроме того, при установлении связей между таблицами необязательно требование уникальности ключа, по которому устанавливается связь. Атрибуты внешнего ключа не обязательно должны иметь те же имена, что и атрибуты ключа, которым они соответствуют. Например, в нашем примере можно было дать атрибуту «№_студ_билета» таблицы УСПЕВАЕМОСТЬ другое имя, например «Студ_билет», Внешний ключ может ссылаться и на ту же таблицу, к которой он принадлежит. В этом случае внешний ключ называется рекурсивным. Условия целостности данных Чтобы информация, хранящаяся в базе данных, была однозначной и непротиворечивой, в реляционной модели устанавливаются некоторые ограничительные условия. Ограничительные условия — это правила, определяющие возможные значения данных. Они обеспечивают логическую основу для поддержания корректных значений данных в базе. Ограничения целостности позволяют свести к минимуму ошибки, возникающие при обновлении и обработке данных. Важнейшими ограничениями целостности данных являются: ▪ категорийная целостность; ▪ ссылочная целостность. Ограничение категорийной целостности заключается в следующем. Кортежи отношения представляют в базе данных элементы определенных объектов реального мира или, в соответствии с терминологией реляционных СУБД, категорий. Например, строка таблицы СТУДЕНТ представляет конкретного студента. Первичный ключ таблицы однозначно определяет каждый кортеж и, следовательно, каждый элемент категории. 48

Таким образом, для извлечения данных, содержащихся в строке таблицы, или для манипулирования этими данными необходимо знать значение ключа для этой строки. Поэтому строка не может быть занесена в базу данных до тех пор, пока не будут определены все атрибуты ее первичного ключа. Это правило называется правилом категорийной целостности и кратко формулируется следующим образом: никакой атрибут первичного ключа строки не может быть пустым. Второе условие накладывает на внешние ключи ограничения для обеспечения целостности данных, называемой ссылочной целостностью. Если две таблицы связаны между собой, то внешний ключ таблицы должен содержать только те значения, которые уже имеются среди значений ключа, по которому осуществляется связь. Если корректность значений внешних ключей не контролируется СУБД, то может нарушиться ссылочная целостность данных. Это можно пояснить на рассматриваемом примере следующим образом. Если удалить из таблицы СТУДЕНТ строку (например, при отчислении студента), имеющую хотя бы одну связанную с ней строку в таблице УСПЕВАЕМОСТЬ, то это приведет к тому, что в таблице УСПЕВАЕМОСТЬ останутся записи об успеваемости студента, который уже отчислен. Такая же ситуация будет наблюдаться и в том случае, если внешнему ключу таблицы УСПЕВАЕМОСТЬ ошибочно будет присвоено значение, отсутствующее в значениях ключа связанной таблицы. Ограничения категорийной и ссылочной целостности должны поддерживаться СУБД. Для соблюдения целостности сущности достаточно гарантировать отсутствие в любом отношении кортежей с одним и тем же значением первичного ключа. Что же касается ссылочной целостности, то здесь обеспечение целостности выглядит несколько сложнее. При обновлении ссылающегося отношения (при вставке новых кортежей или модификации значения внешнего ключа в существующих кортежах) достаточно следить за тем, чтобы не появлялись некорректные значения внешнего ключа. 49

А вот при удалении кортежа из отношения, на которое ведет ссылка, возможно использовать один из трех подходов, каждый из которых поддерживает целостность по ссылкам: ▪ первый подход заключается в том, что запрещается производить удаление кортежа, на который существуют ссылки (т.е. сначала нужно либо удалить ссылающиеся кортежи, либо соответствующим образом изменить значения их внешнего ключа); ▪ при втором подходе при удалении кортежа, на который имеются ссылки, во всех ссылающихся кортежах значение внешнего ключа автоматически становится неопределенным; ▪ третий подход (называемый также каскадным удалением) состоит в том, что при удалении кортежа из отношения, на которое ведет ссылка, из ссылающегося отношения автоматически удаляются все ссылающиеся кортежи. В развитых реляционных СУБД обычно можно выбрать способ поддержания ссылочной целостности для каждой отдельной ситуации определения внешнего ключа. Конечно, для принятия такого решения необходимо анализировать требования конкретной прикладной области. Практически все современных СУБД обеспечивает ссылочную целостность данных. Типы связей между таблицами При установлении связи между двумя таблицами одна из них будет являться главной (master), а вторая — подчиненной (detail). Различают три типа связей между таблицами реляционной базы данных: 1) один к одному — каждой записи одной таблицы соответствует только одна запись другой таблицы; 2) один ко многим — одной записи главной таблицы могут соответствовать несколько записей подчиненной таблицы; 3) многие ко многим — одна запись главной таблицы связана с несколькими записями подчиненной таблицы, а одна запись подчиненной таблицы связана с несколькими записями главной таблицы. Различие между типами связей «один ко многим» и «многие к одному» зависит от того, какая из таблиц выбирается в качестве 50

главной, а какая — в качестве подчиненной. Например, если из связанных таблиц СТУДЕНТ и УСПЕВАЕМОСТЬ в качестве главной выбрать таблицу СТУДЕНТ, то получим тип связи «один ко многим». Если же выбрать в качестве главной таблицу УСПЕВАЕМОСТЬ, получится тип связи «многие к одному». В таблицах реляционной базы данных информация хранится в неупорядоченном виде. Упорядочивание в принципе не поддерживается СУБД, и такое понятие, как «порядковый номер кортежа», не имеет никакого смысла. Свойство отсутствия упорядоченности кортежей отношения также является следствием определения отношения как множества кортежей. Отсутствие требования к поддержанию порядка на множестве кортежей отношения дает СУБД дополнительную гибкость при хранении баз данных во внешней памяти и при выполнении запросов к базе данных.

Реляционная алгебра и реляционное исчисление Для манипулирования данными Кодд предложил два базовых механизма манипулирования реляционными данными — основанную на теории множеств реляционную алгебру и базирующееся на математической логике (точнее, на исчислении предикатов первого порядка) реляционное исчисление. Реляционная алгебра и реляционное исчисление обладают большой выразительной мощностью: сложные запросы к базе данных могут быть выражены с помощью одного выражения реляционной алгебры или одной формулы реляционного исчисления. Язык манипулирования реляционными БД называется реляционно полным, если любой запрос, записываемый с помощью одного выражения реляционной алгебры или одной формулы реляционного исчисления, может быть представлен с помощью одного оператора этого языка. Коддом доказано, что реляционная алгебра и реляционное исчисление логически эквивалентны, т.е. для любого допустимого выражения реляционной алгебры можно построить эквивалентную (т.е. производящую такой же результат) формулу реляционного исчисления и наоборот. Заметим, что крайне редко алгебра или исчисление принимаются в качестве полной основы какого-либо языка БД. Обычно (как, 51

например, в случае языка SQL) язык основывается на некоторой смеси алгебраических и логических конструкций. Тем не менее знание алгебраических и логических основ языков баз данных часто бывает полезно на практике.

Реляционная алгебра Основная идея реляционной алгебры состоит в том, что коль скоро отношения являются множествами, то средства манипулирования отношениями могут базироваться на традиционных теоретико-множественных операциях, дополненных некоторыми специальными операциями, специфичными для баз данных. Набор основных операций реляционной алгебры состоит из восьми операций, которые делятся на два класса — теоретикомножественные операции и специальные операции. В состав теоретико-множественных операций входят операции: ▪ объединения отношений; ▪ пересечения отношений; ▪ взятия разности отношений; ▪ прямого произведения отношений. Специальные реляционные операции включают: ▪ ограничение отношения (выборка); ▪ проекцию отношения; ▪ соединение отношений; ▪ деление отношений. Кроме того, в состав алгебры включается операция присваивания, позволяющая сохранить в базе данных результаты вычисления алгебраических выражений, и операция переименования атрибутов, дающая возможность корректно сформировать заголовок (схему) результирующего отношения. Операции реляционной алгебры Объединение двух отношений производит отношение, включающее все кортежи, входящие хотя бы в одно из отношенийоперандов. 2. Пересечение двух отношений производит отношение, включающее все кортежи, входящие в оба отношения-операнда. 1.

52

Разность двух отношений включает все кортежи, входящие в отношение — первый операнд — такие, что ни один из них не входит в отношение, являющееся вторым операндом. 4. Произведение двух отношений производит отношение, кортежи которого являются конкатенацией (сцеплением) кортежей первого и второго операндов. 5. Ограничение отношения по некоторому условию является отношение, включающее кортежи отношения-операнда, удовлетворяющее этому условию. 6. Проекция отношения на заданный набор его атрибутов производится отношение, кортежи которого производятся путем взятия соответствующих значений из кортежей отношенияоперанда. 7. Соединение двух отношений по некоторому условию образуется результирующее отношение, кортежи которого являются конкатенацией кортежей первого и второго отношений и удовлетворяют этому условию. 8. У операции реляционного деления два операнда — бинарное и унарное отношения. Результирующее отношение состоит из одноатрибутных кортежей, включающих значения первого атрибута кортежей первого операнда таких, что множество значений второго атрибута (при фиксированном значении первого атрибута) совпадает со множеством значений второго операнда. 9. Операция переименования производит отношение, тело которого совпадает с телом операнда, но имена атрибутов изменены. 10. Операция присваивания позволяет сохранить результат вычисления реляционного выражения в существующем отношении БД. Поскольку результатом любой реляционной операции (кроме операции присваивания) является некоторое отношение, можно образовывать реляционные выражения, в которых вместо отношения-операнда некоторой реляционной операции находится вложенное реляционное выражение. 3.

53

Теоретико-множественные операции реляционной алгебры В основе теоретико-множественных операций реляционной алгебры лежит классическая теория множеств. Однако соответствующие операции реляционной алгебры обладают некоторыми особенностями. Операции объединения, пересечения и разности Смысл операции объединения в реляционной алгебре в целом остается теоретико-множественным. Но если в теории множеств операция объединения осмысленна для любых двух множествоперандов, то в случае реляционной алгебры результатом операции объединения должно являться отношение. Это приводят к появлению понятия совместимости отношений по объединению: два отношения совместимы по объединению в том и только в том случае, когда обладают одинаковыми заголовками. Более точно это означает, что в заголовках обоих отношений содержится один и тот же набор имен атрибутов, и одноименные атрибуты определены на одном и том же домене. Если два отношения совместимы по объединению, то при обычном выполнении над ними операций объединения, пересечения и взятия разности результатом операции является отношение с корректно определенным заголовком, совпадающим с заголовком каждого из отношений-операндов. Для отношений "почти" совместимы по объединению, т.е. совместимых во всем, кроме имен атрибутов, можно сделать полностью совместимыми по объединению путем применения операции переименования. Заметим, что включение в состав операций реляционной алгебры трех операций объединения, пересечения и взятия разности является очевидно избыточным, поскольку известно, что любая из этих операций выражается через две других. Тем не менее Кодд в свое время решил включить все три операции, исходя из интуитивных потребностей потенциального пользователя системы реляционных БД, далекого от математики.

54

Операция прямого произведения двух отношений В реляционной алгебре используется специализированная форма операции взятия прямого произведения — расширенное прямое произведение отношений. При взятии расширенного прямого произведения двух отношений элементом результирующего отношения является кортеж — конкатенация (или слияние) одного кортежа первого отношения и одного кортежа второго отношения. Два отношения совместимы по взятию прямого произведения в том и только в том случае, если множества имен атрибутов этих отношений не пересекаются. Любые два отношения могут быть сделаны совместимыми по взятию прямого произведения путем применения операции переименования к одному из этих отношений. Следует заметить, что операция взятия прямого произведения не является слишком осмысленной на практике. Во-первых, мощность ее результата очень велика даже при допустимых мощностях операндов, а во-вторых, результат операции не более информативен, чем взятые в совокупности операнды. Основной смысл включения операции расширенного прямого произведения в состав реляционной алгебры состоит в том, что на ее основе определяется действительно полезная операция соединения. По поводу теоретико-множественных операций реляционной алгебры следует еще заметить, что все четыре операции являются ассоциативными. Если обозначить через OP любую из четырех операций, то (A OP B) OP C=A (B OP C), и, следовательно, без введения двусмысленности можно записать: A OP B OP C (A, B и C — отношения, обладающие свойствами, требуемыми для корректного выполнения соответствующей операции). Все операции, кроме взятия разности, коммутативны, т.е. A OP B = B OP A. Специальные реляционные операции Рассмотрим специальные реляционные операции реляционной алгебры: ограничение, проекцию, соединение и деление. 55

Операция ограничения (выборки) Операция ограничения требует наличия двух операндов: ограничиваемого отношения и простого условия ограничения. Простое условие ограничения может иметь либо вид (a comp-op b), где а и b — имена атрибутов ограничиваемого отношения, для которых осмысленна операция сравнения comp-op, либо вид (a comp-op const), где a — имя атрибута ограничиваемого отношения, а const — литерально заданная константа. В результате выполнения операции ограничения производится отношение, заголовок которого совпадает с заголовком отношения-операнда, а в тело входят те кортежи отношения-операнда, для которых значением условия ограничения является true. Пусть UNION обозначает операцию объединения, INTERSECT — операцию пересечения, а MINUS — операцию взятия разности. Для обозначения операции ограничения будем использовать конструкцию A WHERE comp, где A — ограничиваемое отношение, а comp - простое условие сравнения. Пусть comp1 и comp2 — два простых условия ограничения. Тогда по определению: A WHERE comp1 AND comp2 обозначает то же самое, что и (A WHERE comp1) INTERSECT (A WHERE comp2) A WHERE comp1 OR comp2 обозначает то же самое, что и (A WHERE comp1) UNION (A WHERE comp2) A WHERE NOT comp1 обозначает то же самое, что и A MINUS (A WHERE comp1) С использованием этих определений можно использовать операции ограничения (выборки), в которых условием ограничения является произвольное булевское выражение, составленное из простых условий с использованием логических связок AND, OR, NOT и скобок. Операция взятия проекции Операция взятия проекции также требует наличия двух операндов — проецируемого отношения A и списка имен атрибутов, входящих в заголовок отношения A. Результатом проекции отношения A по списку атрибутов a1, a2, ..., an является отношение с заголовком, определяемым множест56

вом атрибутов a1, a2, ..., an, и с телом, состоящим из кортежей вида таких, что в отношении A имеется кортеж, атрибут a1 которого имеет значение v1, атрибут a2 — значение v2, ..., атрибут an — значение vn. Тем самым, при выполнении операции проекции выделяется «вертикальная» вырезка отношения-операнда с естественным уничтожением потенциально возникающих кортежейдубликатов. Операция соединения отношений Общая операция соединения (называемая также соединением по условию) требует наличия двух операндов соединяемых отношений и третьего операнда — простого условия. Пусть соединяются отношения A и B. Как и в случае операции ограничения, условие соединения comp имеет вид либо (a comp-op b), либо (a comp-op const), где a и b — имена атрибутов отношений A и B, const — литерально заданная константа, а comp-op — допустимая в данном контексте операция сравнения. Результатом операции сравнения является отношение, получаемое путем выполнения операции ограничения по условию comp прямого произведения отношений A и B. Применение условия соединения существенно уменьшит мощность результата промежуточного прямого произведения отношений-операндов только в том случае, когда условие соединения имеет вид (a comp-op b), где a и b — имена атрибутов разных отношений-операндов. Поэтому на практике обычно считают реальными операциями соединения именно те операции, которые основываются на условии соединения приведенного вида. Частный случай соединения — эквисоединение и простое, но важное расширение операции эквисоединения — естественное соединение. Операция соединения называется операцией эквисоединения, если условие соединения имеет вид (a = b), где a и b — атрибуты разных операндов соединения. Операция естественного соединения применяется к паре отношений A и B, обладающих (возможно составным) общим атрибутом c (т.е. атрибутом с одним и тем же именем и определенным на 57

одном и том же домене). Пусть ab обозначает объединение заголовков отношений A и B. Тогда естественное соединение A и B — это спроектированный на ab результат эквисоединения A и B по условию A.c = B.с. Основной смысл операции естественного соединения — возможность восстановления сложной сущности, декомпозированной по причине требования первой нормальной формы. Операция естественного соединения не включается прямо в состав набора операций реляционной алгебры, но имеет очень важное практическое значение. Операция деления отношений Пусть заданы два отношения — A с заголовком {a1, a2, ..., an, b1, b2, ..., bm} и B с заголовком {b1, b2, ..., bm}. Будем считать, что атрибут bi отношения A и атрибут bi отношения B не только обладают одним и тем же именем, но и определены на одном и том же домене. Назовем множество атрибутов {aj} составным атрибутом a, а множество атрибутов {bj} — составным атрибутом b. После этого будем говорить о реляционном делении бинарного отношения A(a,b) на унарное отношение B(b). Результатом деления A на B является унарное отношение C(a), состоящее из кортежей v таких, что в отношении A имеются кортежи , у которых множество значений {w} включает множество значений атрибута b в отношении B. Предположим, что в базе данных сотрудников поддерживаются два отношения: СОТРУДНИК(ИМЯ, ОТД_НОМЕР) и ИМЕНА (ИМЯ), причем унарное отношение ИМЕНА содержит все фамилии, которыми обладают сотрудники организации. Тогда после выполнения операции реляционного деления отношения СОТРУДНИК на отношение ИМЕНА будет получено унарное отношение, содержащее номера отделов, сотрудники которых обладают всеми возможными в этой организации именами.

Реляционное исчисление Предположим, что реляционная схема базы данных, имеет вид СОТРУДНИК (СОТР_НОМ, СОТР_ИМЯ, СОТР_ЗАРП, ОТД_НОМ),

58

Внешний ключ: ОТД_НОМ -> ОТДЕЛ ОТДЕЛ (ОТД_НОМ, ОТД_КОЛ, ОТД_НАЧ),

и требуется узнать имена и номера сотрудников, являющихся начальниками отделов с количеством сотрудников больше 50. Выполнение такого запроса средствами реляционной алгебры, может выглядеть, например, следующим образом: 1) выполнить соединение отношений СОТРУДНИК и ОТДЕЛ по условию СОТР_НОМ = ОТД_НАЧ; 2) ограничить полученное отношение по условию ОТД_КОЛ > 50; 3) спроецировать результат предыдущей операции на атрибут СОТР_ИМЯ, СОТР_НОМ. Если же сформулировать тот же запрос с использованием реляционного исчисления, то это выглядело бы следующим образом: Выдать СОТР_ИМЯ и СОТР_НОМ для сотрудников таких, что существует отдел с таким же значением ОТД_НАЧ и значением ОТД_КОЛ, бóльшим 50. Как видно, в данном случае указаны лишь характеристики результирующего отношения, но ничего не сказано о способе его формирования. В этом случае СУБД должна сама решает, какие операции и в каком порядке нужно выполнить над отношениями СОТРУДНИК и ОТДЕЛ. Говорят, что алгебраическая формулировка является процедурной, т.е. задающей правила выполнения запроса, а заданная средствами реляционного исчисления — описательной (или декларативной), поскольку она всего лишь описывает свойства желаемого результата. Реляционное исчисление кортежей Реляционное исчисление является прикладной ветвью формального механизма исчисления предикатов первого порядка. Базисными понятиями исчисления являются понятие переменной с опреде59

ленной для нее областью допустимых значений и понятие правильно построенной формулы, опирающейся на переменные, предикаты и кванторы. В зависимости от того, что является областью определения переменной, различаются исчисление кортежей и исчисление доменов. В исчислении кортежей областями определения переменных являются отношения базы данных, т.е. допустимым значением каждой переменной является кортеж некоторого отношения. В исчислении доменов областями определения переменных являются домены, на которых определены атрибуты отношений базы данных, т.е. допустимым значением каждой переменной является значение некоторого домена. Для определения кортежной переменной используется оператор RANGE. Например, для того, чтобы определить переменную РАБОТНИК, областью определения которой является отношение СОТРУДНИК, нужно употребить конструкцию RANGE РАБОТНИК IS СОТРУДНИК Из этого определения следует, что в любой момент времени переменная РАБОТНИК представляет некоторый кортеж отношения СОТРУДНИК. При использовании кортежных переменных в формулах можно ссылаться на значение атрибута переменной. Например, для того, чтобы сослаться на значение атрибута СОТР_ИМЯ переменной РАБОТНИК, нужно употребить конструкцию РАБОТНИК.СОТР_ИМЯ. Правильно построенные формулы (WFF — Well-Formed Formula) служат для выражения условий, накладываемых на кортежные переменные. Основой WFF являются простые сравнения (comparison), представляющие собой операции сравнения скалярных значений (значений атрибутов переменных или литерально заданных констант). Например, конструкция "РАБОТНИК.СОТР_НОМ = 140" является простым сравнением. По определению, простое сравнение является WFF, а WFF, заключенная в круглые скобки, — также простое сравнение. Более сложные варианты WFF строятся с помощью логических связок NOT, AND, OR и IF ... THEN. Так, если form — WFF, а comp — простое сравнение, то NOT form, comp AND form, comp OR form и IF comp THEN form являются WFF. Наконец, допускается построение WFF с помощью кванторов. Если form — это WFF, в которой участвует переменная var, то конструкции EXISTS var (form) и FORALL var (form) представляют wff. 60

Переменные, входящие в WFF, могут быть свободными или связанными. Все переменные, входящие в WFF, при построении которой не использовались кванторы, являются свободными. Фактически это означает, что если для какого-то набора значений свободных кортежных переменных при вычислении WFF получено значение true, то эти значения кортежных переменных могут входить в результирующее отношение. Если же имя переменной использовано сразу после квантора при построении WFF вида EXISTS var (form) или FORALL var (form), то в этой WFF и во всех WFF, построенных с ее участием, var — связанная переменная. Это означает, что такая переменная не видна за пределами минимальной WFF, связавшей эту переменную. При вычислении значения такой WFF используется не одно значение связанной переменной, а вся ее область определения. Пусть СОТР1 и СОТР2 — две кортежные переменные, определенные на отношении СОТРУДНИК. Тогда, WFF EXISTS СОТР2 (СОТР1.СОТР_ЗАРП > СОТР2.СОТР_ЗАРП) для текущего кортежа переменной СОТР1 принимает значение true в том и только в том случае, если во всем отношении СОТРУДНИК найдется кортеж (связанный с переменной СОТР2) такой, что значение его атрибута СОТР_ЗАРП удовлетворяет внутреннему условию сравнения. WFF FORALL СОТР2 (СОТР1.СОТР_ЗАРП >СОТР2.СОТР_ЗАРП)

для текущего кортежа переменной СОТР1 принимает значение true в том и только в том случае, если для всех кортежей отношения СОТРУДНИК (связанных с переменной СОТР2) значения атрибута СОТР_ЗАРП удовлетворяют условию сравнения. На самом деле, правильнее говорить не о свободных и связанных переменных, а о свободных и связанных вхождениях переменных. Легко видеть, что если переменная var является связанной в WFF form, то во всех WFF, включающих данную, может использоваться имя переменной var, которая может быть свободной или связанной, но в любом случае не имеет никакого отношения к вхождению переменной var в WFF form. Вот пример: 61

EXISTS СОТР2 (СОТР1.СОТР_ОТД_НОМ = СОТР2.СОТР_ОТД_НОМ) AND FORALL СОТР2 (СОТР1.СОТР_ЗАРП > СОТР2.СОТР_ЗАРП)

Здесь мы имеем два связанных вхождения переменной СОТР2 с совершенно разным смыслом. Целевые списки и выражения реляционного исчисления Итак, WFF обеспечивают средства формулировки условия выборки из отношений БД. Чтобы можно было использовать исчисление для реальной работы с БД, требуется еще один компонент, который определяет набор и имена столбцов результирующего отношения. Этот компонент называется целевым списком (target_list). Целевой список строится из целевых элементов, каждый из которых может иметь следующий вид: ▪ var.attr, где var — имя свободной переменной соответствующей WFF, а attr — имя атрибута отношения, на котором определена переменная var; ▪ var, что эквивалентно наличию подсписка var.attr1, var.attr2, ..., var.attrn, где attr1, attr2, ..., attrn, включает имена всех атрибутов определяющего отношения; ▪ new_name = var.attr; new_name — новое имя соответствующего атрибута результирующего отношения. Последний вариант требуется в тех случаях, когда в WFF используются несколько свободных переменных с одинаковой областью определения. Выражением реляционного исчисления кортежей называется конструкция вида target_list WHERE wff. Значением выражения является отношение, тело которого определяется WFF, а набор атрибутов и их имена — целевым списком. Реляционное исчисление доменов В исчислении доменов областью определения переменных являются не отношения, а домены. Применительно к базе данных СОТРУДНИКИ-ОТДЕЛЫ можно говорить, например, о доменных 62

переменных ИМЯ (значения — допустимые имена) или СОТР_НОМ (значения — допустимые номера сотрудников). Основным формальным отличием исчисления доменов от исчисления кортежей является наличие дополнительного набора предикатов, позволяющих выражать так называемые условия членства. Если R — это n-арное отношение с атрибутами a1, a2, ..., an, то условие членства имеет вид R (ai1:vi1, ai2:vi2, ..., aim:vim) (m <= n), где vij — либо литерально задаваемая константа, либо имя доменной переменной. Условие членства принимает значение true в том и только в том случае, если в отношении R существует кортеж, содержащий указанные значения указанных атрибутов. Если vij — константа, то на атрибут aij задается жесткое условие, не зависящее от текущих значений доменных переменных; если же vij — имя доменной переменной, то условие членства может принимать разные значения при разных значениях этой переменной. Во всех остальных отношениях формулы и выражения исчисления доменов выглядят похожими на формулы и выражения исчисления кортежей. Для примера сформулируем с использованием исчисления доменов запрос: "Выдать номера и имена сотрудников, не получающих минимальную заработную плату". Будем считать для простоты, что определены доменные переменные, имена которых совпадают с именами атрибутов отношения СОТРУДНИК. В случае, когда требуется несколько доменных переменных, определенных на одном домене, будем добавлять в конце имени цифры: СОТР_НОМ, СОТР_ИМЯ WHERE EXISTS СОТР_ЗАРП1 (СОТРУДНИК (СОТР_ЗАРП1) AND СОТРУДНИК (СОТР_НОМ, СОТР_ИМЯ, СОТР_ЗАРП) AND СОТР_ЗАРП > СОТР_ЗАРП1)

Реляционное исчисление доменов является основой большинства языков запросов, основанных на использовании форм. Реляционное исчисление послужило прообразом языка построения запро63

сов SQL (Structure Query Language), который в дальнейшем будет рассмотрен более подробно.

Нормализация отношений Определим нормализацию как процесс приведения реляционных таблиц к стандартному виду. Рассмотрим в качестве примера табл. 2.3 (НАЗНАЧЕНИЕ), в которой хранится информация о назначении сотрудников некоторой фирмы (например, консалтинговой) для работы над проектами. Следует отметить, что один сотрудник может участвовать в нескольких проектах. Кроме того, отношение хранит информацию о должности (Skill) каждого сотрудника. Таблица 2.3 Отношение НАЗНАЧЕНИЕ сотрудников на проекты ID_Worker 1111 1111 2222 2222 2222 2222 3333

Name Алексеев Д.А. Алексеев Д.А. Михайлов В.В. Михайлов В.В. Михайлов В.В. Михайлов В.В. Кузнецов В.И.

Skill Программист I Программист I Вед. программист Вед. программист Вед. программист Вед. программист Программист II

Project Альфа Омега Альфа Омега Гамма Сигма Альфа

Из анализа данной таблицы нетрудно сделать вывод, что она содержит большое количество избыточных данных. Избыточность не только приводит к увеличению объема хранимой информации, но и, что более существенно, может вызвать нарушение целостности данных. Наличие избыточности приводит к одной из трех аномалий: ▪ обновления; ▪ удаления; ▪ ввода. Аномалия обновления Аномалия обновления представляет собой противоречивость данных вызванная их избыточностью и частичным обновлением. Предположим, что должность Алексеева Д.А. была указана неверно, т.е. он является системным администратором, а не программи64

стом I категории. Если при корректировке должность Алексеева Д.А. была изменена только в первой строке (причины частичного обновления не рассматриваются), то получим противоречивые данные о должности Алексеева Д.А. Аномалия удаления Аномалия удаления представляет собой непреднамеренную потерю данных, вызванную удалением других данных. Например, если программист II категории Кузнецов В.И. увольняется, то он удаляется из базы данных, но вместе с ним удаляется (не преднамеренно) информация о должности «программист II категории». Аномалия ввода Аномалия ввода представляет собой невозможность ввода данных в таблицу, вызванная отсутствием других данных. Например, если в рассматриваемой таблице не допускаются пустые значения, то невозможно ввести данные о новом сотруднике, который не назначен ни на один проект, а проходит испытательный срок. Основная цель нормализации – сокращение избыточности хранимых данных и, как следствие: экономия объема используемой памяти; уменьшение затрат на многократные операции обновления избыточных копий; устранение возможности возникновения противоречий (аномалий) из-за хранения в разных местах сведений об одном и том же объекте. Разбиение — процесс разделения таблицы на несколько таблиц в целях избавления от аномалий и поддержки целостности данных. Первая нормальная форма (1НФ) Реляционная таблица находится в первой нормальной форме (1НФ), если значения в таблице являются атомарными для каждого атрибута таблицы. Атомарным является значение, не являющееся множеством или повторяющейся группой. Табл. 2.3 находится в 1-й нормальной форме, так как на пересечении любой строки и лю65

бого столбца находится единственной значение. Табл. 2.4 не находится в 1-й нормальной форме, поскольку атрибут Project не является атомарным, т.е. содержит множество значений. Таблица 2.4 НАЗНАЧЕНИЕ сотрудников на проекты (множественные значения) ID_Worker 1111 2222 3333

Name Алексеев Д.А. Михайлов В.В. Кузнецов В.И.

Skill Программист I Вед. программист Программист II

Project {Альфа, Омега} { Альфа,Омега,Гамма,Сигма} {Альфа }

Вообще говоря, обычная таблица — это таблица, находящаяся в первой нормальной форме, однако в ряде случаев 1НФ могут нарушить повторяющиеся группы и эта ситуация менее очевидна. Рассмотрим табл. 2.5, в которой для хранения информации о проектах используются атрибуты Pr1, Pr2, Pr3 и Pr4. Эти атрибуты являются повторяющейся группой и приводят к значительным проблемам. Таблица 2.5 НАЗНАЧЕНИЕ сотрудников на проекты (с повторяющейся группой) ID_Worker 1111 2222 3333

Name Алексеев Д.А. Михайлов В.В. Кузнецов В.И.

Skill Pr1 Программист I Альфа Вед. программ. Альфа Программист II Альфа

Pr2 Омега Омега

Pr3

Pr4

Гамма

Сигма

В частности, если Михайлов В.В. будет назначен на 5-й проект, то где хранить эту информацию. Если же зарезервировать большое количество атрибутов типа Pr1, то придется хранить большое количество пустых значений. Вторая и третья нормальные формы тесно связаны с понятием функциональных зависимостей. Функциональные зависимости Функциональная зависимость между атрибутами понимается в том смысле, что значение атрибута в кортеже однозначно опреде66

ляет значение другого атрибута в кортеже. Формально это записывается так: ФЗ: А  B, где А и B — атрибуты одной и той же таблицы R. Атрибут в левой части функциональной зависимости называется детерминантом. Функциональная зависимость означает, что если кортежа имеют одно и то же значение атрибута А, то они имеют одно и то же значение атрибута B. На практике функциональные зависимости выявляются путем логического анализа предметной области и взаимосвязи атрибутов описывающих сущности. Например, легко обнаружить функциональную зависимость между почтовым адресом и телефоном, артикулом товара и его названием, местом производства и т.д. С другой стороны, можно формально анализировать содержимое таблицы и руководствуясь приведенным определением отыскивать функциональные зависимости. Рассмотрим абстрактную таблицу (отношение) X(A,B,C,D,E) содержащую четыре кортежа: Таблица 2.6 Абстрактная таблица X A

B

C

D

E

a1

b2

c1

d3

e2

a3

b2

c3

d2

e4

a1

b3

c1

d1

e2

a2

b4

c1

d4

e2

Ключевым атрибутом целесообразно выбрать D. Анализируя содержимое кортежей, можно найти следующие функциональные зависимости: CE; EC; AC; AE.

67

Вторая нормальная форма (2НФ) Реляционная таблица находится во второй нормальной форме, если никакие неключевые атрибуты не являются функционально зависимыми лишь от части ключа. Вторая нормальная форма может оказаться нарушенной только в том случае, когда ключ составной. Для иллюстрации нарушения 2НФ рассмотрим несколько модифицированную табл. 2.3. Таблица 2.7 НАЗНАЧЕНИЕ сотрудников на проекты ID_Worker 1111 1111 2222 2222 2222 2222 3333

Project Альфа Омега Альфа Омега Гамма Сигма Альфа

Start_Date 01.02.2009 01.03.2009 01.02.2009 01.03.2009 01.04.2009 01.05.2009 21.02.2009

Name Алексеев Д.А. Алексеев Д.А. Михайлов В.В. Михайлов В.В. Михайлов В.В. Михайлов В.В. Кузнецов В.И.

В данной таблице присутствует избыточность данных, и, следовательно, имеется потенциальная опасность возникновения аномалий обновления, ввода и удаления данных. Ключ таблицы — составной (ID_Worker, Project), при этом возможно нарушение 2НФ. Анализируя содержимое таблицы, можно обнаружить следующие функциональные зависимости: ID_Worker, Project  Start_Date; ID_Worker  Name. Следовательно, существует функциональная зависимость неключевого атрибута (Name) лишь от части составного ключа (ID_Worker), т.е. имеет место нарушение 2НФ. Для устранения этой проблемы необходимо разбить данную таблицу на две, каждая из которых будет удовлетворять 2НФ. Процесс разбиения можно представить в виде следующих шагов: 1. Создается новая таблица, атрибутами которой будут атрибуты исходной таблицы входящие в противоречащую правилу функциональную зависимость, т.е. ID_Worker  Name.

68

Табл. 2.8 СОТРУДНИК ID_Worker 1111 2222 3333

Name Алексеев Д.А. Михайлов В.В. Кузнецов В.И.

2. Атрибут, стоящий в правой части функциональной зависимости, исключается из исходной таблицы, т.е. Name Таблица 2.9 НАЗНАЧЕНИЕ СОТРУДНИКОВ НА ПРОЕКТЫ ID_Worker 1111 1111 2222 2222 2222 2222 3333

Project Альфа Омега Альфа Омега Гамма Сигма Альфа

Start_Date 01.02.2009 01.03.2009 01.02.2009 01.03.2009 01.04.2009 01.05.2009 21.02.2009

3. Если имеются другие функциональные зависимости, нарушающие 2НФ, то шаги 1 и 2 повторяются для каждой из них. 4. Если один и тот же детерминант входит в несколько функциональных зависимостей, то все зависящие от него атрибуты помещаются в одну таблицу, ключом которой будет детерминант. Таким образом, в результате разбиения получаются две таблицы меньшего размера, и реляционная схема может быть записана так: НАЗНАЧЕНИЕ(ID_Worker, Project, Start_Date) Внешний ключ: ID_Worker -> СОТРУДНИК СОТРУДНИК(ID_Worker, Name) Третья нормальная форма (3НФ) Реляционная таблица находится в третьей нормальной форме (3НФ), если для любой имеющейся функциональной зависимости между атрибутами таблицы XY атрибут X является ключом. 69

Рассмотрим в качестве примера таблицу, в которой хранится информация о бонусах (премиальных) для сотрудников, причем процент премиальных определяется должностью. Таблица 2.10 БОНУС ID_Worker 1111 2222 3333 4444

Skill Программист I Вед. программ. Программист II Программист II

Bonus 30 % 40 % 20 % 20 %

В таблице присутствует избыточность данных и, следовательно, существует потенциальная опасность возникновения аномалий обновления, ввода и удаления. Наличие функциональной зависимости Skill  Bonus приводит к нарушению 3НФ. Для устранения этой проблемы необходимо разбить данную таблицу на две, каждая из которых будет удовлетворять 3НФ. Процесс разбиения, очень похожий на описанный ранее, можно представить в виде следующих шагов: 1. Создается новая таблица, атрибутами которой будут атрибуты исходной таблицы входящие в противоречащую правилу функциональную зависимость, т.е. Skill  Bonus. Таблица 2.11 ДОЛЖНОСТЬ_БОНУС Skill Программист I Вед. программ. Программист II

Bonus 30 % 40 % 20 %

2. Атрибут, стоящий в правой части функциональной зависимости, исключается из исходной таблицы:

70

Таблица 2.10 БОНУС ID_Worker 1111 2222 3333 4444

Skill Программист I Вед. программ. Программист II Программист II

3. Если имеются другие функциональные зависимости, нарушающие 3НФ, то шаги 1 и 2 повторяются для каждой из них. 4. Если один и тот же детерминант входит в несколько функциональных зависимостей, то все зависящие от него атрибуты помещаются в одну таблицу, ключом которой будет детерминант. Таким образом, в результате разбиения получаются две таблицы меньшего размера, и реляционная схема может быть записана как БОНУС (ID_Worker, Skill) Внешний ключ: Skill  ДОЛЖНОСТЬ_БОНУС ДОЛЖНОСТЬ_БОНУС (Skill, Bonus) Следует отметить, что процесс нормализации выполняется последовательно, т.е. сначала таблица приводится к 1НФ, далее к 2НФ и, наконец, к 3НФ. Таким образом, таблица, находящаяся в 3НФ, находится во 2НФ и тем более в 1НФ. Многозначные зависимости Нормализация – это разбиение таблицы на две или более, обладающих лучшими свойствами при вводе, изменении и удалении данных. Окончательная цель нормализации сводится к получению такого проекта базы данных, в котором каждый факт появляется лишь в одном месте, т.е. исключена избыточность информации. Это делается не столько с целью экономии памяти, сколько для исключения возможной противоречивости хранимых данных. По существу, таблица находится в 2НФ, если она находится в 1НФ и удовлетворяет, кроме того, некоторому дополнительному условию, суть которого будет рассмотрена ниже. Таблица находится в 3НФ, если она находится в 2НФ и, помимо этого, удовлетворяет еще другому дополнительному условию и т.д. 71

Таким образом, каждая нормальная форма является в некотором смысле более ограниченной, но и более желательной, чем предшествующая. Это связано с тем, что "(N+1)-я нормальная форма" не обладает некоторыми непривлекательными особенностями, свойственными "N-й нормальной форме". Общий смысл дополнительного условия, налагаемого на (N+1)-ю нормальную форму по отношению к N-й нормальной форме, состоит в исключении этих непривлекательных особенностей. Теория нормализации основывается на наличии той или иной зависимости между полями таблицы. Определены два вида таких зависимостей: функциональные и многозначные. Функциональные зависимости были рассмотрены ранее. Многозначная зависимость может быть определена следующим образом: Поле А многозначно определяет поле В той же таблицы, если для каждого значения поля А существует хорошо определенное множество соответствующих значений В. Для примера рассмотрим табл. 2.11, в которой есть многозначная зависимость: Дисциплина  Преподаватель Таблица 2.11 ОБУЧЕНИЕ Дисциплина

Преподаватель

Учебник

Базы данных

Иванов И.И.

Мейер Д. Теория реляционных баз данных.

Базы данных

Иванов И.И.

Хансен Г., Хансен Дж. Базы данных

Базы данных

Петров П.П.

Мейер Д. Теория реляционных баз данных.

Базы данных

Петров П.П.

Хансен Г., Хансен Дж. Базы данных

...

...

...

Дисциплина (Базы данных) может может читаться несколькими преподавателями (Ивановым и Петровым). Есть и другая многозначная зависимость: Дисциплина Учебник 72

При этом Преподаватель и Учебник не связаны функциональной зависимостью, что приводит к появлению избыточности (для добавление еще одного учебника придется ввести в таблицу две новых строки). Поэтому целесообразно заменить эту таблицу на две: (Дисциплина—Преподаватель и Дисциплина—Учебник). В 4НФ и 5НФ учитываются не только функциональные, но и многозначные зависимости между полями таблицы. Для определения 4НФ и 5НФ установим значение понятия «полной декомпозиции таблицы». Полной декомпозицией таблицы называют такую совокупность произвольного числа ее проекций, соединение которых полностью совпадает с содержимым таблицы. Таблица находится в пятой нормальной форме (5НФ) тогда и только тогда, когда в каждой ее полной декомпозиции все проекции содержат возможный ключ. Таблица, не имеющая ни одной полной декомпозиции, также находится в 5НФ. Четвертая нормальная форма (4НФ) является частным случаем 5НФ, когда полная декомпозиция должна быть соединением ровно двух проекций. Весьма непросто подобрать реальную таблицу, которая находилась бы в 4НФ, но не была бы в 5НФ. Следует отметить, что 4НФ, 5НФ, а также нормальные формы более высоких порядков носят в основном теоретический характер и на практике практически не используются.

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

73

Глава 3. РАБОТА с MS ACCESS Архитектура приложения MS Access Основными элементами архитектуры MS Access являются [4]: ▪ семейства; ▪ объекты; ▪ свойства; ▪ методы, которые организованы в иерархические структуры моделей объектов. MS Access имеет два основных компонента [4]: ядро приложения (Application engine), контролирующее программный интерфейс приложения и ядро базы данных (Jet DBEngine), управляющее хранением данных и определением всех объектов базы данных. Обобщенная модель ядра приложения Обобщенная архитектура ядра приложения представлена на рис. 3.1, на котором прямоугольниками со сплошными линиями представлены объекты или свойства возвращающие объекты ядра приложения. Прямоугольники с пунктирными линиями представляют собой семейства содержащие наборы однотипных объектов (формы, отчеты, элементы управления и т.д.). При открытии базы данных ядро приложения формирует объект Application, который находится на самом верхнем уровне иерархии. Этот объект содержит семейства Forms, Reports, References и Modules. Каждая форма или отчет содержит семейство Controls, в которое входят все элементы управления формы или отчета. Помимо перечисленных семейств объект Application содержит свойства возвращающие объекты, которые позволяют сообщить разработчику массу полезной информации: ▪ имя текущей строки меню; ▪ тип и имя объекта, в котором находится фокус; ▪ активная страница и т.д. Объект DoCmd позволяет выполнять макрокоманды Visual Basic.

74

Объект DBEngine является связующим звеном с объектами DAO (data Access Object), которые будут рассмотрены ниже.

Applicationengine engine application application Application Forms

Form 1

Modules

Form 2

Reports

Reports 1

References

Reports 2

Controls

Controls

DoCmd DBEngine Screen

ActiveForm ActiveReport ActiveControl MousePointer ActiveDatasheet

CurrentDate

AllTables AllQueries AllViews

Рис. 3.1. Обобщенная архитектура ядра приложения

75

Модель Data Access Object Данная модель лучше всего подходит для использования с приложением Application engine базы данных MS Access, поскольку предоставляет объекты, методы и свойства, разработанные с учетом особенностей ядра базы данных Jet DBEngine. При использовании данной модели необходимо подключить библиотеку Microsoft DAO 3.6 Object Library. Это можно сделать, выбрав пункт меню Tools  References в окне редактора Microsoft Visual Basic. В результате появится форма представленная на рис. 3.2, предназначенная для выбора необходимых библиотек (References).

Рис. 3.2. Форма выбора библиотек Visual Basic Укрупненная модель DAO представлена на рис. 3.3. Как и на рис. 3.1, здесь прямоугольниками со сплошными линиями представлены объекты модели DAO; прямоугольники с пунктирными линиями — семейства, содержащие наборы однотипных объектов. При открытии базы данных объект DBEngine устанавливает семейство Workspaces и стандартный объект Workspace. Для защиты доступа к данным используются объекты User и Group. На заключительном этапе DBEngine создает объект Database (внутри семейства Databases). Каждый объект Database содержит набор семейств (TableDefs, QueryDefs и т.д.), объекты которых позволяют получать 76

доступ к различным объектам базы данных (таблицам, запросам, наборам записей и т.д.) и вносить в них изменения.

DBEngine

Workspaces

Workspace Users

User

Groups

Group

Databases

Database TableDefs

TableDef

Fields

QueryDefs

QueryDef

Fields

Indexes

Index Fields

Recordsets

Recordset

Fields

Рис. 3.3. Укрупненная модель DAO Приведенные структуры доступа к данным и элементам пользовательского интерфейса позволяют разработчикам создавать очень гибкие и удобные приложения. 77

Ссылки на семейства, объекты и свойства Существуют три варианта синтаксиса ссылки на объекты MS Access [4]: ▪ Семейство![Объект], например, Forms![Zakaz]; ▪ Семейство(―Объект‖), например, Forms(―Zakaz‖); ▪ Семейство(Относительный номер объекта), например, Forms(1) будет ссылаться на вторую открытую форму. Объекты внутри семейства нумеруются от нуля. Количество объектов внутри семейства определяется с помощью свойства Count. Например, Forms.Count содержит информацию о количестве открытых форм, а Workspaces.Count определяет количество рабочих пространств в семействе Workspaces. Как было рассмотрено ранее, модель доступа к объектам является иерархической и поэтому полное имя объекта начинается с имени объекта самого верхнего уровня иерархии. Полное имя объекта состоит из нескольких частей разделенных символами точки «.» и(или) восклицательного знака «!». Восклицательный знак ставится перед именем объекта, содержащегося в предшествующем объекте или семействе объектов. Имя объекта, расположенного за восклицательным знаком, обычно принадлежит объекту, созданному пользователем, и, как правило, заключается в квадратные скобки. Точка ставится перед именем семейства, свойства или метода, который может применяться для указанного передним объекта. Имена, расположенные вслед за точкой, никогда не содержат пробелов и в квадратные скобки не заключаются. Например, полное имя (ссылка) DBEngine.Workspaces(0).Databases(0).Tabledefs(1).Name указывает на свойство «Name» второго объекта Tabledef (отсчет начинается от нуля) в текущей базе данных. Похожая ссылка DBEngine.Workspaces(0).Databases(0).Tabledefs(10)![Name] указывает на объект «Name», которое содержится в 11-м объекте Tabledef семейства Tabledefs. Заметим, что объект Tabledef описывает одну таблицу базы данных, и в данном случае объект «Name» является одним из полей описываемой таблицы. Полная информация о семействах, объектах и свойствах содержится в интерактивной справочной системе MS Access. 78

Работа с объектными переменными Рассмотрим процедуру на языке Visual Basic, в которой приводится пример использования объектных переменных для создания новой базы данных в текущем рабочем пространстве. Sub NewDatabases() ‗ Объявление объектных переменных типа Workspace и Database Dim wsp As Workspace Dim dbsCurrent As Database, dbsNew As Database Dim dbsAnother As Database, dbs As Database ‗ Инициализация объектных переменных Set dbsCurrent=CurrentDb() Set wsp=DBEngine.Workspaces(0) ‗ Вызов метода CreateDatabase объекта Workspace Set dbsNew=wsp.CreateDatabase(―Newdb.mdb‖, dbLangGeneral) ‗ Вызов метода OpenDatabase объекта Workspace Set dbsAnother =wsp.OpenDatabase(―Another.mdb‖) ‗ Печать имен баз данных семейства Databases рабочего пространства DBEngine.Workspaces(0) For Each dbs In wsp.Databases Debug.Print dbs.Name Next dbs ‗ Освобождение объектных переменных For Each dbs In wsp.Databases Set dbs=Nothing Next dbs Set wsp=Nothing End Sub Подробная информация о свойствах и методах объектов DAO содержится в интерактивной справочной системе MS Access. 79

Практические аспекты работы с MS Access Рассмотрим несколько практических вопросов создания удобных интерфейсов (форм) для работы с данными в MS Access, используя при этом объектные переменные и модель DAO. В качестве предметной области выберем коммерческую фирму, занимающуюся поставками компьютерной техники. Требуется создать базу данных учета IT-заказов для клиентов. Заказ состоит из нескольких товаров. Товары делятся на типы (мониторы, принтеры и т.д.). Реляционная схема данных, реализованная средствами MS Access, представлена на рис. 3.4.

Рис. 3.4. Реляционная схема данных учета IT-заказов Динамическое изменение «Поля со списком» Рассмотрим механизм динамического изменения содержимого «Поля со списком» в зависимости от выбранного значения из другого «Поля со списком». На рис.3.5 представлена форма «Тип_Товар», в которой содержатся два поля со списком. Особенность работы данной формы состоит в том, что при выборе определенного типа товара в поле «Тип товара» (например «Принтеры»), в поле со списком «Товар» отражаются только товары данного типа (например, принтеры Epson, HP, Xerox). Если же выбран тип 80

«Мониторы», то в списке товаров выводятся мониторы, т.е. происходит динамическое изменение списка «Товар» в зависимости от выбранного типа товара.

Рис. 3.5. Форма «Тип_Товар» Реализацию данного механизма можно представить в виде нескольких этапов: 1. Создать запрос (рис. 3.6) на выборку товаров определенного типа

Рис. 3.6. Запрос на выборку товаров определенного типа SQL вариант запроса: SELECT Tovar.Name FROM Tip_Tovara INNER JOIN Tovar ON Tip_Tovara.Id_Tip = Tovar.Id_Tip WHERE Tip_Tovara.Id_Tip=[Forms]![Тип_Товар]![Tip_Tovara];

81

Как видно, в качестве условия отбора строк из таблицы Tovar использует значение из элемента управления [Tip_Tovara], т.е. из поля со списком «Tip_Tovara». 2. Назначить данный запрос в качестве источника строк полю со списком «Tovar» (рис. 3.7).

Рис. 3.7. Источник строк поля со списком «Tovar» 3. В поле со списком «Tip_Tovara» на вкладке события в строке Выход добавить макрос Обновление (ОбновитьSQL) элемента Tovar (рис. 3.8).

Рис. 3.8. Добавление макроса Обновление

82

Динамическое изменение «Текстового поля» Рассмотрим механизм динамического изменения (пересчета) содержимого ‗Текстового поля‘ при изменении значений в других ‗Текстовых полях‘. Форма «Заказ» (рис. 3.9) предназначена для формирования заказов клиентов. При выборе товара автоматически появляется его цена. При вводе количества вычисляется сумма, которая также пересчитывается при изменении цены (персонально для некоторых клиентов).

Рис. 3.9. Форма «Заказ» для формирования заказов клиентов Для реализации данных возможностей используются обработчики событий: 1. Изменение – для поля со списком Товар (имя объекта – Id_Tov): Private Sub Id_Tov_Change() [Cena] = Id_Tov.Column(2) [Summa] = [Cena]* [Kol_vo] End Sub Выражение Id_Tov.Column(2) означает, что используется 3-й столбец (а это как раз цена) из поля со списком Id_Tov, для которого источником строк является таблица Tovar, в которой 3-м столбцом является Цена товара.

83

2. Выход – для полей Цена и Количество: Private Sub Cena_Change() [Summa] = [Cena] *[Kol_vo] End Sub Private Sub Kol_vo_Exit(Cancel As Integer) [Summa] = [Cena] *[Kol_vo] End Sub В выражении [Summa] = [Cena] *[Kol_vo] структуры [Summa], [Cena] и т.д. применяются для доступа к значениям соответствующих полей. Назначение обработчиков событий осуществляется с помощью панели свойств соответствующих элементов управления (рис. 3.10).

Рис. 3.10. Назначение обработчиков событий Параметрические запросы в формах Рассмотрим форму (рис. 3.11), которая позволяет выводить с помощью параметрического запроса все заказы оформленные для выбранной фирмы. 84

Рис. 3.11. Форма Фирма-Заказ Для реализации данных возможностей нужно выполнить следующие действия: 1. Создать запрос с именем «ЗапросЗаказ»: SELECT Zakaz.N_Zak, Zakaz.Data FROM Zakaz WHERE (((Zakaz.Id_Kl)=[Forms]![Фирма_Заказ]![Klient])); в котором условие отбора задано через значение поля со списком «Klient» (т.е. идентификатор выбранной фирмы). 2. Указать в качестве источника записей основной формы «Фирма-Заказ» данный запрос «ЗапросЗаказ». 3. Создать подчиненную форму (подчиненная форма «Zakaz») с источником записей запрос – «ЗапросЗаказ». 4. В поле со списком «Klient» на вкладке события в строке Изменение добавить макрос Обновление (ОбновитьЗапрос) оставив поле имя элемента пустым. Отображение взаимосвязанной информации Рассмотрим форму (рис. 3.12), которая позволяет динамически отображать взаимосвязанную информацию, а именно: для выбранного клиента (элемент управления «Поле со списком») отображаются все заказы клиента (элемент управления «подчиненная форма»), а при выборе заказа динамически изменяется состав заказа (элемент управления «подчиненная форма»).

85

Рис. 3.12. Форма Клиент_Заказ_Состав_Заказа Этапы построения данной формы: 1. С помощью мастера построения таблиц строится главная форма «Klient», содержащая две подчиненные: «Zakaz» подчиненная форма; «Sostav» подчиненная форма. Мастер сам установит порядок подчинения, если таблицы соединены логическими связями. Данная форма прекрасно работает, но клиента приходится выбирать с помощью блока навигации по записям таблицы, расположенном внизу формы (на рисунке не показан, так как был удален). Хотелось бы выбирать клиента из списка, поэтому следует преобразовать элемент управления «Поле» в «Поле со списком» 2. Изменяются атрибуты элемента управления «Поле со списком: Name»: на вкладке «Данные» очистить свойство «Данные»; на вкладке «Данные» в качестве источника строк указать таблицу Klient (рис. 3.13);

86

Рис. 3.13. Вкладка «Данные» поля со списком «Name» на вкладке «Макет» указать Число столбцов равным двум; на вкладке «Макет» указать Ширину столбцов равной нулю (рис. 3.14)

Рис. 3.14. Вкладка «Макет» поля со списком «Name» 3. Создается обработчик события «Изменение» элемента управления «Поле со списком» (рис. 3.15): Private Sub Name_Change() Me.Zakaz_подчиненная_форма.Requery End Sub

87

Рис. 3.15. Вкладка «События» поля со списком «Name» Как видно, данный обработчик содержит всего одну строку, в которой вызывается метод Requery для подчиненной формы «Zakaz_подчиненная_форма». Ключевое слово «Me» используется для обращения к объектам формы. В базе данных для именования подчиненных форм используется следующее правило: <Главная форма>_<Подчиненная форма>_Pod. Например, Klient – Главная форма, Klient_Zakaz_Pod – подчиненная форма, содержащая заказы клиентов. Klient_Zakaz_Sostav_Pod подчиненная форма содержащая состав заказов клиентов. Список форм и их названий представлен на рис. 3.16.

Рис. 3.16. Список форм и их названий БД «Заказ» 88

Многопараметрические запросы Рассмотрим форму (рис. 3.17), в которой динамически изменяется содержимое таблицы отгрузки товаров за период при изменении содержимого поля со списком «Клиент», а также полей «Дата начала» и «Дата окончания». Кроме того, в поле «Итого» динамически вычисляется соответствующая сумма столбца «Факт_отгрузка». Многопараметрические запросы для динамического отображение взаимосвязанной информации с использованием ‗Поля со списком‘

Рис. 3.17. Форма учета отгрузки товаров клиентам Для реализации данных возможностей нужно сформировать многопараметрический запрос и выполнить следующие действия: 1. Создать главную форму с элементами управления Клиент (тип — ‗Поле со списком‘, имя — Klient), Дата начала — (тип — ‗Тектовое поле‘, имя — Dat1), Дата окончания — (тип — ‗Тектовое поле‘, имя — Dat2). 2. Создать многопараметрический запрос (для таблицы – подчиненной формы), в котором в качестве параметров выступают значения элементов управления (рис. 3.18). Для корректного указания параметрических значений (имен элементов управления) удобно воспользоваться построителем выражений, предварительно открыв главную форму, так как при этом проще получить доступ к ее элементам управления. 89

Рис. 3.18. Многопараметрический запрос SQL вариант запроса: SELECT [Отгрузка-приход].Id_менеджера, [Отгрузкаприход].Дата_отгрузки, [Отгрузка-приход].План_отгрузка, [Отгрузка-приход].Факт_отгрузка FROM Магазины INNER JOIN [Отгрузка-приход] ON Магазины.Id = [Отгрузка-приход].Id_магазин WHERE ((([Отгрузка-приход].Дата_отгрузки) Between [Forms]![Форма1]![Dat1] And [Forms]![Форма1]![Dat2]) AND ((Магазины.Id)=[Forms]![Форма1]![Klient])) ORDER BY [Отгрузка-приход].Дата_отгрузки; 3. Создать многопараметрический запрос для текстового поля «Итого», в котором в качестве параметров выступают значения элементов управления (рис. 3.19).

Рис. 3.19. Многопараметрический запрос 90

SQL вариант запроса: PARAMETERS [Mag] Long, [Dat1] DateTime, [Dat2] DateTime; SELECT Sum([Отгрузка-приход].Факт_отгрузка) AS [SumФакт_отгрузка] FROM Магазины INNER JOIN [Отгрузка-приход] ON Магазины.Id = [Отгрузка-приход].Id_магазин WHERE [Отгрузка-приход].Id_магазин=[Mag] AND [Отгрузкаприход].Дата_отгрузки Between [Dat1] And [Dat2]; 4. Создать подчиненную форму, источником записей которой будет запрос, созданный в п. 2, а режимом работы – режим таблицы. 5. Добавить процедуру обработки события на изменение для поля со списком «Klient» и на выход для текстовых полей «Дата начала» и «Дата окончания»: Private Sub Klient_Change() Forms![Форма1]! [Отгрузки по клиентам за период].Form.Requery Dim dbs As DAO.Database Dim qdf As DAO.QueryDef Dim rst As DAO.Recordset Dim strSQL As String Set dbs = CurrentDb Set qdf = dbs.QueryDefs("AllGroup") qdf.Parameters![Mag] = [Forms]![Форма1]![Klient] qdf.Parameters![Dat1] = [Forms]![Форма1]![Dat1] qdf.Parameters![Dat2] = [Forms]![Форма1]![Dat2] Set rst = qdf.OpenRecordset Itog.Value = rst.Fields(0) End Sub Следует обратить внимание на использование префикса DAO, для типов Database, Recordset, QueryDef. 91

Контрольные вопросы 1. Какие основные семейства и объекты составляют модель DAO? 2. В каком случае для ссылки на семейства, объекты и свойства применяется символ «!», а в каком «.»? 3. Что собой представляет объектная переменная и каким образом осуществляется ее инициализация? 4. Для чего вызывается процедура CurrentDb()? 5. Что собой представляет список событий, связанных с элементом управления?

92

Глава 4. КЛИЕНТ-СЕРВЕРНЫЕ СУБД Сетевое многопользовательское приложение строится по принципу файл-серверной архитектуры. Данные в виде одного или нескольких файлов размещаются на файловом сервере. Файловый сервер принимает запросы, поступающие по сети от компьютеровклиентов, и передает им требуемые данные. Однако обработка этих данных выполняется на компьютерах-клиентах. На каждом из компьютеров запускается полная копия процессора обработки данных (например, DBEngine в случае MS Access). Любая копия процессора обработки данных независимо управляет файлами базы данных. Для обеспечения совместного доступа используется некоторый механизм блокировок. В MS Access для этого применяется специальный файл, который имеет имя, совпадающее с именем файла приложения, но с расширением idb, который обязательно создается для каждого файла базы данных с расширением mdb. В архитектуре "клиент-сервер" сервер базы данных не только обеспечивает доступ к общим данным, но и берет на себя всю обработку этих данных. Клиент посылает на сервер запросы на чтение или изменение данных, которые формулируются на языке SQL. Сервер выполняет все необходимые изменения или выборки, контролируя при этом целостность и согласованность данных, и результаты в виде набора записей или кода возврата посылает на компьютер клиента. Недостатки архитектуры с файловым сервером очевидны и вытекают главным образом из того, что данные хранятся в одном месте, а обрабатываются в другом. Это означает, что их нужно передавать по сети, что приводит к очень высоким нагрузкам на сеть и, вследствие этого, резкому снижению производительности приложения при увеличении числа одновременно работающих клиентов. Вторым важным недостатком такой архитектуры является децентрализованное решение проблем целостности и согласованности данных и одновременного доступа к данным. Такое решение снижает надежность приложения. Архитектура "клиент-сервер" позволяет устранить все указанные недостатки. Кроме того, она позволяет оптимальным образом распределить вычислительную нагрузку между клиентом и серве93

ром, что также влияет на многие характеристики системы: стоимость, производительность, поддержку. Процесс разработки таких систем достаточно сложен и одной из наиболее важных задач является как раз решение о том, как функциональность приложения должна быть распределена между клиентской и серверной частью. Существуют двух-, трех- и многозвенные архитектуры в зависимости от того, сколько промежуточных звеньев включается между клиентом и сервером. Основная задача, которую решает клиентское приложение, — это обеспечение интерфейса с пользователем, т. е. ввод данных и представление результатов в удобном для пользователя виде, и управление сценариями работы приложения. Основные функции серверной СУБД — обеспечение надежности, согласованности и защищенности данных, управление запросами клиентов, быстрая обработка SQL-запросов. Вся логика работы приложения — прикладные задачи, бизнесправила — в двухзвенной архитектуре распределяются разработчиком между двумя процессами: клиентом и сервером. Сначала бóльшая часть функций приложения решалась клиентом, сервер занимался только обработкой SQL-запросов. Такая архитектура получила название "толстый клиент — тонкий сервер". Появление возможности создавать на сервере хранимые процедуры, т. е. откомпилированные программы с внутренней логикой работы, привело к тенденции переносить все бóльшую часть функций на сервер. Сервер становился все более "толстым", а клиент — "все более тонким". Такое решение имеет очевидные преимущества, например его легче поддерживать, так как все изменения нужно вносить только в одном месте — на сервере. Однако язык, на котором пишутся хранимые процедуры, не является достаточно мощным и гибким, чтобы на нем было удобно реализовывать сложную логику приложения. Впоследствии возникла тенденция поручить выполнение прикладных задач и бизнес-правил отдельному компоненту приложения (или нескольким компонентам), которые могут работать как на специально выделенном компьютере — сервере приложений, так и на том же компьютере, где работает сервер базы данных. Так возникли трех- и многозвенные архитектуры "клиент-сервер". Такие приложения являются гибкими, масштабируемыми, но сложными в разработке. 94

В настоящее время общепризнанными лидерами среди клиентсерверных СУБД являются Microsoft SQL Server и Oracle. Это коммерческие СУБД, требующие значительных программноаппаратных ресурсов. В связи с этим широкой популярностью пользуются свободно распространяемые СУБД MySQL [5] и PostgreSQL [6], которые по своим функциональным возможностям и быстродействию мало в чем уступают вышеназванному коммерческому ПО, а по некоторым характеристикам и превосходят их.

СУБД MySQL Дистрибутив СУБД MySQL можно абсолютно бесплатно скачать с сайта www.mysql.com. Этот проект изначально разрабатывался применительно к операционной системе Unix (Linux), хотя в настоящее время имеются дистрибутивы и для ОС MS Windows. В дальнейшем будут рассматриваться примеры применительно к ОС Unix. Запуск и работа с MySQL После установки программный комплекс MySQL содержит довольно большое количество библиотечных, исполняемых и конфигурационных файлов, главным из которых является файл mysqld — сервер MySQL. Для запуска сервера необходимо выполнить команду /usr/local/mysql/bin/safe_mysqld & Предполагается, что MySQL установлен в каталог по умолчанию /usr/local/mysql/. Вообще говоря, собственно сервер MySQL находится в файле mysqld. Однако рекомендуется запускать его не напрямую, а с помощью сценария (скрипта) safe_mysqld. Данный скрипт запускает mysqld, а затем непрерывно проверяет, выполняется ли mysqld, и перезапускает его, если тот неожиданно завершает свою работу. Скрипт safe_mysqld запускается в фоновом режиме, о чем свидетельствует знак амперсанда «&». Далее необходимо запустить клиентское приложение, которое должно установить соединение с сервером базы данных (виртуальный канал) по протоколу TCP/IP. Существует большое количество 95

разнообразных клиентов, в том числе и графических. В стандартный комплект входит простейший клиент, работающий в режиме командной строки. Для его запуска надо ввести команду mysql --host=192.168.0.1 --user=root --password=pass1 myBase здесь host=192.168.0.1 – IP-адрес сервера базы данных; user=root – имя пользователя; password=pass1 – пароль пользователя; myBase – имя базы данных (сразу после установки имеется только одна база данных — mysql). Регистрация нового пользователя, назначение пароля и прав доступа к конкретной базе данных будут рассмотрены ниже, в разделе, посвященном системе безопасности MySQL. При успешном установлении соединения клиент mysql будет готов получать команды от пользователя и выдавать результаты обработки запросов. Пример работы клиента, после установления соединения представлен на рис. 4.1.

Рис. 4.1. Пример работы клиента mysql Следует отметить, что клиент mysql имеет встроенную систему помощи, которую можно вызвать, нажав клавишу знак вопроса «?». Работа с сервером базы данных заключается наборе специальных команд и передаче их серверу для обработки. Команды пред96

ставляют из себя SQL-запросы, с помощью которых можно создать, модифицировать или удалить базу данных, а главное — произвести добавление, изменение, удаление и выборку необходимых данных. Сервер, получив команду от клиента, выполняет запрос (предварительно проверив его корректность) и отправляет результат обратно клиенту. Сервер способен одновременно поддерживать несколько баз данных и обслуживать большое количество запросов. Для обеспечения целостности данных используются механизмы блокировок, транзакций и триггеров, которые будут рассмотрены ниже. Структура хранения данных в MySQL Для хранения всех баз данных отводится специальный каталог, например: /usr/local/mysql/data Для каждой базы данных в указанном каталоге создается свой подкаталог, например база данных mysql хранится в подкаталоге /usr/local/mysql/data/mysql Для хранения таблиц MySQL поддерживает несколько механизмов (storage engines). Наиболее используемы MyISAM (Indexed Sequential Access Management Индексно последовательный метод доступа) и InnoDB (разработка компании Innobase http://www.innodb.com). MyISAM является механизмом по умолчанию и хранит каждую таблицу на диске в виде трех файлов. Файл с расширением frm содержит формат таблицы (т.е. имена и типы полей). Данные хранятся в файле с расширением MYD, а в файле с расширением MYI содержится информация о ключах, индексах и прочих внутренних данных, необходимых для быстрого поиска данных. Например, таблица user из базы данных mysql будет храниться в виде трех файлов: /usr/local/mysql/data/mysql/user.frm /usr/local/mysql/data/mysql/user.MYD /usr/local/mysql/data/mysql/user.MYI. 97

Формат данных MyISAM обеспечивает высокую эффективность обработки данных, однако он не поддерживает механизм транзакций, который будет рассмотрен ниже. Формат InnoDB обеспечивает механизм транзакций и блокировок в стиле СУБД Oracle, сохраняет данные из таблиц, а также информацию о индексах в специальном файле (или нескольких файлах) табличного пространства (tablespace). Размер файла табличного пространства неограничен, даже если операционная система накладывает ограничения на максимальный размер файла. Для восстановления поврежденных файлов формата MyISAM необходимо пользоваться утилитой myisamchk [options] Данная утилита содержит большое количество опций, наиболее полезной из которых является опция восстановления (-r). Например, для восстановления поврежденной таблицы user.MYI необходимо выполнить команду myisamchk -r /usr/local/mysql/data/mysql/user.MYI Система безопасности MySQL Система безопасности MySQL основана на механизме аутентификации пользователей подключающихся к базе данных, а также на механизме авторизации поступающих запросов, т.е. проверке прав доступа к тем или иным данным для того или иного пользователя. Информация о правах пользователей хранится в специальной базе данных mysql [5], которая создается при установке сервера MySQL. База данных mysql содержит более 15 таблиц, однако для системы безопасности наиболее важными являются следующие: ▪ user; ▪ db; ▪ host; ▪ tables_priv; ▪ columns_priv. В таблице user, структура которой представлена в табл. 4.1, хранится информация о правах подключения пользователя к серверу 98

базы данных (но не к конкретной базе данных) с определенного хоста. Таблица 4.1 Структура таблицы user Field Host User Password Select_priv Insert_priv Update_priv --Alter_priv

Type char(60) char(16) char(41) enum('N','Y') enum('N','Y') enum('N','Y') --enum('N','Y')

Default

N N N --N

В текстовых полях Host, User и Password содержится информация о имени пользователя (User), его пароле (Password) и доменном имени или IP-адресе хоста (Host) с которого можно подключаться к серверу MySQL. В столбцах User и Host можно использовать символ процента «%», который является шаблоном, заменяющим произвольный набор символов. Специальное имя пользователя nobody также является шаблоном (равносилен %), охватывающим всех пользователей не упомянутых ранее. Поля Select_priv, Insert_priv,,, Alter_priv имеют тип перечисления и могут принимать одно из двух значений: Y или N. Установка значения в Y дает возможность пользователю выполнять соответствующие SQL команды SELECT, INSERT,,, ALTER. По умолчанию эти поля принимают значение N, и тем самым пользователю разрешается только соединиться с сервером, а дальнейшее разграничение прав регулируется другими таблицами. В таблице user из-за использования шаблонов одному и тому же соединению может соответствовать несколько записей. Поэтому поиск выполняется следующим образом: ▪ ищется соответствие для узлов (хостов), не содержащих шаблонов (пустое поле Host трактуется как %); ▪ для одного и того же узла сначала проверяется соответствие имен, не содержащих шаблонов (пустое поле User трактуется как %); 99

▪

первое найденное соответствие считается окончательным, и дальнейший поиск не производится.

Для пояснения данных правил рассмотрим табл. 4.2. Таблица 4.2 Пример таблицы user Host % k36.mephi.ru k12.mephi.ru

User Peter % Peter

Select_priv Insert_priv Y Y N N Y N

Если пользователь Peter соединяется с сервером MySQL с любого хоста, кроме k36.mephi.ru и k12.mephi.ru, то ему разрешается выполнять SQL-запросы SELECT, INSERT. Если же он выходит на связь с хоста k36.mephi.ru, то эти запросы для него запрещены. В данном случае порядок расположения записей не имеет значения, так как перед выполнением поиска выполняется сортировка таблицы в соответствии с перечисленными правилами. В таблице user не упоминаются конкретные базы данных, т.е. она управляет правами доступа к серверу в целом. Права доступа к отдельным базам данных хранятся в таблице db, структура которой представлена в табл. 4.3. Таблица 4.3 Структура таблицы db Field Host Db User Select_priv Insert_priv --Alter_priv

Type char(60) char(32) char(16) enum('N','Y') enum('N','Y') --enum('N','Y')

Default

N N --N

Данная таблица похожа на таблицу user, но в ней вместо поля Password содержится поле Db, в котором указывается имя базы 100

данных. Права, указанные в таблице user, относятся ко всем базам данных сервера и перекрывают права из таблицы db. Поэтому если в таблице user поле Select_priv равно Y, то значение поля Select_priv из таблицы db не имеет значения. Если же в таблице user поле Select_priv равно N, то производится дальнейшая проверка прав доступа в таблицах db, host и т.д. Считается целесообразным создание в таблице user записей для пользователей, в которых не даны никакие права, т.е. пользователь может лишь подключиться к серверу. Все остальные права регулируются через таблицы db, host, tables_priv, columns_priv. Пустое поле Host в таблице db заставляет MySQL найти запись, соответствующую имени узла пользователя, в таблице host, структура которой представлена в табл. 4.4. Таблица 4.4 Структура таблицы host Field Host Db Select_priv Insert_priv Update_priv --Alter_priv

Type char(60) char(32) enum('N','Y') enum('N','Y') enum('N','Y') --enum('N','Y')

Default

N N N --N

Если соответствие не найдено, то MySQL отказывает в доступе. Если найдено, то права определяются как пересечение прав из таблиц db и host. Общее право доступа может быть записано так:

P Puser  Pdb  Phost

,

где Puser , Pdb , Phost — права доступа (например, Select_priv) из таблиц user, db и host соответственно. Таблицы tables_priv и columns_priv позволяют более тонко настроить права доступа на уровне отдельных таблиц и полей. Структура таблицы tables_priv представлена в табл. 4.5. 101

Таблица 4.5 Структура таблицы tables_priv Field Host Db User Table_name Grantor Timestamp Table_priv

Type char(60) char(60) char(16) char(60) char(77) timestamp(14) set(‗Select‘, ‗Insert‘, ‗Update‘, ‗Delete‘, ‗Create‘, ‗Drop‘, ‗Grant‘, ‗References‘, ‗Index‘, ‗Alter‘) Column_priv set(‗Select‘, ‗Insert‘, ‗Update‘, ‗References‘) Поля Host, Db и User имеют тот же смысл, что и в таблицах user, db, host. Назначение полей Table_name, Grantor и Timestamp понятно из их названия. Поля Table_priv и Column_priv имеют тип set (набор) и могут содержать любое количество предварительно заданных значений. В данном случае список значений представляет набор символьных строк определяющих право на выборку (Select), вставку (Insert) и т.д. В одном наборе может быть до 64 элементов и длина поля колеблется от 1 до 8 байт. Таблица columns_priv имеет структуру, похожую на tables_priv (табл. 4.6). Таким образом, контроль доступа можно представить в виде нескольких этапов: 1. Подключение к серверу MySQL. При подключении проверяется соответствующая запись в таблице user. Если такая запись найдена и поле пароля непустое, то производится проверка введенного пользователем пароля. При неверно заданном пароле подключение не производится. 2. Проверка SQL запроса. Сделанные запросы сопоставляются с правами пользователя пославшего запрос. Поиск соответствующих прав выполняется в следующих таблицах в указанном порядке: ▪ db (host); ▪ tables_priv; ▪ columns_priv. 102

Таблица 4.6 Структура таблицы columns_priv Field Host Db User Table_name Column_name Timestamp Column_priv

Type char(60) char(60) char(16) char(60) char(59) timestamp(14) set(‗Select‘, ‗Insert‘, ‗Update‘, ‗References‘)

При первом найденном соответствии поиск прекращается и запрос немедленно выполняется. Так, например, если у пользователя отсутствует право Insert_priv для заданной базы данных (т.е. в таблице db значение поля Insert_priv равно N), то MySQL продолжит поиск прав доступа в tables_priv и, возможно, даже в columns_priv. Все вышесказанное позволяет строить очень гибкую систему безопасности, предоставляя пользователям права в зависимости от их статуса.

СУБД PostgreSQL История объектно-реляционной СУБД PostgreSQL [6] начинается с 1977 г., с разработки в Калифорнийском университете Беркли базы данных Ingres. В 1996-м был открыт исходный код PostgreSQL для программистов, и началось ее развитие под девизом Open Source. Дистрибутив СУБД PostgreSQL можно абсолютно бесплатно скачать с сайта www.postgresql.org. Проект разрабатывался применительно к операционной системе Unix, тем не менее в настоящее время имеются дистрибутивы и для ОС MS Windows. В дальнейшем будут рассматриваться примеры применительно к ОС Unix.

103

Запуск и работа с PostgreSQL Работа с СУБД PostgreSQL мало чем отличается от работы с СУБД MySQL. Прежде чем запустить сервер PostgreSQL необходимо выполнить ряд шагов: 1. Создать пользователя с именем postgres, useradd postgres 2. Создать каталог расположения базы данных, mkdir /usr/local/pgsql/data 3. Назначить пользователя postgres владельцем данного каталога chown postrges /usr/local/pgsql/data 4. Зарегистрироваться как пользователь postrges su – postgres 5. Инициализировать базу данных с помощью утилиты initdb /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data где в качестве параметра -D указывается каталог расположения базы данных. Для запуска сервера необходимо от имени пользователя postrges выполнить команду /usr/local/mysql/bin/postmaster –D /usr/local/pgsql/data & Сервер postmaster запускается в фоновом режиме, о чем свидетельствует знак амперсанда «&». Кроме того, для управления серверным процессом имеется утилита pg_ctl, позволяющая запускать, останавливать и просматривать состояние процесса. Клиентское приложение устанавливает соединение с сервером базы данных (виртуальный канал) по протоколу TCP/IP (порт 5432). Существует большое количество разнообразных клиентов, в том числе графических. В стандартный комплект входит простейший клиент, работающий в режиме командной строки. Для его запуска надо ввести команду /usr/local/mysql/bin/psql.exe -h 192.168.0.1 -U postgres -d template1 здесь -h 192.168.0.1 – IP-адрес сервера базы данных; -U postgres – имя пользователя; 104

-d template1 — имя базы данных (сразу после установки имеется только одна база данных — template1). Регистрация нового пользователя, назначение пароля и прав доступа к конкретной базе данных будут рассмотрены ниже, в разделе, посвященном системе безопасности PostgreSQL. При успешном установлении соединения, клиент psql будет готов получать команды от пользователя и выдавать результаты обработки запросов. Пример работы клиента, после установления соединения представлен на рис. 4.2

Рис. 4.2. Пример работы клиента psql Структура хранения данных в PostgreSQL СУБД PostgreSQL содержит очень большое количество служебных системных таблиц [6], в которых хранится самая разнообразная информация о базах данных, таблицах, полях таблиц, индексах, представлениях и других объектах. Каждый объект имеет уникальный идентификатор Object IDentifier (OID). Для хранения всех баз данных отводится специальный каталог /usr/local/psql/data/base Для каждой базы данных в указанном каталоге создается свой подкаталог, причем его имя является некоторым числом, например 17458. Это число идентификатор объекта – OID, назначенный Post105

greSQL для созданной базы данных. Соответствие между OID и символьным именем базы данных хранится в системной таблице pg_database. Если выполнить SQL-запрос SELECT datname, oid FROM pg_database; то будет выдана информация об именах баз данных и их OID. В этот подкаталог (в указанном примере — 17458) копируются системные таблицы (а также помещаются таблицы, созданные пользователем при проектировании базы данных), причем имена файлов также являются числами, соответствующими идентификаторам объектов (OID). Соответствие между логическими именами и OID находится в системной таблице pg_class. Если выполнить SQL-запрос SELECT relfilenode, relname FROM pg_class; то будет выдана информация о логических именах таблиц и именах файлов (OID), в которых хранятся данные из этих таблиц. Система безопасности PostgreSQL Механизм аутентификации пользователей сервера PostgreSQL регулируется с помощью простого текстового конфигурационного файла pg_hba.conf, который находится в каталоге /usr/local/psql/data/ Каждая строка этого файла является записью и определяет права доступа к той или иной базе данных. Записи могут быть следующего формата: local DATABASE USER METHOD [OPTION] host DATABASE USER CIDR-ADDRESS METHOD [OPTION] hostssl DATABASE USER CIDR-ADDRESS METHOD [OPTION] hostnossl DATABASE USER CIDR-ADDRESS METHOD [OPTION] Первое поле {local, host, hostssl, hostnossl} определяет, с помощью какого метода (протокола) разрешается подключаться к базе данных. Второе поле DATABASE задает имя базы данных. Кроме того, вместо конкретного имени можно указать слова ―all‖, ―sameuser‖, 106

―samegroup‖ или имя текстового файла с префиксом ―@‖ (например, @filedb), в котором содержатся имена баз данных. Второе поле USER задает имя пользователя, которому разрешается подключаться к указанной базе данных. Кроме того, вместо конкретного имени можно указать слова ―all‖, а также имя группы перед которой ставится знак ―+‖. Как и для поля DATABASE, разрешается указывать имя текстового файла с префиксом ―@‖ (например, @userdb), в котором содержатся имена пользователей. Допускается указывать список (элементы которого разделяются запятыми), состоящий из имен пользователей, имен групп (с префиксом ―+‖), имен файлов (с префиксом ―@‖). Третье поле CIDR-ADDRESS определяет диапазон IP-адресов (указывается IP-адрес и маска), с которых разрешается подключаться к базе данных. Четвертое поле METHOD определяет способ проверки подлинности пользователя и может принимать значения trust, reject, md5, crypt, password, krb4, krb5, ident, pam. Как видно, данные ключевые слова обозначают способ шифрования пароля, безоговорочное доверие (trust) или недоверие (reject) пользователю. Например, в самом простейшем случае запись host all all 127.0.0.1/32 md5 означает, что всем пользователям, для подключения к любой базе данных с локального хоста (т.е. с того же хоста, на котором запущен сервер PostgreSQL) требуется указать пароль, зашифрованный методом md5. Имена пользователей и групп хранятся в специальных системных таблицах pg_shadow (pg_user) и pg_group. Для добавления информации в эти таблицы используются SQL (DDL) команды CREATE USER username WITH PASSWORD ‗password‘ IN GROUP group; CREATE GROUP group WITH USER username; Например, зарегистрируем пользователей peter и demetr: CREATE USER peter WITH PASSWORD ‗qwerty‘; CREATE USER demetr WITH PASSWORD ‗asdfgh‘; 107

а затем создадим группу admins и поместим туда пользователей peter и demetr CREATE GROUP group WITH USER peter, demetr; Более подробная информация по командам создания пользователей имеется в [6], а также в online документации PostgreSQL.

Программный интерфейс для работы с сервером БД Современные клиент серверные СУБД предоставляют разработчикам удобный и простой интерфейс, позволяющий клиентскому приложению взаимодействовать с базой данных. Для этого необходимо выполнить следующие действия: 1) осуществить соединение с БД; 2) выбрать заданную БД; 3) послать SQL-запрос; 4) получить строку ответа; 5) выбрать требуемую информацию; 6) закрыть соединение. API для MySQL Ниже приведен пример простейшей программы, выполняющей указанные действия с СУБД MySQL [5] (жирным шрифтом выделены наиболее значимые строки): #include <sys/time.h> #include <stdio.h> #include <mysql.h> int main (char**args) { MYSQL_RES MYSQL_ROW MYSQL

*result ; row; *connection, mysql ;

// Инициализация структуры MYSQL 108

mysql_init(mysql) ; // Установка соединения (ноль при неудачном соединении) connection=mysql_real_connect(&mysql, “beta.myhost.ru” /*host*/, ―user1‖ /*user*/, ―mypass‖ /*password*/, ―db_test‖ /*database*/, 0 /*port*/, ―‖ /*Unix socket*/, 0 /*flags*/) ; // Выполнение запроса на выборку mysql_query(connection, ―SELECT id, val FROM table1‖); // Чтение всего результата запроса и сохранение в структуре result result=mysql_store_result(connection); // Выборка записей из набора в массив символьных строк row while (( row=mysql_fetch_row(result) != NULL) { printf(―id: %s, значение: %s\n‖, (row[0] ? row[0] : ―NULL‖), (row[1] ? row[1] : ―NULL‖) ); } // Освобождение ресурсов, закрытие соединеня mysql_free_result(result) ; mysql_close(connection); } Как видно, структура данной программы очень проста и основана на использовании специальных функций, описанных в заголовочном файле mysql.h. В примере, для упрощения, не производится проверка результатов выполнения запросов к серверу базы данных. В реальных приложениях данная проверка обязательна, так как, например, при неудачной установке соединения (в этом случае функция возвращает ноль) все последующие действия теряют смысл. 109

API для PostgreSQL Для PostgreSQL используются очень похожие функции [6]. Ниже приведена программа, которая просто устанавливает соединение с сервером PostgreSQL и при удачном результате распечатывает параметры соединения: имя пользователя, хост, порт и т.д. Параметры соединения задаются в текстовой строке conninfo в виде пар значений: dbname=db_test, user=peter и т.д. Работа программы основана на использовании библиотеки libpq-fe. #include <stdlib.h> #include int main(int argc, char** argv) { // строка conninfo состоит из разделенных пробелами параметров // вида <par>= // Параметры: dbname, user, password, host, hostaddr (IP), port char *conninfo="dbname=db_test" ; PGconn *myconnection=PQconnectdb(conninfo); if ( PQstatus(myconnection) == CONNECTION_OK ) { printf ("Connection made: DB=%s User=%s Pass=%s Host=%s Port=%s Options=%s \n", PQdb(myconnection), PQuser(myconnection), PQpass(myconnection), PQhost(myconnection), PQport(myconnection), PQoptions(myconnection) ) ; } else { printf ("Connection failed: %s",QerrorMessage(myconnection)); } PQfinish(myconnection); // освобождение ресурсов return EXIT_SUCCESS; } 110

Далее приводится пример более сложной программы, в которой, помимо соединения с сервером базы данных, выполняется набор SQL-запросов. #include <stdlib.h> #include void doSQL(PGconn *conn, char *command) { PGresult *result ; printf ("%s\n", command) ; // Выполнение операторов SQL // Результат возвращается через специальную структуру PGresult. // Если возвращается NULL, то не хватает памяти для новой // структуры. result=PQexec(conn, command) ; printf("Status is %s\n", PQresStatus(PQresultStatus(result))); printf("Result message %s\n", PQresultErrorMessage(result)) ; printf("#rows affected %s\n", PQcmdTuples(result)) ; // Возвращает результат выполнения запроса switch (PQresultStatus(result)) { // Успешное завершение. Команда не возвращает данные // например CREATE TABLE case PGRES_COMMAND_OK: // Доступ к БД не требуется. Пустой запрос. case PGRES_EMPTY_QUERY: // Успешное завершение. Запрос вернул 0 и более строк case PGRES_TUPLES_OK: { int r, n; int nrows=PQntuples(result); // Поля результата пронумерованы с нуля! int nfields=PQnfields(result); 111

printf("Number of rows returned = %d\n", nrows) ; printf("Number of fields returned = %d\n", nfields) ; for (r=0; r
name VARCHAR)") ; doSQL(conn, "INSERT INTO number values(48, 'Petr')") ; doSQL(conn, "INSERT INTO number values(48, 'Marta')") ; doSQL(conn, "INSERT INTO number values(24, 'Demos')") ; doSQL(conn, "INSERT INTO number values(20, 'Iren')") ; doSQL(conn, "UPDATE number SET name='Sara' WHERE value=20") ; doSQL(conn, "DELETE FROM number WHERE value=20"); doSQL(conn, "SELECT * FROM number WHERE value=48"); } else { printf ("Connection failed: %s", PQerrorMessage(conn)) ; } PQfinish(conn); return EXIT_SUCCESS; } В данном примере используется функция doSQL(…), основной задачей которой является выполнение SQL-запросов для установленного соединения путем вызова функции PQexec(…). В основной функции main происходит вызов функции для установления соединения с базой данных, а затем (если соединение успешно установлено) вызов функций вида: doSQL(conn, "SELECT * FROM number WHERE value=48"); в которых в виде текстовых строк передаются SQL-запросы.

Транзакции Транзакция – это набор операций, который выполняется (или не выполняется) как один логический блок [6, 8]. Операции по изменению содержимого базы данных (вставка, удаление, обновление), входящие в логический блок, должны или произойти все вместе, или ни одна из них. Применение транзакций позволяет обеспечивать определенный уровень целостности и восстанавливаемости данных. Для восстановления данных при ошибках или отказах системы используется журнал транзакций. 113

Типичным примером, иллюстрирующим работу транзакций, является перевод денег с одного счета на другой в какой-либо базе данных финансовой организации (банка). В упрощенном виде данная транзакция состоит из двух операций изменения двух кортежей одной таблицы. Один кортеж относится к счету, с которого списывается заданная сумма, а на другой эта сумма начисляется. Если между операциями списания и начисления произойдет сбой системы, то целостность базы данных будет нарушена (баланс первого счета уменьшится, а второго останется прежним). Следовательно, эти две операции должны выполняться как один логический блок, т.е. должны быть выполнены обе или не выполнены вообще. Каждая транзакция в базе данных изолирована от всех остальных транзакций, происходящих в базе данных в то же самое время. В идеале каждая транзакция должна вести себя так, как будто ей предоставлен полный доступ к базе данных. Однако для высокой производительности часто приходится идти на компромисс. Допустимая для использования транзакция должна обладать ACIDсвойствами. ACID — аббревиатура от Atomicity (атомарность), Consistency (согласованность), Isolation (изолированность), Durability (устойчивость). Рассмотрим каждое из этих требований подробнее. Термин Atomicity (атомарность) означает, что транзакция должна выполняться как элементарная (атомарная) операция. Чтобы транзакция считалась успешно выполненной, должен быть выполнен каждый шаг этой транзакции. При неудачном выполнении хотя бы одного из шагов вся транзакция считается неуспешной, и происходит отмена всех операций, произведенных с начала транзакции. Термин Consistency (согласованность) означает, что после окончания транзакции все данные остаются в согласованном состоянии. Если перед началом транзакции данные находятся в согласованном состоянии, то после выполнения транзакции база данных тоже должна находиться в согласованном состоянии (в новом, при успешном завершении транзакции или в том же самом, что и перед началом транзакции при неуспешном завершении транзакции). Термин Durability (устойчивость) означает, что после того как транзакция фиксирована, влияние этой транзакции в базе данных 114

становится постоянным даже в случае отказа системы. Устойчивость обеспечивается журналом транзакций. Термин Isolation (изолированность) означает, что каждая транзакция выполняется так, как если бы она была единственной в системе, т.е. ее выполнение изолируется от другой параллельно выполняемой транзакции. Модификации данных, вносимые одной транзакцией, не зависят от модификаций данных вносимых другой параллельной транзакцией до тех пор, пока эти модификации не будут зафиксированы. Добиться полной изоляции без снижения производительности работы системы практически невозможно. Поэтому стандарт ANSI/ISO SQL определяет четыре уровня изолированности. Уровень изолированности определяет степень изолированности одной транзакции от другой, т.е. определяет уровень, при котором в транзакции допускаются несогласованные данные. Чем выше уровень, тем выше согласованность (непротиворечивость) данных, но при этом может снижаться количество параллельно выполняемых транзакций и тем самым снижаться общая производительность сервера базы данных. Рассмотрим четыре уровня изолированности: Read uncommitted (чтение незафиксированных данных). Самый низкий уровень. Транзакции практически не изолированы. Запрещается читать только физически поврежденные данные. Read committed (чтение фиксированных данных). Разрешается читать только фиксированные данные. Наиболее используемый уровень. Repeatable read (повторяемость чтения). Повторное чтение одной и той же строки в транзакции дает одинаковый результат. Serializable (упорядочиваемость, сериализация). Самый высокий уровень. Транзакции полностью изолируются друг от друга. Результат параллельного выполнения транзакций совпадает с результатом последовательного выполнения тех же транзакций. Данные уровни изолированности явились результатом анализа поведения параллельно выполняющихся транзакций. Если транзакции затрагивают непересекающиеся данные (например, проводят модификацию разных счетов), то никаких проблем не возникает. Однако это не всегда так. Обычно выделяют три типа поведения параллельно выполняющихся транзакций. 1. Чтение нефиксированных данных (Dirty read — неаккуратное чтение) происходит тогда, когда одна транзакция модифициру115

ет данные, а другая читает модифицированные, но еще не зафиксированные данные. 2. Неповторяемое чтение (Nonrepeatable read) происходит тогда, когда одна транзакция производит чтение одной строки данных более одного раза, а между чтениями другая транзакция вносит изменения в эту строку и фиксирует эти изменения. 3. Фантомное чтение (Phantom read) происходит тогда, когда одна транзакция пытается прочитать строку, которой не существует в начале транзакции, но вставляется второй транзакцией, прежде чем закончится первая. В зависимости от уровня изолированности допускаются те или иные типы поведения. Результаты представлены в табл. 4.6. Таблица 4.6 Поведение транзакций при разных уровнях изолированности Уровни изолированности Read uncommitted

Поведение параллельных транзакций Dirty read Да

Nonrepeatable read Да

Phantom read Да

Read committed

Нет

Да

Да

Repeatable read Serializable

Нет Нет

Нет Нет

Да Нет

Обычно, по умолчанию, в качестве уровня изолированности используется Read committed (чтение фиксированных данных), при котором возможно неповторяемое и фантомное чтение. Переход на более высокие уровни приводит к тому, что сервер базы данных налагает блокировки на более длительные периоды времени, и это приводит к уменьшению производительности работы сервера. Для задания уровня изолированности достаточно выполнить команду (пример для PostgreSQL) SET TRANSACTION ISOLATION LEVEL READ COMMITED или SET TRANSACTION ISOLATION LEVEL SERIALIZABLE СУБД PostgreSQL может работать в режиме явных и неявных транзакций (по умолчанию). В режиме неявных транзакций тран116

закция запускается перед выполнением каждого SQL запроса и фиксируется по его завершении. Для инициализации явной транзакции требуется выполнить команду BEGIN WORK. Режим транзакции включается для всех последующих SQL запросов. Для фиксации транзакции выполняется команда COMMIT WORK а для отказа от фиксации (отката) — команда ROLLBACK WORK. В PostgreSQL не допускаются вложенные транзакции, хотя в других СУБД они возможны. Не рекомендуется делать транзакции содержащими большое количество SQL операторов из-за взаимоблокировок таблиц. Кроме того, в различных транзакциях рекомендуется придерживаться одного и того же порядка обработки таблиц и строк в таблицах.

Хранимые процедуры Во многих случаях целесообразно перенести базовые механизмы первичной обработки данных на сторону сервера. Для этих задач современные клиент-серверные СУБД представляют средства в виде так называемых хранимых (на сервере) процедур, которые создаются с помощью специализированных языков типа в PostgreSQL [6] или PL/SQL в Oracle [7] и т.п. Эти языки содержат много общего, хотя имеются и отличия. Рассмотрим пример создания простейшей хранимой процедуры написанной на языке PL/pgSQL: CREATE FUNCTION add_one(int4) RETURNS int4 AS ' DECLARE n1 integer; BEGIN n1:=1; 117

RETURN $1 + n1 ; END ; ' LANGUAGE 'plpgsql' Для создания функции используется оператор CREATE FUNCTION, далее следуют имя функции и типы ее аргументов. После ключевого слова RETURNS указывается тип возвращаемого значения. В PL/pgSQL функция должна возвращать значение, в противном случае возникнет ошибка времени выполнения. Тело функции помещается после ключевого слова AS и ограничивается апострофами «'». В теле функции существуют два блока: ▪ блок объявления переменных (DECLARE); ▪ блок операторов между ключевыми словами BEGIN — END. Все переменные, адресуемые в функции, должны быть объявлены до их использования. Исключением являются только переменные управления циклом. Тип переменной может быть встроенным типом PostgreSQL (integer, float, timestamp и т.д.) или совпадать с типом некоторого столбца из некоторой таблицы. Это задается следующим образом: v1 table1.field3%TYPE; v2 table3.field1%TYPE; В данном примере переменная v1 будет иметь тот же тип, что и столбец field3 из таблицы table1, а переменная v2 будет иметь тот же тип, что и столбец field1 из таблицы table3. Кроме того, можно объявлять и использовать составные переменные (ROWTYPE и RECORD), которые соответствуют целым строкам некоторой таблицы. Например, r1 table2% ROWTYPE; объявляет переменную r1 имеющую поля из таблицы table2. Для доступа к этим полям применяется точечная нотация r1.field1, r1.field2, где field1, field2 столбцы таблицы table2. Составной тип данных RECORD похож на ROWTYPE, но при его определении не требуется ссылаться на конкретную таблицу. В записи будут содержаться поля, присвоенные переменной во время 118

выполнения хранимой процедуры. Объявление будет выглядеть следующим образом: r2 RECORD; Операторы заканчиваются символом «;». Доступ к аргументам функции осуществляется как $1, $2 и т.д. В конце указывается язык, на котором написана хранимая процедура LANGUAGE 'plpgsql'. Для установления языка хранимых процедур для базы данных следует выполнить команду createlang –U postgres plpgsql mydatabase –L/usr/local/pgsql/lib Вызов хранимой процедуры выполняется аналогично посылке SQL запроса на сервер. После соединения с базой данных (например, с помощью клиента psql) следует отправить запрос SELECT add_one(3); Сервер обработает запрос, отыщет и выполнит процедуру add_one(…) и вернет результат ее выполнения – 4. Для построения более сложной хранимой процедуры рассмотрим простую базу данных учета товаров на складе, состоящую из двух таблиц item (товар) и stock (склад). Опишем данные таблицы на языке DDL, который подробно будет рассмотрен в гл. 5. create table item ( item_id description cost_price sell_price CONSTRAINT ); create table stock ( item_id quantity

serial, varchar(64) not null, numeric(7,2), numeric(7,2), item_pk PRIMARY KEY(item_id)

integer integer 119

not null, not null,

CONSTRAINT

stock_pk

PRIMARY KEY(item_id)

); Рассмотрим применение хранимой процедуры для автоматического формирования таблицы заказов (reorders) для тех товаров, количество которых на складе осталось меньше некоторого минимума. Ниже приводится содержимое файла reorders.sql, основное место в котором занимает хранимая процедура reorders(int4): -- Удалить и создать таблицу reorders DROP TABLE reorders ; CREATE TABLE reorders ( item_id integer, message text ); -- Просмотреть stock, для повторного заказа товаров CREATE FUNCTION reorders(int4) RETURNS integer AS ' DECLARE min_stock alias for $1 ; reorder_item integer ; reorder_count integer ; stock_row stock%rowtype ; msg text ; BEGIN SELECT COUNT(*) INTO reorder_count FROM stock WHERE quantity <= min_stock ; for stock_row in SELECT * FROM stock WHERE quantity <= min_stock loop declare item_row item%rowtype; begin 120

SELECT * INTO item_row FROM item WHERE item_id = stock_row.item_id ; msg=''order more'' || item_row.description || ''s at '' || to_char(item_row.cost_price, ''99.99'') ; INSERT INTO reorders VALUES(stock_row.item_id, msg); end ; end loop ; RETURN reorder_count ; END ; ' LANGUAGE 'plpgsql' ; Запуская данный файл на выполнение, можно автоматически формировать портфель заказов на товары, оставшиеся в малом количестве.

Триггеры Триггер представляет собой механизм запуска хранимой процедуры в случае наступления какого либо события, а именно: применения к таблице операторов INSERT, DELETE, UPDATE. Применение триггеров обеспечивает дополнительную защиту целостности данных, так как препятствует ошибочному, непреднамеренному изменению данных. Прежде чем создавать триггер, необходимо создать специальную хранимую процедуру, называемую триггерной. Триггерная процедура отличается от хранимой тем, что: 1) не имеет параметров, причем аргументы триггерной функции передаются в массиве с именем tg_argv, начиная с tg_argv[0]; 2) имеет специальный тип возврата OPAQUE. Триггерная процедура будет вызвана, когда над заданной таблицей будут проводиться изменения. Она должна вернуть или NULL, или строку, соответствующую структуре таблицы, для которой была вызвана триггерная процедура. Для триггеров AFTER, вызываемых после UPDATE, обычно возвращается NULL. Для триггеров BEFORE возвращаемый результат используется для 121

управления обновлением данных. Если возвращается NULL, то UPDATE не выполняется, если же возвращается строка, то именно она выступает как источник обновления. Триггеры создаются командой CREATE TRIGGER, в которой указывается, для какой таблицы, какого события, до или после этого события будет вызываться триггерная процедура. Рассмотрим пример триггерной процедуры, которая будет вызываться всякий раз после выполнения SQL операторов INSERT или UPDATE над таблицей stock (склад). Триггерная процедура CREATE FUNCTION reorder_trigger() RETURNS OPAQUE AS ' DECLARE mq integer ; item_record record ; BEGIN mq:=tg_argv[0]; raise notice ''in trigger, mq is %'', mq ; if new.quantity <= mq then SELECT * INTO item_record FROM item WHERE item_id <= new.item_id ; INSERT INTO reorders VALUES(new.item_id, item_record.description); end if; RETURN NULL ; END ; ' LANGUAGE 'plpgsql' ; Создание триггера CREATE TRIGGER trig_reorder AFTER INSERT OR UPDATE ON stock FOR EACH ROW EXECUTE PROCEDURE reorder_trigger(3) ; 122

Триггер будет вызываться всякий раз после выполнения команд INSERT или UPDATE по изменению содержимого таблицей stock (склад), т.е. после изменения товаров на складе. Если остаток товара окажется меньше заданного количества (меньше 3), то будет добавлена запись в таблицу reorders, т.е. будет формироваться таблица заказов на товары, оставшиеся в малом количестве.

Контрольные вопросы 1. 2. 3. 4. 5. 6.

Какова структура хранения данных в форматах MyISAM и InnoDB СУБД MySQL? Каково назначение системных таблиц в СУБД PostgreSQL? Каким образом реализуется механизм транзакций и каковы базовые свойства транзакций? Чем отличаются хранимые процедуры от триггеров? Чем отличается составной тип данных RECORD от ROWTYPE? Каким образом выполняется доступ к серверу базы данных из приложений пользователя?

123

Глава 5. ЯЗЫК SQL Cтруктура языка SQL Во второй главе были рассмотрены два фундаментальных языка запросов к реляционным БД: реляционная алгебра и реляционное исчисление. При всей своей строгости и теоретической обоснованности эти языки редко используются в современных реляционных СУБД в качестве средств пользовательского интерфейса. Запросы на этих языках трудно формулировать и понимать. Язык SQL (Structure Query Language) представляет собой некоторую комбинацию реляционного исчисления кортежей и реляционной алгебры. На рис. 5.1 представлена обобщенная структура языка SQL. SQL

Операторы манипулирования данными (SQL)

Операторы манипулирования схемой БД (DDL)

SELECT

CREATE

INSERT

DROP

DELETE

ALTER

UPDATE

GRANT

Рис. 5.1. Обобщенная структура языка SQL Язык SQL состоит из двух частей. Операторы манипулирования данными (SELECT, INSERT, DELETE, UPDATE) ориентированы на удобную и понятную пользователям формулировку запросов к реляционной БД.

124

Операторы определения и манипулирования схемой базы данных DDL — Date Definition Language (CREATE, DROP, ALTER) содержат средства для: ▪ определения и манипулирования схемой БД; ▪ определения ограничений целостности и триггеров; ▪ определения представлений БД; ▪ определения структур физического уровня, поддерживающих эффективное выполнение запросов; ▪ авторизации доступа к отношениям и их полям; ▪ определения точек сохранения транзакций и откатов. Существенной особенностью SQL является возможность указания в запросе потребности группирования отношения-результата по указанным полям с поддержкой условий выборки на всю группу целиком. Такие условия выборки могут содержать агрегатные функции, вычисляемые на группе. Эта возможность SQL главным образом отличает этот язык от языков реляционной алгебры и реляционного исчисления, не содержащих аналогичных средств. Кроме того, в SQL не является обязательным удаление кортежей-дубликатов в окончательном или промежуточном отношенияхрезультатах. Результатом оператора выборки в языке SQL является не отношение, а мультимножество кортежей. Самый общий вид запроса на языке SQL представляет теоретико-множественное алгебраическое выражение, составленное из элементарных запросов, при этом допустимы все базовые теоретико-множественные операции (UNION, INTERSECT и MINUS). Операторы манипулирования данными UPDATE и DELETE построены на тех же принципах, что и оператор выборки данных SELECT. Набор кортежей указанного отношения, подлежащих модификации или удалению, определяется входящим в соответствующий оператор логическим выражением, которое может включать сложные предикаты, в том числе и с вложенными подзапросами. В число операторов определения и манипулирования схемы БД SQL входят операторы создания и уничтожения постоянных и временных хранимых отношений (CREATE TABLE и DROP TABLE) и создания и уничтожения представляемых отношений (CREATE VIEW и DROP VIEW). Оператор манипулирования схемой БД ALTER TABLE позволяет добавлять указываемые поля к существующим отношениям. 125

Язык SQL включает очень мощные средства контроля и поддержания целостности БД. Очень важным механизмом, определенным в языке SQL, является механизм триггеров как средство автоматического поддержания целостности БД. В SQL используются структуры физического уровня: индексы и связи, обеспечивающие эффективное выполнение запросов к БД. Индекс — это инвертированный файл, обеспечивающий доступ к кортежам соответствующего отношения на основе заданных значений одного или нескольких столбцов, составляющих ключ индекса. Важной особенностью языка SQL является обеспечение защиты доступа к данным средствами самого языка. Основная идея такого подхода состоит в том, что по отношению к любому отношению БД и любому столбцу отношения вводится предопределенный набор привилегий. С каждой транзакцией неявно связывается идентификатор пользователя, от имени которого она выполняется. После создания нового отношения все привилегии, связанные с этим отношением и всеми его столбцами, принадлежат только пользователю-создателю отношения. В число привилегий входит привилегия передачи всех или части привилегий другому пользователю, включая привилегию на передачу привилегий. Технически передача привилегий осуществляется при выполнении оператора GRANT. Существует также привилегия изъятия (REVOKE) всех или части привилегий у пользователя, которому они ранее были переданы. Деятельность по стандартизации языка SQL началась практически одновременно с появлением первых его коммерческих реализаций. Первый стандарт ANSI/ISO вышел в октябре 1985 г. В марте 1992-го был выработан окончательный проект стандарта (SQL-92), который охватывает практически все необходимые для реализации аспекты: манипулирование схемой БД, управление транзакциями, подключение к БД, динамический SQL.

Определение схемы данных. Оператор CREATE После создания ER-диаграммы предметной области необходимо перейти к реляционной схеме данных, т.е. описать таблицы, поля таблиц (с указанием имен и типов полей), задать индексы и ограничения целостности и т.д. Для выполнения этих операций предна126

значен язык DDL (Date Definition Language) являющийся одной из составных частей языка SQL. Конструкции языка DDL очень просты и не требуют большого времени для их изучения. Описание любой реляционной схемы должно начинаться с создания базы данных. Для этого выполняется оператор CREATE DATABASE. Например, для создания базы банных mydb необходимо, после соединения с сервером базы данных, выполнить команду (здесь и в дальнейшем будут рассматриваться примеры для СУБД PostgreSQL): CREATE DATABASE mydb; Далее следует присоединиться к вновь созданной базе данных с помощью команды \c mydb; После этого можно создавать таблицы с помощью команды CREATE TABLE, например: CREATE TABLE student ( st_id serial, fio varchar(32) NOT NULL, CONSTRAINT st_pk PRIMARY KEY(st_id) ); После имени таблицы (в примере — student) в круглых скобках задаются имена и типы полей (разделяемые запятыми), а также ограничения для столбцов и таблиц. В приведенном примере в качестве ограничения на столбец fio указано NOT NULL (поле не может быть пустым). Кроме того, можно указать: UNIQUE (уникальность), PRIMARY KEY (первичный ключ), DEFAUL (значение по умолчанию), CHECK (condition) (проверка условий при вводе или обновлении данных), REFERENCES (ссылочная целостность). Ограничения для таблиц очень похожи на ограничения для столбцов, но применяются они не к отдельному столбцу, а к таблице в целом. В примере указано ограничение для таблицы: CONSTRAINT st_pk PRIMARY KEY(st_id) Это означает, что первичным ключом является поле st_id. Можно было бы указать PRIMARY KEY в качестве ограничения для столбца st_id, т.е. записать st_id serial PRIMARY KEY, 127

что является эквивалентным. Однако ограничения для таблиц могут относиться к нескольким столбцам, и это позволяет, например, задавать составные ключи. Наибольшую сложность в задании ограничений представляет ограничение ссылочной целостности REFERENCES, которую можно задавать для столбцов и таблиц. Рассмотрим две таблицы: student (студент) и group_table (группа): CREATE TABLE student ( st_id serial PRIMARY KEY, gr_id integer REFERENCES group_table(gr_id), fio varchar(32) NOT NULL ); CREATE TABLE group_table ( gr_id serial PRIMARY KEY, name varchar(32) NOT NULL ); В таблице student поле gr_id является внешним ключом и для него задается ограничение REFERENCES group_table(gr_id), говорящее о том, что данный внешний ключ ссылается на таблицу group_table с первичным ключом gr_id. Заметим, что в качестве имени таблицы названия групп выбрано group_table, а не group, поскольку GROUP BY является ключевой фразой в SQL запросах и это могло бы привести впоследствии к проблемам при построении и отладке SQL-запросов. Аналогичные ограничения для таблиц можно записать так: CREATE TABLE student ( st_id serial, gr_id integer, fio varchar(32) NOT NULL, CONSTRAINT st_pk PRIMARY KEY(st_id), CONSTRAINT gr_fk FOREIGN KEY(gr_id) REFERENCES group_table(gr_id) ); 128

CREATE TABLE group_table ( gr_id serial, name varchar(32) NOT NULL, CONSTRAINT gr_pk PRIMARY KEY(gr_id), ); Рекомендуется [6] всегда использовать ограничения для таблиц, а не смешивать разные виды ограничений для столбцов и таблиц. Если возникает необходимость удалить какую-то таблицу или вообще базу данных, то необходимо использовать оператор DROP: DROP TABLE student; DROP DATABASE mydb; Изменение структуры таблиц выполняется с помощью команды ALTER, которая имеет простой и понятный синтаксис. Например, с помощью команд ALTER TABLE table_name ADD COLUMN col_name col_type; ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name; ALTER TABLE old_table_name RENAME TO new_table_name; можно добавить и переименовать столбец или переименовать саму таблицу.

Выборка данных. Оператор SELECT Прежде чем переходить к рассмотрению особенностей построения сложных SQL-запросов, рассмотрим общий синтаксис оператора SELECT на примере СУБД PostgreSQL (данное описание получено с помощью ввода команды \h SELECT в psql): SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] * | expression [ AS output_name ] [, ...] [ FROM from_item [, ...] ] [ WHERE condition ] [ GROUP BY expression [, ...] ] 129

[ HAVING condition [, ...] ] [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ] [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ] [ LIMIT { count | ALL } ] [ OFFSET start ] [ FOR UPDATE [ OF table_name [, ...] ] ] где from_item может быть одним из следующих значений: [ONLY] table_name [*] [[AS] alias [(column_alias [, ...])]] ( select ) [AS] alias [(column_alias [, ...])] function_name([argument[,...]]) [AS] alias [(column_alias [,...] | column_definition [, ...])] function_name([argument[,...]]) AS (column_definition [, ...] ) from_item [NATURAL] join_type from_item [ON join_condition | USING ( join_column [, ...])] В самом простейшем виде запрос выглядит так: SELECT * FROM table1; В этом случае будут выбраны все поля и строки таблицы table1 в том порядке, в котором они хранятся в базе данных. Символ звездочка используется для указания всех полей таблицы, причем последовательность вывода полей будет совпадать с последовательностью заданной при создании таблицы. В реальных задачах запросы могут быть очень сложными, иметь несколько десятков строк, а их разработка и отладка аналогичны разработке и отладке программы на языке высокого уровня типа C/C++ или Pascal. Язык SQL нечувствителен к регистру, однако в дальнейших примерах ключевые слова языка SQL (SELECT, FROM, WHERE и т.д.), будут записаны заглавными буквами, а названия таблиц, столбцов, функций будут представлены в нижнем регистре. Основой оператора SELECT является синтаксическая конструкция "табличное выражение (table expression)". Семантика табличного выражения состоит в том, что на основе последовательного применения разделов FROM, WHERE, GROUP BY и HAVING из заданных в разделе FROM таблиц строится некоторая новая ре130

зультирующая таблица, порядок следования строк которой не определен и среди строк которой могут находиться дубликаты. В общем случае при определении запроса на выбор данных (SELECT) используются три дополнительных конструкции: спецификация запроса, выражение запросов и раздел ORDER BY. В спецификации запроса задается список выборки. В результате применения списка выборки к результату табличного выражения производится построение новой таблицы, содержащей то же число строк, но, вообще говоря, другое число столбцов. Кроме того, в спецификации запроса могут содержаться ключевые слова ALL или DISTINCT. При наличии ключевого слова DISTINCT из таблицы, полученной применением списка выборки к результату табличного выражения, удаляются строки-дубликаты; при указании ALL (или просто при отсутствии DISTINCT) удаление строкдубликатов не производится. Выражение запросов — это выражение, строящееся по указанным синтаксическим правилам на основе спецификаций запросов. Операциями, используемыми в выражениях запросов, являются UNION (объединение таблиц), INTERSECT (пересечение таблиц), EXCEPT(вычитание таблиц). К таблицам-операндам выражения запросов предъявляется требование, чтобы все они содержали одно и то же число столбцов, а соответствующие столбцы всех операндов были одного и того же типа. Раздел ORDER BY позволяет установить желаемый порядок просмотра результата выражения запросов. Для этого задается список столбцов результата выражения запросов, и для каждого столбца указывается порядок просмотра строк результата в зависимости от значений этого столбца (ASC — по возрастанию (умолчание), DESC — по убыванию). Столбцы можно задавать по имени (не всегда) или порядковый номер столбца в таблице-результате выражения запросов.

Табличное выражение оператора SELECT Вычисление табличного выражения рассматривается как последовательное применение разделов FROM, WHERE, GROUP BY и 131

HAVING (в указанном порядке) к таблицам, заданным в списке FROM. Раздел FROM Результатом выполнения раздела FROM является расширенное декартово произведение таблиц, заданных списком таблиц раздела FROM. Расширенное декартово произведение определяется следующим образом: «Расширенное произведение R есть мультимножество всех строк r таких, что r является конкатенацией строк из всех идентифицированных таблиц в том порядке, в котором они идентифицированы. Мощность R есть произведение мощностей идентифицированных таблиц. Порядковый номер столбца в R есть n+s, где n — порядковый номер порождающего столбца в именованной таблице T, а s — сумма степеней всех таблиц, идентифицированных до T в разделе FROM». Рядом с именем таблицы можно указывать еще одно имя — некоторый синоним имени таблицы (алиас), который можно использовать в других разделах табличного выражения для ссылки на строки именно этого вхождения таблицы. Если табличное выражение содержит только раздел FROM (это единственный обязательный раздел табличного выражения), то результат табличного выражения совпадает с результатом раздела FROM. Раздел WHERE Если в табличном выражении присутствует раздел WHERE, то он вычисляется после FROM. Вычисление раздела WHERE производится по таким правилам: пусть R — результат вычисления раздела FROM. Тогда условие поиска применяется ко всем строкам R, и результатом раздела WHERE является таблица, состоящая из тех строк R, для которого результатом вычисления условия поиска является true. Если условие выборки включает подзапросы, то каждый подзапрос вычисляется для каждого кортежа таблицы R. В условиях поиска могут использоваться следующие предикаты: 132

▪ сравнения; ▪ between; ▪ in; ▪ like; ▪ null; ▪ с квантором; ▪ exists. Следует отметить, что на эффективность выполнения запроса существенно влияет наличие в условии поиска простых предикатов сравнения (предикатов, задающих сравнение столбца таблицы с константой). Наличие таких предикатов позволяет СУБД использовать индексы при выполнении запроса, т.е. избегать полного просмотра таблицы. Предикат сравнения Для сравнения могут использоваться «стандартные» операции: = | <> | < | > | <= | >= Через "<>" обозначается операция "неравенства". Арифметические выражения левой и правой частей предиката сравнения строятся по общим правилам построения арифметических выражений и могут включать в общем случае имена столбцов таблиц из раздела FROM и константы. Типы данных арифметических выражений должны быть сравнимыми. Если правый операнд операции сравнения задается подзапросом, то дополнительным ограничением является то, что мощность результата подзапроса должна быть не более единицы. Предикат between Предикат between имеет следующий синтаксис: [NOT] BETWEEN AND Например, результат "x BETWEEN y AND z" тот же самый, что результат "x >= y AND x <= z". Результат "x NOT BETWEEN y AND z" тот же самый, что результат "NOT (x BETWEEN y AND z)". 133

Предикат in Предикат in определяется следующими синтаксическими правилами: [NOT] IN {<subquery> | ()} Типы левого операнда и значений из списка правого операнда (напомним, что результирующая таблица подзапроса должна содержать ровно один столбец) должны быть сравнимыми. Значение предиката равно true в том и только в том случае, когда значение левого операнда совпадает хотя бы с одним значением списка правого операнда. Предикат like Предикат like имеет следующий синтаксис: [NOT] LIKE <pattern> [ESCAPE <escape character>] Типы данных столбца левого операнда и образца должны быть типами символьных строк. В разделе ESCAPE должен специфицироваться одиночный символ. Значение предиката равно true, если pattern является подстрокой заданного столбца. При этом если раздел ESCAPE отсутствует, то при сопоставлении шаблона со строкой производится специальная интерпретация двух символов шаблона: символ подчеркивания ("_") обозначает любой одиночный символ; символ процента ("%") обозначает последовательность произвольных символов произвольной длины (может быть, нулевой). Если же раздел ESCAPE присутствует и специфицирует некоторый одиночный символ x, то пары символов "x_" и "x%" представляют одиночные символы "_" и "%" соответственно. Предикат null Предикат null описывается синтаксическим правилом: 134

IS [NOT] NULL Значение "x IS NULL" равно true тогда и только тогда, когда значение x не определено. Значение предиката "x NOT IS NULL" равно значению "NOT x IS NULL". Предикат с квантором Предикат с квантором имеет следующий синтаксис: {ALL | SOME | ANY } <subquery> Пусть x левая часть предиката, а S результат вычисления подзапроса <subquery>. Предикат "x ALL S" имеет значение true, если S пусто или значение предиката "x s" равно true для каждого s, входящего в S. Предикат "x ALL S" имеет значение false, если значение предиката "x s" равно false хотя бы для одного s, входящего в S. В остальных случаях значение предиката "x ALL S" равно unknown. Предикат "x SOME S" имеет значение false, если S пусто или значение предиката "x s" равно false для каждого s, входящего в S. Предикат "x SOME S" имеет значение true, если значение предиката "x s" равно true хотя бы для одного s, входящего в S. В остальных случаях значение предиката "x SOME S" равно unknown. Предикат EXISTS Предикат exists имеет следующий синтаксис: EXISTS <subquery> Значением этого предиката всегда является true или false, и это значение равно true тогда и только тогда, когда результат вычисления подзапроса не пуст.

135

Раздел GROUP BY Если в табличном выражении присутствует раздел GROUP BY, то он выполняется следующим. Синтаксис раздела GROUP BY: GROUP BY [{,}...] Если обозначить через R таблицу, являющуюся результатом предыдущего раздела (FROM или WHERE), то результатом раздела GROUP BY станет разбиение R на множество групп строк, состоящего из минимального числа групп таких, что для каждого столбца из списка столбцов раздела GROUP BY, во всех строках каждой группы, включающей более одной строки, значения этого столбца равны. Раздел HAVING Последним при вычислении табличного выражения используется раздел HAVING (если он присутствует). Синтаксис этого раздела следующий: HAVING <search condition> Раздел HAVING может осмысленно появиться в табличном выражении только в том случае, когда в нем присутствует раздел GROUP BY. Условие поиска этого раздела задает условие на группу строк сгруппированной таблицы. Формально раздел HAVING может присутствовать и в табличном выражении, не содержащем GROUP BY. В этом случае полагается, что результат вычисления предыдущих разделов представляет собой сгруппированную таблицу, состоящую из одной группы без выделенных столбцов группирования. Условие поиска раздела HAVING строится по тем же синтаксическим правилам, что и условие поиска раздела WHERE, и может включать те же самые предикаты. Однако имеются специальные синтаксические ограничения по части использования в условии поиска спецификаций столбцов таблиц из раздела FROM данного табличного выражения. 136

Эти ограничения следуют из того, что условие поиска раздела HAVING задает условие на целую группу, а не на индивидуальные строки. В арифметических выражениях предикатов, входящих в условие выборки раздела HAVING, прямо можно использовать только спецификации столбцов, указанных в качестве столбцов группирования в разделе GROUP BY. Остальные столбцы можно специфицировать только внутри спецификаций агрегатных функций COUNT, SUM, AVG, MIN и MAX, вычисляющих в данном случае некоторое агрегатное значение для всей группы строк. Аналогично обстоит дело с подзапросами, входящими в предикаты условия выборки раздела HAVING: если в подзапросе используется характеристика текущей группы, то она может задаваться только путем ссылки на столбцы группирования. Результатом выполнения раздела HAVING является сгруппированная таблица, содержащая только те группы строк, для которых результат вычисления условия поиска есть true. В частности, если раздел HAVING присутствует в табличном выражении, не содержащем GROUP BY, то результатом его выполнения будет либо пустая таблица, либо результат выполнения предыдущих разделов табличного выражения, рассматриваемый как одна группа без столбцов группирования. Агрегатные функции В стандарте SQL-92 определены пять стандартных агрегатных функций: ▪ COUNT(*) — число строк или значений; ▪ COUNT(DISTNICT ) — число строк или значений; ▪ MAX(DISTNICT ) — максимальное значение; ▪ MIN(DISTNICT ) — минимальное значение; ▪ SUM(DISTNICT ) — суммарное значение; ▪ AVG(DISTNICT ) — среднее значение. Агрегатные функции предназначены для того, чтобы вычислять некоторое значение для заданного множества строк. Таким множе137

ством строк может быть группа строк, если агрегатная функция применяется к сгруппированной таблице, или вся таблица. Для всех агрегатных функций, кроме COUNT(*), фактический (т.е. требуемый семантикой) порядок вычислений следующий: на основании параметров агрегатной функции из заданного множества строк производится список значений. Затем по этому списку значений производится вычисление функции. Если список оказался пустым, то значение функции COUNT для него есть 0, а значение всех остальных функций - null. Пусть T обозначает тип значений из этого списка. Тогда результат вычисления функции COUNT — точное число с масштабом и точностью, определяемыми в реализации. Тип результата значений функций MAX и MIN совпадает с T. При вычислении функций SUM и AVG тип T не должен быть типом символьных строк. Вычисление функции COUNT(*) производится путем подсчета числа строк в заданном множестве. Все строки считаются различными, даже если они состоят из одного столбца со значением null во всех строках. Если агрегатная функция специфицирована с ключевым словом DISTINCT, то список значений строится из значений указанного столбца. Далее из этого списка удаляются неопределенные значения, и в нем устраняются значения-дубликаты. Затем вычисляется указанная функция. Если агрегатная функция специфицирована без ключевого слова DISTINCT, то список значений формируется из значений арифметического выражения, вычисляемого для каждой строки заданного множества. Далее из списка удаляются неопределенные значения, и производится вычисление агрегатной функции. Рассмотрим различные случаи применения агрегатных функций в списке выборки в зависимости от вида табличного выражения. Если результат табличного выражения R не является сгруппированной таблицей, то появление хотя бы одной агрегатной функции от множества строк R в списке выборки приводит к тому, что R неявно рассматривается как сгруппированная таблица, состоящая из одной (или нуля) группы с отсутствующими столбцами группирования. Поэтому в этом случае в списке выборки не допускается прямое использование спецификаций строк R: все они должны находиться внутри спецификаций агрегатных функций. Результатом 138

запроса станет таблица, состоящая не более чем из одной строки, полученной путем применения агрегатных функций к R. Аналогично обстоит дело в том случае, когда R представляет собой сгруппированную таблицу, но табличное выражение не содержит раздела GROUP BY (и, следовательно, содержит раздел HAVING). Результат табличного выражения явно объявлен сгруппированной таблицей, состоящей из одной группы, и результат запроса можно формировать только путем применения агрегатных функций к этой группе строк. Результатом запроса станет таблица, состоящая не более чем из одной строки, полученной путем применения агрегатных функций к R. Если табличное выражение содержит раздел GROUP BY и, следовательно, определен, по крайней мере, один столбец группирования, то в этом случае правила формирования списка выборки полностью соответствуют правилам формирования условия выборки раздела HAVING: допускает прямое использование спецификации столбцов группирования, а спецификации остальных столбцов R могут появляться только внутри спецификаций агрегатных функций. Результатом запроса является таблица, число строк в которой равно числу групп в R, и каждая строка формируется на основе значений столбцов группирования и агрегатных функций для данной группы.

Примеры построения SQL-запросов Рассмотрим несколько примеров иллюстрирующих особенности построения SQL-запросов. Основное внимание будет уделено использованию подзапросов и агрегатных функций. База данных туристических поездок Реляционная схема базы данных (travel) учета туристических поездок студентов в разные страны описана с помощью языка описания данных DDL (Date Definition Language): CREATE DATABASE travel; CREATE TABLE student ( st_id serial,

139

fio CONSTRAINT

varchar(32) st_pk

NOT NULL, PRIMARY KEY(st_id)

); CREATE TABLE country ( c_id serial, k_id integer, name varchar(64) not null, CONSTRAINT c_pk PRIMARY KEY(c_id), CONSTRAINT for_key FOREIGN KEY k_id REFERENCES kont(k_id) ); CREATE TABLE kont ( k_id serial, name varchar(64) not null, CONSTRAINT k_pk PRIMARY KEY(k_id) ); CREATE TABLE travel ( tr_id serial, st_id integer not null, c_id integer not null, date date not null, CONSTRAINT travel_pk PRIMARY KEY(tr_id), CONSTRAINT for_key1 FOREIGN KEY st_id REFERENCES student(st_id), CONSTRAINT for_key2 FOREIGN KEY c_id REFERENCES country(c_id) );

База данных состоит из четырех таблиц: 1) student (студент); 2) country (страна); 3) kont (континент); 4) travel (путешествие, поездка). Запрос 1 Перечислить студентов, которые были в Великобритании Для выполнения этого запроса воспользуемся квантором существования EXISTS. Как уже отмечалось выше, язык SQL не чувствителен к регистру, а сам запрос может быть расположен на не140

скольких строках. Настоятельно рекомендуется структурировать запрос, располагая вложенные подзапросы с некоторым отступом и размещая ключевые слова FROM, WHERE и т.д. под тем ключевым словом SELECT, к которому они относятся. В этом случае его легче понимать и анализировать. SELECT fio FROM student WHERE EXISTS (SELECT travel.st_id FROM travel, country WHERE travel.c_id=country.c_id AND travel.st_id=student.st_id AND country.name='GreatBritain' ) Рассматривая данный запрос нетрудно заметить, что он состоит из главного запроса SELECT fio FROM student и подзапроса SELECT travel.st_id FROM travel, country WHERE travel.c_id=country.c_id AND travel.st_id=student.st_id AND country.name='GreatBritain'. Подзапрос формирует таблицу, состоящую из одного столбца travel.st_id (идентификатор студента). Строками данной таблицы будут идентификаторы студентов побывавших в Великобритании. В предложении WHERE содержится условие travel.st_id=student.st_id, в котором фигурирует значение st_id из таблицы student внешнего запроса (таблица student указана в предложении FROM внешнего запроса). Имеет место так называемая корреляция основного запроса и подзапроса. Порядок выполнения коррелированных запросов следующий: 1. Из внешнего запроса выбирается первая строка (т.е. информация о первом студенте), 2. Выполняется подзапрос для значения student.st_id из первой строки. Подзапрос вернет непустую таблицу, если этот студент ездил в Великобританию и, следовательно, квантор существо141

вания (предикат) EXISTS будет иметь значение true (истина). Если студент не ездил в Великобританию, то подзапрос вернет пустую таблицу и квантор существования EXISTS будет иметь значение false (ложь). 3. Фамилия студента из первой строки будет помещена в результирующую таблицу, если значение квантора существования EXISTS окажется истинным. 4. Из внешнего запроса выбирается вторая строка, и весь процесс повторяется заново. Так продолжается для всех строк из таблицы student. Следует отметить, что указание таблицы student в подзапросе приведет к неверной выборке информации. Запрос 2 Перечислить студентов, которые были во Франции и Великобритании Данный запрос очень похож на предыдущий, но здесь добавляется еще один подзапрос с помощью которого выбирается информация о посещении студентом Франции. Два предиката EXISTS объединяются в одно логическое условие с помощью оператора AND: SELECT fio FROM student WHERE EXISTS (SELECT travel.st_id FROM travel, country WHERE travel.c_id=country.c_id AND travel.st_id=student.st_id AND country.name='GreatBritain' ) AND EXISTS (SELECT travel.st_id FROM travel, country WHERE travel.c_id=country.c_id AND travel.st_id=student.st_id AND country.name='France' ) 142

Если СУБД поддерживает операцию пересечения INTERSECT (например, PostgreSQL – поддерживает, а MS Access 2003 – нет), то данный запрос можно представить как SELECT DISTINCT fio FROM student, travel, country WHERE student.st_id=travel.st_id AND travel.c_id=country.c_id AND country.name='GreatBritain' INTERSECT SELECT DISTINCT fio FROM student, travel, country WHERE student.st_id=travel.st_id AND travel.c_id=country.c_id AND country.name='France' Здесь каждый из запросов выбирает фамилии студентов ездивших или в Великобританию или во Францию. Пересечение этих таблиц (множеств) будет давать ответ на поставленный запрос. И, наконец, рассмотрим способ ответа на данный запрос с помощью предиката IN. SELECT fio FROM student WHERE student.st_id IN (SELECT travel.st_id FROM travel, country WHERE travel.c_id=country.c_id AND country.name='GreatBritain' ) AND student.st_id IN (SELECT travel.st_id FROM travel, country WHERE travel.c_id=country.c_id AND country.name='France' ) Подзапросы обязательно должны возвращать таблицу из одного столбца, причем тип поля этой таблицы должен быть аналогичен типу левого операнда в предикате IN. Следует отметить отсутствие 143

корреляции между внешним и внутренними подзапросами, так как внутренние подзапросы могут быть выполнены автономно вне зависимости от значения st_id из таблицы student. Отсутствие корреляции приводит к уменьшению времени выполнения запроса. Запрос 3 Перечислить студентов, которые были только один раз во Франции и только один раз в Великобритании Реализуем данный запрос с помощью агрегатной функции COUNT(): SELECT fio FROM student WHERE (SELECT COUNT(travel.st_id) FROM travel, country WHERE travel.c_id=country.c_id AND travel.st_id=student.st_id AND country.name='GreatBritain' )=1 AND (SELECT COUNT(travel.st_id) FROM travel, country WHERE travel.c_id=country.c_id AND travel.st_id=student.st_id AND country.name='France' )=1 Подзапросы коррелированны с основным запросом, поскольку в условиях отбора WHERE фигурирует поле st_id из таблицы student внешнего запроса. В подзапросах с помощью функции COUNT(travel.st_id) производится подсчет количества строк в результирующей таблице, т.е. количества поездок для каждого студента в Великобританию или во Францию. Так как подзапросы возвращают таблицу, состоящую из одной строки и одного столбца, можно выполнять непосредственной сравнение с заданным значением, в данном случае — с единицей. 144

Запрос 4 Перечислить количество туристических поездок каждого студента. Для тех, кто ни разу никуда не ездил, указать ноль Для решения данной задачи объединим с помощью UNION два запрос. В первом запросе подсчитаем с помощью функции COUNT количество поездок каждого студента, а во втором выберем студентов, которые никуда не ездили: SELECT fio, COUNT(travel.c_id) FROM student, travel WHERE student.st_id=travel.st_id GROUP BY student.fio UNION SELECT fio, 0 FROM student WHERE NOT EXISTS (SELECT * FROM travel WHERE student.st_id=travel.st_id ) ORDER BY fio Во втором запросе имеются коррелированный подзапрос и отрицание квантора существования EXISTS. В этом случае комбинация NOT EXISTS будет возвращать истину, если множество SELECT * FROM travel WHERE student.st_id=travel.st_id будет пустым, т.е. выбранный студент не совершил ни одного путешествия. Конструкция ORDER BY fio приведет к сортировке результирующей таблицы по фамилиям по алфавиту. Запрос 5 Перечислить количество стран, которые посетил каждый студент. Для тех, кто ни разу никуда не ездил, указать ноль SELECT fio, COUNT(DISTINCT travel.c_id) FROM student, travel 145

WHERE student.st_id=travel.st_id GROUP BY student.fio UNION SELECT fio, 0 FROM student WHERE NOT EXISTS (SELECT * FROM travel WHERE student.st_id=travel.st_id ) ORDER BY fio

Данный запрос отличается от запроса 4 только тем, что при аргументе функции COUNT(DISTINCT travel.c_id) указано ключевое слово DISTINCT, которое исключает из списка одинаковые значения. Запрос 6 Перечислить студентов, которые посетили максимальное количество стран (таких студентов может быть несколько) SELECT fio FROM student WHERE student.st_id IN (SELECT travel.st_id FROM travel GROUP BY travel.st_id HAVING COUNT(DISTINCT travel.c_id)= (SELECT MAX(cn) FROM (SELECT COUNT(DISTINCT travel.c_id) AS cn FROM travel GROUP BY travel.st_id ) AS sel_cn ) )

В данном запросе имеется несколько подзапросов. Прежде всего, рассмотрим самый внутренний подзапрос 1: 146

SELECT COUNT(DISTINCT travel.c_id) AS cn FROM travel GROUP BY travel.st_id

в котором используется только одна таблица travel. Группировка строк по полю st_id и использование агрегатной функции COUNT(DISTINCT travel.c_id) позволяет подсчитать количество стран, которые посетил каждый студент. Результатом данного запроса будет таблица, состоящая из одного столбца. Количество строк будет равно количеству студентов совершивших хотя бы одно путешествие. Значениями таблицы будут числа равные числу стран, которые посетил тот или иной студент. В данной таблице нет информации о студентах (их идентификаторах), но они и не нужны, так как результаты этого подзапроса используются только для определения максимального значения с помощью агрегатной функции MAX в подзапросе 2: SELECT MAX(cn) FROM (SELECT COUNT(DISTINCT travel.c_id) AS cn FROM travel GROUP BY travel.st_id ) AS sel_cn

В подзапросе 3 SELECT travel.st_id FROM travel GROUP BY travel.st_id HAVING COUNT(DISTINCT travel.c_id) = max_travel

снова выполняется группировка строк таблицы travel по полю st_id, но для отбора групп, в предложении HAVING записано условие: COUNT(DISTINCT travel.c_id) = max_travel

где max_travel вычисляется с помощью подзапроса 2. Результатом выполнения подзапроса 3 является таблица, состоящая из одного 147

столбца и содержащая идентификаторы студентов, побывавших в максимальном количестве стран. Внешний запрос выбирает из таблицы student фамилии студентов с помощью предиката IN (множество для этого предиката формирует подзапрос 3). Запрос 7 Подсчитать среднее количество поездок, приходящееся на каждого студента группы и перечислить студентов, которые совершили количество поездок выше среднего

Данный запрос можно реализовать следующим образом: SELECT fio, COUNT(travel.c_id) FROM student, travel WHERE student.st_id=travel.st_id GROUP BY student.fio HAVING COUNT(travel.c_id) >= (SELECT AVG(Cn_Tr) FROM (SELECT fio, COUNT(travel.c_id) AS Cn_Tr FROM student, travel WHERE student.st_id=travel.st_id GROUP BY student.fio UNION SELECT fio, 0 AS Cn_Tr FROM student WHERE NOT EXISTS (SELECT * FROM travel WHERE student.st_id=travel.st_id ) ) AS Sel_Cn )

Для вычисления среднего количества поездок, приходящегося на каждого студента, используется агрегатная функция AVG(). Для того чтобы учесть студентов, которые вообще никуда не ездили, используется объединение двух подзапросов с помощью оператора UNION. Отбор подходящих студентов выполняется с помощью условия, записанного в предложении HAVING.

148

Запрос 8 Перечислить студентов, которые совершили поездки во все страны, представленные в таблице country Запросы подобного вида могут быть реализованы путем использования двойного отрицания квантора существования EXISTS. Для этого запрос 8 можно записать так: Перечислить студентов, для которых НЕ существует стран (из таблицы country), куда бы они НЕ совершили поездки. Двойное отрицание существования можно представить в следующем виде: SELECT fio FROM student WHERE NOT EXISTS (SELECT * FROM country WHERE NOT EXISTS (SELECT * FROM travel WHERE travel.c_id=country.c_id AND travel.st_id=student.st_id ) )

Здесь имеется внешний запрос, содержащий коррелированный подзапрос 1: SELECT * FROM country WHERE NOT EXISTS (SELECT * FROM travel WHERE travel.c_id=country.c_id AND travel.st_id=student.st_id ) в котором содержится еще один коррелированный подзапрос 2:

149

SELECT * FROM travel WHERE travel.c_id=country.c_id AND travel.st_id=student.st_id Подзапрос 1 возвращает множество (таблицу) стран, в которые студент не ездил, а подзапрос 2 — множество (таблицу) поездок конкретного студента в конкретную страну. Порядок выполнения данного запроса следующий: 1. Из внешнего запроса выбирается первая строка (т.е. информация о первом студенте), 2. Выполняется подзапрос 1 для значения student.st_id из первой строки таблицы student. Данный подзапрос возвращает множество стран (строки из таблицы country) в которые этот студент не ездил. 3. Для выполнения подзапроса 1 необходимо для каждой строки таблицы country выполнить подзапрос 2. Данный подзапрос возвращает множество строк из таблицы travel, если студент ездил в указанную страну. Если студент не ездил в указанную страну, то множество будет пустым. Страна попадет в результирующее множество подзапроса 1, если множество из подзапроса 2 будет пустым и наоборот. 4. Если подзапрос 1 вернет пустое множество, то отрицание квантора существования NOT EXISTS будет иметь значение true (истина) и фамилия студента из первой строки будет помещена в результирующую таблицу, в противном случае фамилия студента будет пропушена. 5. Из внешнего запроса выбирается вторая строка, и весь процесс повторяется заново. Так продолжается для всех строк из таблицы student.

И в заключение приведем без комментариев довольно большой и сложный запрос. Запрос 9 Перечислить студентов с максимальным приростом числа поездок в 2009 г. по сравнению с 2008-м

150

Данный запрос довольно громоздок, но он состоит из двух однотипных блоков разделенных строкой: T_Prir.Prir = (SELECT MAX(Prir)…) В каждом из блоков производится подсчет количества поездок каждого студента за 2008 и 2009 гг. На основании этих подсчетов вычисляется прирост количества поездок, а также максимальное значение этого прироста. SELECT fio, Prir FROM student, (SELECT Col2008.st_id, Col2009.Col_Tr-Col2008.Col_Tr AS Prir FROM (SELECT travel.st_id, COUNT(travel.c_id) As Col_Tr FROM travel WHERE date>='01/01/2008' AND date<='12/31/2008' GROUP BY travel.st_id UNION SELECT st_id, 0 As Col_Tr FROM student WHERE NOT EXISTS (SELECT * FROM travel WHERE student.st_id=travel.st_id AND date>='01/01/2008' AND date<='12/31/2008' ) ) AS Col2008, (SELECT travel.st_id, COUNT(travel.c_id) As Col_Tr FROM travel WHERE date>='01/01/2009' AND date<='12/31/2009' GROUP BY travel.st_id UNION SELECT st_id, 0 As Col_Tr FROM student WHERE NOT EXISTS (SELECT * FROM travel WHERE student.st_id=travel.st_id AND date>='01/01/2009' AND date<='12/31/2009' ) ) AS Col2009 151

WHERE Col2008.st_id=Col2009.st_id ) AS T_Prir WHERE student.st_id=T_Prir.st_id AND T_Prir.Prir = (SELECT MAX(Prir) FROM (SELECT Col2008.st_id, Col2009.Col_Tr-Col2008.Col_Tr AS Prir FROM (SELECT travel.st_id, COUNT(travel.c_id) As Col_Tr FROM travel WHERE date>='01/01/2008' AND date<='12/31/2008' GROUP BY travel.st_id UNION SELECT st_id, 0 As Col_Tr FROM student WHERE NOT EXISTS (SELECT * FROM travel WHERE student.st_id=travel.st_id AND date>='01/01/2008' AND date<='12/31/2008' ) ) AS Col2008, (SELECT travel.st_id, COUNT(travel.c_id) As Col_Tr FROM travel WHERE date>='01/01/2009' AND date<='12/31/2009' GROUP BY travel.st_id UNION SELECT st_id, 0 As Col_Tr FROM student WHERE NOT EXISTS (SELECT * FROM travel WHERE student.st_id=travel.st_id AND date>='01/01/2007' AND date<='12/31/2007' ) ) AS Col2009 WHERE Col2008.st_id=Col2009.st_id ) AS T_Max ) 152

Контрольные вопросы 1. 2. 3. 4. 5. 6.

В какой последовательности выполняются разделы оператора SELECT? Каково назначение раздела GROUP BY и что собой представляют агрегатные функции? Чем отличается использование предиката IN от квантора существования EXISTS? Что собой представляют коррелированные и некоррелированные SQL-запросы? Каким образом задаются ограничения для столбцов и таблиц при создании базы данных? Написать SQL-запрос вида: «Выбрать студентов, у которых соотношение (Стоимость путевки)/(Длительность путешествия) была минимальна».

153

Список рекомендуемой литературы Основная 1. Хансен Г., Хансен Дж. Базы данных: разработка и управление: Пер. с англ. М.: БИНОМ, 1999.— 704 с. 2. Мейер Д. Теория реляционных баз данных. М.: Мир, 1987.— 540 с. 3. Маклаков С.В. BPWin и ERWin. CASE – средства разработки информационных систем. М.:Диалог-МИФИ, 2001.— 304 с. 4. Вейскас Дж. Эффективная работа с Microsoft Access 2000. СПб: Питер, 2001.— 1040 с. 5. Яргер Р., Риз Дж., Кинг Т. MySQL и mSQL. Базы данных для небольших предприятий и Интернета. СПб: Символ-Плюс, 2000.— 560 с. 6. Стоунз Р., Мэтью Н. PostgreSQL. Основы: Пер. с англ. СПб: Символ-Плюс, 2002.— 640 с. 7. Смирнов С.Н. Работаем с ORACLE. М.: Гелиос, 1998.— 320 с. 8. Интернет ресурс www.intuit.ru Дополнительная 9. Когаловский М.Р. Абстракции и модели в системах баз данных// Журнал «СУБД». М.: Открытые системы, 4—5/1998// www.cemi.rssi.ru/mei/articles/kog98.htm 10. Пржиялковский В.В. Абстракции в проектировании баз данных. // osp.aanet.ru/dbms/1998/01_02/090.htm

154

Константин Яковлевич Кудрявцев

СОЗДАНИЕ БАЗ ДАННЫХ Учебное пособие

Редактор Е. Г. Станкевич Подписано в печать 19.10.2010. Формат 60х84 1/16. Объем 9,75 п.л. Уч.-изд.л. 9,75. Тираж 100 экз. Изд. № 102-1. Заказ

Национальный исследовательский ядерный университет «МИФИ». Типография НИЯУ МИФИ, 115409 Москва, Каширское шоссе, 31.

155