Базы Данных: реляционные особенности и их практический смысл

А. Левин                                                                                        www.geoFAQ.ru

 

Содержание

Предисловие

Часть 1: основные реляционные особенности, теория

Введение

Множество как базисное понятие

Столбцы, строки, таблицы? Атрибуты, кортежи, отношения как множества

Типы данных? Домены!

Первичный ключ, внешний ключ, связи

Пропущенные значения (NULL)

Внешние ключи и связи

Нормализация

Часть 2. Практический смысл реляционных особенностей БД

Домены и ограничения

Первичные ключи и индексы

Внешний ключ и связи

Целостность и каскадные операции

Пропущенные значения

Нормализация

Описания данных и предметной области – форматы и методики проектирования

Мета-Табличный подход к описанию структур и проектированию

Недостатки реляционной модели

Литература, источники информации

 

Предисловие

Базы данных имеют в основе строгую математическую модель, почти все – реляционную, с корнями в теории множеств.  Применяется эта модель везде, где есть формализованная информация, вплоть до построения общения в сети. Разумеется, задействована она и в геотехнологиях. При разработке Баз Данных (БД) крайне важно участие специалистов-экспертов по конкретной области знаний. Даже профессиональные разработчики жалуются, что построение «хороших» БД не поддаётся чистой науке, а требует изрядной доли интуиции. Только тогда БД может быть грамотно спроектирована, тонко настроена, будет работать четко и развиваться непротиворечиво. Однако специалисты эти должны иметь четкое представление о модели данных, о принципах работы математического аппарата, заложенного в основу БД. Отсюда и возникла эта лекции, об основах реляционной модели данных – специалистам-практикам, которые, несомненно, будут востребованы в разработках информационных систем.

Часть 1: основные реляционные особенности, теория

Введение

Реляционная модель данных представляет интерес как наиболее проработанная математически. Прочие модели либо имеют исторический интерес, либо узкую область применения, либо представляют собой развитие реляционной модели. Не будем на них останавливаться. Название «реляционная» происходит от математического термина отношение, relation. Это аналог таблицы в теории баз данных. Термин не очень понятный: набор данных воспринимается как некое «взаимоотношение». Другие термины реляционной модели звучат более удачно – «домены», «атрибуты», «кортежи». Однако даже теоретики часто говорят вместо них: «типы данных», «столбцы», «строки», что уж говорить о разработчиках и простых пользователях! Тем не менее, даже подменяя термины, разницу между ними нужно понимать.

Данные, информация, сведения. Чтобы разобраться в базах данных, сначала нужно понять, что такое, собственно, сами данные? Чем они отличаются от не-данных, от просто информации? Являются ли бумажные листы данными или просто сведениями? Могут ли «твердые носители» составлять часть БД, как это часто приходится слышать? Стоит ли вставлять в БД космоснимки, чертежи, видео? Станут ли они от этого «данными», то есть органичной, работоспособной частью БД, или нет? Данные и информация, данные и сведения, в чем разница?

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

Хранятся ли данные физически в БД или снаружи, файловым или табличным способом, не так уж важно: с точки зрения системы все равно, поступают данные на момент запроса или записаны заранее в некие электронные структуры. Важна степень упорядоченности, однозначность и эффективность. Например, вспомним картотеки с прорезями и дырками по краям. Неэлектронный способ организации данных, однако достаточно эффективный – вставив в нужные отверстия спицы, можно было моментально выдернуть карточки «по запросу».

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

Формализация информации: процесс упорядочивания информации для обработки, превращения ее в данные обычно сводится к формализации информации, что похоже на организацию системы бланков. Эта работа известна спокон веку. В математике формализация – составление формул, когда упорядочивается способ расчета. В информатике - упорядочивание самой информации, когда из разрозненных сведений возникает стройный набор рядов и колонок. При создании БД это целый процесс. Он тем сложнее, чем меньше наработано «бланков» - готовых структур БД и приемов их обработки. Не следует думать, что формализация - дело исключительно программистов или математиков – наиболее разумные из них считают это наоборот, делом экспертов – знатоков предметной области. Чтобы использовать данные, а тем более формализовать информацию, участвуя в разработке, надо знать правила построения БД. Об этом и пойдет лекция.

Таким образом, «данные», «информация», «предметная область» - устоявшиеся термины информатики, а не просто слова.  И если знатоками данных по праву могут себя считать математики, то знатоками информации и предметной области являемся мы с вами. Для этого надо, однако, знать математические приемы работы. Приступим к их изучению.

Множество как базисное понятие

Понятие множества: это всего лишь неупорядоченный набор уникальных значений. Основные особенности множества это:

Эти важнейшие первичные свойства наследуются элементами реляционных баз данных. Зачем это вообще нужно, тем более на практике? Набор есть набор, навалим, потом разберемся. Ну и упорядоченность должна быть, хотя бы по номеру или времени создания… А вот и нет. Вся стройность «хороших» баз данных основана именно на уникальности, вся гибкость систем – на неупорядоченности.

На самом деле свойства множества в математике сформулированы несколько по-другому, но смысл именно такой: «должен быть способ отличить один элемент от другого», и «способ узнать, принадлежит ли элемент данной совокупности». Наши две  «особенности»  вытекают из этих свойств. Применяют множества там, где наблюдается «отсутствие связей между элементами», отсюда вытекает неупорядоченность как свойство самих данных, с которым приходится мириться.

Что дают Базам Данных эти особенности множества?

 

 

Рис. 1. МНОЖЕСТВО

 

Ограниченность изображают на рисунках как четкую границу, отделяющую «своих от чужих». Однако условия могут быть заданы по-разному. Например, «все целые числа», или «все числа от минус 0,5 до плюс 0.5». В общем случае элементов множества может быть бесконечно много, но всегда есть способ установить – число 3,1415 к обоим множествам не относится.

Уникальность позволяет не только искать и находить, но находить с гарантией, что эта находка будет единственной. Система поиска, таким образом, «строится» исходя из твердой уверенности, что данный объект – уникальный, правильно идентифицированный, и именно к нему относятся взятые в другом месте характеристики. Именно к этому речному переходу по каталогу, например, относятся данные промеров русла, а не к рекам вообще со сходным названием (а там разберемся), и не к участку с некими координатами в неизвестной проекции (там наверно где-то он и есть).

Неупорядоченность также важнейшее свойство. Система, получается, заранее не рассчитывает на некий порядок следования элементов. Таким образом, при любом обращении операция обойдет все элементы в множестве: это гарантировано на уровне системы. Понятно, что порядок при этом подразумевается и даже обязательно будет какой-либо, (если задуматься об уникальности!). Но это уже дело конкретной Системы Управления Базами Данных (СУБД), как оно там организовано на низком уровне или как значения сортируются на выходе. Для пользователя беспорядок гарантирован. И это свойство тоже идет от множества.

Далее мы увидим, что неупорядоченность и уникальность прослеживаются на разных уровнях организации реляционных БД, и всегда в тандеме. Не только для самих данных, но и для структур организации, для структур описания структур…

Тип данных, операции со множествами. Итак, условие задано, и появляется возможность в каждом конкретном случае понять, принадлежит ли элемент данному множеству, или находится за его пределами. Этим, по сути, задается тип данных. Для однотипных элементов появляется возможность сравнения, для сходных множеств тоже. Для этого все элементы множеств также должны быть однотипны. Над такими однотипными множествами возможны операции: объединение, пересечение, вычитание (оно же дополнение). Объединение на картинке это оба овала совместно, пересечение показано косой штриховкой, дополнение – точечным крапом.

 

