4.3 Моделирование баз данных

Моделирование БД выполняется в соответствии с трехуровневой архитектурой СУБД, принятой в 1978 г. комитетом по стандартизации ANSI/SPARC (ANSI – Национальный институт стандартизации США; SPARC – Комитет планирования стандартов и меры). Трехуровневая архитектура СУБД включает в себя концептуальный, внутренний и внешний уровни.

Концептуальный уровеньдает представление о логической схеме БД.

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

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

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

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

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

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

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

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

Рис. 4.4. Соотношение концептуальной и внешней моделей

Инфологическая модель «сущность – связь»

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

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

Сущность имеет тип и экземпляры. Типом является название сущности, отличающее одну сущность от другой, например, ГОРОДА. Экземпляр представляет собой конкретное значение этой сущности, например, г. Полоцк Витебской области.

Атрибут– поименованная характеристика сущности. В реляционной таблице атрибуту соответствует поле. Имя атрибута для конкретной сущности должно быть уникальным, но может быть одинаковым для различного типа сущностей. Примерами атрибутов для сущности ГОРОДА могут быть: название города, код АМТС (автоматической междугородной или международной телефонной связи), административное значение (областной или районный центр), расстояние до ближайшей железнодорожной станции.

Атрибут сам может выступать в качестве сущности. Примером может служить сущность КОДЫ АМТС с атрибутами: код АМТС, название города, страна.

Ключ– набор атрибутов сущности, по значениям которых можно найти требуемый экземпляр сущности.

Связь– ассоциирование двух или более сущностей.

Типы связей

Различают следующие типы связей между сущностями:

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

• один ко многим (1:М, в ЭВМ представлена как 1:∞);

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

Связь один к одномуобозначает, что в каждый момент времени одному экземпляру сущностиAсоответствует один и только один экземпляр сущностиBи наоборот(Рис. 4.5).

Рис.4.5 Связь один к одному

Связь один ко многимимеет место в том случае, когда в каждый момент времени одному экземпляру сущностиAсоответствует ноль, один или более экземпляров сущностиB, но каждый экземпляр сущностиBсвязан только с одним экземпляром сущностиA(Рис. 4.6)

Рис. 4.6 Связь один ко многим

Связь многие ко многим, показывает, что в каждый момент времени одному экземпляру сущностиAсоответствует ноль, один или более экземпляров сущностиB, и наоборот – каждому экземпляру сущностиBсоответствует ноль, один или более экземпляров сущностиA (Рис. 4.7).

Рис. 4.7 Связь многие ко многим

ER- диаграммы

Инструментом графического представления инфологических моделей является язык ER-диаграмм (Entity-Relationship– сущность-связь).

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

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

Таблица 4.3 Сущности и атрибуты инфологической модели

На рисунке 4.8 представлена инфологическая модель на языке ER-диаграмм

Рис. 4.8 ER-диаграмма инфологической модели БД

ER-диаграмма, изображенная на рисунке 4.8, удовлетворяет следующим условиям: сотрудники отдела могут ездить в разные города; каждый сотрудник может несколько раз быть в одном и том же городе; в один и тот же город могут ездить разные сотрудники; любая специальность может быть в любом вузе.

Преобразование ER- модели в реляционную модель данных

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

1. Каждая простая сущность превращается в отношение (таблицу). Простая сущность – это сущность, не являющаяся подтипом и не имеющая подтипов. Имя сущности становится именем отношения.

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

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

4. Атрибуты, имеющие типы связи M:1 (и 1:1) становятся внешними ключами.

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

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

а) все подтипы размещаются в одной таблице;

б) для каждого подтипа строится отдельная таблица.

Полученные таблицы должны удовлетворять требованиям:

• каждая таблица состоит из однотипных строк и имеет уникальное имя;

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

• таблицы имеют фиксированное число столбцов и их значений;

• в каждой таблице на пересечении строки и столбца должно находиться только одно значение или ничего;

• в столбцах таблицы размещаются однородные значения данных.

Важным этапом проектирования реляционной БД является обеспечение реляционной целостности данных.

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

Существуют ограничения по условию целостности данных:

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

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

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

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

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

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

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

На завершающем этапе моделирования БД проводится процедура нормализации. Цель нормализации состоит в том, чтобы набор таблиц и состав их полей удовлетворяли условию минимальности по следующим параметрам:

• избыточность полей в таблицах;

• состав первичных ключей;

• нежелательные функциональные зависимости между полями;

• дублирование данных;

• трудоемкость выполнения операций включения, удаления и модификации данных;

• время выполнения запросов на выборку данных

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

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

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

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

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

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

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

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

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

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

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

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

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