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-модели реализованы  во многих системах автоматизированного  проектирования баз данных (например, Microsoft Access).

 

Основные элементы ER-моделей:

• объекты (сущности);
• атрибуты объектов;
• связи между объектами.

Сущность - любой объект предметной области, имеющий атрибуты.

 

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

• типом связи (1:1, 1:М, М:М);
• классом принадлежности. Класс может быть обязательным и необязательным. Если каждый экземпляр сущности участвует в связи, то класс принадлежности – обязательный, иначе – необязательный.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

ПОСТАНОВКА ЗАДАЧИ

 

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

"ПОДОТЧЕТНЫЕ  ЛИЦА" (Код подотчетного лица, ФИО) и "СПРАВОЧНИК ОПЕРАЦИЙ" (Код операции, Операция).

Кроме того, в  базе данных должна быть отображена сущность "УЧЕТНАЯ ВЕДОМОСТЬ", основанная на документе "Учетная ведомость по операциям с подотчетными лицами".

Документ "Учетная  ведомость по операциям с подотчетными лицами" содержит в шапочной части  атрибуты: Номер ведомости, Дата ведомости, ФИО. Каждая строка содержательной (табличной) части данного документа содержит атрибуты: Номер документа, Дата документа, Операция, Сумма.

Сущности "ПОДОТЧЕТНЫЕ ЛИЦА" и "УЧЕТНАЯ ВЕДОМОСТЬ" ассоциированы связью "УПОМЯНУТЫ", а сущности "УЧЕТНАЯ ВЕДОМОСТЬ" и "СПРАВОЧНИК ОПЕРАЦИЙ" – связью "ПРОВОДИЛИСЬ".

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

- в ведомости может быть несколько записей на одну дату с одной и той

   же фамилией подотчетного лица, но с разными операциями;

- в один день может быть оформлено несколько ведомостей.

Необходимо  разработать в среде СУБД Access базу данных "Расчеты с подотчетными лицами", в которой должны быть отражены сущности "ПОДОТЧЕТНЫЕ ЛИЦА", "СПРАВОЧНИК ОПЕРАЦИЙ" и "УЧЕТНАЯ ВЕДОМОСТЬ". Процесс решения задачи предполагает:

1. с помощью ER-метода проектирование структуры необходимых таблиц базы данных, определение набора их атрибутов и выделение первичных ключей;
2. в среде СУБД Access разработку структуры спроектированных таблиц;
3. описание схемы данных;
4. заполнение таблиц справочной информацией на основе следующего справочника операций:

Справочник  операций содержит следующие записи:

Код операции	Операция
1	Аванс
2	Расчет
3	Отчет
4	Возврат
5	Доплата

 

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-экземпляров для связи Объединяются мы исходили из того, что:

1. одна шапочная часть ведомости может объединять несколько строк документа;
2. одна конкретная строка ведомости может находиться только в одном конкретном документе
3. не может существовать ведомость, в которой есть шапочная часть и нет ни одной строки в содержательной части документа;
4. не может существовать ведомость, в которой есть строка табличной части и отсутствует шапочная часть.

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

1. Одно подотчётное лицо может быть упомянуто в разных строках ведомости;
2. В одной строке может быть упомянуто только одно подотчётное лицо;
3. Может существовать подотчётное лицо, которое не упомянуто ни в одной строке ведомости;
4. Не существует строки, в которой бы не упоминалось подотчётное лицо.

 

               

 

Подотчёт. лицо          Упомянуты               Ведомость

 

       ПЛ1        В1

 

       ПЛ2        В2

 

       ПЛ3                 В3

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

 

Таким образом, связь Упомянуты имеет показатель кардинальности 1:n, класс принадлежности сущности Ведомость является обязательным, а класс принадлежности сущности Подотчетные лица – необязательный.

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

1. Код операции из Справочника операций может присутствовать в нескольких строках ведомости;
2. В одной строке ведомости не может быть двух операций одновременно;
3. В Справочнике операций может присутствовать операция, не встречающаяся ни в одной строке;
4. В каждой строке ведомости может присутствовать одна операция.

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

 

                Справочн. операций          Проводились        Строка ведомости

 

       СО1        СВ1

 

       СО2        СВ2

 

       СО3                 СВ3

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

 

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

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