Создание итогового запроса. Разработка баз данных и приложений в 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

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

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

Выполнение:

12
• 3
• Далее ⇒