Delphi 3 и создание приложений баз данных

         

Нормализация таблиц при проектировании базы данных


При проектировании структуры новой БД определяют сущности (объекты, явления) предметной области, которые должны найти свое отражение в базе данных. Анализ предметной области обычно осуществляется:

• на основании существующих сведений о предметной области в широком или в узком смысле, то есть в масштабах, в которых она должна быть представлена в создаваемой БД и работающих с ней приложениях;

• исходя из целей проектирования программной системы;

• на основании представления о том, какое место БД и работающие с ней приложения займут в структуре эксплуатирующей ее организации;

• на основании представлений о том, какие изменения деловых потоков организации последуют после внедрения программной системы в эксплуатацию.

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

Замечание.

Несмотря на существование методик анализа предметных областей, построения эскизов БД (весьма полезных при больших объемах обрабатываемых данных и деловых правил в предметной области, нередко выходящих за рамки одновременного восприятия), необходимо отметить следующее:

• процесс определения окончательной структуры БД является циклическим, то есть на разных этапах проектирования - начиная от эскиза структуры БД и заканчивая опытной или даже промышленной эксплуатацией готовых программных систем - приходится возвращаться к структуре БД и вносить в нее изменения;

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

Одной из таких формализации является требование, согласно которому реляционная база данных должна быть нормализована (то есть подвергнута процедуре нормализации). Рассмотрим, что это такое.

Процесс нормализации имеет своей целью устранение избыточности данных и заключается в приведении к третьей нормальной форме (ЗНФ).

Существует несколько нормальных форм - 1НФ, 2НФ, ЗНФ, 4НФ, ЗНФ, нормальная форма Бойса-Кодда (БКНФ). При практической разработке баз данных важны первые три - 1НФ, 2 НФ, ЗНФ.

Первая нормальная форма (1НФ)

требует, чтобы каждое поле таблицы БД:

• было неделимым;

• не содержало повторяющихся групп.

Неделимость

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

Накладная №123
Дата

Покупатель Адрес
10.01.97

ТОО "Геракл" г. Москва , ул. Стромынка , 20
Отпущен товар Количество ед.изм. Цена ед.изм. Общая стоимость
Тушенка 10000 банки 7000 70 000 000
Сахар 200 кг 5000 1 000 000
Макароны 1000 кг 3000 3 000 000
Итого 74 000 000

Повторяющимися

являются поля, содержащие одинаковые по смыслу значения. Например, если требуется получить статистику продаж четырех товаров по месяцам, можно создать поля для хранения данных о продаже по каждому товару. Однако в этом случае мы имеем дело с повторяющимися группами (рис. 1.17):

Замечание.

Дефис в заголовке таблицы не является обязательным требованием именования таблиц БД; просто таблицы именуются именно такимобразом в использованной автором программе формирования IDEF1X-диаграмм базы данных (Design/IDEF).

Однако, что делать, если товаров не 4, а 104? Конечно, можно определить столько полей, сколько товаров. Но как быть, если число товаров заранее не известно и по одной накладной может быть отпущено 2, а по другой- 772 товара? Реализовать запись с переменным числом полей в реляционных базах данных невозможно, поскольку запись таблицы реляционной БД должна иметь четкую структуру. Исходя из вышесказанного, повторяющиеся группы следует устранить. В результате получим запись, содержащую информацию о статистике продаж по одному товару (рис. 1.18). Для 4 товаров будем иметь 4 записи, для 104 товаров - 104 записи и для n товаров - n записей для каждого месяца.

Пример.

Пусть необходимо автоматизировать процесс отпуска товаров со склада. Товары отпускаются по накладной, примерный вид которой приводится

В начале проектирования, приводя данные к первой нормальной форме, сведем имеющиеся данные в одну таблицу. Известно, что впоследствии будет необходимо производить анализ продаж по городам. Поэтому из поля "Адрес" (допускающего толкование как делимого поля) выделим в отдельное поле "Город". Известно, что каждый покупатель может закупить в один день различное количество товаров. Поэтому переборем искушение назначить каждому товару отдельное поле и выделим факт отпуска товара в отдельную запись (рис. 1.19). Для того, чтобы продолжить нормализацию данных, приведем данные ко второй нормальной форме (2НФ).

Вторая нормальная форма (2НФ)

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

