Методология и практическая реализация курсовой работы по системам принятия решений и базам данных

Введение. Контекст и цели курсового проекта

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

Общая цель данной курсовой работы — разработать и продемонстрировать комплексное IT-решение, состоящее из трех взаимосвязанных частей:

1. Система поддержки принятия решений для выбора наилучшей альтернативы на основе множества критериев.
2. Оптимизационная модель для решения задачи распределения ресурсов.
3. Реляционная база данных, служащая фундаментом для хранения и управления данными предметной области.

Таким образом, мы пройдем весь путь от постановки бизнес-задач до их практической реализации с помощью таких инструментов, как MS Excel и MS Access. В результате будет создана не просто совокупность разрозненных заданий, а единая система, где база данных служит центральным источником проверенных данных для аналитических моделей в СПР.

Теоретические основы. Фундамент для проектирования СПР и баз данных

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

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

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

Ключевыми компонентами любой СППР являются подсистема управления данными, подсистема управления моделями и пользовательский интерфейс, через который происходит взаимодействие. В нашей работе мы будем создавать СПР, основанные на моделях, используя для этого MS Excel.

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

1. Концептуальный: Описание предметной области, выделение ключевых сущностей и связей между ними.
2. Логический: Создание структуры БД на основе выбранной модели (в нашем случае — реляционной), определение таблиц, полей и ключей.
3. Физический: Реализация логической модели средствами конкретной СУБД (например, MS Access).

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

• Первая нормальная форма (1НФ): Все атрибуты таблицы должны быть атомарными (неделимыми), а каждая ячейка должна содержать только одно значение.
• Вторая нормальная форма (2НФ): Таблица должна быть в 1НФ, и все неключевые атрибуты должны полностью зависеть от всего составного первичного ключа.
• Третья нормальная форма (3НФ): Таблица должна быть в 2НФ, и в ней не должно быть транзитивных зависимостей (когда неключевой атрибут зависит от другого неключевого атрибута).

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

Задание 1. Разработка системы поддержки принятия решений

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

Для решения этой задачи мы будем использовать анализ матрицей решений — один из распространенных методов в СПР. Суть метода заключается в оценке каждой альтернативы (поставщика) по ряду заранее определенных критериев.

Критерии для принятия решения:

• Цена за единицу продукции (чем ниже, тем лучше).
• Качество продукции (оценивается по 10-балльной шкале).
• Надежность поставок (оценивается по 10-балльной шкале).
• Условия оплаты (например, возможность отсрочки платежа).

Каждому критерию присваивается вес в зависимости от его важности для компании. Например, цена может иметь вес 40%, качество — 30%, надежность — 20%, а условия оплаты — 10%. Сумма весов всех критериев должна быть равна 100%.

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

Практическая реализация СПР. Инструментарий Excel для анализа

MS Excel является мощным инструментом для создания СПР благодаря своим встроенным функциям и возможностям по работе с таблицами. Реализация нашей задачи по выбору поставщика будет состоять из нескольких шагов.

Шаг 1: Создание таблиц с исходными данными.
На листе Excel создаются две таблицы. Первая содержит исходные данные по поставщикам. Вторая — веса критериев.

Шаг 2: Расчет нормированных и взвешенных оценок.
Поскольку критерии имеют разную размерность (рубли, баллы), их необходимо привести к единой шкале — нормировать. Затем нормированные оценки умножаются на соответствующие веса критериев. Для этого используются простые арифметические формулы Excel. Крайне важно показать таблицы в двух режимах: сначала с итоговыми числовыми значениями, а затем в режиме отображения формул, чтобы продемонстрировать логику расчетов.

Например, для расчета итоговой оценки Поставщика А используется формула, суммирующая произведения его нормированных оценок на веса соответствующих критериев: =C10*D4 + D10*D5 + E10*D6 + F10*D7.

Шаг 3: Формулирование выводов.
После выполнения всех расчетов мы получаем итоговую интегральную оценку для каждого поставщика. Альтернатива с максимальной оценкой является наилучшим выбором согласно заданной модели. Вывод должен быть четким и обоснованным: «На основе проведенного анализа с использованием матрицы решений, наиболее предпочтительным является Поставщик Б с итоговой оценкой 8.5, так как он предлагает наилучшее сочетание цены, качества и надежности в соответствии с установленными приоритетами».

Задание 2. Оптимизационная задача и ее математическая модель

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

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

Условия и ограничения:

1. Каждый сотрудник работает 5 дней в неделю.
2. Каждый сотрудник имеет 2 выходных дня подряд.
3. Заработная плата у всех сотрудников одинакова, поэтому минимизация их числа эквивалентна минимизации фонда оплаты труда.
4. Известно минимально необходимое количество сотрудников на каждый день недели (например, Пн — 17, Вт — 13, Ср — 15 и т.д.).

Чтобы решить эту задачу, ее нужно формализовать, то есть перевести на язык математики.

Математическая модель:

• Переменные решения: Пусть x_i — это количество сотрудников, начинающих свою рабочую неделю в i-й день (где i=1 для понедельника, i=2 для вторника и т.д.).
• Целевая функция: Мы хотим минимизировать общее число сотрудников. Целевая функция будет выглядеть так:
  Z = x_1 + x_2 + x_3 + x_4 + x_5 + x_6 + x_7 → min
• Система ограничений: Теперь нужно обеспечить минимальное количество работающих в каждый день. Например, в понедельник работают те, кто начал смену в понедельник, воскресенье, субботу, пятницу и четверг. Это формирует систему неравенств:
  • Пн: x_1 + x_4 + x_5 + x_6 + x_7 ≥ 17
  • Вт: x_1 + x_2 + x_5 + x_6 + x_7 ≥ 13
  • …и так далее для всех семи дней недели.
• Ограничение целочисленности: Количество сотрудников не может быть дробным, поэтому все x_i должны быть целыми и неотрицательными.

Эта формализованная модель полностью готова для решения с помощью специализированных инструментов.

Поиск оптимального решения с помощью надстройки Excel Solver

Имея готовую математическую модель, мы можем использовать мощный инструмент Excel — надстройку «Поиск решения» (Solver). Этот инструмент предназначен для решения оптимизационных задач: поиска минимума, максимума или конкретного значения целевой функции при соблюдении ряда ограничений.

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

Шаг 2: Настройка диалогового окна «Поиск решения».
После активации надстройки (находится на вкладке «Данные») открывается диалоговое окно, которое нужно настроить:

1. Установить целевую ячейку: Указывается ячейка, в которой рассчитывается общее количество сотрудников. Устанавливается цель — «Минимум».
2. Изменяемые ячейки переменных: Указывается диапазон ячеек, в которых Solver будет подбирать значения (наши x_i).
3. Добавить ограничения: Последовательно вводятся все ограничения из математической модели. Указывается, что ячейки, рассчитывающие количество персонала на каждый день, должны быть «больше или равны» ячейкам с заданными минимальными требованиями. Также добавляется условие, что изменяемые ячейки должны быть «целыми» и «неотрицательными».

Шаг 3: Получение и анализ результатов.
После нажатия кнопки «Найти решение» Solver подбирает оптимальные значения для переменных. Результат выводится в итоговой таблице. Например, решение может показать, что для выполнения всех условий достаточно нанять 23 человека, и оптимальное распределение их выхода на работу будет таким: 4 человека начинают неделю в понедельник, 6 — в четверг и 13 — в субботу. Это решение является математически оптимальным и позволяет выполнить все условия с минимальными затратами.

Задание 3. Проектирование реляционной базы данных для предметной области «Заказ»

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

Шаг 1: Описание предметной области и выделение сущностей.
Анализируем бизнес-процесс. В нем участвуют: Клиенты, которые делают Заказы. В каждом заказе есть несколько Товаров. Заказы обрабатывают Сотрудники. Товары принадлежат к определенным Категориям. Таким образом, мы выделили ключевые сущности: `Клиенты`, `Сотрудники`, `Заказы`, `Товары`, `Категории`, а также связующую сущность `СоставЗаказа`.

Шаг 2: Разработка ER-диаграммы.
Информационно-логическая модель (ER-диаграмма) визуально представляет сущности (в виде прямоугольников) и связи между ними (в виде линий). Например, между `Клиенты` и `Заказы` будет связь «один-ко-многим», так как один клиент может сделать много заказов.

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

• 1НФ: Изначально у нас может быть одна большая таблица `Заказы` со всей информацией. Мы разбиваем ее, убедившись, что все поля атомарны. Например, нельзя хранить несколько товаров в одном поле.
• 2НФ: Мы создаем отдельные таблицы `Клиенты`, `Сотрудники`, `Товары`, чтобы их атрибуты (ФИО клиента, должность сотрудника) зависели только от их первичных ключей, а не от ключа заказа.
• 3НФ: Мы выносим категории товаров в отдельную таблицу `Категории`, чтобы убрать транзитивную зависимость (наименование категории зависело от ID товара, а ID товара — от ID заказа).

