Системы принятия решения и оптимизации в электронных таблицах, конструирование баз данных

Размещено на http://www.allbest.ru/

4

Размещено на http://www.allbest.ru/

Министерство образования и науки Российской Федерации

Государственное образовательное учреждение высшего профессионального образования

Северо-Западный государственный заочный технический университет

Кафедра информатики и прикладной математики

КУРСОВАЯ РАБОТА

ПО

ИНФОРМАТИКЕ

Тема

Системы принятия решения и оптимизации в электронных таблицах, конструирование баз данных

Санкт - Петербург

2011г.

СОДЕРЖАНИЕ

Задание № 1

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

1.2 Построение дерева принятия решений

1.3 Реализация системы принятия решения в табличном процессоре

Выводы

Задание №2

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

2.2 Построение математической модели

2.3 Таблица в режиме вычислений до оптимизации

2.4 Таблица в режиме показа формул до оптимизации

2.5 Таблица в режиме вычислений после оптимизации

2.6 Таблица в режиме показа формул после оптимизации

2.7 Окно поиска решения

Выводы

Задание №3

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

3.2 Таблицы в режиме конструктора

3.3 Информационно-логическая модель

3.4 Запрос

3.5 Однотабличные пользовательские формы

3.6 Детальный отчет

3.7 Схема данных

3.8 Многотабличная база данных

Выводы

Библиографический список

Задание № 1

1. Разработать систему принятия решения для принятия решения аттестации знаний студента по высшей математике

№ семестра	Атрибут	Весовой фактор
1 семестр (раздел 1)	1. Умеет вычислять определители матриц 2. Умеет складывать и вычитать матрицы 3. Умеет перемножать матрицы 4. Умеет вычислять обратную матрицу 5. Умеет транспонировать матрицу 6. Умеет находить вектор выпуска продукции по вектору спроса балансовой модели Уровень решения =	5 5 10 10 5 20 35
2 семестр (раздел 2)	1. Умеет складывать вектора 2. Умеет брать производные 3. Умеет находить экстремальные точки функции с использованием производной 4. Умеет вычислять интегралы 5. Умеет решать дифференциальные уравнения 6. Умеет разрабатывать математические модели на основе дифференциальных уравнений Уровень решения =	5 10 10 10 10 20 45
3 семестр (раздел 3)	1. Умеет вычислять вероятности событии 2. Умеет находить моменты случайных величин 3. Умеет вычислять функцию распределения случайной величины 4. Умеет находить точечные оценки по выборке случайной величины 5. Умеет моделировать случайную величину с заданным распределением 6. Умеет работать с моделями управления запасам Уровень решения =	5 5 10 10 10 20 40

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

1. Создание базы данных для системы принятия решения.

2. Построение дерева принятия решений в текстовом редакторе Microsoft Word.

3. Создание в табличном процессоре Excel в режиме вычислений.

4. Создание в табличном процессоре Excel в режиме показа формул.

Необходимо разработать систему принятия решения для аттестации знаний абитуриента на основе тестирования.

Составим базу данных на основе базы знаний, в которую добавляется столбец ответов.

Если на заданный вопрос дается положительный ответ («Да»), т. е. абитуриент продемонстрировал достаточные знания по этой теме, весовой фактор атрибута сохраняется. Если ответ отрицательный («Нет») весовой фактор обнуляется.

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

1.2 ПОСТРОЕНИЕ ДЕРЕВА ПРИНЯТИЯ РЕШЕНИЙ

система принятие решение база данных

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

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

Первый блок проверяет выполнение условие S < 35. При проверке любого условия возможны два исхода: «Условие выполнено» (ветка «ДА») и «Условие не выполнено» (ветка «Нет»).

Если условие S < 35 выполняется, абитуриент получает оценку «не аттестован» (первый блок принятия решения). Если условие S < 35 не выполняется, значит, набранная абитуриентом сумма баллов больше 35. В этом случае нужно установить, в каком диапазоне находится сумма баллов.