Рис. 2. Операции над множествами

 

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

Рис. 2а. ограничение (выборка)

 

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

 

Рис. 2б. Декартово произведение

 

Как можно догадаться, все это наследуется в полной мере реляционными структурами и методами, дополняется специальной прикладной  математикой и имеет вполне определенный практический смысл.

Столбцы, строки, таблицы? Атрибуты, кортежи, отношения как множества

С таблицами знакомы все, это основной элемент баз данных. Но лишь немногие подозревают, что таблицы – лишь внешнее отражение сложных внутренних структур БД. Говоря точнее, таблица – результат ВЫВОДА данных на экран, на принтер. Почти всегда таблица – результат поиска, отбора, то есть на экране мы видим не все данные, а лишь их часть в удобной табличной форме. Есть и другие форматы… Как же все устроено «внутри» реляционной БД?

Атрибуты это самый простой элемент структуры. В таблице мы их видим как названия столбцов. Атрибуты по сути это множество имён столбцов. Множество? Да, именно в математическом смысле. То есть, во-первых, уникальное, во-вторых, неупорядоченное.

Уникальность атрибутов обеспечивается именованием. Система должна следить за тем, чтобы не было двух одинаковых. Поскольку таблиц много, то обычно спереди добавляется приставка - имя таблицы.

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

 

Рис. 4. Атрибуты

 

Обратите внимание, что множество атрибутов данной таблицы не сортировано. С точки зрения теории, последовательность A.11, A.3, A7, A.1, A.9 столь же уместна, как и A.1, A.3, A.7, A.9, A.11. Разумеется, порядок использования атрибутов может задаваться при операциях, выборке, например:

 

SELECT A.11, A.3, A7, A.1, A.9 FROM A

 

Собственно, вам предлагаются фразы языка SQL – Structured Query Language. Язык этот именно реляционный, и приводится для привыкания к реляционному стилю мысли, который он наглядно воплощает. Никакого упора на его изучение в тексте не будет, и если вам такой языковый подход покажется лишним, можете просто эти фразы пропустить – это дополнительный материал.

У атрибутов есть тип: в описании структуры таблицы обязательно указано, какой тип данных может быть присвоен атрибуту. Наиболее известные типы – числовой, текстовой, логический. Есть и другие типы, в том числе производные. Тип должен соблюдаться для всех значений атрибута. В таблице-каталоге скважин, например, могут быть атрибуты:

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

ID: 151k X: 7541203 Y: 5724619 Z: 31,6 H: 17,5.

В таком написании это больше похоже на кортеж, который есть не что иное, как набор пар: атрибут-значение. Не простых однако пар, но об этом чуть позже.

Является ли кортеж множеством? В руководствах это часто звучит. Однако сравнивать между собой вдоль по строке эти пары нельзя, значения ведь в парах разнотипные! Строго говоря, кортеж - не множество.  Это подчеркивается его названием, «cortege» переводится как цепочка, последовательность. Однако не следует думать, что значения в строках таблицы всегда выстроены – нет, как и для атрибутов, порядок следования может быть любой. Как правило, система обращается к значениям по именам атрибутов, а не по их порядку. В этом смысле изображения кортежей на рисунке равноправны.

 

Рис.5. Кортежи

 

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

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

Отношение по сути и есть множество таких однотипных цепочек - кортежей.  Именно множество, где каждый элемент имеет некоторый тип. Тип этот сложный, составной, можно его назвать тип «Скважина».

 

Рис.6. Отношение

 

Что за словечко вообще – «отношение»? Отношение чего к чему? Термин не выдуман специально для БД, он происходит из теории множеств, где обозначает сочетание одного множества с другим для составления пар, уже знакомое нам как декартово произведение. Одно исходное множество для сочетания мы видим – набор атрибутов, а другое? Это же просто значения, их бесчисленное множество. Множество?! Пожалуй, да, а вот бесчисленное ли? Об этом мы еще поговорим.

Итак, свойства отношений которые, как мы уже поняли, представляют собой множества однотипных элементов - кортежей:

  1. Уникальность составляющих кортежей – не должно быть двух одинаковых.
  2. Неупорядоченность кортежей. Порядок их следования, как можно догадаться, по умолчанию не определен.
  3. Кроме того, в отношении соблюдается неупорядоченность атрибутов. Как во всем отношении, так и по кортежам. Как было сказано выше, обращение системы к значениям идет строго по именам атрибутов, И никогда - по их физическому порядку.

Здесь уместны комментарии – на самом деле, в математике неупорядоченность множеств не считается определяющим свойством. Известны упорядоченные множества, частично упорядоченные… Однако реляционная теория основывается на самых простых, «классических» множествах, именно неупорядоченных. Вы не можете, таким образом, ожидать от отношения некоего порядка строк, а вынуждены задавать его явно специальными командами. Среди них нет команды «по умолчанию» или по «физическому порядку», как в жизни. Всегда как порядок используется одна из колонок–атрибутов. Стоит позаботиться о том, чтобы в таблице такие колонки были - например, атрибут «километраж по трассе» или «дата  бурения».

Итак, основные понятия реляционной теории – атрибут, кортеж, отношение.

В просторечии им соответствуют столбец, строка, таблица. Столбцы еще именуют колонками (columns), а строки – «записями» (records). Первое понятно, а второе имеет давнее происхождение, когда БД создавались последовательным вписыванием строк-значений, кропотливо и вручную.  Словом, в любых разработках, в описаниях БД, в терминологии SQL это синонимы, однако всегда под ними понимаются именно реляционные атрибуты, кортежи, отношения, а не наоборот.

Необходимо вкратце сказать о связи этих понятий с предметной областью:

Атрибут обычно понимают как свойство некоего объекта;

Кортеж представляет один какой-либо объект исследования, рассмотрения. Точнее, набор свойств объекта (что не одно и то же, если вдуматься).

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

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

Типы данных? Домены!

Основные типы данных в БД те же, что и в программировании:

Однако любых математических типов будет недостаточно, чтобы построить целостную базу данных и избежать несоответствий. Например, координаты XY  в системе Гаусса-Крюгера должны быть миллионы метров – не меньше и не больше. Высота Z не может быть выше 10 км, редкие виды растительности ограничиваются Красной Книгой… Это помогает не только отсекать возможные ошибки, но и заранее сузить область определения, задать ей практичные рамки. Такое пользовательское описание данных очень близко к понятию домена. Говоря точнее, домен это потенциально возможное множество значений. Domain в переводе означает «область», здесь смысл не расходится с переводом.

Свойства домена: очевидно, что домен является множеством, хотя в общем случае его значения нельзя просто перечислить. Зато всегда можно понять, в домене данное значение или нет. От множества, таким образом, наследуются свойства:

 

Рис. 7.  Домены

 

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