Шаг 4: Итоговая логическая структура.
В результате нормализации мы получаем набор взаимосвязанных таблиц. Для каждой таблицы определяется перечень полей, их типы данных (текстовый, числовой, дата/время), а также назначаются первичные ключи (уникальный идентификатор записи, например, `КодКлиента`) и внешние ключи (поле, ссылающееся на первичный ключ в другой таблице, например, `КодКлиента` в таблице `Заказы`). Эта структура полностью готова к реализации в любой реляционной СУБД.

Практическая реализация и наполнение базы данных в MS Access

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

Шаг 1: Создание таблиц в режиме конструктора.
Для каждой сущности из нашей логической структуры (`Клиенты`, `Заказы`, `Товары` и т.д.) в MS Access создается таблица. В режиме конструктора мы определяем имена полей, выбираем для них подходящие типы данных (например, «Короткий текст» для ФИО, «Числовой» для ID, «Дата/время» для даты заказа) и назначаем первичный ключ для каждой таблицы.

Шаг 2: Установка связей в схеме данных.
Это один из важнейших шагов, который превращает набор отдельных таблиц в единую базу данных. В окне «Схема данных» мы перетаскиваем поля первичных ключей на соответствующие им внешние ключи в других таблицах (например, `КодКлиента` из таблицы `Клиенты` на поле `КодКлиента` в таблице `Заказы`). При создании связей необходимо установить флажок «Обеспечение целостности данных». Это заставит Access следить за тем, чтобы нельзя было создать заказ для несуществующего клиента или удалить клиента, у которого есть активные заказы.

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

Шаг 3: Заполнение таблиц данными.
После создания структуры базу данных необходимо наполнить тестовыми данными. Для каждой таблицы создается по 5-10 записей. Важно соблюдать логику: сначала заполняются справочники (`Категории`, `Товары`, `Клиенты`, `Сотрудники`), а затем таблицы, которые на них ссылаются (`Заказы`, `СоставЗаказа`). Это необходимо для корректной работы ограничений целостности данных.

Извлечение данных. Создание запросов и отчетов

База данных создана и наполнена. Теперь ее главная задача — предоставлять информацию для анализа. В MS Access для этого используются запросы и отчеты. Запросы позволяют извлекать, фильтровать и объединять данные, а отчеты — представлять их в удобном для печати и анализа виде. Для создания запросов используется язык SQL (Structured Query Language).

Мы создадим несколько запросов разной сложности:

1. Простой запрос на выборку: Показать всех клиентов из города «Москва». Здесь используется одна таблица `Клиенты` и условие `WHERE`.
2. Запрос с объединением таблиц: Показать информацию о заказах: ФИО клиента и ФИО сотрудника, который его обработал. Для этого потребуется объединить (с помощью `JOIN`) три таблицы: `Заказы`, `Клиенты` и `Сотрудники`.
3. Итоговый запрос с группировкой: Посчитать, сколько заказов обработал каждый сотрудник. Здесь мы используем группировку по сотрудникам (`GROUP BY`) и агрегатную функцию `COUNT()` для подсчета количества заказов.

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

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

Заключение. Итоги и значимость проделанной работы

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

Резюмируя полученные результаты:

• В первом задании была создана система поддержки принятия решений в MS Excel, которая на примере выбора поставщика показала, как формализовать и решить многокритериальную задачу с помощью анализа матрицей решений.
• Во втором задании мы столкнулись с оптимизационной задачей по составлению графика работы персонала. Была разработана математическая модель и найдено оптимальное решение с помощью надстройки «Поиск решения», что продемонстрировало возможности Excel в решении задач линейного программирования.
• В третьем задании был спроектирован и реализован фундамент всей системы — реляционная база данных в MS Access. Процесс проектирования, включая нормализацию, создание связей и запросов на языке SQL, показал, как обеспечить надежное и структурированное хранение информации.

Главным итогом работы является демонстрация успешной интеграции технологий: надежная и нормализованная база данных в Access стала центральным источником проверенных данных, которые могут использоваться аналитическими и оптимизационными моделями в Excel. Это подтверждает основной тезис о том, что база данных служит основой для эффективной СПР. Таким образом, разработанное решение имеет практическую значимость, а полученные в ходе работы навыки являются фундаментальными для любого специалиста в области информационных технологий.

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

1. Информатика. Методические указания по курсовому проектированию, СЗТУ, 2010г.