- •Краткое введение в субд - базы данных как аппарат моделирования.
- •Язык sql FoxPro 5.0
- •Создание бд
- •Модификация структуры бд
- •Модификация содержимого таблиц
- •Запросы к бд - команда select. Представления
- •Примерные типы заданий.
- •Тема 3. Задачи-многоходовки. Представления и транзакции.
- •Тема 4. Проектирование "реальной" бд.
“beta-версия”
© Н.Р.Бухараев. ПРАКТИКУМ ПО СУБД
От автора.
Данное методическое пособие предназначено для преподавателей, ведущих практические занятия по курсу “Введение в СУБД. Язык структурированных запросов SQL”, читаемого для студентов второго курса факультета ВМК.
Как показывает практика, системы управления баз данных (СУБД) составляют подавляющую часть всех программных продуктов – при этом часть, все более увеличивающуюся в связи с развитием локальных и, в особенности, глобальных компьютерных сетей. При этом язык структурированных запросов (Structured Query Language – SQL) играет de facto роль стандарта коммуникации разнообразных, в том числе - работающих под управлением различных операционных систем СУБД, и потому становится неотъемлимой составной частью любой современной СУБД.
Сказанное объясняет несомненную важность упомянутого курса. К сожалению, литература по данной тематике либо недоступна ( в разных смыслах этого слова) преподавателям университета, либо не отвечает предъявляемым ими критериям – в первую очередь, традиционно сложившимися в КГУ высоким стандартам математической фундаментальности обучения.
Для того, чтобы хоть отчасти восполнить недостаток необходимой справочной литературы и задачников по СУБД, автор включил в настоящее пособие краткое теоретическое введение в терминологию СУБД, синтаксис языка SQL, встроенного в язык СУБД Visual FoxPro 5.0 и типовые задания по СУБД. Последние, не являясь законченными учебными заданиями позволят, как надеется автор, послужить материалом для генерации таких заданий самими преподавателями.
Автор заранее благодарен за все замечания и пожелания коллег и студентов, направленных на улучшение данного пособия как по содержанию, так и стилю изложения. Особенно, он благодарит Р.Тагирова и В.Байрашеву, уже сделавших ряд ценных замечаний подобного рода.
Краткое введение в субд - базы данных как аппарат моделирования.
Принципиальная особенность и цель СУБД - полное и точное формальное описание ( моделирование) объектов и процессов некоторой реальной ситуации (предметной области) в виде совокупности взаимосвязанных таблиц. Стандартная физическая реализация таблиц - файлы, т.е. постоянные и "большие" наборы данных на внешних носителях, поэтому такое описание должно быть, по возможности, минимальным и эффективным, направленным на экономию памяти при хранении файлов и высокую скорость их обработки.
Под таблицей (table) обычно понимают n-арное отношение Т, т.е. произвольное множество n-ок (строк или записей таблицы), компоненты которых в СУБД принято называть столбцами или полями записи.
T={r=<c1,…,cn>: c1D1,… cnDn}
В дополнение к этому чисто теоретико-множественному определению отметим, что в СУБД столбцы и строки таблиц неравноправны и имеют разный смысл: столбцы именуются и представляют структуру хранящейся в таблице информации, строки же представляют содержание этой информации и либо нумеруются (в языках СУБД, ориентированных на преобразования отдельных записей – таких, например, как языки семейства Xbase, к которым, в частности, относится язык FoxPro), либо не именуются вообще ( в языках СУБД, ориентированных на преобразование таблиц в целом – таких, как изучаемый нами язык SQL). В последнем случае единственным способом косвенного именования является выделение одной или нескольких записей заданием предиката, которому они удовлетворяют.
В любом случае, СУБД предусматривают средства упорядочения записей, поэтому таблицы часто удобнее трактовать как последовательности кортежей - строк таблицы.
Поля записи могут содержать лишь значения базовых, или - скалярных типов и, в частности, общее для всех таких типов неопределенное значение NULL.
С точки зрения логики моделирования, таблица представляет некоторый класс единообразно описываемых объектов, а поля записей – атрибуты (характеристики, свойства) этих объектов.
Пример описания классов объектов в виде таблиц.
ЛЮДИ =
{<номер_паспорта, фамилия_имя_отчество, пол, доход,... (другие атрибуты людей)>}
ДОМА =
{<номер_дома, название_улицы, город, число_этажей,...(другие атрибуты домов)>}
Очевидно, что при моделировании мы не пытаемся полностью отразить наше понимание объектов (“что есть человек?”), но руководствуемся принципом минимальной достаточности, т.е. отбираем наиболее существенные, в контексте данной задачи, атрибуты описываемого объекта.
Отметим, что, в отличие от привычного описания сложных типов в процедурных языках, определение таблицы не рекурсивно – таблицы не могут содержать в качестве полей другие таблицы. Для описания нетривиальных, сложно структурированных объектов используется механизм отношений - ссылок или связей между таблицами.
Если PK(r) - некоторое выражение над полями таблицы PT, можно считать, что PK различает две записи r1,r2PT, если PK(r1)PK(r2). PK называется (первичным) ключом (primary key) таблицы, если оно, позволяет идентифицировать каждую запись таблицы, отличить ее других:
r1,r2 PT ( r1=r2 PK(r1)=PK(r2))
В самом простом, но часто встречающемся, случае ключом таблицы служит значение одного из ее полей, в противном случае ключ называется составным. По договоренности, все поля записи образуют ее первичный ключ (проще говоря, таблица не может содержать двух полностью совпадающих записей).
Так, в предыдущем примере номер паспорта (но не фамилия!) – первичный ключ таблицы ЛЮДИ, а первые три поля таблицы ДОМА (адрес дома) образуют составной первичный ключ этой таблицы.
Далее, пусть FK(r) - выражение над полями другой таблицы CT. Запись r1, r1CT ссылается (references) на запись r2, r2PT, если FK(r1)=PT(r2). FK называется внешним ключом (foreign key) таблицы CT, если каждая запись в CT имеет соответствие в PT:
r1 CT r2PT (FK(r1)=PT(r2))
В этом случае таблица PT называется родительской (parent table), таблица CT - дочерней (child table), а соответствуюшее отношение - связью "многие-ко-многим". Если, к тому же, PT - первичный ключ таблицы PT и, соответственно, каждая запись CT ссылается на единственную запись PT, то получающееся функциональное отношение называют связью "многие-к-одному". Наконец, если эта функция инъективна, т.е. не принимает одинаковых значений на разных аргументах, соответствующую связь называют связью "один-к-одному".
Так, для того, чтобы отразить в нашей модели отношения вида “(Этот) человек живет в (этом) доме”, мы должны будем внести значение первичного ключа таблицы ДОМА (адрес дома) в соответствующие поля таблицы ЛЮДИ.
Отметьте, что вполне корректный логически, наш выбор слишком большего, по объему занимаемой памяти, первичного ключа в родительской таблице ДОМА не оправдан технически, что заставляет нас прибегнуть к стандартному в проектировании БД приему сжатия информации - кодировке.
Так, например, мы можем
а) добавить в таблицу ДОМА дополнительное поле код_дома – не отражающий никаких реальных атрибутов объекта, искусственный первичный ключ таблицы;
б) завести дополнительные справочные таблицы
УЛИЦЫ={<код_улицы, название_улицы>} и
ГОРОДА={<код_города,название_города>},
сопоставляющую длинным названиям улиц и городов некоторые краткие коды с тем, чтобы иметь возможность хранить в таблицах ДОМА и ЛЮДИ более краткие ключи вида
<номер_дома, код_улицы, код_города>
в) связать улицы с номерами имеющихся на них домов, а города – с названиями имеющихся в нем улиц, определив структуру введенных таблиц следующим образом
ЛЮДИ={<код_дома,...(другие атрибуты людей)>}
ДОМА={<код_дома, номер_дома, код_улицы,...(другие атрибуты домов)>},
УЛИЦЫ={<код_улицы, название_улицы, код_города>},
ГОРОДА={<код_города, название_города>},
Будем считать в дальнейшем, что мы приняли последний вариант решения (обоснование такого выбора см. ниже).
В том крайне нежелательном случае, когда подразумевается наличие связи между таблицами, но в реальности некоторая запись дочерней таблицы не ссылается ни на одну запись родительской таблицы, то такую запись называют сиротой (orphan). По договоренности, запись с неопределенным, т.е. равным NULL, ключом сиротой не считается.
Так, в нашем примере запись в таблице ЛЮДИ будет сиротой, если она содержит ссылку на не существующий адрес дома – отсутствующее в таблице ДОМА значение поля код_дома.
Функциональные связи наиболее популярны в СУБД. Связи "многие-ко-многим", т.е. отношения общего вида, трудны для понимания и обработки - их обычно стараются представить в виде нескольких отношений "один-ко-многим". Наличие связи "один-к-одному" означает, как правило, что удобнее иметь дело не с двумя, а с одной таблицей - их композицией по этому соответствию - и применяется лишь в случаях, когда лишь небольшое число записей одной таблице имеет соответствие в другой (отношение типа “быть подмножеством”) .
В нашем модели-примере, очевидно, мы имеем дело со связью “один-ко-многим” между таблицами ЛЮДИ и ДОМА – в одном доме может жить много людей, но один человек не может считаться живущим в нескольких домах.
Осуществив разбиение (декомпозицию) информации о связи между домами и улицами, а также улицами и городами (которую мы намеревались первоначально хранить в таблице ДОМА) и вынеся ее в отдельные таблицы УЛИЦЫ и ГОРОДА, мы получили новые связи “один-ко-многим” между таблицами УЛИЦЫ-ДОМА и ГОРОДА-УЛИЦЫ.
Вернемся к поднятому ранее вопросу выбора вариантов кодирования (сжатия) информации. Очевидно, такое сжатие будет тем более эффективным, чем больше его “коэффициент” - среднее количество дочерних записей, приходящихся на одну родительскую запись. В худшем случае – связи “один-ко-одному” – мы в реальности лишь увеличим объем хранимой информации за счет введения избыточных кодов. Таким образом, (весьма нелегкая в практическом воплощении) стратегия определения структуры БД должна преследовать, помимо первичной цели моделирования, и цель максимизации коэффициента сжатия.
Базой данных (БД) называется формальная модель некоторой предметной области в виде совокупности таблиц, связанных отношениями.
Для того, чтобы в процессе модификации - вставки (insert), обновления (update) и удаления (delete) записей - БД не перестала адекватно отражать реальное содержание предметной области, необходимо выполнение следующих требований:
таблицы БД не должны содержать нереальных данных, которые могут появиться как следствие ошибок программ обработки БД или некорректного ввода. Для этого в определение таблиц БД добавляют правила достоверности (validation rules) значений поля или группы полей, определяющие необходимые условия их корректности;
так, например, значение поля номер_дома в таблице ДОМА должен принадлежать некоторому допустимому интервалу натуральных чисел – скажем, от 1 до 1000 ;
БД должна удовлетворять условию ссылочной целостности (referential integrity), т.е. в таблицы БД, - в результате тех же причин - не должны попадать записи-"сироты" , Следовательно, модификация связанных таблиц должна быть согласованной. Логически, возможны несколько вариантов такого согласования, а именно - модификация записи родительской таблицы может быть
а) каскадной (cascades), т.е. продолжаться на соответствующие записи дочерней таблицы;
b) нулевой (nulls), т.е. устанавливать ключи дочерних записей в NULL;
c) ограниченной (restricted), т.е. исполняемой лишь в тех случаях, когда у нее нет ни одной дочерней записи.
Как и правила проверки, варианты сохранения ссылочной целостности БД также являются частью ее определения. Процедуры, автоматически отслеживающие действия пользователя по модификации БД, и реализующие соответствующий вариант согласования, называются триггерами (от английского trigger – спусковой крючок).
Так, в нашем примере наиболее естественно принять ограниченный вариант удаления – информация о доме не может быть удалена, пока таблица ЛЮДИ содержит хотя бы одну запись о человеке, в этом доме проживающем, и каскадный вариант модификации – изменение первичного ключа в любой записи таблицы ДОМА ( т.е. одного из полей номер_дома, код_улицы, код_города) должно автоматически изменять атрибуты соответствующих записей дочерней таблицы ЛЮДИ.
Помимо проблем логической целостности и компактного хранения данных, проблемы обеспечения высокой скорости доступа к данным также играют в СУБД важную роль. Эти проблемы решаются в за счет применения тех или иных вариантов дихотомического поиска в таблицах, требующих, в свою очередь, компактного хранения нужного порядка расположения записей таблиц в отдельных индексных файлах. Исходя из предполагаемой частоты тех или иных запросов, пользователь СУБД может затребовать создания ускоряющих выполнение этих запросов индексных файлов. В силу своей особой роли ключевые поля таблиц, как правило, индексируются автоматически.
Подытоживая определение базы данных, еще раз подчеркнем, что, помимо собственно данных в виде таблиц и индексов, БД хранит также программный код проверки правил достоверности и триггеров. Таким образом, в понятии БД нетрудно усмотреть начала объектного подхода, хотя само понятие объектно-ориентированной СУБД в настоящее время еще находится в стадии становления.
Все основные команды СУБД можно рассматривать как преобразователи одной или нескольких таблиц, результатом которых снова является таблица (новая или обновленная старая):
T := (T1,…,Tn)
В отличие от команд модификации БД, результатом команды запроса к БД является абстрактная, виртуальная таблица, не существующая физически в виде постоянно хранимого файла, но лишь отражающей текущее состояние реально физически хранимых, или – базовых таблиц БД. Цель использования таких таблиц ясна – отобразить в развернутом и полном виде информацию о реальных объектах и отношениях предметной области, выводимую из компактно закодированной информации, содержащейся в базовых таблицах.
Так, полная информация о домах должна включать не о введенных нами в целях экономии хранения кодах улиц и городов, но об их реальных названиях, хранящихся в базовых справочных таблицах УЛИЦЫ и ГОРОДА и, конечно, другие атрибуты домов из базовой таблицы ДОМА.
Другим примером может служить запрос на порождение отношения СОСЕДИ -“(эти) люди живут в одном доме”, выразимого в терминах имеющегося отношения “(этот) человек живет в (этом) доме”.
Представления – тип абстрактных таблиц, определение (но не содержимое!) которых храниться в виде именованных команд запроса как часть БД. Подобно базовым таблицам, представления могут служить аргументами других запросов и представлений, позволяя таким образом пользователю БД определять сложные родо-видовые иерархии объектов предметной области.
Так, в нашей модели-примере естественно определить представление ПОЛНАЯ_ИНФОРМАЦИЯ_О_ДОМАХ – именованном запросе, “хранящем” раскодированную по таблицам ДОМА, УЛИЦЫ и ГОРОДА информацию о домах в виде, естественном для пользователя БД, и представление ПОЛНАЯ_ИНФОРМАЦИЯ_О_ЛЮДЯХ, “содержащую” (с точки зрения пользователя БД) информацию о людях, включающую ссылки на представление ПОЛНАЯ_ИНФОРМАЦИЯ_О_ДОМАХ и базовую таблицу ЛЮДИ.
Можно ли относиться к абстрактным таблицам также, как к реальным? “Содержимое” модифицируемых представлений можно, по определению, редактировать с помощью команд модификации – но поскольку такового, в реальности, не существует, последнее означает модификацию базовых таблиц
Так, изменение, номера дома в некоторой записи представления ПОЛНАЯ_ИНФОРМАЦИЯ_О_ЛЮДЯХ в реальности может означать лишь изменение значения дочернего ключа код_дома в таблице ЛЮДИ.
По существу, модификация, т.е. преобразование представления T, основанном на запросе S к базовым таблицам T1,...,Tn, T=S(T1,..,Tn), к некоторому новому состоянию T’, означает требование к СУБД найти такое модификацию (преобразование) базовых таблиц к новому состоянию T1’,...,Tn’, которое вело бы к желаемому содержимому представления, T’=S(T1’,..,Tn’).
Понятно, что такое обратное преобразование технически трудно реализуемо и, даже теоретически, может быть неоднозначным или просто не существовать; помимо этого различные СУБД накладывают дополнительные ограничения на критерий модифицируемости, исходя из соображения эффективности генерации требуемой модификации.
Пример неосуществимости модификации представления. Заведем в нашей модели-примере представление, содержащее единственное поле – средний доход людей, информация о которых хранится в таблице БД. Как повысить средний доход – т.е. изменить значения поля доход в таблице ЛЮДИ? Как подсказывает математика (и наш жизненный опыт), решение этой задачи далеко не однозначно.
Безопасность данных играет в СУБД первостепенную роль. Помимо не рассматриваемого в данном пособии и часто достаточно изощренного аппарата администрирования БД (к которому относятся, в частности, вопросы архивации/восстановления физически поврежденных таблиц и/или индексов и ограничения прав доступа пользователей БД), безопасность данных обеспечивается механизмом поддержки транзакций.
Транзакция - логически единая операция по модификации базовых таблиц, состоящая из одной или нескольких команд СУБД, переводящая БД из одного корректного, с точки зрения предметной области, состояния в другое, также логически корректное. Поддержка транзакций гарантирует, что входящие в транзакцию команды либо выполнятся полностью, либо – если последнее невозможно по какой-либо причине (сбой энергоснабжения, конфликт действий пользователей в многопользовательской/ сетевой БД и пр.) – полное восстановление состояния БД на момент начала транзакции.
Примером транзакции может служить каскадное удаление записи родительской таблицы, с последующим удалением записей дочерних таблиц. Все эти удаления должны быть либо осуществлены полностью, либо не осуществлены вообще – в противном случае в БД могут появиться записи-сироты.