Домен и атрибуты. Итак, атрибуты должны быть увязаны с доменами, как говорят, «определены на некоем домене». Мало того, по теории БД понятие домена является краеугольным.  На одном домене могут быть заданы несколько атрибутов. Например, домен «Глубин» может определять возможные значения для атрибутов «Глубина скважины», «Глубина обсадки», «Глубина установки фильтра», и даже «Глубина грунтовых вод», хотя это уже не техническая характеристика. Все эти атрибуты близки по диапазону, и вряд ли каждому нужен отдельный домен. Наоборот, задав их на одном домене, мы фиксируем возможность сравнивать эти характеристики (что глубже чего расположено, например). Это очень ценно для БД, поскольку отражает естественные соотношения, задает возможность взаимопроверок, сравнений.

Атомарность значений: теория настаивает, что значения домена должны быть простыми, атомарными, как говорят, то есть не составными. Нельзя, как значение, записать в ячейку БД наборчик {УГВ: и 14.7 и 18.3 м} одновременно. Это вообще-то не  следует из теории множеств. Множества вообще могут содержать сложные элементы,. Однако домены нет – ведь тип объявлен явно! Если домен задан как «Числовое значение от нуля до 200 метров», то в ячейку БД можно вписать только ОДНО числовое значение, а не два и не три вместе, как варианты.  Иначе как будут процедуры сравнивать эти числа? Проверять уникальность? Словом, одно значение - это один тип, а два вместе – уже пара значений, принципиально другой тип элемента.

Некоторые системы, однако, грешат неатомарными значениями, разрешая последовательности и даже вложенные таблицы в качестве значений, пусть это будет на их совести, но реляционными считаться они не могут. Избежать неатомарности в нашем примере просто – заводим две колонки по смыслу этих значений, например, максимум и минимум, первое измерение и второе: по сути, задаем пару значений как некий новый тип. Таблица при этом остается реляционной. Об этом процессе реляционного улучшения еще придется поговорить.

Естественность доменов. Даже теоретики БД признают, что домены должны нести смысловую нагрузку. Можно, конечно, определить домен как числовой тип данных «от и до» и на этом успокоиться. Однако куда полезнее относиться к домену, как к некоей группе параметров описания предметной области, к некоему смысловому понятию. Например, домен «Координаты», «Азимуты», «Температуры», «Глубины» и тому подобные.

Одного только названия и математического условия для домена может оказаться недостаточно. Например, глубины скважин от 0 до 200 метров и глубины почвенных разрезов от 0 до 250 см вряд ли разумно считать одним доменом. Разные единицы измерения, способы, разная дискретность, вообще разные природные среды, ну и главное - трудно представить необходимость сравнения этих двух параметров между собой. Словом, не только условие важно, а все, что формулирует некую часть предметной области – описание, точность измерения, способ и т.д., но в первую очередь – естественный смысл.

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

Ограничить излишние сравнения между атрибутами, таким образом основное назначение доменов. С другой стороны, можно сказать: «наметить возможные сравнения между нужными атрибутами»! И не только в одной БД - если в разных массивах данных описаны сходные домены – координаты в одной системе, температуры по Цельсию, глубины от поверхности в метрах, то понятно, что вполне возможно их значения сравнивать, сопоставлять между собой, совместно анализировать! Таким образом, ограничения оборачиваются свободой действий, свободой манипуляции с данными.

Первичный ключ, внешний ключ, связи

Уникальность строк-кортежей должна обеспечиваться в таблице, помните? В связи с этим первичный ключ – primary key - очень важное понятие, можно сказать «ключевое». Теоретически это набор значений, который однозначно идентифицирует данный кортеж. Точнее сказать, набор атрибутов отношения, минимально необходимый для идентификации. В теории допустимо, чтобы хоть одно значение различалось, в самой последней колонке, например. Однако мы знаем, что в реальности есть свойства важные, основные, а есть вспомогательные, несущественные. На практике удобно заранее определить небольшой набор признаков, существенных для описания объекта предметной области. Это и будет первичный ключ. Он может быть простой – из одной колонки, и составной – из нескольких. В принципе возможны таблицы, где все колонки входят в первичный ключ, все признаки существенны.

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

 

УРОВНИ ГРУНТОВЫХ ВОД

СКВАЖИНА

ДАТА ЗАМЕРА

Уровень

151k

01.06.99

13,1

151k

08.06.99

14,2

119

01.06.99

6,4

119

05.06.99

8,3

27

01.06.99

7,1

27

01.06.99

11,7

 

Рис 8. Первичный ключ

 

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

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

Практический смысл первичного ключа очевиден: объект предметной области однозначно описывается с помощью набора атрибутов таблицы. Если меняется понимание объекта, меняется и этот набор атрибутов. В примере со скважинами, если мы захотим измерять УГВ каждый час, придется добавить колонку «Время», если захотим вводить несколько образцов на каждый почвенный горизонт – добавим в ключ «Глубину» в сантиметрах.

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

Пропущенные значения (NULL)

Базы данных разрешают пропущенные значения, обозначая их словечком «NULL» - «не известно». Казалось бы, что тут необычного?  В таблицах и должны быть пробелы, куда от них денешься? Однако с точки зрения реляционной теории это не так уж очевидно. Значения атрибута, как мы знаем, должны быть однотипны. Можно ли сравнить любое целочисленное значение с NULL'ем? Значение -1 (минус один) будет больше NULL или нет? Ответ один – NULL, то есть опять-таки «не известно». Понятно, что это принципиально другой, особый тип данных. Очевидно, что это ведет к усложнению логики, ведь на любой вопрос к ячейке может быть теперь не два, а три ответа – «ДА», «НЕТ» и «не известно». В реальности так и происходит – команды СУБД содержат особые фразы для условий вида «IS NOT NULL».  Теория их запрещает во многих случаях, например, в составе первичного ключа, и все системы за этим неукоснительно следят.

Внешние ключи и связи

Внешний ключ – Foreign key - служит для связи таблиц. Это значения из одной таблицы, по которым можно однозначно привязаться к другой. Точнее говоря, для отношения внешний ключ - это опять-таки набор определенных заранее атрибутов. Например, в таблице точек наблюдений может быть атрибут «Административный Район», где для каждой точки проставлен код района, которому она принадлежит. Имеется таблица-справочник административных районов, в которой каждый район описан отдельной строкой. Для каждой точки по коду района можно найти его название и другие характеристики. Можно вообще соединить две таблицы в одну по этим ключам. Говорят, что атрибут «Район» – внешний ключ, ссылающийся на другую таблицу. Колонка ID в той, второй таблице, должна быть обязательно первичным ключом, иначе могут случайно сыскаться два одинаковых кода района в разных строках и система даст сбой, не сумеет однозначно привязаться.

 

Рис. 10. Внешний ключ – Foreign key

 

Таким образом, на одну точку обеспечивается одно значение, одна строка в таблице РАЙОНЫ. Наоборот, точек к такому району может относиться множество, и связь называется «один-ко-многим», это наиболее распространенный тип связи. На диаграммах конец связи «ко многим» обозначается разветвлением, стрелкой или знаком бесконечность.

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

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

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

Рис. 11. Связь таблицы «с собой же»

 

Таким компактным образом в реляционной БД может храниться иерархическая структура. Система первичных и внешних ключей, таким образом, составляет основу структуры БД, ее каркас.

Первичный ключ определен на каком-либо домене. Чтобы связь работала четко, на этом же домене должен быть определен и внешний ключ. В нашем примере это очевидно: набор кодов административных районов должен быть один и тот же, а не для разных областей. Идя далее, можно говорить, что к любому первичному ключу потенциально возможна связь, и соответственно, на его домене, скорее всего, будет основан внешний ключ в другой таблице. Такие «ведущие» домены особенно важны – ведь от них зависит структура не только таблиц, но и всей БД! 

Нормализация

Выше мы видели примеры, когда таблица не вполне соответствует требованиям реляционной теории. Содержит неатомарные значения, например, или объекты относятся к разным типам. Теоретически проработана методика, как поступать с такими таблицами. Описаны первая нормальная форма, когда значения всех атрибутов атомарны. Математически строго определены вторая нормальная форма, третья и так далее, как результаты улучшения предыдущих форм. Это улучшение называется нормализацией.

Как происходит нормализация? Уже приводились примеры неатомарности значений. Допустим, в какой-то колонке встречаются два значения вместо положенного одного (по сути это и не реляционное отношение вовсе, а сырые данные). Нужно разделить колонку на две. Разумеется, каждому значению придется придать смысл – максимальное значение и минимальное, среднее и максимум, и т.п. Но откуда взять этот смысл? Теория вам этого не скажет, математика не поможет, тут потребуется анализ данных, а точнее, исходной информации экспертом. На этом простом примере видно, что вся теория нормализации в реальности упирается в тщательную работу с первоисточниками, в грамотную формализацию. Мало того – на деле выходит, что теорией этой пользоваться опасно. Однако, с другой стороны, для проведения нормализации вполне достаточно здравого смысла, опыта и интуиции эксперта. Ведь когда, на каком этапе проекта делить колонку надвое, тоже важно! Если такой случай один на сто тыщ, ясно, что он погоды не делает - не лучше ли просто его игнорировать (ввести только среднее), пока такие исключения не накопяться? А тогда уж вводить в БД не только «max» и «min», а полные ряды наблюдений по совершенно другой системе, с точным временем измерения, сопутствующими условиями замера – словом, по необходимости.

В каких именно случаях и какая нужна нормализация, будет показано дальше.


Часть 2. Практический смысл реляционных особенностей БД

Домены и ограничения

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

Ограничения математического типа можно задавать в любой СУБД. В структуре таблиц можно задавать ограничения «от» и «до» любым числовым атрибутам,  это уже шаг к полноценным доменам. Ограничив значения от нуля до 50 м/с, получаем домен скоростей ветра. Кроме того, возможны и сложные условия, и даже процедурные проверки.

Другим видом задания домена является перечисление, как вы уже видели выше. Обычно оно реализуется в виде списков, например, перечисление всех возможных румбов: N, NE, E, SE, S, SW, W, NW. Такие законченные списки хранятся в отдельных местах, порой таких, что их трудно пополнять и редактировать..  Однако в общем случае список не может состоять из одной колонки, и не может считаться стопроцентно законченным. Классификация, как и в реальности, должна пополняться, редактироваться, адаптироваться, совершенствоваться. Для такой работы в реляционных БД всегда есть готовые структуры – сами таблицы! Мы знаем, что связь таблицы-списка с основной работает как ограничение, не дает вводить незарегистрированные значения, а только краснокнижные виды, например. Именно с помощью таблиц можно организовать домен более гибко – например, как список всех возможных видов фауны, с пометкой особо охраняемых по категориям. На практике при разработке БД выделяется особый класс таблиц – справочники-классификаторы, это и есть самый удобный способ задания перечисляемых доменов. Таблица видов флоры и фауны, почвенных горизонтов, административных единиц – словом, в реальной гео-системе все классификации природной среды и смежных отраслей обязательно должны быть! И работать целостно, непротиворечиво, удобно – например, в виде понятных выпадающих списков и при вводе, и при поиске, и при анализе данных. Словом, для реализации доменов вполне подходят те же механизмы, что и для самих данных. В любом случае важно еще соблюдать какой-либо принцип именования и взаимоотношения доменов – он частенько отсутствует, и его приходится придумывать разработчику. Впрочем, это, скорее, вопрос дисциплины проектирования и программирования.

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

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

Родственные домены могут и должны составлять структуру, каркас хорошей БД. Например, виды растительности: они объединяются в роды,  возникает еще одна таблица. Логичным образом возникают семейства, отряды, типы… Если эти соподчиненные таблицы работают  как домены, ограничивая значения только известными, это задает ни много ни мало, а модель предметной области в части растительности.

Другой пример моделирования предметной области – координаты XYZ. Если собрать домены координат и глубин в одной системе, то они определят пространство исследования. Если добавить еще и время, то таким образом будет заранее задана некая пространственно-временная модель. Для нее будут известны единицы измерения, может быть задана дискретность, описаны неоднородности (подрайоны, сезоны и т.п.). Таким образом, на основе взаимосвязанных доменов также возникает заранее удобная и полезная аппроксимация.

Можно осмелиться и сказать, что вообще конкретное условие, набор значений для доменов второстепенное дело. Важен в первую очередь естественный смысл, описанный  «по-русски». Конкретные условия и наборы могут меняться со временем, но смысл должен оставаться и даже наследоваться другими смежными базами данных.

Целесообразность разработки требует, однако, постепенности. Приведем пример: всем видам данных системы разумно поставить ограничение. Сначала удобно ограничить глубину максимально известной на данный момент. Далее не исключено, что проект потребует более глубокого бурения, придется условие слегка расширить. Набор редких видов определяется только известными в регионе – но  в дальнейшем может случиться, что в ловушки забредет «соседний» зверь. Таким образом, и условия и справочники должны быть потенциально готовы к поэтапным изменениям, расширениям, дополнениям в ходе развития проекта.

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

Первичные ключи и индексы

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

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

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

 

CREATE TABLE DISTRICTS

( ID CHAR(5), NAME CHAR(32),

PRIMARY KEY (ID) );

 

В команде SQL на создание таблицы объявление PRIMARY KEY означает, что в некоторых колонках таблицы (ID, например) не должно быть дубликатов и, кроме того, еще и пропущенных значений.

Возможно задать отдельно «уникального индекс» еще по каким-то колонкам, кроме первичного ключа, «про запас» на будущее, например, по названию NAME.

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

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

Работа с дубликатами. В одной точке в одно время были замерены два уровня грунтовых вод. Если сами уровни одинаковы, то вопрос кажется простым – это ошибка, и можно удалить любую из строк без потери информации. Однако ситуацию лучше расследовать: данные, как мы знаем, имеют неформализованную подоснову, первичную информацию. По первичке мы увидим, что два измерения все же были, но с интервалом в минуту, а по методике положено делать замеры раз в сутки. В этом случае одно измерение явно лишнее, и может быть отброшено (или оставлено в архиве как контрольное). Если глубина замеров различна, то и так мы поступить не можем – возможно, дело в неточности измерения. Тогда придется осреднить показания, или взять максимум, или иным способом агрегировать значения. Это можно называть «поглощением дубликатов», или, в общем случае,  «работой с дубликатами». Лучше, когда такие вопросы формализованы уже самой методикой работ, но на практике случается всякое. На примере дубликатов еще раз подчеркнем, что первичный ключ не всегда может быть разработан сначала и до конца. Приходится дорабатывать его по мере пополнения БД и развития проекта.

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

Внешний ключ и связи

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

Пример организации связи между точками и районами на языке SQL будет выглядеть вот так:

 

CREATE TABLE POINTS

