Этап 2-й. Логическое проектирование – развитие концептуального представления БД с учетом принимаемой модели (иерархической, сетевой, реляционной и т.д.).

Концептуальное и логическое проектирование вместе называют также инфологическим или семантическим проектированием.

В настоящее время для проектирования БД активно используются CASE-средства, в основном ориентированные на использование ERD (Entity – Relationship Diagrams, диаграммы «сущность–связь»). С их помощью определяются важные для предметной области объекты (сущности), отношения друг с другом (связи) и их свойства (атрибуты). Следует отметить, что средства проектирования ERD в основном ориентированы на реляционные базы данных (РБД), и если существует необходимость проектирования другой системы, скажем объектно-ориентированной, то лучше избрать другие методы проектирования.

ERD были впервые предложены П. Ченом в 1976 г. Основные элементы ERD перечислены ниже .

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

Экземпляр сущности (запись, строка, в РБД – кортеж) – уникально идентифицируемый объект.

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

Атрибут (столбец, поле) – свойство сущности или связи.

Большинство современных CASE-средств моделирования данных, как правило, поддерживает несколько графических нотаций построения информационных моделей. В частности система ERwin фирмы Computer Associates поддерживает две нотации: IDEF1X и IE (англ. Information Engineering – информационное проектирование). Данные нотации являются взаимно-однозначными, т. е. переход от одной нотации к другой и обратно выполняется без потери качества модели. Отличие между ними заключается лишь в форме отображения элементов модели.

При использовании любого CASE-средства вначале строится логическая модель БД в виде диаграммы с указанием сущностей и связей между ними. Логической моделью называется универсальное представление структуры данных, независимое от конечной реализации базы данных и аппаратной платформы. На основании полученной логической модели переходят к физической модели данных. Физическая модель представляет собой диаграмму, содержащую всю необходимую информацию для генерации БД для конкретной СУБД или даже конкретной версии СУБД. Если в логической модели не имеет значения, какие идентификаторы носят таблицы и атрибуты, тип данных атрибутов и т. д., то в физической модели должно быть полное описание БД в соответствии с принятым в ней синтаксисом, с указанием типов атрибутов, триггеров, хранимых процедур и т. д. По одной и той же логической модели можно создать несколько физических. Например, ERwin 4.0 позволяет на основании логической модели сформировать физические более, чем для 20 популярных СУБД (ORACLE, Informix, DB2, MS SQL Server, Access, Foxpro, Paradox и т. д.). На основании физической модели можно сгенерировать либо саму БД или DDL-скрипт¹, который, в свою очередь, может быть использован для генерации БД.

Перечисленный выше порядок действий называется прямое проектирование БД (Forward Engineering DB). CASE-средства позволяют выполнять также обратное проектирование БД (Reverse Engineering DB), т.е. на основании системного каталога БД или DDL-скрипта построить физическую и, далее, логическую модель данных.

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

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

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

Далее рассматривается процедура прямого проектирования с использованием методологии IDEF1X. Методология IDEF1 была разработана Т. Рэмеем. В настоящее время на основе IDEF1 создана ее новая версия – методология IDEF1X, которая в 1981 г. принята ICAM в качестве федерального стандарта США.

¹Data Definition Language – язык определения данных, подмножество языка SQL.

1.2. Концептуальное проектирование с использованием методологии IDEF1X

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

Ниже рассматривается последовательность шагов при концептуальном проектировании.

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

Возможные трудности в определении объектов связаны с использованием постановщиками задачи:

· примеров и аналогий при описании объектов (например, вместо обобщающего понятия «работник» они могут упоминать его функции или занимаемую должность: «руководитель», «ответственный», «контролер», «заместитель»);

· синонимов (например, «допускаемая скорость» и «установленная скорость», «разработка» и «проект», «барьерное место» и «ограничение скорости»);

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

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

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

· должна иметь уникальное имя, и к одному и тому же имени должна всегда применяться одна и та же интерпретация;

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

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

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

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

