Основы баз данных с OpenEdge

Введение

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

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

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

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

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

Итак, наша задача – сформировать у читателя понимание основных принципов реляционных высокопроизводительных баз данных на примере СУБД OpenEdge.

Характеристики базы данных

База данных – это структурированный организованный набор данных, который обеспечивает:

Централизованное хранения данных – ввод и хранение всех данных выполняется на компьютере. Это минимизирует использование бумаги, файлов, папок, а также уменьшает вероятность их потери. К данным, находящимся на компьютере, могут получить множество пользователей используя сеть, при этом физическое или географическое их месторасположение не имеет значения.
Актуальность данных – внесённые пользователями изменения мгновенно становятся доступны другим пользователям.
Скорость и производительность – вы можете искать, сортировать, извлекать, вносить изменения и печатать данные, а также выполнять различные расчёты, значительно быстрее, чем если бы это делалось вручную.
Точность и согласованность – вы можете обеспечивать проверку вводимых данных на корректность, тем самым обеспечивая непротиворечивость и правильность данных. Например, пользователь не сможет удалить карточку клиента, если у этого клиента имеются заказы.
Анализ – базы данных могут хранить, отслеживать и обрабатывать огромные объёмы данных из разнообразных источников.
Безопасность – для обеспечения безопасности данных, вы можете определять списки авторизованных пользователей, у которых доступ к данным будет защищён с помощью пароля. Например, вы можете ограничить пользователя в выполнении определённых действий с данными.
Восстановление данных – системные сбои неизбежны. Целостность данных в результате таких сбоев гарантируются базой данных. Системы управления базами данных (СУБД) используют транзакционный журнал для того, чтобы проверять правильность восстановления данных после аварии.
Транзакции – концепция транзакции, это обеспечение восстановления после различных ошибок. Транзакция гарантирует, что набор изменений связанных с базой данных всегда будет выполняться по принципу «всё или ни чего». Это позволяет восстановить базу данных в предыдущее состояние, если сбой произошёл уж после того, как изменения стали вноситься, или если вы просто решили отменить сделанные изменения. Чтобы удовлетворять определению транзакции, СУБД должна придерживаться следующих четырёх свойств:
- Атомарность (Atomicity) – транзакция должна быть полностью завершена или полностью отменена. Не может быть частичных транзакций;
- Последовательность (Consistency) – транзакция должна преобразовывать базу данных из одного непротиворечивого состоянии в другое.
- Изолированность (Isolation) – каждая транзакция должна выполняться независимо от любой другой транзакции.
- Долговечность (Durability) – постоянность завершённых транзакций, иными словами изменения, внесённые завершённой транзакцией, не могут быть отменены.

Использование первых символов английского варианта этих свойств, при удовлетворении всех свойств, характеризует транзакции вашей базы данных как ACID устойчивые.

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

Компоненты баз данных

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

Таблицы

Таблица – это логически связанная информация, рассматриваемая как единое целое и состоящая из столбцов и строк.

Пример таблицы Customer из базы данных Sports

Обычно таблицы делятся на три типа:

Основные – таблицы, являющиеся независимыми сущностями. Они часто обозначают или моделируют объекты, существующие в реальном мире. Например: клиенты, продавцы, сотрудники, товары и прочее.
Ассоциативные – таблицы которые представляют связи между объектами. Например: заказ связан с клиентом и товарами.
Таблицы характеристик – их цель состоит в том, чтобы квалифицировать и описывать некоторые другие объекты. Эти таблицы сами по себе не имеют никакого значения, они используются только относительно описываемого объекта. Например: таблица Order-Lines могла бы описывать таблицу Order, без таблицы Order таблица Order-Lines будет бесполезна.

Таблицы состоят из строк (Row) или записей (Record). В строке содержатся данные, а каждая строка рассматривается как отдельный модуль. В таблице Customer, представленной выше, содержится четыре строки, каждая из которых содержит информацию о конкретном клиенте. Также и каждая строка таблицы Order содержит информацию о размещённых клиентом заказах.

Строки состоят из наборов столбцов (Column) или полей (Field). Все строки в таблице состоят из одинаковых столбцов. В таблице Customer имеется три столбца: Cust Number, Name и Street.

