Лабораторная работа № 13
Создание реляционной базы данных, заполнение БД,
создание форм.
Программа MS Access
позволяет создавать реляционные БД, а также обеспечивать их обработку с помощью
запросов, форм и отчетов.
Цель работы:
1)
Создать с помощью конструктора
реляционную БД «Поликлиника», в качестве основных объектов которой будут
использованы три таблицы: «Посещения», «Пациент» и «Врач». Таблицы «Посещения»
и «Врач» должны быть связаны с таблицей «Пациент».
2)
Создать три запроса к
созданной БД, из которых один запрос должен быть универсальным.
3)
Создать форму с помощью
мастера форм и конструктора.
Ход работы:
I.
Создание базы данных «Поликлиника».
1.
Загрузить офисную
программу ACCESS.
2.
Создать новую базу данных
[Файл, Создать новую базу данных…], сохранить её в своей папке и присвоить имя «Поликлиника».
3.
Откроется окно БД
«Поликлиника»
 |
Практическая работа
«Создание реляционной базы данных»
БД «Поликлиника» создаётся из трёх таблиц: «Врач»; «Посещение»; «Пациент»
Таблица в программе ACCESS создаётся двумя шагами:
§
сначала создаётся
структура таблицы;
§
затем таблица заполняется
данными.
1.
Для создания таблицы
заходим на ленту Создание и выбираем «Создание таблицы в режиме конструктора»
и создаем структуру таблицы
«Врач»
Заполнить только Имя поля
и Тип
данных, обязательно
указать ключевое поле. Для этого выделить нужную строку, щелкаем правой
кнопкой мыши и выбираем — Ключевое поле. Закрыть окно, сохраняя таблицу под именем «Врач»
2.
Открыть таблицу «Врач», двойным щелчком «мыши» и построчно
заполнить её данными.
3.
Аналогично создать таблицы
«Пациент» и «Посещение»
 |
 |
 |
|||
 |
|||
4. Таблица «Пациент»
является базовой таблицей, таблицы «Посещения» и «Врач» — подчиненными.
Необходимо установить связи между таблицами.
5. Ввести команду Сервис, Схема
данных.
Появится диалоговая панель Добавить таблицу.
С помощью клавиши {Shift}и или выделить три таблицы :«Врач»; «Посещение»;
«Пациент» и нажать Добавить. Выделенные таблицы будут добавлены в
специальное окно – Схема данных.
6.
Свяжем таблицу «Врач» с
таблицей «Пациент». Для этого перетащим мышкой поле № участка из таблицы «Врач» к одноимённому полю в
таблицу «Пациент».
В появившейся диалоговой панели Связи установим флажок на Обеспечение
целостности данных, а затем на каскадное обновление связанных полей
и каскадное удаление связанных полей. В завершении щелкнуть по кнопке Создать
(или Ок).
7.
Свяжем таблицу «Пациент» с
базовой таблицей «Посещения». Для этого перетащим мышкой из таблицы «Пациент»
поле Фамилия
пациента к одноименному
полю базовой таблицы «Посещения» и выполним действия, описанные в п. 7.
 |
Если связи созданы –
создана и БД. В противном случае необходимо искать ошибки в структуре таблиц
или данных.
II. Создание запроса
в реляционной базе данных «Поликлиника»
Создадим запрос,
который осуществляет выборку информации, необходимой для вывода на экран:
списка пациентов
участка №1, посетивших поликлинику после 15.05.98 г.
1. В группе объектов в окне Поликлиника: база данных
выбрать объект Запрос и Создание запроса в режиме конструктора.
2. Добавить все три
таблицы, выделяя их, удерживая клавишу {Shift}.
 |
3. Для
создания запроса, необходимо нажать на нужную строку и выбрать соответствующее
название таблицы и соответствующее название поля из списка
 |
и задать необходимые условия отбора. Выполнить
запуск запроса нажав, соответствующую кнопку .
 |
В результате получим
для первого участка следующие записи:
 |
4. Сохранить запрос
под именем Летние
посещения.
5. Для создания универсального запроса необходимо в нужном поле в строке «Условие
отбора» ввести фразу в квадратных скобках, например, создать
универсальный запрос поиска информации по диагнозу.
 |
6. Пустить запрос на запуск в появившемся окне ввести нужный диагноз,
например: ОРЗ.
 |
|||
 |
Результат запроса по ОРЗ.
7. Запрос
сохранить как «Диагноз»
8. Создать
формы и отчеты для таблиц
5. Для создания универсального запроса необходимо в нужном поле в строке «Условие
отбора» ввести фразу в квадратных скобках, например, создать
универсальный запрос поиска информации по диагнозу.
 |
6. Пустить запрос на запуск в появившемся окне ввести нужный диагноз, например:
ОРЗ.
 |
|||
 |
Результат запроса по ОРЗ.
7.
Запрос сохранить как «Диагноз»
8. Создать
формы и отчеты для таблиц
III
Создание форм с помощью мастера и конструктора форм
Задание 1. Создание автоформ
1.
Выберите объект базы- Формы. Нажмите кнопку Создать, в
открывшемся окне Новая форма выберите способ создания формы
«Автоформа в столбец», в качестве источника укажите таблицу «Врач».
2. Сохраните
созданную форму с именем – «Врач»
 |
3. Откройте форму и введите новую запись с
использованием формы: № участка- 3, ФИО врача — Сидорова Л.П., открыв таблицу
«Врач», убедитесь, что новая запись появилась.
3адание 2. Создание формы с помощью «Мастера
форм».
1. Выберите объект базы- Формы. Выберите Создание форм с помощью
мастера, в качестве источника укажите таблицу Пациент.
 |
2.
С помощью кнопки Выбор всех полей добавьте поля в таблицу, нажмите
кнопку Далее.
 |
 |
||
3. Внешний вид формы в один столбец, стиль официальный (перемещение
между окнами кнопкой далее)
4. Сохраните форму под именем «Пациент»
 |
5. Самостоятельно добавьте еще двух пациентов участка № 3.
Внешний вид формы «Пациент»,
Задание 3. Создание формы с помощью
конструктора.
1. Выберите объект базы- Формы. Нажмите кнопку Создать в
открывшемся окне выберите конструктор, в качестве источника
укажите таблицу Посещение.
 |
2. При нажатии кнопки Далее появится следующее
окно, панель инструментов и схема таблицы.
 |
3. Добавьте к форме Заголовок, меню Вид-Заголовок/примечание
формы.
 |
4.
Пользуясь кнопкой надпись, на панели элементов создайте в области заголовка
надпись «Посещение». Параметры заголовка – полужирный шрифт, размер – 14,
цвет – синий.
 |
|||
 |
|||
5. Рядом с надписью «Посещения» создайте кнопку для
закрытия формы. Для этого на панели элементов используйте элемент Кнопка, перенеся
ее курсором мыши в нужное место формы и вычерчивая ее рамки, при этом
запустится мастер Создание кнопок.
 |
6. Выберете категорию Работа с формой и действие Закрыть
форму. Далее Стоп, Кнопка 1 и Готово.
7. В область данных добавьте поля с помощью
перетаскивания мышью из схемы таблицы.
8. В Область данных добавьте две кнопки категории переходы по записям
Предыдущая запись(верхняя стрелка) и следующая запись (нижняя стрелка).
 |
|||
 |
|||
9.
Сохраните форму под именем Посещение. Откройте форму Посещение ее
примерный конечный вид должен выглядеть следующим образом.
 |
10. Научитесь перемещаться по записям с помощью созданных вами кнопок и
закрывать форму с кнопки Стоп.
Лабораторная работа № 5
«Составление штатного расписания хозрасчетной больницы»
Цели работы:
—
научиться использовать электронные
таблицы для автоматизации расчетов;
—
закрепить приобретенные навыки по
заполнению, форматированию и печати таблиц.
Постановка задачи
Заведующий хозрасчетной больницей должен составить
штатное расписание, то есть определить, сколько сотрудников, на каких
должностях и с каким окладом он должен принять на работу. Общий месячный фонд
зарплаты составляет $ 10000.
Построим модель решения этой задачи.
Поясним, что является исходными данными. Казалось бы,
ничего не дано, кроме общего фонда заработной платы. Однако заведующему
больницей известно больше: он знает, что для нормальной работы больницы нужно
5-7 санитарок, 8-10 медсестер, 10-12 врачей, 1 заведующий аптекой, 3 заведующих
отделениями, 1 главный врач, 1 заведующий хозяйством, 1 заведующий больницей.
На некоторых должностях число людей может меняться. Например, зная, что найти
санитарок трудно, руководитель может принять решение о сокращении числа
санитарок, чтобы увеличить оклад каждой из них.
Итак, заведующий принимает следующую модель задачи. За
основу берется оклад санитарки, а все остальные вычисляются исходя из него: во
сколько-то раз или на сколько-то больше. Говоря математическим языком, каждый
оклад является линейной функцией от оклада санитарки: A*C+B,
где C — оклад санитарки; A и B —
коэффициенты, которые для каждой должности определяются решением совета
трудового коллектива.
Допустим, совет решил, что:
q медсестра должна
получать в 1,5 раза больше санитарки (A=1.5, B=0 );
q врач — в 3
раза больше санитарки (B=0, A=3);
q заведующий отделением — на $30 больше, чем врач (A=3, B=30);
q заведующий аптекой — в 2 раза больше санитарки (A=2, B=0);
q заведующий хозяйством — на $40 больше медсестры (A=1.5, B=
40);
q главный врач — в
4 раза больше санитарки (A=4, B=0);
q заведующий больницей — на $20 больше главного врача (A=4, B=20).
Задав количество человек на каждой должности, можно
составить уравнение:
N1*(A1*C+B1)+N2*(A2*C+B2)+…+N8*(A8*C+B8)=10000,
где N1- количество санитарок; N2 — количество
медсестер и т. д.
В этом уравнении нам известны A1… A8 и B1…
B8, а неизвестны C и N1…
N8.
Ясно, что решить такое уравнение известными методами
не удастся, да и единственно верного решения нет. Остается решать уравнение
путем подбора. Взяв первоначально какие-либо приемлимые значения неизвестных,
подсчитаем суммы. Если эта сумма равна фонду заработной платы, то нам повезло.
Если фонд заработной платы превышен, то можно снизить оклад санитарки либо
отказаться от услуг какого-либо работника и т.д.
Проделать такую работу трудно. Но вам поможет
электронная таблица.
Рис. 5. 1.
Ход
работы
1.
Отведите для каждой
должности одну строку и запишите названия должностей в столбец A
(см. рис. 5. 1 — пример заполнения таблицы).
2.
В столбцах B
и C укажите соответственно коэффициенты A и B.
3.
В ячейку H5 занесите
заработную плату санитарки (в формате с фиксированной точкой и двумя знаками
после нее).
4.
В столбце D вычислите
заработную плату для каждой должности по формуле A* C+B.
Обратите внимание! Этот столбец должен заполняться формулами с использованием абсолютной
ссылки на ячейку H5, в которой указана зарплата санитарки. Изменение
содержимого этой ячейки должно приводить к изменению содержимого всего столбца D и пересчету всей таблицы.
5.
В столбце E
укажите количество сотрудников на соответствующих должностях в соответствии со
штатным расписанием.
6.
В столбце F
вычислите заработную плату всех рабочих данной должности. Тогда сумма элементов
столбца F даст суммарный фонд заработной платы.
Данные в столбцах D, F
должны быть представлены в формате с фиксированной точкой и двумя знаками после
нее.
7. Если расчетный фонд заработной платы не равен
заданному, то внесите изменения в зарплату санитарки или меняйте количество сотрудников
в пределах штатного расписания, затем осуществляйте перерасчет — до тех пор,
пока сумма не будет равна заданному фонду.
8. Сохраните таблицу в личной папке под именем раб_3. xls.
9.
После получения удовлетворительного результата
отредактируйте таблицу.
См. рис. 5.2 — пример оформления штатного расписания
больницы без подобранных числовых значений.
9.
1. Оставьте видимыми столбцы A, D,
E, F.
Столбцы B, C можно скрыть, воспользовавшись пунктом меню Формат,
столбец, Скрыть.
 |
Рис. 5. 2
9. 2. Дайте заголовок таблице «Штатное расписание
хозрасчетной таблицы» и подзаголовок «зав. больницей Петров И. С. ».
9. 3. Оформите таблицу, используя авто форматирование. Для этого:
·
выделите всю таблицу, включая заголовки;
Рис. 5. 3.
· выберите пункт меню Формат, Автоформат (см.
рис. 5. 3);
· выберите удовлетворяющий вас формат.
10. Сохраните
отредактированную таблицу в личной папке под именем
раб_3. xls.
11.
Предъявите преподавателю: файл раб_3. xls..
Лабораторная работа №6
«Знакомство с графическими возможностями Excel»
Цели работы:
§
научиться строить графики;
§
освоить основные приемы
редактирования и оформления диаграмм;
§
научиться распечатывать диаграммы.
Задача
Построить графики функций y1 = x2
— 1, y2 = x2
+ 1, y = 10 * (y1/y2)
по данным лабораторной работы №3.
Построение графиков
Для построения обыкновенных графиков
функций y = f(x) используется тип диаграммы ХУ – график с
точечными маркерами. Эта возможность используется для проведения
сравнительного анализа значений У при одних и тех же значениях Х, а также для
графического решения систем уравнений с двумя переменными.
Воспользуемся таблицей, созданной в лабораторной
работе №3. На одной диаграмме построим три совмещенных графика: y1 = x2
-1, y2 = x2
+ 1, y = 10*(y1/y2).
Задание 1. Загрузите
файл раб_2.xls (см. рис. 6. 1.).
Рис. 6. 1.
Задание 2. Снимите
защиту с листа.
Задание 3. Переместите
вспомогательную таблицу под основную, начиная с ячейки В27.
Задание 4. Щелкните
по кнопке Мастер диаграмм и выберите на вкладке Стандартные, Тип:
График, Вид: График с маркерами, помечающими точки данных. (см. рис. 6.
2.)
Рис. 6. 2.
Задание 5. Постройте
график по шагам, для этого надо щелкнуть по кнопке Далее..
Рис. 6. 3.
5.1. На 2-м шаге укажите ячейки D3:F24
(см. рис. 6. 3.)
Обратите внимание, что указываются 3 столбца (3 ряда
у-значений), так как мы хотим построить 3 совмещенных графика. Для построения
одного графика необходим 1 столбец у-значений.
5.2. На 3-м шаге вид диалогового окна представлен на
рис. 6. 4.
Рис. 6. 4.
5.3. На 4-м шаге выберите размещение диаграммы на
имеющемся Листе1 и щелкните по кнопке Готово (см. рис. 6. 5.)
Рис. 6. 5.
В результате этих действий экран примет
вид рис. 6. 6.
Рис. 6. 6.
5.4. Теперь надо исправить неправильный
образец диаграмм.
Для этого выполните команду
Диаграмма, Параметры диаграммы, Заголовки, где добавьте название диаграммы
«Совмещенные графики». Укажите название по оси Х — «х», название по оси У —
«у» (см. рис. 6. 7.)
Рис.
6. 7.
Для того чтобы получить график, представленный на рис.
6. 8., его необходимо отредактировать.
Рис. 6. 8.
Задание 6. Самостоятельно
отформатируйте область построения диаграммы подобно рис. 6. 8. Для этого
используйте команды пункта меню Диаграмма.
Задание 7. Сохраните
файл под новым именем раб_4.xls.
Задание 8. Подготовьте
таблицу и график к печати: выберите альбомную ориентацию.
Задание 9. Распечатайте
таблицу и график на одном листе.
Задание 10. Подведите
итоги.
Проверьте:
—
знаете ли вы, что такое Мастер диаграмм;
—
умеете ли вы: строить
одиночный график; строить совмещенные графики; редактировать область диаграмм.
Если нет, то еще раз внимательно
перечитайте соответствующие разделы работы.
Предъявите преподавателю:
—
файл раб_4.xls
на экране и на рабочем диске в личном каталоге;
—
распечатанные на одном листе
таблицу и график.
Урок № 3
СОСТАВЛЕНИЕ ШТАТНОГО РАСПИСАНИЯ ХОЗРАСЧЕТНОЙ БОЛЬНИЦЫ
Цели работы:
- научиться использовать электронные таблицы для автоматизации расчетов;
- закрепить приобретенные навыки по заполнению, форматированию и печати таблиц.
Постановка задачи:
Заведующий хозрасчетной больницей должен составить штатное расписание, т.е. определить, сколько сотрудников, на каких должностях и с каким окладом он должен принять на работу. Общин месячный фонд зарплаты составляет $10 000.
Построим модель решения этой задачи.
Поясним, что является исходными данными. Казалось бы, что ничего не дано, кроме общего фонда заработной платы. Однако заведующему больницей известно больше: он знает, что для нормальной работы больницы нужно:
5-7 санитарок;
8-10 медсестер;
10-12 врачей;
1 заведующий аптекой;
3 заведующих отделениями;
1 главный врач;
1 завхоз,
1 заведующий больницей.
На некоторых должностях число людей может меняться. Например, зная, что найти санитарок трудно, руководитель может принять решение сократить число санитарок, чтобы увеличить оклад каждой из них.
Итак, заведующий принимает для себя следующую модель задачи. За основу берется оклад санитарки, а все остальные вычисляются через него: во столько-то раз или на столько-то больше. Говоря математическим языком, каждый оклад является линейной функцией от оклада санитарки:
А • С + В,
где С — оклад санитарки;
А и В — коэффициенты, которые для каждой должности определяются решением совета трудового коллектива.
Допустим, совет решил, что медсестра должна получать в 1,5 раза больше санитарки,
врач — в 3 раза больше санитарки,
заведующий отделением — на 30 $ больше, чем врач заведующий аптекой — в 2 раза больше санитарки, завхоз — на 40 $ больше медсестры,
главный врач — в 4 раза больше санитарки, заведующий больницей — на 20 $ больше главного врача.
Задав количество человек на каждой должности, можно составить уравнение:
N1 *(А1 *C+B1)+N2 * (А2 *C+B2)+…+N8 *(А8 * С+В8)=10000,
Где:
- N1 — количество санитарок
- N2 — количество медсестер
- А1…А8 и В1…В8; и т.д. — коэффициенты для каждой должности.
В этом уравнении нам известны А1…А8 и В1…В8, а не известны С и N1…N8.
Ясно, что решить такое уравнение известными методами не удается, да и единственно верного решения нет. Остается решать уравнение путем подбора.
Взяв первоначально какие-либо приемлемые значения неизвестных, подсчитаем сумму. Если эта сумма равна фонду заработной платы, то вам повезло. Если фонд заработной платы превышен, то можно снизить оклад санитарки, либо отказаться от услуг какого-либо работника и т. д. Проделать такую работу вручную трудно. Но вам поможет электронная таблица.
ХОД РАБОТЫ:
ЗАДАНИЕ 1. Заполните таблицу (см. рис. пример заполнения таблицы).
1.1. Заполните шапку таблицы.
1.2.Отведите для каждой должности одну строку и занесите
название должностей в столбец С.
1.3.В столбцах А и В укажите коэффициенты А и В, соответствующие каждой должности.
1.4.В ячейку НЗ занесите значение заработной платы санитарки 150 и установите для нее формат 0,00 — два знака после
запятой.
ЗАДАНИЕ 2. В столбце D вычислите заработную плату для каждой
должности.
В постановке задачи было объяснено, что заработная плата вычисляется по формуле А*С+В. В нашей таблице коэффициенты А и В находятся в столбцах А и В, а С — зарплата санитарки указана в ячейке НЗ.
Внимание! Столбец D должен заполняться формулами с использованием абсолютной ссылки на ячейку НЗ. Изменение содержимого этой ячейки должно приводить к изменению содержимого всего столбца D. Аналогично в работе №2(1) в задании 3 изменение значения шага во вспомогательной таблице приводило к пересчету в основной. В данной задаче удобно использовать еще один способ абсолютной адресации — именованную ячейку.
Именованная ячейка
В Excel можно присвоить имя любой ячейке или области. Чтобы присвоить имя ячейке, ее необходимо выделить и выполнить команду Вставка – Имя — Присвоить. На экране появится диалоговое окно с полем ввода, где необходимо набрать имя и нажать кнопку <ОК>
Имя должно начинаться с буквы, не содержать пробелов, не совпадать с адресацией. Например, нельзя использовать имя F:2, но можно F_12 (рис. 3.3).
Второй способ именования состоит в использовании поля имени, которое располагается слева в строке формул.
Для этого необходимо:
- выделить ячейку или область;
- перейти в поле имени и щелкнуть левой кнопкой мыши;
- ввести имя и нажать клавишу <Ок>. При выборе имени из списка имен Excel немедленно перейдет к этой именованной ячейке или области.
Использовать именованную ячейку удобно в формулах, так как можно заменить адрес ячейки, ничего не говорящий о ее содержании на более выразительное имя. При ссылке в формулах на именованную ячейку она будет адресована абсолютно и при копировании формул не возникнут ошибки. Кроме того, рекомендуется именовать все «важные» ячейки, в которых планируется часто изменять данные и которые содержат итоговые результаты.
2.1.Ячейке НЗ присвойте имя С.
2.2.В ячейку D3 занесите формулу =АЗ*С+В3.
Хотя эта формула равнозначна =АЗ* $Н$3+В, но имя С облегчает понимание формулы.
2.3. Скопируйте формулу из ячейки D3 в D4: D10.
При копировании адрес ячейки с зарплатой санитарки остался постоянным (абсолютным), а адреса A3 и ВЗ перенастраиваются (они относительные).
ЗАДАНИЕ 3. В столбце Е укажите количество сотрудников на должностях.
Данные в ячейках ЕЗ:Е5 могут изменяться в пределах штатного расписания, количество сотрудников на других должностях неизменно (см. постановку задачи).
ЗАДАНИЕ 4. В столбце F вычислите заработную плату всех сотрудников данной должности.
4.1.В ячейку F3 занесите формулу =D3*E3 (зарплата * количество сотрудников).
4.2.Скопируйте формулу из ячейки F3 в F4 -.F10.
4.3.Установите для данных в столбцах D и F формат 0,00 —
два знака после запятой.
ЗАДАНИЕ 5. Определите суммарный месячный фонд заработной платы.
5.1. Просуммируйте столбец F, используя кнопку 
5.2.Переместите значение суммы в ячейку F12 и сделайте к
ней подпись : Суммарный месячный фонд заработной платы
5.3.Составьте штатное расписание.
Вносите изменения в зарплату санитарки или меняйте количество сотрудников в ячейках ЕЗ:Е5 до тех пор, пока полученный суммарный месячный фонд заработной платы не будет равен заданному $10000 (в ячейке F12 необходимо получить значение *= 10000).
ЗАДАНИЕ 6. Сохраните таблицу в личном каталоге под именем work3.xls
ЗАДАНИЕ 7. Составьте штатное расписание с использованием функции автоматизации расчетов — Подбор параметра.
Подбор параметра — удобное средство Excel для анализа «Что — если». При этом значения для ячеек-параметров изменяются так, чтобы число в целевой ячейке стало равно заданному.
7.1. Выберите команду Меню: Сервис – Подбор параметра.
7.2. Укажите в поле Установить в ячейке адрес целевой ячейки
7.3. Укажите в поле Значение — 10000.
7.4.Укажите в поле Изменяя значение ячейки — адрес ячейки с зарплатой
санитарки $Н$3 и нажмите на кнопку <ОК>.
Начнется процесс подбора параметра. На рисунке показам результат подбора параметра. Если нажать на кнопку <ОК>, значения ячеек в таблице будут изменены в соответствии с найденным решением.
ЗАДАНИЕ 8. Составьте несколько вариантов штатного расписания с использованием функции Подбор параметра и оформите их в виде таблицы.
8.1.Измените количество сотрудников на должностях санитарки, медсестры и врача.
8.2.Подберите зарплату санитарки в новых условиях.
8.3.Составьте таблицу нескольких вариантов штатного расписания.
В Excel имеется автоматическое средство сохранения вариантов поиска решения при помощи Диспетчера сценариев, который находится в меню Сервис — Сценарии. Им можно воспользоваться, но разбираться в принципе его работы придется самостоятельно.
ЗАДАНИЕ 9. Сохраните таблицу в личном каталоге под тем же именем work3.xls
ЗАДАНИЕ 10. Проанализируйте полученные варианты, выберите и оформите один из них.
10.1. Оставьте столбцы С, D, Е, F.
Внимание! Удалить столбцы А, В, Н нельзя, так как в таблице на них есть ссылки, но их можно скрыть. Столбцы А, В, Н необходимо выделить, а затем воспользоваться пунктом главного меню Формат.- Столбец — Скрыть или вызвать контекстно-зависимое меню и выбрать пункт — Скрыть.
10.2.Дайте заголовок таблице «Штатное расписание хозрасчетной больницы и подзаголовок «Зав. больницей Петров И.С. «
10.3.Удалите таблицу вариантов штатного расписания.
- Таблицу оформите, используя «автоформатирование»
- выделите всю таблицу, включая заголовки;
- выберите пункт меню Формат — Автоформат;
- выберите удовлетворяющий вас формат.
ЗАДАНИЕ 11. Сохраните отредактированную таблицу в личном каталоге под именем hospital.xls.
ЗАДАНИЕ 12. Подготовьте таблицу к печати, воспользовавшись предварительным просмотром печати:
12.1.Выберите альбомное расположение.
12.2.Уберите сетку.
12.3.Укажите в верхнем колонтитуле фамилию, а в нижнем —
дату и время.
ЗАДАНИЕ 13. Выведите отредактированную таблицу hospital.xls на печать.
Цель
работы:
на примере составления штатного
расписания хозрасчетной больницы
научиться решать экономические задачи
с помощью функции подбора параметров
Постановка
задачи:
Заведующий
хозрасчетной больницы должен составить
штатное расписание, то есть определить,
сколько сотрудников, на каких должностях
и с каким окладом он должен принять на
работу. Общий месячный фонд зарплаты
составляет 100 000 руб.
Исходные
данные: заведующий знает, что для
нормальной работы больницы требуется
5-7 санитарок, 8-10 медсестер, 10-12 врачей,
1 заведующий аптекой, 1 заведующий
больницей. На некоторых должностях
число людей может меняться. Например,
зная, что найти санитарок сложно,
руководитель может принять решение о
сокращении числа санитарок, чтобы
увеличить оклад каждой из них.
Заведующий
берет за основу следующую модель. За
основу берется оклад санитарки, а все
остальные вычисляются исходя из него:
A
* C
+ B,
где C
– оклад санитарки, A,
B
– коэффициенты, которые определяются
для каждой должности решением трудового
коллектива.
Допустим,
что совет решил:
Медсестра
должна получать в 1,5 раза больше санитарки,
то есть A
= 1.5, B
= 0;
Врач
– в 3 раза больше санитарки (A
= 3, B
= 0);
Заведующий
отделением – на 300 руб. больше, чем врач
(A
= 3; B
= 300);
Заведующий
аптекой – в 2 раза больше санитарки (A
= 2; B
= 0);
Заведующий
хозяйством – на 400 руб. больше медсестры
(A
= 1.5; B
= 400);
Главный
врач – в 4 раза больше санитарки (A
= 4; B
= 0);
Заведующий
больницей – на 200 руб. больше главного
врача (A
= 4; B
= 200).
Ход
выполнения работы:
Заполнение
таблицы. Введем данные о коэффициентах
A
и B
в ячейки B6:C13
в соответствии с представленными выше
рекомендациями. Далее для каждой
должности введем формулу, определяющую
зарплату сотрудника =B6*$H$5+C6 — для санитарки
(ячейка D6).
Продлим формулу на ячейки D7:D13.
Заметим, что используется ссылка на
ячейку H5,
которая представляет собой зарплату
санитарки.
Задав
количество сотрудников для каждой
должности в соответствии с представленными
выше рекомендациями, можно вычислить
суммарную зарплату для каждой должности
– так для всех санитарок будем использовать
формулу =D6*E6. Продлим формулу на ячейки
D7:D13.
В
ячейке F15
представлен суммарный месячный фонд
зарплаты всех сотрудников больницы
=СУММ(F6:F13).
Теперь
изменяя величину заработной платы
санитарки (значение в ячейке H5),
мы будем получать различные значения
месячного фонда зарплаты. Нужно подобрать
такое значение H5,
при котором мы не превысим предоставленных
нам 100 000 руб. Также мы можем изменять
число сотрудников!
Сделать
вручную это очень сложно, поэтому мы
воспользуемся функционалом Excel
– Поиск решения. Введем следующие
параметры:
То
есть мы хотим найти такое значение H5,
при котором значение F5
составит 100 000. После нажатия кнопки
Выполнить, мы получим следующее решение:
1611.38 – заработная плата санитарки –
см. штатное расписание в таблице 3.1.
Таблица
3.1 – Штатное расписание №1
|
ШТАТНОЕ |
|||||
|
зав. |
|||||
|
должность |
коэф. |
коэф. |
зарплата |
кол-во |
суммарная |
|
A |
B |
сотрудника |
сотрудников |
зарплата |
|
|
санитарка |
1 |
0 |
1611,38 |
5 |
8056,91 |
|
медсестра |
1,5 |
0 |
2417,07 |
8 |
19336,59 |
|
врач |
3 |
0 |
4834,15 |
10 |
48341,46 |
|
зав. |
3 |
300 |
5134,15 |
1 |
5134,15 |
|
зав. |
2 |
0 |
3222,76 |
1 |
3222,76 |
|
завхоз |
1,5 |
400 |
2817,07 |
1 |
2817,07 |
|
главврач |
4 |
0 |
6445,53 |
1 |
6445,53 |
|
зав. |
4 |
200 |
6645,53 |
1 |
6645,53 |
|
месячный |
100000,00 |
||||
|
фонд |
|||||
|
зарплаты |
Если
мы изменим число санитарок, медсестер
и врачей, то мы можем получить новое
штатное расписание и новые данные о
заработной плате на каждой должности
– см. таблицу 3.2, зарплата санитарки
1753,98 руб. Для таблицы 3.2 произведем
Автоформатирование.
Таблица
3.2 – Штатное расписание №2
|
ШТАТНОЕ |
|||||
|
зав. |
|||||
|
должность |
коэф. |
коэф. |
зарплата |
кол-во |
суммарная |
|
A |
B |
сотрудника |
сотрудников |
зарплата |
|
|
санитарка |
1 |
0 |
1753,98 |
3 |
5261,95 |
|
медсестра |
1,5 |
0 |
2630,97 |
8 |
21047,79 |
|
врач |
3 |
0 |
5261,95 |
9 |
47357,52 |
|
зав. |
3 |
300 |
5561,95 |
1 |
5561,95 |
|
зав. |
2 |
0 |
3507,96 |
1 |
3507,96 |
|
завхоз |
1,5 |
400 |
3030,97 |
1 |
3030,97 |
|
главврач |
4 |
0 |
7015,93 |
1 |
7015,93 |
|
зав. |
4 |
200 |
7215,93 |
1 |
7215,93 |
|
месячный |
100000,00 |
||||
|
фонд |
|||||
|
зарплаты |
Выводы:
в ходе выполнения работы мы узнали, что
ЭТ Excel
позволяют решать сложные экономические
задачи с помощью Поиска решения.
Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]
- #
- #
- #
- #
- #
- #
- #
- #
- #
- #
- #
Министерство образования и науки Республики Казахстан
Карагандинский Государственный Технический Университет
Кафедра САПР
ПОЯСНИТЕЛЬНАЯ ЗАПИСКА
к курсовой работе
По дисциплине:
«Проектирование баз данных»
Тема:
«Проектирование базы данных «Больница»
2009
Содержание
Введение
1. Постановка задачи
2. Обоснование выбора СУБД и программного обеспечения
3. Структура базы данных
3.1 Концептуальная модель данных
3.2 Логическая модель данных
4. Нормализация отношений
5. SQL – запросы
6. Интерфейс системы для работы с базой данных
6.1 Главное окно программы
6.2 Разработка меню
7. Отчеты
Заключение
Список использованной литературы
Введение
Любая организация нуждается в своевременном доступе к информации. Ценность информации в современном мире очень высока. Роль распорядителей информации в современном мире чаще всего выполняют базы данных. Базы данных обеспечивают надежное хранение информации, структурированном виде и своевременный доступ к ней. Практически любая современная организация нуждается в базе данных, удовлетворяющей те или иные потребности по хранению, управлению и администрированию данных.
Целью выполнения курсового проекта является приобретение студентами практических навыков проектирования баз данных и разработки программного обеспечения по управлению базами данных с использованием СУБД. В ходе выполнения данного курсового проекта необходимо разработать информационную базу данных для больницы, которая поможет любому пользователю легко найти нужную информацию о любом сотруднике или пациенте.
Больница – это такая организация, которая работает с очень большим объемом информации, как о сотрудниках, так и о пациентах. Врачам всегда следить за данными о своих пациентах, о курсе лечения больных. А руководству и бухгалтерии необходимо быть в курсе событий о своих сотрудниках. Для этого нужна общая база данных, включающая всю необходимую информацию. Программа является очень актуальной на сегодняшний день, она автоматизирует работу с базой данных и предоставляет пользователю (оператору) понятный и дружественный интерфейс.
Мощность базы данных обусловлена возможностью ее постоянного пополнения новыми данными, причем в неограниченном количестве информации. Это является очень удобным для пользователя. Таким образом, создание базы данных, обладающей такими свойствами, задача достаточно актуальная и полезная.
В задачи курсового проекта входят:
1. Разработка структуры баз данных.
2. Работа с несколькими таблицами и установление взаимосвязей между таблицами.
3. Разработка системы меню.
4. Разработка и формирование отчетов.
5. Использование расширенных средств СУБД ввода и вывода данных.
6. Организация справочной системы.
1. Постановка задачи
Необходимо спроектировать базу данных для больницы, в которой должны быть учтены все требования заказчика.
Требования были следующими:
-
База данных должна отражать всю информацию о больнице, в частности о сотрудниках и о пациентах;
-
В базе данных должна быть справочная информация для врачей о диагнозах и способах их лечения; о процедурах, которые имеются в больнице и их оплате; о лекарствах, имеющих место в медицине.
-
Должна быть возможность вносить изменения в данные и пополнения новыми данными.
-
В программе должна присутствовать функции поиска, выполнения определенных запросов, печати отчетов.
-
Программа должна иметь справочное руководство по использованию базы данных.
2. Обоснование выбора СУБД и программного обеспечения
В качестве средства проектирования базы данных, была выбрана СУБД MS SQL Server 2000.
В отличие от всех остальных наиболее известных СУБД, MS SQL Server имеет ряд преимуществ. Главным из них является то, что он обеспечивает создание информационных систем с архитектурой «клиент-сервер», в которой он играет роль сервера баз данных. Одним из важных свойств Microsoft SQL Server является возможность управления целостностью данных.
Также можно сказать, что он удовлетворяет требованиям, предъявляемым к системам распределенной обработки информации. Эта СУБД поддерживает: тиражирование данных, параллельную обработку, создание и обработку больших баз данных на недорогих аппаратных платформах, отличается простотой управления и использования.
Средством, связывающим клиента с сервером, является язык SQL (Structured Query Language) – язык структурированных запросов. В данном курсовом проекте были использованы основные операторы этого языка, в частности для создания запросов. Этот язык является очень распространенным на данный момент и имеет ряд особенностей.
Он позволяет:
• создавать базы данных и таблицы с полным описанием их структуры;
• выполнять основные операции манипулирования данными, такие как
вставка, модификация и удаление данных из таблиц;
• выполнять простые и сложные запросы.
Этот язык был выбран, так как он доступен для изучения, синтаксис команд понятен и прост.
В качестве среды разработки самой программы для работы с базой данных была выбрана среда программирования Borland Delphi 7, использующая язык программирования Object Pasсal.
Основные причины выбора языка Object Pascal:
– Главной из причин является возможность работы с базами данными;
– Легкость в организации доступа к базе данных;
– Возможность редактирования таблиц, вставки и удаления записей;
– Возможность создания достаточно понятного и дружественного интерфейса для пользователя.
– Простота языка позволяет быстро его освоить и создавать сложные программы;
Также, на выбор Object Pascal в качестве базового программного обеспечения, повлияли такие обстоятельства как, достаточное количество необходимой литературы по программированию в этой среде, распространенность этой среды и имеющийся достаточный опыт работы в ней.
3. Структура базы данных
3.1 Концептуальная модель
Основным объектом в рассматриваемой модели является больница. У больницы есть свои характеристики: заведующий больницей, адрес, название. Она представлена в виде составляющих ее отделений, которые тоже имеют ряд характеристик: заведующий отделением, название отделение, старшая медсестра.
В каждом отделении имеются сотрудники и пациенты, которые имеют свои личные данные.
Концептуальная модель базы данных представлена на рисунке 3.1.
Рисунок 3.1 – Концептуальная модель базы данных
3.2 Логическая модель базы данных
Логическая модель представлена на рисунке 3.2.
Рисунок 3.2 – Логическая модель базы данных
В базе данных имеется 9 таблиц. Первая самая главная таблица «Отделения», которая является главной для таблиц «Персонал», «Пациенты» и «Диагнозы», с которыми она связана по полю «Код отделения» и имеет место связь «1-ко многим». В свою очередь таблица «Персонал» является главной для таблицы «Процедуры», которая связана по полю «Код медсестры», связь также «1-ко многим».
Две таблицы «Диагноз-Лекарства» и «Диагноз-Процедуры» имеют составные ключи и связаны с двумя таблицами, по коду диагноза с таблицей «Диагнозы» и по коду лекарства и процедуры с таблицами «Лекарства» и «Процедуры» соответственно. Получилась связь «многие – ко многим». Такая связь нужна, для того чтобы можно было для одного диагноза выбрать много процедур и лекарств. Соответственно, для процедур и лекарств могут соответствовать много диагнозов.
4. Нормализация отношений
Нормализация – это процесс проектирования базы данных с целью исключения аномалий модификаций, избыточности данных и приведения отношений к нормальным формам.
Аномалии модификации – последствия, которые могут возникнуть вследствие изменения отношения (таблицы).
Аномалии устраняются путем разбиения отношения на 2 и более новых отношений. Аномалии делятся на 2 основных вида: аномалия удаления, аномалия вставки.
Можно сказать, что в данном курсовом проекте все таблицы находятся в 1НФ, так как все они удовлетворяют требованиям, необходимым для того, чтобы таблица считалась отношением. Главным образом, это требование атомарности.
Отношение находится во 2НФ, если оно находится в 1НФ и все неключевые атрибуты в отношении зависят только от всего ключа в целом.
Принадлежность к 2НФ всех таблиц, кроме двух таблиц («Диагноз-Процедуры», «Диагноз-Лекарства»), можно определить по наличию 1 первичного простого ключа, состоящего из одного атрибута. А эти две таблицы состоят только из двух ключевых атрибутов, т.е. первичный ключ является составным. Таким образом, в этих таблицах нет неключевых атрибутов, независящих от всего ключа в целом.
Отношение находится в 3НФ, если отношение находится во 2НФ и не имеет транзитивных зависимостей.
В данном курсовом проекте все таблицы находятся в 3НФ. Хотя в начале курсового проекта предполагалось, что таблица «Персонал» будет включать в себя атрибуты «Должность» и «Оклад», что говорило бы о наличие транзитивной зависимости. В результате нормализации таблица «Персонал» была разбита на два отношения «Персонал» и «Должности», которые теперь находятся в 3НФ.
Отношение находится в НФБК, если оно находится в 3НФ и каждый детерминант является ключом-кандидатом.
Можно сказать, что во всех таблицах данной базы данных, кроме первичных ключей ключей-кандидатов не наблюдается, поэтому нельзя сказать, что они находятся в НФБК.
В 4НФ таблицы не могут находиться, так как они не находятся в НФБК.
Таким образом, все отношения находятся в 3НФ.
5. SQL–запросы
В данном курсовом проекте были выполнены следующие запросы:
По сотрудникам:
1. Вывести список сотрудников по определенной должности.
SELECT Personal. Fio as ‘ФИО’, Otdeleniya. Nazvanie_otdel as ‘Название отделения’
FROM Otdeleniya INNER JOIN Personal ON Otdeleniya. Kod_otdel = Personal.kod_otdel
WHERE Personal. Doljnost like «Должность»
order by Otdeleniya. Nazvanie_otdel
2. Вывести список сотрудников по должности, учитывая отделение.
SELECT Personal. Fio as ‘ФИО’, Otdeleniya. Nazvanie_otdel as ‘Название отделения’
FROM Otdeleniya INNER JOIN Personal ON Otdeleniya. Kod_otdel = Personal.kod_otdel
WHERE Otdeleniya. Nazvanie_otdel = «Отделение’ AND Personal. Doljnost like ‘Должность’
3. Вывести список сотрудников, оклад которых превышает n-й суммы
SELECT Personal. Fio as ‘ФИО’, Personal. Doljnost as ‘Должность’, Personal. Oklad as ‘Оклад’
FROM Personal
where Personal. Oklad> (Сумма)
4. Вывести общий список сотрудников.
SELECT Personal. Fio as ‘ФИО’, Personal.doljnost as ‘Должность’, otdeleniya.nazvanie_otdel as ‘Название отделения’
FROM Otdeleniya INNER JOIN Personal ON Otdeleniya. Kod_otdel = Personal. Kod_otdel
group by otdeleniya.nazvanie_otdel, Personal. Fio, Personal.doljnost
order by Personal. Fio
По пациентам:
1. Вывести список пациентов по определенному диагнозу.