Разработка баз данных на уроках информатики (11 класс)

ГОУ СПО Саранский колледж электроники, экономики и права им. А. И. Полежаева

Т. Ю. Мордашева

Методические рекомендации

по проведению лабораторных работ

по дисциплине

 «Базы данных»

Саранск 2009

Методические рекомендации по проведению лабораторных работ по дисциплине «Базы данных»/ Т. Ю. Мордашева - Саранский колледж электроники, экономики и права им. А. И. Полежаева; - Саранск, 2009. -29с.

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

Пособие предназначено для преподавателей и студентов специальности 230106.

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

Методические рекомендации

Мордашева Т. Ю.

Бумага офсетная. Формат 60х84 1/16. Гарнитура Таймс.

Печать способом ризографии.

Отпечатано с оригинала – макета заказчика

430000, г. Саранск, пр. Ленина, 10.

©Мордашева Т. Ю.

Содержание

Пояснительная записка. 4

Лабораторная работа №1 «Организация таблиц». 5

Лабораторная работа №2 «Запрросы на выборку». 6

Лабораторная работа №3 «Многотабличные запросы». 8

Лабораторная работа №4 «Вычисляемые поля». 9

Лабораторная работа №5 «Итоговые запросы». 10

Лабораторная работа №6 «Запросы на обновление. Перекрестные запросы». 11

Лабораторная работа №7  «Запрсы на создание таблиц, добавление, удаление записей»  13

Лабораторная работа №8 «SQL – запросы на выборку». 14

Лабораторная работа №9 «SQL – запросы на изменение». 16

Лабораторная работа№10  «Построение форм». 17

Лабораторная работа №11 «Подчиненные формы». 18

Лабораторная работа№12 «Использование макрокоманд». 20

Лабораторная работа №13 «Группы макросов». 23

Лабораторная работа №14  «Условия на выполнение макрокоманд». 25

Лабораторная работа №15 Процедуры обработки событий». 26

Литература. 29

Пояснительная записка

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

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

Методические указания по лабораторным работам учебной дисциплины «Базы данных» предназначены для реализации государственных требований к минимуму содержания и уровню подготовки выпускников по специальности 230106 «Техническое обслуживание средств вычислительной техники и компьютерных сетей». Курс «Базы данных» рассчитан на 90 часов, в том числе 30 часов отводится на лабораторные работы.

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

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

Тема: Организация таблиц.

Цель: изучение назначения условий на значение полей, синтаксиса предикатов IN, BETWEEN, LIKE,  правил задания маски ввода.

Пояснение к работе

Чтобы задать условие на значение, введите выражение в строке свойства/Условие на значение для этого поля. После этого Access не позволит ввести данные, не удовлетворяющие этому условию. При записи условий можно использовать операторы:

 < (меньше), <=(меньше либо равно),  >(больше), >=(больше либо равно),  ><( не равно), IN(проверяет на равенство любому значению из списка, пример IN(«Россия»; «США»)), BETWEEN (проверяет на значение из диапазона,  пример BETWEEN 3 AND 67),