( ID INT, X FLOAT, Y FLOAT, DISTRICT CHAR(5),

PRIMARY KEY (ID),

FOREIGN KEY (DISTRICT) REFERENCES DISTRICTS);

 

Это автоматически означает, что в колонке POINTS.DISTRICT, объявленной внешним ключом, могут быть значения только из колонки DISTRICTS.ID.

Разумеется, порядок создания таблиц важен – сначала создается таблица DISTRICTS, которая «ничего не знает» про POINTS (это логично, если вдуматься – административные районы сто лет стояли, а точки лишь потом понатыкали J). Поскольку при создании POINTS ссылается на DISTRICTS, то она создается во вторую очередь. Результатом применения двух вышеприведенных команд SQL будет пара связанных таблиц:

 

Рис. 12. Связь двух таблиц

 

Разрешается задать связь и потом, после создания и даже наполнения таблиц: возможны всякие варианты. Однако хорошим тоном считается задавать все связи сразу, хотя бы приблизительно. Тип связи («один-ко-многим» или «один-к-одному») не задается пользователем, он вычисляется системой, исходя из того, где первичный ключ, а где – внешний. В нашем случае в таблице POINTS внешний ключ, следовательно, здесь один и тот же код района может повторяться много раз, хоть в каждой строке, значит, это сторона связи = «много».  В таблице DISTRICTS код района ID – первичный ключ, в этой колонке никак не может быть одинаковых значений, это сторона связи «один».

Получается, однотипные ключевые атрибуты входят в разные таблицы, задавая связи или обозначая сравнение данных. В таком случае  важно, чтобы ключевые колонки были однотипны, определены на одном наборе, словом, сопоставимы не только как типы данных, но и по здравому смыслу. Разумно это отслеживать в БД через домены. В идеале, система должна допускать установление связи, только когда оба соединяемых атрибута принадлежат одному домену. Как говорят, «определены на одном домене».

Соединение для запроса может реализовываться временно, в момент проведения операции, как простое математическое соединение двух таблиц по каким-либо колонкам, подобно застежке-молнии. Это не структурная связь, она не требует ключей в общем случае. Вы можете связать две таблицы: замеров УГВ и отбора проб грунтовых вод, например, чтобы поглядеть, какие пробы отобраны ниже уровня. Или две таблицы разного опробования почв, чтобы увидеть, какие пробы совпадают по глубине.

 

SELECT ID, H, Ca, Mg, Clay

FROM SOIL_CHEM, SOIL_MEX

WHERE ID = НОМЕР AND H = Глубина;

Рис. 13. Временная связь двух таблиц в запросе, по условию

Заметим, что во второй таблице колонка Глубина не является ключевым – однако и в первой это не обязательно для запроса. Разные названия однотипных полей в таблицах сделаны для наглядности.

 

Требования однотипности для любых связей очевидны. Если такая связь, например, идет по численным полям (H, Глубина), то крайне важно, чтобы в соответствующем месте были зафиксированы еще и такие характеристики этих параметров, как единицы измерения и дискретность параметра. Понятно, что если глубина задается не дискретно, с точностью до 19-го знака, то в таблицах трудно будет сыскаться строго одинаковым значениям, и придется оперировать некоей «окрестностью». Стратегически важные параметры пространства-времени разумно дискретизировать заранее. Например, глубину для почвенных исследований всех видов задавать всегда целыми сантиметрами. Тогда сопоставление глубин образцов с горизонтами и друг с другом будет адекватным.

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

То же можно сказать и о датах. Задав дискретность отбора проб днями или часами во всей БД, получаем однородный домен времени, и можем сравнивать все даты адекватно (более точное значение времени может оставаться в архивной БД или той же таблице, но как «простой атрибут»).

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

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

Однако естественная связь, как правило, требует не простого, а составного ключа, из нескольких параметров.  Как быть на практике? Составной внешний ключ неудобен для работы, тормозит систему и усложняет схему. На практике его стараются избежать, например, в нужный момент перекодировать значения трех ключевых колонок одним кодовым номером. На практике это реализуется через механизмы автонумерации и индексов. Например, если скважины были сделаны разными субподрядчиками, приходится во многих таблицах иметь составной первичный ключ – номер скважины и код субподрядчика. Как правило, эта пара подменяется на некий автономер, а уникальность естественного ПК поддерживается дополнительным уникальным индексом. Такое преобразование разумно лишь в готовой БД, и нет смысла торопиться. Когда БД содержит много данных, медленно работает, и сложно делать оперативные запросы, тогда стоит озаботиться этим. Целесообразность в данном случае тоже уместна.

Целостность и каскадные операции

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

Целостность связей формирует структуру всей БД. В примере выше с внешним ключом видно, что таблица DISTRICTS «главнее», поскольку POINTS ссылается на нее. Очевидно, что если один из районов исчезает (например, упразднен), то значения в POINTS не могут продолжать ссылаться на несуществующее значение, требуются какие-то изменения. Это и есть принцип ссылочной целостности. Система в общем случае не даст удалить район, пока не решены ссылки в POINTS.  Ссылки должны быть либо удалены (что нонсенс – исследования ведь не исчезают), либо исправлены заранее на другой район (что вернее).

Можно пользоваться таким механизмом целостности, как «каскадные операции». Если механизм включен, то при удалении основной сроки удаляются связанные с ней. Однако это слишком мощное оружие. При удалении работника удаляется проект, который он вел и так далее, что в общем случае неверно, неестественно. Лучше избегать включения каскадности на удаление. Второй вариант – каскадное переименование, при изменении кода района в таблице DISTRICTS в таблице POINTS он тоже автоматически меняется. Это естественно, полезно и эту возможность включать разумно, хотя она, понятно, немного замедляет работу БД.

Целостность понятий не менее важна и для БД в целом, и для отдельных операций. Это естественная смысловая, или, как говорят, «семантическая целостность».  Она должна быть реализована и через первичные ключи, и через связи, и через домены, особенно через систему справочников-классификаторов,. Все эти элементы должны иметь естественный смысл, соответствовать естественным понятиям, реально существующим классификациям, методикам и нормативам.

Присоединяемая таблица жестко задает набор значений для основной, но что это за набор? Часто он называется гордым именем «справочник-классификатор», и должен соответствовать этому. Классификация должна быть составлена целостно, охватывать свою предметную область без пропусков: нельзя пропустить градацию «суглинок» между супесью и глиной. Также не должно быть наложения понятий, размытых границ между ними и т.п. Написания должны быть максимально корректными, ведь, по сути, они тиражируются по БД путем запросов и подстановок. Словом, над таблицами-справочниками, нужна тщательная  редакторская работа.

Пропущенные значения

Как мы знаем, пропущенные значения порождают усложненную логику. Так надо ли городить огород? Но как же еще отразить реальную таблицу, если одни объекты имеют некоторое свойство, а другие – нет? В жизни и даже в документации сплошь и рядом пробелы встречаются. Для одних проб содержание измерено, для других – нет. Или измерено, но не обнаружено… Стоп! Не измерено или не обнаружено? Если не обнаружено, то это означает просто ноль, 0, «Zero»! Если «ниже предела измерений», как часто пишут лаборатории, то это «почти ноль», «следы», третий вариант!  Рассмотрим пример.

 

 

РАЗРЕЗ

ГОРИЗОНТ

