2. НОРМАЛИЗАЦИЯ ДАННЫХ

Теперь займемся проектированием эффективной структуры данных. На сегодняшний день известны три модели данных: иерархическая, сетевая и реляционная. Так как Microsoft Visual FoxPro – это реляционная СУБД, то выбора у нас нет. Теория реляционных баз данных была разработана в начале 70-х годов Коддом (E. F. Codd) на основе математической теории отношений. В реляционной базе данных все данные хранятся в виде таблиц, при этом все операции над базой данных сводятся к манипуляциям с таблицами. Основными понятиями в этой теории являются: таблица, строка, столбец, индекс, первичный и внешний ключи, связи. Таблица состоит из строк и столбцов и имеет уникальное имя в базе данных. База данных содержит множество таблиц, связь между которыми устанавливается с помощью совпадающих полей. В каждой из таблиц содержится информация о каких-либо объектах одного типа.

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

Э.Ф. Кодд доказал, что, следуя при создании таблиц и связей между ними только немногим формализованным правилам, можно обеспечить простоту манипулирования данными. Его методика получила наиме­но­вание нормализации данных. Теория реляционных баз данных основана на концепции использования ключевых полей для определения отно­шений между таблицами. Чем больше таблиц, тем больше отношений требуется определить, чтобы связать их между собой. Из теории Кодда отнюдь не следует, что каждая таблица должна быть напрямую связана с любой другой таблицей. Но, поскольку каждая таблица связана хотя бы с одной таблицей в базе данных, можно утверждать, что все таблицы в базе имеют прямые или косвенные отношения друг с другом.

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

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

Представьте себе ситуацию, связанную с вводом данных о проживающих на Восточном шоссе. Это пять тысяч человек. Вот несколько вариантов адреса: Шоссе Восточное, Восточное шоссе, ш. Восточное, ш-се Восточное. А сколько еще вариантов может появиться у оператора, работающего с вашей программой. О грамматических ошибках  и вариантах с номером дома, запятыми и точками в адресе позволю себе умолчать. Какую информационно-поисковую систему мы получим в результате? Скорее всего: искать можно – найти нельзя! Вашему вниманию – несколько советов по включению полей в таблицы.

·       Включайте поля, относящиеся только к предметной области таблицы. Поле, представляющее факт из другой предметной области, должно принадлежать другой таблице. Позже, при установлении отношений между таблицами, вы увидите, как можно объединить данные нескольких полей из разных таблиц. А на этом этапе убедитесь, что каждое поле таблицы описывает только одну предметную область. Если вы обнаружите, что в разных таблицах встречается однотипная информация, значит, какие-то таблицы содержат лишние поля.

·       Не включайте производные или вычисляемые данные. В большинстве случаев вам нет необходимости хранить результаты вычислений в таблице. С помощью Visual FoxPro вы всегда сможете выполнить необходимые вычисления в нужный момент. Не имеет смысла хранить итоговые поля в таблице.

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

·       Разделите информацию на наименьшие логические единицы. Вам может показаться удобным иметь одно поле для хранения полного имени клиента или одно поле для названия и описания продукта. Если вы объединяете более одной категории информации в одном поле, вам будет потом весьма непросто выделить из него отдельные факты. Постарайтесь разбить информацию на наименьшие логические части.

Воспользуемся практическими рекомендациями теории нормализации для разработки на основании таблицы «Недвижимость» многотабличной базы данных «Real Estate».

На рис. 2.1 вы видите то, что у вас должно получиться после всех манипуляций, кратко изложенных выше и предусмотренных теорией нормализации. Практический же путь к этому результату смотрите на следующих полутора десятках страниц.

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

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

 

 


