Практические работы по базам данных (СУБД Access)

Пояснительная записка к практикуму по работе с базами данных в СУБД Access

Автор-составитель: Нагорная Галина Васильевна,

учитель информатики

МБОУ «Лицей имени академика Б.Н. Петрова», город Смоленск

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

Каждая практическая работа рассчитана на 20 мин самостоятельной работы обучающегося.

Практикум включает:

• рекомендации по проведению уроков,

• презентации:

  • Базы данных.ppt,

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

• cформированные базы данных:

  • Упражнения.mdb (4 таблицы),

  • Реляционная база данных Абитуриент.mdb (5 таблиц),

  • Дополнительно: Студенты.mdb, Фирма.mdb, Библиотека.mdb

Часть I практикума (практические работы №1 – №5), рассчитана на 6 уроков, из них первый урок-лекционный.

Приобретаемые умения и навыки:

• Проектирование структуры однотабличной базы данных.

• Создание новой базы данных.

• Создание структуры таблицы.

• Создание первичного ключа.

• Редактирование структуры таблицы

• Создание запросов для вывода отдельных полей на экран.

• Создание формы с помощью Мастера форм.

• Просмотр данных с помощью формы.

• Формирование простых запросов на выборку к готовой базе данных.

• Просмотр результатов выполнения запроса

• Редактирование, удаление и добавление данных с помощью форм.

• поиск, сортировка и фильтрация данных.

Часть II практикума (№6 – №10) рассчитана на 6 уроков, из них первый урок-лекционный.

Приобретаемые умения и навыки:

• Проектирование структуры многотабличной базы данных.

• Создание реляционной базы данных.

• Сложные условия поиска.

• Формирование сложных запросов к базе данных.

• Создание вычисляемых полей.

• Формирование отчетов на основе таблиц и запросов

Кроме того, добавлен обобщающий урок (№13) и урок №14 «Защита проекта»

Если учитель использует УМК автора Л.Л. Босовой, то использование части I практикума возможно в 9 классе, в рамках темы «Моделирование и формализация» и II части практикума возможно в 11 классе в рамках темы «Информационное моделирование»

В 11 классе я использую УМК Семакина И. Г., Хеннера Е. К., Шеиной Т. Ю., и, соответственно, учащиеся выполняют обе части практикума (I и II), что дает поступательное и более полное усвоение указанной темы.

Литература

1. Семакин И.Г. Информатика, Программа для старшей школы, 10-11 класс, базовый уровень, 2015

2. Информатика, 11 класс, Семакин И.Г., Хеннер Е.К., Шеина Т.Ю., 2014

3. Информатика и ИКТ. Задачник-практикум, часть 2, Семакин И.Г., Хеннер Е.К., 2014

4. Методическое пособие для учителя / Авторы-составители: М. С. Цветкова, И.Ю. Хлобыстова.—Эл. изд.—М. : БИНОМ. Лаборатория знаний, 2013

5. ЕГЭ 2018, Информатика, Комплекс материалов, Лещинер В.Р., Крылов С.С., Якушкин А.П., 2018

Практическая работа№1

Объекты БД. Таблица и форма.

Цель:

• освоить создание, заполнение и редактирование таблицы БД,

• освоить создание формы и ее использование.

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

• Создать таблицу Автомобилисты в БД Упражнения.

  • Откройте БД Упражнения.

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

• • Создайте структуру таблицы Автомобилисты в соответствии с табличной моделью:

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

Модель

Номер

Дата регистрации

Левченко Н.Г.

Волга

А111ФФ-59

15.08.98

Сидоров A.M.

Форд

К222РР-59

14.02.95

Горохов Н.Н.

Жигули

Б333ММ-59

27.10.01

Федоров К Р.

Волга

А444ПП-02

20.05.96

Сидоров A.M.

Жигули

К555ММ-59

27.10.95

Прохоров К.П.

Ауди

А666КК-87

06.12.06

Семенов А.И.

ВМW

М777ВВ-67

30.04.03

2. Редактирование данных и структуры таблицы

• Изменить данные в таблице Автомобилисты:

• в режиме таблицы в поле Модель заменить название моделей в записях №2 и №5 на «Рено»;

• заменить фамилии автовладельцев в записях №2 и №5 на фамилии Светлов и Рыжов.

• Удалить из таблицы Автомобилисты записи №6 и №7:

• • выделить запись - контекстное меню– Удалить запись.

• Изменить макет таблицы Автомобилисты

• Изменить размер шрифта в таблице:

• • выделите все записи – меню СУБД – Шрифт

• Самостоятельно определите способ изменения высоты строк и ширины столбцов.

• • Установите: высоту строки = 30, ширину столбца Номер = 50

• Изменить структуру таблицы:

• Выполнить сортировку таблицы по полю Владелец в порядке, обратном алфавитному:

• • выделите поле Владелец - нажмите кнопку Сортировка по убыванию на панели инструментов.

• Измените название поля Номер на Номер автомобиля (используя контекстное меню)

3. Создание формы с помощью мастера

• Создать форму Авто для таблицы Автомобилисты с помощью мастера.

• В окне БД выбрать вкладку «Создание», в меню выбрать кнопку Конструктор форм , затем пункт Мастер форм.

• В окне «Создание форм»

• • из списка таблиц выберите таблицу Автомобилисты,

  • переместите из доступных полей в выбранные поля имена Владелец, Модель, Номер, Дата,

  • далее следовать указаниям мастера. Закрыть форму, сохранив ее под именем «Авто»,

.

4. Изменение данных таблицы с помощью формы

• Открыть форму Авто и выполнить указанные ниже замены.

• С помощью формы Авто в поле Модель заменить название моделей в записях №2 и №5 на прежние.

• Заменить фамилии автовладельцев в записях №2 и №5 на прежние фамилии.

• С помощью формы Авто добавить в таблицу Автомобилисты записи №6 и №7:

• Показать учителю выполненную работу и удалить форму Авто.

Практическая работа№2

Самостоятельная работа

Цель:

1. закрепить алгоритм создания БД, а также уметь редактировать структуру таблицы и записи указанным образом.

2. знать алгоритм создания формы к таблице БД, а также уметь редактировать форму в режиме конструктора.

1. Создание базы данных Друзья

• Создать БД Друзья:

  • Меню «Пуск» - Все программы - Microsoft Оffice - Microsoft Оffice Access.

  • В открывшемся окне MS Access выполнить:

• • • на панели задач (по центру) выбрать пункт Новая база данных…

• • В диалоговом окне создания новой БД в поле необходимо ввести имя Друзья указать папку для хранения БД Друзья и нажать кнопку Создать. На экране появится окно базы данных Друзья.

• В БД Друзья создать структуру таблицы Друзья в режиме конструктора таблиц.

Друзья (ФИО, Телефон, Дата рождения, Увлечение).

• Внести в таблицу пять записей.

2. Создание формы для таблицы Сведения о друзьях

• Создать форму Дружба для таблицы Сведения о друзьях в режиме Мастер форм. Используя диалоговое окно Мастера форм, внесите на форму все поля.

3. Редактирование таблицы

1. Любым способом изменить высоту строки в таблице и сделать ее равной 30.

2. Изменить шрифт текста в таблице на Arial Cyr, размер шрифта 14, полужирный.

3. Добавить поле «Школа» и заполнить его в таблице.

4. Заменить название поля «Дата рождения» на «День рождения».

5. Используя форму Дружба, добавить в таблицу записи №6 и №7.

6. Выполните сортировку таблицы по полю «Дата рождения» в алфавитном порядке.

4. Редактирование формы в режиме конструктора

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

• Отредактировать форму Дружба в режиме конструктора.

• Откройте форму Дружба в режиме конструктора (щелчок по заголовку формы правой кнопкой мыши)

• Вставить на форму любое фото или рисунок:

• • Меню СУБД: Конструктор – Эмблема – выбрать файл с рисунком.

• Открыть таблицу Друзья и форму Дружба и показать учителю результат работы.

Практическая работа№3

Цель:

1. уметь выполнять поиск и сортировку записей в однотабличной БД.

2. уметь применять фильтр для поиска записей.

1. Сортировка записей в БД

• Открыть таблицу Спортсмен в БД Упражнения.

• Сортировать записи в таблице Спортсмен по полю «Страна»: щелкнуть в любом месте поля – щелкнуть по кнопке на панели инструментов Сортировка по возрастанию (убыванию).

• Сортировать записи в таблице Спортсмен по нескольким полям (многоуровневая сортировка):

• • выделить два соседних поля Страна и Вид спорта (сортировка начнется с крайнего левого поля) - щелкнуть по кнопке на панели инструментов Сортировка по возрастанию.

  • Проанализировать результат сортировки.

• По аналогии сортировать записи в таблице Спортсмен по полям Вид спорта и Место. Проанализировать результат сортировки.

2. Поиск и замена данных в БД

• Выполнить поиск записей по всей таблице Спортсмен, если известно, что записи содержат слово «Россия»:

  • меню СУБД – Правка – Найти – поиск образца Россия в таблице Спортсмен - при совпадении поля целиком - щелкнуть по кнопке Найти далее.

• Выполнить поиск записей по всей таблице Спортсмен, если известно, что записи содержат часть слова «СШ»:

  • меню СУБД – Правка – Найти – поиск образца СШ в таблице Спортсмен - при совпадении с любой частью поля – с учетом регистра - щелкнуть по кнопке Найти далее.

• Выполнить поиск записей по всей таблице Спортсмен, если известно, что записи содержат слово «легкая»:

  • меню СУБД – правка – поиск – поиск образца «легкая» в таблице Спортсмен при совпадении с начала поля - щелкнуть по кнопке Найти далее.

• Выполнить замену слова «ФРГ» в поле Страна на слово «Германия» по всему полю Страна: меню СУБД – правка – заменить – далее выполнять самостоятельно.

• Выполнить замену слова «легкая» в поле Вид спорта на слово «тяжелая» по всему полю Вид спорта.

3. Фильтрация записей

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

• Выбрать записи по спортсменам из США с помощью фильтра по выделенному:

  • выделить в таблице слово «США» - меню СУБД – Записи - фильтр по выделенному.

• Выбрать записи по спортсменам из США и России с помощью фильтра по выделенному: выделить в таблице слово «США» и слово «Россия» (используя клавишу Shift) - контекстное меню – фильтр по выделенному.

• Выбрать записи по спортсменам из России, завоевавшим 1, 2 места, используя команду Изменить фильтр:

  • контекстное меню таблицы – изменить фильтр – на бланке со списком полей выбрать поле Страна и значение «Россия» - выбрать поле Место и набрать значение «<3» - контекстное меню таблицы – применить фильтр.

• Выбрать записи по спортсменам, фамилии которых начинаются на букву А:

  • контекстное меню таблицы – изменить фильтр – на бланке со списком полей выбрать поле Фамилия и набрать значение «А*» - контекстное меню таблицы – применить фильтр.

Справочная информация

Примеры использования символов для задания шаблонов для поиска

предполагается любой символ в слове

По?ода

Порода

Погода

На месте звездочки (*)

предполагается любая группа символов.

Д*нь

*й

Добрый день

День

Дань

Последний

128-й