Ca

Mg

Гумус

Зольность

007

A0

1,8

1.3

-

78,5

008

T

6,3

4.2

-

42,4

008

B

1,7

1,6

1,0

-

008

C

12,9

8,6

0,6

-

009

A1

19,5

10,2

6,7

-

009

BC

15,3

6,3

1,5

-

010

A0

12,8

4,5

-

37,8

 

Рис. 14. Пропущенные значения – NULL values

 

Во всех почвенных образцах измерены содержания кальция, магния и других общих параметров. В то же время параметры «Гумус» и «Зольность» определены не для всех горизонтов, для некоторых приходится ставить «не известно», NULL. При внимательном рассмотрении мы увидим, что это не случайно – содержание гумуса не измерялось для дерна и торфяных почв, зольность – наоборот.  Ну что ж, возможно и так, тут надо быть экспертом, чтобы разобраться. Поэтому по умолчанию ставят NULL - безликое «не известно», «не измерено». Однако при правильно поставленной методике этого не должно быть и можно избежать пустот уже при построении структур. В примере выше можно было бы разнести параметры в разные бланки, а в БД – сделать разные таблицы, одна – для всех проб с  общими параметрами, другие – отдельно для «гумусных» и отдельно для «зольных» проб. Почему так не делают в общем случае? Для экономии бумаги и простоты структуры, чтобы не плодить на каждый подвид объектов отдельные таблицы. Получается, что пропущенные значения позволяют достичь компромисса между жесткими требованиями реляционной модели и многообразием предметной области. В жизни ведь каждый объект «слегка» отличается от другого… «Пропущенные значения», таким образом, позволяют слегка отступать от структуры, применяясь к реальности.

NULL, как мы знаем, обозначает «не известно». Можно запретить применение таких значений для какого-либо поля, если это нежелательно. Pеализуется это через свойства атрибута, или средствами SQL:

 

CREATE TABLE DISTRICTS

( ID CHAR(5), NAME CHAR(32)  NOT NULL,

PRIMARY KEY (ID) );

 

После такого указания система при добавлении строк не даст оставить клетки колонки NAME пустыми. Указания NOT NULL для первичного ключа не требуется, это само собой разумеется. При анализе данных применяется словосочетание «IS NULL». Как правило, такие операции нужны, чтобы предварительно подготовить, отфильтровать данные.

 

SELECT * FROM SOILS_CHEM

WHERE HUMUS IS NOT NULL;

 

Далее можно наводить статистику, уже зная точно, что все значения гумуса отобраны в отдельную виртуальную таблицу, в которой меньше строк, чем в исходной, и нет пробелов. Статистика будет правильной. Значения «не известно» однако, неестественные для предметной области, и их лучше избегать, как было показано выше. Однако в реальности нет стопроцентно однотипных объектов, они все чем-то слегка отличаются…

На практике редко встречается смысл «не известно», или даже «не измерено»: скорее «не свойственно», «Not Available», N/A. Именно это понятие, именно это обозначение многие реальные БД применяют, чтобы смазать, замаскировать неоднотипность, и без этого на практике никак. Усложненная логика от этого не исчезает, однако можно ее спрятать от пользователя.

Никакой реализации значений c естественным смыслом вроде N/A и СЛЕДЫ (TRACES) способов в реляционных БД нет. На практике эти вещи реализуются через подмену и другие ухищрения. Можно обозначать N/A наименее вероятным значением вроде 99999 или -65545. Удобнее всего, однако, постановить для своей базы данных, что NULL всегда обозначает N/A, а «не известно» имитировать каким-либо другим способом либо вообще избегать. Разумеется, в любом случае потребуется предобработка данных перед основным анализом, фильтрация или восстановление значений под каждую конкретную задачу.

Вместо СЛЕДОВ обычно подставляется половина предела измерения, чтобы меньше рисковать статистикой. Если речь идет о загрязнении, то подставляется максимально возможное значение. В подсчетах запасов для подстановки используется минимально возможное или даже статистически наиболее вероятное. Тогда предобработки можно избежать, ведь заранее запланирован минимальный ущерб для статистики. Удобно, когда способы обозначения пропущенных значений хранятся в доменах данных (как принадлежность типа), либо заводится особая логическая колонка на каждое значение.

Таким образом, пропущенные значения тоже должны иметь естественный смысл. Целесообразность работы с ними в том, что в начале можно ставить NULL, по мере уточнения смысла пропусков перевести их в категории N/A, TRACES и т.п. Не так редки случаи, когда после расследования по первоисточникам NULL заменяется на вульгарный ноль!

Нормализация

Не будем здесь углубляться в теорию нормализации, ее можно прочесть в пособиях и… скорее всего, забыть про неё. Лучше перечислим основные случаи и рецепты к ним. Вот характерные признаки того, что нормализация требуется:

 

 

РАЗРЕЗ

ДАТА

ГОРИЗОНТ

H

Ca

Mg

Гумус

007

17.06.99

A0

8

1,8

1.3

---

007

17.06.99

T

22

6,3

4.2

---

007

17.06.99

B

28

1,7

1,6

1,0

008

19.06.99

A0

5

12,9

8,6

---

008

19.06.99

A1

22

19,5

10,2

6,7

008

19.06.99

BC

37

15,3

6,3

1,5

010

21.06.99

A1

5

12,8

4,5

5,2

010

21.06.99

A1

25

14,6

8,5

1,4

 

Рис. 15. Нормализация

 

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

 

Описания данных и предметной области – форматы и методики проектирования

Язык SQL, как было показано выше на примерах, может служить не только для выбора данных, но и для описания таблиц, ключей, связей. В реализации SQL есть фразы для описания всех возможных компонентов структуры БД. Мало того, SQL является стандартом для всех реляционных СУБД, и мало отличается от системы к системе. В этом формате хранятся описания таблиц для их быстрого воссоздания в другой системе, клонирования поодиночке и целыми группами, поскольку это команды, которые могут быть моментально запущены на выполнение. Зачем же выдуманы другие варианты описания структуры? Начнем с того, что SQL не содержит естественных терминов, и неудобен для проектирования.

 

E-R диаграммы применяются для описания не только готовых таблиц, но и самой предметной области. Они воплощают подход «сущность-связь», и служат для поэтапного моделирования предметной области средствами реляционных БД. На диаграммах выделяются:

- Классы сущностей (Entity), например «Скважина», «Почвенный разрез», «Проба».

- Связи (Relationships). Для них нужны указания «один-много» (изображаются разными хвостиками) и «обязательно-необязательно» (показаны типом линии, сплошная/пунктирная). Эти характеристики, к слову, почти всегда совпадают друг с другом. Могут быть зафиксированы и пользовательские, смысловые описания связей: «Скважины содержат…» «Пробы отобраны в…».

- Основные атрибуты сущностей (Номер/индекс; Координаты, Глубина отбора);

Рис. 16. Сущности и связи в E-R диаграммах

 

Понятие уникальности расширено – уникальным идентификатором может служить набор атрибутов, но, кроме них, еще и связи. Так, для сущности «Проба» связь «отобраны в» служит идентификации вместе с глубиной отбора.

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

