Создание итогового запроса. Разработка баз данных и приложений в MS Access

ПРИЛОЖЕНИЕ 3

Разработка баз данных и приложений в MS Access

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

Предметная область:

объекты: студенты, группы, предметы, преподаватели

взаимодействие объектов: экзаменационная ведомость

Показать таблицы и связи между ними. В таблицах привести перечень полей с указанием имени и типа каждого поля. Отметить первичные (pk) и внешние (fk) ключи для каждой таблицы. Задать правила удаления: ограниченное [R] или каскадное [C].

Выполнение

Задание 2. Реализовать проект разработанной в задании 1 базы данных средствами СУБД MS Access. Подготовить схему данных, три типа запросов и формы ввода данных.

ЛАБОРАТОРНАЯ РАБОТА №1

«Создание базовых таблиц»

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

Выполнение:

1. Запустите программу Microsoft Access (Пуск > Программы > Microsoft Access).

2. В окне Microsoft Access включите переключатель Новая база данных и щелкните на кнопке ОК.

3. В окне Файл новой базы данных выберите папку \Мои документы и дайте файлу имя: Экзамены. Убедитесь, что в качестве типа файла выбрано Базы данных Microsoft Access, и щелкните на кнопке Создать. Откроется окно новой базы – Экзамены.

4. Откройте панель Таблицы.

5. Дважды щелкните на значке Создание таблицы в режиме конструктора – откро­ется бланк создания структуры таблицы.

6. Для таблицы Студенты введите следующие поля:

Имя поля	Тип поля
Код студента	Счетчик
Фамилия	Текстовый
Имя	Текстовый
Отчество	Текстовый
Код группы	Числовой

7. Щелкните на поле Код студента. Щелчком правой кнопки мыши над полем откройте контекстное меню и выберите в нем пункт Ключевое поле.

8. Щелкните на поле Код группы. В нижней части бланка задайте свойство Размер поля равным Целое.

9. Закройте окно Конструктора. При закрытии окна дайте таблице имя Студенты.

10. Повторив действия пунктов 5-9, создайте таблицы Группы, Преподаватели, Предметы и Ведомость. Для таблицы Ведомость при определении ключевого поля, состоящего из трех полей (Код студента, Код предмета и Дата), нажмите клавишу SHIFT и с нажатой левой клавишей мыши выделите указанные три поля черным цветом. Затем нажмите правую клавишу мыши и из контекстного меню выберите пункт Ключевое поле.

11. В окне Экзамены: база данных откройте (дважды щелкнув на названии) по очереди созданные вами таблицы Группы, Предметы и Преподаватели и наполните их экспериментальным произвольным содержанием (5-6 записей). Таблицы Студенты и Ведомость будут заполнены позже. Закончив работу, закройте таблицы.

ЛАБОРАТОРНАЯ РАБОТА № 2

«Назначение мастера подстановок»

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

Выполнение:

1. Многие таблицы содержат коды объектов, а не их названия. Например, в таблице Студенты есть поле Код группы. При вводе данных в такую таблицу пришлось бы вместо названия группы вводить ее числовой код, что неудобно, т.к. требует запоминания значений кодов. Используя Мастер подстановок, MS Access упрощает процедуру ввода и избавляет пользователя от необходимости запоминать коды объектов.

2. В окне Экзамены: база данных перейдите на таблицу Студенты и нажмите кнопку . Встаньте на поле Код группы и в колонке Тип данных для этого поля выберите тип Мастер подстановок.

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

4. В появившемся списке таблиц выберите Таблица: Группы и нажмите кнопку Далее.

5. Из списка Доступные поля кнопкой > переместите поле Название в правое окно Выбранные поля и нажмите кнопку Далее.

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

7. Мастер подстановок настроен. Откройте таблицу Студенты и наполните ее произвольным содержанием (9-10 записей). Обязательно добавьте студента с фамилией Иванов.

8. Повторяя пункты 3-6, настройте Мастера подстановок для трех полей таблицы Ведомость (Код студента, Код преподавателя, Код предмета).

9. Наполните таблицу Ведомость произвольными данными (14-15 записей). Для студента Иванова введите 3-4 записи.

ЛАБОРАТОРНАЯ РАБОТА №3

«Создание межтабличных связей»

Цель:научиться связывать таблицы.

Выполнение:

1. Вернитесь в окно Экзамены: база данных и разыщите на панели инструментов кнопку Схема данных . Если есть сложности, найдите команду строки меню: Сервис > Схема данных. Воспользуйтесь любым из этих средств, чтобы открыть окно Схема данных. Одновременно с открытием этого окна открывается диалоговое окно Добавление таблицы, на вкладке Таблицы которого можно выбрать таблицы, между которыми создаются связи.

