УДК 004.9 (076) ББК 30.2-05я7 И41
Рецензент: канд. техн. наук, доцент кафедры «Вычислительная техника» Афанасьев Александр Николаевич. Одобрено секцией методических пособий научно-методического совета университета.
Информационное обеспечение и базы данных: методические указания к И41 выполнению лабораторных работ / сост. А. Ф. Похилько, А. В. Удовиченко. – Ульяновск: УлГТУ, 2006. – 64 с. Указания составлены в соответствии с программой дисциплины. Изложена методика выполнения лабораторных работ по теме и даны образцы решения задач. Работа подготовлена на кафедре «Системы автоматизированного проектирования» УлГТУ. Печатается в авторской редакции.
УДК 004.9 (076) ББК 30.2-05я7 © Оформление. УлГТУ, 2006 © Похилько А. Ф., Удовиченко А. В., составление, 2006
Учебное издание ИНФОРМАЦИОННОЕ ОБЕСПЕЧЕНИЕ И БАЗЫ ДАННЫХ Составители: ПОХИЛЬКО Александр Фёдорович УДОВИЧЕНКО Антон Владимирович Подписано в печать 30.03.2006. Формат 60×84/16 Бумага офсетная. Печать трафаретная. Усл. печ. л. 2,50. Уч.-изд.л. 2,00. Тираж 100 экз. Заказ Ульяновский государственный технический университет 432027, г. Ульяновск, ул. Сев. Венец, д.32. Типография УлГТУ, 432027, г. Ульяновск, ул. Сев. Венец, д.32.
2
ОГЛАВЛЕНИЕ ОБЩИЙ ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТ ЧАСТЬ 1: Табличные процессоры 1 ЛАБОРАТОРНАЯ РАБОТА N 1 1.1 Тема 1.2 Цель
6 7 7 7 7
1.3 1.4 1.5 1.5.1 1.5.2 1.5.3 1.5.4 1.6 1.7
1 часть л/р №1 Общие сведения Постановка задачи Содержание работы Начало Подбор параметра Варианты штатного расписания Штатное расписание хозрасчетной больницы Форма отчётности Контрольные вопросы
7 7 9 10 10 11 11 12 12 12
1.8 1.8.1 1.8.2 1.9 1.10 1.11 1.12
2 часть л/р №1 Общие сведения: Создание экранной или печатной формы Создание формы для ввода данных в список Постановка задачи Содержание работы Форма отчётности Контрольные вопросы
13 13 13 13 14 14 17 17
2 ЛАБОРАТОРНАЯ РАБОТА №2 2.1 Тема 2.2 Цель 2.3 Общие сведения: 2.3.1 Использование справочных данных 2.3.2 Создание сводной таблицы и диаграммы 2.4 Постановка задачи 2.5 Содержание работы 2.6 Форма отчётности 2.7 Контрольные вопросы
19 19 19 19 19 20 21 21 24 25
3 ЛАБОРАТОРНАЯ РАБОТА №3 3.1 Тема 3.2 Цель
26 26 26 3
3.3 3.3.1 3.3.2 3.3.3 3.4 3.5 3.6 3.7
Общие сведения Создание базы данных Работа с БД Связывание таблиц на схеме данных Постановка задачи Содержание работы Форма отчётности Контрольные вопросы
26 26 27 28 29 30 33 34
ЧАСТЬ 2: СУБД
35
4 ЛАБОРАТОРНАЯ РАБОТА №4 4.1 Тема 4.2 Цель 4.3 Общие сведения 4.3.1 Формы 4.3.2 Запросы 4.3.3 Отчёты 4.4 Задание 4.5 Порядок работы 4.6 Форма отчётности 4.7 Контрольные вопросы
35 35 35 35 35 36 36 37 37 39 40
5 ЛАБОРАТОРНАЯ РАБОТА №5 5.1 Тема 5.2 Цель 5.3 Задание 5.4 Варианты 5.5 Общие сведения 5.5.1 Основные понятия 5.5.2 Классификация сущностей 5.5.3 Связи 5.6 Содержание работы 5.7 Форма отчётности 5.8 Контрольные вопросы
41 41 41 41 42 43 43 44 45 46 48 49
6 ЛАБОРАТОРНАЯ РАБОТА №6 6.1 Тема 6.2 Цель 6.3 Задание 6.4 Общие сведения и порядок работы 6.4.1 Создание страниц доступа к данным 6.4.2 Подключение страницы доступа к данным к базе данных 6.4.3 Создание из базы данных Access страницы доступа к данным
50 50 50 50 50 50 50 51
4
6.4.5 6.4.6 6.4.7 6.4.8 6.4.9 6.4.10 6.4.11 6.5 6.6
одной таблицы Использование фильтра на странице доступа к данным Создание страницы доступа к данным двух таблиц Создание страницы доступа к данным в режиме конструктора Создание страниц с многоуровневой группировкой с помощью конструктора Работа с гиперссылками Создание гиперссылки на странице текущей базы данных на другую страницу, расположенную в той же базе данных Создание гиперссылки на существующий файл Форма отчётности Контрольные вопросы
ПРИЛОЖЕНИЕ 1 Классификация красок ПРИЛОЖЕНИЕ 2 Варианты заданий к лабораторной работе № 5
5
52 52 53 53 54 55 56 56 57 58 59
ОБЩИЙ ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТ 1. Внимательно прочитать методические указания к лабораторной работе. 2. В меню Пуск/Программы (или аналогичном) найти пиктограмму необходимого для работы программного продукта (Excel, Access) и запустить его. 3. Следуя содержанию и предъявляемым в ней требованиям выполнить лабораторную работу. 4. Подготовить отчётность в указанной форме. 5. Сохранить полученные результаты на индивидуальный съёмный носитель (дискету, Flash – карту и т.п.). 6. Предъявить полученные результаты преподавателю. 7. Ответить на предложенные преподавателем контрольные вопросы.
6
ЧАСТЬ 1: Табличные процессоры 1. ЛАБОРАТОРНАЯ РАБОТА N 1 1.1 Тема: Занесение, организация и простейшие приёмы работы с данными в табличном процессоре (EXCEL). 1.2 Цель: изучить принципы организации данных в табличном процессоре; научиться использовать различные способы адресации при обращении к ячейкам, освоить некоторые приемы оформления таблиц, закрепить навыки заполнения и редактирования таблиц; научиться создавать и использовать формы. 1 ЧАСТЬ Л/Р №1 1.3 Общие сведения: Создание электронных таблиц предполагает заполнение и редактирование документа. Базовые команды формирования таблиц можно разбить на две группы: - команды, изменяющие содержимое ячеек (ввод и редактирование данных, очистка, копирование); - команды, изменяющие структуру таблицы (удаление, вставка, перемещение фрагментов таблицы). Выполнение этих команд требует определенных навыков, связанных с выделением блоков ячеек, строк и столбцов. Чтобы выделить диапазон (блок) ячеек, нужно подвести указатель мыши к левому верхнему углу выделяемого блока, нажать левую клавишу мыши и, удерживая ее нажатой, передвинуть указатель мыши в правый нижний угол выделяемого блока и отпустить левую клавишу мыши - все ячейки диапазона будут окрашены. Для снятия выделения нужно щелкнуть мышкой в любом месте таблицы. Чтобы выделить два диапазона ячеек, нужно выделить сначала первый диапазон, нажать на клавишу Ctrl и, удерживая ее нажатой, выделить второй диапазон. Чтобы выделить отдельный столбец таблицы, нужно установить курсор на букве, обозначающей столбец, в верхней части рабочего листа и щелкнуть левой клавишей мыши. Аналогично выделяется строка таблицы. Если нажать 7
левую клавишу мыши и, не отпуская ее, "протащить" по номерам строк, то эти строки будут выделены. Принцип относительной адресации означает, что адреса ячеек, используемых в формулах, определены не абсолютно, а относительно места расположения формулы. Поэтому всякое изменение места расположения формулы (вследствие переноса, копирования и пр.) ведет к изменению адресов ячеек в этих формулах. Для копирования содержимого ячейки используется один из трех способов: 1) указатель мыши устанавливается в правый нижний угол копируемой ячейки, нажимается левая клавиша мыши и, не отпуская клавишу, протаскивается на нужное место; 2) указатель мыши устанавливается на копируемую ячейку, вызывается контекстное меню правой клавишей мыши и выбирается команда Копировать, затем выделяется нужный диапазон ячеек и нажимается клавиша ввода команды; 3) в основном меню выбирается команда Правка, затем в раскрывшемся меню команда Копировать. Обычно в ячейке находятся числовые значения, а формула не видна. Увидеть формулу в отдельной ячейке можно, если перевести эту ячейку в режим редактирования клавишей F2. Можно сделать видимыми все формулы таблицы одновременно, если перейти в режим отображения формул. Обычно в исходном состоянии (по умолчанию) таблица находится в режиме отображения значений, вычисленных по формулам. Для заполнения таблицы с использованием абсолютной адресации существуют два способа: 1) «Замораживание» адреса ячейки - использование символа $. Можно «заморозить» только номер строки (например, F$5) или только имя столбца ($F5). Чтобы сделать абсолютным адрес ячейки, нужно значок $ писать дважды ($F$5). 2) Использование имен ячеек. Ячейке назначается имя, которое используется затем в формулах. Имя должно начинаться с буквы русского или латинского алфавита и не должно быть похоже на адрес ячейки, оно не может содержать пробела. Для этого выбирается команда Вставка из основного меню, затем пункт Имя/Определить. Для оформления документов используется форматирование таблицы. Можно использовать различные шрифты, добавлять новые столбцы, выравнивание, автозаполнение, перемещение ячеек, изменять Формат ячеек с помощью контекстного меню, автоформатирование.
8
1.4 Постановка задачи: На данной практической работе вам необходимо составить штатное расписание хозрасчетной больницы. Представьте себя заведующим хозрасчетной больницей и должны составить штатное расписание, т.е. определить, сколько сотрудников, на какие должности и с каким окладом вы принимаете на работу. Общий месячный фонд зарплаты составляет некую фиксированную сумму. Пусть известно, что для нормальной работы больницы нужно 5-7 санитарок, 8-10 медсестер, 10-12 врачей, 1 заведующий аптекой, 3 заведующих отделениями, 1 главный врач, 1 заведующий хозяйством и 1 заведующий больницей. За основу берется оклад санитарки, а все остальные вычисляются исходя из него. Каждый оклад является линейной функцией от оклада санитарки: А*С+В, где С - оклад санитарки, А и В - коэффициенты. Например, медсестра должна получать в 1,5 раза больше санитарки (А=1,5,В=0); − врач - в 3 раза больше санитарки (В=0, А=3); − зав.отделением - на 30$ больше, чем врач (А=3, В=30); − зав.аптекой - в 2 раза больше санитарки (А=2, В=0); − зав.хозяйством - на 40$ больше медсестры (А=1,5,В=40); − гл.врач - в 4 раза больше санитарки (А=4, В=0); − зав.больницей - на 20$ больше гл. врача (А=4, В=20). Задав количество человек на каждой должности, можно составить уравнение: N1(A1*C+B1)+N2(A2*C+B2)+...+N8(A8*C+B8)=(Сумма фонда з/п), где N1-количество санитарок, N2-количество медсестер и т.д. В этом уравнении известны А1...А8 и В1...В8, а неизвестны С и N1...N8. Решить такое уравнение известными методами не удастся, его можно решить путем подбора. Примечание: здесь и далее размер зарплаты, а также прочих величин следует выбирать близким к реальным на текущий момент значениям с целью облегчения восприятия и анализа данных.
9
1.5 Содержание работы: 1.5.1 Начало 1. Заполните таблицу. Таблица 1.1 A
B
C
коэфф. А коэфф. В должность
D
E
F
G
З/П
количество суммарная зарплата
сотрудн.
сотрудн.
санитарка
7
медсестра
9
врач
10
зарплата
санитарки
зав.отделением зав.аптекой завхоз главврач зав.больницей Суммарный месячный фонд зарплаты
2. В столбце D вычислите заработную плату для каждой должности. Заработная плата вычисляется по формуле: A*C+B. В таблице коэффициенты А и В находятся в столбцах А и В, а С - зарплата санитарки указана в ячейке Н3. Обратите внимание! Столбец D должен заполняться формулами с использованием абсолютной ссылки на ячейку Н3.То есть Вы должны ячейке Н3 присвоить имя С, в ячейку D3 занести формулу =A3*C+B3 и скопировать эту формулу из ячейки D3 в D4:D10. 3. В столбце F вычислите заработную плату всех сотрудников данной должности, т.е. в ячейку F3 занесите формулу = D3*E3 (<зарплата>*<кол-во сотрудников>) и скопируйте ее в F4:F10, установив в столбцах D и F формат 0,00. 4. Определите суммарный месячный фонд заработной платы, т.е. просуммируйте столбец F c помощью кнопки суммирования и переместите значение суммы в ячейку F12. 5. Составьте штатное расписание. Вносите изменения в зарплату санитарки или меняйте количество сотрудников в ячейках Е3:Е5 до тех пор, пока 10
полученный суммарный месячный фонд зарплаты не будет равен заданной сумме. 6. Составьте штатное расписание с использованием функции автоматизации расчетов. 1.5.2 Подбор параметра Порядок действий: а) выбрать меню Сервис/Подбор параметра; б) в списке Установить в ячейке указать адрес ячейки реального суммарного фонда ($F$12); в) в списке Значение – сумму фонда з/п, к которой нужно приблизиться; г) в списке Изменить ячейку – указать ячейку, в которой указана базовая з/п ($H$3). 7. Составьте несколько вариантов штатного расписания с использованием функции Подбор параметра и оформите их в виде таблицы, изменяя количество сотрудников на должностях санитарки, медсестры и врача и подобрав зарплату санитарки в новых условиях. 1.5.3 Варианты штатного расписания Таблица 1.2 Варианты
Должность
Количество сотрудников
Базовая зарплата (санитарки)
. вариант 1
санитарка
6
медсестра
8
врач
10
.....
8. Проанализируйте полученные варианты и выберите и оформите один из них в виде таблицы (таблица 1.3). В таблице оставить столбцы C,D,E,F, а столбцы A,B,H выделить и выбрать пункт меню Формат/Столбец/Скрыть.
11
1.5.4 Штатное расписание хозрасчетной больницы Таблица 1.3 Зарплата
Кол-во
Суммарная
сотрудника
сотрудников
зарплата
санитарка
1500,00
6
9000,00
медсестра
2250,00
9
20250,00
врач
4500,00
10
45000,00
зав.отделением
0,00
3
0,00
зав.аптекой
0,00
1
0,00
завхоз
0,00
1
0,00
главврач
0,00
1
0,00
зав.больницей
0,00
1
0,00
суммарный месячный
фонд
зарплаты
74250,00
Должность
1.6 Форма отчётности к первой части л/р: 2. Показать несколько вариантов штатного расписания на разных листах. 3. Продемонстрировать как изменяются значения з/п и итоговых сумм в расписании в зависимости от изменений коэффициентов и базовой з/п. 4. Продемонстрировать работу функции Подбор параметра, подобрав для разных расписаний разные суммы фондов з/п изменяя базовую з/п или введённый дополнительный общий для всех сотрудников коэффициент (имеющий смысл например учёта инфляции). 5. Ответить на вопросы. 1.7 Контрольные вопросы к первой части: 1. Объясните назначение табличного процессора. 2. Принцип организации данных. 3. Расскажите структуру документов в Excel. 4. Как адресуются ячейки. Какие возможны типы данных в ячейках. 5. Расскажите основные функции для работы с ячейками и их группами. 6. Ввод формул. Функция подбора.
12
2 ЧАСТЬ Л/Р №1 1.8 Общие сведения: 1.8.1 Создание экранной или печатной формы Для создания личной формы нужно создать лист нужного формата, содержащий нужный текст и графику, а затем сохранить содержащую его книгу как шаблон. Для получения пустой копии данной формы следует создать новую форму на основе этого шаблона с помощью команды Создать в меню Файл. Существует возможность распечатать лист и заполнить форму на бумаге или сначала заполнить пустые ячейки, а потом распечатать форму. При заполнении формы в Microsoft Excel можно автоматизировать ввод и анализ данных. Нажмите кнопку Создать. Добавьте необходимые вопросы, названия, формулы и другие данные, которые должны присутствовать в форме. Отформатируйте ячейки листа. Для добавления флажков, переключателей и других элементов управления к форме, которая будет использоваться в Excel, служат элементы управления на панели инструментов Формы. Если форма будет использоваться в специальной программе Visual Basic для приложений, связанной с другими программами Office, Интернетом или Web, элементы управления добавляются с помощью панели инструментов Элементы управления. Если пользователи будут заполнять форму на компьютере, можно указать информацию, доступную для каждой записи. Для предотвращения нежелательных изменений электронной формы можно разрешить пользователям изменять и вводить данные только в указанные ячейки. Удалите все неиспользованные листы из книги. После завершения составления формы выберите команду Сохранить как в меню Файл. В окне Тип файла выберите пункт Шаблон (*.xlt). В поле Папка выберите папку, в которой должен храниться данный шаблон. В поле Имя файла введите имя шаблона, а затем сохраните его в папке или подчиненной папке шаблонов. 1.8.2 Создание формы для ввода данных в список Для вставки записей с помощью формы нужно указать ячейку списка, начиная с которой следует добавлять записи. В меню Данные/Форма/Создать. Далее введите данные новой записи (TAB для перемещения к следующему полю формы и SHIFT+TAB - к предыдущему). Чтобы добавить запись в список, нажмите клавишу ENTER. По завершении набора последней записи нажмите кнопку Закрыть, чтобы добавить набранную запись и выйти из формы. Примечание: если поле списка содержит формулу, то в форме выводится ее результат. Изменять это поле в форме нельзя; при добавлении записи, содержащей формулу, результат формулы не будет вычислен до нажатия 13
клавиши ENTER или кнопки Закрыть. 1.9 Постановка задачи: В данной лабораторной работе Вам необходимо создать 2 вида форм: экранную(печатную) и встроенную форму для заполнения списков. Создайте на основании штатного расписания форму, пригодную как для распечатки (т.е. для последующего её заполнения на распечатанном листе), так и для работы с другими расписаниями. Также создайте форму для подбора количества единиц персонала (примеры см. рис1.3., рис.1.4). Необходимо создать форму для заполнения списков сотрудников в штатном расписании. 1.10 Содержание работы: 1. Создайте (или используйте готовую из первой чати л/р) таблицу вида таблица.1.1. Используя форматирование, создайте из неё форму (Рис.1.1). Выполняется путём выделения цветом нужных ячеек, установкой соответствующих элементов управления, выделением границ рабочей области листа и последующим скрытием границ ячеек. При помещении элемента Счётчик через команду Формат объекта установите изменяемую ячейку (см. рис1.1). Аналогично изменяются свойства и других элементов.
Рис. 1.1. Формат элемента
Примечание: Элемент управления можно расположить в любом удобном месте (в том числе и на диаграмме на отдельном листе). Важно только правильно указать адрес(а) связанных с элементом ячеек. Включить панель можно поставив галку на Вид/Панели инструментов/Формы. 2. Удалите ненужные листы из книги, затем скройте сетку на листе формы (Сервис/Параметры/закладка «Вид»/Параметры окна снять галку напротив «сетка»). 3. Сохраните созданную форму как шаблон (Файл/Сохранить как… в выпадающем меню Рис.1.2 Создание книги на типов файлов выбрать Шаблон *.xlt ). Теперь базе шаблона при создании новой книги можно будет выбрать шаблон для документа (рис.1.2). 14
4. Сделайте форму подбора количества персонала на другом листе, используя элементы с панели форм. (пример рис.1.4).
Рис.1.3 Пример простой формы.
Рис.1.4 Пример использования элементов в формах.
Рис.1.5 Пример заполнения списка с помощью формы Получение значений ячеек с Лист1 для вычисления значений «Сумма» и 15
получения значения «Макс.» выполняется посредством функции СМЕЩ()(см. справку по функциям Excel) которая участвует в формуле при вычислении значения ячеек Таблица 1.4 Коэф. А
1,00 1,50 1,25 1,60 1,20 1,10 1,75
Коэф. B
100,00 700,00 150,00 200,00 100,00 100,00 500
Должность
Санитар(ка) Врач Медсестра Зав.отд. Зав.аптекой Зав.хоз. Гл.врач. Итого
З.П.
1433,00 2699,50 1816,25 2332,80 1699,60 1566,30 2832,75 14380,20
Кол-во Суммарно
5 4 6 2 1 1 1
7165,00 10798,00 10897,50 4665,60 1699,60 1566,30 2832,75 20
39624,75
1333
Макс. 10000,00 15000,00 15000,00 5000,00 2000,00 2000,00 3000,00 52000,00
5. Вызовите форму для заполнения списка единиц персонала. Для этого нужно выделить диапазон ячеек, в котором находится список, включая подписи к столбцам или строкам (подписи потом будут отображаться в обозначениях полей на форме). Выбрать пункт меню Данные/Форма… ). 6. Занесите нужное количество записей (см. пример рис.1.5). Примечание: Формы можно, например, использовать для создания визиток (беджиков) для персонала (см. рис.1.6). На рисунке показан пример как с помощью элемента Счётчик организуется занесение должности. Автоматическое занесение прочих данных организуется аналогично.
Рис.1.6 Пример использования формы 7. Для визуального отображения и сравнения з/п постройте диаграмму на отдельном листе (Вставка/Диаграмма). Тип диаграммы выберите исходя из соображений наглядности. Далее следуйте указаниям мастера создания диаграмм. На лист разместите элемент управления Счётчик, свяжите его с ячейкой
16
базовой з/п. Изменяя значение базовой з/п путём манипуляций со Счётчиком на листе с диаграммой, пронаблюдайте изменения соотношений долей зарплат в общем фонде. Пример см. на рис.1.7. При выделении диапазона, выделяйте данные так, чтобы в диапазон
Рис.1.7 Пример круговой диаграммы попали и категории сотрудников и их ставки и суммарные фонды каждой категории. Впоследствии, можно изменить свойства диаграммы так, чтобы она отображала либо ставки, либо и суммарные фонды каждой категории. 1.11 Форма отчётности: 1. Показать созданные на базе штатного расписания формы. 2. Продемонстрировать как изменяются значения з/п и итоговых сумм в расписании в зависимости от изменений количества единиц персонала. 3. Продемонстрировать размещение и использование элементов управления в формах. 4. Показать диаграмму и зависимость распределения на диаграмме от изменений производимых при помощи Счётчика. 5. Ответить на вопросы. 1.12 Контрольные вопросы ко второй части: 1. Какими способами можно производить анализ и обработку данных в Excel? 17
2. 3. 4. 5.
В чём основные удобства подобной (Excel) организации данных? Что такое форма. Как создать простейшую форму. Панель «Формы». Отображение. Содержимое. Как и какие элементы можно использовать для создания и оформления.
18
2. ЛАБОРАТОРНАЯ РАБОТА №2 2.1 Тема: Справочные данные. Организация данных в виде сводных таблиц и диаграмм. 2.2 Цель: Научиться организовывать данные, избегая их избыточности. Составлять сложные таблицы с использованием справочных данных. Уметь использовать сводные диаграммы и таблицы для анализа внесённых данных. 2.3 Общие сведения: 2.3.1 Использование справочных данных Справочник (справочная таблица и т.п.) – это обыкновенная таблица Excel один из столбцов (строк) которой используется как критерий поиска данных в других столбцах (строках) этой таблицы. При формировании справочных таблиц следует помнить: - «ключевой» реквизит (тот, по которому будет выбираться информация из справочника) всегда должен располагаться в первой графе (или первой строке*) справочной таблицы; - строки (столбцы*) справочной таблицы должны быть упорядочены в порядке возрастания «ключевого» реквизита. (* справедливо для «вертикальных» справочников). При выборке данных из справочника используется функция ВПР (ГПР – для «горизонтальных» спр. таблиц). ВПР(искомое_значение;инфо_таблица;номер_столбца;интервальный_пр осмотр) Искомое_значение - это значение, которое должно быть найдено в первом столбце массива. Искомое_значение может быть значением, ссылкой или текстовой строкой. Инфо_таблица - это таблица с информацией, в которой ищутся данные. Можно использовать ссылку на интервал или имя интервала, например, База Данных или Список. Если интервальный_просмотр имеет значение ИСТИНА, то значения в первом столбце аргумента инфо_таблица должны быть расположены в возрастающем порядке: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ВПР может выдать неправильный результат. Если интервальный_просмотр имеет значение ЛОЖЬ, то инфо_таблица не обязана быть сортированной.
19
Значения в первом столбце аргумента инфо_таблица могут быть текстами, числами или логическими значениями. Регистр не учитывается (т. е. строчные и заглавные буквы не различаются). Номер_столбца - это номер столбца в массиве инфо_таблица, в котором должно быть найдено соответствующее значение. Если номер_столбца равен 1, то возвращается значение из первого столбца аргумента инфо_таблица; если номер_столбца равен 2, то возвращается значение из второго столбца аргумента инфо_таблица и так далее. Если номер_столбца меньше 1, то функция ВПР возвращает значение ошибки #ЗНАЧ!; если номер_столбца больше, чем количество столбцов в аргументе инфо_таблица, то функция ВПР возвращает значение ошибки #ССЫЛ!. Интервальный_просмотр - это логическое значение, которое определяет, нужно ли, чтобы ВПР искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается приблизительно соответствующее значение; другими словами, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем искомое_значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д. Если ВПР не может найти искомое_значение и интервальный_просмотр имеет значение ИСТИНА, то используется наибольшее значение, которое меньше, чем искомое_значение. Если искомое_значение меньше, чем наименьшее значение в первом столбце аргумента инфо_таблица, то функция ВПР возвращает значение ошибки #Н/Д. Если ВПР не может найти искомое_значение и интервальный_просмотр имеет значение ЛОЖЬ, то ВПР возвращает значение ошибки #Н/Д. 2.3.2 Создание сводной таблицы и диаграммы Смысл создания сводных таблиц и диаграмм в наглядном отображении необходимого минимума данных, суммарных и итоговых сведений, структурированных нужным образом. Сводная таблица и/или диаграмма создаётся в книге, в которой требуется её создать с помощью мастера построения сводных таблиц (диаграмм) (меню Данные / Сводная таблица…). 1. На шаге1 выполнения мастера сводных таблиц и диаграмм установите переключатель Создать таблицу на основе данных, находящихся: в положение в списке или базе данных Microsoft Excel, а Вид создаваемого отчета в положение Сводная таблица. 2. На шаге 2 мастера укажите диапазон ячеек, в котором находятся исходные данные. 20
3. На шаге 3 решите, нужно ли создавать макет сводной таблицы заранее (это можно сделать позже). Также можно задать некоторые параметры сводной таблицы, нажав на кнопку Параметры. Выполните одно из следующих действий: Если на шаге 3 была нажата кнопка Макет, выполните формирование макета отчета, нажмите кнопку OK в диалоговом окне Мастер сводных таблиц и диаграмм – Макет, а затем кнопку Готово для создания отчета. Если кнопка Макет на шаге 3 не была нажата, нажмите кнопку Готово, а затем сформируйте макет отчета на листе. Сводную таблицу (как и диаграмму) рекомендуется создавать на отдельном листе. Формирование макета осуществляется перетаскиванием заголовков нужных полей на поля макета, специально отведённые под категории, по которым будет производиться обобщение см. рис.2.1. Результат такого действия см. таблицу 2.4. Для создания сводной диаграммы нужно на шаге 1 выбрать соответствующий пункт (Вид создаваемого отчёта – Сводная диаграмма). При создании сводной диаграммы Excel автоматически создает связанный отчет сводной таблицы. Макет сводной диаграммы создается по тому же принципу, что и сводной таблицы. 2.4 Постановка задачи: В данной л/р вам необходимо создать основную таблицу (см. пример таблица.2.1) для учёта продаж краски со склада. Причём данные для заполнения ячеек цвета, типа тары, объёма необходимо брать из справочных таблиц, которые также нужно создать на отдельном листе. Далее, информацию из таблицы продаж проанализировать: определить какой краски сколько продано, на какую сумму; какая краска наиболее продаваема; какая тара наиболее часто используется; какой самый большой объём краски был продан и т.д. Результаты анализа оформить в виде сводной таблицы, а для наглядности и удобства отображения - в виде диаграммы. Какие поля использовать в качестве исходных данных для построения диаграммы решите исходя из того, какую задачу вы поставили. Примечание: Информацию о красках берется из классификации красок в приложении 1. 2.5 Содержание работы: 1. Используя данные из приложения 1, создайте на отдельном листе две 21
справочные таблицы (пример таблица 2.2 и таблица 2.3). Таблицы после создания отформатируйте. В поле «Код» занесите неповторяющиеся числовые значения, так как функция выбора будет по этим значениям определять записи в справочной таблице. Кроме того, код для того и предназначен, чтобы однозначно идентифицировать объекты без перечисления большого количества их характеристик, определяющих его уникальность. 2. Создайте таблицу продаж красок (см. пример таблица 2.1). В поля, имеющие в таблице 1 обозначение «(Спр)» перед названием, данные не заносятся вручную, а выбираются из справочных таблиц с помощью функции ВПР (см. выше и пример на рис.2.2). Осуществляется это либо прямым набором текста функции, либо выбором из списка Вставка/Функция… Вручную вводятся значения полей Название, Код тары, Код краски, Партия. В поле «Сумма» вводится формула произведения объёма на количество. Таблица 2.1 Продажа красок Название Код Код (Спр)Тип (Спр)Об (Спр)Тип (Спр)Сто (цвет) тары краски тары ъём краски имость Партия Сумма Белый 150 111 баллон 0.50 КФ 50.00 34 850.00 Белый 175 113 баллон 0.75 ПФ 65.00 12 585.00 Белый 250 117 банка 5.00 ЭЦ 125.00 32 20000.00 Белый 220 111 банка 2.00 КФ 50.00 23 2300.00 Коричневый 215 112 банка 1.50 НЦ 65.00 4 390.00 Коричневый 150 121 баллон 0.50 МА 50.00 5 125.00 Красный 175 122 баллон 0.75 МА 50.00 20 750.00 Чёрный 175 117 баллон 0.75 ЭЦ 125.00 4 375.00 Чёрный 220 116 банка 2.00 МС 120.00 12 2880.00 Маренго 150 110 баллон 0.50 АЦ 75.00 3 112.50 Оранжевый 175 113 баллон 0.75 ПФ 65.00 7 341.25 Серебристый 250 116 банка 5.00 МС 120.00 6 3600.00 Жёлтый 210 111 банка 1.00 КФ 50.00 21 1050.00 Синий 210 118 банка 1.00 ПЭ 100.00 12 1200.00 Красный 215 110 банка 1.50 АЦ 75.00 56 6300.00 Красный 220 122 банка 2.00 МА 50.00 28 2800.00 Красный 220 120 банка 2.00 БТ 35.00 39 2730.00 Чёрный 175 112 баллон 0.75 НЦ 65.00 25 1218.75 Чёрный 210 114 банка 1.00 ФЛ 45.00 13 585.00 Маренго 175 119 баллон 0.75 ХВ 80.00 9 540.00 Зелёный 150 115 баллон 0.50 ЦГ 80.00 11 440.00 Итого: 49172.50
22
Таблица.2.3
Таблица.2.2 Код 150 175 210 215 220 250
Справочная таблица тары Тара Объём, кг. баллон 0.500 баллон 0.750 банка 1.000 банка 1.500 банка 2.000 банка 5.000
3. Проанализируйте, каких цветов краска наиболее продаваема; какие ёмкости наиболее используемы; какая краска даёт наибольшую сумму продаж. Для нужно создать сводную таблицу, а затем на её основании построить диаграмму. Вид диаграммы и критерии обобщения для сводной таблицы определяются исходя из того какие сведения нужно получить.
Справка уд. стоимости Удельная стоимость, Код: Тип руб./кг. 110 75.00 АЦ 111 50.00 КФ 112 65.00 НЦ 113 65.00 ПФ 114 45.00 ФЛ 115 80.00 ЦГ 116 120.00 МС 117 125.00 ЭЦ 118 100.00 ПЭ 119 80.00 ХВ 120 35.00 БТ 121 50.00 МА
Рис.2.1 Пример макета
Рис. 2.2 Функция ВПР Для каждого из выбранных (не меньше двух) вариантов анализа постройте свою сводную таблицу и диаграмму. 23
Таблица 2.4 (Спр)Тип тары Название (цвет) Данные баллон банка Белый Сумма по полю Сумма 1435 Сумма по полю Партия 46 Жёлтый Сумма по полю Сумма Сумма по полю Партия Зелёный Сумма по полю Сумма 440 Сумма по полю Партия 11 Коричневый Сумма по полю Сумма 125
Общий итог 22300 23735 55 101 1050 1050 21 21 440 11 390 515
Рис. 2.3 Пример диаграммы 2.6 Форма отчётности: Показать и продемонстрировать следующее: 1. Созданные, заполненные и отформатированные таблицы со справочными данными. 2. Созданную, отформатированную, заполненную таблицу продаж красок. 3. Результаты работы функции ВПР (как осуществляется заполнение данных о продаже красок). 4. Несколько вариантов (не меньше двух) сводной таблицы и сводной диаграммы, построенной на её основе. Сводные данные должны быть представлены в виде, соответствующем решаемой задаче анализа.
24
Например, для определения какой цвет и в какой таре приносит наибольшую долю продаж используются таблица 2.4 и диаграмма на рис.2.3 2.7 Контрольные вопросы: 1. 2. 3. 4. 5.
В чём смысл справочных данных? Как организуются справочные данные? Что такое сводная таблица? Принцип построения. Что такое сводная диаграмма? Принцип построения. Как осуществляется анализ данных с помощью сводных таблиц и диаграмм?
25
3. ЛАБОРАТОРНАЯ РАБОТА №3 3.1 Тема: Построение простой справочной системы. Связь между приложениями – табличным процессором и СУБД ( на примере Excel и Access). 3.2 Цель: Закрепить знания по построению информационных систем в табличных процессорах. Научиться импортировать и экспортировать данные между табличным процессором и СУБД (Excel и Access). Освоить базовые приёмы работы с СУБД Access. Сравнить возможности и удобство модификации данных в табличном процессоре и СУБД. 3.3 Общие сведения: СУБД Access применяется в тех случаях, когда прикладная задача требует хранения и обработки разнородной информации о большом количестве объектов и предполагает возможность многопользовательского режима. Электронные таблицы Excel являются удобным средством хранения ограниченного количества табличных данных с широкими возможностями выполнения расчетов и анализа данных. Документы Excel предназначаются, как правило, для индивидуального использования. 3.3.1 Создание базы данных Для создания новой базы данных необходимо воспользоваться областью задач Создание файла окна Microsoft Access. Эта область появляется справа при первом запуске Access. Если вы уже использовали Access для работы с какимлибо файлом и после его закрытия не видите в главном окне этой области, вы можете: − выбрать команду Создать из меню Файл; − нажать кнопку Создать на панели инструментов. − Открыть. Любое из этих действий приведет к открытию справа области задач Создание файла. При создании новой базы данных в процессе работы в Access прежняя база данных будет автоматически закрыта. Обратите внимание, что две базы данных в одном окне приложения Access одновременно открытыми быть не могут. Создать новую базу данных можно тремя способами: − путем создания новой пустой базы данных; − созданием копии существующей базы данных; 26
− с помощью создания базы данных с использованием шаблона. 3.3.2 Работа с БД Особым окном в Access является окно базы данных, которое позволяет получить доступ ко всем объектам базы данных и выбрать режим работы с объектом. В левой части окна находится панель объектов, которая содержит ярлыки для каждого из объектов Access: Таблицы, Запросы, Формы, Отчеты, Страницы, Макросы, Модули. Существуют 3 способа создания таблиц: с помощью конструктора таблиц; в режиме таблицы (вводом данных); с помощью мастера таблиц. Кроме того, можно импортировать таблицы извне и создавать связи с таблицами из внешнего файла. В режиме Таблицы осуществляется работа с данными, находящимися в таблице: просмотр, редактирование, добавление, сортировка и т. п. В режиме Конструктор создается или модифицируется структура таблицы, т. е. задаются имена полей таблицы и их типы, поля описываются, задаются их свойства. Рекомендуется при создании таблиц определять ключевое поле. Ключевое поле — это одно или несколько полей, комбинация значений которых однозначно определяет каждую запись в таблице. Если для таблицы определены ключевые поля, то Microsoft Access предотвращает дублирование или ввод пустых значений в ключевое поле. Ключевые поля используются для быстрого поиска и связи данных из разных таблиц при помощи запросов, форм и отчетов. В БД можно выделить три типа ключевых полей: счетчик, простой ключ и составной ключ. Для создания ключевого поля типа Счетчик необходимо в режиме Конструктора таблиц: − Включить в таблицу поле счетчика. − Задать для него автоматическое увеличение на 1. − Указать это поле в качестве ключевого путем нажатия на кнопку Ключевое поле на панели инструментов Конструктор таблиц. Для создания простого ключа достаточно иметь поле, которое содержит уникальные значения (например, коды или номера). Если выбранное поле содержит повторяющиеся или пустые значения, его нельзя определить как ключевое. Для определения записей, содержащих повторяющиеся данные, можно выполнить запрос на поиск повторяющихся записей. Если устранить повторы путем изменения значений невозможно, следует либо добавить в таблицу поле счетчика и сделать его ключевым, либо определить составной ключ. Составной ключ необходим в случае, если невозможно гарантировать уникальность записи с помощью одного поля. Он представляет собой комбинацию нескольких полей. Для определения составного ключа необходимо: 27
− Открыть таблицу в режиме Конструктор. − Выделить поля, которые необходимо определить как ключевые. − Нажать кнопку Ключевое поле на панели инструментов Конструктор таблиц. 3.3.3 Связывание таблиц на схеме данных Для того чтобы было удобно просматривать, создавать, удалять и модифицировать связи между таблицами, в MS Access используется Схема данных. Чтобы открыть схему данных, необходимо выполнить команду Сервис/Схема данных. По умолчанию схема будет содержать все таблицы со связями. Чтобы убрать какую-либо таблицу из схемы данных, необходимо щелкнуть правой кнопкой мыши на любом месте этой таблицы и из контекстного меню выбрать команду Скрыть таблицу. Удаление таблицы из схемы данных не означает удаление ее из базы данных, просто она не присутствует в схеме. Чтобы вновь добавить в эту схему свернутую таблицу или таблицу, у которой связи еще не установлены, необходимо щелкнуть правой кнопкой мыши на свободном пространстве схемы данных и из контекстного меню, выбрать команду Добавить таблицу, в диалоговом окне Добавление таблицы раскрыть вкладку Таблицы, выбрать из списка таблицу, и нажать кнопку Добавить. Связь между таблицами Access строит автоматически, когда две таблицы имеют одинаковые названия связанных полей и согласованные типы данных, причем хотя бы в одной из таблиц связанное поле является ключевым. Если нужная связь автоматически не создана, ее можно создать двумя путями. Первый путь — графический. Следует выбрать в главной таблице поле для связи, нажать левую кнопку мыши и перетащить поле во вторую таблицу. Отпустить левую кнопку мыши над тем полем подчиненной таблицы, с которым устанавливается связь. После этого появится диалоговое окно Изменение связей. Альтернативный вариант — выбрать команду Связи/Изменить связь из главного меню Access. Эта команда появляется в меню, когда открыто окно Схема данных. В диалоговом окне Изменение связей можно выбрать из списков названия связанных таблиц и полей для связывания. Если отношение между таблицами «один-ко-многим», то слева из списка Таблица/Запрос выбирается главная таблица и поле в этой таблице, а справа из списка Связанная таблица/Запрос — подчиненная и соответственно поле в ней. Если отношение «один-к-одному», то порядок таблиц значения не имеет. Если вы устанавливали связь графически, то все поля в списках уже выбраны, и нужно только определить правила ссылочной целостности. Для этого устанавливают флажок Обеспечение
28
целостности данных и один или оба флажка: Каскадное обновление связанных полей и Каскадное удаление связанных записей. При необходимости можно изменить параметры объединения, для чего требуется нажать кнопку Объединение и выбрать один из переключателей: - объединение только тех записей, в которых связанные поля обеих таблиц совпадают; - объединение всех записей из <имя первой таблицы> и только тех записей из <имя второй таблицы>, в которых связанные поля совпадают; - объединение всех записей из <имя второй таблицы> и только тех записей из <имя первой таблицы >, в которых связанные поля совпадают. По умолчанию устанавливается первый переключатель. Нажать кнопку ОК. Когда создается новая связь, можно также воспользоваться кнопкой Новое и в окне Создание ввести имена связываемых таблиц и имена полей, используемых для связи. Нажать кнопку ОК. После установки всех параметров связи необходимо нажать кнопку ОК в окне Параметры объединения и закрыть окно Схема данных, нажав на кнопку Закрыть в правом верхнем углу окна. 3.4 Постановка задачи: Создать в Excel справочную систему для определения характеристик краски по её обозначению. Пример см . рис.3.1. Механизм работы системы следующий: при выборе элементов кода материала из выпадающих списков, они появляются в выделенной цветом области листа, а соответствующие им расшифровки – в области «Описание». Смысловое содержание полей аналогично стандарту обозначений (см. Приложение 1).
Рис.3.1. Пример справочной системы. Примечание: Для облегчения последующего импорта данных в СУБД следует задать имена диапазонам данных. Данное действие производится выделением
29
диапазона а затем командой меню Вставка/Имя/Присвоить осуществляется присвоение имени диапазону. Следующим шагом является освоение азов работы с СУБД Access. Необходимо создать новую базу данных и импортировать в неё данные из книги Excel, содержащей справочную систему. Данные из справочных таблиц поместить в соответсятвующие таблицы Access. Новые таблицы получаются ипортом данных (Файл / Внешние даные / Импорт или в окне базы даных закладка Таблицы / Создать / Импорт). При импорте удобно выбирать в мастере импорта опцию Именованные диапазоны, и, если они были поименованы ранее, будут отображены для того, чтобы их можно было удобно выбрать. Примечание: Если в таблицах Excel использовалось объединение ячеек, то при переносе данных в Access в автоматически созданных таблицах могут появиться пустые столбцы. Во избежание путаницы их надо удалить. Далее следует создать таблицу, в которую будут заноситься данные о красках. Каждая запись этой таблицы должна представлять собой аналог обозначения лакокрасочного материала (см. Приложение 1). Заполнение полей записи этой таблицы должно осуществлять из справочных таблиц, например подстановкой значений из выпадающего списка. Основная цель создания этой таблицы – организовать подобие справочной системы в Excel, но с возможностью занесения множества записей, а также их сортировки, поиска и пр. К примеру, добавьте поле «партия», в которое будет заноситься количество проданной краски. Важно понять разницу между хранением данных в произвольном табличном виде совместно с методами их обработки (табличный процессор) и отдельным, формализованным и структурированным хранением данных и способов их обработки. Для этого внесите изменения в справочной системе в Excel – например, удалите некоторые записи из одной или нескольких справочных таблиц и проверьте работу системы, когда совершается выбор отсутствующей записи. Аналогичную операцию проделайте в справочной системе, сделанной в Access. 3.5 Содержание работы: 1. Создайте (и/или используйте существующие) таблицы справочных данных (расшифровок кодовых обозначений). Исходные данные возьмите из Приложения 1 или спросите у преподавателя уже подготовленные исходные данные. 2. Разместите на листе элементы управления Поле со списком с панели Формы аналогично рис.3.1, т.е. в соответствии с очерёдностью следования элементов обозначения. Укажите в качестве диапазона значений диапазон ячеек, 30
содержащих те справочные данные, которые нужно отображать. Ячейку, с которой будет связан элемент управления, укажите ту, в которой будет содержаться порядковый номер выбранного пункта списка. Эти ячейки в последствии скрываются (например, на рис.3.2 результат выбора из списка заносится в ячейки между выделенными цветом и полями со списком).
Рис.3.2 Пример форматирования элемента 3. Данные в выделенные ячейки заносятся функцией ВПР().Для функции в качестве аргумента для поиска укажите ячейку, куда заносятся данные элементом управления Поле со списком. В качестве диапазона – таблицу с соответствующими справочными данными. В качестве искомого значения (первого параметра) можно использовать аббревиатуры и обозначения, а можно в столбце левее таблицы проставить числа (пронумеровать записи) и потом скрыть столбец. Отформатируйте ячейки с данными, так, чтобы было наглядно видно обозначение материала. Для этого примените форматирование ячеек (изменение шрифта, цвет заливки и т.п.). 4. Информацию в блок «Описание» занесите с помощью ввода в ячейки функции ВПР(). Отличие от предыдущего пункта при внесении функции в последнем аргументе, указывающем из какого столбца диапазона выбрать значение. 5. Проведите окончательную «доводку» системы до презентабельного вида. Добавьте другие возможности по желанию. 6. Запустите MS Access. Создайте новую базу данных. Осуществляется это действие при помощи мастера создания/открытия БД, при запуске СУБД, либо после выбором пункта меню Файл / Создать, выбрав пункт Новая база данных. 7. Далее импортируйте данные из справочных таблиц Excel в таблицы БД Access. Для этого воспользуйтесь мастером импорта. 31
8. В окне БД нажмите кнопку Создать, затем в открывшемся окне в списке вариантов выберите Импорт таблиц. В открывшемся диалоговом окне в выпадающем списке Тип файлов выберите тип файлов Microsoft Excel (*.xls). Найдите файл, в котором выполняли предыдущую часть работы. Выделите его и нажмите ОК. 9. В открывшемся окне Импорт электронной таблицы выберите Импорт именованных диапазонов и выберите первый из них (если в списке именованные диапазоны не отображаются, то поименуйте их как указано в начале работы). Нажмите Далее. 10. Если в именованный диапазон были включены заголовки столбцов, то установите соответствующий флажок в этом окне. Нажмите Далее. 11. Выберите, что данные нужно сохранить в новой таблице. Нажмите Далее. 12. Задайте имя отражающее смысл содержимого для каждого непустого поля (имя аналогично обозначениям в справочной системе в Excel). Пустые поля удалите. Нажмите Далее. 13. Определите нужен ли ключ для этой таблицы (здесь и в дальнейшем для отсутствия дублирования данных будем считать что нужен). Если поле сокращения содержит уникальные значения (а данном конкретном случае так и есть), то можно выбрать в качестве ключевого поле, содержащее сокращения. Если просто нужно отсутствие дублирования данных, то можно позволить системе самой создать ключ. Нажмите Далее. 14. Задайте имя таблице. Имя должно отражать смысл содержимого таблицы. Можно оставить совпадающим с именем диапазона. 15. Повторите действия п.8-п.14 для всех диапазонов справочных данных. 16. Создайте новую таблицу (назовём её таблицей продаж) в режиме конструктора, в ней создайте ключевое поле и столько полей, сколько позиций в схеме классификации красок. Названия полей следует выбирать отражающие смысл содержимого поля. Тип полей (кроме ключевого) задайте текстовым. 17. С помощью Мастера подстановки (Режим Конструктор / Тип данных / Мастер подстановки…) задайте для поля таблицу, из которой будут выбираться значения. - на первом шаге укажите, что значения будут выбираться из таблицы или запроса, а не вводиться фиксированным списком; - на втором шаге выберите таблицу, из которой будет производиться выборка данных; - затем выберите поля, значения которых будут включены в столбец подстановки; - на четвёртом шаге можно указать скрывать или нет ключевое поле таблицы; - задайте название столбца подстановки. После указанных действий мастером будет создано поле, значения которого Рис.3.3. Пример поля д 32
можно выбирать из списка, хранящегося в другой таблице базы. 18. Впоследствии, можно указать другое поле для подстановки значения из справочной таблицы. 19. Поставьте на закладке Подстановка в свойствах поля присоединённым столбцом, тот, который содержит текстовую расшифровку кода (рис.3.4). В итоге, каждое поле должно отображаться аналогично как на рис.3.3. 20. Повторите действия для каждого поля. 21. Задайте поле себестоимости вводимого материала за единицу массы. 22. Проверьте работу новой заготовки справочной системы на базе СУБД путём заполнения нескольких записей в созданной таблице продаж красок. 23. Для сравнения удобства обращения со справочными данными в табличном процессоре и СУБД проведите их модификацию и там и там, и сравните затраты времени на действия, сравните функционирование систем после модификации данных.
Рис.3.4 Изменения свойств поля подстановки 3.6 Форма отчётности: 1. Представить проиндексированные и отформатированные таблицы со справочными данными. 2. Показать работающую версию информационной системы (Excel). Продемонстрировать её работу. 3. По указанию преподавателя произвести модификацию данных, изменения в справочной системе (Excel). Продемонстрировать
33
4. 5. 6. 7.
результаты изменений. Представить импортированные в СУБД данные. Продемонстрировать умение производить импорт данных в БД. Показать функционирующую таблицу продаж красок. Произвести сравнение возможностей и удобства модификации данных в СУБД и табличном процессоре. Ответить на контрольные вопросы.
3.7 Контрольные вопросы: 1. 2. 3. 4. 5. 6. 7.
Назначение и принцип построения справочных систем. Различия в хранении и организации данных в Excel и Access. Назначение СУБД. Разница в назначениях СУБД и табличного процессора. Для чего служит функция ВПР (ГПР)? Расскажите и продемонстрируйте работу этих функций. Для чего служит и как работает элемент управления Поле со списком?
34
ЧАСТЬ 2: СУБД 4. ЛАБОРАТОРНАЯ РАБОТА №4 4.1 Тема: Основы работы с СУБД. Организация ввода и отображения информации. 4.2 Цель: Закрепить основные и освоить новые приёмы работы в СУБД Access. Научиться вводить, организовывать, отбирать и отображать данные. 4.3 Общие сведения: 4.3.1 Формы Формы служат для наглядного и удобного ввода данных в таблицы БД. На формах может находиться дополнительная информация, поясняющая смысл вводимых данных и контекст её использования. В СУБД Access существует несколько способов создания форм: - с помощью Автоформы; - с помощью мастера; - без помощи мастера; - на базе нескольких таблиц. С помощью автоформ можно создавать формы, в которых выводятся все поля и записи базовой таблицы или запроса. Если выбранный источник записей имеет связанные таблицы или запросы, то в форме также будут присутствовать все поля и записи этих источников записей. С помощью мастера создаются формы, в которых нет необходимости отображать все данные, а только нужна небольшая настройка формы под нужный формат. Без помощи мастера создаются формы, требующие специфической отдельной настройки отображения незначительной части данных из источника данных. Формы на базе нескольких таблиц используются, как правило, для отображения данных из нескольких связанных таблиц. Формы создаются выбором в окне базы данных закладки Формы, где после нажатия кнопки Создать откроется окно выбора способа создания формы. В выпадающем списке, если необходимо, выбирается источник данных, которые будет отображать форма и которые будут (если возможно) с её помощью заноситься и модифицироваться. Основными способами всё же являются создание формы с помощью конструктора и с помощью мастера, поэтому эти варианты вынесены отдельно ещё и в область окна базы данных, отображающую список имеющихся форм. 35
Далее, либо следуя шагам мастера, либо создавая форму вручную, указываются поля, которые необходимо отобразить на форме, производится их размещение, занесение дополнительной информации, других элементов управления и т.п. 4.3.2 Запросы Запросы являются основным средством для выборки, отображения и модификации разнородной информации и/или информации из разных источников (таблиц и других запросов). Запросы создаются выбором в окне базы данных закладки Запросы, где после нажатия кнопки Создать откроется окно выбора способа создания запроса. В выпадающем списке, если необходимо, выбирается источник данных, которые будет отображать запрос. Внешне запрос выглядит аналогично обыкновенной таблице. Использование его также аналогично использованию таблицы. Примечание: При создании запроса из нескольких таблиц необходимо, чтобы они были связаны между собой, т.к. в противном случае неясно, по какому критерию отбирать записи из других источников и как их присоединять к первому. 4.3.3 Отчёты Отчёты предназначены для наглядного отображения информации на экран и перевода отображаемых данных в твёрдую копию (распечатка). Отчёты создаются выбором в окне базы данных закладки Отчёты, где после нажатия кнопки Создать откроется окно выбора способа создания отчёта. В выпадающем списке, если необходимо, выбирается источник данных, которые будет отображать отчёт. При создании отчёта с помощью мастера, на одном из шагов можно выбрать уровни группировки данных (аналогично сводной таблице в Excel). После выбора полей для уровней группировки, мастер отчётов разбивает данные по выбранным полям как по категориям. Примечание: В окнах мастеров создания форм, запросов и отчётов, при выборе полей источника данных, после перенесении полей выбранного в данный момент источника данных из левой панели в правую, в выпадающем списке можно выбрать другой источник и таким же способом добавить поля в правую панель. Т.е. таким образом использовать данные из нескольких источников в одной форме (запросе, отчёте).
36
4.4 Задание: За основу взять данные, импортированные в СУБД из табличного процессора на предыдущей лабораторной работе, и таблицу продаж красок. Организовать ввод данных в справочную систему, для чего создать две формы для ввода красок на условном складе и для заполнения таблицы продаж красок. Сформировать отчётность о продажах красок. Сделать выборку данных о продажах и наличии на складе тех или иных лакокрасочных материалов, на основании которой также сформировать отчёт(ы). Смысл выборки данных определите сами (например, отобрать данные о продажах за один месяц, суммой более определённого значения). 4.5 Порядок работы: 1. Создайте (или используйте созданную в предыдущей работе) таблицу красок, имеющихся в наличии на условном складе. Необходимые поля: название материала, сокращённое обозначение плёнкообразователя, обозначение по преимущественному значению, дополнительные цифры, тип (горячей сушки и т.п.). Введите поле себестоимости данной краски. 2. Для заполнения таблицы красок создайте форму (см. рис.4.1). Для этого в окне БД выберите закладку Формы и в режиме конструктора создайте бланк формы. На него поместите элементы типа Поле со списком. В качестве таблицы, в которую будут помещаться данные поля выберите таблицу красок. В качестве источника строк выберите одну из справочных таблиц. Задайте столбец, значение которого будет сохраняться в поле таблицы красок при выборе значения из списка. Повторите операцию для всех полей, для которых существуют справочные данные. Для ввода других полей можно либо просто создать поле, связанное с таблицей, либо перетащить его на форму из плавающего окошка, в котором отображаются поля таблицы красок. Сохраните форму. Перейдите в режим формы и заполните несколько (710) записей таблицы красок с помощью формы. 3. Создайте таблицу продаж красок. Поля: <ключевое>, <код краски>, <количество единиц объема проданной краски>, <дата продажи>. Создайте форму для заполнения этой таблицы. Форма создаётся аналогично первой, но в выпадающем списке для кода краски задайте для отображения столько
Рис.4.1. Форма ввода красок
37
столбцов, сколько их в таблице красок. См. рис.4.3. Сохраните форму. Перейдите из режима конструктора в режим формы. Заполните несколько записей таблицы продаж (10-15).
Рис.4.2 Форма продаж красок
Рис.4.3 Пример ввода в форму продаж 4. Создайте запрос о продажах краски. Для этого в окне БД выберите закладку Запросы. Создайте запрос в режиме конструктора. В поле окна конструктора запросов, где отображаются таблицы (верхнее) перетащите из окна БД таблицу продаж и таблицу красок. При необходимости свяжите ключевое поле ТБД красок с полем кода краски в ТБД продаж. Создайте вычисляемое поле, которое будет отображать сумму, на которую было продано данной краски. Для этого вставьте столбец и в ячейке строки «поле» введите выражение: Сумма: [Краски].[Себестоимость]*[Продажа красок].[Продано_ед_объема] (имена полей задайте свои) или постройте выражение нажав правую клавишу мыши в строке «Поле» конструктора запросов в новом свободном столбце и выбрав пункт меню Построить. Пример построения запроса см .рис.4.4. Для отображения выберите все поля обеих таблиц. 5. Создайте отчёт. Для этого в окне БД выберите закладку «Отчёты». Создайте отчёт в режиме мастера отчётов. В качестве источника данных выберите запрос. На одном из шагов добавьте уровни группировки (см. рис.4.5). На одном из следующих шагов мастера нажмите кнопку Итоги и выберите категорию, по которой будут подводиться итоги. Пример см.рис.4.6. 6. Создайте несколько отчётов на основе запросов к созданной БД продаж лакокрасочных изделий. 38
Рис.4.4 Окно конструктора запросов
Рис.4.5 Добавление уровней группировки 4.6 Форма отчётности: 1. Представить рабочую справочную систему. 2. Продемонстрировать работу системы. Показать занесение и модификацию данных с помощью форм. 3. Построить содержательный по смыслу запрос и создать на его основе отчёт. 4. По заданию преподавателя выполнить те или иные действия по модификации данных или системы. 5. Ответить на контрольные вопросы.
39
Рис.4.6 Пример отчёта 4.7 Контрольные вопросы: 1. 2. 3. 4. 5.
С какой целью создаются экранные формы (отчёты, запросы)? Какие разновидности форм (отчётов, запросов) допускаются в Access? Что может быть источником данных для формы (отчёта, запроса)? Из каких разделов и элементов может состоять экранная форма? Уровни группировки. Определение. Смысл.
40
5. ЛАБОРАТОРНАЯ РАБОТА №5 5.1 Тема: Разработка и реализация в реляционной базе данных инфологической модели предметной области информационной системы 5.2 Цель: Научиться анализировать и моделировать предметную область. Реализовывать в виде таблиц реляционной БД. Связывать таблицы и поддерживать целостность данных. 5.3 Задание: на 1 часть работы 1. Придумать или выбрать один из предложенных вариантов предметной области. Дополнительные варианты заданий см. Приложение 1. Примечание: в случае самостоятельного выбора предметной области сложность должна быть не ниже чем у предложенных вариантов. 2. Разработать инфологическую модель БД. модель должна обеспечивать: - минимальную избыточность данных; - целостность данных; - модификацию данных; - смысловую целостность и связность (к примеру нет смысла указывать наименование поставщиков продуктов в таблице рецептов и т.п.). Должно присутствовать не менее 3 сущностей (стержневых). Число записей в таблицах БД не менее 5-10. на 2 часть работы 3. 4. 5. 6.
Реализовать модель в виде реляционной БД с помощью MS Access. Создать набор форм для ввода данных. Заполнить БД. Создать несколько запросов, отчётов, модифицировать данные и убедиться в обеспечении целостности БД. Отчёты и запросы должны быть осмысленными. Например, для ПО «Ресторан» создать среди прочих отчёт «Меню», где указывалось бы название блюда, стоимость, масса, калорийность и т.п.
41
5.4 Варианты: 1. «Ресторан». Даны поставщики продуктов: атрибуты: имя фирмы; тип (ЗАО, ООО, …); контактная информация (телефон, адрес, ...). Даны продукты: имя, поставщик, себестоимость, наличие на складе, … . Даны рецепты блюд: продукт, кол-во продукта, время приготовления, прочие условия, стоимость. Необходимо также предусмотреть план закупок продуктов (у каких поставщиков, сколько, за какую стоимость, суммарно и т.д.), учёт отпуска рецептов по датам, количеству. Страны и города в адресах поставщиков выделить в отдельные сущности. 2. «Малое предприятие». Даны список выпускаемой продукции: наименование, цена, количество, … . Даны материалы, использующиеся на предприятии: (наименование, цена, поставщик, прочие характеристики. Даны поставщики: атрибуты: имя фирмы; тип (ЗАО, ООО, …); контактная информация (телефон, адрес, ...), поставляемый материал, …. Даны заказчики: атрибуты: имя фирмы; тип (ЗАО, ООО, …); контактная информация (телефон, адрес, ...), закупаемая продукция, количество, … . Необходимо предусмотреть план закупок материалов в зависимоти от количества продаж. Страны и города в адресах потавщиков и заказчиков выделить в отдельные сущности. 3. «Учебное заведение». Даны преподаватели: (ФИО, должность, звание, кафедра и т.п.). Даны кафедры (наимнование, сотрудники, …). Дан список групп (обозначение специальность, кол-во студентов, …). Даны специальности: (наименование, кафедры, преподаватели, группы студентов). Необходимо предусмотреть простановку оценок и учёт прохождения аттестации группами. Также учёт З/П преподавателей в зависимости от кол-ва ведомых предметов и групп (преподаватель может курировать одну группу и вести несколько предметов).
42
5.5 Общие сведения: Реальным средством моделирования данных является не формальный метод нормализации отношений, а так называемое семантическое моделирование. В качестве инструмента семантического моделирования используются различные варианты диаграмм сущность-связь (ER - EntityRelationship). Диаграммы сущность-связь позволяют использовать наглядные графические обозначения для моделирования сущностей и их взаимосвязей. Различают концептуальные и физические ER-диаграммы. Концептуальные диаграммы не учитывают особенностей конкретных СУБД. Физические диаграммы строятся по концептуальным диаграммам и представляют собой прообраз конкретной базы данных. Сущности, определенные в концептуальной диаграмме становятся таблицами, атрибуты становятся колонками таблиц (при этом учитываются допустимые для данной СУБД типы данных и наименования столбцов), связи реализуются путем миграции ключевых атрибутов родительских сущностей и создания внешних ключей. При правильном определении сущностей, полученные таблицы будут сразу находиться в 3НФ. 5.5.1 Основные понятия
Цель инфологического моделирования - обеспечение наиболее естественных для человека способов сбора и представления той информации, которую предполагается хранить в создаваемой базе данных. Поэтому инфологическую модель данных пытаются строить по аналогии с естественным языком (последний не может быть использован в чистом виде из-за сложности компьютерной обработки текстов и неоднозначности любого естественного языка). Основными конструктивными элементами инфологических моделей являются сущности, связи между ними и их свойства (атрибуты). Сущность - любой различимый объект (объект, который мы можем отличить от другого), информацию о котором необходимо хранить в базе данных. Сущностями могут быть люди, места, самолеты, рейсы, вкус, цвет и т.д. Необходимо различать такие понятия, как тип сущности и экземпляр сущности. Понятие тип сущности относится к набору однородных личностей, предметов, событий или идей, выступающих как целое. Экземпляр сущности относится к конкретной вещи в наборе. Например, типом сущности может быть ГОРОД, а экземпляром - Москва, Киев и т.д. Атрибут - поименованная характеристика сущности. Его наименование должно быть уникальным для конкретного типа сущности, но может быть 43
одинаковым для различного типа сущностей (например, <цвет> может быть определен для многих сущностей: <собака>, <автомобиль>, <дым> и т.д.). Атрибуты используются для определения того, какая информация должна быть собрана о сущности. Примерами атрибутов для сущности <автомобиль> являются <тип>, <марка>, <номерной знак>, <цвет> и т.д. Здесь также существует различие между типом и экземпляром. Тип атрибута <цвет> имеет много экземпляров или значений: <красный>, <синий>, <банановый>, <белая ночь> и т.д., однако каждому экземпляру сущности присваивается только одно значение атрибута. Абсолютное различие между типами сущностей и атрибутами отсутствует. Атрибут является таковым только в связи с типом сущности. В другом контексте атрибут может выступать как самостоятельная сущность. Например, для автомобильного завода <цвет> - это только атрибут продукта производства, а для лакокрасочной фабрики <цвет> - тип сущности. Ключ - минимальный набор атрибутов, по значениям которых можно однозначно найти требуемый экземпляр сущности. Минимальность означает, что исключение из набора любого атрибута не позволяет идентифицировать сущность по оставшимся. Связь - ассоциирование двух или более сущностей. Если бы назначением базы данных было только хранение отдельных, не связанных между собой данных, то ее структура могла бы быть очень простой. Однако одно из основных требований к организации базы данных - это обеспечение возможности отыскания одних сущностей по значениям других, для чего необходимо установить между ними определенные связи. А так как в реальных базах данных нередко содержатся сотни или даже тысячи сущностей, то теоретически между ними может быть установлено более миллиона связей. Наличие такого множества связей и определяет сложность инфологических моделей. 5.5.2 Классификация сущностей
Рис. 5.1. Элементы расширенного языка ERд Стержневая сущность (стержень) - это независимая сущность, существующая как отдельное, независимое от других понятие. Ассоциативная сущность (ассоциация) - это связь вида "многие-комногим" ("-ко-многим" и т.д.) между двумя или более сущностями или экземплярами сущности. Ассоциации рассматриваются как полноправные сущности: они могут участвовать в других ассоциациях и обозначениях точно 44
так же, как стержневые сущности; могут обладать свойствами, т.е. иметь не только набор ключевых атрибутов, необходимых для указания связей, но и любое число других атрибутов, характеризующих связь. Характеристическая сущность (характеристика) - это связь вида "многие-к-одной" или "одна-к-одной" между двумя сущностями (частный случай ассоциации). Единственная цель характеристики в рамках рассматриваемой предметной области состоит в описании или уточнении некоторой другой сущности. Необходимость в них возникает в связи с тем, что сущности реального мира имеют иногда многозначные свойства. Книга может иметь несколько характеристик переиздания (исправленное, дополненное, переработанное, ...) и т.д. Обозначающая сущность или обозначение - это связь вида "многие-кодной" или "одна-к-одной" между двумя сущностями и отличается от характеристики тем, что не зависит от обозначаемой сущности.
Рис. 5.2. Инфологическая модель базы данных " " 5.5.3 Связи Каждая связь может иметь один из следующих типов связи: - связь типа один-к-одному означает, что один экземпляр первой сущности (левой) связан с одним экземпляром второй сущности (правой). Связь один-к-одному чаще всего свидетельствует о том, что на самом деле мы имеем всего одну сущность, неправильно разделенную на две. - связь типа один-ко-многим означает, что один экземпляр первой сущности (левой) связан с несколькими экземплярами второй сущности (правой). Это наиболее часто используемый тип связи. Левая сущность (со стороны "один") называется родительской, правая (со стороны "много") - дочерней. - связь типа много-ко-многим означает, что каждый экземпляр первой сущности может быть связан с несколькими экземплярами второй сущности, и каждый экземпляр второй сущности может быть связан с несколькими экземплярами первой сущности. Тип связи много-ко-многим является временным типом связи, допустимым на ранних этапах 45
разработки модели. В дальнейшем этот тип связи должен быть заменен двумя связями типа один-ко-многим путем создания промежуточной сущности. Каждая связь может иметь одну из двух модальностей связи: - модальность "может" означает, что экземпляр одной сущности может быть связан с одним или несколькими экземплярами другой сущности, а может быть и не связан ни с одним экземпляром. - модальность "должен" означает, что экземпляр одной сущности обязан быть связан не менее чем с одним экземпляром другой сущности. Связь может иметь разную модальность с разных концов. Вариант 1
Вариант 2
Рис.5.3 Обозначение связей на диаграммах Описанный графический синтаксис позволяет однозначно читать диаграммы, пользуясь следующей схемой построения фраз: <Каждый экземпляр сущности 1> <модальность связи> <наименование связи> <тип связи> <экземпляр сущности 2>. Каждая связь может быть прочитана как слева направо, так и справа налево. Например, для сотрудников некоторой фирмы: слева направо - "каждый сотрудник может иметь несколько детей", а справа налево - "Каждый ребенок обязан принадлежать ровно одному сотруднику". 5.6 Содержание работы: 1. Выберите предметную область (ПО). Предметная область выбирается исходя из ваших предпочтений. Для примера допустим, что у нас есть некая организация, реализующая товары, производимые в некоторых городах, через сеть торговых фирм. 2. Проанализируйте и выделите основные сущности ПО, их связи и пр. Итак, независимыми (стержневыми) сущностями являются <Фирмы>, <Товар>. Ассоциативной будет сущность <Заказы>, которая будет связывать экземпляры сущностей <Фирмы> и <Товары>. Характеристической сущностью будет <Тип фирмы> ОАО, ЗАО и т.п. (т.к. здесь сущность зависит от стержневой сущности). Обозначающей сущностью будет <Города>, т.к. она не 46
зависит от <Фирмы> и может также соотноситься с товаром (если бы его № лиц. Адрес № зак. Код Ф. поставляли вНазвание другой город) или с заказами. Код Ф.
Тип Ф. 3. Определите атрибуты сущностей и связей, ключи и т.п. Дата Допустим, что атрибутами для каждой сущности будут: Фирмы Заказы Кол-во M 1 номер M лицензии, код; - для Фирм: название, адрес в городе, N быть может, внутренний код - для Товаров:1 название, срок годности, цена и, Код тов. товара; Тип Ф. - для Заказов: номер, дата, количество; 1 Наимен. - для Городов: название. M 1 Тип Ф. Товар Кроме перечисленных, Города у сущностей добавятся атрибуты, являющиеся Цена часть ключа связанной сущности, если она является дочерней. Срок находится (по В городе может Назв. быть несколько фирм, ноНазв. каждая фирма Код тов. города годн. города крайней мере, зарегистрирована) только в одном городе, поэтому Фирмы и Города свяжутся как «многие(со стороныER-диаграммы фирм)-к-одному(со стороны городов). Рис.5.4 Пример Кроме того, фирма должна быть в городе, а вот город фирм может не иметь (см. понятие модальности). Также Тип фирмы свяжется с Фирмами как «один-комногим», т.к. фирма может быть только одного типа, а фирм определённого типа может быть много. Здесь и далее типы связей и модальности определите сами. Ключевыми атрибутами для сущностей будут атрибуты или совокупности атрибутов, однозначно идентифицирующие экземпляр сущности. Для города – это название (при условии, что имеются ввиду города одной страны). Соответственно, название, как ключевой атрибут мигрирует в атрибуты сущности Фирмы.
Примечание: если ключевых атрибутов у данной сущности слишком много, то Рис.5.5 Пример реализации диаграммы в реляционной БД (схема данных обычно в качестве ключа используют уникальное обозначение (чаще A ) автоинкрементное поле (счётчик)). Пример диаграммы см. на рис.5.4. 4. Изобразите свою ПО на листке в виде ER-диаграммы. Представьте преподавателю на проверку. Если необходимо, внесите коррективы и дополнения. 5. Реализуйте ER-диаграмму в виде реляционной модели в СУБД Access. Любой сущности в ER-диаграмме будет соответствовать таблица в реляционной БД. Атрибуту – поле таблицы. Связи – связь. Пример см. рис.5.5. 6. Для удобства заполнения мигрировавших ключевых атрибутов (полей, составляющих ключ) используйте Мастер подстановок. Учтите, что мастер сам создаёт связь между таблицами, но при этом не устанавливает для ней обеспечения целостности данных. 7. Создайте формы для заполнения основных таблиц (1-2). 8. Заполните таблицы данными 5-10 записей.
47
9. Создайте несколько запросов, отчётов, модифицируйте данные и убедитесь в обеспечении целостности БД.
Рис.5.6 Пример отчёта Например, для выбора товаров по городам и определения сумм продаж данного товара в городах, постройте запрос с помощью Мастера или Конструктора, на основе которого создайте отчёт. Для определения суммы проданного товара создайте вычисляемое поле (как это сделать см. предыдущие л/р). Пример рис.5.6. 5.7 Форма отчётности: 1. Представить преподавателю и обосновать инфологическую модель ПО. 2. Показать реализацию модели ПО в реляционную БД. 3. Продемонстрировать работу системы: занесение и изменение данных (формы), выборка (запросы) и отображение (отчёты). 4. Ответить на контрольные вопросы. 5.8 Контрольные вопросы:
1. 2. 3. 4. 5. 6. 7.
Пpедметная область, объекты и атpибуты. Информационное моделирование предметных областей. Инфологическая модель предметной области. Назначение. Особенности. Даталогическая модель предметной области. Назначение. Особенности. Последовательность пpоектиpования pеляционных БД. ER-диаграммы. Сущности. Атрибуты. Отношения. Соответствие элементов ER-диаграмм и элементов реляционной БД.
48
49
6. ЛАБОРАТОРНАЯ РАБОТА №6 6.1 Тема: WEB-страницы доступа к данным базы 6.2 Цель: Научиться создавать WEB-страницы для динамического отображения и изменения дданных в удалённой или локальной(как частный случай) БД. 6.3 Задание: Взять за основу данные из предыдущей лабораторной работы. 1. В текущей БД создать с помощью мастера страницу для доступа к одной таблице БД. Попробовать изменить данные и убедиться в этом, открыв таблицу обычным способом. 2. Создать при закрытой БД с помощью конструктора страницу доступа к нескольким таблицам. Убедиться в её работоспособности. 3. Создать страницу, отображающую данные запроса. 4. Создать общую страницу, с которой при помощи гиперссылок осуществлялся переход к боле детальным данным. 6.4 Общие сведения и порядок работы: 6.4.1 Создание страниц доступа к данным Разработка страницы доступа к данным ведется в интерактивном режиме средствами, похожими на те, что используются при конструировании форм или отчетов, однако и в разработке и во взаимодействии с такими страницами имеются значительные отличия. Могут быть созданы страницы различного типа. Одни страницы позволяют только просматривать информацию баз данных, обеспечивая при этом группировку, сортировку и фильтрацию данных, другие обеспечивают не только просмотр информации, но и ее редактирование, удаление и добавление записей в базу данных. 6.4.2 Подключение страницы доступа к данным к базе данных При создании страницы из базы данных Access для нее автоматически определяется в качестве источника данных открытая база данных. Это может быть как локальная, так и сетевая база данных. Создавать страницу можно, не открывая базы данных, с помощью команды Файл / Создать. При этом, как и при создании базы данных или проекта Access, открывается окно Создание, в 50
котором на вкладке Общие следует выбрать значок Страница доступа к данным. Для создания страницы вне базы данных доступен только режим конструктора, после выбора которого открывается окно Список полей, а для подключения к нужному источнику данных открывается окно Свойства связи с данными. В этом окне на вкладке Поставщик услуг можно увидеть, что по умолчанию в качестве поставщика услуг выбран Microsoft OLE DB Provider для SQL-сервера. На вкладке Подключение можно определить SQL-сервер и базу данных на нем. Для подключения к базе данных Access нужно сначала на вкладке Поставщик услуг выбрать Microsoft Jet 4.0 OLE DB Provider и затем на вкладке Подключение — базу данных на локальном компьютере или другом доступном компьютере сети. После выбора нужных для подключения к базе данных параметров и проверки подключения Список полей отображает имя базы данных, к которой присоединена страница, и объекты базы данных, доступные при конструировании страницы. Список полей позволяет добавлять поля таблиц и запросов на страницу доступа к данным. Для этого достаточно выбрать имя поля в списке и перетащить его на страницу. Каждое поле страницы поддерживает прямую динамическую ссылку, которая связывает страницу доступа и реальные данные в базе. Если необходимо изменить сведения о подключении страницы к базе данных, например, при изменении местоположения базы, откройте страницу доступа к данным в режиме конструктора, откройте Список полей и щелкните правой кнопкой мыши на значке базы данных, расположенном вверху списка. В контекстном меню выберите строку Подключение. В открывшемся окне Свойства связи с данными укажите новые сведения о подключении страницы к базе данных. При просмотре страницы в режиме страницы можно определить, какая база данных является источником данных для нее. Для этого необходимо выполнить команду Файл / Свойства страницы. На вкладке Состав в свойстве Строка подключения отображаются сведения об используемой базе данных. 6.4.3 Создание из базы данных Access страницы доступа к данным одной таблицы В базе данных Access страница доступа к данным (как и форма или отчет) может быть создана с помощью мастера или в режиме конструктора. Создайте страницу доступа к данным для работы с таблицей с помощью мастера автостраниц. Этот мастер, не вступая в диалог с пользователем, создает страницу на основе всех полей только одной таблицы. Откройте базу данных. В окне базы данных в списке Объекты выберите объект Страницы и нажмите кнопку Создать. В диалоговом окне Новая страница доступа к данным выберите Автостраница: в столбец. Выберите таблицу, на данных которой должна быть основана страница, и нажмите кнопку ОК. На панели перехода по записям доступны все кнопки. Эта страница 51
позволяет изменять, добавлять и удалять записи из таблицы, производить фильтрацию записей по значению выделенного поля. Перейдите в режим конструктора. Введите название страницы и исправьте надписи полей, если нужно. Сохраните созданную страницу доступа к данным. Access предлагает сохранить страницу в текущей папке в виде HTML-файла, имеющего расширение .htm. Автоматически Access добавляет ярлык для этой страницы в окно базы данных. Чтобы узнать путь к файлу страницы, наведите указатель на ярлык страницы в окне базы данных и подождите появления всплывающей подсказки. Закройте Access. Откройте вновь созданную страницу в Internet Explorer. Убедитесь, что и в среде браузера страница позволяет производить просмотр, в том числе с применением фильтра, изменение, добавление и удаление записей в таблице подключенной базы данных. Можно открыть страницу в режиме страницы из меню Access с помощью команды Файл / Открыть. При этом Access открывает страницу без открытия базы данных, к которой подключена страница, поэтому мастера, построители и надстройки будут недоступны. 6.4.5 Использование фильтра на странице доступа к данным Создайте как в предыдущем упражнении с помощью мастера автостраниц страницу доступа к данным для ввода, просмотра и редактирования записей в таблице. При просмотре страницы часто удобно отображать ту или иную группу записей. Чтобы отобрать для вывода записи в поле, по значению которого вы хотите отфильтровать записи, щелкните на кнопке Фильтр по выделенному на панели перехода по записям. В результате без группировки будут отобраны записи только одной группы. Отфильтрованные записи также можно изменять, добавлять и удалять из таблицы. Фильтр по выделенному применяется к группе записей, выбранных по предыдущему фильтру. (После удаления фильтра вновь выводятся все записи). 6.4.6 Создание страницы доступа к данным двух таблиц Создайте страницу доступа к данным, воспользовавшись мастером для создания основы такой страницы. Добавление уровней группировки приводит к созданию страницы, доступной только для чтения данных из таблиц. Выберите поля, по которым будет производиться сортировка записей на странице, и установите флажок Применить тему к странице. Нажмите кнопку Готово. Откроется созданная страница в режиме конструктора и окно со списком тем. Каждая тема может быть просмотрена и выбрана подходящая тема для оформления страницы.
52
При размещении на странице полей Access автоматически создает запрос SQL, который и становится источником ее записей. С помощью запроса на страницу из базы доставляются указанные в нем данные. На странице, созданной мастером, отображается по одной записи запроса. Основным средством мастера для организации записей запроса на странице является группировка. Работа мастера страниц мало отличается от работы мастера отчетов. При размещении с помощью мастера полей из не скольких таблиц на странице или при добавлении уровней группировки становится невозможным редактирование данных на этой странице. Для перехода в режим просмотра страницы щелкните на кнопке панели инструментов Вид. Убедитесь, что невозможно редактировать данные на странице с группировкой. Доработайте страницу в режиме конструктора. Для добавления строки подписей столбцов раскрывающегося списка откройте окно Сортировка и группировка, щелкнув на соответствующей кнопке панели инструментов. В этом же окне можно добавить или убрать для любой группы Раздел кнопок перехода, Примечание группы и установить ряд других параметров. Щелкните на кнопке Вид панели инструментов. Если выделить одно из полей с информацией о кафедре, станут доступ ными кнопки фильтра. Кнопки редактирования записей на этой стра нице всегда будут оставаться недоступными. 6.4.7 Создание страницы доступа к данным в режиме конструктора Создайте страницу доступа к данным для просмотра, ввода и редактирования записей об изучении предметов студенческими группами. Используйте режим конструктора. В окне со списком страниц открытой базы данных дважды щелкните на строке Создание страницы доступа к данным в режиме конструктора. Откройте список полей, щелкнув на соответствующей кнопке панели инструментов. Выделите в списке полей таблицы и перетащите их на страницу в Раздел: несвязанный. В окне Мастер макета выберите флажок Отдельные элементы. На странице будет создан раздел заголовка, в котором разместились все поля таблицы, и раздел кнопок перехода. Добавьте из таблицы поля. Откройте страницу в браузере и убедитесь, что страница из нескольких таблиц, созданная конструктором, допускает редактирование. Добавьте в базу данных, подключенную к странице, несколько записей. 6.4.8 Создание страниц с многоуровневой группировкой с помощью конструктора Создайте страницу, которая позволит просматривать таблицы. Дважды щелкните на строке Создание страницы доступа к данным в режиме 53
конструктора в окне списка страниц открытой базы данных. Откройте список полей, щелкнув по соответствующей кнопке панели инструментов. Выделите в списке полей таблицу и добавьте на страницу поля. Для того чтобы сгруппировать записи, создайте уровень группировки (выделите поле и щелкните на кнопке Группировать по таблицам панели инструментов). Удалить уровень группировки можно, щелкнув по кнопке Понизить уро вень на поле группировки. 6.4.9 Работа с гиперссылками В Access встроена возможность создавать и использовать гиперссылки. Гиперссылки могут храниться в полях таблиц базы данных и элементах управления форм. Гиперссылки можно также добавлять в элементы управления отчетов. Однако при просмотре отчета в Access гиперссылки работать не будут. Для их использования отчет необходимо вывести в формате Word, Excel или создать на их основе страницу в формате HTML. Гиперссылки также удобны на страницах доступа к данным. Однако и здесь гиперссылки, сохраненные в полях базы, не работают, если только на эти поля не сделаны ссылки в связанных гиперссылках страниц. К тому же гиперссылки в полях должны быть записаны как URL- или UNC-адреса. Связанная гиперссылка может быть присоединена также к текстовому полю, содержащему адрес. По гиперссылкам осуществляется переход к объектам той же самой или другой базы данных Access, к документам, созданным в Word, Excel и PowerPoint и расположенным на локальных или сетевых дисках, а также к документам, расположенным на Web- и FTP-серверах в глобальной сети Internet или в корпоративной сети intranet. В гиперссылках для указания местоположения объекта используются адреса URL и UNC. URL (Uniform (Universal) Resource Locator - - Унифицированный указатель ресурса) - является стандартом для определения типа и местоположения объекта в Internet и intranet. URL может указывать на серверы WWW, FTP, адреса электронной почты, конференции, файлы локальных дисков и другие объекты. Например: http://www.mail.ru ftp://ftp.microsoft.com mailto:
[email protected] Первая часть URL (перед двоеточием) определяет протокол, который должен использоваться для доступа к объекту. Например, протокол HTTP предназначен для передачи HTML-страниц в Internet. Две косые черты указывают, что после них следует адрес компьютера, на котором установлен сервер. Адрес, как правило, представляется полным доменным именем компьютера, хотя может быть представлен и цифровым IP-адресом. Если на одном компьютере работает несколько серверов, то вслед за адресом компьютера стоит двоеточие и номер порта. Далее в строке может стоять косая черта, отделяющая адрес от остальной части, которая определяет URL-путь, указывающий точное местоположение ресурса на сервере. Например, в случае 54
доступа по HTTP здесь задается путь к файлу и имя файла, содержащего Webстраницу. При отсутствии URL-пути по умолчанию определяется доступ к домашней странице сервера. Домашняя страница представляет содержимое сервера и позволяет перейти к нужному разделу. UNC-адрес (Universal Name Convention - Универсальное соглашение об именах) является стандартным форматом ссылки на файл, доступный в сети. В отличие от ссылки на файл локального диска он включает имя компьютера в сети вместо имени диска. Для разделения имени компьютера, папки и файла в UNC-адресах используется обратная косая черта. UNC-адрес позволяет ссылаться на данные, которые не обязательно должны располагаться на Webсервере. Например, путь к файлу в папке на компьютере в сети имеет вид \\Имя компьютера в сети\Папка\документ.расширение. Используя данные типа Гиперссылка, можно хранить гиперссылки в полях таблиц БД. В формах и отчетах гиперссылки хранятся в элементах управления типа Надпись, на страницах доступа данных - в объекте Гиперссылка. Гиперссылка в форме или на странице имеет неизменное значение при переходе по записям. Разместив гиперссылки в поле таблицы, можно связать с каждой записью новую гиперссылку. Использование гиперссылок доступно всем пользователям, поскольку для этого не нужно никаких дополнительных средств и сложных программ. 6.4.10 Создание гиперссылки на странице текущей базы данных на другую страницу, расположенную в той же базе данных Откройте в режиме конструктора страницу доступа к данным. Щелкните на кнопке панели элементов Гиперссылка и вставьте гиперссылку в нужное место страницы. В диалоговом окне Добавить гиперссылку в разделе Связать с щелкните на значке Страница в этой базе данных. В поле Текст введите текст, который должен отображаться на странице. Если оставить это поле пустым, то Access в качестве текста будет выводить имя ярлыка страницы, которую мы выберем для создания на нее ссылки. Ярлык содержится в окне базы данных и ссылается на файл со страницей. Нажмите кнопку Подсказка и введите текст, который должен появляться при наведении указателя мыши на гиперссылку. Если не вводить подсказку, Access в качестве подсказки будет показывать адрес гиперссылки — адрес размещения страницы. Выберите в списке страницу доступа к данным, ссылку на которую нужно создать, например, страницу. На страницу доступа к данным будет добавлена гиперссылка в элемент управления Гиперссылка. Чтобы проверить действие этой ссылки, переключитесь в режим страницы и щелкните на гиперссылке. Access откроет страницу в Web-браузере. Закройте базу данных и Access. Откройте Internet Explorer и в нем страницу. Щелкнув на гиперссылке «ссылка»: страница доступа к данным, перейдите на соответствующую страницу. 55
Для добавления на страницу гиперссылки на страницу в Internet или intranet щелкните на значке Имеющийся файл или Web-страница в разделе Связать с. Нажмите кнопку Web-страница, чтобы найти нужную страницу. После переключения из Internet Explorer в Access, программа Access подставит заголовок и адрес URL текущей открытой Web-страницы в поля Текст и dведите имя файла или Web-страницы. Если ссылка на интересующую вас страницу уже использовалась ранее, можно попробовать найти ее в списках, открывающихся кнопками Просмотренные страницы или Вставленные ссылки в области Выберите из списка. 6.4.11 Создание гиперссылки на существующий файл Создайте на странице доступа к данным гиперссылку на тексто вый файл < имя >.doc. Откройте страницу доступа к данным в режиме конструктора. Выполните команду Вставка / Гиперссылка. В открывшемся диалоговом окне Добавить гиперссылку в разделе Связать с щелкните на значке Имеющийся файл или Web-страница. В поле Текст введите текст, который должен отображаться на странице. Если нужно, введите подсказку. В противном случае будет отображаться адресная ссылка. В поле Введите имя файла или Web-страницы введите путь к файлу или адрес URL. Чтобы не выполнять ввод вручную, можно просмотреть последние вставленные объекты и выбрать нужный. Для этого в области Выберите из списка нажмите кнопку Последние файлы или Вставленные ссылки и выберите объект из списка. Для выбора объекта на локальном или любом доступном сервере сети нажмите кнопку Файл. При необходимости перейти в определенное место текстового файла нажмите кнопку Вкладка и выберите вкладку из списка. Переключитесь в режим страницы и щелкните на гиперссылке. Access откроет текстовый файл в Webбраузере. 6.5 Форма отчётности: 1. Показать страницу для доступа к одной таблице БД. Попробовать изменить данные и убедиться в этом, открыв таблицу обычным способом (через ОС). 2. Показать страницу доступа к нескольким таблицам. Продемонстрировать её работу. 3. Показать страницу, отображающую данные запроса. 4. Показать общую страницу, с которой при помощи гиперссылок осуществлялся переход к боле детальным данным и продемонстрировать её работу 5. Ответить на контрольные вопросы..
56
6.6 Контрольные вопросы: 1. 2. 3. 4. 5. 6.
Что такое WEB-документ? Смысл создания страниц доступа к данным. Создание страниц доступа к данным с помощью мастера. Создание страниц доступа к данным в режиме конструктора. Редактирование данных посредством страниц доступа к данным. Редактирование данных нескольких таблиц посредством одной страницы доступа к данным. 7. Просмотр и редактирование данных нескольких связанных таблиц через страницу доступа к данным. 8. Гиперссылки. Назначение. Размещение. Работа. 9. Автономная (без Access) работа с БД через страницы доступа к данным.
57