Рис. 7.1. Сущности

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

Как правило, атрибуты указываются только для сущностей. Если у связи имеются атрибуты, то это указывает на тот факт, что связь является сущностью. Самый простой способ определения атрибутов – после идентификации сущности или связи, задать себе вопрос «Какую информацию требуется хранить о …?». Существенно помочь в определении атрибутов могут различные бумажные и электронные формы и документы, используемые в организации при решении задачи. Это могут быть формы, содержащие как исходную информацию (например, «Ведомость возвышений наружного рельса в кривых»), так и результаты обработки данных (например, «Форма № 1»).

Выявленные атрибуты могут быть следующих видов:

· простой (атомарный, неделимый) – состоит из одного компонента с независимым существованием (например, «должность работника», «зарплата», «норма непогашенного ускорения», «радиус кривой» и т. д.);

· составной (псевдоатомарный) – состоит из нескольких компонентов (например, «ФИО», «адрес», и т. д.). Степень атомарности атрибутов, закладываемая в модель, определяется разработчиком. Если от системы не требуется выборки всех клиентов с фамилией Иванов или проживающих на улице Комсомольской, то составные атрибуты можно не разбивать на атомарные;

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

· многозначный – содержит несколько значений (например, у одного отделения компании может быть несколько контактных телефонов);

· производный (вычисляемый) – значение атрибута может быть определено по значениям других атрибутов (например, «возраст» может быть определен по «дате рождения» и текущей дате, установленной на компьютере);

· ключевой – служит для уникальной идентификации экземпляра сущности (входит в состав первичного ключа);

· неключевой (описательный) – не входит в первичный ключ;

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

После определения атрибутов задаются их домены (области допустимых значений), например:

· наименование участка – набор из букв русского алфавита длиной не более 60 символов;

· поворот кривой – допустимые значения «Л» (влево) и «П» (вправо);

· радиус кривой – положительное число не более 4 цифр.

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

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

· суперключ (superkey) – атрибут или множество атрибутов, которое единственным образом идентифицирует экземпляр сущности. Суперключ может содержать «лишние» атрибуты, которые необязательны для уникальной идентификации экземпляра. При правильном проектировании структуры БД суперключом в каждой сущности (таблице) будет являться полный набор ее атрибутов;

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

· первичный ключ (primary key) – потенциальный ключ, который выбран для уникальной идентификации экземпляров внутри сущности;

· альтернативные ключи (alternative key) – потенциальные ключи, которые не выбраны в качестве первичного ключа.

Если некий атрибут (набор атрибутов) присутствует в нескольких сущностях, то его наличие обычно отражает наличие связи между экземплярами этих сущностей. В каждой связи одна сущность выступает как родительская, а другая – в роли дочерней. Это означает, что один экземпляр родительской сущности может быть связан с несколькими экземплярами дочерней. Для поддержки этих связей обе сущности должны содержать наборы атрибутов, по которым они связаны. В родительской сущности это первичный ключ. В дочерней сущности для моделирования связи должен присутствовать набор атрибутов, соответствующий первичному ключу родительской. Однако здесь этот набор атрибутов уже является вторичным ключом. Данный набор атрибутов в дочерней сущности принято называть внешним ключом (foreign key).

Рассмотрим пример. Пусть имеется таблица, содержащая сведения о студенте, со следующими столбцами:

· номер пенсионного страхового свидетельства (НПСС);

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

· номер пенсионного страхового свидетельства;

В качестве уникального идентификатора можно было бы выбрать совокупность атрибутов «Фамилия»+«Имя»+«Отчество», если вероятность учебы в вузе двух полных тезок была бы равна нулю.

Если в сущности нет ни одной комбинации атрибутов, подходящей на роль потенциального ключа, то в сущность добавляют отдельный атрибут – суррогатный ключ (искусственный ключ, surrogate key). Как правило, тип такого атрибута выбирают символьный или числовой. В некоторых СУБД имеются встроенные средства генерации и поддержания значений суррогатных ключей (например, MS Access).Также стоит отметить, что некоторые разработчики вместо поиска потенциальных ключей и выбора из них первичного в каждую сущность добавляют искусственный атрибут, который в дальнейшем и используют в качестве первичного ключа.

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

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