Продолжим рассмотрение описанного выше примера. Для приведения к 2НФ выделим поля, которые входят в первичный ключ. Дата накладной и номер накладной по отдельности не могут уникально определять запись, поскольку они будут одинаковы для всех записей, относящихся к одной и той же накладной. Поэтому введем в первичный ключ поле "Товар". При этом исходим из имеющегося правила, что по одной накладной может быть отпущено одно наименование конкретного товара, то есть не может иметь место ситуация, когда отпуск одного и того же товара оформляется в накладной двумя строками (что влечет за собой две одинаковые записи в таблице "Отпуск товаров со склада"):

Дата Покупатель Адрес

10.01.97

ТОО "Геракл" г. Москва , ул. Стромынка, 20
Отпущен товар Количество ед. изм. Цена ед.изм . Общая стоимость
Тушенка 6000 банки 7000 42 000 000
Тушенка 4000 банки 7000 28 000 000
Сахар 200 кг 5000 1 000 000
Макароны 1000 кг 3000 3 000 000
Итого 74 000 000

Покажем на рис. 1.20. структуру таблицы "Отпуск товаров со склада" после выделения полей в составе первичного ключа (эти поля отчеркнуты от прочих полей линией и располагаются в верхней части структуры таблицы).

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

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

Первое требование 2НФ выполнено. Чего не скажешь о втором требовании, гласящем, что значения всех полей записи должны однозначно зависеть от совокупного значения первичного ключа и не должна иметь место ситуация, когда некоторые поля зависят от части первичного ключа. Действительно, при дальнейшем анализе можно увидеть, что поля "Единица измерения", "Цена за единицу измерения" зависят только от значения поля "Товар". В самом деле, стоимость единицы измерения товара и название самой единицы измерения не зависят от конкретной накладной и будут одинаковыми для всех накладных, в которые входит данный товар. Поэтому выделяем данные поля в отдельную таблицу "Товары" и определяем связь: поскольку один товар может присутствовать во многих накладных, таблицы "Товары" и "Отпуск товаров со склада" находятся в связи "один-ко-многим" (рис. 1.22.).

После анализа структуры таблицы "Отпуск товаров со склада" можно заметить, что значение поля "Покупатель" никоим образом не зависит от пары значении "Номер накладной", "Товар", а зависит только от значения поля "Номер накладной". Поэтому данное поле и зависящие от его значения поля "Город", "Адрес" выделяются в отдельную таблицу "Покупатели" (рис. 1 23.)

Анализируя далее структуру таблицы "Отпуск товаров со склада", можно заметить, что одно из оставшихся полей - "Дата" зависит только от значения поля "Номер накладной". Поэтому выделяем дату и номер накладной в отдельную таблицу "Накладные" (рис. 1.24).

Установим связи между таблицами Один покупатель может встречаться во многих накладных. Поэтому между таблицами "Покупатели" и "Накладные" имеется связь "один-ко-многим" по полю "Покупатель". Одной накладной может соответствовать несколько товаров Поэтому между таблицами "Накладные" и "Отпуск товаров со склада" имеется связь "один-ко-многим" по полю "Номер накладной" (рис 1 25).

Для того чтобы уяснить, до конца нормализованы таблицы в составе разрабатываемой нами БД или нет, проанализируем ее структуру с позиций третьей нормальной формы (ЗНФ)

Третья нормальная форма

(ЗНФ) требует, чтобы в таблице не имелось Транзитивных зависимостей между неключевыми полями, то есть чтобы значение любого поля таблицы, не входящего в первичный ключ, не зависело от значения другого поля, не входящего в первичный ключ.

Продолжим рассмотрение примера. Можно увидеть, что в таблице "Отпуск товаров со склада" имеется зависимость значения поля "Общая стоимость" от значения поля "Количество". Значение поля "Общая стоимость" может вычисляться как значение поля "Количество", умноженное на значение поля "Цена за единицу измерения" из таблицы "Товары" (из записи с таким же значением поля "Товар"). Поэтому поле "Общая стоимость" из таблицы "Отпуск товаров со склада" удаляем В результате получаем нормализованную базу данных, структура которой приводится на рис. 1 26.

Замечание.

В таблице "Покупатели" значение поля "Адрес" зависит от значения поля "Город", поскольку в разных городах могут оказаться улицы с одинаковыми названиями и, соответственно, дома с одинаковыми номерами (вспомним известный кинофильм "Ирония судьбы, или с легким паром"). Думается, что такой зависимостью можно пренебречь, поскольку поле "Адрес" в нашем случае носит чисто информационный характер и не должно входить в условия запросов самостоятельно. Вообще говоря, на практике не всегда возможно получить идеально нормализованную БД. Часто к этому и не стремятся - по причинам, изложенным в следующем разделе.

Содержание раздела