Недостатки: E-R диаграммы идут от бланков, от готовых сущностей,  и никак не облегчают процесс понимания сущностей, выделения объектов. Поэтому простые структуры моделируются легко, а сложные для понимания, не-предметные, промежуточные сущности выделяются с трудом. Например, таблица наблюдений и понятие «наблюдения» будут сформулированы только «потом», когда в разработке вдруг возникнет соответствующий бланк (или комплект данных).

 

 

Рис. 17. Сущности и связи – класс «Наблюдения»

 

После появления сущности «ТОЧКИ» становится очевидно, что к ним могут быть отнесены и скважины, и разрезы как частный случай. Тогда все координаты могут быть сведены в одну таблицу.

Рис. 18. Сущности и связи – нормализованный вариант

 

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

Получается, что ключевое для диаграмм понятие «сущность» в E-R методике не определяется и остается интуитивным – как сущность, может выступать что угодно - сложный объект, событие, даже связь объектов: словом, всё, что нужно по ситуации. Для разработки требуется масса вопросов, которые требуют однозначных ответов по неоднородным массивам данных. Схема данных требует последующей обкатки, нормализации, слияния. Обратный процесс – отладка по данным – как правило, происходит вручную, тогда как реальное проектирование с такой рабочей схемы обычно только начинается.

E-R методика использует понятие доменов, но как вспомогательное. На примере с точками и наблюдениями ясно, что домен координат стоило бы выделить заранее, до проектирования конкретных сущностей и даже БД. Модель объекта есть по сути набор признаков, так может с них и начать? Тогда ситуации, описанные выше, в проектировании случались бы реже.

 

Диаграммы UMLUnified Modelling Languageкак и ER, это графический, диаграммный язык. Он используется для объектно-ориентированного моделирования предметной области, и рассчитан на широкий круг разработчиков не только баз данных, а информационных систем вообще. Считается, что можно на нем с успехом проектировать сложные системы, не зная языков программирования, и даже не имея программных средств. Он заметно сложнее ER, и здесь я приведу лишь основные его аспекты.

Основной элемент схемы  - класс объектов. Кроме этого, в схеме могут быть связи, ограничения и, разумеется, всякие комментарии.

Классы объектов примерно соответствуют сущностям в ER и таблицам в реляционных БД. Графически класс выражается в виде именованного прямоугольника. В него входят однотипные объекты, но они не являются полными аналогами кортежей, а намного сложнее. Для каждого объекта определены общие свойства (аналоги атрибутов), и общие операции над объектами. Свойство класса обычно снабжено заранее пользовательским типом данных (прямой аналог доменов).

Рисунок 19. Объект UML – точка наблюдения

 

Уже из этого рисунка видно, что объект может быть намного сложнее, чем просто строка с данными, и не может быть однозначно отражен в БД. Так, например, в рамках БД вполне возможно представить преобразование локальных координат в мировые, решение задач соседства уже сложнее, ну а вынесение на карту явно потребует средств ГИС. Тем не менее основная, структурно важная часть данных вполне укладывается в рамки реляционных понятий.

Связи в UML намного сложнее, чем в E-R модели. Связь-обобщение означает, что один объект происходит от другого. Например, скважина может рассматриваться, как разновидность точки наблюдения. Как и в точке, имеются координаты, применимы все операции, но плюс к тому – есть свои особые свойства – глубина, данные по глубине. Другой вариант – почвенный разрез. Точка может рассматриваться как обобщение разрезов, скважин, гидрологических постов, геодезических пикетов и т.п.

Рисунок 20. Связи UML: обобщение, ассоциация

 

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

 

Наиболее тесные ассоциации называются агрегатами, когда один объект составляет часть другого. Есть еще и обязательные агрегаты – композиты, другие варианты и подварианты, особые свойства и случаи. Система UML-логики постоянно совершенствуется, развивается.

 

Рисунок 21. Агрегатные связи UML

 

Это лишь основные примитивы, но и они позволяют моделировать предметную область достаточно интеллектуально. Как и в ER, все они имеют четкий математический аппарат – но не всегда стопроцентный. Некоторые сочетания объектов и связей вызывают конфликты, и тогда требуется участие разработчика. В отличие от ER, есть еще и текстовой язык для описания сложных свойств, процедур, ограничений. Словом, можно утверждать, что в UML всегда можно спроектировать БД такой же сложности, как и в ER, но не наоборот.

Недостатки: UML, как можно видеть избыточно широк, и на этапах реализации конкретных БД это сказывается – изыски приходится отсекать, конфликты разрешать простыми известными средствами. Скупые средства ER заранее формализуют предметную область  точно под требования реляционной БД, тогда как UML провоцирует изначально на более сложные решения, как это видно на примере свойств класса точек наблюдения (рис.19).

Программные средства для превращения UML-схем в реальные структуры РБД (и программные объекты для других систем) также имеются. Почти все БД могут использовать UML-схемы, тем или иным образом транслируя их в SQL-конструкции. Однако в ГИС сам UML не применяется, и нужно сначала конвертировать построения в переходные XML файлы.

 

XML представляет совсем другой формат, предназначенный не для проектирования, а для описания. Это особым образом структурированные тексты, которые в принципе могут содержать описание всего на свете. Можно сказать, что это даже не язык, не набор конкретных слов, а способ выражения, категория, подобная речи, или флажковому переговору. Как конкретных речевых языков может быть много, так много и диалектов XML – все зависит от набора конкретных слов или сочетаний флажков. Порожден XML в эпоху Интернета как более строгая разновидность языка разметки веб-страниц. Но если обычный веб-документ - просто последовательный текст с форматированием, то XML – иерархический документ. Если в обычном документе небольшое количество общеизвестных команд форматирования, то в XML их может быть бесконечное множество. Наиболее общие команды описаны в стандартах языка, остальные - в отраслевых или корпоративных спецификациях (которых тоже бесконечно много). Некоторые «флажки» известны лишь отдельным организациям, фирмам, и даже пользователям.  На XML нынче все обмениваются сведениями, начиная от финансовой документации и кончая гугловскими геоданными (KML, KMZ).  Разумеется, схемы БД в нем описать можно. Рассмотрим схемы простой БД ТОЧКИ-РАЙОНЫ (POINTS-DISTRICTS):

Диалект MS Office Data

 

Диалект ArcGIS (ESRI)

 

Рис. 22. XML: диалекты Microsoft Office Data и ESRI

 

Как видно, XML содержит и комментарии, и ссылки, и многое другое про запас, хотя в БД всего шесть полей и ни одного значения. В принципе XML может содержать и сами данные, и программные процедуры, и комментарии, и целиком чертежи с фотографиями. Служит он чаще всего именно для передачи данных, а не типовых структур (для простых структур и без того хватает средств). Зато сложные структуры с легкостью уминаются в XML - например, пространственные данные ESRI с их сложными топологическими взаимоотношениями. В итоге можно сказать, что и SQL-структуры, и E-R диаграммы, и UML классы со связями вполне можно содержать в XML, что cплошь и рядом делается. Чего XML не может содержать, так это горячий кофе с булочкой  - но по XML-описанию его с легкостью можно изготовить J

Недостатки: отсутствие общего стандарта, или хотя бы каких-либо устоев не дают возможности широко применять какой-либо один диалект XML для обмена. В MS Office это Майкрософт стандарт, у ESRI свой гео-XML, у Гугла свой формат для гео-данных.

