Базы данных

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

Реляционной, то есть основанной на «отношениях», базу данных можно назвать по двум причинам:

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

Предварительное проектирование обязательно нужно в следующих ситуациях:

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

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

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

В простой ER-диаграмме названия атрибутов пишутся в столбик под именем сущности. Имена характеристикам стоит давать простые, короткие и однозначные. Не надо уточнять, про какой объект идет речь, вроде «Адрес участка» для сущности «Участки», ведь и так ясно, что это свойство участка.

Позже, в рабочей таблице атрибуты превратятся в ее колонки.

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

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

Здесь стоит заметить, что «один-к-одному» это частный случай «один-ко-многим» и встречается очень редко.

В простой ER-диаграмме связи показываются линиями от одной сущности до другой или, если более точно, от внешнего ключа в одной таблицы до соответствующего первичного ключа в другой. На каждом конце линии указывают соответствующую степень — один или много. В качестве обозначения «много» рекомендуется писать «n», но в примерах можно встретить «*», «∞» или трезубец.

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

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

Например, у нас есть Здания и Помещения. Зададим вопросы:

1. «С одним Зданием сколько Помещений связано? Много!»
   На конце связи, подходящей к Помещениям отмечаем «n».
2. «С одним Помещением сколько Зданий связано? Одно!» 
   На конце связи, подходящей к Зданиям отмечаем «1».

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

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

1. расставить первичные и внешние ключи;
2. избавиться от типичных ошибок проектирования путем нормализации.

Можно сказать, что это уникальный идентификатор каждой строки в таблице. В большинстве случаев можно просто в каждую таблицу добавить (обычно первым) атрибут с общепринятым именем «ID» и типом int — целочисленный. Это будет просто номер строки в порядке добавления.

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

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

Именовать внешние ключи лучше так, как называется другая соответствующая (внешняя) таблица, но в единственном числе, например, «здание». Нет нужды писать что-то вроде «ID здания», так как это несколько визуально «захламляет» структуру базы данных. Keep it simple.

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

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

• если связь «один-ко-многим»:
  1. в таблице, где «один» создайте первичный ключ, если его нет;
     
  2. в таблице, где «много» создайте внешний ключ и назовите его так, как называется другая таблица в единственном числе;
     
• если «многие-ко-многим»:
  1. в обоих таблицах создайте первичные ключи, если их нет;
     
  2. создайте промежуточную таблицу со своим первичным и двумя внешними ключами, соответствующими первичным ключам связанных таблиц. Назовите внешние ключи так, как называются другие соответствующие таблицы в единственном числе;
  3. при необходимости в промежуточную таблицу можно добавить и другие атрибуты, которые описывают эту связь. В примере это поле right, в котором содержится описание права персоны на недвижимость.

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

По мере выполнения проверок считается, что база данных находится в той или иной нормальной форме:

1. первая форма:
   • в одной ячейке нет нескольких внешних ключей;
   • расположение строк не имеет значения;
   • расположение колонок не имеет значения;
   • определен первичный ключ;
2. вторая форма:
   • нет атрибута, который бы являлся на самом деле характеристикой другой сущности, на которую стоит ссылка в составном первичном ключе, например:
     [Номер здания] [Номер помещения] Назначение помещения Назначение здания
3. третья форма:
   1. нет атрибута, который бы являлся на самом деле характеристикой другой сущности, например:
      [Номер помещения] Назначение помещения Площадь Минимальная площадь

Есть и другие нормальные формы, но рассматриваемые в них случаи весьма нетривиальны. Первых трех достаточно в большинстве случаев.

Проекция — это операция в реляционной алгебре, при которой из таблицы выбираются нужные столбцы, например:

• Выбор одного столбца из таблицы
  SELECT area FROM rooms;

• Выбор нескольких произвольных столбцов в одной таблице
  SELECT id, area FROM rooms;

• Выбор всех столбцов в одной таблице
  SELECT * FROM rooms;

• Создание псевдонимов столбцов
  SELECT area AS Площадь FROM rooms;

• Удаление дубликатов строк (без удаления данных)
  SELECT DISTINCT area FROM rooms;

• Выполнение сортировки по одному произвольному столбцу
  SELECT area FROM rooms ORDER BY area ASC/DESC;