Ключи

Существует два типа ключей (Key): Primary и Foreign.

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

Обязательность (Mandatory). Столбец не может содержать нулевые или пустые значения. Если столбец будет оставлен пустым, то есть вероятность возникновения дублирования записей.
Уникальность (Unique). Например, таблица Student должна содержать уникальный идентификатор для каждого студента. Столбец Cust Number в таблице Customer – это уникальный идентификатор каждого клиента поскольку более практично использовать именно это поле, чем поле Name, так как разные клиенты могут иметь одинаковые имена. Кроме того, иногда люди могут менять имя или фамилию, например, после вступления в брак.
Стабильность (Stable) – низкая вероятность изменения значения поля. Например, как в предыдущем примере, Cust Number это хороший ключ, так как он может принимать значение равное социальному индивидуальному коду человека, которое не только является уникальным, но и вряд ли когда-либо изменится, в то время как имя клиента может поменяться.
Краткость (Short) – ключ не должен содержать много символов. Маленькие столбцы занимают меньше места, по ним быстрее выполняется поиск, и они менее склонны к ошибкам. Например, столбец Cust Number состоит из 9 цифр. Обратиться к нему намного легче, чем обратиться к полю Name, которое содержит имя из 30 символов.

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

Ещё существуют ключи называемые составными (Composite Key) – это ключи, которые состоят из более чем одного столбца.

Индексы

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

Правильно спроектированные индексы обеспечивают:

Эффективный быстрый поиск.
В ABL(4GL)-приложениях, записи упорядочиваются автоматически согласно указанного индекса, т.е. независимо от того, каким образом выполняется изменение таблицы, при её просмотре или печати, строки будут отображаться в индексированном порядке, вместо их реального порядка сохранения на диске.
Когда индекс определяется как уникальный, то каждая строка будет уникальной. Это гарантирует отсутствие дублирования информации.
Вы можете создавать индекс из нескольких столбцов, что позволит сразу сортировать записи в таблице в разном порядке.
Эффективный доступ к данным в таблицах со множеством связей.

Применение принципов реляционной модели

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

Такая модель может состоять из следующих таблиц:

Таблица клиентов (Customer) – состоит из четырёх строк, по одной на каждого клиента. Каждая строка состоит из двух столбцов: Cust Number и Name. Для того чтобы однозначно идентифицировать каждого клиента, клиент должен иметь уникальный номер. Каждый столбец содержит единственное значение. Первичный ключ таблицы строится по полю Cust Number.
Таблица заказов (Order) –содержит пять строк с информацией о размещённых клиентами заказах. Каждая строка состоит из двух столбцов: Cust Number из таблицы Customer и Order Num. Primary Key – это поле Order Num. Здесь столбец Cust Number – это Foreign Key, который связывает две таблицы. Такая связь позволяет находить все заказы, размещённые конкретными клиентами, а также информацию по клиенту для конкретного заказа.
Таблица Order-Line – содержит семь строк с описанием элементов каждого заказа. Каждая строка состоит из трёх столбцов: Order-Line-Num, Item Num из таблицы Item, Order Num из таблицы Order. Primary Key – это комбинация из столбцов Order Num и Order-Line-Num. Здесь есть два Foreign Key, Order Num и Item Num, которые связывают таблицы Customer, Order и Эти связи позволят выполнять поиск такой информации как: все строки заказа в заказе; информацию о заказе по конкретному элементу заказа; информацию о каждом элементе заказа.
Таблица элементов/предметов заказа (Item) – содержит четыре строки для каждого отдельного элемента. Каждая строка состоит из двух столбцов: Item Num и Description. Каждый элемент заказа имеет уникальный номер. Поле Item Num это Primary Key.

Пример описанных таблиц с данными в графическом виде:

Предположим, что мы хотим найти всех клиентов, заказавших Ski Boots (лыжные ботинки). Чтобы получить эти данные мы должны узнать, какой номер имеют эти ботинки, и кто их заказывал. Поскольку не существует прямой связи между таблицами Item и Customer, нам придётся подключиться ко всем четырём таблицам используя их связи Primary – Foreign Key. Для это мы должны:

Выбрать строку в таблице Item, у которой столбец Description соответствует значению Ski Boots. Значение в столбце Item Num в нашем случае будет I1.
Теперь необходимо выбрать заказы из таблицы Order, которые содержат элемент Item со значением I1. Поскольку таблица Order не содержит информацию об элементах заказа (Item), то нам сначала нужно сделать выборку из таблицы Order-Lines, содержащую I1, и определить связь Order с Order-Lines. В нашем случае, заказ O1 и O4 содержит Item Num = I1.
Теперь мы знаем номера заказов (Order Num) и можем найти клиентов, разместивших эти заказы. Выбираем заказы O1 и O4 и определяем связанные с ними номера клиентов (Cust Num). Это C1 и C3.
В завершение определяем имена клиентов C1 и C3, выполнив выборку строк из таблицы Customer, содержащих в поле Cust Num значения C1 и C2. Результат: лыжные ботинки купили Don Smith и Jim Cain.

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

База данных OpenEdge и реляционная модель

СУБД OpenEdge – это система управления реляционной базой данных (Relational Database Management System, RDBMS), которая позволяет добавлять, изменять, управлять или удалять данные и их структуры в базе данных в зависимости от ваших требований.

Схема и мета-схема базы данных

Логическая структура базы данных OpenEdge состоит из элементов реляционной модели, о которых вы только что прочитали, т.е. из таблиц, столбцов и индексов. Описание структуры базы данных, таблиц и столбцов в пределах таблиц, называется схема базы данных (Database Schema) или описания данных (Data Definitions).

Основная структура базы данных, которая даёт возможность хранить и искать данные, называется мета-схемой базы данных (Database Metaschema). Иными словами, мета-схемой называется описание структуры объектов базы данных. Объектами мета-схемы являются классы. Каждый класс описывает совокупность объектов, обладающих сходными характеристиками и одинаковым набором реквизитов. Все таблицы мета-схемы базы данных OpenEdge начинаются на символ подчёркивания (_).

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

О физической структуре базы данных и её связях с логической структурой вы можете прочитать в моей книге «Основы администрирования СУБД OpenEdge».

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

Основные моменты, которые нужно помнить

База данных – это электронная файловая система, предназначенная для организации и хранения данных.
База данных состоит из таблиц. Таблица, это совокупность строк относительно определённого объекта, например клиента.
Строка – это совокупность частей информации относительно одного объекта, например, информация о конкретном клиенте.
Столбец – это конкретная часть информации об объекте, например, имя клиента.
Индекс – это набор указателей на строку, которые вы используете для поиска или сортировки информации, например, индекс по номеру клиента.
Первичный ключ (Primary Key) – это столбец или набор столбцов, по которому можно однозначно идентифицировать строку в таблице. Поскольку значение ключа всегда уникально, вы можете использовать его для защиты от дублирования строк. Этот ключ не может и не должен содержать неопределённых или нулевых значений.
Индекс в базе данных работает подобно индексному указателю в папке файлов. Он позволяет легче искать информацию.
Foreign Key – это столбец или группа столбцов в одной таблице, чьи значения должны соответствовать Primary Key из другой таблицы

Проектирование баз данных

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

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

Анализ данных

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

Какие типы информации используются в настоящее время бизнесом? В каких типах информации сейчас нуждается бизнес?
Какой тип информации я хочу получать из системы? Какие отчёты я хочу формировать из неё?
Что я буду делать с этой информацией?
Какой контроль и какая защита понадобится этой системе?
Потребуется ли расширение системы?

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

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

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

Логическое проектирование базы данных

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

Сбор информации для логического проекта, это итеративный процесс, который включает следующие действия:

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

На этой стадии, вы не рассматриваете требования обработки, производительности или аппаратные ограничения.

Табличные взаимосвязи

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

В примере ниже таблицы Customer и Order связаны между собой по Foreign Key (поле Customer Number).

Если поле Customer Number будет индексом в обоих таблицах, то вы сможете быстро выполнять следующие операции:

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