· размер ключа в байтах должен быть как можно короче;

· тип домена ключа – числовой. При выборе символьных атрибутов в ключ часто возникают проблемы с вводом ошибочных значений (путают регистр букв; добавляют лишние пробелы; используют буквы, пишущиеся на разных языках одинаково). В числовых атрибутах вероятность ошибки при вводе значения меньше;

· вероятность изменения значений ключа была наименьшей (например, «Номер пенсионного страхового свидетельства» более постоянный параметр, чем «ИНН» или «Номер паспорта»);

· с ключом проще всего работать пользователям (например, «Номер пенсионного страхового свидетельства» – это набор из 11 цифр, а «Номер паспорта» зависит от его вида: гражданина СССР, гражданина РФ или зарубежный).

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

Рис. 7.2. Сущности

У независимой сущности «Участки» в качестве первичного ключа назначен суррогатный ключ, у зависимой сущности «План» – первичный ключ составной, состоящий из пяти атрибутов.

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

· связи типа «часть–целое», определяемые обычно глаголами «состоит из», «включает» и т.п.;

· классифицирующие связи (например, «тип – подтип», «множество – элемент», «общее – частное» и т. п.);

· производственные связи (например, «начальник–подчиненный»);

· функциональные связи, определяемые обычно глаголами «производит», «влияет», «зависит от», «вычисляется по» и т. п.

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

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

· именем – указывается в виде глагола и определяет семантику (смысловую подоплеку) связи;

· кратностью (кардинальность, мощность): один-к-одному (1:1), один-ко-многим (1:N) и многие-ко-многим (N:M, N = M или N <> M). Кратность показывает, какое количество экземпляров одной сущности определяется экземпляром другой. Например, на одном участке (описывается строкой таблицы «Участки») может быть один, два и более путей (каждый путь описывается отдельной строкой в таблице «Пути»). В данном случае связь 1:N. Другой пример: один путь проходит через несколько раздельных пунктов и через один раздельный пункт может проходить несколько путей – cвязь N:M;

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

· обязательностью: обязательная (при вводе нового экземпляра в дочернюю сущность заполнение атрибутов внешнего ключа обязательно и для введенных значений должен существовать экземпляр в родительской сущности) и необязательная (заполнение атрибутов внешнего ключа в экземпляре дочерней сущности необязательно или введенным значениям не соответствует экземпляр в родительской сущности);

· степенью участия – количеством сущностей, участвующих в связи. В основном между сущностями существуют бинарные связи, т. е. ассоциации, связывающие две сущности (степень участия равна 2). Например, «Участок» состоит из «Путей». В то же время по степени участия возможны следующие типы связей:

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

o тернарная – связывает три сущности. Например, «Студент» на «Сессии» получил «Оценку по дисциплине»;

В методологии IDEF1X степень участия может быть только унарной или бинарной. Связи большей степени приводятся к бинарному виду.

Внешний вид связи на диаграммах IDEF1X указывает на ее мощность, тип и обязательность (табл. 7.1).

Таблица 7.1. Типы связей

Внешний вид	Тип и обязательность связи	Мощность связи справа
	Обязательная, идентифицирующая	1
	Обязательная, идентифицирующая	0 .. ∞
Z	Обязательная, идентифицирующая	0 или 1
P	Обязательная, идентифицирующая	1 .. ∞
<число>	Обязательная, идентифицирующая	<число>
	Необязательная, неидентифицирующая	0 .. ∞
	Обязательная, неидентифицирующая	0 .. ∞

1. Идентифицирующая связь отображается сплошной линией, неидентифицирующая – пунктирной.

На рис. 7.3–7.5 приведены примеры отображения связей.

Рис. 7.3. Идентифицирующая связь

Рис. 7.4. Неидентифицирующая связь