• Сортировка по нескольким столбцам
  SELECT area FROM rooms ORDER BY building ASC/DESC, area ASC/DESC; 

Выборка — это операция в реляционной алгебре, при которой из таблицы выбираются нужные строки, например:

• Фильтрация строк с помощью произвольного сравнения

  SELECT * FROM rooms WHERE area > 100;

• Комбинирование условий с помощью логических операторов AND, OR и NOT
  SELECT * FROM rooms WHERE area > 100 AND building = 1; 
  

• Фильтрация строк по произвольному списку

  SELECT * FROM rooms WHERE id IN (1, 4, 35);

• Проверка на значение null с помощью оператора IS NULL / IS NOT NULL

  SELECT * FROM rooms WHERE area IS NULL;

В запросе можно для каждой строки выполнять как простые, так и достаточно сложные математические операции. Принцип составления формул похож на применяемый в MS Excel, например:

• Просто калькулятор
  SELECT 2+2;
• Расчет с выводом результата в отдельном столбце
  SELECT area*100 AS Стоимость FROM rooms;
• Расчет в условии
  SELECT * FROM rooms WHERE area*100 > 10000;

Для расчетов по столбцам используются агрегатные функции, например:

• Подсчет строк
  SELECT COUNT(*) FROM rooms WHERE area > 100;

• Обобщенное значение
  SELECT MIN/MAX/AVG(area) FROM rooms;

• Группирование строк

  SELECT floor, COUNT(*) FROM rooms GROUP BY floor;

• Фильтрация групп

  SELECT floor, COUNT(*) FROM rooms GROUP BY floor HAVING COUNT(*) > 3;

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

Итак, для правильного, то есть отвечающего на поставленный вопрос соединения необходимо прописать в запросе:

1. существующие в виде первичных и внешних ключей связи в виде выражения:
   FROM tableA LEFT/RIGHT/INNER JOIN tableB ON foreign key = primary key;
2. условия выборки нужных строк после WHERE (если надо).

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

• Помещения площадью более 100 и адреса зданий, в которых они расположены
  SELECT r.id, b.address
  FROM rooms AS r INNER JOIN buildings AS b ON r.building = b.id
  WHERE r.area > 100;

Есть несколько видов соединений, например для предыдущего примера:

• внутреннее (INNER JOIN), если нужно отобрать только такие помещения, отвечающие условию выборки, у которых в атрибуте r.building указаны идентификаторы существующих в таблице buildings зданий. Если нужного здания нет, то строки с этим помещением в результате запроса просто не будет;
• внешнее (LEFT JOIN или LEFT OUTER JOIN), если нужно показать все помещения, отвечающие условию выборки, независимо от того, есть ли в таблице buildings соответствующие здания или нет. Для помещений с отсутствующими в базе данных зданиями в поле b.address отобразится NULL.

Есть и реже используемый RIGHT JOIN, при котором в первую очередь отберутся все здания независимо от наличия помещений. Однако, в рассматриваемом примере у нас нет зданий без помещений.

Добавляя нужные условия, мы можем составлять самые разнообразные запросы, например:

• Помещения, для которых нет соответствующих зданий (так называемое вычитание)
  SELECT r.id
  FROM rooms AS r LEFT JOIN buildings AS b ON r.building = b.id
  WHERE b.id IS NULL;

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

Последние события:

• 06.2022 — ФГБУ «Центр геодезии, картографии и ИПД» объявил конкурс на 39,75 млн руб. на предоставление права использования программной платформы онтологического проектирования (в целях разработки НСПД);
• 12.2022 — ФГБУ «Центр геодезии, картографии и ИПД» выполнил первый этап работ по созданию отраслевой онтологии.

Заметим, что под пространственным объектом понимается как сам объект, так и соответствющая ему цифровая модель.

В соответствии с Приказом Минэкономразвития России от 29.03.2017 №142 пространственные метаданные формируются в электронном виде в виде файлов в формате XML и содержат информацию:

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

Стандартом SFA OGS предусмотрены типы объектов:

