Теперь займемся
проектированием эффективной структуры данных. На сегодняшний день известны три
модели данных: иерархическая, сетевая и реляционная. Так как Microsoft Visual FoxPro – это реляционная СУБД, то
выбора у нас нет. Теория реляционных баз данных была
разработана в начале 70-х годов Коддом (E. F. Codd) на основе математической теории отношений. В реляционной базе данных все данные хранятся в виде
таблиц, при этом все операции над базой данных сводятся к манипуляциям с таблицами.
Основными понятиями в этой теории являются: таблица, строка, столбец, индекс,
первичный и внешний ключи, связи. Таблица состоит из строк и столбцов и имеет
уникальное имя в базе данных. База данных содержит множество таблиц, связь
между которыми устанавливается с помощью совпадающих полей. В каждой из таблиц
содержится информация о каких-либо объектах одного типа.
Приступая
к созданию нового приложения, главное – самым тщательным образом спроектировать
структуру его таблиц. Если не уделить структуре должного внимания, то в лучшем
случае это может проявиться в неэффективной работе приложения, а в худшем – в
невозможности реализации некоторых требований к системе в целом. И, наоборот,
при хорошей организации набора таблиц будут решены не только текущие проблемы,
но и потенциальные, которые в данный момент вы не могли предвидеть. В общем,
структура данных является определяющим фактором успеха или провала всего
приложения.
Э.Ф. Кодд доказал,
что, следуя при создании таблиц и связей между ними только немногим
формализованным правилам, можно обеспечить простоту манипулирования данными.
Его методика получила наименование нормализации
данных. Теория реляционных баз данных основана на концепции использования
ключевых полей для определения отношений между таблицами. Чем больше таблиц,
тем больше отношений требуется определить, чтобы связать их между собой. Из
теории Кодда отнюдь не следует, что каждая таблица должна быть напрямую связана
с любой другой таблицей. Но, поскольку каждая таблица связана хотя бы с одной
таблицей в базе данных, можно утверждать, что все таблицы в базе имеют прямые
или косвенные отношения друг с другом.
Мы установили, какие
поля будут включены в базу данных. Следующий этап состоит в разделении их на
таблицы. Конечно же, можно было бы работать с приведенной
выше единственной таблицей «Недвижимость», но даже не знающим правил
нормализации ясно, что для каждого проживающего в квартире не имеет смысла
повторять всю информацию о здании, квартире, ответственном квартиросъемщике и
лицевом счете, а при переименовании улицы – вносить исправления в тысячи
записей, содержащих сведения о технических характеристиках квартиры.
Наличие повторяющейся
информации приведет к неоправданному увеличению размера базы данных. В
результате снизится скорость выполнения запросов. При многократном вводе
повторяющихся данных возрастет вероятность ошибки.
Представьте себе
ситуацию, связанную с вводом данных о проживающих на
Восточном шоссе. Это пять тысяч человек. Вот несколько вариантов адреса: Шоссе
Восточное, Восточное шоссе, ш. Восточное, ш-се Восточное. А сколько еще вариантов может появиться у
оператора, работающего с вашей программой. О грамматических ошибках и вариантах с номером дома, запятыми и
точками в адресе позволю себе умолчать. Какую информационно-поисковую систему
мы получим в результате? Скорее всего: искать можно – найти нельзя! Вашему вниманию – несколько советов по
включению полей в таблицы.
·
Включайте поля, относящиеся только к предметной области таблицы. Поле, представляющее факт из другой предметной
области, должно принадлежать другой таблице. Позже, при установлении отношений
между таблицами, вы увидите, как можно объединить данные нескольких полей из
разных таблиц. А на этом этапе убедитесь, что каждое поле таблицы описывает
только одну предметную область. Если вы обнаружите, что в разных таблицах
встречается однотипная информация, значит, какие-то таблицы содержат лишние
поля.
·
Не включайте производные или вычисляемые данные. В большинстве случаев вам нет необходимости хранить
результаты вычислений в таблице. С помощью Visual FoxPro вы всегда сможете выполнить
необходимые вычисления в нужный момент. Не имеет смысла хранить итоговые поля в
таблице.
·
Включите всю необходимую информацию Довольно
легко упустить важные данные. Вернитесь к собранным на первой стадии
проектирования материалам. Внимательно рассмотрите бланки и отчеты и убедитесь
в том, что вся интересующая вас информация может быть извлечена или вычислена
из таблиц. Подумайте о вопросах, которые вы будете формулировать к базе данных.
Сможете ли вы получить на них ответ, используя данные из ваших таблиц? Включили
ли вы поля, в которых будут храниться уникальные данные типа кода клиента?
Какие таблицы содержат информацию, необходимую для создания отчета или формы?
·
Разделите информацию на наименьшие логические единицы. Вам может показаться удобным иметь
одно поле для хранения полного имени клиента или одно поле для названия и
описания продукта. Если вы объединяете более одной категории информации в одном
поле, вам будет потом весьма непросто выделить из него
отдельные факты. Постарайтесь разбить информацию на наименьшие логические
части.
Воспользуемся практическими рекомендациями теории
нормализации для разработки на основании таблицы «Недвижимость» многотабличной
базы данных «Real Estate».
На рис. 2.1 вы видите
то, что у вас должно получиться после всех манипуляций, кратко изложенных выше
и предусмотренных теорией нормализации. Практический же путь к этому результату
смотрите на следующих полутора десятках страниц.
Первая
нормальная форма. Таблица находится в первой нормальной форме,
если значения всех ее полей атомарные, и в ней отсутствуют повторяющиеся группы
полей.
На «заре»
существования реляционных баз данных на количество полей в записи накладывались
определенные ограничения. Как следствие, разработчики объединяли несколько
предполагаемых полей в одно, чтобы все нужные данные поместить в одну запись.
Известно, что если поле содержит несколько значений, то существенно усложняется
формирование отношений между полями, считывание данных и выполнение других
операций, а необходимость выполнения поиска подстрок и синтаксического анализа
полей в значительной степени замедляет работу приложения. К счастью, сейчас все
ограничения на количество полей в записи сняты.
Приведем наши данные к первой нормальной форме.
Выделим самостоятельные группы полей и поместим их в отдельные таблицы. На
первый взгляд их четыре. Это информация об адресе, здании, квартире и
собственниках. Добьемся атомарности всех полей. Поле FioHost, в которое записывается информация о фамилии, имени и
отчестве ответственного квартиросъемщика, заменим тремя полями: Family, Name, Second. Также
поступим и с проживающими в квартире. Поле Address разобьем на три: название, признак и порядок их
следования в официальных документах. Получится следующая картина (табл. 2.1).
Информация об адресе (Street)
№ |
Поле |
Тип |
Размер |
Описание |
1 |
Street |
Числовой |
4 |
Номер улицы |
2 |
Name |
Текстовый |
30 |
Название
улицы |
3 |
Sign |
Текстовый |
10 |
Признак
адреса |
4 |
First |
Логический |
1 |
Порядок
следования в документах |
Street |
Name
|
Sign |
First |
173 |
Воронежская |
Улица |
Ложь |
174 |
Воронежский |
проезд |
Истина |
175 |
Воронежское |
шоссе |
Истина |
176 |
Ворошилова |
Улица |
Ложь |
Если
значением поля First является Ложь, то при формировании адреса
здания в официальных документах на первое место будет поставлен признак: Улица
Ворошилова, а если Истина – название: Воронежское шоссе или Воронежский проезд.
Обратите внимание на заполнение поля Sign. Если в поле First стоит Ложь, то значение признака пишется с большой
буквы.
Обратите внимание на
то, как легко будет сейчас решаться проблема переименования улицы. Допустим,
что отныне Воронежское шоссе, стоящее под номером 175 в таблице Street, переименовано, например, в улицу Муравьева-Амурского.
Вносим исправления только в таблицу Street.
Оставляем этот номер, меняем название, признак и значение поля First c Истина
на Ложь. Проблема решена. Так как во всех остальных таблицах Воронежское шоссе
(улица Муравьева-Амурского) фигурирует под номером 175, то никакие изменения не
требуются.
Информация о здании (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 |
Наличие
лифта |
Информация о квартире (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 |
Авто |
Данные его
паспорта |
Информация о проживающих в квартире (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).
Информация о районах города (District)
№ |
Поле |
Тип |
Размер |
Описание |
1 |
District |
Числовой |
1 |
Номер района |
2 |
Area |
Текстовый |
15 |
Название района |
Информация о материале стен здания (Wall)
№ |
Поле |
Тип |
Размер |
Описание
|
1 |
Material |
Числовой |
1 |
Номер материала |
2 |
Wall |
Текстовый |
15 |
Название
материала |
Структура
таблицы Building несколько изменилась. Вместо описаний района и
материала стен появились ссылки на соответствующие таблицы (табл. 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, то
разберитесь по специальной литературе с нормальной формой Бойса-Кодда,
четвертой и пятой нормальными формами.