Рис. 7.5. Рекурсивная связь

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

4. Определение суперклассов и подклассов.

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

Суперкласс – сущность, включающая в себя подклассы.

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

Рис. 7.6. Иерархия наследования (неполная категория)

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

Для каждой категории требуется указать дискриминатор – атрибут родового предка, который показывает, как отличить одну сущность от другой. В приведенном примере дискриминатор – атрибут «Тип».

Иерархии категорий делятся на два типа: неполные (рис. 7.6) и полные (рис. 7.7).

Рис. 7.7. Иерархия наследования (полная категория)

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

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

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

1.3. Пример построения концептуальной модели

На рис. 7.8 показан фрагмент концептуальной информационной модели для задачи определения допускаемых скоростей.

Рис. 7.8. Фрагмент концептуальной информационной модели

В концептуальной модели выделены следующие логические блоки данных:

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

1.4. Логическое проектирование с использованием методологии IDEF1X

Цель логического проектирования – развить концептуальное представление БД с учетом принимаемой модели БД (иерархической, сетевой, реляционной и т. д.).

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

1. Удаление и проверка элементов, не отвечающих принятой модели данных.

Если в концептуальной модели присутствуют связи N:M, то их следует устранить путем определения промежуточной сущности. Связь N:M заменяется двумя связями типа 1:M, устанавливаемыми со вновь созданной сущностью (рис. 7.9).

Рис. 7.9. Замена связи N:M

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

В разработанной концептуальной модели существовала связь N:M («Раздельные пункты» : «Пути»), которая имела собственные атрибуты. После устранения связь N:M, ее атрибуты перешли в сущность «Раздельные пункты на пути» (см. рис. 7.9, неключевые атрибуты).

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

1.3. Удаление сложных связей (со степенью участия более 2).

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

1.4. Удаление рекурсивных связей (со степенью участия 1).

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

Рис. 7.10. Замена рекурсивной связи

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

1.5. Удаление многозначных атрибутов (атрибутов имеющих несколько значений).

Многозначность устраняется путем введения новой сущности и связи 1:N (рис.7.11).

Рис. 7.11. Удаление многозначных атрибутов

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

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

Рис. 7.12. Избыточные связи

В приведенном примере одну из связей «Руководит» можно смело удалить (лучше между «Руководителем филиала» и «Сотрудником»).

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

В то же время не всегда можно выполнить такое объединение (рис. 7.13).

Рис. 7.13. Связи 1:1

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

2. Проверка модели с помощью правил нормализации.

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

Ниже приводятся краткие сведения из теории нормализации.

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

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

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

Процесс нормализации впервые был предложен Э.Ф. Коддом в 1972 г. Сначала было предложено три вида нормальных форм (1NF, 2NF и 3NF). Затем Р. Бойсом и Э.Ф. Коддом (1974 г.) было сформулировано более строгое определение третьей нормальной формы, которое получило название нормальная форма Бойса–Кодда (BCNF). Вслед за BCNF появились определения четвертой (4NF) и пятой (5NF или PJNF) нормальных форм (Р. Фагин, 1977 и 1979 г.). На практике нормальные формы более высоких порядков используются крайне редко. При проектировании БД, как правило, ограничиваются третьей нормальной формой, что позволяет предотвратить возможное возникновение избыточности данных и аномалии обновлений.

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

Степень неделимости (атомарности), т. е. решение о том, следует разбивать неатомарный атрибут на атомарные или оставить его псевдоатомарным, определяется проектировщиком БД исходя из конкретных условий. Если при обработке таблиц нет необходимости различать атомарные составляющие псевдоатомарного атрибута, то его можно не делить (например, атрибуты «Фамилия, имя, отчество», «Адрес» и т. д.).

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

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

Например, таблица «Оценки по экзаменам» характеризуется следующим набором атрибутов {Номер зачетной книжки, Дисциплина, Дата сдачи, ФИО студента, № группы, Оценка}. Очевидно, что первичным ключом является набор {Номер зачетной книжки, Дисциплина, Дата сдачи}. Полной функциональной зависимостью обладает только один неключевой атрибут «Оценка». Атрибуты «ФИО студента» и «№ группы» могут быть однозначно определены по части первичного ключа – «Номер зачетной книжки». Таким образом, требуется разбиение исходной таблицы на две (рис. 7.14).

Рис. 7.14. Обеспечение полной функциональной зависимости

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

Транзитивная зависимость. Если для атрибутов А, В и С некоторого отношения существуют зависимости вида А → В и В → С, то атрибут С транзитивно зависит от атрибута А через атрибут В.

Например, таблица «Работник» характеризуется набором атрибутов {Табельный номер, Фамилия, Имя, Отчество, Должность, Зарплата, …}, первичный ключ – {Табельный номер}. В этой таблице от первичного ключа («Табельный номер») зависит неключевой атрибут «Должность», а от «Должности» другой неключевой атрибут «Зарплата». Для приведения к 3NF необходимо добавить новую таблицу (рис. 7.15).

Рис. 7.15. Устранение транзитивной зависимости

BCNF. Отношение находится в BCNF, если оно находится в 3NF и каждый детерминант отношения является его возможным ключом.

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

Например, таблица «Поставка деталей» характеризуется набором атрибутов {ID поставщика, Наименование поставщика, ID детали, Количество деталей}, потенциальные ключи – {ID поставщика, ID детали} и {Наименование поставщика, ID детали}. Подразумевается, что не может быть двух поставщиков с одним наименованием. Атрибут «ID поставщика» характеризуется полной функциональной зависимостью от атрибута «Наименование поставщика» и наоборот. Чтобы устранить эту нежелательную зависимость, следует один из этих ключей принять в качестве первичного и добавить новую таблицу, куда вынести два указанных атрибута (рис. 7.16).

Рис. 7.16. Приведения отношения к BCNF

Отличие BCNF от 2NF заключается в том, что выносимый в отдельную таблицу атрибут («Наименование поставщика») входит в состав потенциального ключа, а не является простым неключевым атрибутом («ФИО студента» и «№ группы» на рис. 7.14).

4NF. Отношение находится в 4NF в том и только в том случае, если в нем отсутствуют нетривиальные многозначные зависимости.

Нетривиальная многозначная зависимость. В отношении с атрибутами А, В и С существует нетривиальная многозначная зависимость, если для каждого значения атрибута А имеется набор значений атрибута В (A −>> B) и набор значений атрибута С (A −>> С), но между атрибутами В и С нет зависимостей.

Например, таблица «Экзамены» характеризуется набором атрибутов {Номер группы, Номер зачетной книжки, Дисциплина}, первичный ключ – весь набор атрибутов. В данной таблице имеется две многозначные зависимости: номер группы определяет список студентов, которые в ней учатся (Номер группы −>> Номер зачетной книжки), и номеру группы соответствует список дисциплин учебного плана, по которым требуется сдавать экзамены (Номер группы −>> Дисциплина). В то же время между номером зачетной книжки и дисциплиной зависимость отсутствует. Для приведения таблицы к 4NF требуется разбить ее на две (рис. 7.17).

Рис. 7.17. Приведения отношения к 4NF

5NF (нормальная форма проекции соединения, PJNF). Отношение находится в 5NF, если в нем нет зависимостей соединения.

Зависимость соединения. В отношении с атрибутами А, В и С существует зависимость соединения, если для каждого значения атрибута А имеется набор значений атрибута В (A −>> B) и набор значений атрибута С (A −>> С), а также существует многозначная зависимость между атрибутами В и С (В −>> С или С −>> B).