Существует несколько типов взаимосвязей между таблицами. Первый тип, это отношение «один к одному» (one-to-one), когда каждая строка в одной таблице, имеет только одну связанную строку во второй таблице. Например, когда за одним сотрудником закреплён только один офис. Или, когда каждый отдел имеет только одного начальника, т.е. один начальник может управлять только одним отделом. Эта связь представлена на следующем рисунке.

Бизнес-правила могут подразумевать, что за одним офисом может быть закреплён один или вообще не одного сотрудника. Или, у одного отдела может быть один или не может быть вообще начальника. Такая связь называется «ноль или один» (zero-or-one).

Второй тип, это связь «один ко многим» (one-to-many), которая имеет место, если каждая строка в одной таблице, имеет одну или более связанных строк в другой таблице. На следующем рисунке приведён пример такой связи. Здесь один клиент может разместить множество заказов, или каждый коммерческий представитель может иметь множество клиентских счетов.

Тем не менее, бизнес-правила могут складываться так, что у одного клиента может быть ноль или много заказов, один студент может посещать ноль или более курсов, а коммерческий представитель может не иметь, а может и иметь счета. Такие отношения называются «ноль или много» (zero-or-many).

Третий тип, это «много ко многим» (many-to-many), когда строка в одной таблице может иметь множество связанных строк во второй таблице, так же как строки из второй таблицы могут быть связаны со множеством строк в первой таблице. На следующем рисунке представлен пример, когда заказ содержит множество товаров, и в тоже время каждый товар может содержаться во множестве других отдельных заказах. Или, когда сотрудник может работать над несколькими проектами, в то время как над каждым проектом может работать множество сотрудников.

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

Например, для установки связи «один ко многим» между таблицами Order и Item, необходимо создать таблицу перекрёстных ссылок Order-Line как показано на рисунке ниже. Эта таблица будет содержать такие поля из обоих таблиц, как Order Number и Item Number. Без этой таблицы, вы были бы вынуждены сохранять дублирующую информацию или добавлять дополнительные столбцы в обоих таблицах Order и Item.

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

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

База данных считается нормализованной, если её таблицы (по крайней мере, большинство таблиц) представлены как минимум в третьей нормальной форме. Часто многие таблицы нормализуются до четвертой нормальной формы, иногда, наоборот, производится денормализация. Использование таблиц в пятой нормальной форме, или, вернее сказать, сознательное приведение их к пятой нормальной форме, в реальных базах данных практически не встречается.

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

Первая нормальная форма:

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

Чтобы это понять, изучим ненормализованную таблицу Customer

Cust Num	Name	Street	Order Number
101	Jones, Sue	2 Mill Ave.	M31, M98, M129
102	Hand, Jim	12 Dudley St.	M56
103	Lee, Sandy	45 School St.	M37, M40
104	Tan, Steve	67 Main St.	M41

Здесь, каждое поле столбца Order Number может имеет более чем одно значение. Это сильно затрудняет выполнение даже таких простых задач, как удаление заказа, подсчёт общего количества заказов по клиенту или выполнение печати заказов в отсортированном порядке. Чтобы выполнить любую из этих задач, вам придётся придумывать сложные алгоритмы анализа каждого значения в таком поле. Поэтому, вы должны изменить таблицу так, чтобы каждое поле в ней содержало только одно конкретное значение.

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

Cust Num	Name	Street	Order Number1	Order Number2	Order Number3
101	Jones, Sue	2 Mill Ave.	M31	M98	M129
102	Hand, Jim	12 Dudley St.	M56	Null	Null
103	Lee, Sandy	45 School St.	M37	M140	Null
104	Tan, Steve	67 Main St.	M41	Null	Null

Здесь, вместо одного столбца Order Number создано три столбца с одинаковым назначением. Такой вид таблиц крайне неэффективен.Что будет, если клиент сделает ещё один заказ, если у него уже есть три заказа? Вам придётся или добавить новый столбец, или же заменить одно из значений в существующих столбцах. Довольно трудно определить максимально возможное количество клиентских заказов. Но допустим, что вы остановились на их количестве равным 200. Хорошо, но тогда получается, что если клиент в среднем будет иметь лишь 10 заказов, то у вас таблица будет содержать по крайней мере 190 пустых (Null) значений. Кроме того, достаточно трудно и долго выполнять извлечение данных из повторяющихся столбцов. Например, для того чтобы определить, какой клиент имеет заказ с номером M98, вам будет нужно просмотреть каждый такой столбец индивидуально на предмет наличия соответствующего значения, а их у нас 200.

