Создание сверочных таблиц БД.

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

Теоретические сведения.

 

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

ОТДЕЛ

Наименование отдела	Начальник отдела	Кабинет	Телефон отдела

 

СОТРУДНИК

Ф.И.О	Наименование отдела	Занимаемая должность	Пол	Паспорт	Адрес проживания	Заработная плата

 

Рис. 3.1.

Для связывания полей необязательно совпадение их имен (например, они могут называться в одной таблице как Факультет, а в другой - Фак-т), но обязательно совпадение их типов.

Связывание производится по следующему алгоритму:

Сверяемая таблица (например, СОТРУДНИК) открывается в режиме Конструктор.

В списке типов данных соответствующего поля выбирается опция Мастер подстановок.

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

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

В появившейся карточке слева приведен список всех полей выбранной таблицы, а справа находится окно, в которое переносятся связываемые поля (рис. 3.2). Для этого необходимо выделить мышью поле и нажать кнопку >. Если поле было выбрано ошибочно, то его выбор отменяется кнопкой <. Завершается выбор также нажатием кнопки Далее.

 

 

Рис. 3.2

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

7. В последнем окне нажимается клавиша Готово.

 

Задание к лабораторной работе

Создать таблицу ОТДЕЛ, включающую следующие поля:

· название отдела. Из него в дальнейшем будут выбираться значения поля Отдел таблицы СОТРУДНИК;

· начальник отдела (текстовое 20);

· телефон отдела (текстовое 8. маска вида 27-13-56);

· кабинет (целый).

Заполнить таблицу 5-ю записями.

Создать таблицу ДОЛЖНОСТЬ, включающую следующие поля:

· названия должностей. Из него в дальнейшем будут выбираться значения поля Должность таблицы Сотрудник;

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

Заполнить таблицу 5-ю записями.

Связать поля Отдел и Должность таблицы Сотрудник с однотипными полями таблиц ОТДЕЛ и ДОЛЖНОСТЬ.

Дополнить таблицу Сотрудник 10-ю записями. Результат показать преподавателю.

 

Лабораторная работа №4.

 

Импорт данных.

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

Теоретические сведения.

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

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

Импорт начинается выбором последовательности команд главного меню Файл/Внешние данные/Импорт. В появившемся окне диалога Импорт открывается папка с документом Excel'а и в нижней части окна устанавливается Тип файлов = Microsoft Excel.

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

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

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

На последнем этапе создания новой таблицы СУБД попросит дать ей имя, после чего надо нажать кнопку Готово. Пиктограмма таблицы с импортированными данными появится на вкладке Таблица БД.

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

Объединение целевой и импортированной таблицы в дополнительную можно выполнить по следующей процедуре.

На вкладке Таблицы выделяется мышью целевая таблица и копируется в буфер (кнопка на панели инструментов).

Далее нажимается кнопка Поместить (значок ), что приводит к появлению окна диалога Вставка таблицы. В нем надо установить переключатель Параметры вставки = Структуры и данные и задать имя дополнительной таблицы. Нажатие кнопки ОК приведет к её появлению.

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

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

Существует также возможность экспорта данных из СУБД Access в электронную таблицу Excel. Реализация данного процесса производится по следующей процедуре.

На вкладке Таблицы мышью выделяется экспортируемая таблица.

Далее активизируются команды главного меню Файл ® Сохранить как/Экспорт, что приводит к появлению окна Сохранение объекта.

Устанавливается переключатель Во внешнем файле или базе данных нажимается кнопка ОК.

В следующем окне устанавливается Тип файла = Microsoft Excel 97, меняется, если требуется, имя экспортированного файла и нажимается кнопка Экспорт.

 

Задание к лабораторной работе

Средствами ЭТ Excel создается таблица из 5-ти записей, аналогичная таблице ОТДЕЛ БД Фирма (см. лабораторную работу № 3).