Считается, что у каждой уважающей себя корпорации должен быть свой диалект XML. Мало того, между ними мало общего, мало общих терминов и слов, и они друг друга «не понимают». Таблицы геобазы данных ESRI не загрузятся в MS Access, и наоборот. Шейп-файлы, описанные в XML, никак не будут поняты Гуглом. По сути, это автономные форматы, и кто в какой области станет стандартом, кто будет принят – совершенно не ясно. Пока идет настоящая гонка XML, и чем закончится этот вавилон, не понятно.

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

Кроме того, схемы для всех БД являются лишь надстройками, не интегрируются вовнутрь БД, по сути, перестают работать в момент создания первого же варианта структуры – становятся мертвой документацией.  Между тем есть другие подходы, как ни странно, более древние (даже ортодоксальные), избегающие этих недостатков.

Мета-табличный подход к описанию структур и проектированию

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

Рассмотрим её подробнее на примере УГВ. Разумнее начать работу со списка атрибутов (параметров), по методике исследований он всегда имеется.

 

Name

DataType

Size

Unit

Номер (индекс) скважины

CHAR

20

 

Координата X (плановая, восток)

FLOAT

 

 

Координата Y (плановая, север)

FLOAT

 

 

Координата Z (высота абсолютная)

FLOAT

 

 

Диаметр бурения

INTEGER

 

мм

Дата бурения

DATETIME

 

 

Глубина скважины

FLOAT

 

м

Диаметр обсадки

INTEGER

 

мм

Глубина верхней кромки фильтра

FLOAT

 

м

Глубина нижней кромки фильтра

FLOAT

 

м

Глубина появления воды при бурении

FLOAT

 

м

Наличие водозабора

LOGICAL

 

 

Дата измерения уровня грунтовых вод

DATETIME

 

 

Глубина залегания уровня грунтовых вод

FLOAT

 

м

 

Рис. 23. Список атрибутов предварительный

Приведены атрибуты лишь скважин и УГВ

 

Список может начинаться, как несортированный перечень в одну колонку, к тому же импортом из методической документации. Кодовые имена, типы данных – все можно совершенствовать по мере уточнения. В какой таблице они будут, тоже можно решить потом – указание в первой колонке можно ставить, можно менять. Одно лишь важно - с самого начала давать однотипные осмысленные названия, например «Глубина такая-то…», «Глубина того-то…». Тогда при сортировке одинаковые атрибуты окажутся рядом, и потом можно будет их унифицировать. Например, объединить в домены по сходству названия или типа данных. В этом поможет сортировка имен, выборка, словом, те же средства БД, что и для данных.

Любое описание данных называется мета-данными, сверх-данными. Тем более этот термин подходит для табличного воплощения мета-данных. Описательные таблицы, чтоб не путать с основными,  называются мета-таблицами, атрибуты мета-таблиц – мета-атрибутами и т.д.

 

DNAME

Name

DataType

Size

Unit

IDS

Номер (индекс) скважины

CHAR

20

 

XY

Координаты плановые

FLOAT

 

 

Z

Координата Z (высота абсолютная)

FLOAT

 

м

Date_

Дата

DATETIME

 

 

D

Диаметр

INTEGER

 

мм

H

Глубина по скважине

FLOAT

 

м

Wtaken

Наличие водозабора

LOGICAL

 

 

 

Рис. 24. Список доменов – Мета-таблица DOMAINS

Приведены домены, полученные обобщением атрибутов предварительного списка (рис. 23).

 

Как видно, в доменах можно фиксировать и единицу измерения, и многое другое - количество описательных колонок не ограничено. После формирования доменов появляется возможность упорядочить список атрибутов. Затем создаем примерный список таблиц.

 

TNAME

Name

BOREHOLES

Каталог скважин

GW_LEVELS

Уровни грунтовых вод

GW_CHEM

Химия подземных вод

BH_LITHO

Литологические горизонты

Geology

Геология горизонта (справочник)

Lithology

Литология пород (справочник)

 

Рис. 25. Список таблиц – мета-таблица TABLES

 

Теперь можно «раскидать» атрибуты по таблицам. Процесс неоднозначный, и требует повторов, итераций, изучения первичного материала. После того, как все основные мета-списки сформированы, следует выбрать первичные атрибуты и пометить их галочкой. Это, как правило, требует тестирования данных на предмет дубликатов. Далее можно наметить связи между будущими таблицами. Для получения простых, одинарных связей достаточно пометить атрибут внешний ключ ссылкой на таблицу-справочник (мета-колонка ForeignKey).

 

TNAME

ANAME

PrimaryKey

Domain

ForeignKey

BOREHOLES

IDS

ü

IDS

 

BOREHOLES

X

 

XY

 

BOREHOLES

Y

 

XY

 

GW_LEVELS

IDS

ü

IDS

BOREHOLES

GW_LEVELS

Date_M

ü

Date_

 

GW_LEVELS

GW_Level

 

H

 

GW_CHEM

IDS

ü

IDS

BOREHOLES

GW_CHEM

Date_S

ü

Date_

 

GW_CHEM

Depth_S

ü

H

 

GW_CHEM

Date_An

 

Date_

 

GW_CHEM

Pe

 

Pe

 

GW_CHEM

Ca

 

CaMg

 

GW_CHEM

Mg

 

CaMg

 

 

 

 

Рис. 26. Мета-таблица ATTRIBUTES с указанием связей

Показаны лишь основные атрибуты трех таблиц

 

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

 

 

Рис. 27. Схема метаданных

 

После проверок целостности этой мета-БД можно по ней создать рабочую базу данных по скважинам – существуют простые сценарии генерации схем по таким мета-таблицам. Если точнее, относительно просто создаются соответствующие SQL-скрипты «CREATE TABLE и т.п. Если вдруг понадобятся составные связи, то из-за них  придется делать отдельную таблицу RELATIONSHIPS.

Подобные мета-таблицы как правило, УЖЕ имеются в каждой СУБД, причем набор их примерно одинаков – в любой СУБД есть скрытые системные sysTABLES, sysCOLUMNS, sysCONSTRAINTS, sysINDEXES. Однако к ним нет прямого доступа, они не имеют описательных колонок, поэтому рекомендуется все же делать свои версии.

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

Разработчики знают эту методику и применяют её, но не как основную, потому что объемы современных БД солидные, сроки разработки сжатые и, как правило, используются специальные средства проектирования, особенно визуальные. Табличные Метаданные не унифицированы производителями, и только отдельные статьи пытаются эти форматы урегулировать,  Визуальные средства для метатабличного подхода представить несложно, однако намного важнее стандартизовать описания. Возможности моделирования этим способом довольно ограничены, но ведь и сама реляционная модель имеет определенные рамки.

Недостатки реляционной модели

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

Заключение

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

Материал впервые был изложен на семинаре по ГИС Учебного центра «Изыскатель» в ноябре 2007 г. C замечаниями и предложениями обращайтесь по адресу geologic@mail.ru, с любыми вопросами – на форум http://www.geofaq.ru/forum

 

Литература, источники информации

  1. Основы современных баз данных. С.Д. Кузнецов. www.citforum.ru
  2. Введение в системы управления базами данных. Пушников А.Ю. www.citforum.ru
  3. Понимание SQL (Understanding SQL). Мартин Грабер. www.sql.ru
  4. Базы Данных: реляционные особенности и их практический смысл. А. Левин. www.geoFAQ.ru