Приведем наши данные к первой нормальной форме. Выделим самостоятельные группы полей и поместим их в отдельные таблицы. На первый взгляд их четыре. Это информация об адресе, здании, квартире и собственниках. Добьемся атомарности всех полей. Поле FioHost, в которое записывается информация о фамилии, имени и отчестве ответственного квартиросъемщика, заменим тремя полями: Family, Name, Second. Также поступим и с проживающими в квартире. Поле Address разобьем на три: название, признак и порядок их следования в официальных документах. Получится следующая картина (табл. 2.1).

Таблица 2.1

Информация об адресе (Street)

 

Поле

Тип

Размер

Описание

1

Street

Числовой

4

Номер улицы

2

Name

Текстовый

30

Название улицы

3

Sign

Текстовый

10

Признак адреса

4

First

Логический

Порядок следования в документах

 

Street

Name

Sign

First

173

Воронежская

Улица

Ложь

174

Воронежский

проезд

Истина

175

Воронежское

шоссе

Истина

176

Ворошилова

Улица

Ложь

 

Если значением поля First  является Ложь, то при формировании адреса здания в официальных документах на первое место будет поставлен признак: Улица Ворошилова, а если Истина – название: Воронежское шоссе или Воронежский проезд. Обратите внимание на заполнение поля Sign. Если в поле First стоит Ложь, то значение признака пишется с большой буквы.

Обратите внимание на то, как легко будет сейчас решаться проблема переименования улицы. Допустим, что отныне Воронежское шоссе, стоящее под номером 175 в таблице Street, переименовано, например, в улицу Муравьева-Амурского. Вносим исправления только в таблицу Street. Оставляем этот номер, меняем название, признак и значение поля First c Истина на Ложь. Проблема решена. Так как во всех остальных таблицах Воронежское шоссе (улица Муравьева-Амурского) фигурирует под номером 175, то никакие изменения не требуются.

 Таблица 2.2

Информация о здании (Building)

 

Поле

Тип

Размер

Описание

1

Street

Числовой

4

Ссылка на номер улицы

2

House

Текстовый

4

Номер дома

3

District

Текстовый

15

Район города

4

Land

Числовой

10

Площадь земельного участка

5

Year

Числовой

4

Год постройки здания

6

Material

Текстовый

15

Материал стен здания

7

Comment

Поле Memo

Авто

Примечания

8

Wear

Числовой

2

Износ в процентах

9

Cost

Денежный

15

Стоимость здания в рублях

10

Line

Числовой

5

Расстояние от центра города

11

Square

Числовой

10

Площадь нежилых помещений

12

Picture

Поле OLE

Авто

Фото здания

13

Kind

Числовой

1

Вид собственности

14

Elevator

Логический

1

Наличие лифта

 

Таблица 2.3

Информация о квартире (Flat)

 

Поле

Тип

Размер

Описание

1

Street

Числовой

4

Ссылка на номер улицы

2

House

Текстовый

4

Номер дома

3

Flat

Числовой

4

Номер квартиры

4

Storey

Числовой

2

Номер этажа

5

Rooms

Числовой

1

Количество комнат

6

SquareFlat

Числовой

Авто

Общая площадь квартиры

7

Dwell

Числовой

Авто

Жилая площадь квартиры

8

Branch

Числовой

Авто

Вспомогательная площадь квартиры

9

Balcony

Числовой

Авто

Площадь балкона

10

Height

Числовой

Авто

Высота квартиры

11

Account

Числовой

5

Номер лицевого счета

12

Family

Текстовый

20

Фамилия квартиросъемщика

13

Name

Текстовый

20

Имя квартиросъемщика

14

Second

Текстовый

20

Отчество квартиросъемщика

15

Pasport

Поле Memo

Авто

Данные его паспорта

 

Таблица 2.4

Информация о проживающих в квартире (Owners)

 

Поле

Тип

Размер

Описание

1

Street

Числовой

4

Ссылка на номер улицы

2

House

Текстовый

4

Номер дома

3

Flat

Числовой

4

Номер квартиры

4

Number

Числовой

2

Порядковый номер проживающего

5

Family

Текстовый

20

Фамилия проживающего

6

Name

Текстовый

20

Имя проживающего

