Социология. Моделирование данных hl. Системы управления базами данных
 Скачать 0.68 Mb.
|
|
Код_клиента № паспорта Фамилия Имя 1 AB 2358955 Сидоров Сергей 2 DB 2456886 Петров Петр 3 MA 8654212 Иванов Иван Товары Код_товара Наименование Цена 1236 Пылесос Вихрь-150 88999 5543 Холодильник Минск 131 310056 445 Телевизор Горизонт ТЦ 655 260324 Заказы Код_заказа Код_клиента Код_товара Дата заказа 1 1 1236 05.03.2000 2 2 5543 15.04.2000 3 2 445 21.04.2000 4 1 3245 02.10.2000 5 3 1236 12.12.2000 6 3 445 30.12.2000 Связь «много ко многим» также автоматически возникает между таблицами, связанными посредством неключевых полей. Пусть первая 21 Клиенты Код_клиента № паспорта Фамилия Имя Отчество Год рождения Адрес Телефон Товары Код_товара Наименование Цена Категория 1 ∞ Заказы Код_заказа Код_клиента Код_товара Дата заказа Количество Полная цена Отпущено 1 ∞ таблица содержит информацию о том, на каких станках могут работать рабочие некоторой бригады. Вторая таблица содержит сведения о том, кто из бригады ремонтников какие станки обслуживает. Работа Обслуживание Работает Станок Иванов А. В. станок 1 Иванов А.В. станок 2 Петров Н.Г. станок 1 Петров Н.Г. станок З Сидоров В. К. станок 2 Обслуживает Станок Голубев Б. С. станок 1 Голубев Б. С. станок 2 Зыков А.Ф. станок 2 Зыков А.Ф. станок З Первой и третьей записям таблицы «Работа» соответствует первая запись таблицы «Обслуживание». Четвертой записи таблицы «Работа» соответствуют вторая и четвертая записи таблицы «Обслуживание». Такой вид связи «много ко многим» характеризуется как слабый вид связи или даже как отсутствие связи, поскольку никакого контроля за целостностью данных в этом случае не производится. Информация, размещенная в связанных таблицах, может быть легко объединена с помощью естественного соединения (по значениям внешних ключей). Для нашего примера будем иметь: Заказы Фамилия Имя Наименование Цена Дата заказа Сидоров Сергей Пылесос Вихрь-150 88999 05.03.2000 Петров Петр Холодильник Минск 131 310056 15.04.2000 Петров Петр Телевизор Горизонт ТЦ 655 260324 21.04.2000 Сидоров Сергей Электрочайник BOSCH 50346 02.10.2000 Иванов Иван Пылесос Вихрь-150 88999 12.12.2000 Иванов Иван Телевизор Горизонт ТЦ 655 260324 30.12.2000 Кроме вышеупомянутых связей в реляционной модели возможны также рекурсивные связи. Предположим, мы должны хранить информацию о сотрудниках некоторой компании с указанием отношений подчиненности между отдельными сотрудниками. Поскольку один экземпляр объекта «Сотрудники» ссылается на другой экземпляр того же объекта, который в свою очередь может ссылаться на третий экземпляр, то связь будет унарной и рекурсивной. Данная связь легко реализуется путем введения внешнего ключа, ссылающегося на первичный ключ той же таблицы. 2.3.3. Целостность в реляционной БД Рассмотрим подробнее ограничители целостности в применении к реляционной модели данных. Понятие ссылочной целостности неразрывно связано с понятием внешних ключей и формулируется для реляционной БД следующим образом: реляционная БД не должна содержать 22 несогласованных значений внешних ключей, т.е. таких значений, для которых не существует отвечающих им значений соответствующих потенциальных ключей базовых таблиц. Иначе говоря, внешние ключи могут содержать только те значения, которые принимают соответствующие им ключевые (в общем случае потенциальные) поля базовых таблиц. Если в БД уже есть клиент с номером 10 и товар с номером 25, то только тогда можно внести запись о покупке клиентом 10 товара 25 в таблицу «Заказы». С другой стороны нельзя удалить клиента с номером 25, или даже изменить его номер, если в БД хранится хотя бы одна запись о сделанном им заказе. В противном случае согласованность внешних ключей тоже нарушается, так как запись в таблице «Заказы» будет содержать значение, указывающее на несуществующего клиента. Заметим, что внешний ключ может также содержать значения первичного ключа своего же отношения для обеспечения рекурсивной связи. Ограничители ссылочной целостности могут быть схематично отображены с помощью линий со стрелками, направленных от внешних ключей к потенциальным ключам базовых отношений. Есть два правила внешних ключей, обеспечивающих согласованное состояние БД. Первое заключается в ограничении обновления и удаления. При работе с базовой таблицей это правило запрещает удаление записей, которые содержат связанные записи в подчиненных таблицах и изменение значений их первичных ключей. При работе с подчиненной таблицей это правило запрещает установку внешнего ключа в значение, которого не существует для соответствующего потенциального ключа базовой таблицы. Второе из них заключается в каскадном обновлении внешних ключей и каскадном удалении записей подчиненных таблиц при операциях с базовой таблицей. При изменении значения базового ключа, использующегося в связи, все значения внешних ключей всех подчиненных таблиц должны быть изменены соответственно (каскадное обновление). При удалении записи из базовой таблицы все связанные с ней записи из подчиненных таблиц должны быть также удалены (каскадное удаление). Если для внешних ключей разрешены Null значения, то в некоторых реляционных СУБД вместо каскадного удаления может применяется операция Nullify, которая устанавливает внешние ключи подчиненных таблиц в Null при удалении соответствующего значения базового ключа. Кроме ссылочной целостности можно сформулировать еще три ограничителя целостности для реляционной БД: • ограничители домена (domain constraints), запрещающие ввод значений атрибутов, не принадлежащих домену. К ним можно отнести также все ограничители значений (тип, формат, задание списка и диапазона значений); 23 • ограничители ключей (key constraints), обеспечивающие уникальность значений потенциальных ключей (обеспечивается применением уникальных индексов); • ограничители записи (entity constraints), запрещающие Null значения для первичных ключей, так как в противном случае будет нарушено требование идентифицируемости записи. 2.4. Нормализация данных в реляционной модели Один и тот же набор данных в реляционной модели можно представить различными способами. Процесс нормализации данных позволяет решить вопрос о наиболее эффективной структуре данных, обладающей минимальной избыточностью. Это позволяет решить следующие задачи: a) исключить ненужное повторение данных, b) обеспечить быстрый доступ к данным, c) обеспечить целостность данных. Обычно говорят только об уменьшении избыточности данных и нормализацией называют процесс уменьшения избыточности информации в реляционной БД. Теория нормализации оперирует с пятью нормальными формами и одной промежуточной, уточняющей третью. На практике же обычно руководствуются только первыми тремя нормальными формами. Процесс проектирования БД с использованием нормальных форм является итерационным и заключается в последовательном применении правил нормальных форм от низшей к высшей. Каждая следующая нормальная форма уменьшает избыточность данных и сохраняет свойства всех предыдущих форм. Перевод отношения в следующую нормальную форму осуществляется методом «декомпозиции без потерь», т.е. разбиением исходной таблицы на несколько связанных. Такая декомпозиция должна обеспечить равенство результатов выборок из исходного отношения и выборок, основанных на совокупности полученных отношений, т.е. говоря математическим языком, должно соблюдаться следующее правило: R R m i i = = 1 , где } , , , { 2 1 n A A A R = - исходное отношение, а } , , , { 2 1 m R R R D = - декомпозиция, т.е. множество нормализованных отношений. При декомпозиции следует не только опасаться потери информации, но также избегать и возможности появления так называемых подложных записей, которые могут появиться вследствие проведения объединений (например, такие записи могут появляться при объединении таблиц посредством не ключевых полей). И наконец, при установлении 24 зависимостей между таблицами следует придерживаться связей, существующих между реальными объектами. Рассмотрим ненормализованную таблицу «Продажи», которая будет содержать сведения о покупателях, сведения о проданных товарах и оформленных заказах: Продажи № Наименование 1 Фамилия 2 Имя 3 Отчество 4 Паспорт 5 Телефон 6 Адрес 7 Кредит 8 Примечание № Наименование 9 Дата_заказа 10 Наим_товара 11 Категория 12 Размещение 13 Цена 14 Количество 15 Полная_цена 16 Прим_заказ Таблицу «Продажи» можно рассматривать как однотабличную БД. Основная проблема состоит в том, что в ней содержится значительное количество повторяющейся информации. Например, сведения о каждом покупателе повторяются для каждого сделанного им заказа. Наличие повторяющейся информации ведет к возможной потере согласованности данных, т.е. к появлению аномалий обновления, неоправданному увеличению размера базы данных, и, как следствие, к снижению скорости поиска и выполнения запросов. Аномалии обновления можно условно разбить на три вида: a) аномалии добавления. Возникают в случаях, когда информацию в таблицу нельзя поместить до тех пор, пока она не полная. Например, мы хотим добавить информацию о новом товаре, пришедшем на склад и у которого пока еще нет покупателей. Нам придется оставить незаполненными все поля, касающиеся клиентов и оформления заказа. Но если некоторые поля были объявлены обязательными, то либо в них придется ввести что-нибудь, либо вообще отказаться от записи. К тому же, после первой же покупки этого товара необходимость в этой записи вообще исчезнет; b) аномалии удаления. Состоят в том, что при удалении некоторой информации может пропасть и другая информация, не связанная напрямую с удаляемой. Например, при удалении последней записи о продаже некоторого товара сведения об этом товаре будут утеряны, хотя это никак не будет означать, что данного товара больше нет на складе; c) аномалии модификации. Проявляются в том, что изменение значения некоторого поля в одной записи может привести к просмотру всей 25 таблицы и соответствующему изменению других записей таблицы. Например, такая аномалия возникнет при обновлении не всех записей о покупках некоторого клиента при смене адреса этого клиента. Причем простая автозамена здесь не сможет помочь во всех случаях, тат как адрес может быть записан разным образом. 2.4.1. Первая нормальная форма Первая нормальная форма требует соответствия исходной таблицы требованиям, предъявляемым к отношениям, т.е.: 1. Таблица не должна иметь повторяющихся записей; 2. Все атрибуты должны быть простыми (скалярными); Первое требование легко решается введением ключевого поля, однозначно определяющего остальные поля таблицы. Разумеется, можно использовать одно из полей, имеющееся в таблице. К примеру, номер паспорта. Но паспорт легко сменить, сменив фамилию. В таком случае рекомендуется ввести так называемый табельный номер, основным назначением которого будет являться идентификация некоторой записи. Если этот номер не имеет практического смысла, то его можно скрыть от пользователей. Ввод табельного номера рекомендуется в следующих случаях: • в предметной области наблюдается синомия, т.е. ключевой атрибут не является уникальным (например, фамилия); • если естественный атрибут может меняться со временем; • если данный атрибут участвует во многих связях. Так как связь осуществляется посредством ключевых полей, то во всех подчиненных таблицах придется повторить значение ключевого поля. При применении не числовых и достаточно громоздких полей скорость выполнения операций доступа и поиска будет существенно падать. Лучшим решением является применение целочисленных значений. Второе требование постулирует, чтобы в каждой ячейке было представлено одно значение, отнюдь не массив или перечисление. Составные поля должны быть разложены на простые, а многозначные – вынесены в отдельные таблицы. В нашем случае поле «Адрес» должно быть разложено на поля «Страна», «Город» и «Адрес», чтобы сохранить возможность поиска и сортировки по данным полям. Поле «Телефон» является многозначным и, следовательно, должно быть выделено в отдельную таблицу. После ввода составного ключа «Код_клиента», «Код_товара» и «Дата_заказа» таблица «Продажи» будет находиться в первой нормальной форме. Получим: 26 2.4.2. Вторая нормальная форма Таблица находится во второй нормальной форме, если: 1. она удовлетворяет условиям первой нормальной формы и 2. любое поле, не входящее в ключ, должно однозначно идентифицироваться значением первичного ключа. Если первичный ключ является составным, то остальные поля должны зависеть от полного выражения ключа, а не от его части (неключевые атрибуты должны характеризоваться полной функциональной зависимостью от первичного ключа). Легко заметить, что атрибуты «Фамилия», «Имя», «Отчество», «Паспорт», «Страна», «Город», «Адрес», «Предприятие», «Руководитель», «Кредит» и «Примечание» зависят только от части составного ключа – поля «Код_клиента», а атрибуты «Наим_товара», «Категория» и «Цена» зависят только от поля «Код_товара». Следовательно, они должны быть вынесены в две отдельные таблицы. Иначе говоря, требование 2 также постулирует устранение повторяющейся информации для группы полей. Такие повторяющиеся группы полей обычно соответствуют некоторым реальным объектам, информацию о которых мы сохраняем. Следовательно, таблица должна содержать данные, относящиеся только к одному объекту. Поскольку здесь явно выделяются объекты «покупатели», «товары» и «заказы», нам необходимы три таблицы. Записи одной таблицы будут содержать сведения об покупателях, второй – информацию о товарах, третей – информацию о каждом из заказов. Вначале разобьем таблицу «Продажи» на две отдельные 27 Продажи Код_клиента Фамилия Имя Отчество Паспорт Страна Город Адрес Кредит Примечание Дата_заказа Код_товара Наим_товара Категория Размещение Цена Количество Полная_цена Прим_заказ 1 ∞ Телефоны Телефон Код_клиента таблицы («Клиенты» и «Заказы») и определим поле «Код_клиента» в качестве совпадающего поля для связывания таблиц. Аналогично выделим из таблицы «Заказы» таблицу «Товары», которая будет содержать информацию о товарах каждого типа. Для связывания таблиц «Заказы» и «Товары» будем использовать поле "Код_товара". Между таблицами «Клиенты» – «Заказы» и «Товары» – «Заказы» будут отношения один-ко-многим. В таблице «Заказы» в качестве ключевого поля можно выбрать поля «Код_клиента», «Код_товара» и «Дата_заказа», а также ввести табельный номер «Код_заказа». Получим: 2.4.3. Третья нормальная форма Таблица находится в третьей нормальной форме, если: 1. она удовлетворяет условиям второй нормальной формы и, 2. ни одно из полей таблицы, не входящих в ключ, не должно идентифицироваться с помощью другого поля, не входящего в ключ (иначе, отношение не должно иметь неключевых атрибутов, которые бы находились в транзитивной функциональной зависимости от первичного ключа). Сведение таблицы к третьей нормальной форме предполагает разделение таблицы с целью помещения в отдельную таблицу (или несколько таблиц) столбцов, которые не зависят напрямую от выражения первичного ключа. В результате такого разбиения каждое из полей, не входящих в первичный ключ, должно оказаться независимым от какого- либо другого не ключевого поля. Обратимся к таблице «Товары». Поле «Размещение» этой таблицы содержит описание места размещения товара на складе, которое однозначно определяется значением поля «Категория». Поскольку поле «Размещение», не входящее в индекс, однозначно определяется другим неключевым полем «Категория», таблица «Товары» не является таблицей в третьей нормальной форме. Для приведения этой 28 Клиенты Код_клиента Фамилия Имя Отчество Паспорт Страна Город Адрес Кредит Примечание Товары Код_товара Наим_товара Категория Размещение Цена 1 ∞ Заказы Код_заказа Код_клиента Код_товара Дата_заказа Количество Полная_цена Прим_заказ ∞ ∞ Телефоны Телефон Код_клиента 1 таблицы к третьей нормальной форме создадим новую таблицу Категории. Получим: Третья нормальная форма также постулирует отсутствие полей, которые могут быть вычислены на основе других. Если полная цена может быть вычислена на основе полей «Цена» и «Количество», то его лучше исключить из таблицы и вычислять динамически. Но в нашем случае это поле содержит отпускную цену, которая может включать скидки и налоги и может не зависеть напрямую от цены товара. На этом процесс нормализации обычно заканчивается. Если же необходима более детальная проработка данных, то дополнительно рассматривают требования нормальных форм более высокого порядка. 2.4.4. Нормальная форма Бойса – Кодда (Boyce-Codd) Третья нормальная форма постулирует отсутствие зависимости неключевых атрибутов от других неключевых атрибутов, забывая о том, что в ряде случаев может наблюдаться зависимость части ключевого составного атрибута от некоторого неключевого. Если в таблице имеется такая зависимость, то необходимо перейти к усиленной третьей нормальной форме или форме Бойса – Кодда (BCNF). Таблица находится в форме Бойса – Кодда, если она: 1. удовлетворяет требованиям третьей нормальной формы и 2. в ней отсутствуют зависимости атрибутов составного ключа от не ключевых атрибутов. Этот вид зависимости схематично отображен на рисунке. Не ключевой атрибут С функционально зависит от составного ключа A+B. Атрибут B, в свою очередь, функционально зависит от 29 Клиенты |