По ветке «Нет» от первого блока проверка условий приходим на второй блок проверки, где записано условие S < 37. При его выполнении очевидно, что сумма баллов находится в диапазоне 35-37, т.е. абитуриент получает оценку «Удовлетворительно». По ветке «Да» идем к вершине «Удовл.» В случае невыполнения данного условия очевидно, что сумма балов равна или превысила значение 37. Поэтому по ветке «Нет» происходит переход к последнему блоку проверки условия S < 42. При выполнении данного условия (ветка «Да») абитуриент получает оценку «Хорошо», при невыполнении (ветка «Нет») - оценку «Отлично».

ДЕРЕВО ПРИНЯТИЯ РЕШЕНИЙ -1 СЕМЕСТР

Размещено на http://www.allbest.ru/

4

Размещено на http://www.allbest.ru/

ДЕРЕВО ПРИНЯТИЯ РЕШЕНИЙ -2 СЕМЕСТР

Размещено на http://www.allbest.ru/

4

Размещено на http://www.allbest.ru/

ДЕРЕВО ПРИНЯТИЯ РЕШЕНИЙ -3 СЕМЕСТР

Размещено на http://www.allbest.ru/

4

Размещено на http://www.allbest.ru/

1.3 РЕАЛИЗАЦИЯ СИСТЕМЫ ПРИНЯТИЯ РЕШЕНИЙ В ТАБЛИЧНОМ ПРОЦЕССОРЕ

Реализация системы принятия решений приведена в таблице ниже (режим показа формул) и в таблице (режим показа вычислений) Excel Microsoft Office.

Ответы вводятся с использованием двоичной системы. При положительном ответе («Да») нужно ввести число 1, при отрицательном («Нет») - ввести - 0. Ввод ответа в таком виде позволяет легко вычислить весовой фактор ответа путем умножения кода ответа на весовой фактор атрибута.

ТАБЛИЦА В РЕЖИМЕ ВЫЧИСЛЕНИЙ - 1 СЕМЕСТР

№ семестра	Атрибут	Весовой фактор	Ответ	Весовой фактор ответа
1 семестр ( раздел 1 )	1. Умеет вычислять определители матриц	5	1	5
	2. Умеет складывать и вычитать матрицы	5	0	0
	3. Умеет перемножать матрицы	10	0	0
	4. Умеет вычислять обратную матрицу	10	1	10
	5. Умеет транспонировать матрицу	5	0	0
	6. Умеет находить вектор выпуска продукции по вектору спроса балансовой модели	20	1	20
	Уровень решения	35
	Суммарный весовой фактор	35
	Принятие решения
	удовлетворительно

ТАБЛИЦА В РЕЖИМЕ ПОКАЗА ФОРМУЛ-1 СЕМЕСТР

№ семестра	Атрибут	Весовой фактор	Ответ	Весовой фактор ответа
1 семестр (раздел 1)	1. Умеет вычислять определители матриц	5	1	=ПРОИЗВЕД(C2:D2)
	2. Умеет складывать и вычитать матрицы	5	0	=ПРОИЗВЕД(C3:D3)
	3. Умеет перемножать матрицы	10	0	=ПРОИЗВЕД(C4:D4)
	4. Умеет вычислять обратную матрицу	10	1	=ПРОИЗВЕД(C5:D5)
	5. Умеет транспонировать матрицу	5	0	=ПРОИЗВЕД(C6:D6)
	6. Умеет находить вектор выпуска продукции по вектору спроса балансовой модели	20	1	=ПРОИЗВЕД(C7:D7)
	Уровень решения	35
	Суммарный весовой фактор	=СУММ(E2:E7)
	Принятие решения
	=ЕСЛИ(E9<C8;"не аттестован";ЕСЛИ(E9<37;"удовлетворительно";ЕСЛИ(E9<42;"хорошо";"отлично")))

ТАБЛИЦА В РЕЖИМЕ ВЫЧИСЛЕНИЙ - 2 СЕМЕСТР

2 семестр ( раздел 2 )	1. Умеет складывать вектора	5	0	0
	2. Умеет брать производные	10	0	0
	3. Умеет находить экстремальные точки функции с использованием производной	10	1	10
	4. Умеет вычислять интегралы	10	1	10
	5. Умеет решать дифференциальные уравнения	10	1	10
	6. Умеет разрабатывать математические модели на основе дифференциальных уравнений	20	1	20
	Уровень решения	45
	Суммарный весовой фактор	50
	Принятие решения
	хорошо