Например, таблица «Дисциплины» характеризуется набором атрибутов {Кафедра, Преподаватель, Дисциплины}, первичный ключ – весь набор атрибутов. В данной таблице имеется несколько многозначных зависимостей: на кафедре работает несколько преподавателей (Кафедра −>> Преподаватель), кафедра ведет несколько дисциплин учебного плана (Кафедра −>> Дисциплина), преподаватель может вести несколько дисциплин (Преподаватель −>> Дисциплина) и, наоборот, одну дисциплину могут вести разные преподаватели (Дисциплина −>> Преподаватель). Для устранения зависимости соединения следует разбить исходную таблицу на три (рис. 7.18).

Рис. 7.18. Приведения отношения к 5NF

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

· изменение наименования раздельного пункта;

· выборка данных по раздельным пунктам расчетного пути участка и т. д.

4. Определение требований поддержки целостности данных.

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

· обязательные данные – атрибуты, которые всегда должны содержать одно из допустимых значений (NOT NULL). Например, поворот кривой (влево или вправо) должен быть обязательно задан. Обязательными также являются все атрибуты, входящие в первичный ключ сущности;

· домены – наборы допустимых значений для атрибута. Например, радиус кривой должен быть положительным числом не более 4 цифр или поворот кривой может принимать одно из двух допустимых значений – «Л» (влево) или «П» (вправо);

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

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

o при наличии обязательной связи вставка записи в дочернюю сущность требует обязательного заполнения атрибутов внешнего ключа, и введенному значению должна соответствовать запись родительской сущности;

o аналогичное требование выдвигается при обновлении внешнего ключа в дочерней сущности;

o удаление записи из дочерней сущности или вставка записи в родительскую не вызывают нарушения ссылочной целостности;

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

Автоматическая поддержка всех видов ограничений целостности возможна за счет использования операторов SQL.

Ссылочная целостность может быть обеспечена за счет использования триггеров. Триггер – это хранимая в БД процедура, вызываемая автоматически при выполнении удаления (DELETE), вставки (INSERT) или обновления (UPDATE) записи. Набор команд, входящих в триггер, зависит от принятой стратегии (типа триггера) поддержания целостности. Рассмотрим типы триггеров на примере удаления записи:

· RESTRICT (ограничение действия). Удаление записи из родительской таблицы запрещается, если в дочерней таблице существует хотя бы одна зависимая запись;

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

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

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

· NO CHECK или NONE или IGNORE (без проверки). При удалении записи из родительской таблицы никаких действий по сохранению ссылочной целостности данных не предпринимается.

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

1.5. Пример построения логической модели

На рис. 7.19 приведен блок «Информация об участках дороги» логической информационной модели. Данная модель соответствует третьей нормальной форме.

Рис. 7.19. Блок «Информация об участках дороги» логической информационной модели

На рис. 7.19 также показаны триггеры на действия, выполняемые как со стороны родительской сущности, так и со стороны дочерней. Триггеры показаны в следующем формате «Действие : Тип триггера». Действие может быть одного из трех типов: D (DELETE), I (INSERT) и U (UPDATE). Тип триггера обозначается: C (CASCADE) и R (RESTRICT).

Логическая модель построена с использованием ERwin 4.0.

1.6. Физическое проектирование с использованием методологии IDEF1X

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

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

1. Анализ необходимости введения контролируемой избыточности.

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

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

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

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

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

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

Даже в тех случаях, когда связь между двумя сущностями необязательная, стоит подумать об их объединении, с учетом того, что часть полей в записях не будет заполняться. Руководствоваться в таких случаях надо из тех же соображений, что и при использовании производных данных. В частности, на рис. 7.6 и 7.7 показана иерархия наследования для сущности «Сотрудник». Если эту иерархию оставить без изменений, то в БД будет четыре сущности (Сотрудник, Постоянный сотрудник, Совместитель и Консультант), согласованность которых надо будет поддерживать. При выполнении запросов по сотрудникам надо будет оперировать четырьмя таблицами. Таким образом, лучше в БД вместо иерархии запроектировать одну таблицу, в которую добавить атрибуты «Ставка» и «Организация». В атрибуте «Ставка» для постоянного сотрудника всегда будет значение «1», а атрибут «Организация» будет заполняться только для консультантов. Несмотря на избыточность, такая замена с точки зрения обработки данных и эксплуатации более выгодна.