Для того чтобы привести таблицу Customer к первой нормальной форме, разобьём её на две отдельные таблицы. В которых первая будет содержать информацию только о клиентах (Customer), а вторая только информацию о заказах (Order). То, что у нас получилось смотрите ниже.

Таблица Customer приведённая к первой нормальной форме:

Cust Num (Primary key)	Name	Street
101	Jones, Sue	2 Mill Ave.
102	Hand, Jim	12 Dudley St.
103	Lee, Sandy	45 School St.
104	Tan, Steve	67 Main St.

Созданная в результате нормализации таблица Order:

Order Number (Primary key)	Cust Num (Foreign key)
M31	101
M98	101
M129	101
M56	102
M37	103
M140	103
M41	104

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

Таблица, приведённая к первой нормальной форме, имеет следующие преимущества:

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

Вторая нормальная форма

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

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

Cust Num	Name	Street	Order Number	Order Date	Order Amount
101	Jones, Sue	2 Mill Ave.	M31	19.03.18	400,87
101	Jones, Sue	2 Mill Ave.	M98	13.08.18	3000,90
101	Jones, Sue	2 Mill Ave.	M129	09.02.18	919,45
102	Hand, Jim	12 Dudley St.	M56	14.05.17	1000,50
103	Lee, Sandy	45 School St.	M37	25.12.17	299,89
103	Lee, Sandy	45 School St.	M140	15.03.18	299,89
104	Tan, Steve	67 Main St.	M41	02.04.17	2300,56

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

Первые три поля в таблице содержат повторяющуюся информацию в столбцах Cust Num, Name и Street. Это называется избыточностью.
Если клиент Sue Jones поменяет свой адрес, то вам придётся внести изменения в остальные поля, содержащие прежний адрес. В этом случае, вы будете должны изменить три строки. Если какая-либо из строк по каким-либо причинам не будет изменена, то это приведёт к противоречивости данных. Таким образом целостность базы данных будет под вопросом.
Если вы решите удалить все заказы до 1 Ноября 2017 года, то в итоге вы потеряете всю информацию по клиентам Jim Hand и Steve Tan. Непреднамеренное удаление строк во время операций изменения называется аномалией.

Для решения этих проблем вы должны переместить данные. Таблица, должна содержать индивидуальные данные клиентов, такие как Cust Num, Name и Street. Эти данные всегда остаются одни и те же, когда клиент оформляет заказ. Столбцы Order Num, Order Date и Order Amount не относятся на прямую к клиенту и не зависят от первичного клича Cust Num. Поэтому, они должны располагаться в другой отдельной таблице. Таким образом, для того чтобы привести эту таблицу Customer ко второй нормальной форме, вы должны переместить столбцы Order Num, Order Date и Order Amount в таблицу Order.

Таблица Customer:

Cust Num	Name	Street
101	Jones, Sue	2 Mill Ave.
102	Hand, Jim	12 Dudley St.
103	Lee, Sandy	45 School St.
104	Tan, Steve	67 Main St.

Таблица Order:

Order Number (Primary key)	Order Date	Order Amount	Cust Num (Foreign key)
M31	19.03.18	400,87	101
M98	13.08.18	3000,90	101
M129	09.02.18	919,45	101
M56	14.05.17	1000,50	102
M37	25.12.17	299,89	103
M140	15.03.18	299,89	103
M41	02.04.17	2300,56	104

Таблица Customer теперь содержит только одну строку по каждому из клиентов, в то время как таблица Order содержит строки по каждому клиентскому заказу, и её первичный ключ состоит из поля Order Number. Эта таблица так же содержит общий столбец Cust Num, который обеспечивает связь заказа с клиентом.

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

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

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

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