ТАБЛИЦА В РЕЖИМЕ ПОКАЗА ФОРМУЛ-2 СЕМЕСТР

2 семестр (раздел2)	1. Умеет складывать вектора	5	0	=ПРОИЗВЕД(C1:D1)
	2. Умеет брать производные	10	0	=ПРОИЗВЕД(C2:D2)
	3. Умеет находить экстремальные точки функции с использованием производной	10	1	=ПРОИЗВЕД(C3:D3)
	4. Умеет вычислять интегралы	10	1	=ПРОИЗВЕД(C4:D4)
	5. Умеет решать дифференциальные уравнения	10	1	=ПРОИЗВЕД(C5:D5)
	6. Умеет разрабатывать математические модели на основе дифференциальных уравнений	20	1	=ПРОИЗВЕД(C6:D6)
	Уровень решения	45
	Суммарный весовой фактор	=СУММ(E1:E6)
	Принятие решения
	=ЕСЛИ(E8<C7;"не аттестован";ЕСЛИ(E8<47;"удовлетворительно";ЕСЛИ(E8<52;"хорошо";"отлично")))

ТАБЛИЦА В РЕЖИМЕ ВЫЧИСЛЕНИЙ - 3 СЕМЕСТР

3 семестр ( раздел 3 )	1. Умеет вычислять вероятности событий	5	0	0
	2. Умеет находить моменты случайных величин	5	0	0
	3. Умеет вычислять функцию распределения случайной величины	10	1	10
	4. Умеет находить точечные оценки по выборке случайной величины	10	1	10
	5. Умеет моделировать случайную величину с заданным распределением	10	1	10
	6. Умеет работать с моделями управления запасам	20	1	20
	Уровень решения	40
	Суммарный весовой фактор	50
	Принятие решения
	отлично

ТАБЛИЦА В РЕЖИМЕ ПОКАЗА ФОРМУЛ-3 СЕМЕСТР

3 семестр (раздел3)	1. Умеет вычислять вероятности событий	5	0	=ПРОИЗВЕД(C1:D1)
	2. Умеет находить моменты случайных величин	5	0	=ПРОИЗВЕД(C2:D2)
	3. Умеет вычислять функцию распределения случайной величины	10	1	=ПРОИЗВЕД(C3:D3)
	4. Умеет находить точечные оценки по выборке случайной величины	10	1	=ПРОИЗВЕД(C4:D4)
	5. Умеет моделировать случайную величину с заданным распределением	10	1	=ПРОИЗВЕД(C5:D5)
	6. Умеет работать с моделями управления запасам	20	1	=ПРОИЗВЕД(C6:D6)
	Уровень решения	40
	Суммарный весовой фактор	=СУММ(E1:E6)
	Принятие решения
	=ЕСЛИ(E9<C7;"не аттестован";ЕСЛИ(E9<42;"удовлетворительно";ЕСЛИ(E9<47;"хорошо";"отлично")))

ВЫВОДЫ

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

Задание № 2

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

Составить план перевозок, чтобы затраты были минимальные.

Склады	Магазины
	В1=120	В2=140	В3=220	В4=160
А1=300	3	5	8	2
А2=180	6	1	4	3
А3=160	2	3	1	4

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

1. Построение математической модели в текстовом редакторе Microsoft Word.

2. Построение начального плана решения.

3. Оптимизация начального плана в табличном процессоре Excel.

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

2.2 ПОСТРОЕНИЕ МАТЕМАТИЧЕСКОЙ МОДЕЛИ

Обозначим:

Хij - количество продукции, отправляемой со склада i в магазин j;

Сij - стоимость перевозки единицы продукции со склада i в магазин j.

Математическая модель будет состоять из ряда ограничений:

а) исходя из физического смысла задачи (количество и стоимость продукции не могут быть отрицательными величинами)

Хij0; Сij0 (1)

б) ограничения по предложению (со складов нельзя вывести продукции больше, чем там имеется):

(2)

в) ограничения по спросу (в магазины следует завести не меньше продукции, чем им требуется):

(3)

Общая стоимость перевозок (целевая функция) равна:

Z=CijXij=

(4)

Необходимо определить такие значения переменных Ху, которые удовлетворяют ограничениям (1), (2) и (3) и обращают в минимум

целую функцию Z (4). В такой постановке задача является транспортной задачей линейного программирования.

Необходимым и достаточным условием разрешимости транспортной задачи является условие баланса:

= (5)

где - суммарное количество продукции на складах, (при этом = - количество продукции на одном складе, i=1,2,3);

- суммарное количество продукции, требуемое в магазинах (при этом - = количество продукции, которое требуется -му магазину, =1,2,3,4,5).

В нашем случае

=300+240+280=820; =160+240+220+200=820

==60,

следовательно, задача с балансом.

2.3 ТАБЛИЦА В РЕЖИМИ ВЫЧИСЛЕНИЙ ДО ОПТИМИЗАЦИИ

ОПТИМИЗАЦИЯ ПЛАНА ПЕРЕВОЗОК
Склады		Магазины
		1-й	2-й	3-й	4-й
Номер	Запас	Стоимость перевозок
ОПТИМИЗАЦИЯ ПЛАНА ПЕРЕВОЗОК
Склады	Магазины
		1-й	2-й	3-й	4-й
Номер	Запас	Стоимость перевозок
1	300	4	10	8	12
2	240	24	6	18	16
3	280	12	18	4	10
	Потребности магазинов
		160	240	220	200
Всего вывозится		План перевозок
1	300	50	120	30	100
2	240	80	120	20	20
3	280	30	0	170	80
		Завоз в магазины
		160	240	220	200
Стоимость перевозок в каждый магазин	2480	1920	1280	100
Целевая функция	5780

2.4 ТАБЛИЦА В РЕЖИМЕ ПОКАЗА ФОРМУЛ ДО ОПТИМИЗАЦИИ

Склады		Магазины
		1-й	2-й	3-й	4-й
Номер	Запас	Стоимость перевозок
ОПТИМИЗАЦИЯ ПЛАНА ПЕРЕВОЗОК
Склады	Магазины
		1-й	2-й	3-й	4-й
Номер	Запас	Стоимость перевозок
1	300	4	10	8	12
2	240	24	6	18	16
3	280	12	18	4	10
	Потребности магазинов
		160	240	220	200
Всего вывозится		План перевозок
1	=СУММ(C15:F15)	50	120	30	100
2	=СУММ(C16:F16)	80	120	20	20
3	=СУММ(C17:F17)	30	0	170	80
		Завоз в магазины
		=СУММ(C15:C17)	=СУММ(D15:D17)	=СУММ(E15:E17)	=СУММ(F15:F17)
Стоимость перевозок в каждый магазин	=СУММПРОИЗВ(C9:C11;C15:C17)	=СУММПРОИЗВ(D9:D11;D15:D17)	=СУММПРОИЗВ(E9:E11;E15:E17)	=СУММ(F16:F18)
Целевая функция	=СУММ(C20:F20)

2.5 ТАБЛИЦА В РЕЖИМЕ ВЫЧИСЛЕНИЙ ПОСЛЕ ОПТИМИЗАЦИИ

ОПТИМИЗАЦИЯ ПЛАНА ПЕРЕВОЗОК
Склады		Магазины
		1-й	2-й	3-й	4-й
Номер	Запас	Стоимость перевозок
ОПТИМИЗАЦИЯ ПЛАНА ПЕРЕВОЗОК
Склады	Магазины
		1-й	2-й	3-й	4-й
Номер	Запас	Стоимость перевозок
1	300	4	10	8	12
2	240	24	6	18	16
3	280	12	18	4	10
	Потребности магазинов
		160	240	220	200
Всего вывозится		План перевозок
1	300	160	0	0	200
2	240	0	240	0	0
3	280	0	0	220	0
		Завоз в магазины
		160	240	220	200
Стоимость перевозок в каждый магазин	640	1440	880	0
Целевая функция	2960

2.6 ТАБЛИЦА В РЕЖИМЕ ПОКАЗА ФОРМУЛ ПОСЛЕ ОПТИМИЗАЦИИ

ОПТИМИЗАЦИЯ ПЛАНА ПЕРЕВОЗОК
Склады		Магазины
		1-й	2-й	3-й	4-й
Номер	Запас	Стоимость перевозок
ОПТИМИЗАЦИЯ ПЛАНА ПЕРЕВОЗОК
Склады	Магазины
		1-й	2-й	3-й	4-й
Номер	Запас	Стоимость перевозок
1	300	4	10	8	12
2	240	24	6	18	16
3	280	12	18	4	10
	Потребности магазинов
		160	240	220	200
Всего вывозится		План перевозок
1	300	160	0	0	200
2	240	0	240	0	0
3	280	0	0	220	0
		Завоз в магазины
		=СУММ(C15:C17)	=СУММ(D15:D17)	=СУММ(E15:E17)	=СУММ(F15:F17)
Стоимость перевозок в каждый магазин	=СУММПРОИЗВ(C9:C11;C15:C17)	=СУММПРОИЗВ(D9:D11;D15:D17)	=СУММПРОИЗВ(E9:E11;E15:E17)	=СУММ(F16:F18)
Целевая функция	=СУММ(C20:F20)

2.7 ОКНО ПОИСКА РЕШЕНИЯ

ВЫВОДЫ

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

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

Задание №3

Разработать информационно-логическую модель предметной области «Книга» с атрибутами.

Создать логическую структуру этой базы данных для ACCESS. Заполнить таблицы базы данных записями.

Создать отчет с параметрами автор, название, инвентарный номер.

Создать запрос о книгах одного издательства.

система принятие решение база данных

Код книги	место публикации	тип обложки
Код темы	язык	цена
автор	переводчик	число страниц
название	издательство	инвентарный номер
год издания	редактор
номер ISBN	тираж

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

1. Создать новую базу данных.

2. Создать таблицы в режиме конструктора

3. Создать логическую модель

4. Создать запрос, формы

5. Создать детальный отчет

6. Создать схему данных

СУБД Access оринтирована на работу с объектом, к которому относятся таблицы базы данных, формы, запросы, отчеты.

3.2 ТАБЛИЦЫ В РЕЖИМЕ КОНСТРУКТОРА

АВТОР

НАЗВАНИЕ

ИНВЕНТАРНЫЙ НОМЕР

3.3 ИНФОРМАЦИОННО-ЛОГИЧЕСКАЯ МОДЕЛЬ

Размещено на http://www.allbest.ru/

4

Размещено на http://www.allbest.ru/

3.4 ЗАПРОС

3.5 ОДНОТАБЛИЧНЫЕ ПОЛЬЗОВАТЕЛЬСКИЕ ФОРМЫ

АВТОР

НАЗВАНИЕ

ИНВЕНТАРНЫЙ НОМЕР

3.6 ДЕТАЛЬНЫЙ ОТЧЕТ

АВТОР

НАЗВАНИЕ

ИНВЕНТАРНЫЙ НОМЕР

КНИГА

3.7. СХЕМА ДАННЫХ

3.8 МНОГОТАБЛИЧНАЯ БАЗА ДАННЫХ

АВТОР

НАЗВАНИЕ

ИНВЕНТАРНЫЙ НОМЕР

ВЫВОДЫ

Для получения определенных данных из Базы Данных пользователь может посмотреть запрос. На основе запроса можно построить отчет или форму. Использование запроса позволяет осуществить различные формы к одной и той же информации.

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

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

Библиографический список

1. Информатика \Учебно-методический комплекс \ Блок контроля освоения дисциплины \Методические указания к выполнению курсовой работы Методические указания к выполнению контрольной работы \ Санкт-Петербург Издательство СЗТУ 2011-05-11

2. Информатика \Методические указания к выполнению лабораторных работ с табличным процессором Excel \ Санкт-Петербург 2006 \ Издательство СЗТУ 2006

3. Информатика \Методические указания к выполнению лабораторных работ Система управления базами данных Accesse \ Санкт-Петербург 2006 \ Издательство СЗТУ 2009

Размещено на Allbest.ru