1.2.2. Дублирование атрибутов в связях типа 1:M.

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

1.2.3. Использование служебных таблиц (справочных таблиц, классификаторов, типовых списков значений).

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

Обычно служебные таблицы содержат два атрибута: идентификатор (код, шифр) и описание (наименование). Например, в БД можно предусмотреть служебные таблицы «Вид раздельного пункта», «Род балласта» и т. д. с атрибутами {ID, Наименование}. Эти таблицы связываются неидентифицирующей обязательной связью с исходной, при этом в ней вместо наименования параметра будет содержаться идентификатор этого наименования.

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

· значительно снижается вероятность ошибки при указании значений для этих атрибутов. Если не использовать служебные таблицы, то разные пользователи могут вносить рассогласованные значения, в том числе и с ошибками. Например, «Щебеночный», «щебеночный», «Щебен.», «Шебеночный», «Щ» и т. д. Несмотря на то, что по смыслу это все один и тот же род балласта, программа будет их воспринимать как разные;

· уменьшается размер исходной таблицы. Например, если непосредственно хранить наименование рода балласта в таблице «План пути», то в каждой записи на этот атрибут будет расходоваться не менее 10 байт (длина слова «Щебеночный» 10 букв), а если использовать идентификатор, то достаточно всего одного байта. Экономия в 10 раз;

· если описание параметра может измениться, то значительно проще изменить одно значение в служебной таблице, чем корректировать множество записей в исходной. Например, если принято решение вместо полного наименования рода балласта отображать только начальную букву (Щ, П, Г и А), то изменениям подвергнется только служебная таблица, а исходные таблицы «План пути» и «ВСП на прямых участках» останутся без изменений.

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

Рис. 7.20. Использование служебных таблиц

1.2.4. Введение повторяющихся (многозначных) атрибутов.

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

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

2. Перенос логической модели данных в среду целевой СУБД.

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

· первичных, альтернативных и внешних ключей;

· неопределенных (NULL) и обязательных (NOT NULL) значений;

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

3. Реализация бизнес-правил и анализ транзакций.

Реализацию бизнес-правил (сумма длин переходных кривых не должна быть более длины всей кривой) можно включить в SQL-операторы создания таблиц (CREATE TABLE опция CHECK для полей или таблицы в целом) или в триггеры (CREATE TRIGGER).

После реализации бизнес-правил необходимо проверить выполнимость и эффективность (время отклика, скорость выборки, объем задействованных данных) выполнения всех транзакций.

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

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

4.1. Разработка пользовательских представлений (видов).

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

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

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

В СУБД, поддерживающих SQL, возможно выполнение запросов от имени определенного пользователя, которое задается администратором БД. Каждый пользователь обладает строго определенным набором прав (привилегий) в отношении конкретной таблицы или представления. Наделение правами выполняется с помощью оператора GRANT, отмена – REVOKE. Операции, на которые можно назначить права: SELECT, INSERT, DELETE и UPDATE. Кроме того, возможно задание передачи прав от одного пользователя к другому.

5. Организация мониторинга и настройка функционирования системы.

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

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

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

1.7. Пример построения физической модели

На рис. 7.20 приведен блок «Информация об участках дороги» физической информационной модели.

Рис. 7.20. Блок «Информация об участках дороги» физической информационной модели

Для ее построения использовался ERwin 4.0. В качестве целевой СУБД выбрана IBM DB2, хотя ERwin позволяет на основе логической модели перейти к физической для более чем 20 самых распространенных серверных (ORACLE, DB2, Informix, MS SQL Server и т.д.) и «настольных» (FoxPro, Access, Paradox и т. д.) СУБД. Данная модель разработана с учетом принятого в DB2 синтаксиса, а также в отличие от логической модели содержит необходимые служебные таблицы.

1. РАЗРАБОТКА ИНФОРМАЦИОННОЙ МОДЕЛИ

1.1. Основы проектирования баз данных