Производится ее импортирование в таблицу ОТДЕЛ базы данных Фирма с использованием дополнительной таблицы.

После получения корректного результата производится непосредственное импортирование данных в целевую таблицу ОТДЕЛ.

Таблица СОТРУДНИК экспортируется в таблицу Excel.

Результат работы демонстрируется преподавателю.

Лабораторная работа №5

 

Фильтрация данных

Цель работы: изучение и закрепление на практике методов выборки данных из таблиц БД путем фильтрации.

 

Теоретические сведения.

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

Фильтр по выделенному. В режиме просмотра таблицы в некотором поле выделяется значение, которое является условием отбора, например, название факультета в таблице описания студентов вуза. Далее нажимается пиктограмма Фильтр по выделенному на панели инструментов. Все записи таблицы, содержащие выделенное значение, отобразятся на экране. Остальные будут скрыты. Удаление фильтра осуществляется отжатием утопленной кнопки (Удалить фильтр). В данном режиме можно произвести фильтрацию только по части значения. Для этого надо её выделить (например, первые буквы некоторого слова) и нажать кнопку Фильтр по выделенному.

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

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

Операция И реализуется между значениями различных полей, например, надо выбрать из таблицы СОТРУДНИК женатых сотрудников определенного отдела. Для этого производят фильтрацию по первому значению, например, отделу, а затем результат фильтруется по второму значению, семейному положению.

 

Задание к лабораторной работе.

В таблице Сотрудник БД Фирма осуществить следующие виды фильтрации:

выбор сотрудников одного отдела;

выбор сотрудников, название должности которых заканчивается на букву "т";

выбор сотрудников, фамилия которых начинается на букву "В";

выбор всех сотрудников, кроме одного отдела;

выбор сотрудниуков по двум должностям;

выбор сотрудников-женщин одного отдела.

Результат каждой фильтрации показать преподавателю.

Лабораторная работа № 6

 

Запросы к БД

Цель работы: изучение и закрепление на практике методов формирования и использования запросов для выборки и/или модификации данных в таблицах.

Теоретические сведения.

 

Вопросы, которые формируются средствами СУБД к одной или нескольким таблицам, называются запросами.

Запросы позволяют:

формировать сложные критерии для выбора записей из одной или нескольких таблиц;

указывать поля, которые должны быть отображены для выбранных записей;

редактировать группы записей, удовлетворяющих определенным критериям;

выполнять вычисления с использованием выбранных данных.

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

В СУБД Paradox используется методология «Запрос по образцу» (Query by Example или QBE), включающая форму (бланк) запроса и специальный язык для её заполнения. Переход к формированию запроса реализуется путем перехода на вкладку "Запросы" конкретной БД и нажатия кнопки Создать, после чего СУБД предлагает различные варианты реализации запросов. Наиболее эффективным является режим конструктора. Далее выбирается требуемая таблица и нажимается кнопка Добавить. На экране появляется бланк запроса (рис. 6.1), представляющий набор пустых столбцов, каждый из которых содержит фиксированный набор ячеек. Операции работы с бланком запроса перечислены в команде ЗАПРОС главного меню и представлены пиктограммами в панели инструментов.

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

Рис. 6.1. Бланк запроса.

Исходно бланк запроса пуст. Для занесения конкретного поля в бланк необходимо:

- двойным щелчком левой клавиши мыши поместить его в первый свободный столбец бланка,

или

- перетащить его мышью (при нажатой левой клавише мыши).

Полная очистка бланка при его переформировании может быть выполнена командой главного меню Правка ® Очистить. Очистка конкретного столбца проводиться путем его выделения щелчком левой клавиши мыши в зоне выделения и нажатия клавиши Delete на клавиатуре или нажатия кнопки (Удалить в буфер)на панели инструментов.

Запуск запроса на выполнение осуществляется командами главного меню Запрос ® Запуск или нажатием кнопки на панели инструментов.

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

