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

Строка акта (ИнвНомер).

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

Строка акта (ИнвНомер, НомерАкт, КодМОЛ)

Кроме того, к сгенерированным отношениям добавляется отношение:

)Акт (НомерАкт, ДатаАкт)

На основании правила 4 генерации отношений связь Подписывает порождает два отношения по одному для каждой сущности, причем ключевой атрибут КодМОЛ сущности МОЛ должен быть включен в число атрибутов отношения Строка акта. После включения атрибута КодМОЛ наличие атрибутов ФИОМОЛ и Подразделение в отношении Строка акта становится избыточным, т. к. значения указанных атрибутов однозначно определяются значением атрибута КодМОЛ.

Кроме того, к сгенерированным отношениям добавляется отношение:

МОЛ (КодМОЛ, ФИОМОЛ, Подразделение).

4. Фрагменты окон СУБД Access

Создаем базу данных в MS Access, исходя из варианта, что нам необходимо распределить материальные ценности между материально - ответственными лицами. Файл нашей базы данных называется Материальные ценности.mdb. База данных будет содержать пять таблиц: Инвентарная группа, инвентарный объект, Cтрока акта, Акт, МОЛ.

Таблица Инвентарная группа содержит поля: Код инвентарной группы, Наименование Инвентарной Группы. В MS Access щелкаем на вкладку Таблицы, потом на строчку Создание таблиц в режиме конструктора. Появляется окно, в котором пишем наименование столбцов и указываем свойства (рисунок 6).

В таблице Инвентарная группа поле КодИнвГр делаем ключевым. Для этого ставим курсор в это поле и на панели инструментов нажимаем

Рисунок 6 - Структура таблицы Инвентарная группа в режиме конструктора значок с элементом ключа

При этом с левой стороны появится значок ключа. Далее сохраняем таблицу, на панели инструментов щелкаем по значку сохранить или Файл->Сохранить как, появляется окно, в котором указываем название таблицы и нажимаем ОК.

Таблица Инвентарный объект содержит поля: Инвентарный номер, Название инвентарного объекта, Балансовая стоимость, Код инвентарной группы; таблица Строка акта - Инвентарный номер, Номер акта, Код МОЛ; таблица Акт - Номер акта, Дата акта; таблица МОЛ- Код МОЛ, Фамилия имя отчество МОЛ, Подразделение. Далее на рисунках 7-10 представлены структуры таблиц проектируемой БД в СУБД MS Access. Таблицы Инвентарная группа, Инвентарный объект, материально-ответственные лица содержат нормативно-справочную информацию, а таблицы Акт и Строка акта - оперативно справочную.

Рисунок 7 - Структура таблицы Инвентарный объект в режиме конструктора

Рисунок 8 - Структура таблицы Строка акта в режиме конструктора

Рисунок 9 - Структура таблицы Акт в режиме конструктора

Рисунок 10 - Структура таблицы МОЛ в режиме конструктора

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

Рисунок 11 -Форма Инвентарная группа

Рисунок 12 -Форма Инвентарный объект

Рисунок 13 -Форма МОЛ

Создаем форму для таблицы Акт вместе с подчиненной формой для таблицы Строка акта.

. Вызываем мастера форм двойным щелчком по кнопке Создание формы с помощью мастера во вкладке Формы окна базы данных.

. В первом диалоговом окне Создание форм мастера форм в раскрывающемся списке Таблицы и запросы выбираем таблицу Акт. Включаем в форму все поля выбранной таблицы с помощью кнопки >>. Затем в этом же окне выбираем таблицу Строка акта и включите в форму все ее поля.

. Во втором диалоговом окне мастера форм выбираем вид представления данных - Акт и устанавливаем переключатель Подчиненные формы.

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

Доработка к курсовой работе

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

. В пятом диалоговом окне мастера форм задаем имена: для формы - Акт, для подчиненной формы - Строка акта подчиненная форма. Устанавливаем переключатель Дальнейшие действия: в положение Открыть форму для просмотра и ввода данных. Нажимаем на кнопку Готово (рисунок 14).

Рисунок 14 - Форма Акт

После создания форм мы заполняем таблицы данными. Данные вводим в созданные нами формы, которые берутся из таблиц 1 и 2. После того как заняли таблицы их содержимым, построим запросы которые указаны в задании.

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

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

На бланк переносятся из верхней части окна конструктора запросов поля из исходных таблиц (при этом автоматически заполняются строки бланка Поле и Имя таблицы), затем формируются критерии отбора записей (в строке Условие отбора). На рисунке 4.10 представлен запрос (в режиме конструктора) По дате, этот запрос по набранной дате выдает список всех инвентарных объектов, переданных в этот день, с указанием их наименования, стоимости и фамилий материально ответственных лиц.

Рисунок 15 - Запрос по дате

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

Выбираем Запросы - Создание запросов с помощью мастера

В окне Таблицы и запросы, выбрать таблицу МОЛ - поле Подразделение, таблица Инвентарный объект - ИнвНомер, НазвИнвОб, Балансовая стоимость

В следующем окне, выбираем итоговый отчет

Выбираем Итоги, ставим галачку Sum напротив балансовой стоимости и галочку в окне Подсчет записей в Инвентарном объекте

Открыть для просмотра данных

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

Выбираем этот запрос в режиме конструктора и в сортировке выбрать по убыванию.

Запрос в режиме конструктора представлен на рисунке 16.

Рисунок 16 - Запрос на обобщение

После построения запросов запускаем их на выполнение. Запуск запросов на выполнение осуществляется нажатием кнопки Запуск на панели инструментов. Результаты запросов По дате и По обобщению представлены ниже (рисунок 17 и 18).

Рисунок 17 - Результаты запроса По дате

Рисунок 18 - Результаты запроса Обобщение

5. Схема данных БД

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

Рисунок 19 - Схема данных

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

6. Содержание таблиц БД

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

Рисунок 20

Рисунок 21 - Заполнение таблиц акт и Строка акта

Рисунок 23 - Заполнение таблиц Инвентарная группа и Инвентарный объект

Рисунок 24 - Заполнение таблицы материально-ответственные лица

Заключение

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

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

Список литературы

1.      Информационные технологии: практикум: учеб. пособие / С.К. Койпыша. - Мн.: Выш. шк., 2005. - 219 с.: ил.

.        Скрытые и малоизвестные возможности Microsoft Office 2003 / Пол МакФедрис; пер. с англ. К. В. Ульяновой. - М. : НТ Пресс, 2007-608с.

.        Виктор Пасько Access 97 (русифицированная версия) - Киев, 1997 - 368с.

.        Левин А. Самоучитель полезных программ. - М., 1999-2003.

.        Шафрин Ю. Информационные технологии: В 2 ч. - М.: Лаборатория Базовых знаний, 2001.

.        Хэлворсон М., Янг М. Эффективная работа в Microsoft Office. - СПб., 1997.

.        www.intuit.ru