На месте знака фунта (#) предполагается любая цифра.

# й

5й

8й

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

Иванов[!аы]

Иванову

[но не Иванова или Ивановы

Примеры условных выражений для поиска

Практическая работа№4

Создание запроса к таблицам БД

Цель:

• уметь устанавливать ограничения на ввод данных при заполнении таблицы,

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

1. Изменение свойств поля

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

Установим ограничение на ввод данных в таблицу БД.

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

• Открыть БД Упражнения.

• Не допустить ввода отрицательных чисел в поле место.

• Открыть конструктор таблицы «Спортсмен». Щелкнуть по полю место - в свойствах поля выбрать Условие на значение – набрать в строке выражение >0.

• В свойстве Сообщение об ошибке ввести текст: «введите положительное число»– закрыть конструктор - сохранить изменения в макете таблицы.

• Введите в таблицу две новые записи (Светлов и Яснов из Беларуси). Намеренно допустите ошибку при вводе числа в поле место (введите отрицательное число). Проанализируйте результат ввода.

2. Запрос на выборку данных из таблицы БД

• Сформировать к таблице Спортсмен запрос, с помощью которого из таблицы будут выбраны спортсмены из США и России:

  • На вкладке Создание щелкнуть по кнопке Конструктор запросов – создание с помощью конструктора;

  • на панели Добавление таблицы выбрать Спортсмен- щелкнуть по кнопке Добавить.

  • Перетащить названия Фамилия, Страна на бланк запроса в строку Поле.

  • В строке Условие отбора записать: Россия or США.

  • В строке Вывод на экран поставить метку напротив полей, которые будут представлены в запросе.

  • Закрыть бланк запроса на выборку – сохранить запрос под именем Запрос 1.

  • Открыть Запрос 1 и убедиться, что он правильно сформирован.

• Сформировать к таблице Спортсмен запрос, с помощью которого из таблицы будут выбраны спортсмены, занявшие места со 2 по 4 (Условие отбора: (>1) and (<5)) – сохранить запрос под именем Запрос 2.

• Сформировать к таблице Спортсмен запрос, с помощью которого из таблицы будут выбраны гимнасты из всех стран, кроме США, (Запрос 3):

  • В строке Условие отбора для поля Вид спорта записать: *гимнастика.

  • В строке Условие отбора для поля Страна записать: Not США.

• Сформировать запрос к таблице Страны мира, с помощью которого из таблицы будут выведены все сведения по странам Великобритания и Франция. (Запрос 4)

3. Запрос на удаление записей из таблицы БД

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

• Удалить из таблицы Спортсмен записи по спортсменам из Беларуси.

  • Сформировать к таблице Спортсмен запрос, с помощью которого из базы будут выбраны спортсмены из Беларуси (запрос 5).

  • Открыть запрос5 в режиме конструктора:

    • контекстное меню запроса – пункт Тип запроса – Удаление.

    • Выполнить запрос 5

  • Открыть таблицу Спортсмен и убедиться, что записи удалены.

4. Многоуровневая сортировка данных в запросе

• Сортировать записи в запросе к таблице Спортсмен по полям Вид спорта и Место:

  • Создать запрос, где будут представлены все данные таблицы Спортсмен.

  • На бланке запроса, в строке Сортировка для поля Вид спорта поставить значение по убыванию.

  • На бланке запроса, в строке Сортировка для поля Место поставить значение по возрастанию.

  • Сохранить запрос под именем Запрос 6.

  • Открыть запрос и убедиться, что записи отсортированы.

Практическая работа№5

Самостоятельная работа

Цель:

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

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

1. Ограничение на ввод данных в таблицу БД

• Не допустить ввода данных в поле время таблицы Программа передач больших значения «24»

  • Открыть конструктор таблицы Программа передач. Для поля время ввести Условие на значение: <= 24 и в свойстве Сообщение об ошибке ввести текст: «время передачи не должно превышать 24.00» – закрыть конструктор - сохранить изменения в макете таблицы.

  • Ввести в таблицу две новые записи: мультфильм на канале Россия и фильм на канале Первый.

2. Запрос на выборку данных из таблицы БД

• Сформировать к таблице Программа передач запросы, с помощью которых из базы будут выбраны следующие данные:

1. все фильмы, идущие по понедельникам;

2. ток-шоу на канале Культура;

3. передачи, выходящие на экран с 17.00 по 22.00 в любой день недели;

4. передачи, выходящие на экран до 21.00 в любой день недели, кроме пятницы;

5. вывести названия передач, начинающихся на букву «Н» (см. справочную информацию из практической работы № 3);

6. вывести все фильмы и мультфильмы, демонстрирующиеся по каналам Россия и НТВ.

3. Использование запроса для многоуровневой сортировки данных

• Сортировать записи в запросе по таблице Программа передач по полям канал и время:

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

• По полю канал сортировать записи по возрастанию, а по полю время - по убыванию.

4. Запрос на удаление записей.

• Удалить из таблицы Программа передач с помощью запроса на удаление:

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

2. записи передач на первом канале.

Практическая работа №6

Создание реляционной БД Абитуриент

Цель:

• создание и заполнение таблиц для реляционной БД;

• преобразование группы таблиц в реляционную базу данных.

1. Создание и заполнение таблиц для реляционной БД «Абитуриент»

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

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

Для выбора ключевого поля в конструкторе таблицы установите курсор на имени поля: контекстное меню - ключевое поле (или на панели инструментов СУБД Access выбрать кнопку ).

Таблица Факультет

Таблица Специальность

Таблица Абитуриент Таблица Оценки

1. Создание связей между таблицами БД «Абитуриент» с помощью окна Схема данных

• Открыть окно Схема данных: вкладка Работа с базами данных – кнопка Схема данных.

Используя окно Добавление таблицы, добавить все таблицы БД «Абитуриент».

• Свяжем таблицы между собой в соответствии со схемой:

Например, имя поля код факультета из таблицы Факультет на такое же имя в таблице Специальность.

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

В результате получим следующую схему:

Вопрос: как поступить, если программа отказывается создавать связь между таблицами?

Ответ:

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

• в режиме таблицы проверьте, совпадают ли значения в связываемых полях (Например, если в поле Код факультета таблицы Факультет введены значения «1» или «2», то и в таблице Специальность в поле Код Факультета должны быть те же числа).

Практическая работа№7

Создание запросов и форм в реляционной БД

Цель:

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

• уметь создавать формы для ввода/редактирования данных в связанных таблицах;

  1. Создание запросов на выборку данных в связанных таблицах

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

• Создать список абитуриентов с указанием факультета и перечнем оценок за экзамены.

• Выбрать вкладку Создание – кнопка Конструктор запросов - создать запрос с помощью конструктора.

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

• Добавить в окно запроса таблицы: Факультеты, Специальности, Абитуриенты, Анкеты, Оценки. Удалите лишние (случайные) связи.

• Перетащить нужные поля на бланк запроса (название факультета, ФИО, оценки).

• Установить сортировку по возрастанию в поле «название».

• Сохранить запрос под именем «Оценки за экзамены».

• Создать список абитуриентов факультета №2 с перечнем оценок за экзамены.

• В качестве условия отбора для поля название факультета записать: экономический.

• Запрос назвать «Оценки на факультете №2». Название факультета не выводить на экран.

• Создать список абитуриентов факультета №1 получивших за экзамен по русскому языку оценку не ниже «4».

• В качестве условия отбора для поля с оценками по русскому языку записать: >= 4.

• Запрос назвать «Оценки за русский язык».

• Создать список абитуриентов факультета №2 не получивших за экзамены ни одной «двойки».

• В качестве условия отбора для полей с оценками записать: > 2.

• Запрос назвать «Нет двоек».

2. Создание формы для ввода записей в связанные таблицы

• Создать единую форму в режиме Мастера форм, основным назначение которой будет ввод данных в таблицы Абитуриенты и Анкета.

• В окне «Создание форм» из таблицы Факультеты выбрать поле Код факультета.

• В окне «Создание форм» из таблицы Специальности выбрать поле Код специальности.

• Из таблицы Абитуриент выбрать все поля, кроме поля Код специальности.

• Из таблицы Анкета выбрать все поля, кроме поля Регистрационный номер. Кнопка «Далее».

• В окне «Создание форм» выбрать вид представления Специальность :

• Действовать в соответствии с рекомендациями «мастера». Дать форме название «Сведения об абитуриентах»

• Будут созданы две формы: подчиненная и главная форма Сведения об абитуриентах.

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

Практическая работа№8

Использование вычисляемого поля в запросе на выборку

Цель:

• научиться создавать вычисляемое поле в запросе;

• создать запрос Результаты зачисления в ВУЗ.

1. Использование вычисляемых полей в запросах

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

Создадим два запроса Сумма баллов и Без двоек. На основе этих запросов построим запрос Результаты зачисления в ВУЗ.

• Создать запрос Сумма баллов, полученных за экзамены. Суммарный балл каждого абитуриента нужен для подведения итогов зачисления в ВУЗ.

Запрос должен содержать поля Рег_номер, ФИО и вычисляемое поле Сумма баллов.

• • Создать запрос с помощью конструктора.

  • Добавить на бланк запроса связанные таблицы: Анкеты и Оценки. Удалить лишние (случайные) связи.

  • Перетащить поля Рег_номер и ФИО на бланк запроса.

  • Правее, на свободном поле, вызвать контекстное меню – Построитель выражений. Нижняя часть окна Построителя выражений разбита на 3 окна.

• В первом окне выбрать в папке Таблицы папку Оценки.

• Во втором окне последовательно выбрать щелчком имя поля и знак «+», чтобы сложить Оценка1, Оценка2, Оценка3 для подсчета суммы оценок. ОK

• Название поля «выражение 1» на бланке запроса заменить на Сумма баллов.

• Сохранить запрос под именем Сумма баллов.

• Создать запрос Без двоек. В результирующей таблице запроса напротив фамилии абитуриента будет слово «да», если он не получал двоек за экзамен, и слово «нет» в противном случае.

Расчет будет выполнен на основе встроенной функции iif (аналог функции Если-то-иначе в Excel).

Общий вид функции iif: IIf («expr»; «truepart»; «falsepart»)

Действие функции определим следующим условием:

если (оценка1>2) и (оценка2 >2) и (оценка3 >2), то «да», иначе «нет»

Таким образом,

expr следует заменить логическим выражением

IIf(Оценки!Оценка1>2 And Оценки!Оценка2>2 And Оценки!Оценка3>2;"да";"нет") из папки Оценки;

truepart следует заменить текстом «да»;

falsepart следует заменить текстом «нет».

Запрос должен содержать поле Рег_номер, и вычисляемое поле Экзамены.

• • Открыть вкладку Запросы в окне БД, создать запрос с помощью конструктора.

  • Добавить на бланк запроса таблицу Оценки.

  • Перетащить поле Рег_номер на бланк запроса.

  • Правее, на свободном поле, вызвать контекстное меню – Построитель выражений. Нижняя часть Построителя выражений разбита на 3 окна.

• • С помощью построителя выражений создать вычисляемое поле Экзамены.

• В первом окне выбрать Функции – Встроенные.

• Во втором окне выбрать категорию Все.

• В третьем окне выбрать функцию iif и выполнить нужные замены:

  • • • в первом окне выбрать в папке Таблицы папку Оценки.

      • во втором окне последовательно выбирать щелчком имена полей, между ними вставлять «>2» логическую операцию And, так, чтобы получилось выражение:

IIf(Оценки!Оценка1>2 And Оценки!Оценка2>2 And Оценки!Оценка3>2;"да";"нет")

• Название поля «выражение 1» на бланке запроса заменить на Экзамены.

• Обязательно установите «галочку» в строке вывод на экран на бланке запроса.

• Запрос сохраните под именем Без двоек

Практическая работа№9

Создание запроса на изменение базовой таблицы в БД

Цель:

• создать запрос / или таблицу/ с итогами экзаменов.

• Создание запроса Результаты зачисления на основе запросов
  Сумма баллов и Без двоек.

На основе запроса Результаты зачисления будет заполнена базовая таблица Итоги.

Запрос должен иметь поле Рег_номер, поле с Фамилией абитуриента, поле с названием факультета, расчетное поле Зачисление.

Расчет будет выполнен на основе встроенной функции iif.

Если (сумма баллов>=11) и экзамены сданы без двоек то «зачислен» иначе «не зачислен».

Порядок работы:

• • Открыть вкладку Запросы в окне БД, создать запрос с помощью конструктора.

  • Добавить на бланк запроса запрос Сумма баллов и запрос Без двоек, между ними должна протянуться связь по полю Рег_номер

  • Перетащить поле Рег_номер на бланк запроса.

  • С помощью Построителя выражений создать вычисляемое поле Зачисление справа от поля Рег_номер.

• В окне Построителя выбрать Функции – Встроенные –<Все> - iif

• Выполнить нужные замены:

• в первом окне открыть папку Запросы – Без двоек, во втором окне выбрать поле Экзамены и ввести в формулу для iif условие

iif( ([Без двоек]!Экзамены="да")…

• в первом окне открыть папку Запросы – Сумма баллов, во втором окне выбрать поле Сумма баллов добавить в формулу для iif условие And ([Сумма баллов]![Сумма баллов]>10) ,

• добавить в формулу для iif текст «зачислен» - «не зачислен»

В результате получим расчетное поле Зачисление:

Зачисление: IIf(([Без двоек]!Экзамены="да") And ([Сумма баллов]![Сумма баллов]>10); "зачислен";"не зачислен")

• • Обязательно установите «галочку» в строке Вывод на экран на бланке запроса.

  • Сохранить запрос

• Проверить результат выполнения запроса.

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

• Создадим базовую таблицу Итоги, для хранения данных об итогах зачисления абитуриентов в ВУЗ и заполним таблицу Итоги с помощью запроса на создание таблицы.

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

Этапы работы:

1. создание таблицы Итоги (Рег. Номер; Зачисление),

2. преобразование запроса Результаты зачисления в запрос на создание таблицы,

3. заполнение таблицы Итоги с помощью запроса Результаты зачисления,

4. подключение таблицы Итоги к РБД.

Порядок работы:

• Создать таблицу Итоги(Рег. Номер; Зачисление).

• Открыть запрос Результаты зачисления в режиме конструктора и изменить тип запроса: контекстное меню – Тип запроса – Создание таблицы…(или меню СУБД –Запрос-Создание таблицы…).

• • Закрыть запрос Результаты зачисления .

• Заполнить таблицу Итоги с помощью запроса Результаты зачисления.

• • Открыть (активизировать) запрос Результаты зачисления. При выполнении запроса СУБД предупредит об изменении в таблице Итоги.

  • Открыть таблицу Итоги в режиме конструктора и сделать Рег_номер ключевым полем.

• В Схеме данных установить связь между таблицами Оценки и Итоги.

• Открыть таблицу Итоги и убедиться в том, что в таблице присутствуют фамилии абитуриентов с результатами зачисления в ВУЗ.

Практическая работа№10

Создание отчета

Цель:

• научиться создавать и редактировать отчеты;

• создать группу отчетов по СУБД Абитуриент.

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

1. Создание отчета на основе запроса.

Создадим отчет Оценки абитуриентов на основе запроса Оценки за экзамены.

• Создать запрос Оценки за экзамены, включающий факультет, фамилию, оценки за экзамены.

  • Выбрать вкладку «Отчеты»:

• • Создать отчет с помощью мастера – на основе запроса Оценки за экзамены.

  • Сгруппировать абитуриентов по факультетам :

• • • в окне Вид представления данных выбрать Факультеты,

    • кнопка «далее>».

• • Выбрать порядок сортировки по возрастанию для названий факультетов - «далее».

  • Выбрать вид макета для отчета – ступенчатый. Ok

• Просмотреть отчет.

2. Редактирование отчета в режиме конструктора.

• Отредактировать отчет Оценки абитуриентов в режиме конструктора отчетов. Каждый элемент можно редактировать как самостоятельный объект через контекстное меню этого элемента.

  • изменить текст в заголовке отчета на «Результаты экзаменов», а в верхнем колонтитуле текст элемента «ФИО» на «Фамилия»

  • изменить начертание и размер шрифта элементов верхнего колонтитула. Измените дизайн отчета для лучшего визуального восприятия.

Результат редактирования отчета:

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

1. Создать запрос Список зачисленных в ВУЗ и одноименный отчет к нему с группировкой по факультетам. В отчете выполнить сортировку фамилий по возрастанию.

2. Создать запрос «Списки не поступивших на факультет №2» и отчет к нему.

3. Создать отчет «Список медалистов – двоечников» (список медалистов, получивших на экзаменах хотя бы одну двойку).

4. Экспорт отчета в приложения Ms Word и Ms Excel

• Экспортировать отчет Список зачисленных в Ms Word и в Ms Excel:

  • Вкладка Внешние данные – Экспорт в MS Excel.

  • Сохранить документы под именами Отчет.doc и Отчет.xls в указанной папке.

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

1. Табличная база данных. СУБД.

Задачи учителя на уроке

1. Познакомить с понятием «база данных», со структурой таблицы в однотабличной БД (поле, запись, тип).

2. Познакомить с назначением СУБД Access, ее основными функциями.

3. Познакомить с объектами СУБД на примере БД Упражнения.

Дидактический материал к уроку: презентация Табличная БД.ppt на основе таблиц из задачника – практикума И. Г. Семакина.

2. Создание таблицы и ее заполнение. Форма.

Задачи учителя на уроке

1. Коротко повторить основные понятия и термины, пройденные на предыдущем уроке (использовать презентацию Табличная БД.ppt)

2. Показать создание таблицы Автомобилист (без установки ключа) в БД Упражнения.

3. Объяснить назначение формы, показать создание формы к таблице Автомобилист.

4. Контролировать выполнение учащимися практической работы №1.

3. Самостоятельная работа с элементами обучения

Задачи учителя на уроке:

1. Перед выполнением самостоятельной работы напомнить учащимся последовательность создания таблицы и формы БД.

2. Показать редактирование формы в режиме конструктора и размещение рисунка на форме (необязательная часть задания).

3. Оценить по пятибалльной шкале степень самостоятельности учащихся по выполнению Практической работы №2.

4. Сортировка, фильтрация, поиск и замена данных в таблице

Задачи учителя на уроке

1. Познакомить с понятиями «сортировка», «ключ сортировки», привести примеры задания шаблонов для поиска.

2. Показать на ПК сортировку, фильтрацию, поиск и замену данных в таблице.

3. Контролировать выполнение учащимися практической работы №3.

5. Запросы к БД

Задачи учителя на уроке

1. Познакомить с понятиями «запрос», «условия выборки».

2. Познакомить с видами запросов на примере БД Упражнения.

3. Контролировать выполнение учащимися практической работы №4.

6. Самостоятельная работа

Работа на уроке состоит из двух частей: самостоятельное выполнение практической работы №5 и (необязательно) выполнение тестовой работы по теме.

Задачи учителя на уроке

1. Оценить выполнение практической работы №5.

2. Проверить результаты тестирования.

Если изучение основ работы с базами данных на этом завершается, то перед уроком №6 необходимо провести урок по теме «Создание отчета».

Часть II практикума включает практические работы №6 – №10 и рассчитана на 6 часов обучения.

Источник информации – учебник И. Г. Семакина и др. «Информатика 11 класс», задачник-практикум для 7-11 классов под редакцией И. Г. Семакина (часть 2).

7. Создание структуры многотабличной БД.

Форма урока – лекция. Для иллюстрации теоретического материала используется презентация Проектирование БД.ppt

Цель: познакомить учащихся с этапами проектирования базы данных на примере проектирования базы данных Абитуриент. Источник информации – учебник И. Г. Семакина «Информатика 11 класс».

Задачи учителя на уроке

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

Краткие тезисы к презентации

Требуется создать базу данных, содержащую сведения об абитуриентах, поступающих в высшее учебное заведение.

База данных должна содержать информацию о ВУЗе, сведения об абитуриентах, сведения о результатах экзаменов.

Рассмотрим этапы создания реляционной БД «Абитуриент».

1. Анализ предметной области (этапы работы приемной комиссии ВУЗа).

2. Анализ данных.

Данные БД «Абитуриент» – это сведения о ВУЗе (факультеты, специальности, экзамены) и сведения об абитуриентах (анкетные данные и результаты экзаменов).

Данные взаимосвязаны друг с другом и взаимозависимы, то есть образуют информационную систему. Эту систему можно представить графически, в виде иерархической модели (структура ВУЗа). Каждый уровень иерархии – это класс объектов (класс факультетов, класс специальностей, класс абитуриентов).

3. Создание модели данных.

На основе классов объектов создадим модель данных.

Класс Факультеты связан с классом Специальности отношением один ко многим (т. е. на одном факультете может быть много специальностей). Класс Специальности связан с классом Абитуриенты отношением один ко многим (т. е. на одну специальность поступает много абитуриентов).

Из класса Абитуриенты образуем еще три класса, находящихся на одном уровне иерархии: класс Анкеты (анкетные данные абитуриентов), класс Оценки (оценки абитуриентов за экзамены), класс Итоги (результаты зачисления абитуриентов в ВУЗ). Эти четыре класса связаны друг с другом отношениями «один к одному», так как у одного абитуриента одна анкета, один перечень оценок за экзамены и один итог зачисления в ВУЗ.

Итак, в иерархической структуре верхний уровень связан с подчиненным отношением «один ко многим», а классы одного уровня связаны друг с другом отношениями «один к одному».

Каждый класс данных может быть представлен в виде отдельной таблицы.

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

Связь «Один ко многим» протягивается от главного ключа базовой таблицы к такому же полю (по типу и набору данных) в подчиненной таблице (например: от главного ключа код факультета в таблице Факультеты к одноименному полю код факультета в подчиненной таблице Специальности).

Поле код факультета является внешним ключом для таблицы Специальности.

Поле код специальности является внешним ключом для таблицы Абитуриенты.

Связь «один к одному» протягивается между главными ключами таблиц.

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

Схема данных БД Абитуриент, выполненная при помощи СУБД Access, полностью соответствует модели данных.

Основные функции СУБД Access:

• создание структуры реляционной БД (РБД),

• заполнение БД данными,

• редактирование структуры БД (добавление/удаление полей, их переименование, изменение типа данных, формата и т. д.),

• редактирование записей в БД,

• поиск и сортировка данных.

2. На примере готовой БД Абитуриент показать схему БД, объекты БД и их назначение.

Домашнее задание

Разделить учащихся на группы по 2-3 человека. Каждой группе предложить разработать структуру многотабличной БД по одной из предложенных тем. Модель данных и схему БД начертить в тетради.

Примеры тем для разработки базы данных можно найти учебнике «Информатика 11 класс». В дополнение можно предложить темы¹:

Школьная библиотека,

Прокат дисков,

Студенты,

Торговая фирма.

8. Создание многотабличной БД Абитуриент

Задачи учителя на уроке

1. Коротко повторить основные понятия и термины, пройденные на предыдущем уроке (использовать презентацию).

2. Контролировать выполнение учащимися практической работы №6.

3. Оценить создание схемы по теме домашнего задания.

Домашнее задание Создать БД по выбранной теме

9. Создание запросов и форм к РБД

Задачи учителя на уроке

1. Повторить назначение и способы создания объектов «запрос» и «форма».

Обратить внимание на особенности создания запроса к реляционной БД.

2. Показать на ПК как создаются запросы и формы к РБД.

3. Контролировать выполнение учащимися практической работы №7. Оценить работу учащихся.

Домашнее задание Создать 2 запроса и две формы к БД по выбранной теме.

10. Создание вычисляемых полей в запросах

Задачи учителя на уроке

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

2. Показать на ПК как создается вычисляемое поле в запросе.

3. Контролировать выполнение учащимися практической работы №8.

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

11. Запрос на создание таблицы

Задачи учителя на уроке

1. Рассказать о типах запросов (на выборку, на создание, на удаление и т.д.).

2. Показать алгоритм конструирования запроса на создание таблицы на примере запроса Результаты зачисления и заполнения с его помощью таблицы Итоги.

3. Контролировать выполнение учащимися практической работы №9.

12. Создание отчетов в РБД

Задачи учителя на уроке

1. Познакомить с понятием «отчет».

2. Показать алгоритм создания отчета.

3. Контролировать выполнение учащимися практической работы №10.

4. Оценить работу учащихся.

Домашнее задание Создать 2 отчета по своей БД.

13. Основные характеристики РБД

Задачи учителя на уроке

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

Краткие тезисы

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

Вопрос:

Можно ли разместить в одной таблице данные таблиц Абитуриенты, Анкеты, Оценки, Итоги?

Ответ: да, но объединенные таблицы были бы громоздки и имели повторяющие данные.

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

1. каждая таблица имеет ключ,

2. все поля каждой таблицы зависят только от главного ключа,

3. в таблицах отсутствуют группы повторяющихся значений.

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

Вывод: РБД Абитуриент – нормализованная база данных.

2. Охарактеризовать РБД Абитуриент, используя основные признаки классификации баз данных.

Краткие тезисы

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

Рассмотрим принципы классификации БД.

Классификация БД по характеру хранимой информации:

• Фактографические БД – хранят данные представленные в краткой форме и строго фиксированных форматах, например картотека библиотеки или поликлиники.

• Документальные БД – хранят обширные неструктурированные данные об объектах, например архивы документов.

Классификация БД по техническим средствам хранения данных:

• В централизованной БД вся информация хранится на одном компьютере.

• Распределенная БД используется в локальных и глобальных сетях, где разные части БД хранятся на разных ПК.

Классификация БД по структуре данных (то есть по модели представления данных):

• Иерархическая модель БД графически представляет собой дерево, состоящее из объектов различных уровней. Например, база данных Дерево папок Windows (можно увидеть, открыв Проводник), база данных Доменная система имен компьютеров, подключенных к Интернету.

• Сетевая модель БД – где нет ограничений на связи между объектами, например сеть WWW.

• Реляционная БД - использует табличную модель данных.

Вывод: в соответствии с принципами классификации БД Абитуриент - это

фактографическая централизованная реляционная база данных.

14. Защита проекта.

Задача учителя на уроке

Организовать защиту проектов учащихся по теме «Создание многотабличной БД».

Проект должен содержать:

• в тетради – цели, предмет исследования, модель БД.

• на носителе – РБД по теме проекта на 10 записей с 2-3 запросами, формами и отчетами.

Один проект выполняется 2-3 учащимися. На защите учащиеся представляют проекты, которые они выполнили дома и отвечают на вопросы учителя.

В общей сложности, представляется не более 4-5 проектов. Защита проекта длится не более 5 минут.