Материал: ER-метод логического проектирования баз данных и его реализация в среде СУБД MS Access

ER-метод логического проектирования баз данных и его реализация в среде СУБД MS Access

Введение

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

При определении типа сущности необходимо гарантировать, что каждый экземпляр сущности может быть отличим от любого другого экземпляра той же сущности. Связь - это графически изображаемая ассоциация, устанавливаемая между двумя типами сущностей. Как и сущность, связь - это типовое понятие, все экземпляры обоих связываемых типов сущностей подчиняются устанавливаемым правилам связывания. Связь представляет собой соединение между двумя или более сущностями. При поиске связей в основном следует полагаться на то обстоятельство, что связь обычно выражается глаголом. Типичными примерами связей между двумя сущностями являются: служащие Работают в отделах, студенты Изучают учебные предметы, рабочие Обслуживают механизмы (или механизмы Обслуживаются рабочими).

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

В качестве исходных данных для выполнения логического проектирования БД этап концептуального проектирования предоставляет свои результаты:

набор сущностей;

предварительный перечень атрибутов для каждой сущности (основные атрибуты);

ключевой атрибут для каждой сущности;

набор связей между сущностями;

описание условий применения объектов (сущностей) на данном предприятии.

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

1. Постановка задачи

база данные моделирование таблица

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

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

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

Сущность Материально-ответственные лица имеет атрибуты: Код МОЛ (ключевой атрибут), ФИО МОЛ, Подразделение предприятия.

Сущность Инвентарный объект имеет атрибуты: Инвентарный номер (ключевой атрибут), Название инвентарного объекта, Балансовая стоимость.

Документ "Акт передачи материальных ценностей" содержит в шапочной части атрибуты: Номер акта, Дата акта. Каждая строка содержательной (табличной) части данного документа содержит атрибуты: ФИОМОЛ, Подразделение предприятия, Инвентарный номер, Название инвентарного объекта, Наименование инвентарной группы, Балансовая стоимость.

Сущности Инвентарный объект и Инвентарная группа ассоциированы связью Принадлежит, сущности Акт и Инвентарный объект ассоциированы связью Упоминается, а сущности Акт и Материально-ответственные лица - связью Подписывает.

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

номера актов не повторяются на протяжении всего периода учета;

каждый инвентарный объект идентифицируется уникальным инвентарным номером;

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

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

все объекты одного акта принимаются одним материально-ответственным лицом (МОЛ);

в один день могут быть оформлены несколько актов.

Необходимо разработать в среде СУБД Access базу данных Материальные ценности, в которой должны быть отражены сущности Инвентарная группа, Инвентарный объект и Материально-ответственные лица, содержащие нормативно-справочную информацию, и сущность Акт, основанную на документе "Акт передачи материальных ценностей".

Процесс решения задачи предполагает:

с помощью ER-метода проектирование структуры необходимых таблиц базы данных, определение набора их атрибутов и выделение первичных ключей;

в среде СУБД Access разработку структуры спроектированных таблиц;

описание схемы данных;

заполнение таблиц информацией на основе двух актов (таблицы 1 и 2);

Таблица 1 - Акт № 175 от 12.02.2002 г. передачи материальных ценностей

Таблица 2 - Акт № 199 от 22.02.2002 г. передачи материальных ценностей

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

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

2. Диаграммы er-экземпляров и er-типа

Документу "Акт передачи материальных ценностей", как и всякому экономическому документу с шапочной и табличной частями, удобно поставить в соответствие две сущности Акт и Строка акта.

Сущность Акт имеет атрибуты, соответствующие реквизитам шапочной части документа: Номер акта, Дата акта. Атрибут Номер акта является ключом сущности Акт.

Сущность Строка акта имеет атрибуты Инвентарный номер, Название инвентарного объекта, Наименование инвентарной группы, ФИОМОЛ, Подразделение предприятия. Балансовая стоимость.

Рисунок 1 - Диаграмма ER-экземпляров для связи Объединяются

