Реляционные базы данных

Иногда ты завязываешь отношения, а иногда отношения связывают тебя.

из фильма “My Sassy Girl”

Поговорим о теории баз данных с примерами их реализации в My Visual Database (MVDB).

Что такое реляционные базы?

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

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

Атрибут (или свойство) — это некоторый показатель, который характеризует некий объект и принимает для конкретного экземпляра объекта некоторое числовое, текстовое или иное значение. Информационная система оперирует наборами объектов, спроектированными применительно к данной предметной области, используя при этом конкретные значения свойств тех или иных объектах.

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

Первичный ключ

В реляционной базе данных каждая таблица должна иметь первичный ключ (primary key) — поле или комбинацию полей, которые единственным образом идентифицируют каждую строку таблицы. Если ключ состоит из нескольких полей, он называется составным. Ключ должен быть уникальным и однозначно определять запись. По значению ключа можно отыскать единственную запись. Ключи служат также для упорядочивания информации в БД.

В MVDB первичный ключ создаётся автоматически: при создании таблицы добавляется поле ID с типом INTEGER, которое не отображается в списке полей.

Внешний ключ

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

На схеме изображены три таблицы и связи между ними: первичные ключи (1), внешний ключ (2) – ссылка на таблицу document и внешний ключ (3) – ссылка на таблицу item.

Связи между таблицами

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

Существуют следующие типы информационных связей:

• один-к-одному;
• один-ко-многим;
• многие-ко-многим.

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

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

В этом примере таблица document (документы) имеет связь один-к-одному с таблицей invoice (счет) и таблицей cerificate (сертификат). В частности, это позволяет реализовать объектный подход при хранении данных: объект “счет” наследует атрибуты “номер” и “дата” у родительского объекта “документ”.

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

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

Между объектом country (страна) и city (город) имеется связь один-ко-многим, то есть в одной стране может быть много городов.

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

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

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

Связь многие-ко-многим между таблицами document (документ) и item (номенклатура) реализуется через кросс-таблицу doc_detail (детализация документа). То есть одной записи в таблице document (заголовок документа) соответствует несколько записей в таблице doc_detail (строки документа), также одной записи в таблице item (наименование товара) соответствует несколько записей в таблице doc_detail (строки документа).

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

Поддержание целостности данных

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

• Каскадная обработка. Каскадное удаление – при удалении записи производится удаление всех зависимых записей в других таблицах. Каскадное обновление – при редактировании значения ключевого поля производится обновление всех зависимых полей, в которые вносится новое значение. Каскадное означает, что обработка затронет все нисходящие связи, по цепочке зависимостей.
• Сбросить в NULL – при удалении записи производится сброс ссылок на неё в NULL
• Запретить удаление – запрещает удаление записи, если на неё ссылаются другие записи

В MVDB поддержка целостности данных предполагает два возможных сценария: 

• запрет на удаление записи, если на неё ссылаются другие записи;
• каскадное удаление записей, на которые они ссылаются.

Какой вариант выбрать, решает разработчик в зависимости от назначения тех или иных таблиц. 

Например, если таблица “doc_detail” (детализация документа) ссылается на таблицу “document” (заголовок документа), то необходимо настроить каскадное удаление записей. Тогда при удалении документа автоматически удалятся все записи с детализацией, относящиеся к этому документу. А вот для ссылка на таблицу “item” стоит запрет каскадного удаления: если пользователь попытается удалить информацию о товаре, но в базе будут документы, описывающие приходно-расходные операции с данным товаром, программа выдаст ошибку и заблокирует удаление, тем самым сохранив целостность данных. Для номенклатуры сначала необходимо убрать все ссылки на эту запись, изменив соответствующие записи в таблице с детализацией документов.

Порядок строк

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

При извлечении данных из базы SQLite MVDB упорядочивает записи по первичному ключу. А для настройки порядка отображения данных предусмотрены различные механизмы: свойства компонентов отображения, которые настраиваются разработчиком, а также изменение сортировки конечным пользователем.

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

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

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

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

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

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

Так, если из таблицы Студент требуется получать сведения по имени студента, то поле ФИО следует разделить на три поля Фамилия, Имя, Отчество.

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

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

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

Например, в таблице “student” поле “faculty” содержит название факультета, которое не зависит от первичного ключа. Для нормализации необходимо создать отдельную таблицу для хранения сведений о факультете:

В MVDB первичный ключ всегда состоит из одного столбца.

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

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

Например, в таблице “Student” поля id_faculty и id_department находятся в транзитивной зависимости. Кафедра зависит от факультета. Для устранения транзитивной зависимости необходимо привести зависимости, как указано на схеме.

Операции над табличными данными

Над реляционными таблицами возможны следующие операции:

• Объединение таблиц с одинаковой структурой. Результат— общая таблица: сначала первая, затем вторая (конкатенация).
• Пересечение таблиц с одинаковой структурой. Результат — выбираются те записи, которые находятся в обеих таблицах.
• Вычитание таблиц с одинаковой структурой. Результат — выбираются те записи, которых нет в вычитаемом.
• Выборка (горизонтальное подмножество). Результат — выбираются записи, отвечающие определенным условиям.
• Проекция (вертикальное подмножество). Результат — отношение, содержащее часть полей из исходных таблиц.
• Декартово произведение двух таблиц Записи результирующей таблицы получаются путем объединения каждой записи первой таблицы с каждой записью другой таблицы.

С помощью настройки свойств компонент MVD можно получать выборки и проекции, остальные операции доступны при использовании SQL-запросов.

Реляционная модель данных

Построение реляционной модели лучше всего разобрать на примере. 

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

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

Таблица 1. Карандаш

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

Таблица 2. Изготовитель 

Таблица 3. Толщина

Таблица 4. Цвет

Теперь остается описать структуру для хранения информации о наличии карандашей.

Таблица 5. Наличие карандашей

Хранимые данные

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

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

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