LIKE (указывает шаблон, пример LIKE”??00##*”)

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

Задание

1.     Загрузите  Access, создайте новую базу данных «Заказы и заказчики»

2.     создайте таблицу «Заказчики» с помощью конструктора

Имя поля	Тип данных	Описание
Код заказчика	Числовой
Фамилия имя заказчика	Текстовый
Дата заказа	Дата/время
Адрес заказчика	Текстовый
Город заказчика	Текстовый
Регион заказчика	Текстовый
Страна заказчика	Текстовый
Индекс заказчика	Текстовый
Телефон заказчика	Текстовый
Вид оплаты	Числовой	Оплата; 1=наличные, 2=чек, 3=Американ Экспресс,4=Visa, 5=Mastercard
Кредитная карта	текстовый

3.     укажите поле «Код заказчика» ключевым

4.     задайте условие для поля «Регион заказчика», учитывая , что организация обслуживает только заказчиков из Мордовии или Татарстана (свойство поля/строка условие на значение /записать Мордовия orТатарстан  или  IN(«Мордовия»; «Татарстан»)

5.     Допустим, что организация обслуживает только граждан России, поэтому для поля «страна заказчика» укажите условие- «Россия»

6.     индекс может состоять только из 6 цифр, поэтому для поля «индекс заказчика» укажите условие LIKE «# # # # # #»

7.     вид оплаты указывается цифрами от1 до 5, поэтому для данного поля укажите условие BETWEEN 1 AND 5

8.     создайте маску ввода для поля «дата заказа», используя мастер масок

9.     заполните таблицу тремя записями

10.  измените условия на значение полей, учитывая следующее:

ü организация обслуживает только клиентов из США или Канады

ü индекс США состоит из 4 цифр, индекс Канады состоит из последовательности чередующихся букв и цифр (например, A9G4D3)

ü номер телефона состоит из 9 цифр

ü номер кредитной карты начинается с цифры из диапазона то 1 до 5

ü заполните таблицу пятью записями

 

Содержание отчета студентов:

1.     Опишите технологию выполнения задания № 10.

2.     Ответьте на контрольный вопрос согласно варианту.

 

Контрольные вопросы:

1.     Назначение условий на значение поля?

2.     Как задать условие на значение поля?

3.     Когда происходит проверка условия?

4.     Синтаксис предиката LIKE?

5.     Синтаксис предиката IN?

6.     Синтаксис предиката Between?

7.     Как задать маску ввода?

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

Тема: Запросы на выборку.

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

Пояснение к работе:

Запросы – объект  Access используется для просмотра, анализа и изменения данных из одной или нескольких таблиц.

Способы создания запросов:

1.     С помощью Мастера

2.     С помощью Конструктора

3.     Из фильтра

4.     Из расширенного фильтра.

Задание.

1. Загрузите Access, создайте базу данных Сотрудники, включая следующие поля: табельный номер, фамилия сотрудника, имя сотрудника, отчество сотрудника,  возраст, отдел, должность, стаж, оклад, персональная надбавка. Заполните базу данных 15-ю записями.

2. Создайте запрос из фильтра, для отображения сведений о сотрудниках:

2.1.   фамилии сотрудников начинаются на конкретную букву (фильтр по выделенному);

2.2.   фамилии сотрудников начинаются на конкретную букву (фильтр для);

2.3.   фамилии сотрудников заканчиваются конкретной буквой (фильтр по выделенному);

2.4.   фамилии сотрудников заканчиваются конкретным сочетанием букв (фильтр для);

2.5.   в списке записей исключена запись о сотруднике с конкретной фамилией (фильтр исключение выделенного значения);

2.6.   имена сотрудников начинаются на конкретную букву (фильтр для);

2.7.   имена сотрудников начинаются на конкретную букву (фильтр по выделенному);

2.8.   имена сотрудников заканчиваются конкретной буквой (фильтр для);

Указание. Запрос создается в окне «Фильтр» при выборе пункта Записи\Фильтр\Изменить фильтр.

3.  Создать запрос из расширенного фильтра  для отображения сведений о сотрудниках:

         3.1. Должность сотрудника секретарь, отдел либо бухгалтерия, либо финансовый;

         3.2. Стаж сотрудника от 3 лет до 5 лет;

         3.3. возраст сотрудника не более 45 лет;

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

5. найдите с помощью функции поиска сотрудников, стаж которых равен 3

6. найдите сотрудников, у которых общий заработок (сумма оклада и персональной надбавки) выше среднего заработка по всем сотрудникам, а оклад ниже среднего заработка.

Содержание отчета студентов:

1.     Опишите технологию выполнения задания № 6.

2.     Ответьте на контрольный вопрос согласно варианту.

Контрольные вопросы:

1.     Отличия запросов от фильтров?

2.     Способы создания запросов?

3.     Отличия окна расширенного фильтра от окна конструктора запросов?

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

Тема: Многотабличные запросы

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

 

Пояснение к работе:

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

Задание:

1.     загрузите Microsoft Access

2.     создайте новую базу данных “Магазин на диване”

3.     база данных должна состоять из нескольких таблиц: “Товары”, “Клиенты”, “Продажа”

4.     в таблицу “Товары” включите следующие поля: лот, наименование товара, стоимость, страна производитель

5.     в таблицу «Клиенты» включите следующие поля: код клиента, фамилия клиента (по желанию добавьте поля имя клиента, отчество клиента), телефон, адрес клиента

6.     в таблицу «Продажа» включите следующие поля: код продажи, код клиента, лот, количество,  дата заявки, вид оплаты

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

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

9.     создайте запрос «предпочтения по ценам», который будет показывать клиентов, приобретающих товары по цене не дороже 1000 руб.

10. создайте запрос «обзор», отражающий клиента и количество приобретенного им товара

11.  создайте запрос «Масажер», отражающий клиентов приобретавших массажер или тренажер 

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

13. создайте запрос «отбор», отображающий список ни разу не заказанных товаров;

Указание: необходимо изменить параметр объединения таблиц в запросе, в качестве условия отбора используется функция Is Null.

14. создайте запросы для отображения товаров, заказанных:

1.     первого числа каждого месяца;

2.     в январе или марте каждого года;

3.     по субботам или воскресеньям.

Содержание отчета студентов:

1.Опишите технологию выполнения заданий № 13, 14.

2.Ответьте на контрольный вопрос согласно варианту.

 

Контрольные вопросы:

1.     Как связать несколько таблиц?

2.     Виды связи таблиц?

3.     Как создать запрос на основе нескольких таблиц?

4.     Как изменить параметр объединения таблиц в запросе?

5.     Назначение функции Is Null?

6.     Назначение и синтаксис функции Day()?

7.     Назначение и синтаксис функции  Month()?

8.     Назначение и синтаксис функции Year()?

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

Тема: Вычисляемые поля.

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

Пояснение к работе:

Для выполнения вычислений с любыми полями таблицы можно создать выражение и определить его в качестве нового поля запроса. Для построения сложных выражений используется построитель выражений. При организации нового поля необходимо указать его имя, поставить двоеточие и записать выражение (например: полное имя:[клиенты]![фамилия клиента]&” “&[клиенты]![имя клиента])

Символ & объединяет данные текстовых полей в одно поле.

Задание:

1.     Загрузите Access

2.     откройте Базу Данных «Магазин на диване»

3.     в таблицу Клиенты добавьте поле Имя клиента

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

5.     создайте запрос Долги для отображения клиентов, у которых на сегодняшний день долг просрочен более 60 дней

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

Указание:        1. пени = долг * процентную ставку/100*количество дней с момента заказа до момента окончательной оплаты

                        2 . общая сумма к оплате = пени + долг на день заказа

7. создайте запрос Кредит на 3 месяца, для отображения клиента, приобретенного им товара, его стоимости, количества, задатка, оплаты по месяцам (см. образец)

Указание:        1. оплата кредита осуществляется из расчета 8% годовых

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

месяц1: [общий долг]*(0,08/12)+[общий долг]/3

                            дополнительно используйте функцию  Round для округления до сотых долей

Содержание отчета студентов:

1.     Опишите технологию выполнения заданий № 6, 7.

2.     Ответьте на контрольный вопрос согласно варианту.

Контрольные вопросы:

1.     Назначение построителя выражений?

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

3.     Как задать имя вычисляемому полю?

4.     Как объединить данные нескольких текстовых полей?

5.     Синтаксис функции Round ?

6.     Можно ли  при организации вычисляемого поля использовать вычисляемые поля данного запроса?

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

Тема: Итоговые запросы

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

Пояснение к работе:

Итоговые запросы создаются, если необходимо знать итоговые значения по группам данных. Для задания вычислений итоговых запросов необходимо щелкнуть на кнопке Групповые операции на панели инструментов конструктора запросов, чтобы в бланке запроса появилась строка групповая операция  (или воспользоваться контекстным меню). Для вычисления итогов замените значения группировка в строке групповая операция на конкретные итоговые функции ( например sum (возвращает сумму значений в группе), avg (возвращает среднее арифметическое), min (возвращает минимальное значение в группе), max (возвращает максимальное значение в группе), count(возвращает количество отличных от нуля))

Задание:

1.     Загрузите Access

2.     Откройте Базу Данных «Магазин на диване»

3.     Создайте итоговый запрос «Оплата», который будет отражать сумму денег, которую заплатил каждый покупатель за все товары.

4.     создайте итоговый запрос «Продажа», который будет отражать прибыль данного магазина от продажи каждого вида товара.

5.     создайте итоговый запрос «Итог», который будет отображать общую прибыль магазина;

6.     создайте итоговый запрос «Покупатель», который будет отражать самый дешевый товар и самый дорогой товар, приобретенный покупателем.

7.     создайте итоговый запрос «Частые клиенты», который будет отображать сколько раз приобретали какие-либо товары покупатели.

8.     создайте запрос «Первый», который будет отображать первых покупателей каждого товара

9.     создайте запрос «Средняя цена» для нахождения средней цены по всем товарам.

10. создайте запрос «Низкие цены», для отображения товаров, цены которых ниже средней цены по всем товарам.

Содержание отчета студентов:

1.     Опишите технологию выполнения заданий № 7 - 9.

2.     Ответьте на контрольный вопрос согласно варианту.

Контрольные вопросы:

1.     Назначение итоговых запросов?

2.     Как создать итоговый запрос?

3.     Какие функции относятся к итоговым ?

4.     Назначение функции Sum ?

5.     Назначение функции Count?

6.     Назначение функции Avg?

7.     Назначение функции First?

8.     Назначение функции Last?

9.     Назначение функции Min?

10. Назначение функции Max?

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

Тема: Запросы на обновление данных. Перекрестные запросы.

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

 

Пояснение к работе:

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

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

Перекрестный запрос позволяет вывести вычисляемые значения в перекрестной таблице, напоминающей электронную.

Для построения перекрестного запроса нужно выбрать

объект/запрос/создать/конструктор/ запрос/перекрестный

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

Задание:

1.     Загрузите Access

2.     откройте Базу Данных «Магазин на диване»

3.     создайте запрос, выполнение которого увеличит стоимость какого – либо товара на 1000 руб.

4.     создайте запрос для снижения стоимости товаров, которые не заказывали уже более месяца на 20% (при условии, что каждый товар заказывался только один раз)

5.     ^**создайте запрос для снижения стоимости товаров, которые не заказывали уже более месяца на 20%

6.     создайте запрос для снижения стоимости ни разу не заказанных товаров на 50%

7.     создайте перекрестный запрос «Итоги по  клиентам», который будет отображать какую сумму денег заплатил каждый покупатель за ту или иную покупку.

8.     запрос/создать/конструктор/таблица товары/запрос/перекрестный

9.     перетащите поле «фамилия», укажите – группировка, в строке сортировка укажите      по возрастанию, в строке перекрестная таблица укажите заголовки строк

10. перетащите поле «наименование товара», в строке перекрестная таблица укажите заголовки столбцов

11. создайте вычисляемое поле «общая стоимость за покупку» (цена * количество), в строке перекрестная таблица укажите значения, в строке группировка задайте функцию SUM

Содержание отчета студентов:

1.     Опишите технологию выполнения заданий № 4, 6.

2.     Ответьте на контрольный вопрос согласно варианту.

Контрольные вопросы:

1.     Назначение запросов на обновление?

2.     Как создать запрос на обновление?

3.     Назначение перекрестных запросов?

4.     Как создать перекрестный запрос?

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

Тема: Запросы на создание таблиц, добавление, удаление данных

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

Пояснение к работе:

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

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

         Для создания запроса нужно  выполнить следующие действия запрос/создать/конструктор/выбрать исходную таблицу/запрос/добавление (удаление). Для выполнения запроса выполняются действия запрос/запуск.

Задание:

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

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

3.     Создайте резервную копию таблицы «Все сотрудники»

4.     Создайте запрос «Архивация» на создание таблицы «Архив» (в таблице должны отображаться все данные уволенных сотрудников (условие отбора: значение поля уволен=да))

5.     Создайте запрос «Увольнение», выполнение которого удалит из таблицы «Сотрудники» уволенных сотрудников

6.     Создайте запрос «Увольнение по стажу», выполнение которого в таблице «Сотрудники» обновит данные о сотрудниках, уволив тех сотрудников, стаж которых превысил 35 лет

Указание: необходимо создать вычисляемое поле для нахождения стажа на сегодняшний день, наложить условие отбора на данное поле (>35), в строке обновление для поля уволен указать новое значение - «да»

7.     Перенесите из таблицы «Сотрудники» в таблицу «Архив» уволенных сотрудников (для этого необходимо создать и выполнить  запрос на добавление данных «Добавление уволенных» и выполнить запрос «Увольнение»)

8.     Создайте запрос «Просмотр премий» на создание таблицы «Премии, поместив в нее все данные сотрудников, у которых персональная надбавка составляет более 40% от оклада

9.     В таблице «Сотрудники» персональные надбавки меньшие 150 руб. увеличьте на 750руб., оклады меньшие 1000руб. увеличьте на 200руб. (запрос на обновление)

10. Проверьте  в таблице «Сотрудники» соотношение оклада и персональной надбавки и поместите в таблицу «Премии» данные сотрудников, у которых персональная надбавка составляет более 40% от оклада (запрос на добавление)

11. Увольте сотрудников отдела Техническое обслуживание, у которых возраст превышает 50 лет и стаж менее 10 лет

Указание: 1. Создайте запрос на обновление данных в таблице «Сотрудники»

               2. Выполните запрос «Добавление уволенных» 

          3. Выполните запрос «Увольнение» на удаление из таблицы «Сотрудники»

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

13. Создайте таблицу «Средние оклады», в которой будут отражены средние оклады по каждому отделу.

Содержание отчета студентов:

1.     Опишите технологию выполнения заданий № 9, 11, 13.

2.     Ответьте на контрольный вопрос согласно варианту.

Контрольные вопросы:

1.     Назначение запросов на создание таблиц?

2.     Как с помощью запроса создать таблицу?

3.     Назначение запросов на добавление записей?

4.     Как создать запрос на добавление записей?

5.     Назначение запросов на удаление записей?

6.     Как создать запрос на удаление записей?

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

Тема: SQL-запросы на выборку

Цель: изучение правил построения SQL-запросов, синтаксиса инструкции Select.

Пояснение к работе:

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

Ядром SQL-запросов является инструкция Select. Она используется для отбора строк и столбцов из таблицы базы данных и содержит пять основных предложений. В общем виде ее синтаксис можно представить в следующем виде:

Select <список полей>

From <список таблиц>

[Where< спецификация отбора строк>]

[Group By <спецификация группировки>]

[Having <спецификация отбора групп>]

[Order By <спецификация сортировки>];

         Завершается инструкция символом «;»

Задание:

1. Загрузите Access, создайте базу данных «Страны»:

Страна	Столица	Часть света	Население	Площадь
Австрия	Вена	Европа	7513	84000
Греция	Афины	Европа	9280	132000
Афганистан	Кабул	Азия	20340	647000
Япония	Токио	Азия	114276	372000
Франция	Париж	Европа	55183	551000
Египет	Каир	Африка	38740	1001000
Сомали	Могадишо	Африка	3350	638000
США	Вашингтон	Америка	217700	9363000
Аргентина	Буэнос-айрес	Америка	26060	2777000
Мальта	Валлетта	Европа	330	300
Монако	Монако	Европа	25	200

2.     Создайте SQL-запрос, который будет показывать страны, их столицы и часть света, в которых они находятся;

3.     Создайте SQL-запрос, который будет показывать только страны и  их столицы, которые находятся в Азии;

4.     Создайте SQL-запрос, который будет показывать страны, площадь которых не привышает150000 метров квадратных;

5.     Создайте  SQL-запрос, который будет показывать страны, которые находятся в части света, начинающейся на букву А  и население которых больше 50000 человек;

6.     Создайте SQL-запрос, который будет показывать страны, которые находятся в Европе или Азии;

7.     Создайте SQL-запрос, который будет отражать максимальное население среди стран Европы, Азии, Африки, Америки;

Содержание отчета студентов:

1.     Опишите SQL-запросы.

2.     Ответьте на контрольный вопрос согласно варианту.

 

Контрольные вопросы:

1.      Как открыть запрос в режиме SQL?

2.      Синтаксис инструкции Select?

3.      Назначение и синтаксис предложения  From?

4.      Назначение и синтаксис предложения Where?

5.      Назначение и синтаксис предложения Group By?

6.      Назначение и синтаксис предложения Having?

7.      Назначение и синтаксис предложения Order By?

 

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

Тема SQL – запросы на изменение

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

Пояснение к работе:

Синтаксис запроса на обновление:

UPDATE имя таблицы SET обновление

WHERE Условие отбора;

Синтаксис запроса на удаление:

DELETE список полей, по которым происходит удаление

FROM имя таблицы          

WHERE условие отбора строк;

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

                       SELECT Список полей для новой таблицы INTO [имя новой таблицы]

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

                       WHERE условие отбора строк;

Синтаксис запроса на добавление записей

INSERT INTO [имя таблицы, в которую добавл записи] (список полей таблицы)

SELECT список полей исходной таблицы

FROM имя таблицы

WHERЕ условие отбора;

Задание:

1. Загрузите Access, создайте таблицу «Кассеты»

код кассеты	фильм	жанр	продолжительность
1	По прозвищу зверь	боевик	150
2	Моя вторая мама	мелодрама	200
3	терминатор	боевик	200
4	Динозавры	триллер	200
5	Терминотор-2	боевик	120
6	С легким паром	мелодрама	230
7	Василиса Прекрасная	сказка	500
8	Высота	боевик	100
9	Скорость	боевик	120

2. В режиме SQL создайте запрос на обновление, изменив продолжительность фильмов с 200 мин. на 100 мин.

3. С помощью запроса на обновление замените название фильма Терминатор на Такси

4. С помощью запроса на удаление удалите фильмы, продолжительность которых 100 минут

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

6. Добавьте в  таблицу Старые кассеты фильмы, продолжительность которых более 230 минут (запрос на добавление)

7. Удалите с помощью запроса на удаление все сказки.

8. Замените жанр мелодрама на триллер;

9. Создайте таблицу «Боевик», включив в нее только боевики;

10. Добавьте в таблицу «Боевик», триллеры

Содержание отчета студентов:

1.     Опишите SQL-запросы.

2.     Ответьте на контрольный вопрос согласно варианту.

Контрольные вопросы:

1.     Синтаксис SQL – запросов на обновление.

2.     Синтаксис SQL – запросов на добавление.

3.     Синтаксис SQL – запросов на создание таблиц.

4.     Синтаксис SQL – запросов на удаление записей.

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

Тема: Построение форм

Цель: изучение технологии построения форм с помощью конструктора, встраивания элементов управления в форму.

Пояснение к работе:

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

Автоматизированные средства представляет Мастер форм. Мастер форм можно запустить из окна База данных щелчком на значке Создание формы с помощью мастера на панели Формы.

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

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

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

Раздел Примечание формы добавляется в форму вместе с разделом заголовка формы.

  Элементы управления представлены на Панели элементов: флажок. Переключатель, группа переключателей, поле, надпись и т.д.

Задание:

1.     . Загрузите Access, создайте таблицу  «Предпочтения по  шоколаду», содержащую следующие поля: Фамилия, Имя, Страна, Город, Адрес, Почтовый индекс, Дата ответа (текущая дата), Сорт шоколада, Орехи.

2.     Таблицу не заполняйте;

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

Содержание отчета студентов:

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

2.     Ответьте на контрольный вопрос согласно варианту.

Контрольные вопросы:

1.     Какие существуют способы создания форм?

2.     Разделы форм?

3.     Как добавит/удалить разделы заголовка, колонтитула формы?

4.     Виды элементов управления форм?

5.     Как создать группу переключателей, переключатель, флажки, кнопки?

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

Тема: Подчиненные формы.

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

Пояснение к работе:

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

Подчиненные формы особенно удобны для вывода данных из таблиц или запросов, связанных с отношением «один-ко-многим».

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

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

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

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

1.     непосредственно при создании формы;

2.     добавление подчиненной формы в готовую форму с помощью панели элементов;

Задание:

1. Создайте базу данных «Оптовый склад», состоящий из связанных таблиц: «Товары», «Поставщики», «Клиенты», «Поставки», «Продажи»:

2.     Таблицы не заполняйте;

3.     Создайте с помощью мастера формы «Товары» (на основе таблицы Товары), «Продажи» (на основе таблицы Продажи +поле Клиент), «Поставки» (на основе таблицы Поставки +поле Поставщик);

4.     На форме «Товары» постройте набор вкладок (режим конструктора формы/панель элементов/задайте имена вкладкам: Клиенты, Поставщики)

5.     Поочередно добавьте в область вкладок подчиненные формы: «Продажи», «Поставки» (режим конструктора формы/панель элементов/подчиненная форма/имеющиеся форма «Продажи»/определите поля связи между главной формой и подчиненной (код товара – код товара)

6.     Установите макет подчиненных форм как предложено на рис №2 (отсутствие разделительных линий, кнопок оконного меню, кнопок перехода по записям)

7.     Ключевые поля скройте (при условии, что тип данных- счетчик) ;

8.     Создайте на подчиненных формах кнопки перехода по записям

9.     Заполните базу данных

10. **Организуйте процедуру пересчета остатка товара на складе при его поступлении и продаже.

Содержание отчета студентов:

         1. Ответьте на контрольный вопрос согласно варианту.

Контрольные вопросы:

1.     Назначение подчиненных форм?

2.     Способы создания подчиненных форм?

3.     Способы отображения подчиненных форм?

4.     Отличие подчиненных форм от связанных?

5.     Назначение набора вкладок?

6.     Как создать набор вкладок, добавить вкладку?

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

Тема: Использование макрокоманд

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

Пояснение к работе:

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

Задание:

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

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

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

Необходимо обеспечить просмотр товаров, купленных некоторым клиентом (в свободное поле вносится фамилия некоторого клиента, в списке Имеющиеся покупатели отображаются все клиенты с данной фамилией (однофамильцы), при выборе необходимого клиента в списке Купленные товары отображаются купленные им товары)

3.     Создайте форму Продажа  на основе таблицы Продажа

4.     На форме «Продажа» создайте поле1 (с подписью введите фамилию) для ввода фамилии искомого покупателя

5.     На форме «Продажа» создайте список1 «Имеющиеся покупатели» на основе таблицы Покупатели, в источнике строк списка задайте условие отбора для элементов списка:

               фамилия покупателя =Forms![продажа]![Поле1]

6. Создайте макрос «Поиск» выбрав макрокоманды

                              обновление          

                               имя элемента  список1

7. На форме «Продажа» создайте кнопку «найти покупателя» выберите категорию:  разное, действия: выполнить макрос, укажите имя макроса «Поиск»

8.     На форме «Продажа» создайте список2  «Купленные товары» на основе таблицы «Товары»,  в источнике строк укажите условие отбора  код покупателя =  Forms![продажа]![Список1]

9.     Создайте макрос Товары

Макрокоманда:      обновление   имя элемента список2

10. Выполнение макроса наложите на событие после обновления  списка1

11. В форме «Продажа» поля код, код покупателя, код товара сделайте невидимыми

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

Указание: используйте макрокоманды: ОткрытьФорму, НаЗапись, КЭлементуУправления

Содержание отчета студентов:

1.     Опишите последовательность и аргументы макрокоманд задания № 12.

2.     Ответьте на контрольный вопрос согласно варианту.

Контрольные вопросы:

1.     Виды макрокоманд?

2.     Вид конструктора построения макросов?

3.     Назначение макрокоманды Обновление?

4.     Назначение и параметры макрокоманды ОткрытьФорму?

5.     Назначение и параметры макрокоманды НаЗапись?

6.     Назначение макрокоманды КЭлементуУправления?

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

Тема: Группы макросов.

Цель: изучение назначения и правил объединения макрокоманд в группы, обращения к группам макросов.

Пояснение к работе:

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

Имя, указанное при сохранении группы макросов, будет служить именем группы. Это имя будет выводиться в списке макросов и групп макросов в окне базы данных. Для ссылок на макросы, входящие в группу макросов, используется следующий синтаксис:

имяГруппыМакросов.имяМакроса

Например, имя «Кнопки.Товары» задает ссылку на макрос «Товары» в группе «Кнопки».

Задание:

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

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

         Необходимо автоматизировать процесс изменения цены товара (при двойном щелчке по названию товара в списке :

·        должна открываться форма «Товары» соответственно на данном товаре;

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

·        в дополнительном поле  вводится новая цена товара, нажатие на кнопку «Изменить»  меняет цена данного товара:

2.Создайте пустую форму «Список товаров» с помощью конструктора;

3.     На форме создайте список Имеющихся товаров;

4.     Создайте с помощью мастера форму «Товары»;

5.     Для создания макроса «По товарам» используйте следующие макрокоманды:

         Указание: аргументы макрокоманд определите самостоятельно.

6.     Выполнение макроса  «По товарам.Открытие»установите на событие двойное нажатие кнопки списка Имеющихся товаров в форме «Список товаров»

7.     Выполнение макроса  «По товарам.Изменение цены»установите на событие  нажатие кнопки   кнопки «Изменить»

8.     Дополните макрос «По товарам» (задайте имя группы Клиенты)  для автоматизации следующего действия: при щелчке мышью по Наименование товара в списке « Имеющиеся товары»:

1.     открывается форма либо отчет «Клиенты»

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

3.     **если товар никем не приобретался, то выводится сообщение «Не приобретался»

Содержание отчета студентов:

1.     Опишите макрос «По товарам».

2.     Ответьте на контрольный вопрос согласно варианту.

Контрольные вопросы:

1.     Назначение имен групп макросов?

2.     Как группе макросов задать имя?

4.     Как осуществить ссылку на макросы, входящие в группу макросов?

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

Тема: Условия на выполнение макрокоманд

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

Пояснение к работе:

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

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

Задание:

1.     Загрузите базу данных «Сотрудники»;

2.     Создайте форму «Сотрудники» с помощью мастера;

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

         Необходимо вывести сообщение «Привет, директору», если в текущей записи должность сотрудника – директор;

         Если стаж сотрудника больше 10 лет  и оклад менее 5000, то осуществить прибавку к окладу в размере 50%;

Если же стаж не более 10 лет или оклад более 5000 руб., то вывести сообщение «Нет прибавки»

3.     Создайте макрос «По сотрудникам»

Параметры макрокоманд определите самостоятельно;       

4.     на событие формы текущая запись задайте макрос По сотрудникам.директор

5.     макрос По сотрудникам.прибавка задайте на событие двойное нажатие кнопки по полю оклад;

6.     дополните самостоятельно макрос «По сотрудникам» (задав имя группы: Курсы), который будет выводить сообщение «Необходимо направить на курсы повышения квалификации», если сотрудник работает в отделе снабжения или сбыта и его стаж менее 3 лет

Содержание отчета студентов:

1.     Опишите аргументы макрокоманд задания № 3.

2.     Опишите макрос задания №6.

3.     Ответьте на контрольный вопрос согласно варианту.

Контрольные вопросы:

1.     Назначение условий на выполнение макрокоманд?

2.     Как отобразить столбец Условие  в конструкторе макроса?

3.     Как задать набор макрокоманд на одно условие?

4.     Какие существуют способы для записи условий?

5.     Правила записи составных условий?

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

Тема: Процедуры обработки событий

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

Пояснение к работе:

Изменение значений свойств формы или элемента управления с помощью макроса или процедуры обработки события позволяет добиться следующих результатов:

·         Скрыть или отобразить элемент управления в зависимости от значения другого элемента управления.

·         Отключить или заблокировать элемент управления в зависимости от значения другого элемента управления.

·         Вывести разные наборы записей в зависимости от выбора пользователя.

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

Задание:

1. Откройте базу данных «Продажа товаров»

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

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

Указание по выполнению:

1.     Создайте с помощью мастера форму «Товары»

2.     На форме создайте группу переключателей (группа1), свободное поле для ввода критериев поиска (поле1), список1(отображающий результат поиска по стране), список2 (отображающий результат поиска по названию), кнопку «Показать товары».

         Работа формы осуществляется следующим образом:

1.     по умолчанию оба списка невидимы,

2.     при нажатии переключателя  поле1 очищается и получает фокус,

3.     если выбран переключатель 1, то список1 не отображается, список2 отображается и обновляется, если выбран переключатель 2, то список2 не отображается, список1 отображается и обновляется. 

Для автоматизации работы формы необходимо:

1. Для группы переключателей на событие после обновления задайте процедуру:

Private Sub Группа1_AfterUpdate()

Поле1.Value = ""   (Null)

…(поле1 должно получить фокус)

Список1.Visible = False

…(Список2 не должен отображаться)

End Sub

2. Для кнопки «Показать товары» на событие нажатие кнопки задайте процедуру:

Private Sub Кнопка21_Click()

If Группа1 = 1 Then

…(Список2  отображается)

…(Список1 не отображается)

…(Список2 обновить)

End If

If …Then

…(Список2  не отображается)

…(Список1  отображается)

…(Список1 обновить)

End If

End Sub

Содержание отчета студентов:

1.     Опишите процедуры VBA.

2.     Ответьте на контрольный вопрос согласно варианту.

Контрольные вопросы:

1.     Назначение свойства Visible?

2.     Назначение свойства Value?

3.     Назначение свойства Requery?

4.     Как задать полю пустое значение?

5.     Назначение свойства SetFocus?

Литература

1.     Харитонова И. А., Михеева В. Д. Microsoft Access 2000.

      СПб.:БХВ-Петербург, 2001-1088 с.: ил.

2.     Вейскас Дж. Эффективная работа с Microsoft Access 2000 –

СПб: Питер,2001-1040 с.: ил.

3.     Биллариал Б.Программирование Access 2002 в примерах: Пер. с англ. - М.: КУЛИЦ-ОБРАЗ, 2003. - 496 с.

4.     Блютман К. Access.Трюки. Издательство:Питер,2006. - 331с.

5.     Грабер М. Справочное руководство по SQL, М., 1997. - 470c.

6.     Демин В. М., Кузин А. В. Разработка баз данных в системе Microsoft Access Издательство ИНФРА-М, ИЗДАТЕЛЬСКИЙ ДОМ, Форум, 2005. - 224с.

7.     Конноли Т, Бегг К, Страчан А. Базы данных. Проектирование, реализация и сопровождение. Теория и практика. Издательство: Вильямс, 2003. - 1440с.

8.     Кренке Д. Теория и построение баз данных. Издательство: Питер, 2003. -  800с.

9.     Кузнецов С.Д. Основы современных баз данных, К., 1999 -  440с.

10. Мак–Фердис. Формы, отчёты и запросы в Microsoft Access 2003. Перевод с английского.–М.: Издательский дом Вальянс, 2005. – 416 с.

11. Симанович С.В. Информатика: Базовый курс. – Спб:Питер,2003 - 640с.