Ниже представлена таблица Order, которая содержит столбец Total After Tax. Значение этого столбца рассчитывается на основании добавления 10% к значению столбца Order Amount.

Order Number (Primary key)	Order Date	Order Amount	Total After Tax	Cust Num (Foreign key)
M31	19.03.18	400,87	441,74	101
M98	13.08.18	3000,90	3300,99	101
M129	09.02.18	919,45	1011,39	101
M56	14.05.17	1000,50	1100,55	102
M37	25.12.17	299,89	329,87	103
M140	15.03.18	299,89	329,87	103
M41	02.04.17	2300,56	2530,61	104

Для того чтобы привести эту таблицу к третьей нормальной форме, необходимо избавиться от столбца Total After Tax, поскольку его значение зависит от значение в столбце Order Amount, а также от ставки самого налога, которая со временем может поменяться. А для того, чтобы получить значения Total After Tax в своих отчётах, вам лучше создать небольшой алгоритм, который будет выполнять соответствующие расчёты. Или например, взять таблицу сотрудников, вам не нужно хранить в ней возраст сотрудника, поскольку в ней уже есть поле с датой его рождения, а значит вы всегда сможете вычислить его возраст с помощью простого алгоритма.

Преимущества таблицы в третьей нормальной форме в следующем:

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

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

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

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

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

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

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

Определение индексов

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

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

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

Как работают индексы

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

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

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

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

Существует четыре важных причины для создания индексов в таблице:

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

Например, когда вы запрашиваете информацию о заказе с номером 4, СУБД не переходит напрямую к таблице. Вместо этого, она обращается к индексу Order-Num для поиска указанного значения. Для чтения строки, она использует указатель на эту строку в таблице Order. Поскольку индекс хранится в числовом порядке, то его поиск и поиск строки выполняется очень быстро.

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

Автоматическая сортировка строк. Индекс определяет сортировку строк. Так как индекс автоматический последовательно сортирует строки (вместо того порядка в котором они были созданы и сохранены на диске), вы можете быстро получать необходимые отчёты в пределах определённого диапазона дат. Например, когда вы формируете запрос «найти все заказы сформированные с 06.09.2017 по 20.09.2017», все строки с такими заказами будут отображены в хронологическом порядке.

Примечание: хотя индекс и навязывает строкам определённый порядок, данные, хранимые на диске, всё равно расположены в том порядке, в котором они были созданы. Индексы, не контролируют порядок расположения данных на диске. Вы можете создавать сколько угодно индексов, тем самым определяя любой порядок для отображения данных, но не хранения их. Однако не переусердствуйте (см. ниже).

Обеспечение уникальности. Когда вы определяете уникальный индекс для таблицы, то система гарантирует, что ни какие две строки с одинаковыми значениями не могут быть созданы для этого индекса. Например, в индексе Order-Num существует значение 4, и если вы попытаетесь создать ещё один заказ с номером 4, то вы получите сообщение об ошибки, информирующее вас о том, что строка с таким номером уже существует. Это произойдёт потому, что Order-Num это уникальный индекс для таблицы Order.
Быстрая обработка межтабличных связей. Две таблицы связаны если вы определяете столбец (или столбцы) в одной таблице, которые вы можете использовать для доступа к строкам в другой таблице. Если таблица к которой вы обращаетесь, содержит индекс, основанный на связывающем столбце, то доступ к строке будет осуществлён более эффективно. Столбцы, которые вы используете, для связи двух таблиц, не обязательно должны иметь одинаковые названия в этих таблицах.

Но у индексов есть и свои недостатки. Поэтому, всегда помните две вещи при создании индекса в своей базе данных:

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

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

Как выбрать таблицы и столбцы, по которым нужно создать индексы?

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

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

Индексы и ROWID