Как и для всякого экономического документа, можно считать, что между сущностями Акт и Строка акта установлена связь Объединяются. Эта связь имеет показатель кардинальности 1:n, классы принадлежности обеих сущностей являются обязательными. Таким образом, получаем диаграмму ER-экземпляров, приведенную на рисунке 1.

При построении диаграммы ER-экземпляров для связи Объединяются мы исходили из того, что:

одна шапочная часть акта может объединять несколько строк документа;

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

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

не может существовать акта, в котором есть строка табличной части и отсутствует шапочная часть.

При построении диаграммы ER-экземпляров для связи Принадлежит надо исходить из того, что:

одной инвентарной группе могут принадлежать несколько инвентарных объектов;

один и тот же инвентарный объект может принадлежать только одной инвентарной группе;

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

не может существовать инвентарного объекта, который не принадлежит ни одной инвентарной группе.

Рисунок 2 - Диаграмма ER-экземпляров для связи Принадлежит

Таким образом, связь Принадлежит имеет показатель кардинальности n:1, класс принадлежности сущности Инвентарный объект является обязательным, а класс принадлежности сущности Инвентарная группа - необязательный.

Соответствующая диаграмма приведена на рисунке 2.

По условию задачи сущности Акт и Инвентарный объект ассоциированы связью Упоминается. Так как мы разделили сущность Акт на две сущности Акт и Строка акта, то необходимо уточнить, с какой из этих двух сущностей связана сущность Инвентарный объект. Ясно, что следует рассматривать связь Упоминается между сущностями Инвентарный объект и Строка акта, т. к. инвентарный объект упоминается именно в табличной части документа. При построении диаграммы ER-экземпляров для связи Упоминается необходимо исходить из того, что:

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

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

не может существовать строки акта, в которой не упоминается инвентарный объект;

может существовать инвентарный объект, который не упоминается ни в одной строке ни одного акта.

Соответствующая диаграмма приведена на рисунке 3.

Рисунок 3 - Диаграмма ER-экземпляров для связи Упоминается

Таким образом, связь Упоминается имеет показатель кардинальности n:1, класс принадлежности сущности Строка акта является обязательным, а класс принадлежности сущности Инвентарный объект - необязательный.

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

Рисунок 4 - Диаграмма ER-экземпляров для связи Подписывает

Соответствующая диаграмма приведена на рисунке 4.

При построении диаграммы ER-экземпляров для связи Подписывает мы исходили из того, что:

одно материально-ответственное лицо может подписать несколько актов;

один акт может подписать только одно материально-ответственное лицо;

не может существовать акта, который не подписан материально-ответственным лицом;

может существовать материально-ответственное лицо, которое не подписало ни один акт.

Таким образом, связь Подписывает имеет показатель кардинальности 1:n, класс принадлежности сущности Строка Акта является обязательным, а класс принадлежности сущности Материально-ответственные лица - необязательный.

Теперь мы можем построить диаграмму ER-типа для проектируемой базы данных Материальные ценности, эта диаграмма приведена на рисунке 5.

Рисунок 5 - Диаграмма ER-типа для базы данных Материальные ценности

3. Список таблиц БД

На основании правила 4 генерации отношений связь Принадлежит порождает два отношения по одному для каждой сущности, причем ключевой атрибут КодИнвГр сущности Инвентарная группа должен быть включен в число атрибутов отношения Инвентарный объект. Получаем следующие отношения:

Инвентарная группа (КодИнвГр, НаимИнвГр);

Инвентарный объект (ИнвНомер, НазвИнвОб, БалансСтоим, КодИнвГр).

На основании правила 4 генерации отношений связь Упоминается порождает два отношения по одному для каждой сущности, причем ключевой атрибут ИнвНомер сущности Инвентарный объект должен быть включен в число атрибутов отношения Строка акта. После включения атрибута ИнвНомер наличие атрибутов Название инвентарного объекта, Наименование инвентарной группы и Балансовая стоимость в отношении Строка акта становится избыточным, т. к. значения указанных атрибутов однозначно определяются значением атрибута ИнвНомер. Таким образом, в дополнение к уже сгенерированным двум отношениям получаем отношение: