Модель удаленного доступа к данным
В модели удаленного доступа (Remote Data Access, RDA) база данных хранится на сервере. На сервере же находится ядро СУБД. На клиенте располагается презентационная логика и бизнес - логика приложения. Клиент обращается к серверу с запросами на языке SQL. Структура модели удаленного доступа приведена на рис. 10.5.
Рис. 10.5. Модель удаленного доступа (RDA)
Преимущества данной модели:
перенос компонента представления и прикладного компонента на клиентский компьютер существенно разгрузил сервер БД, сводя к минимуму общее число процессов в операционной системе;
205
сервер БД освобождается от несвойственных ему функций; процессор или процессоры сервера целиком загружаются операциями обработки данных, запросов и транзакций. (Это становится возможным, если отказаться от терминалов, не располагающих ресурсами, и заменить их компьютерами, выполняющими роль клиентских станций, которые обладают собственными локальными вычислительными ресурсами);
резко уменьшается загрузка сети, так как по ней от клиентов к серверу передаются не запросы на ввод - вывод в файловой терминологии, а запросы на SQL, и их объем существенно меньше. В ответ на запросы клиент получает только данные, релевантные запросу, а не блоки файлов, как в FS - модели.
Основное достоинство RDA - модели - унификация интерфейса "клиент - сервер", стандартом при общении приложения-клиента и сервера становится язык SQL.
Недостатки:
все-таки запросы на языке SQL при интенсивной работе клиентских приложений могут существенно загрузить сеть;
так как в этой модели на клиенте располагается и презентационная логика, и бизнес-логика приложения, то при повторении аналогичных функций в разных приложениях код соответствующей бизнес - логики должен быть повторен для каждого клиентского приложения. Это вызывает излишнее дублирование кода приложений;
сервер в этой модели играет пассивную роль, поэтому функции управления информационными ресурсами должны выполняться на клиенте. Действительно, например, если нам необходимо выполнять контроль страховых запасов товаров на складе, то каждое приложение, которое связано с изменением состояния склада, после выполнения операций модификации данных, имитирующих продажу или удаление товара со склада, должно выполнять проверку на объем остатка, и в случае, если он меньше страхового запаса, формировать соответствующую заявку на поставку требуемого товара. Это усложняет клиентское приложение, с одной стороны, а с другой - может вызвать необоснованный заказ дополнительных товаров несколькими приложениями.
206
205 :: 206 :: Содержание
Модель удаленного управления данными. Модель файлового сервера
Модель удаленного управления данными также называется моделью файлового сервера (File Server, FS). В этой модели презентационная логика и бизнес-логика располагаются на клиенте. На сервере располагаются файлы с данными и поддерживается доступ к файлам. Функции управления информационными ресурсами в этой модели находятся на клиенте.
Распределение функций в этой модели представлено на рис. 10.4.
В этой модели файлы базы данных хранятся на сервере, клиент обращается к серверу с файловыми командами, а механизм управления всеми информационными ресурсами, собственно база мета - данных, находится на клиенте.
Рис. 10.4. Модель файлового сервера
204
Достоинства этой модели в том, что мы уже имеем разделение монопольного приложения на два взаимодействующих процесса. При этом сервер (серверный процесс) может обслуживать множество клиентов, которые обращаются к нему с запросами. Собственно СУБД должна находиться в этой модели на клиенте.
Каков алгоритм выполнения запроса клиента?
Запрос клиента формулируется в командах ЯМД. СУБД переводит этот запрос в последовательность файловых команд. Каждая файловая команда вызывает перекачку блока информации на клиента, далее на клиенте СУБД анализирует полученную информацию, и если в полученном блоке не содержится ответ на запрос, то принимается решение о перекачке следующего блока информации и т. д.
Перекачка информации с сервера на клиент производится до тех пор, пока не будет получен ответ на запрос клиента.
Недостатки:
высокий сетевой трафик, который связан с передачей по сети множества блоков и файлов, необходимых приложению;
узкий спектр операций манипулирования с данными, который определяется только файловыми командами;
отсутствие адекватных средств безопасности доступа к данным (защита только на уровне файловой системы).
205
204 :: 205 :: Содержание
Модели физической организации данных при бесфайловой организации
Файловая структура и система управления файлами являются прерогативой операционной среды, поэтому принципы обмена данными подчиняются законам операционной системы. По отношению к базам данных эти принципы могут быть далеки от оптимальности. СУБД подчиняется несколько иным принципам и стратегиям управления внешней памятью, чем те, которые поддерживают операционные среды для большинства пользовательских процессов или задач.
Это и послужило причиной того, что СУБД взяли на себя непосредственное управление внешней памятью. При этом пространство внешней памяти предоставляется СУБД полностью для управления, а операционная среда не получает непосредственного доступа к этому пространству.
Физическая организация современных баз данных является наиболее закрытой, она определяется как коммерческая тайна для большинства поставщиков коммерческих СУБД. И здесь не существует никаких стандартов, поэтому в общем
184
случае каждый поставщик создает свою уникальную структуру и пытается обосновать ее наилучшие качества по сравнению со своими конкурентами. Физическая организация является в настоящий момент наиболее динамичной частью СУБД. Стремительно расширяются возможность устройств внешней памяти, дешевеет оперативная память, увеличивается ее объем и поэтому изменяются сами принципы организации физических структур данных. И можно предположить, что и в дальнейшем эта часть современных СУБД будет постоянно меняться. Поэтому при рассмотрении моделей данных, используемых для физического хранения и обработки, мы коснемся только наиболее общих принципов и тенденций.
При распределении дискового пространства рассматриваются две схемы структуризации: физическая, которая определяет хранимые данные, и логическая, которая определяет некоторые логические структуры, связанные с концептуальной моделью данных (рис. 9.12).
Рис. 9.12. Классификация объектов при статичной организации физической модели данных
Определим некоторые понятия, используемые в указанной классификации.
Чанк (chank) - представляет собой часть диска, физическое пространство на диске, которое ассоциировано одному процессу (on line процессу обработки данных).
Чанком может быть назначено неструктурированное устройство, часть этого устройства, блочно-ориентированное устройство или просто файл UNIX.
Чанк характеризуется маршрутным именем, смещением (от физического начала устройства до начальной точки на устройстве, которая используется как чанк), размером, заданным в Кбайтах или М байтах.
При использовании блочных устройств и файлов величина смещения считается равной нулю.
Логические единицы образуются совокупностью экстентов, то есть таблица моделируется совокупностью экстентов.
Экстент - это непрерывная область дисковой памяти.
185
Для моделирования каждой таблицы используется 2 типа экстентов: первый и последующие.
Первый экстент задается при создании нового объекта типа таблица, его размер задается при создании. EXTENTSIZE - размер первого экстента, NEXT SIZE -размер каждого следующего экстента.
Минимальный размер экстента в каждой системе свой, но в большинстве случаев он равен 4 страницам, максимальный - 2 Г байтам.
Новый экстент создается после заполнения предыдущего и связывается с ним специальной ссылкой, которая располагается на последней странице экстента. В ряде систем экстенты называются сегментами, но фактически эти понятия эквиваленты.
При динамическом заполнении БД данными применяется специальный механизм адаптивного определения размера экстентов.
Внутри экстента идет учет свободных станиц.
Между экстентами, которые располагаются друг за другом без промежутков, производится своеобразная операция конкатенации, которая просто увеличивает размер первого экстента.
Механизм удвоения размера экстента: если число выделяемых экстентов для процесса растет в пропорции, кратной 16, то размер экстента удваивается каждые 16 экстентов.
Например, если размер текущего экстента 16 Кбайт, то после заполнения 16 экстентов данного размера размер следующего будет увеличен до 32 Кбайт.
Совокупность экстентов моделирует логическую единицу - таблицу-отношение (tblspace).
Экстенты состоят из четырех типов страниц: страницы данных, страницы индексов, битовые страницы и страницы blob - объектов.
Blob - это сокращение Binary Larg Object, и соответствует оно неструктурированным данным. В ранних СУБД такие данные относились к типу Memo. В современных СУБД к этому типу относятся неструктурированные большие текстовые данные, картинки, просто наборы машинных кодов. Для СУБД важно знать, что этот объект надо хранить целиком, что размеры этих объектов от записи к записи могут резко отличаться и этот размер в общем случае неограничен.
Основной единицей осуществления операций обмена (ввода - вывода) является страница данных. Все данные хранятся постранично. При табличном хранении данные на одной странице являются однородными, то есть станица может хранить только данные или только индексы.
Все страницы данных имеют одинаковую структуру, представленную на рис. 9.13.
Слот - это 4 - байтовое слово, 2 байта соответствуют смещению строки на странице и 2 байта - длина строки. Слоты характеризуют размещение строк данных на странице. На одной странице хранится не более 255 строк. В базе данных каждая строка имеет уникальный идентификатор в рамках всей базы данных, часто называемый RowID - номер строки, он имеет размер 4 байта и состоит из номера страницы и номера строки на странице. Под номер страницы отводится
186
3 байта, поэтому при такой идентификации возможна адресация к 16 777 215 страницам.
Рис. 9.13. Обобщенная структура страницы данных
При упорядочении строк на страницах не происходит физического перемещения строк, все манипуляции происходят со слотами.
При переполнении страниц создается специальный вид страниц, называемых страницами остатка. Строки, не уместившиеся на основной странице, связываются (линкуются) со своим продолжением на страницах остатка с помощью ссылок - указателей "вперед" (то есть на продолжение), которые содержат номер страницы и номер слота на странице.
Страницы индексов организованы в виде В-деревьев. Страницы blob предназначены для хранения слабоструктурированной информации, содержащей тексты большого объема, графическую информацию, двоичные коды.
Эти данные рассматриваются как потоки байтов произвольного размера, в страницах данных делаются ссылки на эти страницы.
Битовые страницы служат для трассировки других типов страниц. В зависимости от трассируемых страниц битовые страницы строятся по 2 - битовой или 4 - битовой схеме. 4 - битовые страницы служат для хранения сведений о столбцах типа Varchar, Byte, Text, для остальных типов данных используются 2 - битовые страницы.
Битовая структура трассирует 32 страницы. Каждая битовая структура представлена двумя 4 - байтными словами. Каждая i-я позиция описывает одну i-ю страницу. Сочетание разрядов в 1-х позициях двух слов обозначает состояние данной страницы: ее тип и занятость.
При обработке данных СУБД организует специальные структуры в оперативной памяти, называемые разделяемой памятью, и специальные структуры во внешней памяти, называемые журналами транзакций. Разделяемая память служит для кэширования данных при работе с внешней памятью с целью сокращения времени доступа, кроме того, разделяемая память служит для эффективной поддержки режимов одновременной параллельной работы пользователей с базой данных.
Журнал транзакций служит для управления корректным выполнением транзакций.
Однако тема параллельной обработки данных выходит за рамки данного раздела, и поэтому архитектура разделяемой памяти будет освещена в разделах, посвященных распределенной обработке данных.
Модели "клиент-сервер" в технологии баз данных
Вычислительная модель "клиент - сервер" исходно связана с парадигмой открытых систем, которая появилась в 90-х годах и быстро эволюционировала. Сам термин "клиент-сервер" исходно применялся к архитектуре программного обеспечения, которое описывало распределение процесса выполнения по принципу взаимодействия двух программных процессов, один из которых в этой модели назывался "клиентом", а другой - "сервером". Клиентский процесс запрашивал некоторые услуги, а серверный процесс обеспечивал их выполнение. При этом предполагалось, что один серверный процесс может обслужить множество клиентских процессов.
Ранее приложение (пользовательская программа) не разделялась на части, оно выполнялось некоторым монолитным блоком. Но возникла идея более рационального использования ресурсов сети. Действительно, при монолитном исполнении используются ресурсы только одного компьютера, а остальные компьютеры в сети рассматриваются как терминалы. Но теперь, в отличие от эпохи main-фреймов, все компьютеры в сети обладают собственными ресурсами, и разумно так распределить нагрузку на них, чтобы максимальным образом использовать их ресурсы.
И как в промышленности, здесь возникает древняя как мир идея распределения обязанностей, разделения труда. Конвейеры Форда сделали в свое время прорыв в автомобильной промышленности, показав наивысшую производительность труда именно из-за того, что весь процесс сборки был разбит на мелкие и максимально простые операции и каждый рабочий специализировался на выполнении только одной операции, но эту операцию он выполнял максимально быстро и качественно.
Конечно, в вычислительной технике нельзя было напрямую использовать технологию автомобильного или любого другого механического производства, но идею использовать было можно. Однако для воплощения идеи необходимо было разработать модель разбиения единого монолитного приложения на отдельные части и определить принципы взаимосвязи между этими частями.
Основной принцип технологии "клиент - сервер" применительно к технологии баз данных заключается в разделении функций стандартного интерактивного приложения на 5 групп, имеющих различную природу:
прикладные функции, определяющие основные алгоритмы
функции ввода и отображения данных (Presentation Logic);
прикладные функции, определяющие основные алгоритмы решения задач приложения (Business Logic);
функции обработки данных внутри приложения (Database Logic);
функции управления информационными ресурсами (Database Manager System);
служебные функции, играющие роль связок между функциями первых четырех групп.
201
Структура типового приложения, работающего с базой данных приведена на рис. 10.2.
Рис. 10.2. Структура типового интерактивного приложения, работающего с базой данных
Презентационная логика (Presentation Logic) как часть приложения определяется тем, что пользователь видит на своем экране, когда работает приложение. Сюда относятся все интерфейсные экранные формы, которые пользователь видит или заполняет в ходе работы приложения, к этой же части относится все то, что выводится пользователю на экран как результаты решения некоторых промежуточных задач либо как справочная информация. Поэтому основными задачами презентационной логики являются:
формирование экранных изображений;
чтение и запись в экранные формы информации;
управление экраном;
обработка движений мыши и нажатие клавиш клавиатуры.
Некоторые возможности для организации презентационной логики приложений предоставляет знако - ориентированный пользовательский интерфейс, задаваемый моделями CICS (Customer Control Information System ) и IMS/DC фирмы IBM и моделью TSO (Time Sharing Option) для централизованной main-фреймовой архитектуры. Модель GUI - графического пользовательского интерфейса, поддерживается в операционных средах Microsoft?s Windows, Windows NT, в OS/2 Presentation Manager, X-Windows и OSF/Motif.
Бизнес-логика, или логика собственно приложений (Business processing Logic), -это часть кода приложения, которая определяет собственно алгоритмы решения конкретных задач приложения. Обычно этот код пишется с использованием различных языков программирования, таких как С, C++, Cobol, SmallTalk, Visual-Basic.
Логика обработки данных (Data manipulation Logic) - это часть кода приложения, которая связана с обработкой данных внутри приложения.
Данными управляет собственно СУБД (DBMS). Для обеспечения доступа к данным используются язык запросов и средства манипулирования данными стандартного языка SQL.
202
Обычно операторы языка SQL встраиваются в языки 3-го или 4-го поколения (3GL, 4GL), которые используются для написания кода приложения.
Процессор управления данными (Database Manager System Processing) - это собственно СУБД, которая обеспечивает хранение и управление базами данных. В идеале функции СУБД должны быть скрыты от бизнес - логики приложения, однако для рассмотрения архитектуры приложения нам надо их выделить в отдельную часть приложения.
В централизованной архитектуре (Host-based processing) эти части приложения располагаются в единой среде и комбинируются внутри одной исполняемой программы.
В децентрализованной архитектуре эти задачи могут быть по-разному распределены между серверным и клиентским процессами. В зависимости от характера распределения можно выделить следующие модели распределений (см. рис. 10.3):
распределенная презентация (Distribution presentation, DP);
удаленная презентация (Remote Presentation, RP);
распределенная бизнес-логика (Remote business logic, RBL);
распределенное управление данными (Distributed data management, DDM);
удаленное управление данными (Remote data management, RDA).
Рис. 10.3. Распределение функций приложения в моделях "клиент - сервер"
203
Эта условная классификация показывает, как могут быть распределены отдельные задачи между серверным и клиентскими процессами. В этой классификации отсутствует реализация удаленной бизнес - логики. Действительно, считается, что она не может быть удалена сама по себе полностью. Считается, что она может быть распределена между разными процессами, которые в общем-то могут выполняться на разных платформах, но должны корректно кооперироваться (взаимодействовать) друг с другом.
Модели серверов баз данных
В период создания первых СУБД технология "клиент - сервер" только зарождалась. Поэтому изначально в архитектуре систем не было адекватного механизма организации взаимодействия процессов типа "клиент" и процессов типа "сервер". В современных же СУБД он является фактически основополагающим и от эффективности его реализации зависит эффективность работы системы в целом.
Рассмотрим эволюцию типов организации подобных механизмов. В основном этот механизм определяется структурой реализации серверных процессов, и часто он называется архитектурой сервера баз данных.
Первоначально, как мы уже отмечали, существовала модель, когда управление данными (функция сервера) и взаимодействие с пользователем были совмещены в одной программе. Это можно назвать нулевым этапом развития серверов БД.
Затем функции управления данными были выделены в самостоятельную группу - сервер, однако модель взаимодействия пользователя с сервером соответствовала парадигме "один - к - одному" (рис. 10.8), то есть сервер обслуживал запросы только одного пользователя (клиента), и для обслуживания нескольких клиентов нужно было запустить эквивалентное число серверов.
Выделение сервера в отдельную программу было революционным шагом, который позволил, в частности, поместить сервер на одну машину, а программный интерфейс с пользователем - на другую, осуществляя взаимодействие между ними по сети. Однако необходимость запуска большого числа серверов для обслуживания множества пользователей сильно ограничивала возможности такой системы.
Для обслуживания большого числа клиентов на сервере должно быть запущено большое количество одновременно работающих серверных процессов, а это резко повышало требования к ресурсам ЭВМ, на которой запускались все серверные процессы. Кроме того, каждый серверный процесс в этой модели запускался
210
как независимый; поэтому если один клиент сформировал запрос, который был только что выполнен другим серверным процессом для другого клиента, то запрос тем не менее выполнялся повторно.
В такой модели весьма сложно обеспечить взаимодействие серверных процессов. Эта модель самая простая, и исторически она появилась первой.
Рис. 10.8. Взаимодействие пользовательских и клиентских процессов в модели "один - к - одному"
Проблемы, возникающие в модели "один - к - одному", решаются в архитектуре "систем с выделенным сервером", который способен обрабатывать запросы от многих клиентов. Сервер единственный обладает монополией на управление данными и взаимодействует одновременно со многими клиентами (рис. 10.9). Логически каждый клиент связан с сервером отдельной нитью ("thread"), или потоком, по которому пересылаются запросы. Такая архитектура получила название многопотоковой односерверной ("multi-threaded").
Она позволяет значительно уменьшить нагрузку на операционную систему, возникающую при работе большого числа пользователей ("trashing").
Рис. 10.9. Многопотоковая односерверная архитектура
Кроме того, возможность взаимодействия с одним сервером многих клиентов позволяет в полной мере использовать разделяемые объекты (начиная с открытых файлов и кончая данными из системных каталогов), что значительно уменьшает потребности в памяти и общее число процессов операционной системы. Например, системой с архитектурой "один - к - одному" будет создано 100 копий процессов СУБД для 100 пользователей, тогда как системе с многопотоковой архитектурой для этого понадобится только один серверный процесс.
Однако такое решение имеет свои недостатки. Так как сервер может выполняться только на одном процессоре, возникает естественное ограничение на применение
211
СУБД для мультипроцессорных платформ. Если компьютер имеет, например, четыре процессора, то СУБД с одним сервером используют только один из них, не загружая оставшиеся три.
В некоторых системах эта проблема решается вводом промежуточного диспетчера. Подобная архитектура называется архитектурой виртуального сервера ("virtual server") (рис. 10.10).
В этой архитектуре клиенты подключаются не к реальному серверу, а к промежуточному звену, называемому диспетчером, который выполняет только функции диспетчеризации запросов к актуальным серверам. В этом случае нет ограничений на использование многопроцессорных платформ. Количество актуальных серверов может быть согласовано с количеством процессоров в системе.
Однако и эта архитектура не лишена недостатков, потому что здесь в систему добавляется новый слой, который размещается между клиентом и сервером, что увеличивает трату ресурсов на поддержку баланса загрузки актуальных серверов ("load balancing") и ограничивает возможности управления взаимодействием "клиент - сервер". Во-первых, становится невозможным направить запрос от конкретного клиента конкретному серверу, во-вторых, серверы становятся равноправными - нет возможности устанавливать приоритеты для обслуживания запросов.
Рис. 10.10. Архитектура с виртуальным сервером
Подобная организация взаимодействия клиент-сервер может рассматриваться как аналог банка, где имеется несколько окон кассиров, и специальный банковский служащий - администратор зала (диспетчер) направляет каждого вновь пришедшего посетителя (клиента) к свободному кассиру (актуальному серверу). Система работает нормально, пока все посетители равноправны (имеют равные приоритеты), однако стоит лишь появиться посетителям с высшим приоритетом, которые должны обслуживаться в специальном окне, как возникают проблемы. Учет приоритета клиентов особенно важен в системах оперативной обработки транзакций, однако именно эту возможность не может предоставить архитектура систем с диспетчеризацией.
Современное решение проблемы СУБД для мультипроцессорных платформ заключается в возможности запуска нескольких серверов базы данных, в том числе и на различных процессорах. При этом каждый из серверов должен быть многопотоковым. Если эти два условия выполнены, то есть основания говорить о многопотоковой архитектуре с несколькими серверами, представленной на рис. 10.11.
212
Она также может быть названа многонитевой мультисерверной архитектурой. Эта архитектура связана с вопросами распараллеливания выполнения одного пользовательского запроса несколькими серверными процессами.
Рис. 10.11. Многопотоковая мультисерверная архитектура
Существует несколько возможностей распараллеливания выполнения запроса. В этом случае пользовательский запрос разбивается на ряд подзапросов, которые могут выполняться параллельно, а результаты их выполнения потом объединяются в общий результат выполнения запроса. Тогда для обеспечения оперативности выполнения запросов их подзапросы могут быть направлены отдельным серверным процессам, а потом полученные результаты объединены в общий результат (см. рис 10.12). В данном случае серверные процессы не являются независимыми процессами, такими, как рассматривались ранее. Эти серверные процессы принято называть нитями (treads), и управление нитями множества запросов пользователей требует дополнительных расходов от СУБД, однако при оперативной обработке информации в хранилищах данных такой подход наиболее перспективен.
Рис. 10.12. Многонитевая мультисерверная архитектура
Моделирование отношений "один - ко - многим" на файловых структурах
Отношение иерархии является типичным для баз данных, поэтому моделирование иерархических связей является типичным для физических моделей баз данных.
Для моделирования отношений 1:М (один - ко - многим) и М:М (многие - ко - многим) на файловых структурах используется принцип организации цепочек записей внутри файла и ссылки на номера записей для нескольких взаимосвязанных файлов.
Моделирование отношения 1:М с использованием однонаправленных указателей
В этом случае связываются два файла, например F1 и F2, причем предполагается, что одна запись в файле F1 может быть связана с несколькими записями в файле F2. Условно это можно представить в виде, изображенном на рис. 9.10.
Рис. 9.10. Иерархическая связь между файлами
При этом файл F1 в этом комплексе условно называется "Основным", а файл F2 - "зависимым" или "подчиненным". Структура основного файла может быть условно представлена в виде трех областей.
"Основной файл" F1.
| Ключ | Запись | Ссылка - указатель на первую запись в "Подчиненном" файле, с которой начинается цепочка записей файла F2, связанных с данной записью файла F1 |
178
В подчиненном файле также к каждой записи добавляется специальный указатель, в нем хранится номер записи, которая является следующей в цепочке записей "подчиненного" файла, связанной с одной записью "основного" файла.
Таким образом, каждая запись "подчиненного файла" делится на две области: область указателя и область, содержащую собственно запись.
Структура записи "подчиненного" файла.
| Указатель на следующую запись в цепочке | Содержимое записи |
В качестве примера рассмотрим связь между преподавателями и занятиями, которые эти преподаватели проводят. В файле F1 приведен список преподавателей, а в файле F2 - список занятий, которые они ведут.
| F1 | ||
| Номер записи | Ключ и остальная запись | Указатель |
| 1 | Иванов И. Н. ... | 1 |
| 2 | Петров А. А. | 3 |
| 3 | Сидоров П. А. | 2 |
| 4 | Яковлев В. В. |
| F2 | ||
| Номер записи | Указатель на следующую запись в цепочке | Содержимое записи |
| 1 | 4 | 4306 Вычислительные сети |
| 2 | - | 4307 Контроль и диагностика |
| 3 | 6 | 4308 Вычислительные сети |
| 4 | 5 | 84305 Моделирование |
| 5 | - | 4309 Вычислительные сети |
| 6 | - | 84405 Техническая диагностика |
| 7 | - |
В этом случае содержимое двух взаимосвязанных файлов F1 и F2 может быть расшифровано следующим образом: первая запись в файле F1 связана с цепочкой записей файла F2, которая начинается с записи номер 1, следующая запись номер 4 и последняя запись в цепочке - запись номер 5. Последняя - потому что пятая запись не имеет ссылки на следующую запись в цепочке. Аналогично можно расшифровать и остальные связи. Если мы проведем интерпретацию данных связей на уровне предметной области, то можно утверждать, что преподаватель Иванов ведет предмет " Вычислительные сети" в группе 4306, "Моделирование" в группе 84305 и "Вычислительные сети" в группе 4309.
Аналогично могут быть расшифрованы и остальные взаимосвязанные записи.
179
Объединенные представления
Часто представления базируются на многотабличных запросах. Такое использование позволяет упростить разработку пользовательского интерфейса, сохранив при этом корректность схемы базы данных. Для примера снова обратимся к базе данных "Библиотека" и создадим представление, которое содержит список читателей-должников с указанием книг, которые у них на руках, и указанных в базе сроков сдачи этих книг. Такое представление может понадобиться для административного приложения, которое разрабатывается для директора библиотеки или его заместителя, они должны принимать административные меры для наказания нарушителей и возврата книг в библиотеку.
CREATE VIEW DEBTORS
ISEN.TITLE. NUM_READER.NAME.ADRES,HOME_PHON. WORK_PHON.DATA_OUT
AS
SELECT ISBN.TITLE.NUM_READER.NAME.ADRES.HOME_PHON. WORK_PHON.DATA_OUT
FROM BOOKS.EXEMPLAR,READERS
WHERE BOOKS.ISBN = EXEMPLAR.ISBN AND
EXEMPLAR.NUM_READER = READERS.NUM_READER AND
EXEMPLAR.PRESENT = FALSE AND
EXEMPLAR.DATA OUT < GetDate()
160
160 :: Содержание
Общие понятия и определения целостности
Поддержка целостности в реляционной модели данных в ее классическом понимании включает в себя 3 аспекта.
Во-первых, это поддержка структурной целостности, которая трактуется как то, что реляционная СУБД должна допускать работу только с однородными структурами данных типа "реляционное отношение". При этом понятие "реляционного отношения" должно удовлетворять всем ограничениям, накладываемым на него в классической теории реляционной БД (отсутствие дубликатов кортежей, соответственно обязательное наличие первичного ключа, отсутствие понятия упорядоченности кортежей).
В дополнение к структурной целостности необходимо рассмотреть проблему неопределенных Null значений. Как уже указывалось раньше, неопределенное значение интерпретируется в реляционной модели как значение, неизвестное на данный момент времени. Это значение при появлении дополнительной информации в любой момент времени может быть заменено на некоторое конкретное значение. При сравнении неопределенных значений не действуют стандартные правила сравнения: одно неопределенное значение никогда не считается равным другому неопределенному значению. Для выявления равенства значения некоторого атрибута неопределенному применяют специальные стандартные предикаты:
15 NULL и IS NOT NULL
Если в данном кортеже (в данной строке) указанный атрибут имеет неопределенное значение, то предикат IS NULL принимает значение TRUE (Истина), а предикат IS NOT NULL - FALSE (Ложь), в противном случае предикат IS NULL принимает значение FALSE, а предикат IS NOT NULL принимает значение TRUE.
Ведение Null значений вызвало необходимость модификации классической двузначной логики и превращения ее в трехзначную. Все логические операции, производимые с неопределенными значениями, подчиняются этой логике в соответствии с заданной таблицей истинности.
Таблица 8.1. Таблица истинности для логических операций с неопределенными значениями
| А | В | Not A | A&B | A ? В |
| TRUE | TRUE | FA | TRUE | TRUE |
| TRUE | FALSE | FALSE | FALSE | TRUE |
В стандарте SQL2 появилась возможность
136
| А | В | Not A | A&B | A ? В |
| TRUE | Null | FALSE | Null | TRUE |
| FALSE | TRUE | TRUE | FALSE | TRUE |
| FALSE | FALSE | TRUE | FALSE | FALSE |
| FALSE | Null | TRUE | FALSE | Null |
| Null | TRUE | Null | Null | TRUE |
| Null | FALSE | Null | FALSE | Null |
| Null | Null | Null | Null | Null |
< Логическое выражение> IS {TRUE | FALSE | UNKNOWN}
Во-вторых, это поддержка языковой целостности, которая состоит в том, что реляционная СУБД должна обеспечивать языки описания и манипулирования данными не ниже стандарта SQL. He должны быть доступны иные низкоуровневые средства манипулирования данными, не соответствующие стандарту.
Именно поэтому доступ к информации, хранимой в базе данных, и любые изменения этой информации могут быть выполнены только с использованием операторов языка SQL.
В-третьих, это поддержка ссылочной целостности (Declarative Referential Integrity, DRI), означает обеспечение одного из заданных принципов взаимосвязи между экземплярами кортежей взаимосвязанных отношений:
кортежи подчиненного отношения уничтожаются при удалении кортежа основного отношения, связанного с ними.
кортежи основного отношения модифицируются при удалении кортежа основного отношения, связанного с ними, при этом на месте ключа родительского отношения ставится неопределенное Null значение.
Ссылочная целостность обеспечивает поддержку непротиворечивого состояния БД в процессе модификации данных при выполнении операций добавления или удаления.
Кроме указанных ограничений целостности, которые в общем виде не определяют семантику БД, вводится понятие семантической поддержки целостности.
Структурная, языковая и ссылочная целостность определяют правила работы СУБД с реляционными структурами данных.
Требования поддержки этих трех видов целостности говорят о том, что каждая СУБД должна уметь это делать, а разработчики должны это учитывать при построении баз данных с использованием реляционной модели. И эти требования поддержки целостности достаточно абстрактны, они определяют допустимую форму представления и обработки информации в реляционных базах данных. Но с другой стороны, эти аспекты
137
никак не касаются содержания базы данных. Для определения некоторых ограничений, которые связаны с содержанием базы данных, требуются другие методы. Именно эти методы и сведены в поддержку семантической целостности.
Давайте рассмотрим конкретный пример. То, что мы можем построить схему базы данных или ее концептуальную модель только из совокупности нормализованных таблиц, определяет структурную целостность. И мы построили нашу схему библиотеки из пяти взаимосвязанных отношений. Но мы не можем с помощью перечисленных трех методов поддержки целостности обеспечить ряд правил, которые определены в нашей предметной области и должны в ней соблюдаться. К таким правилам могут быть отнесены следующие:
В библиотеке должны быть записаны читатели не моложе 17 лет.
В библиотеке присутствуют книги, изданные начиная с 1960 по текущий год.
Каждый читатель может держать на руках не более 5 книг.
Каждый читатель при регистрации в библиотеке должен дать телефон для связи: он может быть рабочим или домашним.
Принципы семантической поддержки целостности как раз и позволяют обеспечить автоматическое выполнение тех условий, которые перечислены ранее.
Семантическая поддержка может быть обеспечена двумя путями: декларативным и процедурным путем. Декларативный путь связан с наличием механизмов в рамках СУБД, обеспечивающих проверку и выполнение ряда декларативно заданных правил-ограничений, называемых чаще всего "бизнес-правилами" (Business Rules) или декларативными ограничениями целостности.
Выделяются следующие виды декларативных ограничений целостности:
Ограничения целостности атрибута: значение по умолчанию, задание обязательности или необязательности значений (Null), задание условий на значения атрибутов.
Задание значения по умолчанию означает, что каждый раз при вводе новой строки в отношение, при отсутствии данных в указанном столбце этому атрибуту присваивается именно значение по умолчанию. Например, при вводе новых книг разумно в качестве значения по умолчанию для года издания задать, значение текущего года. Например, для MS Access 97 это выражение будет иметь вид:
YEAR(NOW( ))
Здесь NOW() - функция, возвращающая значение текущей даты, YEAR(data) -функция, возвращающая значение года указанной в качестве параметра даты.
В качестве условия на значение для года издания надо задать выражение, которое будет истинным только тогда, когда год издания будет лежать в пределах от 1960 года до текущего года. В конкретных СУБД это значение будет формироваться с использованием специальных встроенных функций СУБД.
Для MS Access 97 это выражение будет выглядеть следующим образом:
Between 1960 AND YEAR(NOW( ))
138
В СУБД MS SQL Server 7.0 значение по умолчанию записывается в качестве "бизнес-правила". В этом случае будет использоваться выражение, в котором явным образом должно быть указано имя соответствующего столбца, например:
YEAR_PUBL >= 1960 AND YEAR_PUBL
Здесь GETDATE() - функция MS SQL Server7.0, возвращающая значение текущей даты, YEAR_PUBL - имя столбца, соответствующего году издания.
Ограничения целостности, задаваемые на уровне доменов, при поддержке доменной структуры. Эти ограничения удобны, если в базе данных присутствуют несколько столбцов разных отношений, которые принимают значения из одного и того же множества допустимых значений. Некоторые СУБД поддерживают подобную доменную структуру, то есть разрешают определять отдельно домены, задавать тип данных для каждого домена и задавать соответственно ограничения в виде бизнес-правил для доменов. А для атрибутов задается не примитивный первичный тип данных, а их принадлежность тому или другому домену. Иногда доменная структура выражена неявно и в ряде СУБД применяется специальная терминология для этого.
Так, например, в MS SQL Server 7. 0 вместо понятия домена вводится понятие типа данных, определенных пользователем, но смысл этого типа данных фактически эквивалентен смыслу домена. В этом случае действительно удобно задать ограничение на значение прямо на уровне домена, тогда оно автоматически будет выполняться для всех атрибутов, которые принимают значения из этого домена. А почему удобно задать это ограничение на уровне домена? А если мы зададим это ограничение для каждого атрибута, входящего в домен, разве наша система будет работать неправильно? Нет, конечно, она будет работать правильно, но представьте себе, что у вас в организации изменились правила работы, которые выражены в виде декларативных ограничений на значения. В нашем случае, например, мы будем комплектовать библиотеку более новыми книгами и теперь будем принимать в библиотеку книги, изданные не позднее 1980 года. А если это ограничение у нас задано не на один столбец, то нам надо просматривать все отношения и во всех отношениях менять старое правило на новое. Не легче ли заменить его один раз в домене, а все атрибуты, которые принимают значения из этого домена, будут автоматически работать по новому правилу.
Да, это действительно легче, тем более что в процессе работы схема базы данных разрастается и начинает содержать более сотни отношений, и задача нетривиальная - найти все отношения, в которых ранее установлено это ограничение и исправить его.
Одним из основных правил при разработке проекта базы данных, как мы уже упоминали раньше, является минимизация избыточности, а это означает, что если возможно информацию о чем-то, в том числе и об ограничениях, хранить в одном месте, то это надо делать обязательно.
Ограничения целостности, задаваемые на уровне отношения. Некоторые семантические правила невозможно преобразовать в выражения, которые будут применимы только к одному столбцу. В нашем примере с библиотекой
139
мы не сможем выразить требование наличия по крайней мере одного телефонного номера для быстрой связи с читателем.
У нас под телефоны отведены два столбца, это в некотором роде искусственно, но специально так сделано, чтобы показать вам другой тип ограничений. Каждый из атрибутов является в общем случае необязательным и может принимать неопределенные значения: не обязательно должен быть задан как рабочий, так и домашний телефон. Мы хотим потребовать, чтобы из двух по крайней мере один телефон был бы задан обязательно. Попробуем сформулировать это в терминологии неопределенных значений баз данных. Домашний телефон должен быть задан (NOT NULL) или рабочий телефон должен быть задан (NOT NULL). Для MS Access97 или для MS SQL Server97 соответствующее выражение будет выглядеть следующим образом:
HOME_PHON IS NOT NULL OR WORK_PHON IS NOT NULL
Ограничения целостности, задаваемые на уровне связи между отношениями: задание обязательности связи, принципов каскадного удаления и каскадного изменения данных, задание поддержки ограничений по мощности связи. Эти виды ограничений могут быть выражены заданием обязательности или необязательности значений внешних ключей во взаимосвязанных отношениях.
Декларативные ограничения целостности относятся к ограничениям, которые являются немедленно проверяемыми. Есть ограничения целостности, которые являются откладываемыми. Эти ограничения целостности поддерживаются механизмом транзакций и триггеров. Мы их рассмотрим в следующих главах.
Ограничение стандарта SQL1 на обновление представлений
Несмотря на то, что для пользователей представления выглядят как реальные отношения, существует ряд ограничений на операции модификации данных, связанные с представлениями.
СУБД может обновлять данные через представления только в том случае, если она может однозначно сопоставить каждой строке представления строку из реальной таблицы базы данных, а для каждого обновляемого столбца представления однозначно определить исходный столбец исходной таблицы базы данных, Далеко не для всех запросов это возможно сделать. Действительно, запросы с группировкой, сложные запросы с подзапросами возвращают результат, который СУБД не сможет однозначно интерпретировать в терминах реальных таблиц БД.
Согласно стандарту, представление можно обновлять только в том случае, когда его запрос соответствует следующим требованиям:
В запросе должен отсутствовать предикат DISTINCT, то есть повторяющиеся строки не должны исключаться из таблицы результатов запроса.
В предложении FROM должна быть задана только одна таблица, которую можно обновлять, то есть у представления должна быть только одна исходная таблица (это горизонтальное или вертикальное представление), а пользователь должен иметь соответствующие права доступа к ней. Если таблица сама является представлением, то она тоже должна удовлетворять данным условиям.
Каждое имя в списке возвращаемых столбцов должно быть ссылкой на простой столбец: в списке не должны содержаться выражения, вычисляемые столбцы или агрегатные функции.
В предложении WHERE не должен стоять вложенный запрос; в нем могут присутствовать только простые условия поиска.
В запросе не должно присутствовать выражение группировки GROUP BY или HAVING.
Однако в ряде коммерческих СУБД эти требования смягчены и операции модификации разрешены для более широкого класса представлений.
161
161 :: Содержание
Операции над отношениями. Реляционная алгебра
Напомним, что алгеброй называется множество объектов с заданной на нем совокупностью операций, замкнутых относительно этого множества, называемого основным множеством.
Основным множеством в реляционной алгебре является множество отношении. Всего Э. Ф. Коддом было предложено 8 операций. В общем это множество избыточное, так как одни операции могут быть представлены через другие, однако множество операций выбрано из соображений максимального удобства при реализации произвольных запросов к БД. Все множество операций можно разделить на две группы: теоретико-множественные операции и специальные операции. В первую группу входят 4 операции. Три первые теоретико-множественные операции являются бинарными, то есть в них участвуют два отношения и они требуют эквивалентных схем исходных отношений.
Оператор чтения очередной строки курсора
После открытия указатель текущей строки установлен перед первой строкой курсора. Стандартно оператор FETCH перемещает указатель текущей строки на следующую строку и присваивает базовым переменным значение столбцов, соответствующее текущей строке.
Простой оператор FETCH имеет следующий синтаксис:
FETCH INTO
Оператор извлечения очередной строки из курсора будет выглядеть следующим образом:
FETCH Debtor_reader_cursor into @FIRST_NAME, @LAST_NAME. @ADRES. @HOME_PHON.
@WORK_PHON, @TITLE
255
Расширенный оператор FETCH имеет следующий синтаксис:
FETCH
[NEXT | PRIOR | FIRST | LAST
| ABSOLUTE {n | }
|RELATIVE{n|}]
FROM
INTO
Здесь параметр NEXT задает выбор следующей строки после текущей из базового набора строк, связанного с курсором. Параметр PRIOR задает перемещение на предыдущую строку по отношению к текущей. Параметр FIRST задает перемещение на первую строку набора, а параметр LAST задает перемещение на последнюю строку набора.
Кроме того, в расширенном операторе перемещения допустимо переместиться сразу на заданную строку, при этом допустима как абсолютная адресация, заданием параметра ABSOLUTE, так и относительная адресация, заданием параметра RELATIVE. При относительной адресации положительное число сдвигает указатель вниз от текущей записи, отрицательное число сдвигает вверх от текущей записи.
Однако для применения расширенного оператора FETCH в соответствии со стандартом SQL2 описание курсора обязательно должно содержать ключевое слово SCROLL. Иногда такие курсоры называют в литературе прокручиваемыми курсорами. В стандарт эти курсоры вошли сравнительно недавно, поэтому в коммерческих СУБД очень часто операторы по работе с подобными курсорами серьезно отличаются. Правда, реалии сегодняшнего дня заставляют поставщиков коммерческих СУБД более строго соблюдать последний стандарт SQL. В технической документации можно встретить две версии синтаксиса оператора FETCH: одну, которая соответствует стандарту, и другую, которая расширяет стандарт дополнительными возможностями, предоставляемыми только данной СУБД для работы с курсором.
Если вы предполагаете, что ваша БД может быть перенесена на другую платформу, а это надо всегда предусматривать, то лучше пользоваться стандартными возможностями. В этом случае ваше приложение будет более платформенно - независимым и легче будет его перенести на другую СУБД.
256
255 :: 256 :: Содержание
Оператор определения курсора
Стандарт определяет следующий синтаксис оператора определения курсора:
DECLARE CURSOR FOR
::=
Имя курсора - это допустимый идентификатор в базовом языке программирования.
В объявлении курсора могут быть использованы базовые переменные. Однако необходимо помнить, что на момент выполнения оператора OPEN значения всех базовых переменных, используемых в качестве входных переменных, связанных с условиями фильтрации значений в базовом запросе, должны быть уже заданы.
Определим курсор, который содержит список всех должников нашей библиотеки. Должниками назовем читателей, которые имеют на руках хотя бы одну книгу, срок сдачи которой уже прошел.
DECLARE Debtor_reader_cursor CURSOR FOR
SELECT READERS. FIRST_NAME. READERS.LAST_NAME. READERS.ADRES,
READERS HOME_PHON. READERS WORK_PHON, BOOKS.TITLE
FROM READERS,BOOKS,EXEMPLAR
WHERE READERS.READER_ID = EXEMPLAR.READER_ID AND
BOOKS.ISBN = EXEMPLARE.ISBN AND
EXEMPLAR.DATA_OUT > Getdate( )
ORDER BY READERS.FIRST_NAME
При определении курсора мы снова использовали функцию Transact SQL GetdateO, которая возвращает значение текущей даты. Таким образом, определенный курсор будет создавать набор строк, содержащих перечень должников, с указанием названий книг, которые они не вернули вовремя в библиотеку.
В соответствии со стандартом SQL2 Transact SQL содержит расширенное определение курсора
DECLARE [INSENSITIVE] [SCROLL] CURSOR
FOR
[FOR {READ ONLY | UPDATE [OF [....n]]}]
253
Параметр INSENSITIVE (нечувствительный) определяет режим создания набора строк, соответствующего определяемому курсору, при котором все изменения в исходных таблицах, произведенные после открытия курсора другими пользователями, не видны в нем. Такой набор данных нечувствителен ко всем изменениям, которые могут проводиться другими пользователями в исходных таблицах, этот тип курсора соответствует некоторому мгновенному слепку с БД.
СУБД более быстро и экономно может обрабатывать такой курсор, поэтому если для вас действительно важно рассмотреть и обработать состояние БД на некоторый конкретный момент времени, то имеет смысл создать "нечувствительный курсор".
Ключевое слово SCROLL определяет, что допустимы любые режимы перемещения по курсору (FIRST. LAST, PRIOR. NEXT, RELATIVE, ABSOLUTE) в операторе FETCH.
Если не указано ключевое слово SCROLL, то считается доступной только стандартное перемещение вперед: спецификация NEXT в операторе FETCH.
Если указана спецификация READ ONLY (только для чтения), то изменения и обновления исходных таблиц не будут выполняться с использованием данного курсора. Курсор с данной спецификацией может быть самым быстрым в обработке, однако если вы не укажите специально спецификацию READ ONLY, то СУБД будет считать, что вы допускаете операции модификации с базовыми таблицами, и в этом случае для обеспечения целостности БД СУБД будет гораздо медленнее обрабатывать ваши операции с курсором.
При использовании параметра UPDATE [OF [,...]] мы задаем перечень столбцов, в которых допустимы изменения в процессе нашей работы с курсором. Такое ограничение упростит и ускорит работу СУБД. Если этот параметр не указан, то предполагается, что допустимы изменения всех столбцов курсора.
Вернемся к нашему примеру. Если мы преследуем цель мгновенного слепка БД, дающего сведения о должниках, то применим все параметры, позволяющие ускорить работу с нашим курсором. Тогда оператор описания курсора будет выглядеть следующим образом:
DECLARE Debtor_reader_cursor INSENSITIVE CURSOR
FOR
SELECT READERS.FIRSTJAME, READERS. LAST JAME, READERS.ADRES,
READERS.HOME_PHON, READERS.WORK_PHON. BOOKS.TITLE
FROM READERS,BOOKS.EXEMPLAR
WHERE READERS.READER_ID = EXEMPLAR.READER_ID AND
BOOKS.ISBN = EXEMPLARE.ISBN AND
EXEMPLAR.DATA_OUT > Getdate( )
ORDER BY READERS. FIRST_NAME
FOR READ ONLY
При описании курсора нет ограничений на вид оператора SELECT, который используется для создания базового набора строк, связанного с курсором. В
254
операторе SELECT могут использоваться группировки и встроенные подзапросы и вычисляемые поля.
Оператор открытия курсора
Оператор открытия курсора имеет следующий синтаксис:
OPEN [USING ]
Именно оператор открытия курсора инициирует выполнение базового запроса, соответствующего описанию курсора, заданному в операторе DECLARE ... CURSOR. При выполнении оператора OPEN СУБД производит семантическую проверку курсора, то есть выполняет этапы со 2 по 5 в алгоритме выполнения запросов (рис. 12.1), поэтому именно здесь СУБД возвращает коды ошибок прикладной программе, сообщающие ей о результатах выполнения базового запроса. Ошибки могут возникнуть в результате неправильного задания имен полей или имен исходных таблиц или при попытке извлечь данные из таблиц, к которым данный пользователь не имеет доступа.
По стандарту СУБД возвращает код завершения операции в специальной системной переменной SQLCODE. В прикладной программе пользователь может анализировать эту переменную, что необходимо делать после выполнения каждого оператора SQL. При неудачном выполнении операции открытия курсора СУБД возвращает отрицательное значение SQLCODE.
В случае удачного завершения выполнения оператора открытия курсора набор данных, сформированный в результате базового запроса, остается доступным пользователю до момента выполнения оператора закрытия курсора.
Однако надо помнить, что СУБД автоматически закрывает все курсоры в случае завершения транзакции (COMMIT) или отката транзакции (ROLLBACK). После того как курсор закрыт его можно открыть снова, но при этом соответствующий запрос выполнится заново. Поэтому допустимо, что содержимое первого курсора будет не соответствовать его содержимому при повторном открытии, потому что за это время изменилось состояние БД.
255
255 :: Содержание
Оператор выбора SELECT
Язык запросов (Data Query Language) в SQL состоит из единственного оператора SELECT. Этот единственный оператор поиска реализует все операции реляционной алгебры. Как просто, всего один оператор. Однако писать запросы на языке SQL (грамотные запросы) сначала совсем не просто. Надо учиться, так же как надо учиться решать математические задачки или составлять алгоритмы для решения непростых комбинаторных задач. Один и тот же запрос может быть реализован несколькими способами, и, будучи все правильными, они, тем не менее, могут существенно отличаться по времени исполнения, и это особенно важно для больших баз данных.
Синтаксис оператора SELECT имеет следующий вид:
SELECT [ALL | DISTINCT] "список полей>|*)
FROM
[WHERE ]
[GROUP BY ]
[HAVING ]
[ORDER BY ]
Здесь ключевое слово ALL означает, что в результирующий набор строк включаются все строки, удовлетворяющие условиям запроса. Значит, в результирующий набор могут попасть одинаковые строки. И это нарушение принципов теории отношений (в отличие от реляционной алгебры, где по умолчанию предполагается отсутствие дубликатов в каждом результирующем отношении). Ключевое слово DISTINCT означает, что в результирующий набор включаются только различные строки, то есть дубликаты строк результата не включаются в набор.
Символ *. (звездочка) означает, что в результирующий набор включаются все столбцы из исходных таблиц запроса.
В разделе FROM задается перечень исходных отношений (таблиц) запроса.
74
В разделе WHERE задаются условия отбора строк результата или условия соединения кортежей исходных таблиц, подобно операции условного соединения в реляционной алгебре.
В разделе GROUP BY задается список полей группировки.
В разделе HAVING задаются предикаты-условия, накладываемые на каждую группу.
В части ORDER BY задается список полей упорядочения результата, то есть список полей, который определяет порядок сортировки в результирующем отношении. Например, если первым полем списка будет указана Фамилия, а вторым Номер группы, то в результирующем отношении сначала будут собраны в алфавитном порядке студенты, и если найдутся однофамильцы, то они будут расположены в порядке возрастания номеров групп.
В выражении условий раздела WHERE могут быть использованы следующие предикаты:
Предикаты сравнения { =, <>. >,=,
Предикат Between A and В - принимает значения между А и В. Предикат истинен, когда сравниваемое значение попадает в заданный диапазон, включая границы диапазона. Одновременно в стандарте задан и противоположный предикат Not Between A and В, который истинен тогда, когда сравниваемое значение не попадает в заданный интервал, включая его границы.
Предикат вхождения в множество IN (множество) истинен тогда, когда сравниваемое значение входит в множество заданных значений. При этом множество значений может быть задано простым перечислением или встроенным подзапросом. Одновременно существует противоположный предикат NOT IN (множество), который истинен тогда, когда сравниваемое значение не входит в заданное множество.
Предикаты сравнения с образцом LIKE и NOT LIKE. Предикат LIKE требует задания шаблона, с которым сравнивается заданное значение, предикат истинен, если сравниваемое значение соответствует шаблону, и ложен в противном случае. Предикат NOT LIKE имеет противоположный смысл.
По стандарту в шаблон могут быть включены специальные символы:
символ подчеркивания (_) - для обозначения любого одиночного символа;
символ процента (%) - для обозначения любой произвольной последовательности символов;
остальные символы, заданные в шаблоне, обозначают самих себя.
Предикат сравнения с неопределенным значением IS NULL. Понятие неопределенного значения было внесено в концепции баз данных позднее. Неопределенное значение интерпретируется в реляционной модели как значение, неизвестное на данный момент времени. Это значение при появлении дополнительной информации в любой момент времени может быть заменено на некоторое конкретное значение. При сравнении неопределенных значений не действуют стандартные правила сравнения: одно неопределенное значение никогда не считается равным другому неопределенному значению. Для выявления равенства значения некоторого атрибута неопределенному применяют специальные стандартные предикаты:
15 NULL и IS NOT NULL
75
Если в данном кортеже ( в данной строке) указанный атрибут имеет неопределенное значение, то предикат IS NULL принимает значение "Истина" (TRUE), а предикат IS NOT NULL - "Ложь" (FALSE), в противном случае предикат IS NULL принимает значение "Ложь", а предикат IS NOT NULL принимает значение "Истина".
Введение Null-значений вызвало необходимость модификации классической двузначной логики и превращения ее в трехзначную. Все логические операции, производимые с неопределенными значениями, подчиняются этой логике в соответствии с заданной таблицей истинности:
| А | В | Not A | А ? В | A ? B |
| TRUE | TRUE | FALSE | TRUE | TRUE |
| TRUE | FALSE | FALSE | FALSE | TRUE |
| TRUE | Null | FALSE | Null | TRUE |
| FALSE | TRUE | TRUE | FALSE | TRUE |
| FALSE | FALSE | TRUE | FALSE | FALSE |
| FALSE | Null | TRUE | FALSE | Null |
| Null | TRUE | Null | Null | TRUE |
| Null | FALSE | Null | FALSE | Null |
| Null | Null | Null | Null | Null |
В условиях поиска могут быть использованы все рассмотренные ранее предикаты.
Отложив на время знакомство с группировкой, рассмотрим детально первые три строки оператора SELECT:
SELECT - ключевое слово, которое сообщает СУБД, что эта команда - запрос. Все запросы начинаются этим словом с последующим пробелом. За ним может следовать способ выборки - с удалением дубликатов (DISTINCT) или без удаления (ALL, подразумевается по умолчанию). Затем следует список перечисленных через запятую столбцов, которые выбираются запросом из таблиц, или символ ?*? (звездочка) для выбора всей строки. Любые столбцы, не перечисленные здесь, не будут включены в результирующее отношение, соответствующее выполнению команды. Это, конечно, не значит, что они будут удалены или их информация будет стерта из таблиц, потому что запрос не воздействует на информацию в таблицах - он только показывает данные.
76
FROM - ключевое слово, подобно SELECT, которое должно быть представлено в каждом запросе. Оно сопровождается пробелом и затем именами таблиц, используемых в качестве источника информации. В случае если указано более одного имени таблицы, неявно подразумевается, что над перечисленными таблицами осуществляется операция декартова произведения. Таблицам можно присвоить имена-псевдонимы, что бывает полезно для осуществления операции соединения таблицы с самой собою или для доступа из вложенного подзапроса к текущей записи внешнего запроса (вложенные подзапросы здесь не рассматриваются).
Все последующие разделы оператора SELECT являются необязательными.
Самый простой запрос SELECT без необязательных частей соответствует просто декартову произведению. Например, выражение
SELECT *
FROM R1. R2
соответствует декартову произведению таблиц R1 и R2.
Выражение
SELECT R1.A, R2.B
FROM R1, R2
соответствует проекции декартова произведения двух таблиц на два столбца А из таблицы R1 и В из таблицы R2, при этом дубликаты всех строк сохранены, в отличие от операции проектирования в реляционной алгебре, где при проектировании по умолчанию все дубликаты кортежей уничтожаются.
WHERE - ключевое слово, за которым следует предикат - условие, налагаемое на запись в таблице, которому она должна удовлетворять, чтобы попасть в выборку, аналогично операции селекции в реляционной алгебре.
Рассмотрим базу данных, которая моделирует сдачу сессии в некотором учебном заведении. Пусть она состоит из трех отношений R1, R2, R3. Будем считать, что они представлены таблицами Rl, R2 и R3 соответственно.
R1 = (ФИО, Дисциплина, Оценка); R2 = (ФИО, Группа);R3 = (Группы, Дисциплина )
| R1 | ||
| ФИО | Дисциплина | Оценка |
| Петров Ф. И. | Базы данных | 5 |
| Сидоров К. А. | Базы данных | 4 |
| Миронов А. В. | Базы данных | 2 |
| Степанова К. Е. | Базы данных | 2 |
| Крылова Т. С. | Базы данных | 5 |
| Сидоров К. А. | Теория информации | 4 |
| Степанова К. Е. | Теория информации | 2 |
| Крылова Т. С. | Теория информации | 5 |
77
| R1 | ||
| ФИО | Дисциплина | Оценка |
| Миронов А. В. | Теория информации | Null |
| Владимиров В. А. | Базы данных | 5 |
| Трофимов П. А. | Сети и телекоммуникации | 4 |
| Иванова Е. А. | Сети и телекоммуникации | 5 |
| Уткина Н. В. | Сети и телекоммуникации | 5 |
| Владимиров В. А. | Английский язык | 4 |
| Трофимов П. А. | Английский язык | 5 |
| Иванова Е. А. | Английский язык | 3 |
| Петров Ф. И. | Английский язык | 5 |
| R2 | |
| ФИО | Группа |
| Петров Ф. И. | 4906 |
| Сидоров К. А. | 4906 |
| Миронов А. В. | 4906 |
| Крылова Т. С. | 4906 |
| Владимиров В. А. | 4906 |
| Трофимов П. А. | 4807 |
| Иванова Е. А. | 4807 |
| Уткина Н. В. | 4807 |
| R3 | |
| Группа | Дисциплина |
| 4906 | Базы данных |
| 4906 | Теория информации |
| 4906 | Английский язык |
| 4807 | Английский язык |
| 4807 | Сети и телекоммуникации |
Вывести список всех групп (без повторений), где должны пройти экзамены.
SELECT DISTINCT Группы
FROM R3
78
Результат:
| Группа |
| 4906 |
| 4807 |
SELECT ФИО
FROM R1
WHERE Дисциплина = "Базы данных" AND Оценка = 5
Результат:
| ФИО |
| Петров Ф. И. |
| Крылова Т. С. |
SELECT ФИО.Дисциплина
FROM R2.R3
WHERE R2.Группа - R2.Группа:
Здесь часть WHERE задает условия соединения отношений R2 и R3, при отсутствии условий соединения в части WHERE результат будет эквивалентен расширенному декартову произведению, и в этом случае каждому студенту были бы приписаны все дисциплины из отношения R3, а не те, которые должна сдавать его группа.
Результат:
| ФИО | Дисциплина |
| Петров Ф. И. | Базы данных |
| Сидоров К. А. | Базы данных |
| Миронов А. В. | Базы данных |
| Степанова К. Е. | Базы данных |
| Крылова Т. С. | Базы данных |
| Владимиров В. А. | Базы данных |
| Петров Ф. И. | Теория информации |
| Сидоров К. А. | Теория информации |
| Миронов А. В. | Теория информации |
| Степанова К. Е. | Теория информации |
79
| ФИО | Дисциплина |
| Крылова Т. С. | Теория информации |
| Владимиров В. А. | Теория информации |
| Петров Ф. И. | Английский язык |
| Сидоров К. А. | Английский язык |
| Миронов А. В. | Английский язык |
| Степанова К. Е. | Английский язык |
| Крылова Т. С. | Английский язык |
| Владимиров В. А. | Английский язык |
| Трофимов П. А. | Сети и телекоммуникации |
| Иванова Е. А. | Сети и телекоммуникации |
| Уткина Н. В. | Сети и телекоммуникации |
| Трофимов П. А. | Английский язык |
| Иванова Е. А. | Английский язык |
| Уткина Н. В. | Английский язык |
SELECT DISTINCT Rl.ФИО
FROM Rl a. Rl b
WHERE a.ФИО = b.ФИО AND
a.Дисциплина <> b.Дисциплина AND
а.Оценка
Здесь мы использовали псевдонимы для именования отношения R1 а и b, так как для записи условий поиска нам необходимо работать сразу с двумя экземплярами данного отношения.
Результат:
| ФИО |
| Степанова К. Е. |
80
Наличие неопределенных (Null) значений повышает гибкость обработки информации, хранящейся в БД. В наших примерах мы можем предположить ситуацию, когда студент пришел на экзамен, но не сдавал его по некоторой причине, в этом случае оценка по некоторой дисциплине для данного студента имеет неопределенное значение. В данной ситуации можно поставить вопрос: "Найти студентов, пришедших на экзамен, но не сдававших его с указанием названия дисциплины".Оператор SELECT будет выглядеть следующим образом:
SELECT ФИО, Дисциплина
FROM R1
WHERE Оценка IS NULL
Результат:
| ФИО | Дисциплина |
| Миронов А. В. | Теория информации |
Оператор закрытия курсора
Оператор закрытия курсора имеет простой синтаксис, он выглядит следующим образом:
CLOSE
Оператор закрытия курсора закрывает временную таблицу, созданную оператором открытия курсора, и прекращает доступ прикладной программы к этому объекту. Единственным параметром оператора закрытия является имя курсора.
256
Оператор закрытия может быть выполнен в любой момент после оператора открытия курсора.
В некоторых коммерческих СУБД кроме оператора закрытия курсора используется еще оператор деактивации (уничтожения) курсора. Например, в MS SQL Server 7.0 наряду с оператором закрытия курсора используется оператор
DEALLOCATE
Здесь оператор закрытия курсора не уничтожает набор данных, связанный с курсором, он только закрывает к нему доступ и освобождает все блокировки, которые ранее были связаны с данным курсором.
При выполнении оператора DEALLOCATE SQL Server освобождает разделяемую память, используемую командой описания курсора DECLARE. После выполнения этой команды невозможно выполнение команды OPEN для данного курсора.
257
256 :: 257 :: Содержание
Операторы DDL в языке SQL с заданием ограничений целостности
Декларативные ограничения целостности задаются на уровне операторов создания таблиц. В стандарте SQL оператор создания таблиц имеет следующий синтаксис:
определение таблицы>::=CREATE TABLE
( [{.}...])
::=|
::=
[][...]
::=DEFAULT { | USER | NULL }
::=NOT NULL
[]|
|
CHECK ()
::= UNIQUE
140
: :=FOREIGN KEY
.:= REFERENCES
()
Давайте кратко прокомментируем оператор определения таблицы, синтаксис которого мы задали с помощью традиционной формы Бэкуса - Наура.
При описании таблицы задается имя таблицы, которое является идентификатором в базовом языке СУБД и должно соответствовать требованиям именования объектов в данном языке.
Кроме имени таблицы в операторе указывается список элементов таблицы, каждый из которых служит либо для определения столбца, либо для определения ограничения целостности определяемой таблицы. Требуется наличие хотя бы одного определения столбца. То есть таблицу, которая не имеет ни одного столбца, определить нельзя. Количество столбцов в одной таблице не ограничено, но в конкретных СУБД обычно бывают ограничения на количество атрибутов. Так, например, в MS SQL Server 6.5 максимальное количество столбцов в таблице было 250, но уже в MS SQL Server 7.0 оно увеличено до 1024.
Оператор CREATE TABLE определяет так называемую базовую таблицу, то есть реальное хранилище данных.
Как видно, кроме обязательной части, в которой задается имя столбца и его тип данных, определение столбца может содержать два необязательных раздела: значение столбца по умолчанию и раздел дополнительных ограничений целостности столбца.
В разделе значения по умолчанию указывается значение, которое должно быть помещено в строку, заносимую в данную таблицу, если значение данного столбца явно не указано. В соответствии со стандартом языка SQL значение по умолчанию может быть указано в виде литеральной константы с типом, соответствующим типу столбца; путем задания ключевого слова USER, которому при выполнении оператора занесения строки соответствует символьная строка, содержащая имя текущего пользователя (в этом случае столбец должен иметь тип символьных строк); или путем задания ключевого слова NULL, означающего, что значением по умолчанию является неопределенное значение.
Если значение столбца по умолчанию не специфицировано и в разделе ограничений целостности столбца указано NOT NULL (то есть наличие неопределенных значений запрещено), то попытка занести в таблицу строку с незаданным значением данного столбца приведет к ошибке.
Задание в разделе ограничений целостности столбца выражения NOT NULL приводит к неявному порождению проверочного ограничения целостности для всей таблицы "CHECK (С IS NOT NULL)" (где С - имя данного столбца). Если ограничение NOT NULL не указано и раздел умолчаний отсутствует, то неявно порождается раздел умолчаний DEFAULT NULL. Если указана спецификация уникальности, то порождается соответствующая спецификация уникальности для таблицы.
При задании ограничений уникальности данный столбец определяется как возможный ключ, что предполагает уникальность каждого вводимого значения в данный столбец. И если это ограничение задано, то СУБД будет автоматически
141
осуществлять проверку на отсутствие дубликатов значений данного столбца во всей таблице.
Если в разделе ограничений целостности указано ограничение по ссылкам данного столбца, то порождается соответствующее определение ограничения по ссылкам для таблицы: FOREIGN KEY() , что означает, что значения данного столбца должны быть взяты из соответствующего столбца родительской таблицы. Родительской таблицей в данном случае называется таблица, которая связана с данной таблицей связью "один - ко - многим" (1:М). При этом каждая строка родительской таблицы может быть связана с несколькими строками определяемой таблицы. Трансляция операторов SQL проводится в режиме интерпретации, поэтому важно, чтобы сначала была бы описана родительская таблица, а потом уже все подчиненные (дочерние) таблицы, связанные с ней. Иначе транслятор определит ссылку на неопределенный объект.
Наконец, если указано проверочное ограничение столбца, то условие поиска этого ограничения должно ссылаться только на данный столбец, и неявно порождается соответствующее проверочное ограничение для всей таблицы.
В проверочных ограничениях, накладываемых на столбец, нельзя задавать сравнение со значениями других столбцов данной таблицы.
В главе 5 определены типы данных, которые допустимы по стандартам SQL. Попробуем написать простейший оператор создания таблицы BOOKS из базы данных "Библиотека".
При этом будем предполагать наличие следующих ограничений целостности:
Шифр книги - последовательность символов длиной не более 14, однозначно определяющая книгу, значит, это - фактически первичный ключ таблицы BOOKS.
Название книги - последовательность символов, не более 120. Обязательно должно быть задано.
Автор - последовательность символов, не более 30, может быть не задан.
Соавтор - последовательность символов, не более 30, может быть не задан.
Год издания - целое число, не менее 1960 и не более текущего года. По умолчанию ставится текущий год.
Издательство - последовательность символов, не более 20, может отсутствовать.
Количество страниц - целое число не менее 5 и не более 1000.
CREATE TABLE BOOKS
(
ISBN varchar(14) NOT NULL PRIMARY KEY,
TITLE varchar(120) NOT NULL.
AUTOR varchar (30) NULL.
COAUTOR varchar(30) NULL.
YEAR_PUBL smallint DEFAULT Year(GetDate( )) CHECK(YEAR_PUBL >= I960 AND
YEAR PUBL
142
PUBLICH varchar(20) NULL,
PAGES smallint CHECK(PAGES > - 5 AND PAGES
):
Почему мы не задали обязательность значения для количества страниц в книге? Потому что это является следствием проверочного ограничения, заданного на количество страниц, количество страниц всегда должно лежать в пределах от 5 до 1000, значит, оно не может быть незаданным и система это контролирует автоматически.
Теперь зададим описание таблицы "Читатели", которой соответствует отношение READERS:
Номер читательского билета - это целое число в пределах 32 000 и он уникально определяет читателя.
Имя, фамилия читателя - это последовательность символов, не более 30.
Адрес - это последовательность символов, не более 50.
Номера телефонов рабочего и домашнего - последовательность символов, не более 12.
Дата рождения - календарная дата. В библиотеку принимаются читатели не младше 17 лет.
CREATE TABLE READERS
(
READER_ID Smallint(4) PRIMARY KEY,
FIRST_NAME char(30) NOT NULL,
LAST_NAME char(30) NOT NULL,
ADRES . char(50).
HOME_PHON char(12).
WORK_PHON char(12).
BIRTH_DAYdate CHECK(DateDiff(year, GetDate( ).BIRTH_DAY) >=17)
):
Здесь DateDiff (часть даты, начальная дата, конечная дата) - функция MS SQL Server 7.0, которая определяет разность между начальной и конечной датами, заданную в единицах, определенных первым параметром - часть даты. Мы задали в качестве параметра Year, что значит, что мы разность определяем в годах.
Теперь зададим операцию создания таблицы EXEMPLAR (экземпляры книги). В этой таблице первичным ключом является атрибут, задающий инвентарный номер экземпляра книги. В такой постановке мы полагаем, что при поступлении книг в библиотеку им просто присваиваются соответствующие порядковые номера. Для того чтобы не утруждать библиотекаря все время помнить, какой номер был последним, мы можем воспользоваться тем, что некоторые СУБД допускают специальный инкрементный тип данных, то есть такой, значения которого автоматически увеличиваются или уменьшаются на заданную величину при каждом новом вводе данных. В СУБД MS Access такой тип данных называется
143
"счетчик" (counter) и он всегда имеет начальное значение 1 и шаг, равный тоже 1, то есть при вводе каждого нового значения счетчик увеличивается на 1, значит, практически считает вновь введенные значения. В СУБД MS SQL Server 7.0 это свойство IDENTITY, которое может быть присвоено ряду целочисленных типов данных. В отличие от "счетчика" свойство IDENTITY позволяет считать с любым шагом, положительным или отрицательным, но обязательно целым. Если мы не задаем дополнительных параметров этому свойству, то оно начинает работать как счетчик в MS Access, начиная с единицы и добавляя при каждом вводе тоже единицу.
Кроме того, таблица EXEMPLAR является подчиненной двум другим ранее определенным таблицам: BOOKS и READERS.
При этом с таблицей BOOKS таблица EXEMPLAR связана обязательной связью, потому что не может быть ни одного экземпляра книги, который бы не был приписан конкретной книге. С таблицей READERS таблица EXEMPLAR связана необязательной связью, потому что не каждый экземпляр в данный момент находится на руках у читателя. Для моделирования этих связей при создании таблицы EXEMPLAR должны быть определены два внешних ключа (FOREIGN KEY). При этом атрибут, соответствующий шифру книги (мы его назовем так же, как и в родительской таблице - ISBN), является обязательным, то есть не может принимать неопределенных значений, а атрибут, который является внешним ключом для связи с таблице READERS, является необязательным и может принимать неопределенные значения.
Необязательными там являются два остальных атрибута: дата взятия и дата возврата книги, оба они имеют тип данных, соответствующей календарной дате. Атрибут, который содержит информацию о присутствии или отсутствии книги, имеет логический тип. Напишем оператор создания таблицы EXEMPLAR в синтаксисе MS SQL Server 7.0:
CREATE TABLE EXEMPLAR
(
EXEMPLAR_ID INT IDENTITY PRIMARY KEY.
ISBN varchar(14) NOT NULL FOREIGN KEY references BOOKS(ISBN),
READER_ID Smallint(4) NULL FOREIGN KEY references READERS (READER_ID),
DATA_IN date,
DATA_OUT date,
EXIST Logical.
):
Как мы уже знаем, не все декларативные ограничения могут быть заданы на уровне столбцов таблицы, часть ограничений может быть задана только на уровне всей таблицы. Например, если мы имеем в качестве первичного ключа не один атрибут, а последовательность атрибутов, то мы не можем определить ограничение типа PRIMARY KEY (первичный ключ) только на уровне всей таблицы.
Допустим, что мы считаем экземпляры книги не подряд, а отдельно для каждого издания, тогда таблица EXEMPLAR в качестве первичного ключа будет иметь набор из двух атрибутов: это шифр книги (ISBN) и порядковый номер экземпляра
144
данной книги (ID_EXEMPL), в этом случае оператор создания таблицы EXEMPLAR будет выглядеть следующим образом:
CREATE TABLE EXEMPLAR
(
ID_EXEMPLAR int NOT NULL.
ISBN varchar(14) NOT NULL FOREIGN KEY references BOOKS(ISBN).
READERJD Small int (4) NULL FOREIGN KEY references READERS (READERJD).
DATA_IN date.
DATA_OUT date.
EXIST Logical.
PRIMARY KEY (IDJXEMPLAR. ISBN)
);
Мы видим, что один и тот же атрибут ISBN, с одной стороны, является внешним ключом (FORIGN KEY), а с другой стороны, является частью первичного ключа (PRIMARY KEY). И ограничение типа первичный ключ (PRIMARY KEY) задается не на уровне одного атрибута, а на уровне всей таблицы, потому что оно содержит набор атрибутов.
То же самое можно сказать и о проверочных (CHECK) ограничениях, если условия проверки предполагают сравнения значений нескольких столбцов таблицы. Введем дополнительное ограничение для таблицы BOOKS, которое может быть сформулировано следующим образом: соавтор не может быть задан, если не задан автор. При описании книги допустимо не задавать ни автора, ни соавтора, или задать и автора и соавтора, или задать только автора. Однако задание соавтора в отсутствие задания автора считается ошибочным. В этом случае оператор создания таблицы BOOKS будет выглядеть следующим образом:
CREATE TABLE BOOKS
(
ISBN varchar(14) NOT NULL PRIMARY KEY.
TITLE varchar(120) NOT NULL.
AUTOR varchar (30) NULL.
COAUTOR varchar(30) NULL.
YEAR_PUBL small int DEFAULT Year(GetDate( )) CHECK(YEAR_PUBL >= I960 AND
YEAR_PUBL
PUBLICH varchar(20) NULL.
PAGES smallint CHECK(PAGES >= 5 AND PAGES
CHECK (NOT (AUTOR IS NULL AND COAUTOR IS NOT NULL))
);
Для анализа ошибок целесообразно именовать все ограничения, особенно если таблица содержит несколько ограничений одного типа. Для именования ограничений используется ключевое слово CONSTRAINT, после которого следует уникальное
145
имя ограничения, затем тип ограничения и его выражения. Для идентификации ограничений рекомендуют использовать систему именования, которая легко позволит определить при получении сообщения об ошибке, которое вырабатывает СУБД, какое ограничение нарушено.
Обычно имя ограничения состоит из краткого названия типа ограничения, далее через символ подчеркивания идет имя атрибута или таблицы, в зависимости от того, к какому уровню относится ограничение, и, наконец, порядковый номер ограничения данного типа, если к одному объекту задается несколько ограничений одного типа.
Сокращенные обозначения ограничений состоят из одной или двух букв и могут быть следующими:
PK - для первичного ключа;
FK - для внешнего ключа;
CK - для проверочного ограничения;
U - для ограничения уникальности;
DF - для ограничения типа значение по умолчанию.
Приведем пример оператора создания таблицы BOOKS с именованными ограничениями:
CREATE TABLE BOOKS
(
ISBN varchar(14) NOT NULL,
TITLE varchar(120) NOT NULL,
AUTOR varchar (30) NULL.
COAUTOR varchar(30) NULL.
YEAR_PUBL smallint NOT NULL.
PUBLICH varchar(20) NULL.
PAGES smallint NOT NULL.
CONSTRAINT PK_BOOKS PRIMARY KEY (ISBN).
CONSTRAINT DF_ YEAR_PUBL DEFAULT (Year(GetDate( )).
CONSTRAINT CK_ YEAR_PUBL CHECK (YEAR_PUBL >= 1960 AND
YEAR_PUBL
CONSTRANT CK_PAGES CHECK (PAGES > = 5 AND PAGES
CONSTRAINT CK_BOOKS CHECK (NOT (AUTOR IS NULL AND COAUTOR IS NOT NULL))
):
CREATE TABLE READERS
(
READER_ID Smallint PRIMARY KEY.
FIRST_NAME char(30) NOT NULL.
LAST_NAME char(30) NOT NULL.
ADRES char(50).
146
HOME_PHON char(12).
WORK_PHON char(12),
BIRTH_DAY date СНЕCK DateDiff(year. GetDate().BIRTH_DAY) >=17 ).
CONSTRAINT CK_READERS CHECK (HOME_PHON IS NOT NULL OR WORK_PHON IS NOT NULL)
);
CREATE TABLE CATALOG
(
ID_CATALOG Smallint PRIMARY KEY,
KNOWELEDGE_AREA varchar(150)
);
CREATE TABLE EXEMPLAR
(
ID_EXEMPLAR int NOT NULL,
ISBN varchar(14) NOT NULL FOREIGN KEY references BOOKS(ISBN).
READER_ID Smallint(4) NULL FOREIGN KEY references READERS (READERJD).
DATA_IN date.
DATA_OUT date,
EXIST Logical,
PRIMARY KEY (ID_EXEMPLAR, ISBN)
);
CREATE TABLE RELATION_1
(
ISBN varchar(14) NOT NULL FOREIGN KEY references BOOKS(ISBN).
ID_CATALOG smallint NOT NULL FOREIGN KEY references CATALOG(ID_CATALOG).
CONSTRAINT PK RELATION 1 PRIMARY KEY (ISBN,ID CATALOG)
).
Операторы языка SQL, как указывалось ранее, транслируются в режиме интерпретации, в отличие от большинства алгоритмических языков, трансляторы для которых выполнены по принципу компиляции. В режиме интерпретаций каждый оператор отдельно транслируется, то есть переводится в машинные коды, и тут же выполняется. В режиме компиляции вся программа, то есть совокупность операторов, сначала переводится в машинные коды, а затем может быть выполнена как единое целое. Такая особенность SQL накладывает ограничение на порядок описания создаваемых таблиц. Действительно, если при трансляции оператора описания подчиненной таблицы с указанным внешним ключом и соответствующей ссылкой на родительскую таблицу эта родительская таблица не будет обнаружена, то мы получим сообщение об ошибке с указанием ссылки на несуществующий объект. Сначала должны быть описаны все основные таблицы, а потом подчиненные таблицы.
В нашем примере с библиотекой порядок описания таблиц следующий:
147
Таблица BOOKS
Таблица READERS
Таблица CATALOG (системный каталог)
Таблица EXEMPLAR
Таблица RELATION_1 (дополнительная связующая таблица между книгами и системным каталогом).
Набор операторов языка SQL принято называть не программой, а скриптом. Тогда скрипт, который добавит набор из 5 взаимосвязанных таблиц базы данных "Библиотека" в существующую базу данных, будет выглядеть следующим образом:
CREATE TABLE BOOKS
(
ISBN varchar(14) NOT NULL ,
TITLE varchar(120) NOT NULL.
AUTOR varchar (30) NULL.
COAUTOR varchar(30) NULL.
YEAR_PUBL smallint NOT NULL.
PUBLICH varchar(20) NULL.
PAGES smallint NOT NULL.
CONSTRAINT PK_BOOKS PRIMARY KEY (ISBN).
CONSTRAINT DF_ YEAR_PUBL DEFAULT (Year(GetDate( )).
CONSTRAINT CK_ YEAR_PUBL CHECK (YEAR_PUBL >= 1960 AND
YEAR_PUBL
CONSTRANT CK_PAGES CHECK (PAGES > = 5 AND PAGES
CONSTRAINT CK_BOOKS CHECK (NOT (AUTOR IS NULL AND COAUTOR IS NOT NULL))
CREATE TABLE READERS
(
READER_ID Smallint PRIMARY KEY.
FIRST_NAME char(30) NOT NULL.
LAST_NAME char(30) NOT NULL.
ADRES char(50),
HOME_PHON char(12).
WORK_PHON char(12),
BIRTH_DAY date СНЕCK DateDiff(year. GetDate(),BIRTH_DAY) >=17 ).
CONSTRAINT CK_READERS CHECK ( HOME_PHON IS NOT NULL OR WORK_PHON IS NOT NULL)
);
CREATE TABLE CATALOG
(
148
ID_CATALOG Smallint PRIMARY KEY.
KNOWELEDGE_AREA varchar(lSO)
);
CREATE TABLE EXEMPLAR
(
ID_EXEMPLAR Int NOT NULL,
ISBN varchar(14) NOT NULL FOREIGN KEY references BOOKS(ISBN).
READER_ID Smallint(4) NULL FOREIGN KEY references READERS (READERJD),
DATA_IN date,
DATA_OUT date,
EXIST Logical,
PRIMARY KEY (ID_EXEMPLAR. ISBN)
):
CREATE TABLE RELATION_1
(
ISBN varchar(14) NOT NULL FOREIGN KEY references BOOKS(ISBN).
ID_CATALOG smallint NOT NULL FOREIGN KEY references CATALOG(ID_CATALOG).
CONSTRAINT PK_RELATION_1 PRIMARY KEY (ISBN.ID_CATALOG)
).
При написании скрипта мы добавили в оператор создания таблицы "Читатели" ограничение на уровне таблицы, которое связано с обязательным наличием хотя бы одного из двух телефонов.
Операторы манипулирования данными
В операции манипулирования данными входят три операции: операция удаления записей - ей соответствует оператор DELETE, операция добавления или ввода новых записей - ей соответствует оператор INSERT и операция изменения (обновления записей) - ей соответствует оператор UPDATE. Рассмотрим каждый из операторов подробнее.
Все операторы манипулирования данными позволяют изменить данные только в одной таблице.
Оператор ввода данных INSERT имеет следующий синтаксис:
INSERT INTO имя_таблицы [() ] VALUES ()
Подобный синтаксис позволяет ввести только одну строку в таблицу. Задание списка столбцов необязательно тогда, когда мы вводим строку с заданием значений всех столбцов. Например, введем новую книгу в таблицу BOOKS
INSERT INTO BOOKS ( ISBN.TITL.AUTOR.COAUTOR.YEARIZD.PAGES)
VALUES ("5-88782-290-2"."Аппаратные средства IBM PC. Энциклопедия".
"Гук М. "."".2000.816)
В этой книге только один автор, нет соавторов, но мы в списке столбцов задали столбец COAUTOR, поэтому мы должны были ввести соответствующее значение в разделе VALUES. Мы ввели пустую строку, потому что мы знаем точно, что нет соавтора. Мы могли бы ввести неопределенное значение NULL.
Так как мы вводим полную строку, то мы можем не задавать список столбцов, ограничиться только заданием перечня значений, в этом случае оператор ввода будет выглядеть следующим образом:
INSERT INTO BOOKS VALUES ("5-88782-290-2".
"Аппаратные средства IBM PC. Энциклопедия".Тук М."."".2000.816)
Результаты работы обоих операторов одинаковые.
Наконец, мы можем ввести неполный перечень значений, то есть не вводить соавтора, так как он отсутствует для данного издания. Но в этом случае мы должны
95
задать список вводимых столбцов, тогда оператор ввода будет выглядеть следующим образом:
INSERT INTO BOOKS ( ISBN,TITL.AUTOR.YEARIZD.PAGES)
VALUES ("5-88782-290-2"."Аппаратные средства IBM PC. Энциклопедия".
"Гук М.".2000.816)
Столбцу COAUTOR будет присвоено в этом случае значение NULL.
Какие столбцы должны быть заданы при вводе данных? Это определяется тем, как описаны эти столбцы при описании соответствующей таблицы, и будет рассмотрено более подробно при описании языка DDL (Data Definition Language) в главе 8. Здесь мы пока отметим, что если столбец или атрибут имеет признак обязательный (NOT NULL) при описании таблицы, то оператор INSERT должен обязательно содержать данные для ввода в каждую строку данного столбца. Поэтому если в таблице все столбцы обязательные, то каждая вводимая строка должна содержать полный перечень вводимых значений, а указание имен столбцов в этом случае необязательно. В противном случае, если имеется хотя бы один необязательный столбец и вы не вводите в него значений, задание списка имен столбцов - обязательно.
В набор значений могут быть включены специальные функции и выражения. Ограничением здесь является то, что значения этих функций должны быть определены на момент ввода данных. Поэтому, например, мы можем сформировать оператор ввода данных в таблицу EXEMPLAR следующим образом:
INSERT INTO EXEMPLAR (INV.ISBN.YES_NO.NUM_READER.DATE_IN. DATE_OUT)
VALUES (1872. "5-88782-290-2".NO.344.GetDateC).DateAdd(d.GetDateC).14))
И это означает, что мы выдали экземпляр книги с инвентарным номером 1872 читателю с номером читательского билете 344, отметив, что этот экземпляр не присутствует с этого момента в библиотеке, и определили дату выдачи книги как текущую дату (функция GetDate()), а дату возврата задали двумя неделями позднее, использовав при этом функцию DateAdd (), которая позволяет к одной дате добавить заданное количество интервалов даты и тем самым получить новое значение типа "дата". Мы добавили 14 дней к текущей дате.
Оператор ввода данных позволяет ввести сразу множество строк, если их можно выбрать из некоторой другой таблицы. Допустим, что у нас есть таблица со студентами и в ней указаны основные данные о студентах: их фамилии, адреса, домашние телефоны и даты рождения.
Тогда мы можем сделать всех студентов читателями нашей библиотеки одним оператором:
INSERT INTO READER.(NAME_READER. ADRESS. HOOM_PHONE. BIRTH_DAY)
SELECT (NAME_STUDENT, ADRESS. HOOM_PHONE. BIRTH_DAY)
FROM STUDENT
При этом номер читательского билета может назначаться автоматически, поэтому мы не вводим значения этого столбца в таблицу. Кроме того, мы предполагаем, что у студентов дневного отделения еще нет работы и поэтому нет рабочего телефона, и мы его не вводим.
Оператор удаления данных позволяет удалить одну или несколько строк из таблицы в соответствии с условиями, которые задаются для удаляемых строк.
96
Синтаксис оператора DELETE следующий:
DELETE FROM имя_таблицы [WHERE условия_отбора]
Если условия отбора не задаются, то из таблицы удаляются все строки, однако это не означает, что удаляется вся таблица. Исходная таблица остается, но она остается пустой, незаполненной.
Например, если нам надо удалить результаты прошедшей сессии, то мы можем удалить все строки из отношения R1 командой
DELETE FROM R1
Условия отбора в части WHERE имеют тот же вид, что и условия фильтрации в операторе SELECT. Эти условия определяют, какие строки из исходного отношения будут удалены. Например, если мы исключим студента Миронова А. В., то мы должны написать следующую команду:
DELETE FROM R2
WHERE ФИО = ?Миронов А.В.?
В части WHERE может находиться встроенный запрос. Например, если нам надо исключить неуспевающих студентов, то по закону о высшем образовании неуспевающим считается студент, имеющий две и более задолженности по последней сессии. Тогда нам в условиях отбора надо найти студентов, имеющих либо две или более двоек, либо два и более несданных экзамена из числа тех, которые студент сдавал. Для поиска таких горе - студентов нам надо выбрать из отношения R1 все строки с оценкой 2 или с неопределенным значением, потом надо сгруппировать полученный результат по атрибуту ФИО и, подсчитав количество строк в каждой группе, которое соответствует количеству несданных экзаменов каждым студентом, отобрать те группы, у которых количество строк не менее двух.
Теперь попробуем просто записать эту сложную конструкцию на SQL и убедимся, что этот сложный запрос записывается достаточно компактно.
DELETE FROM R2
WHERE R2.ФИО IN
(SELECT R1.ФИО
FROM Rl
WHERE Оценка = 2 OR Оценка IS NULL
GROOP BY R1.ФИО
HAVING COUNT(*) >= 2
Однако при выполнении операции DELETE, включающей сложный подзапрос, в подзапросе нельзя упоминать таблицу, из которой удаляются строки, поэтому СУБД отвергнет такой красивый подзапрос, который попытается удалить всех не только сдававших, но и несдававших студентов, которые имеют более двух задолженностей.
DELETE FROM R2
WHERE R2 ФИО IN
97
(SELECT R1 ФИО
FROM (R2 NATURAL INNER JOIN R3 ) LEFT JOIN Rl USING ( ФИО. Дисциплина)
WHERE Оценка = 2 OR Оценка IS NULL
GROOP BY R1.ФИО
HAVING COUNT(*) >= 2
Все операции манипулирования данными связаны с понятием целостности базы данных, которое будет рассматриваться далее в главе 9. В настоящий момент мне бы хотелось отметить только то, что операции манипулирования данными не всегда выполнимы, даже если синтаксически они написаны правильно. Действительно, если мы бы захотели удалить какую-нибудь группу из отношения R3, то СУБД не позволила бы нам это сделать, так как в отношениях R1 и R2 есть строки, связанные с удаляемой строкой в отношении R3. Почему так делается, мы узнаем позднее, а пока просто примем к сведению, что не все операторы манипулирования выполнимы.
Операция обновления данных UPDATE требуется тогда, когда происходят изменения во внешнем мире и их надо адекватно отразить в базе данных, так как надо всегда помнить, что база данных отражает некоторую предметную область. Например, в нашем учебном заведении произошло счастливое событие, которое связано с тем, что госпожа Степанова К. Е. пересдала экзамен по дисциплине "Базы данных" с двойки сразу на четверку. В этом случае нам надо срочно выполнить соответствующую корректировку таблицы R1. Операция обновления имеет следующий формат:
UPDATE имя_таблицы
SET имя_столбца = новое_значение
[WHERE условие_отбора]
Часть WHERE является необязательной, так же как и в операторе DELETE. Она играет здесь ту же роль, что и в операторе DELETE, - позволяет отобрать строки, к которым будет применена операция модификации. Если условие отбора не задается, то операция модификации будет применена ко всем строкам таблицы.
Для решения ранее поставленной задачи нам необходимо выполнить следующую операцию
UPDATE Rl
SET R1.Оценка = 4
WHERE R1.ФИО = "Степанова К.Е." AND R1.Дисциплина = "Базы данных"
В каких случаях требуется провести изменение в нескольких строках? Это не такая уж редкая задача. Например, если мы расширим нашу учебную базу данных еще одним отношением, которое содержит перечень курсов, на которых учатся наши студенты, то можно с помощью операции обновления промоделировать операцию перевода групп на следующий курс. Пусть новое отношение R4 имеет следующую схему:
R4 = < Группа, Курс >
98
| R4 | |
| Группа | Курс |
| 4906 | 3 |
| 4807 | 4 |
UPDATE R4
SET R4.Kypc = R4.Kypc + 1
И результат будет выглядеть следующим образом:
| Группа | Курс |
| 4906 | 4 |
| 4807 | 5 |
| R5 | ||
| ФИО | Группа | Стипендия |
| Петров Ф. И. | 4906 | |
| Сидоров К. А. | 4906 | |
| Миронов А. В. | 4906 | |
| Крылова Т. С. | 4906 | |
| Владимиров В. А. | 4906 | |
| Трофимов П. А. | 4807 | |
| Иванова Е. А. | 4807 | |
| Уткина Н. В. | 4807 |
При отсутствии троек на сданных экзаменах назначим обычную стипендию с надбавкой 0%. Однако
99
все эти изменения мы должны будем сделать отдельными операциями обновления.
Назначение повышенной стипендии:
UPDATE R5
SET R5.Стипендия = 50%
WHERE R5.ФИО IN
(SELECT R1.ФИО
FROM R1
WHERE R1.Оценка = 5
GROOP BY R1.ФИО
HAVING COUNT(*) =3 )
Назначение стипендии с надбавкой 25%:
UPDATE R5
SET R5.Стипендия = 25%
WHERE R5.ФИО IN
(SELECT R1.ФИО
FROM R1
WHERE R1.ФИО NOT IN
(SELECT А.ФИО
FROM R1 A
WHERE А.Оценка
GROOP BY R1.ФИО
HAVING COUNT(*)>=2 )
Назначение обычной стипендии:
UPDATE R5
SET R5.Стипендия = О%
WHERE R5.ФИО IN
(SELECT R1.ФИО
FROM R1
WHERE R1.Оценка >=4 AND R1.ФИО NOT IN
(SELECT А.ФИО
FROM R1 A
WHERE А.Оценка
Снятие стипендии:
UPDATE R5
SET R5.Стипендия = -100%
WHERE R5.ФИО IN
100
(SELECT R1.ФИО
FROM R1
WHERE R1.Оценка
Почему мы в первом запросе на обновление не использовали дополнительную проверку на отсутствие двоек, троек и несданных экзаменов, как мы сделали это при назначении следующих видов стипендии? Просто мы учли особенности нашей предметной области: у нас в соответствии с исходными данными не только 3 экзамена. Но если мы можем предположить, что число экзаменов может быть произвольным и изменяться от семестра к семестру, то нам надо изменить наш запрос. Запрос - это некоторый алгоритм решения конкретной задачи, которую мы формулируем заранее на естественном языке. И оттого, что наша задача решается всего одним оператором языка SQL, она не становится примитивной. Мощность языка SQL и состоит в том, что он позволяет одним предложением сформулировать ответы на достаточно сложные запросы, для реализации которых на традиционных языках понадобилось бы писать большую программу. Итак, подумаем, как нам надо изменить текст нашего запроса на обновление для назначения повышенной стипендии при любом количестве сданных экзаменов. Прежде всего, каждая группа может иметь свое число экзаменов в сессию, это зависит от специальности и учебного плана, по которому учится данная группа.
Поэтому для каждого студента нам надо знать, сколько экзаменов он должен был сдавать и сколько экзаменов он сдал на пять, и в том случае, когда эти два числа равны, мы можем назначить ему повышенную стипендию.
Будем решать нашу задачу по шагам. В конечном счете нам все равно надо знать, сколько экзаменов должен сдавать каждый конкретный студент, поэтому сначала сосчитаем количество экзаменов, которые должна сдавать группа, в которой учится этот студент.
Это мы делать умеем, для этого надо сделать запрос SELECT над отношением R3, сгруппировав его по атрибуту Группа, и вывести для каждой группы количество дисциплин, по которым должны сдаваться экзамены. Если мы учтем, что в одной сессии по одной дисциплине не бывает более одного экзамена, то можно просто подсчитывать количество строк в каждой группе.
SELECT R3.Группа. Число_экзаменов = COUNT(*)
FROM R3
GROOP BY R3.Группа
Однако нам нужен не этот запрос, нам нужен запрос, в котором мы определяем для каждого студента количество экзаменов. Этот запрос мы должны строить по схеме встроенного запроса:
SELECT COUNT(*)
FROM R3
WHERE R2.Группа - R3.Группа
GROOP BY R3.Группа
101
А почему мы здесь в части FROM не написали имя второго отношения R2? Мы имя этого отношения укажем для связи с вышестоящим запросом, когда будем формировать запрос полностью. Теперь попробуем сформулировать полностью запрос. Нам надо объединить отношения R1 и R2 по атрибуту ФИО, нам надо знать группу, в которой учится каждый студент, далее надо выбрать все строки с оценкой 5 и сгруппировать их по фамилии студента, сосчитав количество строк в каждой группе, а выбирать мы будем те группы, в которых число строк в группе равно числу строк во встроенном запросе, рассмотренном ранее, при условии равенства количества строк в группе результату подзапроса, который выводит только одно число.
SELECT R1.ФИО
FROM R1.R2
WHERE R1. ФИО = Р2.ФИО AND R1.Оценка = 5
GROOP BY R1.ФИО
HAVING COUNT(*) - (SELECT COUNT(*)
FROM R3
WHERE R2.Группа = R3.Группа
GROOP BY R3.Группа)
Ну а теперь нам осталась последняя простейшая операция: надо заменить старый вложенный запрос, определявший отличников, получивших три пятерки на сессии, на новый универсальный запрос:
UPDATE R5
SET R5.Стипендия = 50%
WHERE R5.ФИО IN (SELECT R1.ФИО
FROM R1.R2
WHERE Rl. ФИО = R2.ФИО AND R1.Оценка = 5
GROOP BY R1.ФИО
HAVING COUNT(*) = (SELECT COUNT(*)
FROM R3
WHERE R2.Группа = R3.Группа
GROOP BY R3.Группа))
Вот какой сложный запрос мы построили. Это ведь практически один оператор, а какую сложную задачу он решает. Действительно, мощность языка SQL иногда удивляет даже профессионалов, кажется невозможно построить один запрос для решения конкретной задачи, но когда начинаешь поэтапно его конструировать - все получается. Самое сложное - это сделать переход от словесной формулировки задачи к представлению ее в терминах нашего SQL, но этот процесс сродни процессу алгоритмизации при решении задач традиционного программирования, а он всегда был самым трудным, творческим и неформализуемым процессом. Недаром на заре развития программирования известный американский специалист по программированию Дональд Е. Кнут озаглавил свой многотомный
102
капитальный труд по теории и практике программирования "Искусство программирования для ЭВМ" ("The art of computer programming").
Операторы модификации данных
1. Удалить
Это первая из трех операций модификации.
Синтаксис:
DELETE
Эта команда не имеет параметров. Почему? Потому что операции модификации действуют на экземпляр сегмента, найденный командами поиска с удержанием. А он всегда единственный текущий найденный и удерживаемый для модификации экземпляр конкретного сегмента. Поэтому при выполнении команды удаления будет удален именно этот экземпляр сегмента.
2. Обновить
Синтаксис:
UPDATE
Как же происходит обновление, если мы и в этой команде не задаем никаких параметров. СУБД берет данные из рабочей области пользователя, где в шаблонах записей соответствующих внутренних переменных находятся значения полей каждого сегмента внешней модели, с которой работает данный пользователь. Именно этими значениями и обновляется текущий экземпляр сегмента. Значит, перед тем как выполнить операции модификации UPDATE, необходимо присвоить соответствующим переменным новые значения.
Ввести новый экземпляр сегмента.
INSERT
39
Эта команда позволяет ввести новый экземпляр сегмента, имя которого определено в параметре команды. Если мы вводим данные в сегмент, который является подчиненным некоторому родительскому экземпляру сегмента, то он будет внесен в БД и физически подключен к тому экземпляру родительского сегмента, который в данный момент является текущим.
Как видим, набор операций поиска и манипулирования данными в иерархической БД невелик, но он вполне достаточен для получения доступа к любому экземпляру любого сегмента БД. Однако следует отметить, что способ доступа, который применяется в данной модели, связан с последовательным перемещением от одного экземпляра сегмента к другому. Такой способ напоминает движение летательного аппарата или корабля по заданным координатам и называется навигационным.
40
37 :: 38 :: 39 :: 40 :: Содержание
Операторы поиска данных
Синтаксис:
GET UNIQUE WHERE ;
37
список поиска состоит из последовательности условий вида:
ОС ;
ОС - операция сравнения;
условия могут быть соединены логическими операциями И и ИЛИ {& , V}.
Назначение:
Получить единственное значение.
Пример:
Найти типовую модель стоимостью не более $600, которая существует не менее чем в 10 экземплярах.
GET UNIQUE ТИПОВЫЕ МОДЕЛИ WHERE Типовые модели. Стоимость = 10
Данная команда всегда ищет с начала БД и останавливается, найдя первый экземпляр сегмента, удовлетворяющий условиям поиска.
Синтаксис:
GET NEXT WHERE
Назначение:
Получить следующий экземпляр сегмента для тех же условий.
Пример:
Напечатать полный список заказов стоимостью не менее $500.
GET UNIQUE ИНДИВИДУАЛЬНЫЕ МОДЕЛИ WHERE Индивидуальные модели Стоимость >= $500 WHILE NOT FAIL (пока не конец поиска) DO
PRINT № заказа. Стоимость. Количество
GET NEXT ИНДИВИДУАЛЬНЫЕ МОДЕЛИ
END
Синтаксис:
GET NEXT WITHIN PARENT [ where ]
Назначение:
Получить следующий для того же исходного.
Пример:
Получить перечень винчестеров, имеющихся на складе номер 1, в количестве не менее 10 с объемом 10 Гбайт.
GET UNIQUE СКЛАД WHERE Склад Номер = 1
GET NEXT ИЗДЕЛИЕ WITHIN PARENT WHERE Изделие Наименование = "Винчестер"
GET NEXT ХАРАКТЕРИСТИКИ WITHIN PARENT
WHERE ХАРАКТЕРИСТИКИ Параметр = 10 AND
ХАРАКТЕРИСТИКИ Единицы Измерения = Гб AND
ХАРАКТЕРИСТИКИ Величина > 10
While Not Fail (пока поиск не завершен) DO
Get Next Within Parent
end
38
Операторы поиска данных с возможностью модификации
1. Найти и удержать единственный экземпляр сегмента. Эта операция подобна первой операции поиска GET UNIQUE, единственным отличием этой операции является го, что после выполнения этой операции над найденным экземпляром сегмента допустимы операции модификации (изменения) данных.
Синтаксис:
GET HOLD UNIQUE WHERE
2. Найти и удержать следующий с теми же условиями поиска. Аналогично операции 4 эта операция дублирует вторую операции поиска GET NEXT с возможностью выполнения последующей модификации данных.
Синтаксис:
GET HOLD NEXT [WHERE < дополнительные условия>]
3. Получить и удержать следующий для того же родителя. Эта операция является аналогом операции поиска 3, но разрешает выполнение операций модификации данных после себя.
Синтаксис:
GET HOLD NEXT WITHIN PARENT [ where ]
Операторы, связанные с многострочными запросами
Рассмотрим более сложные многострочные запросы.
Для реализации многострочных запросов вводится новое понятие - понятие курсора или указателя набора записей. Для работы с курсором добавляется несколько новых операторов SQL:
Оператор DECLARE CURSOR - определяет выполняемый запрос, задает имя курсора и связывает результаты запроса с заданным курсором. Этот оператор не является исполняемым для запроса, он только определяет структуру будущего множества записей и связывает ее с уникальным именем курсора. Этот оператор подобен операторам описания данных в языках программирования.
Оператор OPEN дает команду СУБД выполнить описанный запрос, создать виртуальный набор строк, который соответствует заданному запросу. Оператор OPEN устанавливает указатель записей (курсор) перед первой строкой виртуального набора строк результата.
Оператор FETCH продвигает указатель записей на следующую позицию в виртуальном наборе записей. В большинстве коммерческих СУБД оператор перемещения FETCH реализует более широкие функции перемещения, он позволяет перемещать указатель на произвольную запись, вперед и назад, допускает как абсолютную адресацию, так и относительную адресацию, позволяет установить курсор на первую или последнюю запись виртуального набора.
252
Оператор CLOSE закрывает курсор и прекращает доступ к виртуальному набору записей. Он фактически ликвидирует связь между курсором и результатом выполнения базового запроса. Однако в коммерческих СУБД оператор CLOSE не всегда означает уничтожение виртуального набора записей. Мы коснемся этого далее, когда будем рассматривать работу с курсором в MS SQL Server 7.0.
253
252 :: 253 :: Содержание
Организация индексов в виде B-tree (В-деревьев)
Калькированный термин "В-дерево", в котором смешивается английский символ "В" и добавочное слово на русском языке, настолько устоялся в литературе, посвященной организации физического хранения данных, что я не решусь его корректировать.
Встретив как-то термин "Б-дерево", я долго его трактовала, потому что привыкла уже к устоявшемуся обозначению. Поэтому будем работать с этим термином.
Построение В-деревьев связано с простой идеей построения индекса над уже построенным индексом. Действительно, если мы построим неплотный индекс, то сама индексная область может быть рассмотрена нами как основной файл, над которым надо снова построить неплотный индекс, а потом снова над новым индексом строим следующий и так до того момента, пока не останется всего один индексный блок.
Мы в общем случае получим некоторое дерево, каждый родительский блок которого связан с одинаковым количеством подчиненных блоков, число которых равно числу индексных записей, размещаемых в одном блоке. Количество обращений к диску при этом для поиска любой записи одинаково и равно количеству уровней в построенном дереве. Такие деревья называются сбалансированными (balanced) именно потому, что путь от корня до любого листа в этом древе одинаков. Именно термин "сбалансированное" от английского "balanced" -"сбалансированный, взвешенный" и дал название данному методу организации индекса.
Построим подобное дерево для нашего примера и рассчитаем для него количество уровней и, соответственно, количество обращений к диску.
На первом уровне число блоков равно числу блоков основной области, это нам известно, - оно равно 12 500 блоков. Второй уровень образуется из неплотного индекса, мы его тоже уже строили и вычислили, что количество блоков индексной
176
области в этом случае равно 172 блокам. А теперь над этим вторым уровнем снова построим неплотный индекс.
Мы не будем менять длину индексной записи, а будем считать ее прежней, равной 14 байтам.
Количество индексных записей в одном блоке нам тоже известно, и оно равно 73. Поэтому сразу определим, сколько блоков нам необходимо для хранения ссылок на 172 блока.
КIВ3 = KIB2/KZIB = 172/73 = 3 блока
Мы снова округляем в большую сторону, потому что последний, третий, блок будет заполнен не полностью.
И над третьим уровнем строим новый, и на нем будет всего один блок, в котором будет всего три записи. Поэтому число уровней в построенном дереве равно четырем, и соответственно количество обращений к диску для доступа к произвольной записи равно четырем (рис. 9.9). Это не максимально возможное число обращений, а всегда одно и то же, одинаковое для доступа к любой записи.
Tд = Rуровн. = 4
Рис. 9.9. Построенное В-дерево
177
Механизм добавления и удаления записи при организации индекса в виде В-де-рева аналогичен механизму, применяемому в случае с неплотным индексом.
И наконец, последнее, что хотелось бы прояснить, - это наличие вторых названий для плотного и неплотного индексов.
В случае плотного индекса после определения местонахождения искомой записи доступ к ней осуществляется прямым способом по номеру записи, поэтому этот способ организации индекса и называется индексно - прямым.
В случае неплотного индекса после нахождения блока, в котором расположена искомая запись, поиск внутри блока требуемой записи происходит последовательным просмотром и сравнением всех записей блока. Поэтому способ индексации с неплотным индексом называется еще и индексно - последовательным.
Организация стратегии свободного замещения
При этой стратегии файловое пространство не разделяется на области, но для каждой записи добавляется 2 указателя: указатель на предыдущую запись в цепочке синонимов и указатель на следующую запись в цепочке синонимов. Отсутствие соответствующей ссылки обозначается специальным символом, например нулем. Для каждой новой записи вычисляется значение хэш - функции, и если данный адрес свободен, то запись попадает на заданное место и становится первой в цепочке синонимов. Если адрес, соответствующий полученному значению
168
хэш - функции, занят, то по наличию ссылок определяется, является ли запись, расположенная по указанному адресу, первой в цепочке синонимов. Если да, то новая запись располагается на первом свободном месте и для нее устанавливаются соответствующие ссылки: она становится второй в цепочке синонимов, на нее ссылается первая запись, а она ссылается на следующую, если таковая есть.
Если запись, которая занимает требуемое место, не является первой записью в цепочке синонимов, значит, она занимает данное место "незаконно" и при появлении "законного владельца" должна быть "выселена", то есть перемещена на новое место. Механизм перемещения аналогичен занесению новой записи, которая уже имеет синоним, занесенный в файл. Для этой записи ищется первое свободное место и корректируются соответствующие ссылки: в записи, которая является предыдущей в цепочке синонимов для перемещаемой записи, заносится указатель на новое место перемещаемой записи, указатели же в самой перемещаемой записи остаются прежние.
После перемещения "незаконной" записи вновь вносимая запись занимает свое законное место и становится первой записью в новой цепочке синонимов.
Механизмы удаления записей во многом аналогичны механизмам удаления в стратегии с областью переполнения. Однако еще раз кратко опишем их.
Если удаляемая запись является первой записью в цепочке синонимов, то после удаления на ее место перемещается следующая (вторая) запись из цепочки синонимов и проводится соответствующая корректировка указателя третьей записи в цепочке синонимов, если таковая существует.
Если же удаляется запись, которая находится в середине цепочки синонимов, то производится только корректировка указателей: в предшествующей записи указатель на удаляемую запись заменяется указателем на следующую за удаляемой запись, а в записи, следующей за удаляемой, указатель на предыдущую запись заменяется на указатель на запись, предшествующую удаляемой.
169
168 :: 169 :: Содержание
Основные функции группы администратора БД
1. Анализ предметной области: описание предметной области, выявление ограничений целостности, определение статуса (доступности, секретности) информации, определение потребностей пользователей, определение соответствия "данные - пользователь", определение объемно-временных характеристик обработки данных.
2. Проектирование структуры БД: определение состава и структуры файлов БД и связей между ними, выбор методов упорядочения данных и методов доступа к информации, описание БД на языке описания данных (ЯОД).
3. Задание ограничений целостности при описании структуры БД и процедур обработки БД:
задание декларативных ограничений целостности, присущих предметной области;
определение динамических ограничений целостности, присущих предметной области в процессе изменения информации, хранящейся в БД;
определение ограничений целостности, вызванных структурой БД;
разработка процедур обеспечения целостности БД при вводе и корректировке данных;
определение ограничений целостности при параллельной работе пользователей в многопользовательском режиме.
4. Первоначальная загрузка и ведение БД:
разработка технологии первоначальной загрузки БД, которая будет отличаться от процедуры модификации и дополнения данными при штатном использовании базы данных;
25
разработка технологии проверки соответствия введенных данных реальному состоянию предметной области. База данных моделирует реальные объекты некоторой предметной области и взаимосвязи между ними, и на момент начала штатной эксплуатации эта модель должна полностью соответствовать состоянию объектов предметной области на данный момент времени;
в соответствии с разработанной технологией первоначальной загрузки может понадобиться проектирование системы первоначального ввода данных.
5. Защита данных:
определение системы паролей, принципов регистрации пользователей, создание групп пользователей, обладающих одинаковыми правами доступа к данным;
разработка принципов защиты конкретных данных и объектов проектирования; разработка специализированных методов кодирования информации при ее циркуляции в локальной и глобальной информационных сетях;
разработка средств фиксации доступа к данным и попыток нарушения системы защиты;
тестирование системы защиты;
исследование случаев нарушения системы защиты и развитие динамических методов защиты информации в БД.
6. Обеспечение восстановления БД:
разработка организационных средств архивирования и принципов восстановления БД;
разработка дополнительных программных средств и технологических процессов восстановления БД после сбоев.
7. Анализ обращений пользователей БД: сбор статистики по характеру запросов, по времени их выполнения, по требуемым выходным документам
8. Анализ эффективности функционирования БД:
анализ показателей функционирования БД;
планирование реструктуризации (изменение структуры) БД и реорганизации БнД.
9. Работа с конечными пользователями:
сбор информации об изменении предметной области;
сбор информации об оценке работы БД;
обучение пользователей, консультирование пользователей;
разработка необходимой методической и учебной документации по работе конечных пользователей.
10. Подготовка и поддержание системных средств:
анализ существующих на рынке программных средств и анализ возможности и необходимости их использования в рамках БД;
разработка требуемых организационных и программно-технических мероприятий по развитию БД;
26
проверка работоспособности закупаемых программных средств перед подключением их к БД;
курирование подключения новых программных средств к БД.
11. Организационно-методическая работа по проектированию БД:
выбор или создание методики проектирования БД;
определение целей и направления развития системы в целом;
планирование этапов развития БД;
разработка общих словарей-справочников проекта БД и концептуальной модели;
стыковка внешних моделей разрабатываемых приложений;
курирование подключения нового приложения к действующей БД;
обеспечение возможности комплексной отладки множества приложений, взаимодействующих с одной БД.
Основные определения
Появление теоретико-множественных моделей в системах баз данных было пред определено настоятельной потребностью пользователей в переходе от работы с элементами данных, как это делается в графовых моделях, к работе с некоторыми макрообъектами. Основной моделью в этом классе является реляционная модель данных. Простота и наглядность модели для пользователей-непрограммистов, с одной стороны, и серьезное теоретическое обоснование, с другой стороны, определили большую популярность этой модели. Кроме того, развитие формального аппарата представления и манипулирования данными в рамках реляционной модели сделали ее наиболее перспективной для использования в системах представления знаний, что обеспечивает качественно иной подход к обработке данных в больших информационных системах.
Теоретической основой этой модели стала теория отношений, основу которой заложили два логика - американец Чарльз Содерс Пирс (1839-1914) и немец Эрнст Шредер (1841-1902). В руководствах по теории отношений было показано, что множество отношений замкнуто относительно некоторых специальных операций, то есть образует вместе с этими операциями абстрактную алгебру. Это важнейшее свойство отношений было использовано в реляционной модели для разработки языка манипулирования данными, связанного с исходной алгеброй. Американский математик Э. Ф. Кодд в 1970 году впервые сформулировал основные понятия и ограничения реляционной модели, ограничив набор операций в ней семью основными и одной дополнительной операцией. Предложения Кодда были настолько эффективны для систем баз данных, что за эту модель он был удостоен престижной премии Тьюринга в области теоретических основ вычислительной техники.
Основной структурой данных в модели является отношение, именно поэтому модель получила название реляционной (от английского relation - отношение). N - арным отношением R называют подмножество декартова произведения D1?D2? ... ?Dn множеств D1, D2, ..., Dn (n ? 1), необязательно различных. Исходные множества D1, D2, ..., Dn называют в модели доменами.
R ? D1?D2?...?Dn,
где D1?D2?...?Dn - полное декартово произведение.
Полное декартово произведение - это набор всевозможных сочетаний из п элементов каждое, где каждый элемент берется из своего домена. Например, имеем три домена: D1 содержит три фамилии, D2 - набор из двух учебных дисциплин и D3 - набор из трех оценок. Допустим, содержимое доменов следующее:
D1 = {Иванов, Крылов, Степанов};
D2 = {Теория автоматов, Базы данных};
D3 = {3, 4, 5}
Тогда полное декартово произведение содержит набор из 18 троек, где первый элемент - это одна из фамилий, второй - это название одной из учебных дисциплин, а третий - одна из оценок.
: : ; : ; : : ; ; : ; ; : : : : :
Отношение R моделирует реальную ситуацию и оно может содержать, допустим, только 5 строк, которые соответствуют результатам сессии (Крылов экзамен по "Базам данных" еще не сдавал):
: : ; ; ,
Отношение имеет простую графическую интерпретацию, оно может быть представлено в виде таблицы, столбцы которой соответствуют вхождениям доменов в отношение, а строки - наборам из п значений, взятых из исходных доменов, которые расположены в строго определенном порядке в соответствии с заголовком. Такие наборы из n значений часто называют n - ками.
| R | ||
| Фамилия | Дисциплина | Оценка |
| Иванов | Теория автоматов | 4 |
| Иванов | Базы данных | 3 |
| Крылов | Теория автоматов | 5 |
| Степанов | Теория автоматов | 5 |
| Степанов | Базы данных | 4 |
48
В таблице нет двух одинаковых строк.
Таблица имеет столбцы, соответствующие атрибутам отношения.
Каждый атрибут в отношении имеет уникальное имя.
Порядок строк в таблице произвольный.
Вхождение домена в отношение принято называть атрибутом. Строки отношения называются кортежами.
Количество атрибутов в отношении называется степенью, или рангом, отношения.
Следует заметить, что в отношении не может быть одинаковых кортежей, это следует из математической модели: отношение - это подмножество декартова произведения, а в декартовом произведении все n-ки различны.
В соответствии со свойствами отношений два отношения, отличающиеся только порядком строк или порядком столбцов, будут интерпретироваться в рамках реляционной модели как одинаковые, то есть отношение R и отношение R1, изображенное далее, одинаковы с точки зрения реляционной модели данных.
| R1 | ||
| Дисциплина | Фамилия | Оценка |
| Теория автоматов | Крылов | 5 |
| Теория автоматов | Степанов | 5 |
| Теория автоматов | Иванов | 4 |
| Базы данных | Иванов | 3 |
| Базы данных | Степанов | 4 |
Схемой отношения R называется перечень имен атрибутов данного отношения с указанием домена, к которому они относятся:
SR = (А1, А2, Аn), Аi ? Di.
Если атрибуты принимают значения из одного и того же домена, то они называются ? - сравнимыми, где ? - множество допустимых операций сравнения, заданных для данного домена. Например, если домен содержит числовые данные , то для него допустимы все операции сравнения, тогда ? = {=, < >,>=,}. Однако и для доменов, содержащих символьные данные, могут быть заданы не только операции сравнения по равенству и неравенству значений. Если для данного домена задано лексикографическое упорядочение, то он имеет также полный спектр операций сравнения.
Схемы двух отношений называются эквивалентными, если они имеют одинаковую степень и возможно такое упорядочение имен атрибутов в схемах, что на одинаковых местах будут находиться сравнимые атрибуты, то есть атрибуты, принимающие значения из одного домена.
49
SR1 = (A1, А2, ..., Аn) - схема отношения R1.
SR2 = (Bi1, Вi2,..., Вin) - схема отношения R2 после упорядочения имен атрибутов.
Тогда
| SR1 ? SR2 ? | { | 1. n = m |
| 2. Aj, Bij ? Dj. |
В отличие от теоретико - графовых моделей в реляционной модели связи между отношениями поддерживаются неявным образом. Какие же связи между отношениями поддерживаются в реляционной модели? В этой модели, так же как и в остальных, поддерживаются иерархические связи между отношениями. В каждой связи одно отношение может выступать как основное, а другое отношение выступает в роли подчиненного. Это означает, что один кортеж основного отношения может быть связан с несколькими кортежами подчиненного отношения. Для поддержки этих связей оба отношения должны содержать наборы атрибутов, но которым они связаны. В основном отношении это первичный ключ отношения (PRIMARY KEY), который однозначно определяет кортеж основного отношения. В подчиненном отношении для моделирования связи должен присутствовать набор атрибутов, соответствующий первичному ключу основного отношения. Однако здесь этот набор атрибутов уже является вторичным ключом, то есть он определяет множество кортежей подчиненного отношения, которые связаны с единственным кортежем основного отношения. Данный набор атрибутов в подчиненном отношении принято называть внешним ключом (FOREIGN KEY).
Например, рассмотрим ситуацию, когда надо описать карьеру некоторого индивидуума. Каждый человек в своей трудовой деятельности сменяет несколько мест работы в разных организациях, где он работает в разных должностях. Тогда мы должны создать два отношения: одно для моделирования всех работающих людей, а другое для моделирования записей в их трудовых книжках, если для нас важно не только отследить переход работника из одной организации в другую, но и прохождение его по служебной лестнице в рамках одной организации (рис. 4.1).
Рис. 4.1. Связь между основным и подчиненным отношениями
PRIMARY KEY отношения Сотрудник атрибут Паспорт является FOREIGN KEY для отношения "карьера".
Особенности встроенного SQL
При объединении операторов SQL с базовым языком программирования должны соблюдаться следующие принципы:
Операторы SQL включаются непосредственно в текст программы на исходном языке программирования. Исходная программа поступает на вход препроцессора SQL, который компилирует операторы SQL.
Встроенные операторы SQL могут ссылаться на переменные базового языка программирования.
Встроенные операторы SQL получают результаты SQL-запросов с помощью переменных базового языка программирования.
Для присвоения неопределенных значений (NULL) атрибутам отношений БД используются специальные функции.
Для обеспечения построчной обработки результатов запросов so встроенный SQL добавляются несколько новых операторов, которые отсутствуют в интерактивном SQL.
Операторы манипулирования данными не требуют изменения для их встраивания в программный SQL. Однако оператор поиска (SELECT) потребовал изменений.
Стандартный оператор SELECT возвращает набор данных, релевантный сформированным условиям запроса. В интерактивном SQL этот полученный набор данных просто выводится на консоль пользователя и он может просмотреть полученные результаты. Встроенный оператор SELECT должен создавать структуры
250
данных, которые согласуются с базовыми языками программирования. Во встроенном SQL запросы делятся на 2 типа:
Однострочные запросы, где ожидаемые результаты соответствуют одной строке данных. Эта строка может содержать значения нескольких столбцов.
Многострочные запросы, результатом которых является получение целого набора строк. При этом приложение должно иметь возможность проработать все полученные строки. Значит, должен существовать механизм, который поддерживает просмотр и обработку полученного набора строк.
Первый тип запроса - однострочный запрос во встроенном SQL вызвал модификацию оператора SQL, которая выглядит следующим образом:
SELECT [{ALL | DISTINCT}]
INTO
FROM
[WHERE ]
Мы видим, что во встроенный SELECT добавился новый для нас раздел, содержащий список переменных базового языка.
Именно в эти переменные будет помещен результат однострочного запроса, поэтому список переменных базового языка должен быть согласован как по порядку, так и по типу и размеру данных со списком возвращаемых столбцов. По правилам любого языка программирования все базовые переменные предварительно описаны в прикладной программе. Например, если в нашей БД "Библиотека" существует таблица READERS (Читатели), мы можем получить сведения о конкретном читателе.
CREATE TABLE READERS
(
READER_ID Smallint(4) PRIMARY KEY.
FIRST_NAME char(30) NOT NULL,
LAST_NAME char(30) NOT NULL,
ADRES char(50) .
HOME_PHON char(12) ,
WORK_PHON char(12) .
BIRTH_DAY date СНЕCK DateDiff(year. GetDate().BIRTH_DAY) >=17 )
):
Для этого опишем базовые переменные. Рассмотрим пример для MS SQL SERVER 7.0, используя язык Transact SQL. При описании локальных переменных в языке Transact SQL используется специальный символ @. Комментарии в Transact SQL заключены в парные символы /* комментарий */.
DECLARE @READER_ID int
DECLARE @FIRS_NAME Char(30). @LAST_NAME Char(30). @ADRES Char(50)
DECLARE @HOME_PHON Char(12), @WORK_PHON Char(12)
/* зададим уникальный номер читательского билета */
251
SET @READER_ID = 4
/* теперь выполним запрос и поместим полученные сведения в определенные ранее переменные */
SELECT READERS.FIRST_NAME, READERS.LAST_NAME. READERS.ADRES.
READERS.HOME_PHON. READERS.WORK_PHON
INTO @FIRS_NAME. @LAST_NAME. @ADRES, @HOME_PHON.@WORK_PHON
FROM READERS
WHERE READERS.READER_ID = @READER_ID
В этом простом примере мы имена переменных сделали такими же, как и имена столбцов таблицы READERS, но это необязательно. Однако транслятор различает эти объекты, именно поэтому в диалекте Transact SQL принято локальные переменные предварять специальным символом @. В примере мы использовали квалифицированные имена полей, имена полей, предваряемые именем таблицы. В нашем случае это тоже необязательно, потому что запрос выбирает данные только из одной таблицы.
В нашем примере базовые переменные играют разную роль.Локальная переменная @READER_ID является входной по отношению к запросу. Ей присвоено значение 4, и в запросе это значение используется для фильтрации данных, поэтому эта переменная используется в условии WHERE.
Остальные базовые переменные играют роль выходных переменных, в них СУБД помещает результат выполнения запроса, помещая в них значения соответствующих полей отношения READERS, извлеченные из БД.
От автора
Теория баз данных - сравнительно молодая область знаний. Возраст ее составляет немногим более 30 лет. Однако изменился ритм времени, оно уже не бежит, а летит, и мы вынуждены подчиняться ему во всем. Поэтому столь молодая область знаний является практически обязательной для изучения студентами всех технических специальностей. В соответствии с новыми стандартами учебная дисциплина "Базы данных" включена в стандарты всех специальностей, связанных с подготовкой специалистов по вычислительной технике: это группа специальностей 22.01, 22.02, 22.03 и 22.04. В остальные технические специальности раздел, посвященный базам данных, включен в общий курс информатики и вычислительной техники.
И действительно, современный мир информационных технологий трудно представить себе без использования баз данных. Практически все системы в той или иной степени связаны с функциями долговременного хранения и обработки информации. Фактически информация становится фактором, определяющим эффективность любой сферы деятельности. Увеличились информационные потоки и повысились требования к скорости обработки данных, и теперь уже большинство операций не может быть выполнено вручную, они требуют применения наиболее перспективных компьютерных технологий. Любые административные решения требуют четкой и точной оценки текущей ситуации и возможных перспектив ее изменения. И если раньше в оценке ситуации участвовало несколько десятков факторов, которые могли быть вычислены вручную, то теперь таких факторов сотни и сотни тысяч, и ситуация меняется не в течение года, а через несколько минут, а обоснованность принимаемых решений требуется большая, потому что и реакция на неправильные решения более серьезная, более быстрая и более мощная, чем раньше. И, конечно, обойтись без информационной модели производства, хранимой в базе данных, в этом случае невозможно.
Настоящее учебное пособие подготовлено по материалам лекционных курсов, посвященных основам теории баз данных, языку SQL и серверам баз данных, которые читались мною в течение 10 последних лет в Государственном Санкт-Петербургском университете аэрокосмического приборостроения студентам дневной формы обучения и слушателям курсов повышения квалификации, а также слушателям второго высшего образования в данном университете и в Государственном техническом университете (Политехническом институте).
В него включены разделы по распределенной обработке данных с использованием технологии "клиент - сервер", которые могут быть использованы для дальнейшего освоения современной теории и практики работы с базами данных.
Учебное пособие полностью соответствует требованиям стандарта по дисциплине "Базы данных" для всех вычислительных специальностей, а также для бакалавров по направлению 5528 "Информатика и вычислительная техника".
7
Пособие состоит из 14 глав. Оно может быть использовано для самостоятельного освоения курса "Базы данных" и подготовке к сдаче экзамена или как дополнение к лекционным курсам, читаемым в высших учебных заведениях.
Первая глава пособия посвящена истории возникновения области знаний, связанной с базами данных, в данной главе выделены основные этапы развития теории и практики баз данных, даются сравнительные характеристики этих этапов.
Вторая глава знакомит нас непосредственно с базами данных, здесь даются понятия и определения, являющиеся ключевыми для данной области знаний. Здесь рассматривается классическая трехуровневая архитектура, используемая в системах баз данных, упрощенный процесс прохождения запроса в базах данных и, наконец, приводится классификация моделей, используемых в системах баз данных.
Глава 3 посвящена первым теоретико - графовым моделям, которые использовались в ранних системах управления базами данных.
В главе 4 начинается обсуждение современной реляционной модели, которая является основой практически для всех коммерческих систем управления базами данных (СУБД) и наиболее распространена в настоящий момент. В этой же главе дается описание первого языка манипулирования данными, предложенного для данной модели ее создателем американским математиком Е.Ф. Коддом -реляционной алгебры.
Пятая глава полностью посвящена современному стандартному языку работы с базами данных, языку SQL. Язык SQL является сейчас стандартным базовым языком по работе с базами данных, любое собеседование, при приглашении вас на работу, связанную с информационными технологиями, включает решение нескольких запросов на языке SQL.
Мне кажется, что пятая глава данного пособия может помочь научиться писать правильные запросы на языке SQL самостоятельно. Мои студенты учились на примерах, которые приведены в данной главе и те из них, кто успешно решал приведенные примеры, также успешно сдали собеседования в ведущих зарубежных и отечественных фирмах, связанных с разработкой информационных систем. Я желаю и вам, мои читатели, таких же успехов.
Глава 6 посвящена вопросам проектирования баз данных, в ней рассматриваются базовые понятия функциональных и многозначных зависимостей между свойствами объектов, которые моделируются в базе данных. База данных - это фундамент Вашей будущей информационной системы и от того, как он будет построен, зависит во многом успех всей информационной системы, которая будет строиться на данном фундаменте. От корректности и продуманности структуры спроектированной базы данных зависит успех решения не только текущих информационных задач, но и перспективы развития и наращивания системы.
Глава 7 посвящена семантическим или инфологическим моделям, используемым в современных программных системах поддержки проектирования, называемых CASE-системами (Computer Aided Software Engineering).
Глава 8 посвящена принципам поддержки целостности в базах данных. Понятие целостности - одно из базовых понятий в современных базах данных. Принципы целостности составляют набор некоторых правил, которые выполняются автоматически при работе с данной базой данных. При грамотном составлении этих правил мы избавляем операторов, которые работают с базой данных, а также
8
разработчиков приложений от дополнительного контроля за правильностью и взаимосвязанностью вводимой в базу данных информации. Эти функции теперь выполняет сама СУБД, она контролирует вводимые и удаляемые данные, она не допускает ввода некорректной информации.
Глава 9 посвящена физическим моделям баз данных. В этом разделе описываются основные файловые конструкции, применяемые при создании баз данных.
Глава 10 посвящена вопросам распределенной обработки данных, здесь рассматриваются модели клиент - сервер, применяемые в системах баз данных.
Глава 11 посвящена понятию транзакции, которое является базовым при выполнении параллельных запросов к базам Данных. Рассматриваются две базовые модели транзакций: модель ANSI и расширенная модель транзакций, подробно рассматриваются проблемы, выполняемые при параллельном выполнении транзакций.
В главе 12 рассматриваются дополнительные возможности языка SQL , которые используются при разработке хранимых процедур и приложений, работающих с базами данных, а также принципы трансляции операторов языка SQL, порядок трансляции и выполнения SQL-запросов.
Глава 13 посвящена вопросам защиты информации в базах данных. Понятие защиты информации в базах данных чаще всего связано с концепцией защиты от несанкционированного доступа. В данной главе обсуждается общая концепция защиты информации, которая применяется в базах данных, вводится понятие пользователя и рассматриваются вопросы определения прав и привилегий пользователей по работе с отдельными объектами в базе данных.
Глава 14 посвящена рассмотрению обобщенной архитектуры современных баз данных, здесь рассматривается структура системных каталогов, основные функциональные блоки в современных серверах баз данных и их назначение. В этой главе обобщаются все сведения о базах данных, рассмотренные в предыдущих разделах.
В Заключении кратко характеризуются перспективы развития современных систем баз данных, дается перечень тех тем и вопросов, которые рекомендуется рассмотреть для дальнейшего более глубокого знакомства с теорией и практикой баз данных.
В заключение мне бы хотелось поблагодарить тех, кто помог мне написать данную книгу. Прежде всего, это мои многочисленные студенты, которые решали многие задачи, задавали множество вопросов и постоянно будоражили меня, заставляя придумывать новые рисунки и способы объяснения запутанных понятий, упрощать наиболее сложные моменты изложения, делая их доступными и ясными. Однако переход от многолетнего лекционного материала к написанию на его основе учебного пособия оказался весьма непростым для меня, и здесь я благодарна сотрудникам редакции компьютерной литературы издательства "Питер", особенно Екатерине Вячеславовне Строгановой и Илье Александровичу Корнееву.Только их терпение, доброжелательность и настойчивость позволили мне завершить мой труд. И, конечно, я благодарна моим домашним: маме и дочке, которые весь период написания книги терпели мое плохое настроение, недовольство собой и текстом и полное устранение от домашних дел.
Параллельное выполнение транзакций
Если с БД работают одновременно несколько пользователей, то обработка транзакций должна рассматриваться с новой точки зрения. В этом случае СУБД должна не только корректно выполнять индивидуальные транзакции и восстанавливать согласованное состояние БД после сбоев, но она призвана обеспечить корректную параллельную работу всех пользователей над одними и теми же данными. По теории каждый пользователь и каждая транзакция должны обладать свойством изолированности, то есть они должны выполняться так, как если бы только один пользователь работал с БД. И средства современных СУБД позволяют изолировать пользователей друг от друга именно таким образом. Однако в этом случае возникают проблемы замедления работы пользователей. Рассмотрим более подробно проблемы, которые возникают при параллельной обработке транзакций.
Основные проблемы, которые возникают при параллельном выполнении транзакций, делятся условно на 4 типа:
Пропавшие изменения. Эта ситуация может возникать, если две транзакции одновременно изменяют одну и ту же запись в БД. Например, работают два оператора на приеме заказов, первый оператор принял заказ на 30 мониторов. Когда он запрашивал склад, то там числилось 40 мониторов, и он, получив подтверждение от клиента, выставил счет и оформил продажу 30 мониторов из 40 Параллельно с ним работает второй оператор, который принимает заказ на 20 таких же мониторов (ну уж очень хорошая модель и дешево) и, в свою очередь запросив состояние склада и получив исходно ту же цифру 40, он успешно оформляет заказ для своего клиента. Заканчивая работу с данным заказом, он выполняет команду Обновить (UPDATE), которая заносит 20 как остаток любимых мониторов на складе. Но после этого, наконец, любезно попрощавшись со своим клиентом и заверив его в скорейшей доставке заказанных мониторов, заканчивает работу со своим заказом первый оператор и также выполняет команду Обновить и заносит 10 как остаток тех же мониторов на складе. Каждый из них доволен своей работой, но мы-то знаем, что произошло.
Прежде всего, они продали 50 мониторов из наличествующих 40 штук, и далее на складе еще числится 10 подобных мониторов. БД теперь находится в несогласованном состоянии, а у фирмы возникли серьезные проблемы. Изменения, сделанные вторым оператором, были проигнорированы программой выполнения заказа, с которой работал первый оператор. Подобная ситуация представлена на рис. 11.5.
Проблемы промежуточных данных. Рассмотрим ту же проблему одновременной работы двух операторов. Допустим, первый оператор, ведя переговоры со своим заказчиком, ввел заказанные 30 мониторов, но перед окончательным оформлением заказа клиент захотел выяснить еще некоторые характеристики товара. Приложение, с которым работает первый оператор, уже изменило остаток мониторов на складе, и там сейчас находится информация о 10 оставшихся мониторах. В это время второй оператор пытается принять
231
заказ от своего клиента на 20 мониторов, но его приложение показывает, что на складе осталось всего 10 мониторов, и оператор вынужден отказать выгодному клиенту, который идет в другую фирму, весьма неудовлетворенный работой нашей компании. А в этот момент клиент оператора 1 заканчивает обсуждение дополнительных характеристик наших мониторов и принимает весьма невыгодное решение не покупать у нас мониторы, и приложение оператора 1 выполняет откат транзакции, и на складе снова оказывается 40 мониторов. Мы потеряли выгодного заказчика, но еще хуже было бы, если бы клиент второго оператора согласился на 10 оставшихся мониторов, и приложение, с которым работает оператор два, отработав свой алгоритм, занесло 0 (ноль) оставшихся мониторов на складе, а после этого приложение оператора один снова бы записало исходные 40 мониторов на складе, хотя 10 их них уже проданы. Такая ситуация оказалась возможной потому, что приложение второго оператора имело доступ к промежуточным данным, которые сформировало первое приложение.
Рис. 11.5. Проблема пропавших обновлений
Проблемы несогласованных данных. Рассмотрим ту же самую ситуацию с заказом мониторов.
Предположим, что ситуация несколько изменилась. И оба оператора начинают работать практически одновременно. Они оба получают начальное состояние склада 40 мониторов, а далее первый оператор успешно завершает переговоры со своим клиентом и продает ему 30 мониторов. Он завершает работу своего приложения, и оно выполняет команду фиксации транзакции COMMIT. Состояние базы данных непротиворечивое. В этот момент, выяснив все тонкости и характеристики наших мониторов, клиент второго оператора также решает сделать заказ, и второй оператор, повторно получая
232
состояние склада, видит, что оно изменилось. База данных находится в непротиворечивом состоянии, но второй оператор считает, что нарушена целостность его транзакции, в течение выполнения одной работы он получил два различных состояния склада. Эта ситуация возникла потому, что приложение первого оператора смогло изменить кортеж с данными, который уже прочитало приложение второго оператора.
Проблемы строк-призраков (строк-фантомов). Предположим, что администратор нашей фирмы поручил секретарю напечатать итоговый отчет по результатам работы за текущий месяц. И допустим, что приложение печатает отчет в двух видах: в подробном и в укрупненном. В момент, когда приложение печати начало формировать свой первый вид отчета, один из операторов принимает еще один заказ, поэтому к моменту формирования укрупненного отчета в БД появились новые сведения о продажах, которые и были внесены в укрупненный отчет. Мы получили два отчета в одном приложении, которые содержат разные цифры и не совпадают друг с другом. Такое стало возможно потому, что приложение печати выполнило два одинаковых запроса и получило два разных результата. БД находится в согласованном состоянии, но приложение печати работает некорректно.
Для того чтобы избежать подобных проблем, требуется выработать некоторую процедуру согласованного выполнения параллельных транзакций. Эта процедура должна удовлетворять следующим правилам:
В ходе выполнения транзакции пользователь видит только согласованные данные.
Пользователь не должен видеть несогласованных промежуточных данных.
Когда в БД две транзакции выполняются параллельно, то СУБД гарантированно поддерживает принцип независимого выполнения транзакций, который гласит, что результаты выполнения транзакций будут такими же, как если бы вначале выполнялась транзакция 1, а потом транзакция 2, или наоборот, сначала транзакция 2, а потом транзакция 1.
Такая процедура называется сериализацией транзакций. Фактически она гарантирует, что каждый пользователь (программа), обращающаяся к базе данных, работает с ней так, как будто не существует других пользователей (программ), одновременно с ним обращающихся к тем же данным.
Для поддержки параллельной работы транзакций строится специальный план.
План (способ) выполнения набора транзакций называется сериальным, если результат совместного выполнения транзакций эквивалентен результату некоторого последовательного выполнения этих же транзакций.
Самым простым было бы последовательное выполнение транзакций, но такой план не оптимален по времени, существуют более гибкие методы управления параллельным доступом к БД. Наиболее распространенным механизмом, который используется коммерческими СУБД для реализации на практике сериализации транзакций является механизм блокировок. Самый простой вариант -это блокировка объекта на все время действия транзакции. Подобный пример рассмотрен на рис. 11.6. Здесь две транзакции, названные условно А и В, работают
233
с тремя таблицами: T1, T2 и Т3. В момент начала работы "с любым объектом этот объект блокируется транзакцией, которая с ним начала работу, и он становится недоступным всем другим транзакциям до окончания транзакции, заблокировавшей ("захватившей") данный объект. После окончания транзакции все заблокированные ею объекты разблокируются и становятся доступными другим транзакциям. Если транзакция обращается к заблокированному объекту, то она остается в состоянии ожидания до момента разблокировки этого объекта, после чего она может продолжать обработку данного объекта.
Поэтому транзакция В ожидает разблокировки таблицы Т2 транзакцией А. Над прямоугольниками стоит условное время выполнения операций.
Рис. 11.6.Блокировки при одновременном выполнении двух транзакций
В общем случае на момент выполнения транзакция получает как бы монопольный доступ к объектам БД, с которыми она работает. В этом случае другие транзакции не получают доступа к объектам БД до момента окончания транзакции. Такой механизм действительно ликвидирует все перечисленные ранее проблемы: пропавшие изменений, неподтвержденные данные, несогласованные данные, строки - фантомы. Однако такая блокировка создает новые проблемы - задержку выполнения транзакций из-за блокировок.
Рассмотрим существующие типы конфликтов между двумя параллельными транзакциями. Можно выделить следующие типы:
234
W-W - транзакция 2 пытается изменять объект, измененный незакончившейся транзакцией 1;
R-W - транзакция 2 пытается изменять объект, прочитанный незакончившейся транзакцией 1;
W-R - транзакция 2 пытается читать объект, измененный незакончившейся транзакцией 1.
Практические методы сериализации транзакций основываются на учете этих конфликтов.
Блокировки, называемые также синхронизационными захватами объектов, могут быть применены к разному типу объектов. Наибольшим объектом блокировки может быть вся БД, однако этот вид блокировки сделает БД недоступной для всех приложений, которые работают с данной БД. Следующий тип объекта блокировки - это таблицы. Транзакция, которая работает с таблицей, блокирует ее на все время выполнения транзакции. Именно такой вид блокировки рассмотрен в примере 11.7. Этот вид блокировки предпочтительнее предыдущего, потому что позволяет параллельно выполнять транзакции, которые работают с другими таблицами.
В ряде СУБД реализована блокировка на уровне страниц. В этом случае СУБД блокирует только отдельные страницы на диске, когда транзакция обращается к ним. Этот вид блокировки еще более мягок и позволяет разным транзакциям работать даже с одной и той же таблицей, если они обращаются к разным страницам данных.
В некоторых СУБД возможна блокировка на уровне строк, однако такой механизм блокировки требует дополнительных затрат на поддержку этого вида блокировки.
В настоящее время проблема блокировок является предметом большого числа исследований.
Для повышения параллельности выполнения транзакций используется комбинирование разных типов синхронизационных захватов.
Рассматривают два типа блокировок (синхронизационных захватов):
совместный режим блокировки - нежесткая, или разделяемая, блокировка, обозначаемая как S (Shared). Этот режим обозначает разделяемый захват объекта и требуется для выполнения операции чтения объекта. Объекты, заблокированные таким образом, не изменяются в ходе выполнения транзакции и доступны другим транзакциям также, но только в режиме чтения;
монопольный режим блокировки - жесткая, или эксклюзивная, блокировка, обозначаемая как X (exclusive). Данный режим блокировки предполагает монопольный захват объекта и требуется для выполнения операций занесения, удаления и модификации. Объекты, заблокированные данным типом блокировки, фактически остаются в монопольном режиме обработки и недоступны для других транзакций до момента окончания работы данной транзакции.
Захваты объектов несколькими транзакциями по чтению совместимы, то есть нескольким транзакциям допускается читать один и тот же объект, захват объекта
235
одной транзакцией по чтению не совместим с захватом другой транзакцией того же объекта по записи, и захваты одного объекта разными транзакциями по записи не совместимы. Правила совместимости захватов одного объекта разными транзакциями изображены на рис. 11.7:
Рис. 11.8. Использование жесткой и нежесткой блокировки
В примере, представленном на рис. 11.7 считается, что первой блокирует объект транзакция А, а потом пытается получить к нему доступ транзакция В.
На рис. 11.8 приведен ранее рассмотренный пример с выполнением транзакций 1 и 2, но с учетом разных типов блокировки. На рисунке видно, что, применив нежесткую блокировку к таблице 2 со стороны транзакции 1, мы обеспечили существенное уменьшение времени выполнения транзакции 2.
Теперь транзакция 2 не ждет окончания транзакции 1, и поэтому завершает свою работу намного раньше.
Рис. 11.7. Правила применения жесткой и нежесткой блокировок транзакций
236
К сожалению, применения разных типов блокировок приводит к проблеме тупиков. Эта проблема не нова. Проблема тупиков возникла при рассмотрении выполнения параллельных процессов в операционных средах и также была связана с управлением разделяемыми (совместно используемыми) ресурсами.
Действительно, рассмотрим пример. Пусть транзакция А сначала жестко блокирует таблицу 1, а потом жестко блокирует таблицу 2. Транзакция В, наоборот, сначала жестко блокирует таблицу 2, а потом жестко блокирует таблицу 1. Если обе эти транзакции начали работу одновременно, то после выполнения операций модификации первыми объектами каждой транзакции они обе окажутся в бесконечном ожидании, транзакция А будет ждать завершения работы транзакции В и разблокировки таблицы 2, а транзакция В также безрезультатно будет ждать окончания работы транзакции А и разблокировки таблицы 1 (см. рис. 11.9).
Рис. 11.9. Взаимная блокировка транзакций
Ситуации могут быть гораздо более сложными. Количество взаимно заблокированных транзакций может оказаться гораздо больше. Эту ситуацию каждая из транзакций обнаружить самостоятельно не может, Ее должна разрешить СУБД. И действительно, в большинстве коммерческих СУБД существует механизм обнаружения таких тупиковых ситуаций.
Основой обнаружения тупиковых ситуаций является построение (или постоянное поддержание) графа ожидания транзакций. Граф ожидания транзакций может строиться двумя способами. В книге К. Дж. Дейта граф ожидания - это, направленный граф, в вершинах которого расположены имена транзакций. Если транзакция А ждет окончания транзакции В, то из вершины А в вершину В идет стрелка. Дополнительно стрелки могут быть помечены именами заблокированных объектов и типом блокировки. Пример такого графа ожиданий приведен на рис. 11.10.
Этот граф ожиданий построен для транзакций Т1, Т2,...,Т12, которые работают с объектами БД А,В,...,Н.
Перечень действий, которые совершают транзакции над объектами, приведен в табл. 11.1.
237
Рис. 11.10. Пример графа ожиданий транзакций
Таблица 11.1. Перечень действий множества транзакций
| Время | Транзакция | Действие |
| 0 | Т1 | Select A |
| 1 | Т2 | Select В |
| 2 | Т1 | Select С |
| 3 | Т4 | Select D |
| 4 | Т5 | Select A |
| 5 | Т2 | Select E |
| 6 | Т2 | Update E |
| 7 | Т3 | Select F |
| 8 | Т2 | Select F |
| 9 | Т5 | Update A |
| 10 | T7 | Commit |
| 11 | Т6 | Select A |
| 12 | Т5 | Commit |
| 13 | Т6 | Select С |
| 14 | Т6 | Update С |
| 15 | Т7 | Select G |
| 16 | Т8 | Select H |
| 17 | Т9 | Select G |
| 18 | Т9 | Update G |
| Время | Транзакция | Действие |
| 19 | Т8 | Select E |
| 20 | Т7 | Commit |
| 21 | Т9 | Select H |
| 22 | Т3 | Select G |
| 23 | Т10 | Select A |
| 24 | T9 | Update H |
| 25 | Т6 | Commit |
| 26 | T11 | Select С |
| 27 | Т12 | Select D |
| 28 | Т12 | Select С |
| 29 | Т2 | Update F |
| 30 | Т11 | Update С |
| 31 | Т12 | Select A |
| 32 | Т10 | Update A |
| 33 | Т12 | Update D |
| 34 | Т2 | Select G |
| 35 | - | - |
На диаграмме состояний ожидания видно, что транзакции Т9, Т8, Т2 и Т3 образуют цикл. Именно наличие цикла и является признаком возникновения тупиковой ситуации. Поэтому в момент 3 перечисленные транзакции будут заблокированы.
Разрушение тупика начинается с выбора в цикле транзакций так называемой транзакции-жертвы, то есть транзакции, которой решено пожертвовать, чтобы обеспечить возможность продолжения работы других транзакций.
Критерием выбора является стоимость транзакции; жертвой выбирается самая дешевая транзакция. Стоимость транзакции определяется на основе многофакторной оценки, в которую с разными весами входят время выполнения, число накопленных захватов, приоритет.
После выбора транзакции - жертвы выполняется откат этой транзакции, который может носить полный или частичный характер. При этом, естественно, освобождаются захваты и может быть продолжено выполнение других транзакций.
В лекциях профессора С. Д. Кузнецова приводится несколько иной принцип, построения графа ожидания. В этом случае граф ожидания транзакций строится в виде ориентированного двудольного графа, в котором существует два типа вершин - вершины, соответствующие транзакциям, и вершины, соответствующие
239
объектам захвата. В этом графе существует дуга, ведущая из вершины-транзакции к вершине - объекту, если для этой транзакции существует удовлетворенный захват объекта. В графе существует дуга из вершины - объекта к вершине - транзакции, если транзакция ожидает удовлетворения захвата объекта.
Для распознавания тупика здесь, так же как и в первом методе, производится построение графа ожидания транзакций и в этом графе ищутся циклы. Традиционной техникой (для которой существует множество разновидностей) нахождения циклов в ориентированном графе является редукция графа.
Не вдаваясь в детали, редукция состоит в том, что прежде всего из графа ожидания удаляются все дуги, исходящие из вершин-транзакций, в которые не входят дуги из вершин-объектов. (Это как бы соответствует той ситуации, что транзакции, не ожидающие удовлетворения захватов, успешно завершились и освободили захваты.) Для тех вершин-объектов, для которых не осталось входящих дуг, но существуют исходящие, ориентация исходящих дуг изменяется на противоположную (это моделирует удовлетворение захватов). После этого снова срабатывает первый шаг, и так до тех пор, пока на первом шаге не прекратится удаление дуг. Если в графе остались дуги, то они обязательно образуют цикл.
Естественно, такое насильственное устранение тупиковых ситуаций является нарушением принципа изолированности пользователей.
Заметим, что в централизованных системах стоимость построения графа ожидания сравнительно невелика, но она становится слишком большой в по-настоящему распределенных СУБД, в которых транзакции могут выполняться в разных узлах сети. Поэтому в таких системах обычно используются другие методы сериализации транзакций.
Для обеспечения сериализации транзакций синхронизационные захваты объектов, произведенные по инициативе транзакции, можно снимать только при ее завершении.
Это требование порождает двухфазный протокол синхронизационных захватов - 2PL(two phase lock) или 2PC (two phase commit). В соответствии с этим протоколом выполнение транзакции разбивается на две фазы:
первая фаза транзакции - накопление захватов;
вторая фаза (фиксация или откат) - освобождение захватов.
В языке SQL введен оператор явной блокировки таблицы, который позволяет точно задать тип блокировки для всей таблицы. Синтаксис операции блокировки имеет вид:
LOCK TABLE имя_таблицы IN {SHARED | EXCLUSIVE} MODE
Имеет смысл блокировать таблицу полностью, когда выполняется операция множественной модификации одной таблицы, то есть когда в ней изменяется большое количество строк. Эта операция иногда называется пакетным обновлением.
Конечно, у блокировки таблицы есть тот недостаток, что все остальные транзакции должны ждать окончания обновления таблицы. Но режим пакетного обновления одной таблицы работает достаточно быстро, и общая производительность выполнения множества транзакций может даже повыситься в этом случае.
Переход к реляционной модели данных
Инфологическая модель используется на ранних стадиях разработки проекта. Если понимать язык условных обозначений, которые соответствуют категориям ER - модели, то ее можно легко "читать", следовательно, она доступна для анализа программистам-разработчикам, которые будут разрабатывать отдельные приложения. Она имеет однозначную интерпретацию, в отличие от некоторых предложений естественного языка, и поэтому здесь не может быть никакого недопонимания со стороны разработчиков.
Все специалисты всегда предпочитают выражать свои мысли на некотором формальном языке, который обеспечивает однозначную их трактовку. Таким языком для программистов раньше был язык алгоритмов. Любой алгоритм имел однозначную интерпретацию. Он мог быть реализован на разных языках программирования, но сам алгоритм был и оставался одним и тем же. В первые годы развития вычислительной техники широко издавались сборники алгоритмов для широко распространенных математических задач. Эти сборники программистами прочитывались как увлекательные детективные романы, и они все настоящим программистам были понятны, хотя специалисты других профилей смотрели на эти сборники как на издания на иностранных, неведомых им, языках. Для описания алгоритмов могли использоваться разные формализмы. Одним из таких формализмов был метаязык, в котором использовались слова на естественном языке и каждый мог прочесть эти слова, но смысл самого алгоритма мог понять только тот, кто владел знаниями трактовки алгоритмов.
Вот таким условным общепринятым языком описания базы данных и стал язык ER-модели. Для ER-модели существует алгоритм однозначного преобразования ее в реляционную модель данных, что позволило в дальнейшем разработать множество инструментальных систем, поддерживающих процесс разработки информационных систем, базирующихся на технологии баз данных. И во всех этих системах существуют средства описания инфологической модели разрабатываемой БД с возможностью автоматической генерации той даталогической модели, на которой будет реализовываться проект в дальнейшем.
Рассмотрим правила преобразования ER - модели в реляционную.
1. Каждой сущности ставится в соответствие отношение реляционной модели данных. При этом имена сущности и отношения могут быть различными, потому что на имена сущностей могут не накладываться дополнительные синтаксические ограничения, кроме уникальности имени в рамках модели. Имена отношений могут быть ограничены требованиями конкретной СУБД, чаще всего эти имена являются идентификаторами в некотором базовом языке, они ограничены по длине и не должны содержать пробелов и некоторых специальных символов. Например, сущность может быть названа "Книжный каталог", а соответствующее ей отношение желательно назвать, например, BOOKS (без пробелов и латинскими буквами).
2. Каждый атрибут сущности становится атрибутом соответствующего отношения. Переименование атрибутов должно происходить в соответствии с теми
129
же правилами, что и переименование отношений в п.1. Для каждого атрибута задается конкретный допустимый в СУБД тип данных и обязательность или необязательность данного атрибута (то есть допустимость или недопустимость NULL значений для него).
Рис. 7.7. Преобразование сущности СОТРУДНИК к отношению EMPLOYEE
3. Первичный ключ сущности становится PRIMARY KEY соответствующего отношения. Атрибуты, входящие в первичный ключ отношения, автоматически получают свойство обязательности (NOT NULL).
Рис. 7.8. Свойства атрибутов отношения EMPLOYEE
4. В каждое отношение, соответствующее подчиненной сущности, добавляется набор атрибутов основной сущности, являющейся первичным ключом основной сущности. В отношении, соответствующем подчиненной сущности, этот набор атрибутов становится внешним ключом (FOREING KEY).
5. Для моделирования необязательного типа связи на физическом уровне у атрибутов, соответствующих внешнему ключу, устанавливается свойство допустимости неопределенных значений (признак NULL). При обязательном типе связи атрибуты получают свойство отсутствия неопределенных значений (признак NOT NULL).
6. Для отражения категоризации сущностей при переходе к реляционной модели возможны несколько вариантов представления. Возможно создать только одно отношение для всех подтипов одного супертипа. В него включают все атрибуты всех подтипов. Однако тогда для ряда экземпляров ряд атрибутов не будет иметь смысла. И даже если они будут иметь неопределенные значения, то потребуются дополнительные правила различения одних подтипов от других. Достоинством такого представления является то, что создается всего одно отношение.
130
Рис. 7.9. Преобразование взаимосвязанных сущностей СТУДЕНТ и ПРЕПОДАВАТЕЛЬ к взаимосвязанным отношениям STUDENT и PROFESSOR
7. При втором способе для каждого подтипа и для супертипа создаются свои отдельные отношения. Недостатком такого способа представления является то, что создается много отношений, однако достоинств у такого способа больше, так как вы работаете только со значимыми атрибутами подтипа. Кроме того, для возможности переходов к подтипам от супертипа необходимо в супертип включить идентификатор связи.
8. Дополнительно при описании отношения между типом и подтипами необходимо указать тип дискриминатора. Дискриминатор может быть взаимоисключающим (М/Е, mutually exclusive ) или нет. Если установлен данный тип дискриминатора, то это значит, что один экземпляр сущности супертипа связан только с одним экземпляром сущности подтипа и для каждого экземпляра сущности супертипа существует потомок. Кроме того, необходимо указать для второго способа, наследуется ли только идентификатор супертипа в подтипы или наследуются все атрибуты супертипа.
9. Если мы зададим наследование только идентификатора, то мы получим следующее преобразование (см. рис. 7.10 и 7.11).
131
Рис. 7.10. Исходная модель взаимосвязи супертипа и подтипов
Рис.7.11. Результирующая модель с наследованием только идентификатора суперсущности
Разрешение связей типа "многие - ко - многим". Так как в реляционной модели данных поддерживаются между отношениями только связи типа "один - ко - многим", а в ER - модели допустимы связи "многие - ко - многим", то необходим специальный механизм преобразования, который позволит отразить множественные связи, неспецифические для реляционной модели, с помощью допустимых для нее категорий.
Это делается введением специального дополнительного связующего отношения, которое связано с каждым исходным связью "один - ко - многим", атрибутами этого отношения являются первичные ключи связываемых отношений. Так, например, в схеме "Библиотека" присутствует связь такого типа между сущностью "Книги" и "Системный каталог". Для разрешения этой неспецифической связи при переходе к реляционной модели должно быть введено специальное дополнительное отношение, которое имеет всего два атрибута: ISBN (шифр книги) и KOD (код области знаний). При этом каждый из атрибутов нового отношения является внешним ключом (FORKING KEY), а вместе они образуют первичный ключ (PRIMARY KEY) новой связующей сущности. На рис. 7.12 представлена реляционная модель, соответствующая представленной ранее на рис. 7.6 инфологической модели "Библиотека".
Теория нормализации, которую мы рассматривали ранее применительно к реляционной модели, применима и к модели "сущность - связь". Поэтому нормализацию можно проводить и на уровне инфологической (семантической) модели
132
и смысл ее аналогичен нормализации реляционной модели. Алгоритм приведения семантической модели к 5-й нормальной форме может быть следующим:
Рис. 7.12. Результирующая модель с наследованием всех атрибутов суперсущности
Шаг 1. Проанализировать схему на присутствие сущностей, которые скрыто моделируют несколько разных взаимосвязанных классов объектов реального мира (именно это соответствует ненормализованным отношениям). Если такое выявлено, то разделить каждую из этих сущностей на несколько новых сущностей и установить между ними соответствующие связи, полученная схема будет находиться в первой нормальной форме. Перейти к шагу 2.
Шаг 2. Проанализировать все сущности, имеющие составные первичные ключи, на наличие неполных функциональных зависимостей непервичных атрибутов от атрибутов возможного ключа. Если такие зависимости обнаружены, то разделить данные сущности на 2, определить для каждой сущности первичные ключи и установить между ними соответствующие связи.
Полученная схема будет находиться во второй нормальной форме. Перейти к шагу 3.
Шаг 3. Проанализировать неключевые атрибуты всех сущностей на наличие транзитивных функциональных зависимостей. При обнаружении таковых расщепить каждую сущность на несколько таким образом, чтобы ликвидировать транзитивные зависимости. Схема находится в третьей нормальной форме. Перейти к шагу 4.
Шаг 4. Проанализировать все сущности на наличие детерминантов, которые не являются возможными ключами. При обнаружении подобных расщепить сущность на две, установив между ними соответствующие связи. Полученная схема соответствует нормальной форме Бойса - Кодда. Перейти к шагу 5.
Шаг 5. Проанализировать все сущности на наличие многозначных зависимостей. Если обнаружатся сущности, у которых имеется более одной многозначной зависимости, то расщепить такие сущности на две, установив между ними соответствующие связи. Полученная схема будет находиться в четвертой нормальной форме. Перейти к шагу 6.
133
Рис. 7.13. Реляционная схема "Библиотека"
Шаг 6. Проанализировать сущности на наличие в них зависимостей проекции-соединения. При обнаружении таковых расщепить сущность на требуемое число взаимосвязанных сущностей и установить между ними требуемые связи. Полученная таким образом схема будет находиться в пятой нормальной форме и, будучи формально преобразованной к реляционной схеме по указанным выше принципам, даст реляционную схему также в пятой нормальной форме.
Перспективы развития систем управления базами данных
Этот этап характеризуется появлением новой технологии доступа к данным -интранет. Основное отличие этого подхода от технологии клиент-сервер состоит в том, что отпадает необходимость использования специализированного клиентского программного обеспечения. Для работы с удаленной базой данных используется стандартный броузер Интернета, например Microsoft Internet Explorer или Netscape Navigator, и для конечного пользователя процесс обращения к данным происходит аналогично скольжению по Всемирной Паутине (см. рис. 1.1). При этом встроенный в загружаемые пользователем HTML-страницы код, написанный обычно на языке Java, Java-script, Perl и других, отслеживает все действия пользователя и транслирует их в низкоуровневые SQL-запросы к базе данных, выполняя, таким образом, ту работу, которой в технологии клиент-сервер занимается клиентская программа. Удобство данного подхода привело к тому, что он стал использоваться не только для удаленного доступа к базам данных, но и для пользователей локальной сети предприятия. Простые задачи обработки данных, не связанные со сложными алгоритмами, требующими согласованного изменения данных во многих взаимосвязанных объектах, достаточно просто и эффективно могут быть построены по данной архитектуре. В этом случае для подключения нового пользователя к возможности использовать данную задачу не требуется установка дополнительного клиентского программного обеспечения. Однако алгоритмически сложные задачи рекомендуется реализовывать в архитектуре "клиент-сервер" с разработкой специального клиентского программного обеспечения.
У каждого из вышеперечисленных подходов к работе с данными есть свои достоинства и свои недостатки, которые и определяют область применения того или иного метода, и в настоящее время все подходы широко используются.
18
Рис. 1.1. Взаимодействие с базой данных в технологии интранет
19
18 :: 19 :: Содержание
Первый этап - базы данных на больших ЭВМ
История развития СУБД насчитывает более 30 лет. В 1968 году была введена в эксплуатацию первая промышленная СУБД система IMS фирмы IBM. В 1975 году появился первый стандарт ассоциации по языкам систем обработки данных - Conference of Data System Languages (CODASYL), который определил ряд фундаментальных понятий в теории систем баз данных, которые и до сих пор являются основополагающими для сетевой модели данных.
В дальнейшее развитие теории баз данных большой вклад был сделан американским математиком Э. Ф. Коддом, который является создателем реляционной
13
модели данных. В 1981 году Э. Ф. Кодд получил за создание реляционной модели и реляционной алгебры престижную премию Тьюринга Американской ассоциации по вычислительной технике.
Менее двух десятков лет прошло с этого момента, но стремительное развитие вычислительной техники, изменение ее принципиальной роли в жизни общества, обрушившийся бум персональных ЭВМ и, наконец, появление мощных рабочих станций и сетей ЭВМ повлияло также и на развитие технологии баз данных. Можно выделить четыре этапа в развитии данного направления в обработке данных. Однако необходимо заметить, что все же нет жестких временных ограничений в этих этапах: они плавно переходят один в другой и даже сосуществуют параллельно, но тем не менее выделение этих этапов позволит более четко охарактеризовать отдельные стадии развития технологии баз данных, подчеркнуть особенности, специфичные для конкретного этапа.
Первый этап развития СУБД связан с организацией баз данных на больших машинах типа IBM 360/370, ЕС-ЭВМ и мини-ЭВМ типа PDP11 (фирмы Digital Equipment Corporation - DEC), разных моделях HP (фирмы Hewlett Packard).
Базы данных хранились во внешней памяти центральной ЭВМ, пользователями этих баз данных были задачи, запускаемые в основном в пакетном режиме. Интерактивный режим доступа обеспечивался с помощью консольных терминалов, которые не обладали собственными вычислительными ресурсами (процессором, внешней памятью) и служили только устройствами ввода-вывода для центральной ЭВМ.
Программы доступа к БД писались на различных языках и запускались как обычные числовые программы. Мощные операционные системы обеспечивали возможность условно параллельного выполнения всего множества задач. Эти системы можно было отнести к системам распределенного доступа, потому что база данных была централизованной, хранилась на устройствах внешней памяти одной центральной ЭВМ, а доступ к ней поддерживался от многих пользователей-задач.
Особенности этого этапа развития выражаются в следующем:
Все СУБД базируются на мощных мультипрограммных операционных системах (MVS, SVM, RTE, OSRV, RSX, UNIX), поэтому в основном поддерживается работа с централизованной базой данных в режиме распределенного доступа.
Функции управления распределением ресурсов в основном осуществляются операционной системой (ОС).
Поддерживаются языки низкого уровня манипулирования данными, ориентированные на навигационные методы доступа к данным.
Значительная роль отводится администрированию данных.
Проводятся серьезные работы по обоснованию и формализации реляционной модели данных, и была создана первая система (System R), реализующая идеологию реляционной модели данных.
Проводятся теоретические работы по оптимизации запросов и управлению распределенным доступом к централизованной БД, было введено понятие транзакции.
14
Результаты научных исследований открыто обсуждаются в печати, идет мощный поток общедоступных публикаций, касающихся всех аспектов теории и практики баз данных, и результаты теоретических исследований активно внедряются в коммерческие СУБД.
Появляются первые языки высокого уровня для работы с реляционной моделью данных. Однако отсутствуют стандарты для этих первых языков.
Пользователи банков данных
Как любой программно-организационно-технический комплекс, банк данных существует во времени и в пространстве. Он имеет определенные стадии своего развития:
23
Проектирование.
Реализация.
Эксплуатация;
Модернизация и развитие.
Полная реорганизация.
На каждом этапе своего существования с банком данных связаны разные категории пользователей.
Определим основные категории пользователей и их роль в функционировании банка данных:
Конечные пользователи. Это основная категория пользователей, в интересах которых и создается банк данных. В зависимости от особенностей создаваемого банка данных круг его конечных пользователей может существенно различаться. Это могут быть случайные пользователи, обращающиеся к БД время от времени за получением некоторой информации, а могут быть регулярные пользователи. В качестве случайных пользователей могут рассматриваться, например, возможные клиенты вашей фирмы, просматривающие каталог вашей продукции или услуг с обобщенным или подробным описанием того и другого. Регулярными пользователями могут быть ваши сотрудники, работающие со специально разработанными для них программами, 'которые обеспечивают автоматизацию их деятельности при выполнении своих должностных обязанностей. Например, менеджер, планирующий работу сервисного отдела компьютерной фирмы, имеет в своем распоряжении программу, которая помогает ему планировать и распределять текущие заказы, контролировать ход их выполнения, заказывать на складе необходимые комплектующие для новых заказов. Главный принцип состоит в том, что от конечных пользователей не должно требоваться каких-либо специальных знаний в области вычислительной техники и языковых средств.
Администраторы банка данных. Это группа пользователей, которая на начальной стадии разработки банка данных отвечает за его оптимальную организацию с точки зрения одновременной работы множества конечных пользователей, на стадии эксплуатации отвечает за корректность работы данного банка информации в многопользовательском режиме.
На стадии развития и реорганизации эта группа пользователей отвечает за возможность корректной реорганизации банка без изменения или прекращения его текущей эксплуатации.
Разработчики и администраторы приложений. Это группа пользователей, которая функционирует во время проектирования, создания и реорганизации банка данных. Администраторы приложений координируют работу разработчиков при разработке конкретного приложения или группы приложений, объединенных в функциональную подсистему. Разработчики конкретных приложений работают с той частью информации из базы данных, которая требуется для конкретного приложения.
Не в каждом банке данных могут быть выделены все типы пользователей. Мы уже знаем, что при разработке информационных систем с использованием
24
настольных СУБД администратор банка данных, администратор приложений и разработчик часто существовали в одном лице. Однако при построении современных сложных корпоративных баз данных, которые используются для автоматизации всех или большей части бизнес-процессов в крупной фирме или корпорации, могут существовать и группы администраторов приложений, и отделы разработчиков. Наиболее сложные обязанности возложены на группу администратора БД.
Рассмотрим их более подробно.
В составе группы администратора БД должны быть:
системные аналитики;
проектировщики структур данных и внешнего по отношению к банку данных информационного обеспечения;
проектировщики технологических процессов обработки данных;
системные и прикладные программисты;
операторы и специалисты по техническому обслуживанию.
Если речь идет о коммерческом банке данных, то важную роль здесь играют специалисты по маркетингу.
Понятие представления операции создания представлений
Для описания внешних моделей в реляционной модели могут использоваться представления. Представление (View) - это SQL-запрос на выборку, который пользователь воспринимает как некоторое виртуальное отношение. Задание представлений входит в описание схемы БД в реляционных СУБД. Представления позволяют скрыть ненужные несущественные детали для разных пользователей, модифицировать реальные структуры данных в удобном для приложений виде и, наконец, разграничить права доступа к данным и тем самым повысить защиту данных от несанкционированного доступа.
В отличие от реальной таблицы представление в том виде, как оно сконструировано, не существует в базе данных, это действительно только виртуальное отношение, хотя все данные, которые представлены в нем, действительно существуют в базе данных, но в разных отношениях. Они скомпонованы для пользователя в удобном виде из реальных таблиц с помощью некоторого запроса. Однако пользователь может этого не знать, он может обращаться с этим представлением как со стандартной таблицей. Представление при создании получает некоторое уникальное имя, его описание хранится в описании схемы базы данных, и СУБД в любой момент времени при обращении к этому представлению выполняет запрос, соответствующий его описанию, поэтому пользователь, работая с представлением, в каждый момент времени видит действительно реальные, актуальные на настоящий момент данные. Оно формируется как бы на лету, в момент обращения.
Оператор определения представления имеет следующий вид:
: := CREATE VIEW
[ ()] AS
158
При необходимости в представлении можно задать новое имя для каждого столбца виртуальной таблицы. При этом надо помнить, что если указывается список столбцов, то он должен содержать ровно столько столбцов, сколько содержит их SQL - запрос.
Если список имен столбцов в представлении не задан, то каждый столбец представления получает имя соответствующего столбца запроса.
Рассмотрим типичные виды представлений и их назначение.
159
158 :: 159 :: Содержание
Предикатные синхронизационные захваты
Несмотря на привлекательность метода гранулированных синхронизационных захватов, следует отметить, что он не решает проблему фантомов (если, конечно, не ограничиться использованием захватов отношений в режимах S и X).
Известно, что проблема фантомов не возникает, если объектом блокировки является целое отношение. Именно это свойство и послужило основой разработки
244
метода предикатных синхронизационных захватов. В этом случае мы рассматриваем захват отношения - простой и частный случай предикатного захвата.
Суть этого метода - оценить множество кортежей, которое связано с той или иной транзакций, и если эти два множества, относящиеся к одному отношению, не пересекаются, то две транзакции могут оперировать ими параллельно без взаимной блокировки, а результаты выполнения обеих транзакций будут корректными.
Поскольку любая операция над реляционной базой данных задается некоторым условием (то есть в ней указывается не конкретный набор объектов базы данных, над которыми нужно выполнить операцию, а условие, которому должны удовлетворять объекты этого набора), идеальным выбором было бы требовать синхронизационный захват в режиме S или X именно этого условия. Но если посмотреть на общий вид условий, допускаемых, например, в языке SQL, то становится абсолютно непонятно, как определить совместимость двух предикатных захватов. Ясно, что без этого использовать предикатные захваты для синхронизации транзакций невозможно, а в общей форме проблема неразрешима.
К счастью, эта проблема сравнительно легко решается для случая простых условий. Будем называть простым условием конъюнкцию простых предикатов, имеющих вид:
имя - атрибута { операция сравнения } значение
Здесь операция сравнения: =, >,
В типичных СУБД, поддерживающих двухуровневую организацию (языковой уровень и уровень управления внешней памяти), в интерфейсе подсистем управления памятью (которая обычно заведует и сериализацией транзакций) допускаются только простые условия. Подсистема языкового уровня производит компиляцию исходного оператора со сложным условием в последовательность обращений к ядру СУБД, в каждом из которых содержатся только простые условия.
Следовательно, в случае типовой организации реляционной СУБД простые условия можно использовать как основу предикатных захватов.
Для простых условий совместимость предикатных захватов легко определяется на основе следующей геометрической интерпретации. Пусть R - отношение с атрибутами а1, а2, ...,.....an, a m1, m2, ..., mn - множества допустимых значений a1, а2, ..., аn соответственно (все эти множества - конечные). Тогда можно сопоставить R конечное n-мерное пространство возможных значений кортежей R. Любое простое условие "вырезает" m-мерный прямоугольник в этом пространстве (m
Тогда S-X, X-S, Х-Х предикатные захваты от разных транзакций совместимы, если соответствующие прямоугольники не пересекаются.
Это иллюстрируется следующим примером, показывающим, что в каких бы режимах не требовала транзакция 1 захвата условия (1
Пример: (n = 2)
Заметим, что предикатные захваты простых условий описываются таблицами, немногим отличающимися от таблиц традиционных синхронизаторов.
Применение агрегатных функций и вложенных запросов в операторе выбора
В SQL добавлены дополнительные функции, которые позволяют вычислять обобщенные групповые значения. Для применения агрегатных функций предполагается предварительная операция группировки. В чем состоит суть операции группировки? При группировке все множество кортежей отношения разбивается на группы, в которых собираются кортежи, имеющие одинаковые значения атрибутов, которые заданы в списке группировки.
Например, сгруппируем отношение R1 по значению столбца Дисциплина. Мы получим 4 группы, для которых можем вычислить некоторые групповые значения, например количество кортежей в группе, максимальное или минимальное значение столбца Оценка.
Это делается с помощью агрегатных функций. Агрегатные функции вычисляют одиночное значение для всей группы таблицы. Список этих функций представлен в таблице 5.7.
Таблица 5.7. Агрегатные функции
| Функция | Результат |
| COUNT | Количество строк или непустых значений полей, которые выбрал запрос |
| SUM | Сумма всех выбранных значений данного поля |
| AVG | Среднеарифметическое значение всех выбранных значений данного поля |
| MIN | Наименьшее из всех выбранных значений данного поля |
| MAX | Наибольшее из всех выбранных значений данного поля |
81
| R1 | |||
| ФИО | Дисциплина | Оценка | |
| Группа 1 | Петров Ф. И. | Базы данных | 5 |
| Сидоров К. А. | Базы данных | 4 | |
| Миронов А. В. | Базы данных | 2 | |
| Степанова К. Е. | Базы данных | 2 | |
| Крылова Т. С. | Базы данных | 5 | |
| Владимиров В. А. | Базы данных | 5 | |
| Группа 2 | Сидоров К. А. | Теория информации | 4 |
| Степанова К. Е. | Теория информации | 2 | |
| Крылова Т. С. | Теория информации | 5 | |
| Миронов А. В. | Теория информации | Null | |
| Группа 3 | Трофимов П. А. | Сети и телекоммуникации | 4 |
| Иванова Е. А. | Сети и телекоммуникации | 5 | |
| Уткина Н. В. | Сети и телекоммуникации | 5 | |
| Группа 4 | Владимиров В. А. | Английский язык | 4 |
| Трофимов П. А. | Английский язык | 5 | |
| Иванова Е. А. | Английский язык | 3 | |
| Петров Ф. И. | Английский язык | 5 |
Агрегатные функции используются подобно именам полей в операторе SELECT, но с одним исключением: они берут имя поля как аргумент.
С функциями SUM и AVG могут использоваться только числовые поля. С функциями COUNT, MAX и MIN могут использоваться как числовые, так и символьные поля. При использовании с символьными полями МАХ и MIN будут транслировать их в эквивалент ASCII кода и обрабатывать в алфавитном порядке. Некоторые СУБД позволяют использовать вложенные агрегаты, но это является отклонением от стандарта ANSI со всеми вытекающими отсюда последствиями.
Например, можно вычислить количество студентов, сдававших экзамены по каждой дисциплине. Для этого надо выполнить запрос с группировкой по полю "Дисциплина" и вывести в качестве результата название дисциплины и количество строк в группе по данной дисциплине. Применение символа * в качестве аргумента функции COUNT означает подсчет всех строк в группе.
SELECT R1.Дисциплина. COUNTC*)
FROM R1
GROUP BY R1 Дисциплина
Результат:
82
| Дисциплина | COUNT(*) |
| Базы данных | 6 |
| Теория информации | 4 |
| Сети и телекоммуникации | 3 |
| Английский язык | 4 |
SELECT R1.Дисциплина. COUNT(*). FROM R1
WHERE R1.Оценка IS NOT NULL GROUP BY R1.Дисциплина
Получим результат:
| Дисциплина | COUNT(*) |
| Базы данных | 6 |
| Теория информации | 3 |
| Сети и телекоммуникаций | 3 |
| Английский язык | 4 |
| Миронов А. В. | Теория информации | Null |
Можно применять агрегатные функции также и без операции предварительной группировки, в этом случае все отношение рассматривается как одна группа и для этой группы можно вычислить одно значение на группу.
Обратившись снова к базе данных "Сессия" (таблицы R1, R2, R3), найдем количество успешно сданных экзаменов:
SELECT COUNT(*)
FROM R1
WHERE Оценка > 2:
Это, конечно, отличается от выбора поля, поскольку всегда возвращается одиночное значение, независимо от того, сколько строк находится в таблице. Аргументом агрегатных функций могут быть отдельные столбцы таблиц. Но для
83
того, чтобы вычислить, например, количество различных значений некоторого столбца в группе, необходимо применить ключевое слово DISTINCT совместно с именем столбца. Вычислим количество различных оценок, полученных по каждой дисциплине:
SELECT R1.Дисциплина, COUNT(DISTINCT R1.Оценка)
FROM R1
WHERE R1.Оценка IS NOT NULL
GROUP BY R1.Дисциплина
Результат:
| Дисциплина | COUNT(DISTINCT R1 .Оценка) |
| Базы данных | 3 |
| Теория информации | 3 |
| Сети и телекоммуникации | 2 |
| Английский язык | 3 |
SELECT R2.Группа. R1.Дисциплина. COUNT(*). АVР(Оценка)
FROM R1.R2
WHERE R1.ФИО = R2.ФИО AND
R1.Оценка IS NOT NULL AND
R1.Оценка > 2
GROUP BY R2.Группа. R1.Дисциплина
Результат:
| Дисциплина | COUNT(*) | АVRОценка) |
| Базы данных | 6 | 3.83 |
| Теория информации | 3 | 3.67 |
| Сети и телекоммуникации | 3 | 4.66 |
| Английский язык | 4 | 4.25 |
84
Предложение GROUP BY позволяет определять подмножество значений в особом поле в терминах другого поля и применять функцию агрегата к подмножеству. Это дает возможность объединять поля и агрегатные функции в едином предложении SELECT. Агрегатные функции могут применяться как в выражении вывода результатов строки SELECT, так и в выражении условия обработки сформированных групп HAVING.
В этом случае каждая агрегатная функция вычисляется для каждой выделенной группы. Значения, полученные при вычислении агрегатных функций, могут быть использованы для вывода соответствующих результатов или для условия отбора групп.
Построим запрос, который выводит группы, в которых по одной дисциплине на экзаменах получено больше одной двойки:
SELECT R2.Группа
FROM R1.R2
WHERE Rl-ФИО = R2.ФИО AND
R1.Оценка = 2
GROUP BY R2.Группа . Rl.Дисциплина
HAVING count(*)> 1
В дальнейшем в качестве примера будем работать не с БД "Сессия", а с БД "Банк", состоящей из одной таблицы F, в которой хранится отношение F, содержащее информацию о счетах в филиалах некоторого банка:
F = (N, ФИО, Филиал, Дата Открытия, Дата Закрытия, Остаток);
Q = (Филиал, Город);
поскольку на этой базе можно ярче проиллюстрировать работу с агрегатными функциями и группировкой.
Например, предположим, что мы хотим найти суммарный остаток на счетах в филиалах. Можно сделать раздельный запрос для каждого из них, выбрав SUM(Остаток) из таблицы для каждого филиала. GROUP BY, однако, позволит поместить их все в одну команду:
SELECT Филиал, SUM(Остаток)
FROM F
GROUP BY Филиал:
GROUP BY применяет агрегатные функции независимо для каждой группы, определяемой с помощью значения поля Филиал. Группа состоит из строк с одинаковым значением поля Филиал, и функция dSUM применяется отдельно для каждой такой группы, то есть суммарный остаток на счетах подсчитывается отдельно для каждого филиала. Значение поля, к которому применяется GROUP BY, имеет, по определению, только одно значение на группу вывода, как и результат работы агрегатной функции. Поэтому мы можем совместить в одном запросе агрегат и поле. Вы можете также использовать GROUP BY с несколькими полями.
Предположим, что мы хотели бы увидеть только те суммарные значения остатков на счетах, которые превышают $5000. Чтобы увидеть суммарные остатки
85
свыше $5000, необходимо использовать предложение HAVING. Предложение HAVING определяет критерии, используемые, чтобы удалять определенные группы из вывода, точно так же как предложение WHERE делает это для индивидуальных строк.
Правильной командой будет следующая:
SELECT Филиал. SUM(Остаток)
FROM F
GROUP BY Филиал
HAVING SUM(Остаток) > 5000;
Аргументы в предложении HAVING подчиняются тем же самым правилам, что и в предложении SELECT, где используется GROUP BY. Они должны иметь одно значение на группу вывода.
Следующая команда будет запрещена:
SELECT Филиал.SUM(Остаток)
FROM F
GROUP BY Филиал
HAVING Дата Открытия - 27/12/1999;
Поле Дата Открытия не может быть использовано в предложении HAVING, потому что оно может иметь больше чем одно значение на группу вывода. Чтобы избежать такой ситуации, предложение HAVING должно ссылаться только на агрегаты и поля, выбранные GROUP BY. Имеется правильный способ сделать вышеупомянутый запрос:
SELECT Филиал.SUM(Остаток)
FROM F
WHERE Дата Открытия = ?27/12/1999?
GROUP BY Филиал:
Смысл данного запроса следующий: найти сумму остатков по каждому филиалу счетов, открытых 27 декабря 1999 года.
Как и говорилось ранее, HAVING может использовать только аргументы, которые имеют одно значение на группу вывода. Практически, ссылки на агрегатные функции - наиболее общие, но и поля, выбранные с помощью GROUP BY, также допустимы. Например, мы хотим увидеть суммарные остатки на счетах филиалов в Санкт-Петербурге, Пскове и Урюпинске:
SELECT Филиал. SUM(Остаток)
FROM F.Q
WHERE F.Филиал = Q.Филиал
GROUP BY Филиал
HAVING Филиал IN ("Санкт-Петербург". "Псков", "Урюпинск");
Поэтому в арифметических выражениях предикатов, входящих в условие выборки раздела HAVING, прямо можно использовать только спецификации столбцов,
86
указанных в качестве столбцов группирования в разделе GROUP BY. Остальные столбцы можно специфицировать только внутри спецификаций агрегатных функций COUNT, SUM, AVG, MIN и MAX, вычисляющих в данном случае некоторое агрегатное значение для всей группы строк. Аналогично обстоит дело с подзапросами, входящими в предикаты условия выборки раздела HAVING: если в подзапросе используется характеристика текущей группы, то она может задаваться только путем ссылки на столбцы группирования.
Результатом выполнения раздела HAVING является сгруппированная таблица, содержащая только те группы строк, для которых результат вычисления условия поиска есть TRUE. В частности, если раздел HAVING присутствует в табличном выражении, не содержащем GROUP BY, то результатом его выполнения будет либо пустая таблица, либо результат выполнения предыдущих разделов табличного выражения, рассматриваемый как одна группа без столбцов группирования.
Пример описания предметной области
Пусть требуется разработать информационную систему для автоматизации учета получения и выдачи книг в библиотеке. Система должна предусматривать режимы ведения системного каталога, отражающего перечень областей знаний, по которым имеются книги в библиотеке. Внутри библиотеки области знаний в систематическом каталоге могут иметь уникальный внутренний номер и полное наименование. Каждая книга может содержать сведения из нескольких областей знаний. Каждая книга в библиотеке может присутствовать в нескольких экземплярах. Каждая книга, хранящаяся в библиотеке, характеризуется следующими параметрами:
уникальный шифр;
название;
фамилии авторов (могут отсутствовать);
место издания (город);
издательство;
год издания;
количество страниц;
стоимость книги;
количество экземпляров книги в библиотеке.
Книги могут иметь одинаковые названия, но они различаются по своему уникальному шифру (ISBN).
В библиотеке ведется картотека читателей.
На каждого читателя в картотеку заносятся следующие сведения:
107
фамилия, имя, отчество;
домашний адрес;
телефон (будем считать, что у нас два телефона - рабочий и домашний);
дата рождения.
Каждому читателю присваивается уникальный номер читательского билета.
Каждый читатель может одновременно держать на руках не более 5 книг. Читатель не должен одновременно держать более одного экземпляра книги одного названия.
Каждая книга в библиотеке может присутствовать в нескольких экземплярах. Каждый экземпляр имеет следующие характеристики:
уникальный инвентарный номер;
шифр книги, который совпадает с уникальным шифром из описания книг;
место размещения в библиотеке.
В случае выдачи экземпляра книги читателю в библиотеке хранится специальный вкладыш, в котором должны быть записаны следующие сведения:
номер билета читателя, который взял книгу;
дата выдачи книги;
дата возврата.
Предусмотреть следующие ограничения на информацию в системе:
Книга может не иметь ни одного автора.
В библиотеке должны быть записаны читатели не моложе 17 лет.
В библиотеке присутствуют книги, изданные начиная с 1960 по текущий год.
Каждый читатель может держать на руках не более 5 книг.
Каждый читатель при регистрации в библиотеке должен дать телефон для связи: он может быть рабочим или домашним.
Каждая область знаний может содержать ссылки на множество книг, но каждая книга может относиться к различным областям знаний.
С данной информационной системой должны работать следующие группы пользователей:
библиотекари;
читатели;
администрация библиотеки.
При работе с системой библиотекарь должен иметь возможность решать следующие задачи:
Принимать новые книги и регистрировать их в библиотеке.
Относить книги к одной или к нескольким областям знаний.
108
Проводить каталогизацию книг, то есть назначение новых инвентарных номеров вновь принятым книгам, и, помещая их на полки библиотеки, запоминать место размещения каждого экземпляра.
Проводить дополнительную каталогизацию, если поступило несколько экземпляров книги, которая уже есть в библиотеке, при этом информация о книге в предметный каталог не вносится, а каждому новому экземпляру присваивается новый инвентарный номер и для него определяется место на полке
библиотеки.
Проводить списание старых и не пользующихся спросом книг. Списывать можно только книги, ни один экземпляр которых не находится у читателей. Списание проводится по специальному акту списания, который утверждается администрацией библиотеки.
Вести учет выданных книг читателям, при этом предполагается два режима работы: выдача книг читателю и прием от него возвращаемых им книг обратно в библиотеку. При выдаче книг фиксируется, когда и какой экземпляр книги был выдан данному читателю и к какому сроку читатель должен вернуть этот экземпляр книги. При выдаче книг наличие свободного экземпляра и его конкретный номер могут определяться по заданному уникальному шифру книги или инвентарный номер может быть известен заранее. Не требуется вести "историю" чтения книг, то есть требуется отражать только текущее состояние библиотеки.
При приеме книги, возвращаемой читателем, проверяется соответствие возвращаемого инвентарного номера книги выданному инвентарному номеру, и она ставится на свое старое место на полку библиотеки.
Проводить списание утерянных читателем книг по специальному акту списания или замены, подписанному администрацией библиотеки.
Проводить закрытие абонемента читателя, то есть уничтожение данных о нем, если читатель хочет выписаться из библиотеки и не является ее должником, то есть за ним не числится ни одной библиотечной книги.
Читатель должен иметь возможность решать следующие задачи:
Просматривать системный каталог, то есть перечень всех областей знаний, книги по которым есть в библиотеке.
По выбранной области знаний получить полный перечень книг, которые числятся в библиотеке.
Для выбранной книги получить инвентарный номер свободного экземпляра книги или сообщение о том, что свободных экземпляров книги нет. В случае отсутствия свободных экземпляров книги читатель должен иметь возможность узнать дату ближайшего предполагаемого возврата экземпляра данной книги. Читатель не может узнать данные о том, у кого в настоящий момент экземпляры данной книги находятся на руках (в целях обеспечения личной безопасности держателей требуемой книги).
Для выбранного автора получить список книг, которые числятся в библиотеке.
Администрация библиотеки должна иметь возможность получать сведения о должниках - читателях библиотеки, которые не вернули вовремя взятые книги; сведения о книгах, которые не являются популярными, т. е. ни один экземпляр
109
которых не находится на руках у читателей; сведения о стоимости конкретной книги, для того чтобы установить возможность возмещения стоимости утерянной книги или возможность замены ее другой книгой; сведения о наиболее популярных книгах, то есть таких, все экземпляры которых находятся на руках у читателей.
Этот совсем небольшой пример показывает, что перед началом разработки необходимо иметь точное представление о том, что же должно выполняться в нашей системе, какие пользователи в ней будут работать, какие задачи будет решать каждый пользователь.
И это правильно, ведь когда мы строим здание, мы тоже заранее предполагаем: для каких целей оно предназначено, в каком климате оно будет стоять, на какой почве, и в зависимости от этого проектировщики могут предложить нам тот или иной проект. Но, к сожалению, очень часто по отношению к базам данных считается, что все можно определить потом, когда проект системы уже создан. Отсутствие четких целей создания БД может свести на нет все усилия разработчиков, и проект БД получится "плохим", неудобным, не соответствующим ни реально моделируемому объекту, ни задачам, которые должны решаться с использованием данной БД.
Отложим на время рассмотрение этапа инфологического моделирования предметной области - этому серьезному вопросу будет посвящена следующая, седьмая глава, а мы пойдем классическим путем и рассмотрим сначала этап даталогического проектирования. Напомним, что этап диалогического проектирования происходит уже после выбора конкретной модели данных. И мы рассматриваем даталогическое проектирование для реляционной модели данных.
Процесс прохождения пользовательского запроса
Рисунок 2.2 иллюстрирует взаимодействие пользователя, СУБД и ОС при обработке запроса на получение данных. Цифрами помечена последовательность взаимодействий:
Рис. 2.2. Схема прохождения запроса к БД
22
Пользователь посылает СУБД запрос на получение данных из БД.
Анализ прав пользователя и внешней модели данных, соответствующей данному пользователю, подтверждает или запрещает доступ данного пользователя к запрошенным данным.
В случае запрета на доступ к данным СУБД сообщает пользователю об этом (стрелка 12) и прекращает дальнейший процесс обработки данных, в противном случае СУБД определяет часть концептуальной модели, которая затрагивается запросом пользователя.
СУБД получает информацию о запрошенной части концептуальной модели.
СУБД запрашивает информацию о местоположении данных на физическом уровне (файлы или физические адреса).
В СУБД возвращается информация о местоположении данных в терминах операционной системы.
СУБД вежливо просит операционную систему предоставить необходимые данные, используя средства операционной системы.
Операционная система осуществляет перекачку информации из устройств хранения и пересылает ее в системный буфер.
Операционная система оповещает СУБД об окончании пересылки.
СУБД выбирает из доставленной информации, находящейся в системном буфере, только то, что нужно пользователю, и пересылает эти данные в рабочую область пользователя.
БМД - это База Метаданных, именно здесь и хранится вся информация об используемых структурах данных, логической организации данных, правах доступа пользователей и, наконец, физическом расположении данных. Для управления БМД существует специальное программное обеспечение администрирования баз данных, которое предназначено для корректного использования единого информационного пространства многими пользователями.
Всегда ли запрос проходит полный цикл? Конечно, нет. СУБД обладает достаточно развитым интеллектом, который позволяет ей не повторять бессмысленных действий. И поэтому, например, если этот же пользователь повторно обратится к СУБД с новым запросом, то для него уже не будут проверяться внешняя модель и права доступа, а если дальнейший анализ запроса покажет, что данные могут находиться в системном буфере, то СУБД осуществит только 11 и 12 шаги в обработке запроса.
Разумеется, механизм прохождения запроса в реальных СУБД гораздо сложнее, но и эта упрощенная схема показывает, насколько серьезными и сложными должны быть механизмы обработки запросов, поддерживаемые реальными СУБД.
23
22 :: 23 :: Содержание
Проверка полномочий
Второй задачей при работе с БД, как указывалось ранее, является проверка полномочий пользователей. Полномочия пользователей хранятся в специальных системных таблицах, и их проверка осуществляется ядром СУБД при выполнении каждой операции. Логически для каждого пользователя и каждого объекта в БД как бы строится некоторая условная матрица, где по одному измерению расположены объекты, а по другому - пользователи. На пересечении каждого столбца и каждой строки расположен, перечень разрешенных операций для данного пользователя над данным объектом. С первого взгляда кажется, что эта модель проверки достаточно устойчивая. Но сложность возникает тогда, когда мы используем косвенное обращение к объектам. Например, пользователю user_N не разрешен доступ к таблице Tabl, но этому пользователю разрешен запуск хранимой процедуры SP_N, которая делает выборку из этого объекта. По умолчанию все хранимые процедуры запускаются под именем их владельца.
Такие проблемы должны решаться организационными методами. При разрешении доступа некоторых пользователей необходимо помнить о возможности косвенного доступа.
В любом случае проблема защиты никогда не была чисто технической задачей, это комплекс организационно - технических мероприятий, которые должны обеспечить максимальную конфиденциальность информации, хранимой в БД.
Кроме того, при работе в сети существует еще проблема проверки подлинности полномочий.
Эта проблема состоит в следующем. Допустим, процессу 1 даны полномочия по работе с БД, а процессу 2 такие полномочия не даны. Тогда напрямую процесс 2 не может обратиться к БД, но он может обратиться к процессу 1 и через него получить доступ к информации из БД.
Поэтому в безопасной среде должна присутствовать модель проверки подлинности, которая обеспечивает подтверждение заявленных пользователями или процессами идентификаторов. Проверка полномочий приобрела еще большее значение в условиях массового распространения распределенных вычислений. При
284
существующем высоком уровне связности вычислительных систем необходимо контролировать все обращения к системе.
Проблемы проверки подлинности обычно относят к сфере безопасности коммуникаций и сетей, поэтому мы не будем их здесь более обсуждать, за исключением следующего замечания. В целостной системе компьютерной безопасности, где четкое выполнение программы защиты информации обеспечивается за счет взаимодействия соответствующих средств в операционных системах, сетях, базах данных, проверка подлинности имеет прямое отношение к безопасности баз данных.
Заметим, что модель безопасности, основанная на базовых механизмах проверки полномочий и проверки подлинности, не решает таких проблем, как украденные пользовательские идентификаторы и пароли или злонамеренные действия некоторых пользователей, обладающих полномочиями, - например, когда программист, работающий над учетной системой, имеющей полный доступ к учетной базе данных, встраивает в код программы "Троянского коня" с целью хищения или намеренного изменения информации, хранимой в БД. Такие вопросы выходят за рамки нашего обсуждения средств защиты баз данных, но следует тем не менее представлять себе, что программа обеспечения информационной безопасности должна охватывать не только технические области (такие как защита сетей, баз данных и операционных систем), но и проблемы физической защиты, надежности персонала (скрытые проверки), аудит, различные процедуры поддержки безопасности, выполняемые вручную или частично автоматизированные.
Распределенные базы данных
Хорошо известно, что история развивается по спирали, поэтому после процесса "персонализации" начался обратный процесс - интеграция. Множится количество локальных сетей, все больше информации передается между компьютерами, остро встает задача согласованности данных, хранящихся и обрабатывающихся в разных местах, но логически друг с другом связанных, возникают задачи, связанные с параллельной обработкой транзакций - последовательностей операций над БД, переводящих ее из одного непротиворечивого состояния в другое непротиворечивое состояние. Успешное решение этих задач приводит к появлению распределенных баз данных, сохраняющих все преимущества настольных СУБД и в то же время позволяющих организовать параллельную обработку информации и поддержку целостности БД.
Особенности данного этапа:
Практически все современные СУБД обеспечивают поддержку полной реляционной модели, а именно:
структурной целостности - допустимыми являются только данные, представленные в виде отношений реляционной модели;
языковой целостности, то есть языков манипулирования данными высокого уровня (в основном SQL);
ссылочной целостности, контроля за соблюдением ссылочной целостности в течение всего времени функционирования системы, и гарантий невозможности со стороны СУБД нарушить эти ограничения.
Большинство современных СУБД рассчитаны на многоплатформенную архитектуру, то есть они могут работать на компьютерах с разной архитектурой и под разными операционными системами, при этом для пользователей доступ к данным, управляемым СУБД на разных платформах, практически неразличим.
Необходимость поддержки многопользовательской работы с базой данных и возможность децентрализованного хранения данных потребовали развития средств администрирования БД с реализацией общей концепции средств защиты данных.
Потребность в новых реализациях вызвала создание серьезных теоретических трудов по оптимизации реализаций распределенных БД и работе с распределенными транзакциями и запросами с внедрением полученных результатов в коммерческие СУБД.
Для того чтобы не потерять клиентов, которые ранее работали на настольных СУБД, практически все современные СУБД имеют средства подключения клиентских приложений, разработанных с использованием настольных СУБД, и средства экспорта данных из форматов настольных СУБД второго этапа развития.
17
Именно к этому этапу можно отнести разработку ряда стандартов в рамках языков описания и манипулирования данными начиная с SQL89, SQL92, SQL99 и технологий по обмену данными между различными СУБД, к которым можно отнести и протокол ODBC (Open DataBase Connectivity), предложенный фирмой Microsoft.
Именно к этому этапу можно отнести начало работ, связанных с концепцией объектно-ориентированных БД - ООБД. Представителями СУБД, относящимся ко второму этапу, можно считать MS Access 97 и все современные серверы баз данных Oracle7.3,Oracle 8.4 MS SQL6.5, MS SQL7.0, System 10, System 11, Informix, DB2, SQL Base и другие современные серверы баз данных, которых в настоящий момент насчитывается несколько десятков.
Реализация системы защиты в MS SQL Server
SQL server 6.5 поддерживает З режима проверки при определении прав пользователя:
Стандартный (standard).
Интегрированный (integrated security).
Смешанный (mixed).
Стандартный режим защиты предполагает, что каждый пользователь должен иметь учетную запись как пользователь домена NT Server. Учетная запись пользователя домена включает имя пользователя и его индивидуальный пароль. Пользователи доменов могут быть объединены в группы. Как пользователь домена пользователь получает доступ к определенным ресурсам домена. В качестве одного из ресурсов домена и рассматривается SQL Server. Но для доступа к SQL Server пользователь должен иметь учетную запись пользователя MS SQL Server. Эта учетная запись также должна включать уникальное имя пользователя сервера и его пароль. При подключении к операционной среде пользователь задает свое имя и пароль пользователя домена. При подключении к серверу баз данных пользователь задает свое уникальное имя пользователя SQL Server и свой пароль.
Интегрированный режим предполагает, что для пользователя задается только одна учетная запись в операционной системе, как пользователя домена, a SQL Server идентифицирует пользователя по его данным в этой учетной записи. В этом случае пользователь задает только одно свое имя и один пароль.
282
В случае смешанного режима часть пользователей может быть подключена к серверу с использованием стандартного режима, а часть с использованием интегрированного режима.
В MS SQL Server 7.0 оставлены только 2 режима: интегрированный, называемый Windows NT Authentication Mode (Windows NT Authentication), и смешанный, Mixed Mode (Windows NT Authentication and SQL Server Authentication). Алгоритм проверки аутентификации пользователя в MS SQL Server 7.0 приведен на рис. 13.1.
Рис. 13.1. Алгоритм проверки аутентификации пользователя в MS SQL Server 7.0
При попытке подключения к серверу БД сначала проверяется, какой метод аутентификации определен для данного пользователя. Если определен Windows NT Authentication Mode, то далее проверяется, имеет ли данный пользователь домена доступ к ресурсу SQL Server, если он имеет доступ, то выполняется
283
попытка подключения с использованием имени пользователя и пароля, определенных для пользователя домена; если данный пользователь имеет права подключения к SQL Server, то подключение выполняется успешно, в противном случае пользователь получает сообщение о том, что данному пользователю не разрешено подключение к SQL Server. При использовании смешанного режима аутентификации средствами SQL Server проводится последовательная проверка имени пользователя (login) и его пароля (password); если эти параметры заданы корректно, то подключение завершается успешно, в противном случае пользователь также получает сообщение о невозможности подключиться к SQL Server.
Для СУБД Oracle всегда используется в дополнение к имени пользователя и пароля в операционной среде его имя и пароль для работы с сервером БД.
284
282 :: 283 :: 284 :: Содержание
Сетевая модель данных
Стандарт сетевой модели впервые был определен в 1975 году организацией CODASYL (Conference of Data System Languages), которая определила базовые понятия модели и формальный язык описания.
Базовыми объектами модели являются:
элемент данных;
агрегат данных;
запись;
набор данных.
Элемент данных - то же, что и в иерархической модели, то есть минимальная информационная единица, доступная пользователю с использованием СУБД.
Агрегат данных соответствует следующему уровню обобщения в модели. В модели определены агрегаты двух типов: агрегат типа вектор и агрегат типа повторяющаяся группа.
Агрегат данных имеет имя, и в системе допустимо обращение к агрегату по имени. Агрегат типа вектор соответствует линейному набору элементов данных. Например, агрегат Адрес может быть представлен следующим образом:
| Адрес | |||
| Город | Улица | дом | квартира |
Агрегат типа повторяющаяся группа соответствует совокупности векторов данных. Например, агрегат Зарплата соответствует типу повторяющаяся группа с числом повторений 12.
| Зарплата | |
| Месяц | Сумма |
40
Записью называется совокупность агрегатов или элементов данных, моделирующая некоторый класс объектов реального мира. Понятие записи соответствует понятию "сегмент" в иерархической модели. Для записи, так, же как и для сегмента, вводятся понятия типа записи и экземпляра записи.
Следующим базовым понятием в сетевой модели является понятие "Набор". Набором называется двухуровневый граф, связывающий отношением "один - ко - многим" два типа записи.
Набор фактически отражает иерархическую связь между двумя типами записей. Родительский тип записи в данном наборе называется владельцем набора, а дочерний тип записи - членом того же набора.
Для любых двух типов записей может быть задано любое количество наборов, которые их связывают. Фактически наличие подобных возможностей позволяет промоделировать отношение "многие - ко - многим" между двумя объектами реального мира, что выгодно отличает сетевую модель от иерархической.
В рамках набора возможен последовательный просмотр экземпляров членов набора, связанных с одним экземпляров владельца набора.
Между двумя типами записей может быть определено любое количество наборов: например, можно построить два взаимосвязанных набора. Существенным ограничением набора является то, что один и тот же тип записи не может быть одновременно владельцем и членом набора.
В качестве примера рассмотрим таблицу, на основе которой организуем два набора и определим связь между ними:
| Преподаватель | Группа | День недели | № пары | Аудитория | Дисциплина |
| Иванов | 4306 | Понедельник | 1 | 22-13 | кид |
| Иванов | 4307 | Понедельник | 2 | 22-13 | кид |
| Карпова | 4307 | Вторник | 2 | 22-14 | Б3 и ЭС |
| Карпова | 4309 | Вторник | 4 | 22-14 | Б3 и ЭС |
| Карпова | 84305 | Вторник | 1 | 22-14 | БД |
| Смирнов | 4306 | Вторник | 3 | 23-07 | ГВП |
| Смирнов | 4309 | Вторник | 4 | 23-07 | гвп |
Экземпляров набора Ведет занятия будет 3 (по числу преподавателей), экземпляров набора Занимается у будет 4 (по числу групп). На рис. 3.6 представлены взаимосвязи экземпляров данных наборов.
Рис. 3.6. Пример взаимосвязи экземпляров двух наборов
Среди всех наборов выделяют специальный тип набора, называемый "Сингулярным набором", владельцем которого формально определена вся система. Сингулярный набор изображается в виде входящей стрелки, которая имеет собственно имя набора и имя члена набора, но у которой не определен тип записи "Владелец набора". Например, сингулярный набор М.
Сингулярные наборы позволяют обеспечить доступ к экземплярам отдельных типов данных, поэтому если в задаче алгоритм обработки информации предполагает обеспечение произвольного доступа к некоторому типу записи, то для поддержки этой возможности необходимо ввести соответствующий сингулярный набор.
В общем случае сетевая база данных представляет совокупность взаимосвязанных наборов, которые образуют на концептуальном уровне некоторый граф.
Сгруппированные представления
Эти представления содержат запросы, которые имеют группировку. Сгруппированные представления всегда должны содержать список столбцов. Они могут использовать агрегированные функции в качестве результирующих столбцов, а в дальнейшем это представление может использоваться как виртуальная таблица, например, в других запросах.
Создадим представление, которое определяет суммарный фон заработной платы и надбавок по каждому подразделению с указанием количества сотрудников, минимальной, максимальной и средней зарплаты и надбавки по подразделению. Такой запрос позволяет сравнить заработную плату и надбавки прямо по всем подразделениям, и он может быть очень эффективно использован администрацией при проведении сравнительного анализа подразделений фирмы.
CREATE VIEW RATE
DEPARTMENT. COUNT(*). SUM(SALARY), SUM(PREMIUM). MAX(SALARY). MIN(SALARY).
AVERAGE (SALARY). MAX(PREMIUM). MIN(PREMIUM). AVERAGE (PREMIUM)
AS
SELECT DEPARTMENT. COUNT(*). SUM(SALARY). SUM(PREMIUM). MAX(SALARY),
MIN(SALARY). AVERAGE (SALARY). MAX(PREMIUM). MIN(PREMIUM).
AVERAGE (PREMIUM)
FROM EMPLOYEE
GROUP BY DEPARTMENT
160
160 :: Содержание
Системный анализ предметной области
С точки зрения проектирования БД в рамках системного анализа, необходимо осуществить первый этап, то есть провести подробное словесное описание объектов предметной области и реальных связей, которые присутствуют между описываемыми объектами. Желательно, чтобы данное описание позволяло корректно определить все взаимосвязи между объектами предметной области.
В общем случае существуют два подхода к выбору состава и структуры предметной области:
Функциональный подход - он реализует принцип движения "от задач" и применяется тогда, когда заранее известны функции некоторой группы лиц и комплексов задач, для обслуживания информационных потребностей которых создается рассматриваемая БД. В этом случае мы можем четко выделить минимальный необходимый набор объектов предметной области, которые должны быть описаны.
Предметный подход - когда информационные потребности будущих пользователей БД жестко не фиксируются. Они могут быть многоаспектными и весьма динамичными. Мы не можем точно выделить минимальный набор объектов предметной области, которые необходимо описывать. В описание предметной области в этом случае включаются такие объекты и взаимосвязи, которые наиболее характерны и наиболее существенны для нее. БД, конструируемая при этом, называется предметной, то есть она может быть использована при решении множества разнообразных, заранее не определенных задач. Конструирование предметной БД в некотором смысле кажется гораздо более заманчивым, однако трудность всеобщего охвата предметной области с невозможностью конкретизации потребностей пользователей может привести к избыточно сложной схеме БД, которая для конкретных задач будет неэффективной.
106
Чаще всего на практике рекомендуется использовать некоторый компромиссный вариант, который, с одной стороны, ориентирован на конкретные задачи или функциональные потребности пользователей, а с другой стороны, учитывает возможность наращивания новых приложений.
Системный анализ должен заканчиваться подробным описанием информации об объектах предметной области, которая требуется для решения конкретных задач и которая должна храниться в БД, формулировкой конкретных задач, которые будут решаться с использованием данной БД с кратким описанием алгоритмов их решения, описанием выходных документов, которые должны генерироваться в системе, описанием входных документов, которые служат основанием для заполнения данными БД.
107
106 :: 107 :: Содержание
Специальные операции реляционной алгебры
Первой специальной операцией реляционной алгебры является горизонтальный выбор, или операция фильтрации, или операция ограничения отношений. Для определения этой операции нам необходимо ввести дополнительные обозначения.
Пусть ? - булевское выражение, составленное из термов сравнения с помощью связок И (?), ИЛИ (?), НЕ (-) и, возможно, скобок. В качестве термов сравнения допускаются:
терм А ос а,
где А - имя некоторого атрибута, принимающего значения из домена D; а -константа, взятая из того же домена D, a ? D; ос - одна из допустимых для данного домена D операций сравнения;
терм А ос В,
где А, В - имена некоторых ? - сравнимых атрибутов, то есть атрибутов, принимающих значения из одного и то же домена D.
Тогда результатом операции выбора, или фильтрации, заданной на отношении R в виде булевского выражения, определенного на атрибутах отношения R, называется отношение R[?], включающее те кортежи из исходного отношения, для которых истинно условие выбора или фильтрации:
R[?(r)] = {r | r ? R ? ?(r) = "Истина"}
Операция фильтрации является одной из основных при работе с реляционной моделью данных. Условие а может быть сколь угодно сложным.
57
Например, выбрать из R10 детали с шифром "0011003".
R12 = R10 [ Шифр детали = "0011003"]
| R12 | ||
| Шифр детали | Название детали | Цех |
| 00011003 | Болт M1 | Цех 1 |
| 00011003 | Болт M1 | Цех 3 |
Следующей специальной операцией является операция проектирования.
Пусть R - отношение, SR = (А1, ... , Аn) - схема отношения R.
Обозначим через В подмножество [ Аi ]; В ? { Аi }.
При этом пусть В1 - множество атрибутов из { Аi }, не вошедших в В.
Если В - {А1i,А2i, ... ,Аki}. В1 = {А1j,А2j,...,Аkj}и r = 1i, a2i,...,aki >, аki ? Аiki,
то r [В], s = < а2j, а2j, ... , аmj, > ; аmj ? Аmj.
Проекцией отношения R на набор атрибутов В, обозначаемой R[B], называется отношение со схемой, соответствующей набору атрибутов В SR[B] = В, содержащему кортежи, получаемые из кортежей исходного отношения R путем удаления из них значений, не принадлежащих атрибутам из набора В.
R[B] = { r[В] }
По определению отношений все дублирующие кортежи удаляются из результирующего отношения.
Операция проектирования, называемая иногда также операцией вертикального выбора, позволяет получить только требуемые характеристики моделируемого объекта. Чаще всего операция проектирования употребляется как промежуточный шаг в операциях горизонтального выбора, или фильтрации. Кроме того, она используется самостоятельно на заключительном этапе получения ответа на запрос.
Например, выберем все цеха, которые изготавливают деталь "Болт Ml".
Для этого нам необходимо из отношения R10 выбрать детали с заданным названием, а потом полученное отношение спроектировать на столбец "Цех". Результатом выполнения этих операций будет отношение R14:
R13 = R10 [ Название детали = "Болт Ml" ]
R14 = R13 [ Цех ]
|
|
58
В отличие от рассмотренных специальных операций реляционной алгебры: фильтрации и проектирования, которые являются унарными, то есть производятся над одним отношением, операция условного соединения является бинарной, то есть исходными для нее являются два отношения, а результатом - одно.
Пусть R = {r}, Q = {q} - исходные отношения,
SR, SQ - схемы отношений R и Q соответственно.
SR = (А1, А2, ... , Ak); SQ = (В1, В2, ... , Вm),
где Аi, Вj - имена атрибутов в схемах отношений R и Q соответственно.
При этом полагаем, что заданы наборы атрибутов А и В
А ? { Аi }i=1,k; В ? { Bj } j=1,m
и эти наборы состоят из ? - сравнимых атрибутов.
Тогда соединением отношений R и Q при условии р будет подмножество декартова произведения отношений R и Q, кортежи которого удовлетворяют условию р, рассматриваемому как одновременное выполнение условий:
r.Аi ?i Вi : i = 1,k, где k - число атрибутов, входящих в наборы А и В, а ?i -конкретная операция сравнения.
Аi ?i Вi D; ?i - i- й предикат сравнения, определяемый из множества допустимых на домене Di операций сравнения.
R [ ? ] Q = { (r,q) | (r, q) | r.А ?i q.Bi = "Истина", i = 1,k}
Например, рассмотрим следующий запрос. Пусть отношение R15 содержит перечень деталей с указанием материалов, из которых эти детали изготавливаются, и оно имеет вид:
|
|
59
R16 = (R15[(R15.Шифр детали = R10.Шифр детали) ? R10.Цех = "Цех1" ? ? R15.Материал = "сталь - ст1"] Rю10)[Название детали]
Последней операцией, включаемой в набор операций реляционной алгебры, является операция деления.
Для определения операции деления рассмотрим сначала понятие множества образов.
Пусть R - отношение со схемой SR = (A1, A2 ,..., Ak);
Пусть А - некоторый набор атрибутов А ? { Аi } i=l,k , А1 - набор атрибутов, не входящих в множество А.
Пересечение множеств А и А1 пусто: А ? А1 = O; объединение множеств равно множеству всех атрибутов исходного отношения: A ? А1 = SR.
Тогда множеством образов элемента у проекции R[A] называется множество таких элементов у проекции R[A1] , для которых сцепление (х, у) является кортежами отношения R, то есть
QA(x) = {у | у ? R[A1] ? (х, у) ? R} - множество образов.
Например, множеством образов отношения R15 по материалу "сталь - ст2" будет множество кортежей
R15.Материал = {< 00011075, Гайка М2, "сталь - ст2">, < 00011077, Гайка М4, "сталь - ст2">}
Дадим теперь определение операции деления.
Пусть даны два отношения R и Т соответственно со схемами:
SR = (А1, А2, ... , Ak); ST = (В1, В2, ... , Вm);
А и В - наборы атрибутов этих отношений, одинаковой длины (без повторений);
А ? SR ; В ? ST.
Атрибуты А1 - это атрибуты из R, не вошедшие в множество А.
Пересечение множеств А ? А1 = O - пусто и A ? A1 = SR. Проекции R [А] и Т[В] совместимы по объединению, то есть имеют эквивалентные схемы: SR[A] ? ST[B]
Тогда операция деления ставит в соответствие отношениям R и Т отношение Q = R[A:B]T, кортежи которого являются теми элементами проекции R[A1], для которых Т[В] входит в построенные для них множество образов:
R[А:В]Т = {r | r ? R[A1] ? Т[В] ? {у | у ? R [А] А (r, у) ? R } }.
Операция деления удобна тогда, когда требуется сравнить некоторое множество характеристик отдельных атрибутов. Например, пусть у нас есть отношение R7, которое содержит номенклатуру всех выпускаемых деталей на нашем предприятии, а в отношении R10 хранятся сведения о том, что и в каких цехах действительно выпускается. Поставим задачу определить перечень цехов, в которых выпускается вся номенклатура деталей.
Тогда решением этой задачи будет операция деления отношения R10 на отношение R7 по набору атрибутов (Шифр детали, Наименование детали).
60
R17 = R10[Шифр детали, Наименование детали: Шифр детали, Наименование детали]R7
| R 17 |
| Цех |
| Цех1 |
1. Построим отношение, которое моделирует ситуацию, когда в каждом цеху изготавливается вся номенклатура, это уже построенное нами ранее расширенное декартово произведение отношений R7 и R8. Это отношение R9:
R9 = R7?R8
2. Теперь найдем перечень того, что из обязательной номенклатуры не выпускается в некоторых цехах
R11 = R9\R10
3. Далее найдем те цеха, в которых не все детали выпускаются, для этого нам надо отношение R11 спроектировать на столбец "Цех":
R18 = R11[Цех]
| R18 |
| Цех |
| Цех 2 |
| Цех 3 |
Посмотрим, как работают операции реляционной алгебры для другого примера. Возьмем набор отношений, которые моделируют сдачу сессии студентами некоторого учебного заведения. Тема весьма понятная и привычная.
R1 = ; R2 = ;
R3 = < Группы, Дисциплина>,
где R1 - информация о попытках (как успешных, так и неуспешных) сдачи экзаменов студентами; R2 - состав групп; R3 - список дисциплин, которые надо сдавать каждой группе. Домены для атрибутов формально задавать не будем, но, ориентируясь на здравый смысл, будем считать, что доменом для атрибута Дисциплина будет множество всех дисциплин, преподающихся в ВУЗе, доменом для атрибута Группа будет множество всех групп ВУЗа и т. д.
61
Покажем, каким образом можно получить из этих таблиц интересующие нас сведения с помощью реляционной алгебры. В каждом из приведенных примеров путем операций над исходными отношениями R1, R2, R3 формируются промежуточные отношения и результирующее отношение S, содержащее требуемую информацию.
Список студентов, которые едали экзамен по БД на "отлично". Результат может быть получен применением операции фильтрации по сложному условию к отношению R1 и последующим проектированием на атрибут "ФИО" (нам ведь требуется только список фамилии).
S = (R1[Оценка = 5 ? Дисциплина = "БД"])[ФИО];
Список тех, кто должен был сдавать экзамен по БД, но пока еще не сдавал. Сначала найдем всех, кто должен был сдавать экзамен по БД. В отношении R3 находится список всех дисциплин, по которым каждая группа должна была сдавать экзамены, ограничим перечень дисциплин только "БД". Для того чтобы получить список студентов, нам надо соединить отношение R3 с отношением R2, в котором определен список студентов каждой группы.
R4 = (R2[R3.Номер Группы = R2.Номер Группы ?
R3.Дисциплина = "БД"] R3)[ФИО];
Теперь получим список всех, кто сдавал экзамен по "БД" (нас пока не интересует результат сдачи, а интересует сам факт попытки сдачи, то есть присутствие в отношении R1):
R5 = (R1[Дисциплина = "БД"])[ФИО];
и, наконец, результат - все, кто есть в первом множестве, но не во втором:
S = R4\ R5;
Список несчастных, имеющих несколько двоек:
S = (R1[R1.ФИО = R?1.ФИО ? R1.Дисциплина ? R?1 Дисциплина ?
R1Оценка ? 2 ? R?1. Оценка ? 2] R?1)[ФИО]
Этот пример весьма интересен: для поиска строк, удовлетворяющих в совокупности условию больше одного, применяется операция соединения отношения с самим собой. Поэтому мы как бы взяли копию отношения R1 и назвали ее R?1.
Список круглых отличников. Строим список всех пар , которые в принципе должны быть сданы:
R4 = (R2[R2.Группа = R3.Группа] R3)[ФИО, Дисциплина];
Строим список пар , где получена оценка "отлично":
R5 = (R1[Оценка = 5])[ФИО, Дисциплина];
Строим список студентов, что-либо не сдавших на "отлично":
R6 = (R4\R5)[ФИО].
62
Наконец, исключив последнее отношение из общего списка студентов, получаем результат:
R2[ФИО] \ R6
Обратите внимание, что для получения множества студентов, что-либо не сдавших на "отлично" (R6), мы осуществили "инверсию" множества всех отлично сданных пар (R5) путем вычитания его из предварительного построенного универсального множества (R4) Рекомендуем очень внимательно разобрать этот пример и вникнуть в смысл каждого действия -это очень пригодится для понимания реляционной алгебры.
Средства изменения описания таблиц и средства удаления таблиц
В язык SQL добавлены средства изменения схемы таблиц. Что можно и что нельзя изменять в описании таблицы? В стандарте SQL2 добавлены достаточно широкие возможности по модификации уже существующих схем таблиц. Для модификации таблиц используется оператор ALTER TABLE, который позволяет выполнить следующие операции изменения для схемы таблицы:
добавить новый столбец в уже существующую и заполненную таблицу;
изменить значение по умолчанию для какого-либо столбца;
удалить столбец из существующей таблицы;
добавить или удалить первичный ключ таблицы;
добавить или удалить новый внешний ключ таблицы;
добавить или удалить условие уникальности;
добавить или удалить условие проверки для любого столбца или для таблицы в целом.
Синтаксис оператора ALTER TABLE:
::= ALTER TABLE
{ ADD |
151
ALTER {SET DEFAULT
DROP DEFAULT } |
DROP {CASCADE | RESTRICT} |
ADD { определение первичного ключа>|
|
|
} |
DROP CONSTRAINT имя условия { CASCADE |
RESTRICT}
}
Одним оператором ALTER TABLE можно провести только одно из перечисленных изменений, например, за один раз можно добавить один столбец. Если вам требуется добавить два столбца, то необходимо применить два оператора.
Давайте рассмотрим несколько примеров. Чаще всего применяется операция добавления столбца. Предложение определения нового столбца в операторе ALTER TABLE имеет точно такой же синтаксис, как и в операторе создания таблицы. Добавим столбец EDUCATION (образование), содержащий символьный тип данных, с заданным перечнем значений ("начальное", "среднее", "неоконченное высшее", "высшее").
ALTER TABLE READERS
ADD EDUCATION varchar (30) DEFAULT NULL
CHECK (EDUCATION IS NULL OR
EDUCATION= "начальное" OR
EDUCATION= "среднее " OR EDUCATION= "неоконченное высшее" OR
EDUCATION= "высшее" )
В таблицу READERS будет добавлен столбец EDUCATION, в который по умолчанию будут добавлены все кортежи неопределенного значения.
В дальнейшем эти значения могут быть заменены на одно из допустимых символьных значений.
Добавим ограничение на соответствие между датами взятия и возврата книги в таблице EXEMPLAR. Действительно, если даты введены, то требуется, чтобы дата возврата книги была бы больше на срок выдачи книги. Считаем, что стандартным сроком являются 2 недели. Теперь сформулируем оператор изменения таблицы EXEMPLARE:
ALTER TABLE EXEMPLARE
ADD CONSTRAINT CK_ EXEMPLARE CHECK ((DATA_IN IS NULL AND DATA_OUT IS NULL) OR
(DATA_OUT >= DATA_IN +14) )
Здесь мы применили операцию сложения к календарной дате, которая предполагает, что добавляют заданное число дней.
Операция удаления столбца связана с проверкой ссылочной целостности, и поэтому не разрешается удалять столбцы, связанные с поддержкой ссылочной
152
целостности таблицы, то есть нельзя удалить столбцы родительской таблицы, входящие в первичный ключ таблицы, если на них есть ссылки в подчиненных таблицах.
При изменении первичного ключа таблицы следует быть внимательными. Во-первых, у исходной таблицы могут быть подчиненные, при этом первичный ключ исходной таблицы является внешним ключом для подчиненных таблиц, и просто его удалить невозможно, СУБД контролирует ссылочную целостность и не позволит выполнить операцию удаления первичного ключа таблицы, если на него имеются ссылки. Следовательно, в этом случае порядок изменения первичного ключа должен быть таким, как на рис. 8.1:
Рис. 8.1. Алгоритм изменения первичного ключа таблицы
153
Чаще всего операция ALTER TABLE применяется в CASE - системах при автоматической генерации скриптов создания таблиц в базе данных. В этих системах универсальный алгоритм предполагает сначала создание всех таблиц, которые заданы в даталогической модели, и только после этого добавляются соответствующие связи. И это понятно - в отличие от человеческого разума искусственный интеллект CASE - системы будет испытывать затруднения в определении иерархических взаимосвязей таблиц базы данных, поэтому он предпочитает использовать универсальный алгоритм, в котором сначала все объекты определяются, а затем добавляются соответствующие свойства для атрибутов, которые являются внешними ключами с указанием требуемых ссылок.
В этом случае все операции назначения внешних ключей будут считаться корректными, потому что все объекты были описаны заранее, и для такого алгоритма порядок создания таблиц безразличен. Далее приведен скрипт, который был получен при разработке схемы базы данных "Библиотека" в Power Designers. 1. По умолчанию для каждой таблицы создается индекс по первичному ключу, так что кроме знакомых операций создания и изменения таблиц мы увидим еще и операцию создания индексов (CREATE INDEX), после изучения физических моделей в базах данных мы еще вернемся к этой операции, а пока примем ее на веру. При создании даталогичекой модели в качестве СУБД был выбран сервер MS SQL Server 6.X, и для этого сервера скрипт был сгенерирован надстроенном языке этой СУБД, называемом TransactSQL. В нем операция USE соответствует операции открытия базы данных, а команда до означает переход к выполнению следующей команды.
/* ================================================================= */
/* Database name: LIBRARY */
/* DBMS name: Microsoft SQL Server 6.x */
/* Created on: 06.10.00 18:56 */
/* ================================================================= */
/* ================================================================= */
/* Database name: LIBRARY */
/* ================================================================= */
use LIBRARY
go
/* ================================================================= */
/* Table: BOOKS */
/* ================================================================= */
create table BOOKS
(
ISBN varchar(14) not null.
TITLE varchar(255) not null.
AUTOR varchar(3O) null.
COAUTOR varchar(3O) null.
PUBLICHER varchar(3O) null.
154
WHERE_PUBLICH varchar(30) null.
YEAR_IZD smallint not null
constraint CKC_YEAR_IZD_BOOKS check (
YEAR_PUBL >= 1969 AND YEAR_PUBL
PAGES small int not null
constraint CKC_PAGES_BOOKS check (
PAGES between 5 and 1000).
constraint PK_BOOKS primary key (ISBN),
constraint CKT_BOOKS check (
(AUTOR IS NOT NULL OR (AUTOR IS NULL AND COAUTOR IS NULL)))
)
go
/* ================================================================= */
/* Table: READERS */
/* ================================================================= */
create table READERS
(
NUM_READER intnot null.
NAME varchar(30) not null.
BIRTH_DAY datetime not null
constraint CKC_BIRTH_DAY_READERS check (
(DateDiff(year. GetDate().BIRTH_DAY) >=17 )),
SEX char(1) not null
constraint CKC_SEX_READERS check (
SEX in (?М?.?Ж?.?н?.?ж?)).
HOME_PHON char(9) null.
WORK_PHON char(9) null.
constraint PKJEADERS primary key (NUM_READER).
constraint CKT_READERS check (
( HOME_PHON IS NOT NULL OR WORK_PHON IS NOT NULL))
)
go
/* ================================================================= */
/* Table: CATALOG */
/* ================================================================= */
create table CATALOG
(
KW_KOD smallint not null.
155
NAME_KW varchar(255) null
constraint PK_CATALOG primary key (KW_KOD)
)
go
/* ================================================================= */
/* Table EXEMPLAR */
/* ================================================================= */
create table EXEMPLAR
(
INV_NUMER intnot null.
ISBN varchar(14) not null
NUM_READER int null.
PRESENT bitnot null.
DATE_IN datetime null.
DATE_OUT datetime null.
constraint PK_EXEMPLAR primary key (INV_NUMER)
)
go
/* ================================================================= */
/* Index RELATION_43_FK */
/* ================================================================= */
create index RELATION_43_FK on EXEMPLAR (ISBN)
go
/* ================================================================= */
/* Index RELATION_44_FK */
/* ================================================================= */
create index RELATION_44_FK on EXEMPLAR (NUM_READER)
go
/* ================================================================= */
/* Table RELATION_67 */
/* ================================================================= */
create table RELATION_67
(
ISBN varchar(14) not null.
KW_KOD smallint not null.
constraint PK_RELATION_67 primary key (ISBN. KW_KOD)
)
go
156
/* ================================================================= */
/* Index: IOIINEONYJ_IAEANOE_CIAIEE_FK */
/* ================================================================= */
create Index IOIINEONY_E_IAEANOE_CIAIEE_FK on RELATION_67 (ISBN)
go
/* ================================================================= */
/* Index: I_AANOAAEAIA_A_EIEAAO_FK */
/* ================================================================= */
create Index I_AANOAAEAIA_A_EIEAAO_FK on RELATION_67 (KW_KOD)
go
alter table EXEMPLAR
add constraint FK_EXEMPLAR_RELATION_BOOKS foreign key (ISBN)
references BOOKS (ISBN)
go
alter table EXEMPLAR
add constraint FK_EXEMPLAR_RELATION_READERS foreign key (NUM_READER)
references READERS (NUM_READER)
go
alter table RELATION_67
add constraint FK_RELATION_IOIINEONY_BOOKS foreign key (ISBN)
references BOOKS (ISBN)
go
alter table RELATION_67
add constraint FK_RELATION_I_AANOAAE_CATALOG foreign key (KW_KOD)
references CATALOG (KW_KOD)
go
В языке SQL присутствует и операция удаления таблиц. Синтаксис этой операции предельно прост:
::= DROP TABLE [CASCADE | RESTRICT]
Параметр CASCADE означает, что при удалении таблицы одновременно удаляются и все объекты, связанные с ней. С таблицей, кроме рассмотренных ранее ограничений, могут быть связаны также объекты типа триггеров и представления. Понятие представления будет рассмотрено в следующем подразделе, а триггеров мы коснемся в разделах, связанных с архитектурой клиент-сервер. Однако операция удаления объектов определяется еще правами пользователей, что связано с концепцией безопасности в базах данных. Это значит, что если вы не являетесь владельцем объекта, то вы можете не иметь прав на его удаление. И в этом случае синтаксически правильный оператор DROP TABLE не может быть выполнен системой в силу отсутствия прав на удаление связанных с удаляемой
157
таблицей объектов. Кроме того, операция удаления таблицы не должна нарушать целостность базы данных, поэтому удалять таблицу, на которую имеются ссылки других таблиц, невозможно,
Например, в нашей схеме, связанной с библиотекой, мы не можем удалить ни таблицу BOOKS, ни таблицу READERS, ни таблицу CATALOG. У этих таблиц есть связь с подчиненными таблицами EXEMPLAR и RELATION_67. Поэтому если вы хотите удалить некоторый набор таблиц, то сначала необходимо грамотно построить последовательность их удаления, которая не нарушит базовых принципов поддержки целостности вашей схемы БД. В нашем примере последовательность операторов удаления таблиц может быть следующей:
DROP TABLE EXEMPLAR
DROP TABLE RELATION_67
DROP TABLE CATALOG
DROP TABLE READERS
DROP TABLE BOOKS
Средства определения схемы базы данных
В стандарте SQL1 задается спецификация оператора описания схемы базы данных, но не указывается способ создания собственно базы данных, поэтому в различных СУБД используются неодинаковые подходы к этому вопросу.
Например, в СУБД ORACLE база данных создается в ходе установки программного обеспечения собственно СУБД. Все таблицы пользователей помещаются в единую базу данных. Однако они могут быть разделены на группы, объединенные в подсхемы. Понятие подсхемы не стандартизировано в SQL и не используется в других СУБД.
В состав СУБД INGRES входит специальная системная утилита, имеющая имя CREATEDB, которая позволяет создавать новые базы данных. Права на использование этой утилиты имеет администратор сервера. Для удаления базы данных существует соответствующая утилита DESTROYDB.
В СУБД MS SQL Server существует специальный оператор CREATE DATABASE, который является частью языка определения данных, для удаления базы данных
149
в языке определен оператор DROP DATABASE. Правами на создание баз данных наделяются администраторы баз данных, которых в общем случае может быть несколько. Правами более высокого уровня обладает администратор сервера баз данных (SQL Server), который и может предоставить права администратора базы данных другим пользователям сервера. Администраторы баз данных могут удалить только свою базу данных. Приведем пример оператора создания схемы базы данных в MS SQL Server 7.0:
CREATE DATABASE database_name
[ON [PRIMARY][[...n]][. [...n]]]
[ LOG ON { [....n]} ][ FOR LOAD | FOR ATTACH ]
:: =
( [ NAME = логическое имя файла,]FILENAME = ?физическое имя файла?
[. SIZE - размер][, MAXSIZE - { максимальный размер | UNLIMITED } ]
[. FILEGROWTH = инкремент увеличения файла] ) [...n]
::= FILEGROUP имя группы файлов спецификация файла> [...n]
Здесь
database_name - имя базы данных, идентификатор в системе;
ON - ключевое слово, которое означает, что далее будут заданы спецификации файлов, которые будут использованы для размещения базы данных;
PRIMARY - ключевое слово, которое определяет первичное файловое пространство, в котором будет размещена собственно база данных;
LOG ON - ключевое слово, которое задает спецификацию файлов, которые будут использованы для хранения журналов транзакций;
FOR LOAD - ключевое слово, которое определяет, что после создания базы данных будет произведена загрузка базы данных данными;
FOR ATTACH - предложение, которое определяет, что база данных для управления будет подсоединена к другому серверу.
Почти все параметры, кроме имени базы данных, являются необязательными, поэтому оператор создания простой базы данных "Библиотека" может выглядеть следующим образом:
CREATE DATABASE Library
Для изменения схемы базы данных в MS SQL Server 7.0 может быть использована команда:
ALTER DATABASE database
{ ADD FILE [...n] [TO FILEGROUP filegroup_name]
| ADD LOG FILE [...n]
| REMOVE FILE имя_файла
| ADD FILEGROUP имя_группы файлов
[REMOVE FILEGROUP имя группы_файлов
150
|MODIFY FILE
|MODIFY FILEGROUP имя_группы_файлов имя_свойства_группы файлов}
Здесь свойства группы файлов определяет одно из допустимых ключевых слов:
READONLY - только для чтения;
READWRITE - для чтения и записи;
DEFAULT - назначает данную группу файлов в качестве группы по умолчанию, в которой размещаются данные, если не задано дополнительных условий размещения информации.
Как видно, при изменении схемы базы данных в нее могут быть добавлены (ADD) дополнительные файлы и файловые группы или удалены (REMOVE ) ранее определенные файлы или файловые группы. Назначение этих файлов нам будет более понятно после того, как мы познакомимся с физическими моделями и файловыми структурами, используемыми для хранения данных в базах данных.
Сейчас мы познакомимся с последней командой, которая предназначена для удаления базы данных. В MS SQL Server 7.0 это команда имеет следующий синтаксис:
DROP DATABASE database_name
После выполнения этой команды уничтожается вся база данных вместе с содержащимися в ней данными.
Страницы данных
На самом общем уровне здесь, так же как и в предыдущей версии, страница данных делится на 3 зоны: заголовок, область данных и таблицу смещений, но размер страницы увеличен, размер заголовка также и некоторые отличия существуют и в структуре остальных зон.
Прежде всего стоит отметить, что в отличие от SQL Server 6.5 в новой версии страницы данных не связаны друг с другом в цепочки. За связь между страницами и объектами отвечает новая специальная структура - карты размещения.
Кроме того, ранее данные на странице хранились непрерывно. При удалении строки данные внутри страницы перемещались так, чтобы не оставалось пустот. Однако такой подход затруднял строчные блокировки. И в новой версии данные на странице не обязательно хранятся непрерывно. Здесь допустимы пропуски. При удалении строки пустое пространство помечается и потом его может занять новая строка, но перемещения строк не происходит.
В SQL Server 7.0 теперь поддерживается классический термин слот (Slot), и это место размещения строки на странице. Если таблица не имеет кластеризованного индекса, то номер слота является идентификатором строки и не меняется, пока не будет удалена соответствующая строка. Если же таблица имеет кластеризованный индекс, то слоты располагаются в порядке, задаваемом индексом.
