4. Физическое проектирование структуры базы данных

Во время логического проектирования была определена структура БД (отношения, их атрибуты и связи). Хотя логическая модель не зависит от конкретной целевой СУБД, она создавалась с учетом выбранной модели организации данных. Между логическим и физическим проектированием существует постоянная обратная связь, так как решения, принимаемые на этапе физического проектирования с целью повышения производительности системы, способны повлиять на структуру логической модели данных [4, 6].

Физическое проектирование БД – это процесс создания и описания реализации БД на вторичных запоминающих устройствах с указанием структур хранения и методов доступа, используемых для организации эффективной обработки данных.

Основной целью физического проектирования является описание способа физической реализации логического проекта. В случае реляционной модели данных это:
1) создание набора реляционных таблиц и определение ограничений для них на основе информации, представленной в глобальной логической модели данных;
2) определение конкретных структур хранения данных и методов доступа к ним, обеспечивающих оптимальную производительность БД;
3) разработка средств защиты создаваемой системы.

В идеале, фазу логического проектирования больших систем следует отделять от их физического проектирования. На это есть ряд причин:
1) они связаны с совершенно разными аспектами системы: что делать и как делать;
2) они выполняются в разное время, поскольку понять, что надо сделать, следует прежде, чем решить, как это сделать;
3) они требуют совершенно разных навыков и умений, которыми обычно обладают разные люди.

Приступая к физическому проектированию БД, прежде всего, необходимо выбрать конкретную целевую СУБД. Рассмотрим такие этапы её выбора:

1. Определение круга программных продуктов, из которого будет выбираться СУБД.
2. Сокращение списка претендентов до двух - трех продуктов.
3. Оценка продуктов.
4. Обоснование выбора единственного продукта.

Рассмотрим выбор продукта для изучения организации реляционных БД.

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

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

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

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

4.3.1. Правила Кодда – как эталон реляционной СУБД

На рынке присутствует несколько сотен реляционных СУБД. Вот лишь некоторые из них: Firebird [7]; Interbase [10, 14, 15]; Microsoft Access [16]; Microsoft SQL Server [17]; MySQL [18]; Oracle [19]; Visual FoxPro [20] и т.д. К сожалению, некоторые из них не соответствуют определению реляционной модели. Ряд поставщиков СУБД, основанных на сетевой или иерархической модели данных, реализуют в своих продуктах только некоторые черты реляционных систем. Это не мешает им заявлять, что такие системы относятся к реляционным. Озабоченный тем, что потенциальные возможности и смысл реляционного подхода искажаются, Едгар Кодд в 1985 году предложил 12 правил определения реляционных систем (а точнее 13, если учитывать фундаментальное нулевое правило). Эти правила образуют своего рода эталон, по которому можно определить принадлежность СУБД к разряду действительно реляционных систем.

Предложенные Коддом правила на протяжении многих лет вызывали массу нареканий со стороны заинтересованных лиц и специалистов. Одни сочли их не более чем чисто теоретическими упражнениями, другие заявили, что их продукты уже удовлетворяют многим этим правилам. Эта дискуссия способствовала росту понимания важнейших свойств действительно реляционных СУБД пользователями и сообществом разработчиков. Чтобы подчеркнуть особое значение этих правил, их разделили на пять функциональных групп [4].

1. Фундаментальные правила.
2. Структурные правила.
3. Правила целостности.
4. Правила управления данными.
5. Правила независимости от данных.

4.3.2. Фундаментальные правила (правила 0 и 12)

Образно выражаясь, правила 0 и 12 являются «лакмусовой бумажкой», которая позволяет определить принадлежность системы к реляционным СУБД. Если система не удовлетворяет этим правилам, то ее не следует считать реляционной.

Правило 0 – фундаментальное правило. Любая система, которая рекламируется или представляется как реляционная СУБД, должна быть способна управлять БД исключительно с помощью ее реляционных функций.

