Лабораторная работа №3. Создание запросов на выборку данных

Стр 1 из 2Следующая ⇒

Лабораторные работы

Часть 1. СУБД

Лабораторная работа №1. Создание таблиц базы данных

Цель работы:

- научиться составлять структуру таблицы и устанавливать свойства ее полей в режиме конструктора таблиц;

- уметь формировать условия в свойствах полей с помощью построителя выражений;

- освоить средство «Мастер подстановок» для формирования вводимых значений;

- научиться определять простой и составной первичный ключ;

- научиться вводить и корректировать данные в созданных таблицах;

- уметь устанавливать межтабличные связи.

Необходимо создать базу данных об успеваемости студентов в ВУЗе на основе трех таблиц: «Группа», «Студент» и «Успеваемость». В таблицах 1-3 представлены основные параметры таблиц.

Таблица 1 - Описание свойств полей таблицы «Группа»

Имя поля	Ключ. поле	Тип данных	Размер, байт	Число десятич. знаков	Подпись поля	Условие на значение
						Сообщение об ошибке
НГ	Да	Текстовый	5		Номер группы
КСПЕЦ		Текстовый	6		Код специальности
КОЛ		Числовой	Байт	0	Кол-во студентов в группе	>=0 And <=40
						Кол. студентов больше допустимого
ПБАЛЛ		Числовой	Одинарное с пл. точкой	2	Проходной балл	>2 And <=5
						Ошибка в оценке

Таблица 2 - Описание свойств полей таблицы «Студент»

Имя

поля

Ключ.

поле

Тип

данных

Размер,

байт

Число

десятич.

знаков

Подпись поля

Условие на значение

Сообщение об ошибке

НГ

Да

Текстовый

Группа

НС

Да

Текстовый

Номер студента в группе

ФИО

Текстовый

ФИО

ПОЛ

Подстановкой (м, ж)

Пол

ГОДР

Дата/Время

Краткий форм. д.

Год рождения

АДРЕС

Текстовый

Адрес

ПБАЛЛ

Числовой

Одинарное с пл. точкой

Проходной балл

Таблица 3 - Описание свойств полей таблицы «Успеваемость»

Имя поля	Ключ. поле	Тип данных	Размер, байт	Число десятич. знаков	Подпись поля	Условие на значение
						Сообщение об ошибке
НГ	Да	Текстовый	5		Номер группы
НС	Да	Текстовый	2		Номер студента
ПР	Да	Подстановкой	20		Название предмета
ОЦЕНКА		Числовой	Целое	0	Оценка	>2 And <=5
						Ошибка в оценке

Задание

I. Создание структуры базы данных

1. Запустите программу Microsoft Access.

2. В первом диалоговом окне Access установите переключатель Новая база данных.

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

4. В окне новой базы данных из списка объектов выберите объект Таблицы. Создайте структуру таблицы Группа в режиме Конструктора, используя параметры из таблицы 1. Поле НГ (номер группы) сделайте ключевым, выбрав эту настройку из панели инструментов , контекстного меню соответствующего имени поля или пункта меню Правка .

5. При закрытии окна Конструктора структура таблицы сохраняется под задаваемым именем. Сохраните структуру первой таблицы под именем Группа.

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

7. Назначьте составной уникальный ключ, состоящий из двух полей: НГ (номер группы) и НС (номер студента в группе). Для выделения обоих полей пользуйтесь клавишей CTRL.

8. Сохраните таблицу под именем Студент.

9. Создайте структуру таблицы Успеваемость, используя параметры таблицы 3. Условие на значение для поля ОЦЕНКА сконструируйте сами Построителем выражений. Для поля ПР примените Мастер подстановок (Информатика, Математика, История, Иностранный язык).

10. Так как ни одно поле этой таблицы не претендует на уникальность, задайте составной уникальный ключ из полей НГ+НС+ПР.

11. Сохраните таблицу под именем Успеваемость.

II. Установка связей между таблицами

12. Выполните команду Сервис-Схема данных или щелкните на кнопке Схема данных.

13. В окне Схемы данных расположите все три таблицы.

14. Установите между таблицами Группа ® Студент связь по простому ключу НГ.

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

16. Между таблицами Студент ® Успеваемость установите связь по составному ключу НГ+НС. Для этого в главной таблице Студент выделите оба этих поля, удерживая клавишу CTRL, и перетащите их на поле НГ таблицы Успеваемость.

17. Установите все флажки, обеспечивающие целостность во взаимосвязанных таблицах.

18. Сохраните схему данных и закройте окно.

19. Откройте таблицы Группа и Студент. Проверьте, как влияют установленные параметры поддержания связной целостности:

- в таблице Группа измените номер группы (что произошло в подчиненной таблице Студент?);

- в подчиненную таблицу Студент попытайтесь ввести запись с номером группы, не представленным в главной таблице Группа;

- в главной таблице Группа, чтобы не очень жалеть о случившемся, удалите запись, которая вводилась последней (выделите ее и выполните команду Правка-Удалить); что произошло в подчиненной таблице Студент?

20. Закройте таблицы Группа и Студент.

III. Ввод и корректировка записей в связанных таблицах

21. Откройте таблицу Группа.

22. Создайте записи для трех групп.

23. Используя значки «+» в строке записи, введите несколько записей в подчиненную таблицу Студент.

24. Таким же образом введите записи в подчиненную таблицу Успеваемость (если «плюсы» не отображены, выполните команду Вставка-Подтаблица).

25. Попробуйте поставить оценку 1 какому-нибудь студенту по любому предмету. Почему не получается?

26. Покажите работу преподавателю. Закройте окно базы данных и Access.

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

1. Дайте определение базы данных (БД), СУБД, ключевого поля БД.

2. В файле какого типа сохраняется база данных?

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

4. Поясните разницу между первичным, внешним и составным ключом.

5. В каких свойствах поля задается ограничение на его значение и заголовок столбца таблицы?

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

7. В отношениях какого типа могут находиться две связанные таблицы?

8. Для чего создается схема данных в базе?

9. Что понимается под связной целостностью базы данных?

10. Для чего предназначен значок «+» в левом столбце таблицы?

11. Какая команда позволяет открыть связанные записи главной и подчиненной таблицы?

12. Для чего при создании структуры таблицы используют Мастер подстановок?

13. Каким образом можно внести изменения в уже созданную структуру таблицы?

Лабораторная работа №2. Поиск, сортировка и фильтрация данных в таблицах

Цель работы:

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

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

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

Задание

1. Откройте свою базу данных.

2. Откройте таблицу Студент.

3. Отсортируйте ее по полю ФИО. Проверьте результат.

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

5. Восстановите сортировку по первичному ключу.

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

7. С помощью фильтра по выделенному показать следующие записи (перед выполнением следующего фильтра удаляйте предыдущий):

- с фамилиями студентов, начинающихся на конкретную букву;

- студентов с заданным годом рождения;

- студентов одной из групп;

- студентов мужского пола этой же группы (последовательное выделение).

8. Обычным фильтром сделайте следующий отбор записей из таблицы Студент:

- студентов из группы М (вместо М укажите номер группы);

- студентов из группы М, родившихся после конкретной даты;

- студентов с проходным баллом > 4,4 из групп М и N;

- студентов, проживающих по заданной улице.

9. Закройте таблицу Студент.

10. Обычным фильтром сделайте отбор записей из таблицы Успеваемость:

- покажите записи с двойками по информатике.

11. Узнайте фамилии этих студентов. Для этого выполните команду Вид-Подтаблица, выберите таблицу Студент и воспользуйтесь значками «+».

12. Покажите работу преподавателю и закройте базу данных.

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

1. Какая команда используются для поиска записей?

2. Возможна ли сортировка по нескольким полям?

3. Какие типы фильтров можно использовать для отбора записей?

4. Можно ли с помощью фильтра по выделенному задать несколько значений полей для отбора записей?

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

6. Какая команда применяется для удаления сохраненного фильтра?

7. Чем фильтр отличается от сортировки?

Лабораторная работа №3. Создание запросов на выборку данных

Цель работы:

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

- уметь создавать запросы с параметром;

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

- уметь задавать групповые операции и вычисляемые поля в запросах;

- уметь создавать запросы на основе других запросов.

Задание

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

1. Откройте свою базу данных.

2. Запрос 1 (Студентки группы N). Перейдите в объект базы данных Запросы. Выполните двойной клик на команде Создание запроса в режиме конструктора. В окно бланка запроса добавьте таблицу Студент. Закройте окно добавления таблиц. Нужно вывести список студенток из группы N заданного года рождения, при этом номер группы и пол на экран не выводить. Для выполнения этого задания из таблицы Студент последовательно методом буксировки перенесем поля НГ, ФИО, ПОЛ, и ГОДР. Сформируем условия отбора запроса, как показано на рисунке 1. Снимем флажки в полях НГ и ПОЛ.

Рисунок 1 – Запрос «Студентки группы 21-6 в режиме конструктора

Запустим запрос на исполнение . Закроем запрос с сохранением, задав ему имя Студентки группы N.

3. Запрос 2 (Запрос с параметром). Вывести адреса и фамилии студенток из группы, номер которой запрашивается с клавиатуры при выполнении запроса. Запрос создается аналогично предыдущему. Параметр запроса формируется в строке Условие отбора в квадратных скобках. Пример приведен ниже на рисунке 2.

Рисунок 2 – Формирование запроса с параметром

4. Запрос 3 (Двоечники). Какие студенты имеют двойки хотя бы по одному предмету? В запросе отобразить поля: Номер группы, Фамилия студента, Название предмета и оценка. Для выполнения этого задания необходимо в бланк конструктора запроса добавить две таблицы: Студент и Успеваемость. Остальное выполняется аналогично предыдущему.

5. Запрос 4 (Высокий проходной балл). У каких студентов проходной балл при поступлении выше проходного бала в группе? В отобранных записях должны быть поля с номером группы, фамилией студента, проходным баллом студента и проходным баллом группы. В условиях отбора используйте выражения с операторами и именами полей. Для выполнения этого задания в бланк запроса необходимо добавить таблицы Группа и Студент, из которых выбираются нужные поля. Условие отбора поля ПБАЛЛ (таблицы Студент) задается Построителем выражений в соответствии с рисунком 3.

Рисунок 3 – Диалоговое окно построителя выражений

6. Запрос 5 (Возраст студентов). Выдать список студентов старше N лет. В запросе предусмотреть поля с фамилией, адресом, датой рождения и вычисляемым полем Возраст. Для построения этого запроса выбираем таблицу Студент, из нее – три поля в соответствии с заданием. Четвертое поле – Возраст – формируем при помощи Построителя выражений путем формирования в название поля выражения: =2011-Year([Студент]![ГОДР]). При этом название функции Year выбираем из списка Встроенных функций категории Дата/время, а поле ГОДР – из таблицы Студент в Построителе выражений. Ограничение на возраст задаем в Условии отбора сформированного вычисляемого поля в бланке запроса.

7. Запрос 6 (Число студентов в группе). Посчитать фактическое число студентов в каждой группе. Это однотабличный запрос с групповой операцией и статистической функцией. Запрос формируется на основе добавления в бланк конструктора запроса категории Групповая операция из панели инструментов . Из таблицы Студент дважды выбирается поле НГ и для них из списка групповых операций берется функция Группировка и Count – подсчет количества записей (рисунок 4).

Рисунок 4 – Фрагмент запроса с использованием групповой операции

8. Запрос 7 (Средний проходной балл в группе). Отобразить с точностью до сотых долей средний проходной балл в группе по таблице Студент. Выполняется аналогично предыдущему запросу, но работаем с функцией Avg по полю ПБАЛЛ. Для данного поля в контекстном меню Вывода на экран устанавливаем в Свойствах поля Формат поля С разделителем и Числом десятичных знаков – 2.

9. Запрос 8 (Средний балл студента). Посчитать с точностью до сотых долей средний балл каждого студента. Запрос постройте на базе двух таблиц: Студент и Успеваемость. Используйте групповые операции и функцию Avg. по полю Оценка.

10. Запрос 9 (Средний балл в группе).

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

12. Запрос 11 (Оценки по информатике). Сконструировать перекрестный запрос, который выдает следующую таблицу. Заголовками строк являются номера групп, а заголовками столбцов – значения оценок (5, 4, 3, 2). На пересечении строк и столбцов указано количество пятерок, четверок, троек и двоек, полученных по информатике.

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

1. Дайте понятие запроса базы данных.

2. Как в бланке запроса отобразить нужные поля таблицы?

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

4. Какие операции сравнения и логические операторы можно использовать в условиях отбора?

5. Как вводится параметр в запрос?

6. Как указываются в запросе одинаковые поля, принадлежащие разным таблицам?

7. Допускается ли группировка записей запроса по нескольким полям?

Дата добавления: 2018-06-01; просмотров: 772; Мы поможем в написании вашей работы!

Поделиться с друзьями:

12 Следующая ⇒

Мы поможем в написании ваших работ!