7

Second

Текстовый

20

Отчество проживающего

8

Born

Числовой

4

Год рождения проживающего

9

Status

Текстовый

20

Льготы и статус проживающего

 

Удовлетворение требованиям первой нормальной формы называется структурной или синтаксической нормализацией.

 


  

  Данные разделены (табл. 2.1, 2.2, 2.3, 2.4) на четыре родственные группы: улицы, здания, квартиры и проживающие (рис. 2.2). Значения всех полей этих таблиц – атомарные. Все таблицы находятся в первой нормальной форме. Однако останавливаться на этом не следует. С такими данными все еще возможно возникновение проблем. Прежде всего, в базе данных много повторений значений – не внутри одной записи, а в пределах одной таблицы. А там, где есть повторяющиеся значения, возможны противоречия. Посмотрите на поля Material и District таблицы Building. Та же картина, которая имела место чуть раньше с названиями улиц. Варианты названий материала стен: шлакобетон, шлакобетонные, шлб, шлет. Уберем название материала стен и названия районов в отдельные таблицы – справочники (Wall и District), оставив в основной таблице Building ссылки на эти справочники. База данных примет более правильный вид (таблицы на рис. 2.3).

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Рис. 2.3. Таблицы базы данных в первой нормальной форме

 

Появились еще две таблицы: Wall и District (табл. 2.5, табл. 2.6).

Таблица 2.5

Информация о районах города (District)

 

Поле

Тип

Размер

Описание

1

District

Числовой

1

Номер района

2

Area

Текстовый

15

Название района

Таблица 2.6

Информация о материале стен здания (Wall)

 

Поле

Тип

Размер

Описание

1

Material

Числовой

1

Номер материала

2

Wall

Текстовый

15

Название материала

 

Структура таблицы Building несколько изменилась. Вместо описаний района и материала стен появились ссылки на соответствующие таблицы (табл. 2.7).

Таблица 2.7

Окончательная структура таблицы Building

 

Поле

Тип

Размер

Описание

1

Street

Числовой

4

Ссылка на номер улицы

2

House

Текстовый

4

Номер дома

3

District

Числовой

1

Ссылка на район города

4

Land

Числовой

10

Площадь земельного участка

5

Year

Числовой

4

Год постройки здания

6

Material

Числовой

1

Ссылка на материал стен здания

7

Comment

Поле Memo

Авто

Примечания

8

Wear

Числовой

2

Износ в процентах

9

Cost

Денежный

15

Стоимость здания в рублях

10

Line

Числовой

5

Расстояние от центра города

11

Square

Числовой

10

Площадь нежилых помещений

12

Picture

Поле OLE

Авто

Фото здания

13

Kind

Числовой

1

Вид собственности

14

Elevator

Логический

1

Наличие лифта

 

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

Настало время поговорить о ключевых полях. Мощь реляционных баз данных, таких как Microsoft Visual FoxPro, опирается на их способность быстро найти и связать данные из разных таблиц при помощи запросов, форм и отчетов. Для этого каждая таблица должна содержать одно или несколько полей, однозначно определяющих каждую запись в таблице. Такие поля называют первичным ключом таблицы. Если для таблицы определен первичный ключ, то Microsoft Visual FoxPro предотвращает дублирование значений полей или ввод значений Null в эти поля. В Microsoft Visual FoxPro можно выделить три типа ключевых полей: простой ключ, составной ключ и счетчик (Integer AutoInc). Если поле содержит уникальные значения, то его можно определить как ключевое или простой ключ. Примеры из нашей реальной жизни: идентификационный номер налогоплательщика, однозначно определяющий каждого жителя нашей страны, номер свидетельства пенсионного фонда, кадастровый номер земельного участка, реестровый номер строе­ния, номер автомобиля – все это уникальные номера в пределах страны. Поле Street (номер улицы) в таблице Street также можно определить как простой ключ. Этим же требованиям отвечают поля District  (номер района) и Material (номер материала) таблиц District и Wall. Можно смело гарантировать их уникальность в пределах нашего программного комплекса. С таблицей Building, содержащей информацию о зданиях, при определении первичного ключа нужно поступить таким образом. К нашим услугам составной ключ. Связка полей – номер улицы плюс номер дома – однозначно определит положение записи, относящейся к одному зданию в этой таблице. С однозначным определением  квартиры в таблице Flat  (квартиры) дело состоит чуть сложнее. Составной первичный ключ выглядит так: номер улицы плюс номер дома плюс номер квартиры.

 В очень редких случаях с определением первичного ключа для таблицы может сложиться тупиковая ситуация. Не отчаивайтесь, добавьте в таблицу поле и определите его тип как «Integer (AutoInc)». Все остальное Visual FoxPro сделает самостоятельно. В это поле будет автоматически вноситься уникальное число даже при работе с Вашей базой в сетевом варианте (с нескольких компьютеров одновременно).

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