Это правило означает, что СУБД не должна прибегать к каким-либо не реляционным операциям для выполнения таких видов работ, как определение данных и манипулирование ими.

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

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

4.3.3. Структурные правила (правила 1 и 6)

Фундаментальным структурным понятием реляционной модели является отношение. Кодд утверждает, что реляционная СУБД должна поддерживать работу с несколькими структурными элементами: отношения, домены, первичные и внешние ключи. Для каждого отношения БД должен быть определен первичный ключ.

Правило 1 – представление информации. Вся информация в реляционной БД представляется в явном виде на логическом уровне и только одним способом — в виде значений в таблицах.

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

Правило 6 – обновление представления. Все представления, которые являются теоретически обновляемыми, должны быть обновляемы и в данной системе.

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

4.3.4. Правила целостности (правила 3 и 10)

В своих работах Кодд предложил два правила поддержки целостности данных. Поддержка целостности данных является важным критерием оценки пригодности системы для практических нужд. Чем больше ограничений целостности поддерживает сама СУБД, а не отдельные ее приложениями, тем выше гарантии качества данных.

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

Правило 10 – независимость ограничений целостности. Специфические для данной реляционной СУБД ограничения целостности должны определяться на подъязыке реляционных данных и храниться в системном каталоге, а не в прикладных программах.

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

4.3.5. Правила управления данными (правила 2, 4, 5 и 7)

Идеальная реляционная СУБД должна поддерживать 18 функций управления данными, которые определяют полноту языка запросов (здесь термин «запрос» включает и операции вставки, обновления и удаления). Правила манипулирования данными определяют способ применения 18 функций управления данными. Строгое следование этим правилам позволяет изолировать пользователя и прикладные программы от физического и логического механизмов реализации средств манипулирования данными.

Правило 2 – гарантированный доступ. Для всех и каждого элемента данных (т.е. его атомарного значения) реляционной БД должен быть гарантирован логический доступ на основе комбинации имени таблицы, значения первичного ключа и имени столбца.

Правило 4 – динамический интерактивный каталог, построенный по правилам реляционной модели. Описание БД должно представляться на логическом уровне таким же образом, как и обычные данные, что позволит санкционированным пользователям обращений к этому описанию тем же реляционным языком, что и к данным.

Это правило указывает на то, что должен существовать только один язык, предназначенный для манипулирования, как метаданными, так и обычными данными; причем в СУБД для организации хранения системной информации должна использоваться только одна логическая структура — отношение.

Правило 5 – исчерпывающий подъязык данных. Реляционная система может поддерживать несколько языков и различные режимы работы с терминалами (например, режим заполнения формы — fill-in-the-blanks). Однако должен существовать, по крайней мере, один язык, операторы которого позволяли бы выполнять операции: 1) определение данных; 2) определение представлений; 3) команды манипулирования данными (доступные как интерактивно, так и из программ); 4) ограничения целостности; 5) авторизация пользователей; 6) организация транзакций (запуск, фиксация и откат).

Следует отметить, что новый стандарт ISO для языка SQL обеспечивает выполнение всех этих функций таким образом, что любой поддерживающий этот стандарт язык автоматически будет удовлетворять и этому правилу.

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

4.3.6. Правила независимости от данных (правила 8, 9 и 11)

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

Правило 8 – физическая независимость от данных. Прикладные программы и средства работы с терминалами должны оставаться логически незатронутыми при внесении любых изменений в способы хранения данных или методы доступа к ним.

Правило 9 – логическая независимость от данных. Прикладные программы и средства работы с терминалами должны оставаться логически незатронутыми при внесении в базовые таблицы любых не меняющих информацию изменений, которые теоретически не должны затрагивать прикладное программное обеспечение.

Правило 11 – независимость от распределения данных. Подъязык манипулирования данными в реляционной СУБД должен позволять прикладным программам и запросам оставаться логически неизменными, независимо от того, как хранятся данные: централизованно или в распределенном виде.

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

4.4.1. Краткая характеристика языка SQL

Язык SQL является первым и пока единственным стандартным языком работы с БД, который получил достаточно широкое распространение [4, 6 – 8]. Есть еще один стандартный язык работы с БД – Network Data-base Language (NDL), который построен на использовании сетевой модели CODASYL, но применяется не во всех разработках. Практически все крупнейшие разработчики СУБД в настоящее время создают свои продукты с использованием языка SQL либо SQL-интерфейсом.

В идеале любой язык работы с БД должен предоставлять пользователю следующие возможности:
1) создавать БД и таблицы с полным описанием их структуры;
2) выполнять основные операции манипулирования данными, такие как вставка, модификация и удаление данных из таблиц;
3) выполнять простые и сложные запросы, осуществляющие преобразование необработанных данных в необходимую информацию;
4) использовать стандартный синтаксиз и структуру команд при переходе от одной СУБД к другой.

Кроме того, язык работы с БД должен решать все рассмотренные выше задачи при минимальных усилиях со стороны пользователя, а структура и синтаксис его команд должны быть достаточно просты и доступны для изучения. Язык SQL удовлетворяет практически всем этим требованиям.

SQL является примером языка с ориентацией на трансформацию, или же языка, предназначенного для работы с таблицами с целью преобразования входных данных к требуемому выходному виду. Язык SQL имеет два основных компонента:
1) язык DDL (Data Definition Language), предназначенный для определения структур БД и управления доступом к данным;
2) язык DML (Data Manipulation Language), предназначенный для выборки и обновления данных.

Язык SQL включает только команды определения и манипулирования данными. В нем отсутствуют какие-либо команды управления ходом вычислений. Другими словами, в этом языке нет команд IF ... THEN ... ELSE, GO TO, DO ... WHILE и т.п., предназначенных для управления ходом вычислительного процесса. Подобные задачи должны решаться с помощью языков программирования или интерактивно, как результат действий пользователя. По причине подобной незавершенности в плане организации вычислительного процесса язык SQL может использоваться двумя способами. Первый – предусматривает интерактивную работу, заключающуюся в вводе пользователем с терминала отдельных SQL-операторов. Второй – состоит во внедрении SQL-операторов в программы, которые написаны на процедурных языках.

Язык SQL относительно прост в изучении.

1. Это не процедурный язык, поэтому в нем необходимо указывать, какая информация должна быть получена, а не как ее можно получить. Иначе говоря, язык SQL не требует описания методов доступа к данным.
2. Как и большинство современных языков, SQL поддерживает свободный формат записи операторов. Это означает, что при вводе отдельные элементы операторов не связаны с фиксированными позициями экрана.
3. Структура команд задается набором ключевых слов, представляющих собой обычные слова и фразы английского языка, например, CREATE TABLE (создать таблицу), INSERT (вставить), SELECT (выбрать) и др.
4. Язык SQL может использоваться широким кругом специалистов, включая администраторов БД, руководящий персонал, прикладных программистов и множество других типов конечных пользователей.

На момент создания сайта язык SQL определяется международным стандартом ISO/IEC/ 9075 (1 – 4, 9 – 11, 13, 14):2008, что формально превращает его в стандартный язык определения и манипулирования реляционными БД. Однако следует отметить, что разработчики СУБД отклоняются от стандартов, или поддерживают стандарты, которые были выпущены гораздо раньше. Поэтому для физической реализации БД необходимо использовать техническую документацию разработчика СУБД. Все примеры и физическая реализация БД «БИБЛИОТЕКА» отработаны в СУБД Interbase от Embarcadero.

4.4.2. Запись SQL-операторов