Самое главное в запросе - возможность использования критериев выборки, которые вводятся в строку Условие отбора. Можно выделить следующие типы запросов на основе критериев выборки:

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

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

[<текст приглашения>].

При запуске параметризованного запроса появляется диалоговое окно (рис. 6.2), в котором пользователь должен ввести собственно условие отбора и нажать клавишу ОК.

 

Рис. 6.2. Запуск параметризированного запроса.

Выборка по строгому несовпадению. В этом случае в выборку отбираются все записи таблицы, кроме записей, содержащих значение, указанное в строке Условие отбора. Для реализации данного запроса перед значением вводится префикс Not или <>. Например, Not "Логистика" в поле Наименование отдела запроса к таблице Сотрудник приведет к выборке всех сотрудников, кроме сотрудников отдела логистики.

Выборка по неточному совпадению. Для выборки записей в условиях неполноты знаний о требуемых значениях используется оператор Like <условие>. Само <условие> образуется следующими подстановочными символами:

·? - любой один символ;

· * - любое количество символов (0 - ¥);

· # - любая одна цифра;

· [список_символов] - любой символ из списка;

· [!список_символов] - любой символ, не входящий в список;

В списке можно указывать сразу диапазон символов, Например, [Г-Л] или [г-лГ-Л].

Примеры использования оператора Like в поле ФИО таблицы Сотрудник:

Like?????????? - выбираются все сотрудники, ФИО которых содержит 10 символов;

Like "В?????????" - выбираются все сотрудники, ФИО которых содержит 10 символов и начинается на букву "В";

Like "В*" - выбираются все сотрудники, ФИО которых начинается на букву "В". Длина ФИО произвольная;

Like "[ВД]*" - выбираются все сотрудники, ФИО которых начинается на буквы "В" или "Д". Длина ФИО произвольная;

Like "[В-М]*" - выбираются все сотрудники, ФИО которых начинается на буквы от "В" до "М". Длина ФИО произвольная.

Выборка по диапазону. Для формирования данных условий выбора используются операторы сравнения >, >=, <, <= и <>. Операции сравнения могут связываться логическими операциями And (И) и Or (ИЛИ). Для этих же целей используется оператор диапазона Between <нижнее_значение> and < верхнее_значение >. Например, выбор заказов стоимостью от 100 до 200 рублей может быть реализован через ввод в запросе условия в поле Стоимость в виде >=100 and <=200 или Between 100 and 200.

Перечень значений в условии выборки можно задать и оператором In (значение, значение,...). Например, выбор сотрудников отдела логистики или администрации можно реализовать, указав в поле Отдел запроса условие In ("логистика", "администрация"). Это же условие можно записать и через операцию ИЛИ: "логистика" or "администрация". Также можно указать одно название отдела в строке Условие отбора (см. рис. 6.1), а второе в следующей строке или. Число строк или не ограничено.

Для выбора записей с пустыми значениями в некотором поле надо в соответствующем поле бланка запроса указать оператор Is Null. Наоборот, записи с непустыми значениями в данном поле выбираются по оператору Is not Null.

В выражениях отбора также можно использовать знаки математических операций +, -, /, * и неограниченное число круглых скобок. Сложные выражения в условиях отбора могут формироваться с помощью соответствующего построителя, который вызывается кнопкой на панели инструментов.

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

<Название_формируемого_поля>:<выражение>.

В <выражении> можно использовать знаки арифметических операций, круглые скобки и имена полей в []. Например, стоимость партии можно вычислить по выражению

Стоимость партии:[количество товара]*[стоимость единицы товара].

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

Стоимость партии:[Товар]![количество товара]*[стоимость единицы товара].

Замечание: [ количество товара ] – это имя поле “количество товара” конкретной таблицы (в данном случае абстрактной таблицы “Товар”), будьте внимательны, в квадратных скобках должно быть точное имя поля соответстсвующей таблицы.

