Файл: Механика и математическое моделирование самара издательство Самарского университета 2021.pdf

24
В нашей базе данных таблицы Товары и Покупки связаны через поле Код товара, таб- лицы Покупки и Покупатели связаны через поле Покупатель, таблицы Товары и Произво-
дители связаны через поле Производитель (Рисунок 7).
Рисунок 7 – Схема данных со связями один ко многим
Связь один к одному
Эта связь возникает, когда на обеих сторонах связи поля являются первичными ключами, т.е. одной записи первой таблицы соответствует только одна запись второй таблицы
(Рисунок 8).
Рисунок 8 – Схема данных со связью один к одному
Свойства связей
На каждой из связей можно использовать следующие свойства:

обеспечение целостности данных;

каскадное обновление связанных полей;

каскадное удаление связанных записей.

25
Включенное свойство обеспечение целостности данных не позволит в поле таблицы на стороне много этой связи вводить значения, которых нет в поле таблицы на стороне один. Таким образом, в базе данных поддерживается ссылочная целостность – на стороне много нельзя вво- дить значения, которых нет на стороне один. Это очень полезное свойство, поэтому его обычно включают.
Включенное свойство каскадное обновление связанных полей позволяет при изменении данных в поле на стороне один автоматически изменять его в поле таблицы на стороне много.
Так как изменение данных в одной записи на стороне один приводит к изменению данных во всех связанных записях на стороне много, то неконтролируемое использование этого свойства может привести к потере данных.
Включенное свойство каскадное удаление связанных записей позволяет при удалении за- писи на стороне один автоматически удалять связанные записи в другой таблице с таким же зна- чением на стороне много. Неконтролируемое использование этого свойства может привести к потере данных в связанных таблицах (Рисунок 9).
Рисунок 9 – Свойства связей
Порядок заполнения таблиц
Таблицы необходимо заполнять, начиная со стороны один связей (Рисунок 10).

26
Рисунок 10 – Порядок заполнения таблиц
Таким образом, возможны два варианта порядка заполнения таблиц:

Производители – Покупатели – Товары – Покупки;

Покупатели – Производители –Товары – Покупки.
КОНТРОЛЬНЫЕ ВОПРОСЫ
1. Перечислите этапы проектирования базы данных.
2. Что такое предметная область?
3. Перечислите преимущества декомпозиции данных.
4. Перечислите различия между первичным и внешним ключами.
5. Назовите типы связей в реляционной базе данных.

27
4. ЗАПРОСЫ НА ВЫБОРКУ
ЧТО ТАКОЕ ЗАПРОС. ВИДЫ ЗАПРОСОВ
С помощью запроса можно выполнить любую обработку данных из таблиц или запросов.
Запросы делятся на два вида: запросы на выборку и запросы на изменение:

запросы на выборку позволяют отбирать поля одного или нескольких источни- ков, сортировать, отбирать записи, создавать новые поля, группировать данные для подведения итогов и создания перекрестных таблиц. Эти запросы не изменяют исходные данные;

запросы на изменение позволяют создавать новые таблицы как объекты, уда- лять, добавлять записи, обновлять данные в полях. Эти запросы изменяют исход- ные данные.
ИСТОЧНИКИ И РЕЗУЛЬТАТЫ ВЫПОЛНЕНИЯ ЗАПРОСА
Источниками запроса могут быть таблицы и/или запросы.
Результатом выполнения запроса является виртуальная таблица с обработанными запи- сями. Эта таблица не сохраняется как объект в базе данных. Ее можно использовать для постро- ения отчетов и форм. Результаты выполнения запроса представляются пользователю в виде форм или отчетов (Рисунок 11).
Запросы
Таблицы
Запрос
Форма
Отчет
Таблица
Рисунок 11 – Источники запроса
ОСОБЕННОСТИ ЗАПРОСОВ
Запросы имеют следующие особенности:

в качестве источника запроса могут быть одна или несколько таблиц и запросов;

запрос может содержать любое количество полей;

28

поля запроса могут участвовать в запросе, но не выводиться на экран;

в запросе могут быть созданы новые вычисляемые поля;

запрос динамически связан с источником – при изменении и добавлении данных в запросе меняются или добавляются соответствующие данные в таблице, при из- менении и добавлении данных в таблице меняются или добавляются соответству- ющие данные в запросе;

запросы могут изменять данные.
ТИПЫ ЗАПРОСОВ И СРЕДСТВА ДЛЯ ИХ СОЗДАНИЯ
Типы запросов
В Access используются следующие типы запросов.
1. Запросы на выборку. Эти запросы не изменяют данные, однако с их помощью можно создавать новые поля:

отбор полей одной или нескольких таблиц;

сортировка записей;

отбор записей по критериям (запрос на выборку);

запрос с параметром;

запрос с вычисляемыми полями;

запрос с итогами;

перекрестный запрос.
2. Запросы на изменение. Эти запросы изменяют данные (добавляют, удаляют, обнов- ляют, создают новые таблицы). Такие запросы требуют особого внимания при выпол- нении:

запрос на добавление;

запрос на удаление;

запрос на обновление;

запрос на создание таблицы.

29
Средства для создания запросов
Access предлагает следующие средства для создания запросов:
1. Мастер запросов. Позволяет создавать лишь некоторые типы запросов в режиме диалога. Редактирование запроса с помощью мастера невозможно.
2. Конструктор запросов. Позволяет создавать и редактировать любые типы запросов.
В конструкторе Access запросы формируются путем заполнения бланка запроса. Эта технология называется «создание запроса по образцу» – QBE (Query By Example).
3. Язык программирования запросов SQL (Structured Query Language – структуриро- ванный язык запросов). Универсальное средство создания и редактирования любых запросов. Является стандартом любой системы поддержки реляционных СУБД.
Использование средств создания запросов
Запросы могут быть созданы разными средствами (Таблица 7).
Таблица 7 – Средства создания запросов
Тип запроса
Средство создания
Запросы на выборку
Отбор полей одной или нескольких таблиц
Мастер запросов "Простой запрос", кон- структор запросов
Сортировка записей
Конструктор запросов
Отбор записей по критериям (запрос на вы- борку)
Запрос с параметром
Запрос с вычисляемыми полями
Запрос с итогами
Мастер запросов "Простой запрос", кон- структор запросов
Перекрестный запрос
Мастер запросов "Перекрестный запрос", конструктор запросов

30
ОПРЕДЕЛЕНИЕ НЕОБХОДИМЫХ ЗАПРОСОВ
Количество и тип запросов определяется количеством необходимых форм и отчетов. Прак- тически все формы и отчеты строятся на запросах. Исключение составляют формы для ввода и редактирования данных, которые строятся непосредственно на таблицах. Анализируя необходи- мые формы для просмотра и отчеты, мы определяем, запросы какого типа нам нужны. Необхо- димо определить источники для каждой формы или отчета и описать запросы в таблице.
СОЗДАНИЕ ЗАПРОСА С ПОМОЩЬЮ МАСТЕРА
Для вызова мастера запросов выполните команду:
Вкладка Создание  Группа Запросы  Команда Мастер запросов
Далее предлагается выбрать мастер запросов:

простой запрос;

перекрестный запрос;

повторяющиеся записи;

записи без подчиненных.
Мы будем использовать только два первых мастера.
Затем по шагам определяют различные параметры создаваемого запроса.
СОЗДАНИЕ ЗАПРОСА С ПОМОЩЬЮ КОНСТРУКТОРА
Конструктор запросов можно вызвать несколькими способами:

для создания нового запроса выполнить команду Вкладка Создание  Команда
Конструктор запросов;

для редактирования существующего запроса щелкните по нему в области нави- гации правой кнопкой мыши и выберите Конструктор.
Бланк запроса
При создании нового запроса в конструкторе сначала появляется окно выбора источников запроса – это могут быть одна или несколько таблиц (запросов).
Затем выводится окно бланка запроса на выборку. В конструкторе Access запросы форми- руются путем заполнения бланка запроса. Эта технология называется «создание запроса по об- разцу» – QBE (Query By Example).