SQL-оператор состоит из зарезервированных слов, а также из слов, определяемых пользователем [4, 6]. Зарезервированные слова являются постоянной частью языка SQL и имеют фиксированное значение. Их следует записывать в соответствии с установленным стандартом, т.е. не разбивать на части для переноса из одной строки в другую. Слова, определяемые пользователем, задаются им самим (в соответствии с определёнными синтаксическими правилами) и представляют собой имена различных объектов БД: таблиц, столбцов, представлений, индексов и т.п. В SQL-операторе слова размещаются в соответствии с установленными синтаксическими правилами. Хотя в стандарте это не указано, многие диалекты языка SQL требуют задания в конце оператора некоторого символа, обозначающего окончание его текста. Как правило это «точка с запятой».

Большинство компонентов SQL-операторов не чувствительно к регистру. Это означает, что могут использоваться любые буквы – как строчные, так и прописные. Одним важным исключением из этого правила являются символьные литералы - данные, которые не должны отличаться от соответствующих им значений, хранящиеся в БД. Например, если в БД хранится значение фамилии ’ИВАНОВ’, а в условии поиска он написан строчными буквами, то эта запись не будет найдена.

Поскольку язык SQL имеет свободный формат, отдельные SQL-операторы и их последовательности будут иметь более читабельный вид при использовании отступов и выравнивания. Рекомендуется придерживаться следующих правил.

1. Каждая фраза в операторе должна начинаться с новой строки.
2. Начало каждой фразы должно быть выровнено с началом остальных фраз оператора.
3. Если фраза имеет несколько частей, каждая из них должна начинаться с новой строки с некоторым отступом относительно начала фразы, что будет указывать на их подчиненность.

Для определения формата SQL-операторовбудем придерживаться такой форму их записи.

1. Прописные буквы будут использоваться для записи зарезервированных слов и должны указываться в операторах точно так же, как это будет показано.
2. Строчные буквы будут использоваться для записи слов, определяемых пользователем.
3. Вертикальная черта ( | ) указывает на выбор одного из нескольких приведенных значений. Например, a | b | c.
4. Фигурные скобки определяют обязательный элемент, например, {а}.
5. Квадратные скобки определяют необязательный элемент, например, [а].
6. Многоточие (...) используется для отображения необязательной возможности повторения конструкции, от нуля до нескольких раз, например, {a | b} [, c…]. Эта запись означает, что после a или b может следовать от нуля до нескольких повторений с, разделенных запятыми.

На практике для определения структуры БД используется DDL-операторы, а для заполнения отношений данными и выборки из них информации с помощью запросов – DML-операторы.

На этапе выбора системы управления БД мы остановились на СУБД Interbase [10, 14, 15]. Отметили, что в наши планы не входит изучения всех тонкостей работы с этим программным продуктом. Тех, кого заинтересуют подробности создания и управления БД именно с помощью этого продукта, традиционно предложим ознакомиться с документацией, поставляемой разработчиком.

СУБД Interbase позволяет создавать БД как в интерактивном режиме, поочередно выполняя SQL-операторы, так и в режиме, интерпретации SCRIPT-файла. Практическая часть ресурса построена на создании SCRIPT-файла и его последовательного наполнения командами. Рекомендуемую последовательность команд в SCRIPT-файле рассмотрим на практических занятиях. Здесь же ограничимся общим обзором SQL-операторов, создающих на жестком диске файл БД и его структуру, соответствующую логической модели.

Для создания файла (или файлов) БД на жестком диске используется оператор CREATE DATABASE. Он дает возможности:

1. Указать имя файла (или файлов) БД и его место положения на локальном или сетевом жестком диске.
2. Указать имя и пароль пользователя, который используется при подключении к БД.
3. Определить набор символов, применяемый в БД по умолчанию. Мы будем использовать набор WIN1251.
4. Для файла БД указать длину файла в страницах, размер каждой страницы в байтах, если БД располагается в нескольких файлах – указать с какой страницы начинается каждый из этих файлов.