Индекс – это список индексных значений и идентификаторов строк (ROWID). ROWID это физические указатели на записи в таблице, которые обеспечивают самый быстрый доступ к строкам. ROWID не изменяется в течение всей жизни строки, он может поменяться только в результате перезагрузки (D&L) данных в базе. Если вы удалите строку и создадите точно такую же новую строку, то ROWID тоже будет отличаться. Индексные блоки базы данных для обеспечения быстрого доступа организованы в древовидную структуру. СУБД размещает индексы строки в пределах индексного дерева. Как только строка будет размещена, доступ к данным по ROWID будет открыт. Во время поиска строки СУБД не блокирует всё индексное дерево. Блокируется только тот блок, который содержит нужную строку. Поэтому другие пользователи могут свободно обращаться к строкам той же самой таблицы.

Идентификаторы строк ROWID могут использоваться повторно. Например, запись №1 имеет ROWID 0x000000010f13f73a. Через некоторое время эта запись была удалена. В то же время в таблице создаются новые запись, которым может быть присвоен освободившийся ROWID 0x000000010f13f73a. По этой причине никогда не храните значения ROWID в качестве ключа в поле таблицы для быстрого поиска записи по ROWID, так как он может в любое время измениться и указывать на совершенно другую запись.

И ещё небольшое примечание. На ровне с ROWID в качестве идентификатора записи существуют RECID. Отличие RECID от ROWID в том, что RECID это целочисленное значение (DEC), а ROWID это шестнадцатеричное значение (HEX). Кроме того, использовать RECID в программировании на языке ABL(4GL) не рекомендуется уже очень давно (более 10 лет). Всегда оперируйте ROWID.

Расчёт размера индекса

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

Количество строк * (7 + количество столбцов в индексе + размер индексируемого столбца) * 2

Например, если у вас есть индекс для символьного столбца со средним количеством символом равным 21 (размер индексируемого столбца), и в таблице имеется 500 строк, то размер индекса будет примерно следующим:

500 * (7 + 1 + 21) * 2 = 29,000 байт

Размер индекса зависит от четырёх моментов:

От количества вхождений или строк.
От количества столбцов в индексе.
От размера значений в столбце, т.е. строковое значение «абвгдежзиклмн» займёт больше места чем строка из «абвгд». Дополнительно, специальные символы и многобайтные символы (Unicode) тоже занимают много места.
Количества аналогичных ключевых значений.

Однако, вы никогда не достигнете максимума, потому что СУБД использует алгоритм сжатия данных, для уменьшения количества дискового пространства, используемого индексами. Фактически для индексов используется на 20-60%% меньше дискового пространства, чем вы вычислили по вышеописанной формуле.

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

Здесь, индекс City создан для городов, а ROWID определён в порядке возрастания. Для самого первого входа индекса «Bolonia» сжатия нет. Но для последующих данных, устраняются все символы, которые идентичны ведущим символам в слове Bolonia. Поэтому для второго входа, «Bolton», не имеет смысла сохранять первые три символа «Bol», так как они идентичны ведущим символам от Bolonia. Взамен Bolton сжимается до «ton». И дальше OpenEdge не будет сохранять следующие входы Bolton. Аналогично, первые два символа в словах «Bonn» и «Boston» («Bo») тоже не будут сохранены.

Для ROWID OpenEdge удаляет идентичные ведущие цифры. Он сохраняет последнюю цифру ROWID отдельно и объединяет ROWID, которые отличаются последней цифрой, в один вход (entry). Например, здесь, OpenEdge сохраняет ведущие три цифры от ROWID 333 как ROWID, а последнюю цифру сохраняет как «nth byte». Далее по списку обратите внимание, что первое вхождение Boston имеет ROWID 1111, а второе вхождение с ROWID 1118. Ведущие три цифры (111) от второго ROWID не сохранены, т.к. они идентичным первому ROWID, которые уже сохранены, и только последняя цифра (8) осталась в индексе.

Из-за таких особенностей сжатия OpenEdge может существенно уменьшить количество пространства, используемого индексами. В вышеприведённом примере для индекса используется всего 65 байт, вместо расчётных 141 байт. Т.е. сэкономлено примерно 54% дискового пространства. Таким образом, мы видим, что используемое индексами дисковое пространство зависит от данных напрямую. И с неуникальными индексами вы можете сэкономить больше.

Устранение избыточных индексов

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

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

Администрирование

Основы баз данных с OpenEdge

Введение

Характеристики базы данных