Подробно о том, как связывать таблицы в access

2.4. Microsoft Access 2007

2.4.5. Создание запросов и поиск информации в базе данных

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

В СУБД Access 2007 применяются различные типы запросов: на выборку, на обновление, на добавление, на удаление, перекрестный query, выполнение вычислений, создание таблиц. Наиболее распространенным является query на выборку. Применяются два типа запросов: query по образцу (QBE) и query на основе структурированного языка запросов (SQL).

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

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

На скриншоте (рисунок 1) средства сортировки и фильтрации выделены скругленным прямоугольником красного цвета.

Рис. 1.

Рассмотрим создание запроса на выборку с помощью Конструктора

Для создания нового пустого запроса в режиме конструктора надо щелкнуть на пиктограмме Конструктор запросов (рисунок 2).

Рис. 2.

Откроется активное окно диалога Добавление таблицы (рисунок 3) на фоне неактивного окна «Запрос1». В этом окне можно выбрать таблицы и queries для создания новых запросов.

Рис. 3.

В окне Добавление таблицы следует выбрать несколько таблиц из представленного списка таблиц, на основе которых будет проводиться выбор данных, и щелкнуть на кнопке Добавить. После этого закрыть окно Добавление таблицы, а окно «Запрос1» станет активным (рисунок 4).

Рис. 4.

Окно Конструктора состоит из двух частей – верхней и нижней. В верхней части окна размещается схема данных запроса, которая содержит список связанных таблиц. В нижней части окна находится Бланк построения запроса QBE, в котором каждая строка выполняет определенную функцию.

Переместим имена полей с таблиц-источников в Бланк. Из таблицы Группы студентов переместим поле Название в первое поле Бланка, из таблицы Студенты переместим поле Фамилии во второе поле, а из таблицы Успеваемость переместим поле Оценка в третье поле и из таблицы Дисциплины переместим поле Название в четвертое поле Бланка запросов.

При необходимости можно задать принцип сортировки (по возрастанию или по убыванию) результатов запроса. В строке «Вывод на экран» автоматически устанавливается флажок просмотра информации.

Условия ограниченного поиска или критерий поиска информации вводится в строке «Условия» отбора и строке «Или». Например, введем критерий поиска — «5/A» в строке «Условия» для поля Оценка. В этом случае в результате выполнения запроса на экране будут отображаться все фамилии студентов, которые получили оценку 5/A (рисунок. 5).

Рис. 5.

Далее надо закрыть окно запроса Запрос1, появится окно диалога Сохранить, ответить — Да и ввести имя запроса, например «Успеваемость студентов». Для запуска запроса дважды щелкнем на query «Успеваемость студентов», откроется таблица с результатами выполненного запроса (рис. 6).

Рис. 6.

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

Закрыть окно запроса на выборку. На вопрос о сохранении изменения ответить — Да и ввести имя запроса, например «Параметрический query». Запустим Параметрический query, дважды щелкнув на нем. В открывшемся на экране окне диалога «Введите значение параметра» надо ввести фамилию студента, информацию об успеваемости которого необходимо получить (рис. 8).

Рис. 7.

Затем надо щелкнуть на кнопке ОК, откроется таблица с результатами выполненного запроса (рис. 8).

Рис. 8.

В некоторых случаях для создания запросов можно использовать Мастер запросов. После создания запросов на выборку информации из БД Access 2007 можно приступать к формированию форм.

Далее >>> Раздел: 2.4.6. Создание форм для ввода данных в таблицы базы данных Access 2007

Каскадные обновления и удаления

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

Если выбрать опцию Каскадное обновление связанных полей  при определении отношения, то каждый раз при изменении первичного ключа записи в первичной таблице Microsoft Access автоматически обновляет первичный ключ к новому значению во всех связанных записях. Например, при изменении идентификатора клиента в таблице «Клиенты», поле CustomerID  в таблице «Заказы» автоматически обновляется для каждого из заказов этого клиента, чтобы связи не были нарушены. Access каскадирует обновления без отображения каких-либо сообщений.

Примечание

Если первичным ключом в первичной таблице является поле AutoNumber, выбор Каскадное обновление связанных полей  не имеет никакого эффекта, поскольку невозможно изменить значение в поле AutoNumber.

Если вы выберете Каскадное обновление связанных полей  при определении связей, при удалении записей в первичной таблице Access автоматически удаляет связанные записи в соответствующей таблице. Например, при удалении записи клиента из таблицы «Клиенты», все заказы клиента автоматически удаляются из таблицы «Заказы». (Это включает записи в таблице «Детали заказа», которые связаны с записями «Заказы»). При удалении записей из формы или таблицы после установки флажка рядом с Каскадное удаление связанных записей, Access предупреждает вас, что связанные записи также могут быть удалены. Однако при удалении записей с помощью запроса удаления Access автоматически удаляет записи в соответствующих таблицах, не отображая предупреждение.

Типы соединения

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

Вариант 1 определяет внутреннее соединение. Внутреннее соединение — это соединение, в котором записи из двух таблиц объединяются в результатах запроса только в том случае, если значения в объединенных полях соответствуют определенному состоянию. В запросе соединение по умолчанию — это внутреннее соединение, которое выбирает записи только в том случае, если значения в объединенных полях совпадают.

Вариант 2 определяет левое внешнее соединение. Левое внешнее соединение — это соединение, в котором все записи с левой стороны операции LEFT JOIN в оператора запроса SQL добавляются к результатам запроса, даже если нет соответствующих значений в объединенном поле из таблицы на правой стороне.

Вариант 3 определяет правое внешнее соединение. Правое внешнее соединение — это соединение, в котором все записи с правой стороны операции RIGHT JOIN в операторе запроса SQL добавляются к результатам запроса, даже если нет соответствующих значений в объединенном поле из таблицы на левой стороне.

Создание схемы данных

Для создания схемы данных в Access добавлен инструмент на панели «Работа с базами данных». СУБД автоматически создает схему по тем таблицам и связям, что существуют в базе. Приведенная выше схема создана системой самостоятельно. Пользователь может внести изменения в макет. Некоторые из них не отразятся на структуре БД, только на отображении информации. А некоторые приведут к изменениям в структуре.

В режиме «Конструктора» доступна операция «Очистить макет». При ее выполнении экран схемы данных в Access очищается, а таблицы и отношения скрываются. Это не значит, что они пропадают из базы — просто не отражаются в макете схемы.

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

Изменение отношений

Если отношение не добавлено при добавлении столбца в объект, это делается непосредственно в макете схемы данных в Access. Как создать новую связь, покажем на примере. Нажмите кнопку «Изменить связи». В редактировании отношений для создания новой связи нажмите «Новое». В форме «Создание» выбираем таблицы для связи и поля, которые будут соответствовать друг другу.

Для уже созданной связи есть возможность изменять параметры объединения записей в запросах. Для этого вызываем диалоговое окно схемы данных MS Access «Изменение связей» и нажимаем кнопку «Объединение». В форме редактирования параметров предложены варианты объединений:

• В первом случае в результатах запроса отображаются только те строки, в которых поля таблиц «Отели» и «Регионы» совпадают.
• Во втором случае объединяются все строки «Регионов» и только совпадающие «Отелей».
• В третьем ситуация обратна второму – все строки «Отелей» объединяются с совпадающими «Регионов».

Мы оставляем автоматический выбор системы – первый вариант.

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

Одно из наиболее
мощных средств Access
– возможность устанавливать
связи между таблицами.
Создание связей между таблицами облегчает
выполнение операций над данными,
позволяет контролировать их целостность.
Для реализации этой операции используется
команда Схема
данных в
меню Сервис
или кнопка Схема
данных панели
инструментов
при
работе в диалоговом окне базы данных
(вкладка Таблицы).
В результате выполнения этой операции
открывается диалоговое окно «Схема
данных»
(см. рис.).

Для добавления
таблицы БД в схему используется диалоговое
окно «Добавление
таблицы»,

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

Задание 1.
Выполните команду Схема
данных
и добавьте в схему все таблицы, созданные
в БД (для добавления таблицу или таблицы
следует выделить и щелкнуть по кнопке
Добавить).
После того, как все таблицы будут включены
в схему окно «Добавление
таблицы»
следует закрыть.

Окно схема данных
принимает вид, показанный на рисунке
(таблицы в окне схемы перемещаются с
помощью мыши, можно изменять их размер).
В окне появляются изображения каждой
таблицы БД, добавленной в схему, с
перечнем всех включенных в них полей.
Ключевые поля выделяются жирным шрифтом.
Для удобства ключевые поля лучше ставить
на первое место. Из этого окна можно
вызвать операции над таблицами с помощью
контекстного меню. Для удобства работы
изображения таблиц лучше всего разместить
так:

Для добавления
связи
между таблицами следует воспользоваться
следующим приемом: с помощью мыши
переместить поле (или поля), которое
необходимо связать, из исходной (главной)
таблицы в соответствующее поле (или
поля) второй (подчиненной) таблицы.
Например: установите курсор мыши на
поле «НомерКлиента»
в таблице «Клиенты»,
нажмите левую кнопку мыши и, удерживая
ее нажатой, переместите курсор до его
совмещения со строкой с тем именем
«НомерКлиента»
в таблице «Заказы».
В большинстве случаев ключевое слово
первой таблицы связывается с аналогичным
полем второй.

После
перемещения поля появляется диалоговое
окно «Связи«,
в котором, если это необходимо, можно
изменить имена полей. В этом же окне
(см. рис.) можно установить флажок
«Обеспечение
целостности данных«,
что дает возможность указать вариант
обновления информации в связанных
таблицах при внесении изменений в
исходную таблицу: связанные поля могут
быть каскадно удалены или обновлены,
что сохранит согласованность данных в
различных таблицах БД.

Задание 2.
Создайте связь между таблицей «Клиенты»
(главная таблица) и таблицей «Заказы»
(подчиненная таблица). Для этого выполните
следующие действия:

1.
Установите курсор мыши на поле
«НомерКлиента»
в списке полей таблицы «Клиенты»
в окне «Схема
данных».
Нажмите левую кнопку мыши и, удерживая
кнопку нажатой, переместите курсор в
окне, совместив перемещаемый значок с
названием поля «НомерКлиента»
в таблице «Заказы».
Отпустите кнопку мыши.

2. В открывшемся
диалоговом окне «Связи»
проверьте информацию о создаваемой
связи: таблица «Клиенты»
(поле «НомерКлиента»)
связывается с таблицей «Заказы»
(по полю «НомерКлиента»).
Тип устанавливаемого отношения (связи)
– «один-ко-многим».
Если информация, отображаемая в окне,
не соответствует тому, что показано на
приведенном выше рисунке, следует
исправить ошибку.

3. Включите флажок
«Обеспечение
целостности данных»
и флажки «Каскадное
обновление связанных полей»
и «Каскадное
удаление связанных полей».

4. Щелкните кнопку
Создать,
завершая выполнение операции.

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

Задание 3. Создайте
связи между таблицами «Услуги»
и «Заказы»,
«Исполнители»
и «Заказы»
по полям «КодУслуги»
и «НомерИсполнителя»
соответственно Закройте схему, подтвердив
сохранение изменений.

Вопросы для
самоконтроля:

1. Какие связи могут
   существовать между таблицами в СУБД?

2. Что обеспечивает
   создание связей?

3. Как разместить в
   схему данных все необходимые связываемые
   таблицы?

4. Как установить
   связь между таблицами?

5. Зачем выставляется
   флажок Обеспечение
   целостности данных?

6. Что нужно делать,
   если целостность данных при создании
   связей не обеспечивается?

Связь вида 1:1

Определение 2

Связь 1:1 создается, если все поля связи основной и дополнительной таблиц – ключевые.

Т.к. значения ключевых полей обеих таблиц не могут повторяться, записи из этих таблиц взаимно-однозначно соотносятся.

Пример 1

Пусть существует основная О и дополнительная Д таблицы. Символом «$*$» обозначим ключевые поля, символом «$+$» обозначим поля, которые используются для связи.

В таблицах совпадают значения в полях связи, поэтому будут связаны запись (а, 10) таблицы О и запись (а, стол) таблицы Д, а также запись (в, 3) и (в, книга).

На практике связь 1:1 используется довольно редко, т.к. информацию, которая хранится в двух таблицах, можно объединить в одну таблицу, которая будет занимать намного меньше места в памяти компьютера.

Пример 2

Например, если объединить запись (а, 10) таблицы О и запись (а, стол) таблицы Д, то получим запись (а, 10, стол) новой псевдотаблицы.

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

Что такое связи между таблицами?

Лучшим решением является хранение информации издателя только один раз, в отдельной таблице, которую мы будем называть «Издатели». Затем вы поместите указатель в таблице «Названия», которая ссылается на запись в таблице «Издатели».

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

Логические отношения в базе данных позволяют эффективно запрашивать данные и создавать отчеты.

Связь «Один к одному»

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

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

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

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

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

Для чего все это нужно?

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

Правильно настроив связи, можно быть уверенным, что ничего не потеряется.

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

• < Назад
• Вперёд >

Новые статьи:

• Объединение таблиц – UNION

• Соединение таблиц – операция JOIN и ее виды

• Тест на знание основ SQL

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.

Связи в базах данных

В качестве примера рассмотрим связи в БД туристического агентства. В ее состав входит таблица гостиниц Таиланда – страны, в которую турагентство отправляет гостей. Отели расположены в различных регионах Королевства.

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

По аналогии добавлен список стран для компании, работающей с несколькими государствами. Тогда к перечню регионов добавляется еще одно поле – «Страна», а в базу вносится справочник стран, наименования которых затем выбираются из списка.

Таблицы связаны по типу один-ко-многим. Это означает, что одна запись из таблицы «Регионы» встречается много раз в таблице «Отели». Кроме этого, существуют виды «многие-ко-многим» и «один-к-одному». Но последняя крайне редко применяется на практике. Ниже мы ознакомимся, как эти типы обозначаются на схемах данных в Access.