Пример разработки простой ER-модели

При разработке ER-моделей мы должны получить следующую информацию о предметной области:

Список сущностей предметной области.

Список атрибутов сущностей.

Описание взаимосвязей между сущностями.

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

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

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

Хранить информацию о покупателях.

Печатать накладные на отпущенные товары.

Следить за наличием товаров на складе.

Выделим все существительные в этих предложениях - это будут потенциальные кандидаты на сущности и атрибуты, и проанализируем их (непонятные термины будем выделять знаком вопроса):

Покупатель - явный кандидат на сущность.

Накладная - явный кандидат на сущность.

Товар - явный кандидат на сущность

(?)Склад - а вообще, сколько складов имеет фирма? Если несколько, то это будет кандидатом на новую сущность.

(?)Наличие товара – это, скорее всего, атрибут, но атрибут какой сущности?

Сразу возникает очевидная связь между сущностями – «покупатели могут покупать много товаров» и «товары могут продаваться многим покупателям». Первый вариант диаграммы выглядит так (рис.5.7).

Рисунок 5.7 Первый вариант диаграммы

Задав дополнительные вопросы менеджеру, мы выяснили, что фирма имеет несколько складов. Причем, каждый товар может храниться на нескольких складах и быть проданным с любого склада. Куда поместить сущности «Накладная» и «Склад» и с чем их связать? Как связаны эти сущности между собой и с сущностями «Покупатель» и «Товар»? Пока известно, что:

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

Каждый покупатель может получить несколько накладных.

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

Каждая накладная обязана содержать несколько товаров (не бывает пустых накладных).

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

Каждая накладная должна быть выписана с определенного склада, и с любого склада может быть выписано много накладных.

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

Рисунок 5.8 Второй вариант диаграммы

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

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

Каждый товар имеет наименование, цену, а также характеризуется единицами измерения.

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

Каждый склад имеет свое наименование.

Снова выпишем все существительные, которые будут потенциальными атрибутами, и проанализируем их:

юридическое лицо - термин ненужный, т.к.фирма не работает с физическими лицами. Не обращаем внимания;

наименование покупателя - явная характеристика покупателя;

адрес - явная характеристика покупателя;

банковские реквизиты - явная характеристика покупателя;

наименование товара - явная характеристика товара;

(?)цена товара - похоже, что это характеристика товара. Отличается ли эта характеристика от цены в накладной?

единица измерения - явная характеристика товара;

номер накладной - явная уникальная характеристика накладной;

дата накладной - явная характеристика накладной;

(?)список товаров в накладной - список не может быть атрибутом. Вероятно, нужно выделить этот список в отдельную сущность;

(?)количество товара в накладной - это явная характеристика, но характеристика чего? Это характеристика не просто «товара», а «товара в накладной»;

(?)цена товара в накладной - опять же это должна быть не просто характеристика товара, а характеристика товара в накладной. Но цена товара уже встречалась выше - это одно и то же?

сумма накладной - явная характеристика накладной. Эта характеристика не является независимой. Сумма накладной равна сумме стоимостей всех товаров, входящих в накладную;

наименование склада - явная характеристика склада.

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

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

каждая накладная обязана иметь несколько записей из списка товаров в накладной;

каждая запись из списка товаров в накладной обязана включаться ровно в одну накладную;

каждый товар может включаться в несколько записей из списка товаров в накладной;

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

Атрибуты «Количество товара в накладной» и «Цена товара в накладной» являются атрибутами сущности «Список товаров в накладной».

Точно также поступим со связью, соединяющей сущности «Склад» и «Товар». Введем дополнительную сущность «Товар на складе». Атрибутом этой сущности будет «Количество товара на складе». Таким образом, товар будет числиться на любом складе и количество его на каждом складе будет свое.

Теперь можно внести все это в диаграмму «Оптовая фирма» (рис. 5.9).

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

Рисунок 5.9 Диаграмма «Оптовая фирма»

· Практические рекомендации по проектированию БД

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

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

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

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

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

Основная цель проектирования БД – это сокращение избыточности хранимых данных, а следовательно, экономия объема используемой памяти, уменьшение затрат на многократные операции обновления избыточных копий и устранение возможности возникновения противоречий из-за хранения в разных местах сведений об одном и том же объекте. Так называемый, «чистый» проект БД («Каждый факт в одном месте») можно создать, используя методологию нормализации отношений. Использование строгой методологии нормализации часто заменяется применением практических методик и правил. В [5] предлагаются 4 фундаментальных правила для проектирования БД с рациональной структурой:

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

В таблице должны храниться сведения лишь об одном типе объектов. Например, если в таблице КНИГИ содержатся сведения о самих книгах (индекс_книги, название) и издательствах (название, адрес), то это данные о двух типах объектов. Такое хранение создает проблемы: если адрес издательства изменился, то коррективы придется вносить во все записи о книгах, изданных данным издательством. Для нормализации следует разбить таблицу КНИГИ на две: КНИГИ и ИЗДАТЕЛЬСТВА.

В таблице не должно быть списков значений для некоторой единицы информации. Допустим, требуется учитывать названия и авторов книг в таблице КНИГИ. У книги может быть несколько авторов. Можно, конечно, хранить список всех авторов в одном столбце, но это затруднит обработку и поиск по отдельному автору. Другое решение – изменить структуру таблицы и добавить еще один или два столбца Автор1, Автор2. А если авторов три или больше? Если необходимо хранить список значений, следует предусмотреть размещение дублирующих данных в другой таблице, связанной с главной. Для нашего примера получаем таблицы: КНИГИ(индекс_книги, название), АВТОРЫ(код_автора, ФИО), КНИГИ_АВТОРЫ(индекс_книги, код_автора). Такая структура позволяет описать книгу с любым числом авторов без изменеия определения таблицы и не допускает наличия пустых мест при сохранении книги с одним автором.

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

Фундаментальные правила можно дополнить практическими рекомендации, которые рекомендует Б. Послед [9]:

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

2. Нужно рационально распределить информацию по таблицам. Обычно вся информация хранится в таблицах двух типов:

a. базовые, содержащие записи об основных объектах и событиях ПО (накладные, счета и т.д.)

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

3. Связать все базовые таблицы со справочниками.

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

· Вопросы и упражнения для самоконтроля к главе 5

Что является исходной информацией на первом шаге процесса проектирования классическим методом?

Какие нормальные формы вы знаете?

Приведение к какой нормальной форме считается достаточным для завершения процесса нормализации?

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

В чем вы видите недостатки процесса проектирования с использованием принципов нормализации?

Каково практическое применение семантических моделей?