ER-метод логического проектирования баз данных и его реализация в среде СУБД MS Access
БЕЛКООПСОЮЗ
Учреждение образования
«Белорусский
торгово-экономический
кооперации»
Кафедра информационно-вычислительных систем
Курсовая работа
по дисциплине «Введение в системы баз данных»
на тему
«ER-метод логического проектирования баз данных и его реализация в
среде СУБД MS Access»
на примере задачи «Учет расчетов с подотчетными лицами (9-й вариант)»
Выполнил: студент 4 курса, группы С-41
специальности
«Управление информационными ресурсами»
Научный руководитель: доцент, к.т.н.
Гомель 2010
Основные задачи проектирования баз данных
Основные задачи:
- Обеспечение хранения в БД всей необходимой информации.
- Обеспечение возможности получения данных по всем необходимым запросам.
- Сокращение избыточности и дублирования данных.
- Обеспечение целостности данных (правильности их содержания): исключение противоречий в содержании данных, исключение их потери и т.д.
Основные этапы проектирования баз данных:
- Концептуальное (инфологическое) проектирование – построение
формализованной модели предметной области. Такая модель строится с использованием стандартных языковых средств, обычно графических, например ER-диаграмм. Такая модель строится без ориентации на какую-либо конкретную СУБД.
Основные элементы данной модели:
- Описание объектов предметной области и связей между ними.
- Описание информационных потребностей пользователей (описание основных запросов к БД).
- Описание алгоритмических зависимостей между данными.
- Описание ограничений целостности, т.е. требований к допустимым значениям данных и к связям между ними.
- Логическое (даталогическое) проектирование – отображение инфологической модели на модель данных, используемую в конкретной СУБД, например на реляционную модель данных. Для реляционных СУБД даталогическая модель – набор таблиц, обычно с указанием ключевых полей, связей между таблицами. Если инфологическая модель построена в виде ER-диаграмм (или других формализованных средств), то даталогическое проектирование представляет собой построение таблиц по определённым формализованным правилам, а также нормализацию этих таблиц. Этот этап может быть в значительной степени автоматизирован.
- Физическое проектирование – реализация даталогической модели средствами конкретной СУБД, а также выбор решений, связанных с физической средой хранения данных: выбор методов управления дисковой памятью, методов доступа к данным, методов сжатия данных и т.д. – эти задачи решаются в основном средствами СУБД и скрыты от разработчика БД.
- На этапе инфологического проектирования в ходе сбора информации о предметной области требуется выяснить:
- основные объекты предметной области (объекты, о которых должна храниться информация в БД), атрибуты объектов, связи между объектами, основные запросы к БД.
Модели «Сущность - Связь»
Модель «сущность-связь» (англ. “Entity-Relationship model”), или ER-модель, предложенная П. Ченом в 1976 г., является наиболее известным представителем класса семантических (концептуальных, инфологических) моделей предметной области. ER-модель обычно представляется в графической форме, с использованием оригинальной нотации П. Чена, называемой ER-диаграмма, либо с использованием других графических нотаций (Crow's Foot, Information Engineering и др.).
Основные преимущества ER-моделей:
- наглядность;
- модели позволяют проектировать базы данных с большим количеством объектов и атрибутов;
ER-модели реализованы
во многих системах
Основные элементы ER-моделей:
- объекты (сущности);
- атрибуты объектов;
- связи между объектами.
Сущность - любой объект предметной области, имеющий атрибуты.
Связь между сущностями характеризуется:
- типом связи (1:1, 1:М, М:М);
- классом принадлежности. Класс может быть обязательным и необязательным. Если каждый экземпляр сущности участвует в связи, то класс принадлежности – обязательный, иначе – необязательный.
ПОСТАНОВКА ЗАДАЧИ
Предприятия выдают наличные деньги под отчет на командировочные, операционные и хозяйственные расходы. Расходовать выданные под отчет суммы допускается лишь на те цели, на которые они выданы. Подотчетное лицо передает в бухгалтерию отчет, где документально должна быть подтверждена каждая потраченная сумма. Если выданный аванс превышает сумму потраченных средств, осуществляется возврат разницы в кассу предприятия. Однако, если аванс не покрыл все оправданные согласно приказу затраты, то подотчетному лицу доплачивается эта разница. В результате проведенного концептуального проектирования БД по учету расчетов с подотчетными лицами было установлено, что в искомой БД должны быть отражены сущности:
"ПОДОТЧЕТНЫЕ ЛИЦА" (Код подотчетного лица, ФИО) и "СПРАВОЧНИК ОПЕРАЦИЙ" (Код операции, Операция).
Кроме того, в базе данных должна быть отображена сущность "УЧЕТНАЯ ВЕДОМОСТЬ", основанная на документе "Учетная ведомость по операциям с подотчетными лицами".
Документ "Учетная ведомость по операциям с подотчетными лицами" содержит в шапочной части атрибуты: Номер ведомости, Дата ведомости, ФИО. Каждая строка содержательной (табличной) части данного документа содержит атрибуты: Номер документа, Дата документа, Операция, Сумма.
Сущности "ПОДОТЧЕТНЫЕ ЛИЦА" и "УЧЕТНАЯ ВЕДОМОСТЬ" ассоциированы связью "УПОМЯНУТЫ", а сущности "УЧЕТНАЯ ВЕДОМОСТЬ" и "СПРАВОЧНИК ОПЕРАЦИЙ" – связью "ПРОВОДИЛИСЬ".
Необходимо учесть следующие обстоятельства:
- в ведомости может быть несколько записей на одну дату с одной и той
же фамилией подотчетного лица, но с разными операциями;
- в один день может быть оформлено несколько ведомостей.
Необходимо разработать в среде СУБД Access базу данных "Расчеты с подотчетными лицами", в которой должны быть отражены сущности "ПОДОТЧЕТНЫЕ ЛИЦА", "СПРАВОЧНИК ОПЕРАЦИЙ" и "УЧЕТНАЯ ВЕДОМОСТЬ". Процесс решения задачи предполагает:
- с помощью ER-метода проектирование структуры необходимых таблиц базы данных, определение набора их атрибутов и выделение первичных ключей;
- в среде СУБД Access разработку структуры спроектированных таблиц;
- описание схемы данных;
- заполнение таблиц справочной информацией на основе следующего справочника операций:
Справочник операций содержит следующие записи:
Код операции |
Операция |
1 |
Аванс |
2 |
Расчет |
3 |
Отчет |
4 |
Возврат |
5 |
Доплата |
- заполнение таблиц оперативной информацией на основе следующих двух учетных ведомостей:
Учетная ведомость № 52 от 04.02.2002 г.Подотчетное лицо Сайков С.С.
Номер документа |
Дата документа |
Операция |
Сумма |
67 |
30.01.2002 |
аванс |
20000 |
73 |
02.02.2002 |
отчет |
19900 |
74 |
02.02.2002 |
расчет |
19900 |
101 |
04.02.2002 |
возврат |
100 |
104 |
04.02.2002 |
аванс |
40000 |
Учетная ведомость № 55 от 06.02.2002 г. Подотчетное лицо Букин Б.Б.
Номер документа |
Дата документа |
Операция |
Сумма |
69 |
30.01.2002 |
аванс |
50000 |
76 |
03.02.2002 |
отчет |
52500 |
102 |
04.02.2002 |
расчет |
52500 |
103 |
05.02.2002 |
доплата |
2500 |
6) Формирование запроса, в результате выполнения которого для заданной даты будет выдан список всех операций, проведенных с данным лицом за все дни, предшествующие заданному (включительно) с указанием операции, номера и даты документа, суммы и фамилии подотчетного лица;
7) Формирование запроса, в результате
выполнения которого для каждого подотчетного
лица будет вычислена его задолженность
(аванс-расчет- возврат+
ДИАГРАММЫ ER-ЭКЗЕМПЛЯРОВ И ER-ТИПА
Документу "Учётная ведомость по операциям с подотчётными лицами", как и всякому экономическому документу с шапочной и табличной частями, удобно поставить в соответствие две сущности Ведомость и Строка ведомости.
Сущность Ведомость имеет атрибуты: Номер ведомости, Дата ведомости. Атрибут Номер ведомости является ключом сущности Ведомость.
Сущность Строка ведомости имеет атрибуты: Код, ФИО, Номер документа, Дата документа, Операция, Сумма.
Как и для всякого экономического документа, можно считать, что между сущностями Ведомость и Строка ведомости установлена связь Объединяются. Эта связь имеет показатель кардинальности 1:n, классы принадлежности обеих сущностей являются обязательными. Таким образом, получаем диаграмму ER-экземпляров, приведенную на рисунке 1.
Ведомость Объединяются Строк
В1 СВ1
В2 СВ2
СВ3
Рисунок 1 - Диаграмма ER-экземпляров для связи Объединяются
При построении
диаграммы ER-экземпляров для связи Объед
- одна шапочная часть ведомости может объединять несколько строк документа;
- одна конкретная строка ведомости может находиться только в одном конкретном документе
- не может существовать ведомость, в которой есть шапочная часть и нет ни одной строки в содержательной части документа;
- не может существовать ведомость, в которой есть строка табличной части и отсутствует шапочная часть.
При построении
диаграммы ER-экземпляров для связи Упомя
- Одно подотчётное лицо может быть упомянуто в разных строках ведомости;
- В одной строке может быть упомянуто только одно подотчётное лицо;
- Может существовать подотчётное лицо, которое не упомянуто ни в одной строке ведомости;
- Не существует строки, в которой бы не упоминалось подотчётное лицо.
Подотчёт. лицо Упомянуты Ведомость
ПЛ1 В1
ПЛ2 В2
ПЛ3 В3
Рисунок 2 - Диаграмма ER-экземпляров для связи Упомянуты
Таким образом, связь Упомянуты имеет показатель кардинальности 1:n, класс принадлежности сущности Ведомость является обязательным, а класс принадлежности сущности Подотчетные лица – необязательный.
По условию задачи сущности Справочник Операций и ведомость ассоциированы связью Проводились. Так как мы разделили сущность ведомость на две сущности Учетная Ведомость и Строка ведомости, то необходимо уточнить, с какой из этих двух сущностей связана сущность Справочник операций. Ясно, что следует рассматривать связь Проводились между сущностями Справочник операций и Строка ведомости, т. к. код операции упоминается именно в табличной части документа. При построении диаграммы ER-экземпляров для связи Проводились необходимо исходить из того, что:
- Код операции из Справочника операций может присутствовать в нескольких строках ведомости;
- В одной строке ведомости не может быть двух операций одновременно;
- В Справочнике операций может присутствовать операция, не встречающаяся ни в одной строке;
- В каждой строке ведомости может присутствовать одна операция.
Соответствующая диаграмма приведена на рисунке 3.
Справочн. операций Проводились Строка ведомости
СО1 СВ1
СО2 СВ2
СО3 СВ3
Рисунок 3 - Диаграмма ER-экземпляров для связи Проводились
Таким образом, связь Проводились имеет показатель кардинальности 1:n, класс принадлежности сущности Строка ведомости является обязательным, а класс принадлежности сущности Справочник операций – необязательный.
Теперь мы можем построить диаграмму ER-типа для проектируемой базы данных Расчёты с подотчётными лицами, эта диаграмма приведена на рисунке 4.
n
1
n
Рисунок 4 - Диаграмма ER-типа для базы данных Расчёты с подотчётными лицами.
ГЕНЕРАЦИЯ ТАБЛИЦ БД
На основании правила 4 генерации отношений[1] связь Упоминаются порождает два отношения по одному для каждой сущности, причем ключевой атрибут КодПодотчЛ сущности ПодотчЛиц должен быть включен в число атрибутов отношения УчетнВед. После включения атрибута КодПодочтЛ наличие атрибута ФИО в отношении УчетнВед становится избыточным, т. к. значение указанного атрибута однозначно определяется значением атрибута КодПодочтЛ. Получаем следующие отношения:
- ПодотчЛиц (КодПодотчЛ, ФИО);
- УчетнВед (НомерВед, ДатаВед, КодПодотчЛ).
На основании правила
4 генерации отношений связь Пров
- СправОпер (КодОпер, Операция);
- СтрокаУчВед (НомДок, ДатаДок, КодОпер, Сумма).
На основании правила
4 генерации отношений связь Объе
- СтрокаУчВед (НомДок, ДатаДок, КодОпер, Сумма, НомерВед) Таким образом, искомая БД состоит из четырех сгенерированных таблиц.
СТРУКТУРА ТАБЛИЦ И СХЕМА ДАННЫХ БД В СРЕДЕ СУБД ACCESS
На рисунках 5 – 8 приведены структуры таблиц проектируемой БД в СУБД MS Access.
Таблицы ПодотчЛиц и СправОпер содержат нормативно-справочную информацию, а таблицы СтрокаУчВед и УчетнВед – оперативно-учетную.
На рисунке 9 представлена схема данных БД Расчет с подотчетными лицами.
Рисунок 5 – структура таблицы ПодотчЛиц в режиме конструктора
Рисунок 6 – структура таблицы СправОпер в режиме конструктора
Рисунок 7 – структура таблицы СтрокаУчВед в режиме конструктора
Рисунок 8 – структура таблицы УчетнВед в режиме конструктора
Рисунок 9. Схема данных БД Расчеты с подотчётными лицам
СОДЕРЖИМОЕ ТАБЛИЦ БД
На рисунках 10 и 11 приведены таблицы ПодотчЛиц и СправОпер, заполненные нормативно-справочной информацией.
Рисунок 10 - Заполненная таблица ПодотчЛ
Рисунок 11 - Заполненная таблица СправОпер
На рисунках 12 и 13 приведены таблицы УчетнВед и СтрокаУчВед, заполненные оперативно-учетной информацией.
Рисунок 12 - Заполненная таблица УчетнВед
Рисунок 13 - Заполненная таблица СтрокаУчВед
ЗАПРОСЫ В РЕЖИМЕ КОНСТРУКТОРА И В РЕЖИМЕ ТАБЛИЦЫ
На рисунке 14 представлен в режиме конструктора запрос СписокОперДляЗаданнойДаты, в результате выполнения которого для заданного периода будет выдан список всех операций с указанием даты, номера документа, суммы и ФИО подотчётного лица. На рисунке 15 приведен пример выполнения запроса СписокОперДляЗаданнойДаты.
Рисунок 14 - Запрос СписокОперДляЗаданнойДаты в режиме конструктора
Рисунок 15 - Пример выполнения запроса СписокОперДляЗаданнойДаты – 03.02.2002 включительно
Для создания запроса, в котором нужно посчитать сумму задолженности для каждого подотчётного лица (аванс-расчет-возврат+доплата) за весь период учёта, был создан запрос Вычисление задолженности (рисунок 16 – 17).
Рисунок 16 – Запрос Вычисление задолженности в режиме конструктора с окном построителя выражений
Рисунок 17 – Запрос Вычисление задолженности в режиме таблицы с окном вычисленных данных
ЗАКЛЮЧЕНИЕ
В ходе разработки курсовой работы были получены начальные знания о СУБД (система управления базами данных) Access, выработаны навыки по использованию ER-метода логического проектирования баз данных, их физической реализации в среде СУБД Access. При выполнении курсовой работы были решены следующие задачи:
- определен состав таблиц проектируемой реляционной базы данных (БД), их полей и первичных ключей с использованием ER-метода логического проектирования БД;
- произведено физическое проектирование БД в среде СУБД Access;
- была заполнена БД оперативно-учетной информацией и реализация требуемых функций в виде запросов.
В настоящее время необходимость интенсивного развития экономики и коренной ее перестройки требует от специалистов по управлению овладения навыками работы с БД и умения использовать для этой цели вычислительную технику.
Для этой цели небольшим фирмам отлично подходит СУБД Access. Она позволяет обеспечить удобный ввод данных в ЭВМ, произвести отбор данных по каким-либо признакам (критериям или параметрам), преобразовать структуры данных, вывод данных, являющихся результатом решения задач в табличном или каком-либо ином удобном виде.
СПИСОК ИСПОЛЬЗОВАНЫХ ИСТОЧНИКОВ
- ER-метод проектирования баз да
нных и его реализация в среде СУБД Access: Пособие для студентов экономических специальностей / Авторы-составители: С. М. Мовшович, К. Г. Сулейманов. — Гомель: УО "Белорусский торгово-экономический университет потребительской кооперации", 2003. — 140 с. - Коннолли Томас, Бегг Каролин, Страчан Анна. Базы данных: проектирование, реализация и сопровождение. Теория и практика, 2-е изд.: Пер. с англ. — М.: Издательский дом «Вильямс», 2000. — 1120 с.: ил. — Парал. тит. англ.
- Джексон Г. Проектирование реляционных баз данных для использования с микроЭВМ: Пер. с англ. — М.: Мир, 1991. — 252 с., ил.
- Дейт, К. Дж. Введение в системы баз данных, 8-е издание.: Пер. с англ. — М.: Издательский дом «Вильямс», 2005. — 1328 с.: ил. — Парал. тит. англ.
- ER-метод логического проектирования баз данных и его реализация в среде СУБД MS Access
- Esquire как журнал для интеллигентных бунтарей. Эпатаж в журналистике
- Essence and reasons of Inflation occurrence, its types
- Ethernet интерфейс
- Ethnic and geographical identity of British people
- Event-менеджмент: выявление и анализ информационных ресурсов
- Excel. Анализ фирмы оптовой торговли продуктами питания. Обеспечение безопасности
- Effectiveness and productivity of labor
- Ekologistyka w Polsce
- Electooculografia
- Electronic money
- English history
- Environmental Education
- ER-метод логического проектирования баз данных и его реализации в среде СУБД Access