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

Министерство образования и науки Украины

Севастопольский национальный технический университет

Кафедра технической кибернетики

КОНТРОЛЬНАЯ РАБОТА

По дисциплине "Информационное обеспечение систем управления "

Выполнил: ст.гр. А-51з

Брусинов С. Э.

Проверил :

Шушляпин Е.А.

Севастополь

2008

1
Цель работы

Получить навыки разработки связных таблиц и запросов к ним на основе ER – подхода.

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

Задача планирования выпуска изделий. меется три вида ресурсов в количествах 100, 100, 200 и два вида изделий. Заданы нормы расхода Aij i-го ресурса на единицу j-го изделия - А11=2, А12=3, А21=1, А22=3, А31=3, А32=8. Эффективность выпуска единицы j-го изделия характеризуется коэффициентами 30, 60 соответственно для первого и второго изделий. Определить количества деталей каждого типа, при которых суммарный показатель эффективности, а количество деталей второго типа не больше 13.

Задание:

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

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

На основе правил ER-подхода определить связные таблицы и внести внешние ключи.

На языке SQL записать один запрос к базе данных с полученной схемой.

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

3 ПРЕДМЕТНАЯ ОБЛАСТЬ

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

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

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

4 ОПРЕДЕЛЕНИЕ СУЩНОСТЕЙ ПРЕДМЕТНОЙ ОБЛАСТИ И ИХ ХАРАКТЕРИСТИК

Проанализировав постановку задачи, можно выделить такие две сущности: «изделие»
и «ресурсы»
, идущий на изготовление. «Ресурсы
» в качестве информационных атрибутов имеет только один – атрибут «количество ресурса
». Ключом сущности является «вид ресурса
», поскольку в таблице «Ресурсы
» не может быть две записи с одинаковыми значениями этого атрибута.

Сущность «Изделие
» имеет атрибуты «вид изделия
», «количество изделий
». Ключом сущности является «вид изделий
» по той же причине, что и для сущности «Детали
».

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

5 ОПРЕДЕЛЕНИЕ СВЯЗЕЙ МЕЖДУ СУЩНОСТЯМИ И ИХ ХАРАКТЕРИСТИК

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

Так как для производства одного вида изделия может расходоваться несколько видов ресурсов, а каждый ресурс, в свою очередь, может быть израсходован на производство нескольких видов изделий, то в данном случае имеет место связь n:m, то есть «много ко многим». Далее необходимо определить классы принадлежности введенных сущностей, для чего рассматриваются ситуации, когда какой-то из видов ресурса не участвует в производстве какого-либо вида изделия либо когда какой-то из видов изделия не требует при производстве какой-либо из видов ресурсов.

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

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

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

На основе данных о прибыли от продажи единицы изделия симплекс-методом, рассмотренным в курсе ИО, получим оптимальный ассортимент изделий.

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

На диаграмме ER-экземпляров слева кружками показывают экземпляры одной сущности, справа – экземпляры другой сущности, а кружки соединяют между собой так, чтобы они отражали имеющие место связи между экземплярами сущности. Далее по построенной диаграмме определяются характеристики связей: если от одного кружка отходит много линий, то связь имеет степень «много»; если существует кружок, не связанный ни с чем другим, то класс принадлежности необязательный. По этому принципу проверяются и правая, и левая сущности. Далее с учетом полученных выводов, рассматривают то, что может произойти в перспективе, и принимают окончательное решение.

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

Рисунок 5.
2 – Диаграмма ER-экземпляров для проектируемой БД

Анализ связей на рисунок 5.5.2 показывает, что диаграмма не противоречит предположениям о степени связи «много ко многим». С учетом же перспектив, о которых шла речь выше, и слева и справа могут быть кружки, не связанные с кружками на противоположной стороне.

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

Диаграмма ER-типа отображает сущности и связи с их характеристиками и изображается несколькими способами. На рисунке 5.3 приведен один из способов отображения диаграммы ER-типа для проектируемой БД.

Рисунок 5.3 – ДиаграммаER – типа для проектируемой БД

6 ФОРМИРОВАНИЕ СХЕМЫ ПРОЕКТИРУЕМОЙ БД

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

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

Основной задачей при реализации третьего этапа является определение базовых таблиц для сущностей и формирование связей в виде дополнительных полей базовых таблиц либо отдельных связных таблиц.

Исходя из правила №6, логическая схема базы данных для рассматриваемого примера будет содержать три таблицы – две базовые для сущностей «Ресурсы» и «Изделие» и одну связную, которую назовем «Изделия-ресурсы». Эти таблицы, приведенные ниже, помимо всего прочего, содержат обозначения (PK) и (FK) для первичных и внешних ключей соответственно.

Таблица 6.1 - РЕСУРСЫ

Таблица 6.2 – ИЗДЕЛИЯ

Таблица 6.3 – ИздеДИЯ-РЕСУРСЫ

Для перехода к физическому уровню схемы определим типы и размеры (форматы) полей, имея в виду реализацию БД в Access.

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

На этом процесс проектирования БД можно считать завершенным.

7 РЕАЛИЗАЦИЯ ПРОЕКТИРУЕМОЙ БД

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

Разработанная база данных включает следующие объекты: таблицы «Мебель», «Нормы расходов», «Материал»; запросы «Запрос1», «Запрос2»; формы «Мебель», «Нормы расходов», «Нормы расходов 1», «Материал»; отчет «Суммарная прибыль». Ниже приведены листинги окон конструкторов для перечисленных выше объектов.

Рисунок 7.1 – Таблица МАТЕРИАЛ

Рисунок 7.2 – Таблица МЕБЕЛЬ

Рисунок 7.3 – Таблица НОРМЫ РАСХОДОВ

Рисунок 7.4 – Запрос 1.

Рисунок 7.5 – Запрос 2.

Текст запроса 2 на языке SQL имеет вид:

SELECT Sum([Цена продажи]*[Количество изделий]) AS [Максимальная прибыль]

FROM Мебель;

Рисунок 7.6 – Формы проектируемой БД

Рисунок 7.7 – Отчет СУММАРНАЯ ПРИБЫЛЬ

ВыводЫ

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

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

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

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