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

БЕЛКООПСОЮЗ

Учреждение образования

«Белорусский торгово-экономический университет потребительской

кооперации»

Кафедра информационно-вычислительных систем

Курсовая работа

по дисциплине «Введение в системы баз данных»

на тему

«ER-метод логического проектирования баз данных и его реализация в

среде СУБД MS Access»

на примере задачи «Учет нематериальных активов (4-й вариант)»

Гомель 2011

ВВЕДЕНИЕ

              Основные этапы проектирования базы данных

              Создание базы данных (БД) в среде системы управления базами данных Access (СУБД Access) предполагает выполнение следующих основных этапов:

1.      концептуальное проектирование;

2.      логическое проектирование;

3.      физическое проектирование;

4.      использование БД – заполнение БД оперативной информацией и формирование запросов и отчетов.

              Концептуальное проектирование – процедура конструирования информационной модели предприятия, не зависящей от условий реализации БД. Таким образом, сконструированная на данном этапе информационная модель не зависит ни от СУБД, ни от средств вычислительной техники.

              Концептуальное проектирование БД выполняется на основе:

      анализа информационных потоков организации;

      использования классификаторов и систем кодирования;

      определения диапазона действия и области применения БД;

      выяснения состава ее пользователей;

      сбора и анализа требований пользователей.

              В настоящем пособии не рассматривается методика проведения концептуального               проектирования. Мы будем считать его выполненным и, таким образом, предполагается, что к моменту начала логического проектирования БД сконструирована информационная модель рассматриваемой предметной области. На этапе логического проектирования информационная модель предприятия уточняется с учетом типа создаваемой БД – реляционной, сетевой или иерархической. В настоящее время реляционные модели БД практически повсеместно вытеснили все другие типы моделей. В СУБД Access реализована именно реляционная БД.

              Процесс физического проектирования БД предполагает выполнение в среде выбранной СУБД следующих работ:

1.      описание логической структуры каждой таблицы;

2.      описание связей между таблицами, входящими в одну БД;

3.      первоначальное заполнение справочников БД необходимой нормативно-справочной информацией.

              Подчеркнем, что концептуальное проектирование БД не связано с какой-либо конкретной СУБД, а этап логического проектирования зависит только от типа СУБД – сетевая, иерархическая или реляционная. Однако способ представления результатов концептуального проектирования зависит от используемого метода логического проектирования. Используемые ниже термины – отношение и атрибут – относятся к реляционной СУБД, каковой и является СУБД Access.

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

              Концепция ER-метода логического проектирования

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

1.      выделены все сущности, информация о которых должна содержаться в искомой БД;

2.      определены основные атрибуты для каждой сущности;

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

4.      сформулированы связи между выделенными сущностями;

5.      выявлены условия применения выделенных сущностей на данном предприятии.

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

              В качестве определения ER-метода можно принять следующее:

      основу ER-метода составляют понятия: сущность, связь и атрибуты;

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

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

              Основные понятия

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

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

              Атрибут есть свойство сущности.

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

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

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

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

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

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

              ER-диаграммы

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

              Сущность Продукт характеризуется такими атрибутами как Номер продукта (НП), Наименование продукта (НАИМ), Единица измерения (ЕИ), Упаковка (УП) и др. Для дальнейшего рассмотрения важно лишь то, что атрибут НП является ключом сущности Продукт. Это означает, что значение атрибута НП однозначно определяет конкретный продукт, т.е экземпляр сущности Продукт. Для определенности будем считать, что НП принимает следующие значения: П1, П2, П3 и т.д.

              Сущность Склад обладает следующими атрибутами: Номер склада(НС), Емкость склада (ЕС), Материально ответственное лицо (МОЛ) и т.д. Ключом сущности является атрибут НС. Будем считать, что НС принимает значения С1, С2, С3 и т. д.

              Сущности Продукт и Склад соотносятся с помощью связи Хранится. Эта связь может быть графически представлена в виде диаграммы ER-экземпляров и диаграммы ER-типа.

                                          ПРОДУКТ                                ХРАНИТСЯ                            СКЛАД

                                                 П1                                                                                          С1

                                                 П2                                                                                          С2

                                                 П3                                                                                          С3

                                                 П4                                                                                          С3

Рис. 1 Пример диаграммы ER-экземпляров

                            НП….                                                                                                          НС….

Рис. 2 Пример диаграммы ER-типа

              В большинстве случаев для определения набора отношений проектируемой БД используются диаграммы ER-типа, а не диаграммы экземпляров.

              Характеристики связи

              Важной характеристикой связи между двумя (и более) сущностями является степень связи.

              Степень связи указывает на количество сущностей, охваченных данной связью. Связь Хранится, существующая между сущностями Продукт и Склад, называется бинарной, поскольку она связывает только две сущности. Связи более высокого порядка, существующие между n сущностями, называются n-сторонними (n-арными). Бинарные связи встречаются наиболее часто и во всех примерах настоящего пособия рассматриваются только бинарные связи.

              Показатель кардинальности описывает количество возможных связей для каждого из экземпляров рассматриваемых сущностей.

              Наиболее распространенными являются бинарные связи с показателями кардинальности "один к одному" (1:1), "один ко многим" (1:n) или "многие к одному" (n:1), "многие ко многим" (m:n).

              Например, если для характеристики студентов вуза выделены две сущности Анкетные данные студентов и Успеваемость студентов(в каждой из этих сущностей ключевым атрибутом является шифр студента), то для связи Учится между экземплярами этих сущностей показатель кардинальности будет 1:1, т. е. каждый студент должен быть в точности один раз охарактеризован в каждой из этих сущностей.

              Если рассмотреть две сущности Академическая группа (ключ – шифр группы) и Факультет (ключ – шифр факультета), то показатель кардинальности связи Принадлежит между экземплярами этих сущностей равен n:1, т.е. каждая группа обязательно принадлежит какому-то одному факультету, но в каждом факультете есть несколько групп. Заметим, что между сущностями Факультет и Академическая группа показатель кардинальности связи равен 1:n.

              Для связи Преподает (Читает) между сущностями Преподаватель и Учебный курс (Дисциплина) показатель кардинальности будет m:n, т.к. каждый преподаватель читает несколько курсов и каждый курс может читаться разными преподавателями (для разных потоков).

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

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

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

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

              Если экземпляры данной сущности могут не участвовать в некоторой связи, то участие в этой связи называется необязательным, т.е. класс принадлежности данной сущности для этой связи является необязательным. В этом случае на диаграмме ER-типа жирная точка вообще не помещается на диаграмме.

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

              Класс принадлежности сущности удобно определять по диаграмме ER-экземпляров: если может существовать точка (экземпляр сущности), из которой не выходит ни одна линия связи, то класс принадлежности данной сущности является необязательным.

              Правила генерации отношений по диаграммам ER-типа

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

              ПРАВИЛО 2. Если показатель кардинальности бинарной связи равен 1:1 и класс принадлежности одной сущности является обязательным, а другой — необязательным, то необходимо построение двух отношений. Под каждую сущность необходимо выделение одного отношения, при этом ключ сущности должен служить первичным ключом для соответствующего отношения. Кроме того, ключ сущности, для которой класс принадлежности является необязательным, добавляется в качестве атрибута в отношение, выделенное для сущности с обязательным классом принадлежности.

              ПРАВИЛО 3. Если показатель кардинальности бинарной связи равен 1:1 и класс принадлежности ни одной сущности не является обязательным, то необходимо использовать три отношения: по одному для каждой сущности, ключи которых служат в качестве первичных в соответствующих отношениях, и одного для связи. Среди своих атрибутов отношение, выделяемое связи, будет иметь по одному ключу сущности от каждой сущности.

              ПРАВИЛО 4. Если показатель кардинальности бинарной связи равен 1:n и класс принадлежности n-связной сущности является обязательным, то достаточным является использование               двух отношений, по одному на каждую сущность, при условии, что ключ сущности каждой сущности служит в качестве первичного ключа для соответствующего отношения. Дополнительно              ключ 1-связной сущности должен быть добавлен как атрибут в отношение, отводимое n-связной сущности.

              ПРАВИЛО 5. Если показатель кардинальности бинарной связи равен 1:n и класс принадлежности n-связной сущности является необязательным, то необходимо формирование трех отношений: по одному для каждой сущности, причем ключ каждой сущности служит первичным ключом соответствующего отношения, и одного отношения для связи. Связь должна иметь среди своих атрибутов ключ сущности от каждой сущности.

              ПРАВИЛО 6. Если показатель кардинальности бинарной связи равен m:n, то для хранения данных необходимо три отношения: по одному для каждой сущности, причем ключ каждой сущности используется в качестве первичного ключа соответствующего отношения, и одного отношения для связи. Последнее отношение должно иметь в числе своих атрибутов ключ сущности каждой сущности.

              На практике при проектировании баз данных для экономико-социальной области применения наиболее часто используется правило 4, т. е. сущности, отображаемые в БД, ассоциированы между собой связью с показателем кардинальности 1:n, причем n-связная сущность имеет обязательный класс принадлежности.

              Особенности ER-метода для экономических приложений

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

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

              Сущность, представляющую собой документ с заголовочной и табличной частями, необходимо представить в виде двух сущностей Заголовок документа и Строка документа. Между этими сущностями устанавливается бинарная связь, которую можно назвать, например, Объединяются. Показатель кардинальности этой связи равен 1:n и n-связная сущность Строка документа имеет обязательный класс принадлежности. Поэтому для этой пары сущностей вместе с их связью должны генерироваться два отношения (по правилу 4) — по одному на каждую сущность. При этом ключевой атрибут каждой сущности будет первичным ключом для соответствующего отношения. Дополнительно ключ 1-связной сущности Заголовок документа должен быть добавлен как атрибут в отношение, отводимое n-связной сущности Строка документа.

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

              Методика применения ER-метода

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

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

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

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

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

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

              ER-метод логического проектирования предполагает выполнение следующих работ:

1.     если в результате концептуального проектирования БД были выделены документы-сущности, то необходимо каждую из них представить в виде двух сущностей Заголовок документа и Строка документа;

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

3.     определение класса принадлежности каждой сущности в каждой связи;

4.     построение диаграммы ER-типа;

5.     на основании правил 1-6 определение всех отношений БД, их атрибутов и первичных ключей.

              Отметим роль диаграмм ER-типа и ER-экземпляров при проектировании базы данных.

              Построение диаграммы ER-типа при проектировании БД дает возможность выбрать правило генерации отношений.

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

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

     сколько линий (одна или больше) может исходить из точки в левой части диаграммы?

     сколько линий (одна или больше) может входить в точку правой части диаграммы?

     может ли в левой части диаграммы быть точка, из которой не выходит ни одна линия?

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

              При этом действуют следующие правила:

     если точка в левой части диаграммы может быть соединена только с одной точкой в правой части, то связь будет иметь показатель …:1;

     если точка в левой части диаграммы может быть соединена с несколькими точками в правой части, то связь будет иметь показатель …:n;

     если точка в правой части диаграммы может быть соединена только с одной точкой в левой части, то связь будет иметь показатель 1:…;

     если точка в правой части диаграммы может быть соединена с несколькими точками в левой части, то связь будет иметь показатель n: …;

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

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

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

К нематериальным активам относят приобретенные предприятием за плату патенты, технологии, права на использование земельных участков, авторские права, программное обеспечение ЭВМ и др.

В результате проведенного концептуального проектирования БД по учету нематериальных активов было установлено, что в искомой БД должны быть отражены сущности: классификатор «ВИДЫ НЕМАТЕРИАЛЬНЫХ АКТИВОВ» (КодВидаНА, НаимВидаНА) и справочники «НЕМАТЕРИАЛЬНЫЕ АКТИВЫ (Инвентарный номер, Название нематериальных активов, Балансовая стоимость) и «МАТЕРИАЛЬНО-ОТВЕТСТВЕННЫЕ ЛИЦА» (ШифрМОЛ, ФИОМОЛ). Кроме того, в базе данных должна быть отображена сущность «УЧЕТНАЯ КАРТА», которая основана на документе «Учетная карта нематериальных активов».

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

Между сущностями «НЕМАТЕРИАЛЬНЫЕ АКТИВЫ» и «ВИДЫ НЕМАТЕРИАЛЬНЫХ АКТИВОВ» установлена связь «ПРИНАДЛЕЖАТ», между сущностями «НЕМАТЕРИАЛЬНЫЕ АКТИВЫ» и «УЧЕТНАЯ КАРТА» установлена связь «УЧИТЫВАЮТСЯ», а между сущностями «МОЛ» и «УЧЕТНАЯ КАРТА» - связь «УПОМИНАЮТСЯ».

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

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

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

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

      в один день может быть составлено несколько учетных карт;

      в одной карте может быть упомянуто только одно МОЛ.

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

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

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

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

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

4)     заполнение таблиц информацией на основе следующих двух учетных карт:

Учетная карта нематериальных активов № НА45 от 26.02.2002 г.

МОЛ   Лавров С.С.