6. Запрос с групповыми операциями. Рассмотренные запросы анализируют отдельные записи таблицы. Вместе с тем, СУБД Access позволяет находить интегральные показатели для групп записей в таблице. Каждая такая группа характеризуется одинаковым значением по какому-то полю, например, одинаковым названием факультета или семейным положением. Для перехода в данный режим запросов необходимо в панели инструментов нажать клавишу Групповые операции , что приведет к появлению в бланке запроса новой второй строки с одноименным названием. В ячейках данной строки указывается или режим группировки по некоторому поляю (опция Группировка), или название групповой операции:

· Sum - сумма значений

· Avg - среднее значение по данному полю для всей группы;

· Count - число записей в данной группе;

· Max -максимальное значение поля в каждой группе;

· Min -минимальное значение поля в каждой группе;

· First -первое значение данного поля в каждой группе;

· Last -последнее значение данного поля в каждой группе и др.

Опции выбора вызываются нажатием кнопки раскрытия в требуемой ячейке.

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

Рассмотренные запросы не изменяют содержимое исходной таблицы. Для реализации подобных действий СУБД Access использует четыре следующих запроса:

7. Запрос-создание новой таблицы. Предназначен для сохранения результатов запроса в виде новой таблицы.

Исходно формируется обычный запрос на выборку необходимой информации из таблицы. После проверки результатов его выполнения производится возврат в режим конструктора запросов. Далее нажимается кнопка Тип запроса на панели инструментов или выбирается команда главного меню Запрос. В появившемся списке выбирается опция Создание таблицы, после чего СУБД запрашивает её имя. Указывается имя создаваемой таблицы и нажимается кнопка ОК. Непосредственно запрос на создание запускается нажатием кнопки на панели инструментов.В окне Таблицы БД появляется пиктограмма созданной таблицы.

8. Запрос-добавление выборки в другую таблицу. Выборку можно добавить к другой таблице, однотипной по структуре или с изменением структуры выборки.

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

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

Нажимается кнопка Тип запроса на панели инструментов или выбирается команда главного меню Запрос. В появившемся списке выбирается опция Добавление, после чего СУБД запрашивает имя таблицы, к которой будет добавлена выборка. Последний шаг - нажатие кнопки ОК.

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

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

9. Запрос-удаление. С помощью запросов можно удалить часть или все записи из таблицы.

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

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

Нажимается кнопка Тип запроса на панели инструментов или выбирается команда главного меню Запрос. В появившемся списке выбирается опция Удаление, после чего в бланке запроса появляется новая третья строка с именем Удаление, куда можно вводить дополнительные условия на выборку удаляемых записей. Последний шаг - нажатие кнопки ОК.

9. Запрос-обновление. С помощью запросов можно обновлять в единой операции некоторые или все значения выбранных полей.

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

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

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

 

Задание к лабораторной работе.

Для таблицы Сотрудник БД Фирма сформировать, проверить корректность и сохранить следующие запросы:

Отображающий в выборке поля:

- ФИО;

- дату рождения;

- отдел;

- оклад.

Отображающий в выборке все поля таблицы для работников одного отдела. Сделать данный запрос параметризованным;

Отображающий в выборке поля:

- ФИО;

- отдел;

- количество детей,

для семейных сотрудников, фамилии которых начинаются на буквы от "Г" до "Ш";

Отображающий в выборке поля:

- ФИО;

- должность;

- оклад,

для холостых сотрудников с окладом больше 1000 руб.

Рассчитывающий для каждого сотрудника налог в виде выражения

налог = (0.99*зарплата - (количество_детей + 1) * 83.49) * 0.12, будьте внимательны, зарплата и количество_детей – абстрактные имена полей абстрактной таблицы, вы должны использовать в формуле имена полей именно вашей таблицы СЛУЖАЩИЙ. В выборке выводятся:

ФИО;

отдел;

зарплата;

налог.

6. Работу запросов продемонстрировать преподавателю.

Лабораторная работа № 7