Посмотрите внимательно на таблицу Flat (квартиры). Она содержит неключевое поле Account (номер лицевого счета), которое однозначно определяет ответственного квартиросъемщика (поля: Family, Name, Second и Pasport) в этой таблице. Уберем все эти поля в еще одну таблицу Account и назначим в ней в качестве простого первичного ключа поле Account (табл. 2.8).

Таблица 2.8

Информация об ответственном квартиросъемщике (Account)

 

Поле

Тип

Размер

Описание

1

Account

Числовой

5

Номер лицевого счета

2

Family

Текстовый

20

Фамилия квартиросъемщика

3

Name

Текстовый

20

Имя квартиросъемщика

4

Second

Текстовый

20

Отчество квартиросъемщика

5

Pasport

Поле Memo

Авто

Данные его паспорта

 

Осталось установить связи между таблицами, и база данных будет готова к работе. Microsoft Visual FoxPro поддерживает три типа связей: один к одному, один ко многим и много к одному.

     Связь «один к одному» означает, что каждой записи одной таблицы соответствует только одна запись другой таблицы и наоборот. В качестве примера рассмотрим связь между таблицами Flat и Account (рис. 2.4). Одна квартира – один ответственный квартиросъемщик. Связь между ними поддерживается при помощи совпадающих полей Account. Обратите внимание! У полей, используемых для связи, одинаковое наименование (Account) и тип (числовой с 5 разрядами). Всегда придерживайтесь этого правила при определении полей для связи любого типа между таблицами. Хотя, если быть более точным, связь между таблицами устанавливается на основании значений совпадающих полей, а не их наименований.

Связь «один ко многим». В качестве иллюстрации данного типа связи обратимся к таблицам Street и Building. Одной улице в таблице улиц Street соответствует несколько зданий из таблицы зданий Building. Связь между ними осуществляется на основании значений совпадающих полей Street. Используется простой первичный ключ таблицы Street. В качестве других примеров могут быть рассмотрены таблицы Building и Flat, Flat и Owners. Одному зданию соответствуют несколько квартир, а одной квартире – несколько собственников. Для связи этих таблиц используются составные первичные ключи.

Связь «много к одному» аналогично ранее рассмотренному типу «один ко многим». Тип связи между объектами полностью зависит от вашей точки зрения. Например, если вы будете рассматривать связь между собственниками и квартирой, то получите много к одному. Несколько собственников проживают в одной квартире.

 

 


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

 Обратившись к материалам главы 3.8, Вы увидите мое видение решения этой проблемы одного из вариантов курсового проекта.

Что за третьей нормальной формой? Если вы довели уровень нормализации таблиц вашей базы данных до третьей нормальной формы и ваша задача – разработка системы масштаба предприятия, то смело можете переходить к разработке интерфейса. Однако если вы участвуете в разработке суперхранилища данных под Oracle или DB2, то разберитесь по специальной литературе с нормальной формой Бойса-Кодда, четвертой и пятой нормальными формами.