Проектирование баз данных. СУБД

Проектирование реляционных БД с использованием ER-технологии

Основные понятия реляционных БД

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

При проектировании и эксплуатации БД к ней предъявляются следующие требования: 

1. Адекватность отображения ПО (полнота, целостность, непротиворечивость актуальность данных)
2. Возможность взаимодействия пользователей разных категорий; обеспечение высокой эффективности доступа
3. Дружественный интерфейс
4. Обеспечение секретности и конфиденциальности
5. Обеспечение взаимной независимости программ и данных
6. Обеспечение надежности БД; защита данных от случайного и преднамеренного разрушения; возможность быстрого и полного восстановления данных в случае сбоев в системе.

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

Этапы проектирования БД

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

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

Модель данных - это правила, которые определяют структуру данных, допустимые реализации данных и допустимые операции над данными. 

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

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

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

Определение РБД

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

Отношение задается своим именем и списком атрибутов - элементов, связанных отношением <имя отношения>(<список атрибутов>)

Имя отношения выбирается таким образом, чтобы оно поясняло смысл связи между элементами отношения (семантику отношения).

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

Атрибут характеризуется именем, типом, значением и другими свойствами.

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

Значение атрибута - величина, характеризующая некоторое свойство объекта и связи. 

Атрибуты соответствуют классам сущностей, объединяемых данным отношением. Список имен атрибутов отношения и их характеристик называется схемой отношения.

Характеристики атрибутов задают область допустимых значений (ОДЗ) для каждого аргумента отношения.

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

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

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

При первой нормальной форме (1НФ) все атрибуты отношения должны быть простыми (атомарными, неделимыми) с точки зрения СУБД. Удовлетворение требованиям первой нормальной формы называется структурной или синтаксической нормализации.

НФ более высокого порядка основаны на понятии функциональной зависимости (ФЗ), которая может быть определена как:

Пусть А и В - подмножества атрибутов отношения. Говорят, что В функционально зависит от А (А-->В), если для каждого значения А существует ровно одно связанное значение В. ФЗ можно выявить, исходя из базовых свойств самих атрибутов путем анализа. 

При второй нормальной форме (2НФ) должна обеспечиваться 1НФ и каждый неключевой атрибут функционально полно зависит от ключа. 

Полная ФЗ от ключа означает, что если ключ составной, то любой неключевой атрибут зависит от всего ключа и не зависит ни от какой его части. 

При третьей нормальной форме (3НФ) отношение должно находиться 2НФ, а также каждый неключевой атрибут нетранзитивно зависит от ключа, проще говоря: отсутствует ФЗ между неключевыми атрибутами. 

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

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

логические ограничения, которые накладываются на данные, называются ограничениями целостности.

Выделяют два основных вида ограничений: внутренние и явные.

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

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

В РМД существует два вида внутренних ограничений целостности:

1. Целостность по существованию - потенциальный ключ отношения не может иметь пустого (NULL) значения. Иными словами, так как потенциальный ключ отношения позволяет из всего множества экземпляров сущности выделить только один, то сущность, не имеющая идентификатора, не существует.
2. Целостность по связи - определяется понятием внешнего ключа (ВК) отношения: подмножество атрибутов отношения R2 называется внешним ключом для отношения R1, если каждому значению ВК отношения R2 найдется такое же значение первичного ключа в отношении R1. Внешний ключ является тем клеем, который обеспечивает связывание отдельных отношений РБД в единое целое. Целостность данных по связи означает систему правил, используемых в СУБД для поддержания связей между записями в связанных таблицах, а также обеспечивает защиту от случайного удаления или изменения связанных данных, от некорректного изменения ключевых полей. 

Основными операциями над отношениями РМД являются 8 операций, входящих в реляционную алгебру Кодда: объединение, пересечение, разность (вычитание), декартово произведение - специальные операции: выбор, проекция, соединение и деление. 

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

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

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

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

Деление -  операция выполняется над двумя отношениями R1 и R2, имеющими в общем случае разные структуры и часть одинаковых атрибутов. В результате операции образуется новое отношение, структура которого получается исключением из множества атрибутов отношения R1 множества атрибутов отношения R2. Результирующие строки образуются из тех строк отношения R1, значения несовпадающих атрибутов которых одинаковые, а значения общих атрибутов образуют отношение, совпадающее с отношением R2.

Проектирование РБД с использованием ER-технологии

Постановка задачи проектирования РБД

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

1. Возможности хранения всех необходимых данных в Бд
2. Исключения избыточных данных
3. Сведения числа хранимых отношений в БД к минимуму
4. Нормализации отношений для упрощения решения проблем, связанных с обновлением, добавлением и удалением данных

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

Один из подходов к проектированию БД: метод "сущность-связь"
Суть метода состоит в построении ER-диаграмм, отображающих в графической форме основные объекты ПО и связи между ними, и в определении характеристик этих связей. Затем по четким правилам делается переход от ER-диаграмм к таблицам БД, осуществляется наполнение таблиц атрибутами и проверка их на выполнение условий нормализации (НФБК). Определяются ключевые атрибуты таблиц и связи между таблицами. Результатом проектирования является схема данных БД.
Сущности и связи
Сущность - это объект, информация о котором должна быть представлена в БД (обычно соответствует существительному). Экземпляр сущности - это информация о конкретном представителе объекта. Например, для сущности Студент экземпляром является Петухов, а для сущности Группа - экземпляром является 144.
Связь - соединение между двумя и более сущностями (соответствует глаголу). Экземпляр связи - это конкретная связь между конкретными представителями объектов. Пример: Петухов учиться в группе 144.
Атрибут - свойство сущности или связи. Например, Фамилия, Имя, Отчество есть атрибуты сущности Личность. А слова Терехин, Николай есть экземпляр этого атрибута.
Атрибут или набор атрибутов, используемый для однозначной идентификации экземпляра сущности, называется ключом сущности. Каждый экземпляр связи определяется набором ключей сущностей, соединяемых этой связью.
Построение ER-диаграммы ПО
Рассмотрим построение ER-диаграммы, описывающей структуру ПО. В ER-диаграмме для отображения сущностей используются прямоугольники, а для отображения связей - ромбы. Различают ER-диаграммы для экземпляров сущностей и ER-диаграммы для классов сущностей. Ниже приведены ER-диаграммы обоих типов.

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

• один к одному (1:1)

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

• один ко многим (1:М или М:1)

Каждый экземпляр первой сущности может быть связан с несколькими экземплярами второй сущности, а каждый экземпляр второй сущности может быть связан только с одним экземпляром первой сущности

• многие ко многим (М:N)

Каждый экземпляр первой сущности может быть связан с несколькими экземплярами второй сущности и наоборот.

Класс принадлежности сущности к связи может быть обязательным и необязательным.

Получение отношений из диаграммы ER-типа

Общий подход к построению БД с использованием ER-метода состоит в выполнении следующих шагов:

1. Построения диаграммы ER-типа, включающей в себя все сущности и связи, важные с точки зрения интересов организации
2. Анализа связей и определения их характеристик: степени связи и класса принадлежности
3. Построения набора предварительных отношений с указанием предполагаемого ключа для каждого отношения
4. Подготовки списка всех представляющих интерес атрибутов (тех из них, которые не были уже перечислены в диаграмме ER-типа в качестве ключей сущности) и назначения каждого из этих атрибутов одному из предварительных отношений с тем условием, чтобы эти отношения находились в НФБК
5. Проверки, все ли полученные отношения находятся в НФБК
6. Построения схемы данных
7. Если полученные в итоге отношения не находятся в НФБК или если некоторым атрибутам не находится логически обоснованных мест в предварительных отношениях, то в этих случаях необходимо пересмотреть ER-диаграммы на предмет устранения возможных затруднений.

Раннее были рассмотрены первые два шага процесса проектирования, третий шаг - построение предварительных отношений - выполняется по определенным правилам.

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

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

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

Правило 4. Если степень бинарной связи равна 1:N и класс принадлежности N-связной сущности является обязательным, то достаточным является использование двух отношений, по одному на каждую сущность, при условии, что ключ каждой сущности служит в качестве первичного ключа для соответствующего отношения. Дополнительно ключ 1-связной сущности должен быть добавлен как атрибут в отношении, отводимое N-связной сущности.

Правило 5. Если степень бинарной связи равна 1:N и класс принадлежности N-связной сущности является необязательным, то необходимо формирование трех отношений: по одному для каждой сущности и одно отношение для связи. Причем ключ каждой сущности используется в качестве первичного ключа соответствующего отношения. Отношение связи должно иметь в числе атрибутов ключи каждой сущности.

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

Правило 7. В случае трехсторонней связи необходимо использовать 4 (n+1) предварительных отношения, по одному для каждой сущности, и одно для связи. Причем ключ каждой сущности должен служить в качестве первичного ключа для соответствующего отношения. Отношение, порождаемое связью, будет иметь среди своих атрибутов ключи от каждой сущности. (Аналогично, когда связь N-сторонняя, требуется n+1 предварительное отношение)

Между ролевыми сущностями могут быть связи, зависящие от деятельности организации и от выполняемых ими функций. Обобщающая сущность описывает общие свойства всех ролевых сущностей. Между обобщающей сущностью и ролевыми сущностями устанавливается связь типа "дерево", иерархическая связь.
Правило 8. Обобщающей сущности соответствует одно отношение, причем ключ сущности служит в качестве ключа отношения, общие для всех ролевых сущностей атрибуты приписываются этому отношению. Ролевые элементы и связи, их соединяющие, порождают такое число отношений, которое определяется ранее описанными правилами, причем каждая роль трактуется как обычная сущность. Связываются отношения с помощью ключевого атрибута. Каждому значению ключевого атрибута ролевой сущности соответствует одна запись в обобщающем отношении с таким же значение ключа.

Связь между таблицами
Результатом проектирования по методу "сущность-связь" является совокупность взаимосвязанных таблиц. Связь между таблицами осуществляется через одинаковые по смыслу. типу и значению атрибуты. Назовем их атрибутами связи. В одних таблицах эти атрибуты играют роль ключевых, в других они вводятся дополнительно.
Таблица, в которой атрибут связи является ключом отношения, называется главной; связанная с ней по этому атрибуту таблица называется подчиненной. Информация в подчиненной таблице зависит от данных в главной таблице. Атрибут связи в подчиненной таблице называется внешним ключом.
Данные в связанных таблицах должны удовлетворять следующим ограничениям целостности:

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

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

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

______________________________________________________________________

______________________________________________________________________

Источник: Проектирование баз данных. СУБД Microsoft Access: Учебное пособие для вузов/ Н.Н,Гринченко, Е.В.Гусев, Н.П.Макаров, А.Н. Пылькин, Н.И, Цуканова - М.: Горячая линия-Телком, 2004