• Geometry (корневой абстрактный класс):
  • Point (размерность 0, одна позиция в пространстве);
  • Curve (размерность 1, последовательность Point):
    • LineString (Curve с линейной интерполяцией между точками. Каждая последовательная пара точек определяет объект Line):
      • Line;
      • LineRing;
  • Surface (размерность 2);
    • Polygon (плоская поверхность, задаваемая при помощи одной внешней границы и 0 или более внутренних);
    • PolyhedralSurface (многогранная поверхность является набором граничащих по внешней границе полигонов);
      
  • GeomCollection (объект, содержащий несколько других объектов):
    • MultiCurve (сейчас не используется):
      • MultiLineString;
    • MultiSurface:
      
      • MultiPolygon;
    • MultiPoint (состоит из Point, которые не соединены между собой и порядок их следования не важен).

Согласно Приказу Минэкономразвития России от 28.02.2023 №123 в документах территориального планирования объектов федерального, регионального и местного значения при описании пространственных данных могут применяться следующие типы геометрий:

• точка (Point);
• линия (Line);
• кривая (Curve);
• полигон (Polygon);
• мультиточка (Multipoint);
• мультикривая (MultiCurve);
• мультиполигон (MultiPolygon).

Для хранения данные из текстового вида часто преобразуются в двоичное представление в шестнадцатеричных строках в формате WKB (Well-known binary), например:

• WKT: POINT(2.0 4.0);
• WKB: 000000000140000000000000004010000000000000, где:
  • 1-byte integer 00 или 0: прямой порядок байт;
  • 4-byte integer 00000001 или 1: POINT (2D);
  • 8-byte float 4000000000000000 или 2.0: x-координата;
  • 8-byte float 4010000000000000 или 4.0: y-координата.

Существуют расширенные версии формата:

• EWKT (Extended) используется в PostGIS и включает до 4-х значений координат (XYZM);

• AGF используется в продуктах Autodesk и включает кривые.

В MySQL описание геометрии в формате WKT преобразуется для использование в запросах в сооветствующее выражение на SQL с помощью специальных функций, так:

Point(15, 20) — это то же самое, что ST_GeomFromText('POINT(15 20)')

Каждый объект обладает свойствами:

• тип;
• идентификатор пространственной привязки SRID:
  • 0 - бесконечная плоскость;
• координаты;
• минимальный ограничивающий прямоугольник MBR ((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY));
• является ли пустой (нет точек, площадь 0);
• размерность:

  • -1 для пустой геометрии;

  • 0 для геометрии без длины и без области;

  • 1 для геометрии с ненулевой длиной и нулевой площадью;

  • 2 для геометрии с ненулевой площадью;

• внутренняя область, границы и внешняя область.

Требования к геометрии для корректной работы функций:

• линия имеет как минимум две точки;
• полигоны имеют как минимум одно кольцо;
• кольца многоугольника замкнуты (первая и последняя точки совпадают);
• кольца многоугольника имеют как минимум 4 точки;
• коллекции не пусты (кроме GeometryCollection);

• полигоны не являются самопересекающимися;

• внутренние кольца многоугольника находятся внутри внешнего кольца;

• мультиполигоны не имеют перекрывающихся полигонов.
  

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

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

Полученные пространственные отношения классифицируются при помощи полученной матрицы пересечений размерности 3 на 3, которую именуют DE-9IM.

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

Для более удобной работы предусмотрены именованные случаи матрицы в виде операций (методов) определения отношений между объектами: equals, intersects и другие.

О пересечениях в 3D см. работу Zlatanova S. (2015) Topological Relationships and Their Use. In: Shekhar S., Xiong H., Zhou X. (eds) Encyclopedia of GIS. Springer, Cham. https://doi.org/10.1007/978-3-319-23519-6_1548-1

В Бразилии исследователи предложили включать координаты центроида в кадастровый номер.

Примеры SQL запросов для СУБД MySQL:

• INSERT INTO t1 (pt_col) VALUES(Point(1,2));
• INSERT INTO geom VALUES (ST_GeomFromText('POINT(1 1)'));
• SELECT ST_AsText(g) FROM geom;
• SELECT ST_Contains(polygon, point) FROM geom;
• SELECT ST_Area(poly) FROM `buildings`;

Для запросов следует создавать индексы типа SPATIAL.