2. Щелчком на кнопке Добавить выберите все 5 таблиц, созданные ранее. В окне Схема данных откроются списки полей этих таблиц.

3. Поместите курсор на поле Код студента таблицы Студенты и при нажатой левой клавише мыши тащите курсор на поле Код студента таблицы Ведомость. При отпускании кнопки мыши автоматически откроется диалоговое окно Изменение связей. Поставьте галочку у строки Обеспечение целостности данных и нажмите кнопку ОК.

4. В окне Схема данных рассмотрите образовавшуюся связь. Убедитесь в том, что линию связи можно выделить щелчком левой кнопки мыши, а щелчком правой кнопки мыши открывается контекстное меню, позволяющее разорвать связь или отредактировать ее.

5. Повторяя пункты 3 и 4, установите связи между остальными таблицами базы данных. Полученная схема данных должна выглядеть так, как показано на рисунке. Проследите, чтобы значки «1» и «¥» (отношение «один ко многим») были расставлены правильно.

ЛАБОРАТОРНАЯ РАБОТА №4

«Создание запросов»

Цель:научиться работать с запросами.

Выполнение:

1) создание запроса на выборку

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

1. В окне Экзамены: база данных откройте панель Запросы. Дважды щелкните на значке Создание запроса в режиме Конструктора – откроется бланк запроса по образцу. Одновременно с ним откроется диалоговое окно Добавление таблицы.

2. В окне Добавление таблицы выберите таблицу Ведомость и щелкните на кнопке Добавить. Закройте окно Добавление таблицы.

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

4. Задайте условие отбора для поля Оценка. В соответствующую строку введите: > 3. Из таблицы будут выбираться не все записи, а только те, полученная оценка которых превышает 3.

5. Закройте окно конструктора и вернитесь в окно Экзамены: база данных. На вопрос о названии запроса введите Хорошисты. Проверьте правильность работы запроса.

6. Выбрав кнопку , усложним запрос, отбирая только записи для студента Иванова. Щелкните правой клавишей мышки над частью окна с отображением включенных таблиц и из появившегося меню выберите строку Добавить таблицу. Добавьте таблицу Студенты. В нижней части экрана в правой свободной колонке (4-ой) выберите из таблицы Студенты поле Фамилия. Галочку вывода на экран отключите и задайте условие отбора «Иванов». Закройте окно конструктора запроса и проверьте правильность работы запроса.

2) создание запросов «с параметром»

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

1. В окне Экзамены: база данных откройте панель Запросы. Дважды щелкните на значке Создание запроса в режиме Конструктора – откроется бланк запроса по образцу.

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

3. Строку Условие отбора для поля Оценка надо заполнить таким образом, чтобы при запуске запроса пользователь получал предложение ввести нужное значение. Текст, обращенный к пользователю, должен быть заключен в квадратные скобки. Если бы мы хотели отобрать записи, оценка для которых равна 5, мы бы написали: 5. Но если мы хотим дать пользователю возможность выбора, мы должны написать: [Введите оценку].

4. Закройте запрос. При закрытии сохраните его под именем Выбор по оценке. Проверьте в правильности работы запроса.

5. В окне Экзамены: базы данных дважды щелкните правой клавишей мыши над запросом Выбор по оценке и в появившемся меню выберите строку Сохранить как. В появившемся окне задайте новое имя запроса Выбор по фамилии. Вызовите для нового запроса Конструктор и, удалив условие выбора для поля Оценка в условии выбора для Фамилия введите [Введите фамилию студента]. Выйдите из Конструктора и проверьте правильность работы нового запроса.

создание итогового запроса

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

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

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

2. В бланк запроса по образцу введите поле Оценка таблицы Ведомость и поле Фамилия таблицы Студенты.

3. На панели инструментов Microsoft Access щелкните на кнопке Групповые операции или воспользуйтесь строкой меню (Вид > Групповые операции). Эта команда необходима для создания в нижней части бланка строки Групповые операции. Именно на ее базе и создаются итоговые вычисления. Все поля, ото­бранные для запроса, получают в этой строке значение Группировка.

4. Для поля, по которому производится группировка записей (в нашем случае – Фамилия), оставьте в строке Групповые операции значение Группировка. Для поля Оценка щелкните в этой строке – появится кнопка раскрывающегося списка, из которого можно выбрать итоговую функцию для расчета значений в данном поле. Средний балл можно подсчитать с помощью итоговой функции Avg. Закройте бланк запроса по образцу и дайте ему имя: Средний балл. Запустите запрос и убедитесь, что он правильно работает.

5. Усложним запрос, добавив выбор фамилии. Для запроса Средний балл вызовите Конструктор и для поля Фамилия в строке Условие отбора задайте: [Введите фамилию].

ЛАБОРАТОРНАЯ РАБОТА № 5

«Работа с формами»

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

Выполнение: