Методические указания для студентов специальности 2205, 0755 «Проектирование и технология эвс», «Комплексная информационная безопасность автоматизированных систем» (стр. 9 из 21)
Выше мы уже говорили о том, что первичный ключ любой таблицы должен содержать уникальные непустые значения для данной таблицы. Это утверждение является одним из правил ссылочной целостности (referential integrity). Некоторые (но далеко не все) СУБД могут контролировать уникальность первичных ключей. Ссылочная целостность обеспечивает контроль правильности ссылок и блокирует выполнение ошибочных операций. Например, при попытке присвоить первичному ключу значение, уже имеющееся в другой записи, СУБД сгенерирует диагностическое сообщение, обычно содержащее словосочетание primary key violation. Это сообщение в дальнейшем может быть передано в приложение, с помощью которого конечный пользователь манипулирует данными.
Если две таблицы связаны соотношением master-detail, внешний ключ detail-таблицы должен содержать только те значения, которые уже имеются среди значений первичного ключа master-таблицы. Если корректность значений внешних ключей не контролируется СУБД, можно говорить о нарушении ссылочной целостности. В этом случае, если мы удалим из таблицы Customers запись, имеющую хотя бы одну связанную с ней detail-запись в таблице Orders, это приведет к тому, что в таблице Orders окажутся записи о заказах, размещенных неизвестно кем. Если же СУБД контролирует корректность значений внешних ключей, то при попытке присвоить внешнему ключу значение, отсутствующее среди значений первичных ключей master-таблицы либо при удалении или модификации записей master-таблицы, приводящих к нарушению ссылочной целостности, СУБД сгенерирует диагностическое сообщение. Сообщение обычно содержит словосочетание foreign key violation, которое в дальнейшем может быть передано в пользовательское приложение.
4.3. Введение в нормализацию данных
Обсудим один из основных принципов проектирования данных - принцип нормализации.
Нормализация представляет собой процесс реорганизации данных путем ликвидации повторяющихся групп и иных противоречий в хранении данных с целью приведения таблиц к виду, позволяющему осуществлять непротиворечивое и корректное редактирование данных. Нередко после нормализации к структуре БД добавляется одна или несколько таблиц.
Теория нормализации основана на концепции нормальных форм. Говорят, что таблица находится в данной нормальной форме, если она удовлетворяет определенному набору требований. Теоретически существуют пять нормальных форм, но на практике обычно используются только первые три. Это делается с целью упрощения структуры БД.
Проиллюстрируем процесс нормализации на примере, использующем данные из базы NorthWind. Предположим, что мы регистрируем все заказанные продукты в следующей таблице:
| OrderlD | ProductlD | CustomerlD | Address | Quantity | OrderDate |
| 10265 | 17 | BLONP | 24, place Kleber | 30 | 07.25.96 |
| 10265 | 70 | BLONP | 24, place Kleber | 20 | 07.25.96 |
| 10278 | 44 | BERGS | Berguvsvagen 8 | 16 | 08.12.96 |
| 10278 | 59 | BERGS | Berguvsvagen 8 | 15 | 08.12.96 |
| 10278 | 63 | BERGS | Berguvsvagen 8 | 8 | 08.12.96 |
| 10278 | 73 | BERGS | Berguvsvagen 8 | 25 | 08.12.96 |
| 10280 | 24 | BERGS | Berguvsvagen 8 | 12 | 08.14.96 |
| 10280 | 55 | BERGS | Berguvsvagen 8 | 20 | 08.14.96 |
| 10280 | 75 | BERGS | Berguvsvagen 8 | 30 | 08.14.96 |
| 10289 | 3 | BSBEV | Fauntleroy Circus | 30 | 08,26.96 |
| 10289 | 64 | BSBEV | Fauntleroy Circus | 9 | 08.26.96 |
| 10297 | 39 | BLONP | 24, place Kleber | 60 | 09.04.96 |
| 10297 | 72 | BLONP | 24, place Kleber | 20 | 09.04.96 |
| 10308 | 69 | ANATR | Avda. De la Constitucion 22 | 1 | 09.18.96 |
| 10308 | 70 | ANATR | Avda. De la Constitucion 22 | 5 | 09.18.96 |
Структура этой таблицы имеет следующий вид (рисунок 17)
OrderedProducts
| OrderID (PK) ProductID(PK) |
| CustomerID Address OrderDate Quantity |
Рисунок 17. Структура ненормализованной таблицы OrderedProducts
Здесь РК обозначает первичный ключ. Чтобы таблица соответствовала первой нормальной форме, все значения ее полей должны быть атомарными и все записи – уникальными. Поэтому любая реляционная таблица, в том числе и таблица ОrderedProducts, по определению уже находится в первой нормальной форме. Атомарность означает, что в одном поле мы не можем записать все сведения о заказанных продуктах. Одна запись соответствует одному заказанному продукту. Далее, если необходимо, то адрес можно разбить на отдельные поля. В БД содержащих сведения о фамилии имени и отчестве желательно разделить эти сведения на три различных столбца.
Рассмотрим далее. Таблица содержит избыточные данные, например: одни и те же сведения о клиенте повторяются в записи о каждом заказанном продукте. Результатом избыточности данных являются аномалии модификации данных - проблемы, возникающие при добавлении, изменении или удалении записей. Например, при редактировании данных в таблице OrderedProducts могут возникнуть следующие проблемы:
• Адрес конкретного клиента может содержаться в базе данных только тогда, когда клиент заказал хотя бы один продукт.
• При удалении записи о заказанном продукте одновременно удаляются сведения о самом заказе и о клиенте, его разместившем.
• Если, заказчик сменил адрес, придется обновить все записи о заказанных им продуктах.
Некоторые из этих проблем могут быть решены путем приведения базы данных ко второй нормальной форме.
Говорят, что реляционная таблица находится во второй нормальной форме, если она находится в первой нормальной форме и ее неключевые поля полностью зависят от всего первичного ключа.
Таблица OrderedProducts находится в первой, но не во второй нормальной форме, так как поля CustomerlD, Address и OrderDate зависят только от поля OrderlD, являющегося частью составного первичного ключа (OrderlD, ProductID). Чтобы перейти от первой нормальной формы ко второй, нужно выполнить следующие шаги:
1. Определить, на какие части можно разбить первичный ключ, так чтобы некоторые из не ключевых полей зависели от одной из этих частей (это части не обязаны состоять из одной колонки).
2. Создать новую таблицу для каждой такой части ключа и группы зависящих от нее полей и переместить их в эту таблицу. Часть бывшего первичного ключа станет при этом первичным ключом новой таблицы.
3. Удалить из исходной таблицы поля, перемещенные в другие таблицы, кроме тех их них, которые станут внешними ключами.
Например, для приведения таблицы OrderedProducts ко второй нормальной форме нужно переместить поля CustomerlD, Address и OrderDate в новую таблицу (назовем ее OrdersInfo), при этом поле OrderID станет первичным ключом новой таблицы (рисунок 18).
OrdersInfo OrderDetails
Рисунок 18. Приведение таблицы OrderedProducts ко второй нормальной форме
В результате новые таблицы приобретут вид, показанный ниже.
| OrdersInfo | |||
| OrderID | CustomerID | Address | OrderDate |
| 10265 | BLONP | 24, place Kleber | 07.25.96 |
| 10278 | BERGS | Berguvsvagen 8 | 08.12.96 |
| 10280 | BERGS | Berguvsvagen 8 | 08.14.96 |
| 10289 | BSBEV | Fauntleroy Circus | 08.26.96 |
| 10297 | BLONP | 24, place Kleber | 09.04.96 |
| 10308 | ANATR | Avda. De la Constitucion 22 | 09.18.96 |
Однако таблицы, находящиеся во второй нормальной форме, по-прежнему содержат аномалии модификации данных. Например, для таблицы OrdersInfo:
• Адрес конкретного клиента по-прежнему может содержаться в базе данных только тогда, когда клиент заказал хотя бы один продукт.
• Удаление записи о заказе в таблице OrdersInfo приведет к удалению записи о самом клиенте.
• Если заказчик сменил адрес, придется обновить несколько записей (хотя, как правило, их меньше, чем в предыдущем случае). Устранить эти аномалии можно путем перехода к третьей нормальной форме.
| OrderDatails: | ||
| OrderID | ProductID | Quantity |
| 10265 | 17 | 30 |
| 10265 | 70 | 20 |
| 10278 | 44 | 16 |
| 10278 | 59 | 15 |
| 10278 | 63 | 8 |
| 10278 | 73 | 25 |
| 10280 | 24 | 12 |
| 10280 | 55 | 20 |
| 10280 | 75 | 30 |
| 10289 | 3 | 30 |
| 10289 | 64 | 9 |
| 10297 | 39 | 60 |
| 10297 | 72 | 20 |
| 10308 | 69 | 1 |
| 10308 | 70 | 5 |
Говорят, что реляционная таблица находится в третьей нормальной форме, если она находится во второй нормальной форме и все ее не ключевые поля зависят только от первичного ключа.
Таблица OrderDetails уже находится в третьей нормальной форме. Не ключевое поле Quantity полностью зависит от составного первичного ключа (OrderID, ProductID). Однако таблица OrdersInfo в третьей нормальной форме не находится, так как содержит зависимость между не ключевыми полями (она называется транзитивной зависимостью - transitive dependence - поле Address зависит от поля CustomerID.
Чтобы перейти от второй нормальной формы к третьей необходимо выполнить следующие шаги: