Конструирование запросов в СУБД Access.

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

Создание базы данных. Конструирование таблиц в СУБД Access

Лабораторное занятие № 6.9.

Тема 6. Технологии организации, хранения и обработки данных

1. Создание базы данных. Конструирование таблиц в СУБД Access.

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

3. Конструирование запросов в СУБД Access.

ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ

БД MS Access - это совокупность взаимосвязанных реляционных таблиц, в каждой из которых содержатся сведения об одной сущности. Каждый столбец таблицы называют полем, строку - записью.

В Microsoft Access поддерживаются два метода создания Базы данных Access:

· с помощью мастера создания баз данных;

· создание пустой базы данных, с добавлением в нее таблиц, форм, отчетов и других объектов.

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

Создание таблицы состоит из двух этапов: определение её структуры (состав полей, их имена, тип и размер каждого поля, ключевые поля и другие свойства) и создание записей таблицы, т.е. заполнение её данными.

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

В нормализованной реляционной базе данных связь двух таблиц характеризуется отношениями записей типа один-к-одному, один-ко-многим, многие-к-многим. Отношение один-к-одному предполагает, что каждой записи одной таблицы соответствует одна запись в другой. Отношение один-ко-многим предполагает, что каждой записи первой таблицы соответствует много записей другой таблицы, но каждой записи второй таблицы соответствует только одна запись в первой. Соответственно, связь многие-к-многим – это связь многих записей одной таблицы ко многим записям другой.

Одним из основных инструментов обработки данных в СУБД являются запросы. В Microsoft Access имеется удобное для пользователя графическое средство формирования запроса по образцу — QBE (Query By Example), с помощью которого легко может быть построен сложный запрос.

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

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

· выбрать записи, удовлетворяющие условиям отбора;

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

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

· сгруппировать записи с одинаковыми значениями в одном или нескольких полях для выполнения над ними групповых функций;

· произвести обновление полей в выбранном подмножестве записей;

· создать новую таблицу базы данных, используя данные из существующих таблиц;

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

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

В Access может быть создано несколько видов запроса:

· запрос на выборку — выбирает данные из взаимосвязанных таблиц и других запросов. Результатом его является таблица, которая существует до закрытия запроса;

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

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

ПРАКТИЧЕСКИЕ ЗАДАНИЯ

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

2.

Создайте новую базу данных: Создать файл... – Новая база данных (Рис. 1 и Рис. 2).

Рисунок 1 – Создать файл... Рисунок 2 – Новая база данных…

3. Задайте имя своей папки (свой диск) и имя базы Дела.

4. Нажмите кнопку Создать.

5. Создайте таблицу Паспортные данные, для этого выберите вкладку Таблица (скорее всего, Вы в ней и находитесь) инажмите кнопку Конструктор (Рис.3).

Рисунок 3 – Вкладка Таблицы.

6. Заполните поля в Конструкторе данными из Таблицы 1.

Таблица 1

Имя поля	Тип данных	Описание
Код личности	Счетчик
Фамилия	Текст	Размер поля – 18
Имя	Текст	Размер поля – 12
Отчество	Текст	Размер поля – 15
Дата рождения	Дата/Время	Краткий формат даты
Номер паспорта	текстовый	Размер поля – 10
Кем выдан	текстовый	Размер поля – 15
Адрес	текстовый	Размер поля – 15

6. Задайте ключевое поле таблицы Код личности.

7. Сохраните и закройте таблицу Паспортные данные.

8. Создайте таблицу Протоколы со следующей структурой:

Имя поля	Тип данных	Описание
Код личности	числовой
Фамилия правонарушителя	Текстовый	Длина поля – 18
Номер протокола	Текстовый	Длина поля – 3
Состав правонарушения	Текстовый	Длина поля – 20
Следователь	Текстовый	Длина поля – 18
Дата правонарушения	Дата/время	Краткий формат даты
Дата суда	Дата/время	Краткий формат даты
Штраф	денежный
Привлекается впервые	логический

9. Сохраните и закройте таблицу Протоколы. Ключевое поле не задаем.

10.
Нажмите на кнопку Схема данных. Добавьте две таблицы Протоколы и Паспортные данные (Рис.4).

Рисунок 4 – Схема данных

11. Нажмите левой кнопкой мыши на Код личности в таблице Протоколы и перетащить на Код личности в таблице Паспортные данные.

12.
Поставьте «галочки» в пунктах Обеспечение целостности данных, Каскадное обновление связанных полей и Каскадное удаление связанных записей (Рис.5).

Рисунок 5 – Создание связей

13. Щелкните кнопку Создать и закройте окно Схема данных.

14.
Откройте таблицу Паспортные данные в режиме Таблицы (Вид – Режим таблицы) и заполните таблицы следующими данными (Рис.6):

Рисунок 6 – Данные для заполнения таблиц

15. Создайте таблицу Следователи путем ввода данных (Рис.7).

Рисунок 7 – Таблица Следователи.

16. Перейдите в режим Конструктора и установите ключевое поле на поле Фамилия. Сохраните и закройте таблицу.

17. Установите связи между таблицами согласно (Рис. 8).

Рисунок 8 – Связи между таблицами

18. Перейдите на вкладку Запросы.

19.

Создайте запрос в режиме Конструктора, который содержит следующие данные: Фамилия, Имя, Отчество, Состав правонарушения (Рис. 9).

Рисунок 9 – Создание запроса в режиме Конструктора

20. Выберите необходимые данные из двух таблиц двойным щелчком мыши. Затем выполните запрос, выбрав в панели меню Запрос – Запуск.

21. Сохраните запрос под именем Правонарушения.

22. Выполните последовательность действий Запросы – Создание запроса в режиме конструктора.

23. Добавьте таблицы Паспортные данные и Протоколы в диалоговое окно Запрос2 и перенесите из таблиц поля: Фамилия, Номер паспорта, Состав правонарушения и Следователь.

24. Задайте режим сортировки по возрастанию в поле запроса Фамилия. В поле Следователь задайте условие отбора: Иванов или Сидоров.

25. Выполните запрос и сохраните запрос под именем Следователи.

26. Выполните последовательность действий Запросы – Создание запроса в режиме конструктора.

27. Добавьте таблицы Паспортные данные и Протоколы в диалоговое окно Запрос3 и перенесите из таблиц поля: Фамилия, Номер паспорта, Состав правонарушения и Дата правонарушения.

28. Задайте для поля Дата правонарушения режим сортировки По возрастанию и условие отбора: Between 01.10.2012 And 01.10.2013

29. Выполните запрос и сохраните его под именем Правонарушения за период.

30. Выполните последовательность действий Запросы – Создание запроса в режиме конструктора.

31. Добавьте таблицы Паспортные данные и Протоколы в диалоговое окно Запрос4 и перенесите из таблиц поля: Фамилия, Номер паспорта, Штраф, Штраф, Штраф, Штраф и Следователь.

32. Включите групповые операции, нажав на панели инструментов на значок , после этого в карточке запроса появится слово группировка. Выставите минимальный, максимальный, средний и количествоштрафа (мin, мах, avg,count).

33. Выполните запрос и сохраните его под именем Штрафы.

34. Выполните последовательность действий Запросы – Создание запроса в режиме конструктора.

35. Добавьте таблицу Протоколы в диалоговое окно Запрос5 и перенесите из таблицы поля: Следователь и Штраф.

36. Включите групповые операции. Выставите количествоштрафа – count.

37. Щелкните левой кнопкой мыши в третьем столбце и в панели инструментов нажать кнопку Построить. .

38. Напечатайте: Премия: ([Count-Штраф]*100000).

39. Выполните запрос и сохраните его под именем Премия.

40. Создайте аналогично Запрос6, которыйдолжен содержать следующие данные: Премия каждому следователю по 200000 рублей за каждое дело в одном районе.

ДОПОЛНИТЕЛЬНЫЕ ЗАДАНИЯ

41. Создайте в Access таблицу сотрудников ГОВД, содержащую не менее 6 полей, введите 5 записей, сделайте выборку всех сотрудников, проживающих в микрорайоне, номер которого вводится пользователем.

42. Создайте в Access таблицу СОТРУДНИКИ, содержащую не менее 6 полей, введите 5 записей, создайте запрос, который показывает все записи содержащие фамилии адвокатов, начинающиеся на букву введенную пользователем.

43. Создайте в Access таблицу, СЛЕДОВАТЕЛЬ, содержащую не менее 6 полей, введите 5 записей. Сделайте запрос на выбор всех записей, содержащих вид раскрытого преступления, название которого вводится пользователем.

Дата добавления: 2017-02-01