Домены создаются оператором CREATE DOMAIN, в котором необходимо каждому домену дать уникальное имя. Он позволяет:

1. Указать тип данных, на базе которых создается домен.
2. Указать значение, принимаемое по умолчанию.
3. Определить конструкции условий проверки значения домена.
4. Определить таблицу, по которой будет проводиться сортировка значений домена.

Для создания таблиц и ключей применяется оператор CREATE TABLE. Таблица должна иметь уникальное имя, причем имена столбцов в её пределах не должны повторяться. С помощью этого оператора можно определить:

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

Порядок создания таблиц в БД играет большое значение. Сначала создаются те таблицы, в которых отсутствуют внешние ключи, дальше таблицы, в которых внешние ключи имеют связь с первичными ключами уже созданных таблиц. В качестве примера приведем порядок создания таблиц «ДОЛЖНОСТИ», «ЧИТАТЕЛИ», «ТИПЫ ТЕЛЕФОНОВ», «ТЕЛЕФОНЫ» (рис. 3.4). Здесь можно поменять порядок создания таблиц «ЧИТАТЕЛИ» и «ТИПЫ ТЕЛЕФОНОВ». Однако, «ЧИТАТЕЛИ» всегда должны создаваться после таблицы «ДОЛЖНОСТИ», а «ТЕЛЕФОНЫ» - после таблиц «ТИПЫ ТЕЛЕФОНОВ» и «ЧИТАТЕЛИ». В противном случае СУБД выдаст ошибку.

Структура БД «БИБЛИОТЕКА», которая получена после работы соответствующих операторов CREATE TABLE, показывает связь между логической и физической моделями (приложение Б, где буквой «P» обозначены первичные ключи, а буквой «F» – внешние). Обозначения первичных и внешних ключей соответствуют обозначениям связей между отношениями в приложении А. Стрелки указывают, что значения внешних ключей в дочерних отношениях должны обязательно ссылаться на значения первичных ключей родительских отношений.

4.6.1. Неизвестное или неприемлемое значение

Определитель NULL указывает на то, что значение атрибута в настоящий момент неизвестно или неприемлемо для этого кортежа.

Определитель NULL следует воспринимать как логическую величину «неизвестно». Иначе говоря, либо это значение не входит в область определения некоторого кортежа, либо никакое значение еще не задано. Ключевое слово NULL представляет собой способ обработки неполных или необычных данных. Однако определитель NULL не следует понимать как нулевое численное значение или заполненную пробелами текстовую строку. Нули и пробелы являются значениями, тогда как ключевое слово NULL призвано обозначать отсутствие какого-либо значения. Некоторые авторы используют термин «значение NULL», но на самом деле определитель NULL не является значением, а лишь обозначает его отсутствие, а потому термин «значение NULL» использовать не рекомендуется.

Применение определителя NULL может вызвать проблемы на этапе реализации. Трудности возникают из-за того, что реляционная модель основана на исчислении предикатов первого порядка, которое обладает двузначной, или булевой, логикой, т.е. допустимыми являются только два значения: истина и ложь. Применение определителя NULL означает, что придется вести работу с логикой более высокого порядка, например, трехзначной или даже четырехзначной (Codd, 1986, 1987, 1990).

Использование понятия NULL в реляционной модели является дискуссионным вопросом. В своих работах Кодд (1990) рассматривает понятие NULL как составную часть этой модели, когда другие специалисты считают этот подход не совсем точным. Они допускают, что включение определителя NULL в реляционную модель является преждевременным (Date, 1995). Следует отметить, что не во всех реляционных системах поддерживается работа с определителем NULL, но в СУБД InterBase этот определитель присутствует.

4.6.2. Обеспечение целостности реляционных данных

Прежде всего, целостность обеспечивается первичными (потенциальными) ключами базовых или родительских отношений [2, 4, 6]. Здесь родительское отношение определяется как отношение, которое соответствует некоторому субъекту (сущности) технологического процесса в логической модели. Например, отношение «ДОЛЖНОСТИ» является родительским для отношения «ЧИТАТЕЛИ» (рис 3.4). Отношение «ЧИТАТЕЛИ» является родительским для того отношения, где значения внешнего ключа ссылается на значение потенциального ключа отношения «ЧИТАТЕЛИ».

Целостность сущностей в базовом отношении обеспечивается тем, что ни один атрибут первичного ключа не может содержать отсутствующих значений (см. опредилитель NULL).

Допущение присутствия определителя NULL в любой части потенциального ключа равноценно утверждению, что не все его атрибуты необходимы для уникальной идентификации кортежей. Приведенное утверждение противоречит определению потенциального ключа.

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

Ссылочная целостность обеспечивается, если значение внешнего ключа дочернего отношения ссылается на существующее значение потенциального ключа родительского отношения, либо задается с помощью определителя NULL.

Допустимость присутствия определителя NULL среди значений атрибутов, используемых в качестве внешнего ключа, определяется требованиями пользователей. Например, если пользователь считает, что информация о должности является необязательной, то в столбце «Код должности» отношения «ЧИТАТЕЛИ» может присутствовать либо определитель NULL, либо значение первичного ключа, которое имеет место в атрибуте «Код» отношения «ДОЛЖНОСТЬ». Если пользователь считает, что информация о должности является обязательной, тогда в столбце «Код должности» присутствие определителя NULL недопустимо.

4.6.3. Операторы для добавления, обновления и удаления данных

Оператор INSERT позволяет добавить новую строку в таблицу с именем, которое Вы указываете. Он даёт возможность перечислить столбцы таблицы, в которых значения в новой строке будут отличаться от значений, которые приняты по умолчанию. Если на уровне домена или в операторе CREATE TABLE значения по умолчанию не определены, тогда в ячейках новой строки, расположенных в столбцах, которые не попали в список, будет записано значение NULL. Количество столбцов и добавляемых значений, перечисленных в операторе, должно совпадать. Если хотя бы одно значение в операторе выходит за область его определения, то действие оператора INSERT будет отклонено и СУБД выдаст соответствующее сообщение.

Порядок добавления новых строк в таблицы не является произвольным. Это связано с тем, что пока в родительской таблице не будет добавлена строка с некоторым значением потенциального ключа, в дочернюю таблицу нельзя будет добавить строку со значением внешнего ключа, которое ссылается на значение потенциального ключа в родительской таблице. Для исключения ошибок добавляйте новые строки в таблицы, соблюдая порядок их создания в БД операторами CREATE TABLE.

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

SET GENERATOR {Имя} TO {Начальное значение}

позволяет указать значение, с которого начнет свою работу генератор. В других СУБД механизм генераторов встроен в оператор CREATE TABLE, где можно указать инкрементный тип данных для столбца таблицы.

Оператор UPDATE изменяет значения в записях таблицы, имя которой обязательно нужно указать. Он позволяет изменить значения в любом количестве столбцов таблицы. Также как и для оператора INSERT, при выходе нового значения за границу области определения СУБД сгенерирует сообщение об ошибке, и действие оператора UPDATE будет отменено.

Множество строк таблицы, в которых будет работать оператор UPDATE, определяется условиями поиска. В большинстве случаев в условии поиска указывается область допустимых значений первичного или потенциального ключа таблицы. Данные обновляются в тех строках, где логическое выражение поиска «истинно». Если Вы не укажите условие обновления, тогда во всех строках таблицы данные будут изменены.

Удаление строк из таблиц обеспечиваеться оператором DELETE. Условие поиска работает точно так же, как и в операторе UPDATE. Если условие поиска не указывать, то из таблицы будут удалены все строки.

4.6.4. Каскадное обновление и удаление

Этот механизм, который является одним из способов обеспечения ссылочной целостности данных, реализуется в операторе CREATE TABLE. Он позволяет разработчику указать для СУБД действие, которое необходимо выполнить системе при выполнении операторов UPDATE или DELETE, делающих попытку удалить или обновить такое значение потенциального ключа в родительской таблице, на которое ссылается одна или несколько строк дочерней таблицы.

Язык SQL предусматривает четыре варианта реакции, как для обновления, так и для удаления значения потенциального ключа из родительской таблицы, на которое имеются ссылки значений внешнего ключа из строк дочерней таблицы. Вариант действия указывается отдельно для оператора UPDATE или DELETE при определении внешнего ключа в дочерней таблице. Действия указываются после ключевых фраз ON UPDATE или ON DELETE. Они определяются исходя из требований пользователей, которые были получены на этапе логического проектирования БД.

Если после ON UPDATE указать слово CASCADE, то в этом случае будет выполняеться каскадное обновление значений внешнего ключа в строках дочерней таблицы при изменении оператором UPDATE ссответствующего значения потенциального ключа в родительской таблице. Если CASCADE написать после ON DELETE, то выполнение оператора DELETE, удаляющего строку со значением потенциального ключа в родительской таблице, приведет к удалению строк в дочерней таблице, в которых значение внешнего ключа совпадает с удаляемым значением потенциального ключа.

Фраза SET NULL, написанная после ON DELETE или ON UPDATE, говорит системе, что после удаления или изменения значения потенциального ключа в родительской таблице необходимо прописать определитель NULL во всех строках дочерней таблицы, в которых значения внешних ключей ссылались на удаленное или измененное значение потенциального ключа.

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

Предложение NO ACTION, написанное после ON UPDATE или ON DELETE, определяет четвертый вариант реакции СУБД на изменение значения потенциального ключа в строке родительской таблицы или удаления такой строки. В этом случае система выведет сообщение об ошибке, т.к. такое действие нарушает ссылочную целостность данных.

Например, попытка удаления строки из таблицы «ДОЛЖНОСТЬ» (рис. 3.4) может привести к тому, что у ряда читателей значение внешнего ключа будет ссылаться на несуществующее значение первичного ключа. Такая ситуация недопустима. Однако, удаление строки из таблицы «ДОЛЖНОСТЬ» со значением первичного ключа, на которое ссылка из таблицы «ЧИТАТЕЛИ» отсутствует, пройдет без проблем. В этом случаи ссылочная целостность данных нарушена не будет.

Если в операторе CREATE TABLE, описывающем структуру дочерней таблицы, не указаны фразы ON UPDATE или ON DELETE в определении внешнего ключа, тогда механизм обеспечения ссылочной целостности при удалении или изменении значения потенциального ключа в родительской таблице работает так же, как и при указании фразы NO ACTION.

Содержимое БД «БИБЛИОТЕКА», которая будет использоваться для изучения приемов выборки информации, приведено в приложении В.

1. В чем заключается физическое проектирование БД?
2. Что влияет на выбор СУБД для реализации физической модели БД?
3. По какой группе правил СУБД считают реляционной?
4. Для чего используют известные вам компоненты языка SQL?
5. Каких правил необходимо придерживаться для записи SQL-операторов?
6. Какие характеристики имеет SQL-оператор, создающий БД?
7. Какие возможности дает SQL-оператор определяющий домены?
8. Какие структурные элементы таблиц определяются SQL-оператором?
9. На что указывает и для чего используется определитель NULL?
10. Как обеспечиваются целостность сущностей и ссылочная целостность?
11. С помощью каких SQL-операторы манипулируют данными в таблицах?
12. Что определяют условия поиска SQL-операторов, которые обновляют или удаляют данные из таблицы?
13. Какие средства для обеспечения уникальности значений первичных ключей Вам известны?
14. Как СУБД реализует каскадное обновление и удаление данных?