31
В верхней части бланка отображаются источники запроса, в нижней части – структура таб- лицы результатов выполнения запроса. Выбрав источники запроса, формируем таблицу резуль- тата выполнения запроса, добавляя туда в нужном порядке поля источников. По каждому полю можно выполнить сортировку, отбор данных и вывод поля на экран. Набор строк бланка меня- ется в зависимости от типа запроса (Рисунок 12).
Рисунок 12 – Окно конструктора запросов с выбранным источником
Назначение строк бланка запроса
В строке Поле можно выбрать любое поле источника или создать новое поле с именем, отличающимся от имен полей источников.
Строка Имя таблицы заполняется автоматически именем таблицы-источника, из которой взято поле. Если создается новое поле, то имени источника для него не существует и строка Имя
таблицы остается для него пустой.
В строке Сортировка можно из списка выбрать способ сортировки в поле – отсутствует,
по возрастанию, по убыванию. Если сортировка выбрана в нескольких полях, то сначала сор- тируется по значениям в самом левом поле, затем среди одинаковых значений этого поля по значениям в следующем поле сортировки и т.д.
В строке Вывод на экран указывается, показывать или нет это поле в таблице-результате выполнения запроса. Обычно не показывают результаты поля различных промежуточных рас- четов.
В строку Условие отбора помещают критерии отбора записей по этому полю. Для состав- ных критериев заполняют последующие строки ИЛИ.

32
Пример заполнения бланка запроса
В запросе (Рисунок 13) источником является таблица Товары. Из неё в запрос выбраны поля Наименование, Категория, Цена, Количество. По поле Наименование выполнена сорти- ровка по возрастанию (т.е. по алфавиту), в строке Условие отбора введен критерий для отбора товаров категории Продукты.
Рисунок 13 – Пример заполнения бланка запроса
Результат выполнения запроса показан ниже (Таблица 8).
Таблица 8 – Результат выполнения запроса
Наименование
Категория Цена поставки Количество
рис продукты
95,00р.
100 сахар продукты
41,00р.
200 сахар рафинад продукты
60,50р.
500 сахар тростниковый продукты
80,50р.
300
ЗАПРОСЫ НА ВЫБОРКУ
Запрос с отбором записей
Для отбора записей в бланке в строках Условие отбора и ИЛИ вводят критерии отбора.
Они могут состоят из следующих компонентов: констант (числовых, текстовых, дата/временя), знаков операций, скобок, имен полей, символов шаблона (*, ?) и имён функций.

33
Простые условия отбора
Условия отбора по текстовым полям:

по точному текстовому значению
Поле:
Категория
Условие отбора:
“продукты”

по символам с начала строки
Поле:
Наименование
Условие отбора:
“сах*”

по подстроке
Поле:
Наименование
Условие отбора:
“*сах*”
Условия отбора по числовым полям:

отбор по точному значению
Поле:
Цена
Условие отбора:
200

отбор с операциями сравнения
Поле:
Цена
Условие отбора:
<200
Условие отбора по логическим полям:

логическое значение «Да» (физическое лицо)
Поле:
Покупатель
Физическое лицо
Условие отбора:
Да

логическое значение «Нет» (не физическое лицо)
Поле:
Покупатель
Физическое лицо
Условие отбора:
Нет

34
Условие отбора по полям даты/время отличается от чисел только тем, что даты (и время) необходимо заключать в решетки (#):

отбор по точной дате
Поле:
Дата покупки
Условие отбора:
#01.03.2013#

отбор по диапазону дат (февраль 2013)
Поле:
Дата покупки
Условие отбора:
>=#01.02.2013# And <=#28.02.2013#
При построении условий отбора по части даты нужно иметь ввиду, что дату можно рас- сматривать как текст из восьми символов в формате ДД.ММ.ГГ:

выбор дня по дате
Поле:
Дата покупки
Условие отбора:
Like "01*"

выбор месяца по дате
Поле:
Дата покупки
Условие отбора:
Like "??.02*"

выбор года по дате
Поле:
Дата поставки
Условие отбора:
Like "*13"
Условие отбора для пустых и непустых значений.
В Microsoft Access различаются два типа пустых значений: отсутствие значения в поле и пустые строки для текстовых полей (“”). Отсутствие значения означает, что данные в поле не введены независимо от типа поля.
Например, если таблица «Товары» содержит поле «Дата поставки», это поле может остаться не заполненным, когда неизвестна точная дата поставки товара. В этом случае поле имеет значение Null, указывающее, что точное значение неизвестно.
Критерий для пустых и непустых значений не зависит от типа данных в поле:

отсутствует дата поставки – поле Дата поставки не заполнено
Поле:
Дата поставки
Условие отбора:
Is Null