Связывание таблиц и удаление связей в Microsoft Access

Связывание таблиц. целостность данных

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

Механизм описания логических связей между таблицами в Access реализован в виде объекта, называемого Схемой данных. Перейти к созданию связей можно командой Сервис/ Схема данных.

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

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

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

Рис.. Объявление связи между таблицами.

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

Данное отношение является наиболее распространенным в реляционных базах данных.

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

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

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

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

• Добавления в связанную таблицу записей, для которых отсутствует соответствующая запись в первичной таблице;
• Осуществления изменений в главной таблице, которые приведут к появлению «осиротевших» записей в связанной таблице;

• Удаления записей в главной таблице, на которые ссылаются записи из связанной таблице.

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

Что означает целостность данных?

Под целостностью данных понимается выполнение логических ограничений на данные. Эти ограничения вытекают из свойств предметной области и свойств модели данных. Одни ограничения определяются неявно и зависят от выбранной модели данных.

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

Второй тип ограничений – это ограничения на связи между таблицами. Рассмотрим особенности задания таких ограничений.

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

• Связанное поле главной таблицы является ключевым полем или имеет уникальный индекс.
• Связанные поля имеют один тип данных. Здесь существует два исключения. Поле счетчика может быть связанно с числовым полем формата Длинное целое. А также поле счетчика можно связать с числовым полем, если в обоих полях для свойства Размер поля задано значение Код репликации.
• Обе таблицы принадлежат одной базе данных Access. Если таблицы являются связанными, то они должны быть таблицами Access. Для установки целостности данных база данных, в которой находится таблица, должна быть открыта. Для связанных таблиц из баз данных других форматов установить целостность данных невозможно.

Установив целостность данных, необходимо следовать следующим правилам.

• Невозможно ввести в поле внешнего ключа связанной таблицы значение, не содержащееся в ключевом поле главной таблицы. Однако в поле внешнего ключа возможен ввод пустых значений, показывающих, что записи не являются связанными. Например, нельзя сохранить запись, регистрирующую заказ на несуществующую модель машины TОYОТА, но можно создать запись для заказа, в котором клиент пока не определился, какую машину он предпочитает, если ввести пустое значение в поле Код модели.
• Не допускается удаление записи из главной таблицы, если существуют связанные с ней записи в подчиненной таблице. Например, невозможно удалить запись из таблицы Модели, если в таблицы Клиенты имеются заказы, относящиеся к данной модели.
• Невозможно удалить значение ключевого поля в главной таблице, ели существуют записи, связанные с данным значением. Например, невозможно изменить код модели в таблице Модели, если в таблице Клиенты имеются заказы, относящиеся к этой модели.

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

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

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

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

:

8.3. Связывание таблиц в Microsoft Access

В базах данных для уменьшенияизбыточности данные различных областейприменения хранятся в разных таблицах. Для получения сводной информации из разных таблиц их необходимо связать. Различают связи

1 : 1, 1 : М. При второмтипе связи каждой записи главной таблицымогут быть поставлены в соответствиеодна или несколько записей подчиненнойтаблицы. Но при этом должны быть определены первичные ключи.

Пусть имеютсятри таблицы:

93

Поставщики (КП,Имя, Статус, Город), где КП — код поставщика

Товары (КТ,Наименование., Вес, Цвет, Цена, Город), где КТ — код товара

Поставки (КП, КТ,Количество)

Длясвязывания таблиц проделайте следующиедействия:

1.ЗапуститеMicrosoft Access.

2.Создайте базу данных БД_ПОСТАВКИ вдиректории c:work.Создайте таблицы и введите данные. Значения таблиц представлены в приложении 8.1.

3. Просмотритетаблицы Поставщики, Товары, Поставки.

4.Установите связь между таблицами. Дляэтого в основном меню щелкните напиктограмме“Схемаданных”. Добавьте все три таблицы вокно “Схемы данных”. Далее перенесите поле КП из таблицы Поставщики наодноименное поле таблицы Поставки.Затем поле КТ из таблицы Товары наодноименное поле таблицы Поставки.Сохраните созданную схему.

8.4. Запросы к связанным таблицам

Для организации запросов к связаннымтаблицам, необходимо:

1. Открыть окно конструктора запроса идобавить таблицы, в которых содер — жится необходимая информация: Поставщики, Товары и Поставки.

2. Произвести выборку из одной таблицы:

Всех поставщиковв алфавитном порядке, поставщиков, находящихся в городе Самара, поставщиков,чей статус выше 5000р.

3. Произвести выборку из двух таблиц:Поставщики и Поставки. Выдать сведенияо поставщиках, которые поставляют товарс кодом — 2. Выдать сведения о поставщиках,которые поставляют товары в количестве>20.

4. Произвести выборку из всех трех таблиц: Поставщики, Товары и Поставки:

Сведенияо поставщиках, которые поставляют товарШатун, Поставщи­ков, поставляющихтовары, в наименованиях которых естьбуква «а» и Поставщиков, поставляющихтовары, ценой менее (или более) 100 рублей.

8.5. Отчеты

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

1. В окне базы данных выберите команду “Oтчеты” и щелкните на кнопку “Создать”. В окне “Новый отчет” выберите таблицу Поставщики и нажмите на кнопку “Мастер”. Выберите нужные поля и вид отчета, например,

94

Табличный.Мастер создаст отчет, и Accessоткроет его в режиме предварительногопросмотра.

2.Если отчет должен содержать фильтр илисортировку, откройте таблицу в режиме“Tаблица”и убедитесь в установке фильтра илисортировки. Если это так, они будутавтоматически включены в отчет.

3. Если длясоздания отчета желательно использоватьсуществующий запрос, щелкните на вкладке“Запросы” и выберите нужный запрос.

Отчетс группировкой данных.Группировка позволяет сгруппироватьотчет по дополнительным уровням.

Например,для определения наличия товаров погородам, выберите таблицуТовары. Изнее выберите поля Город, Наименование.,Цена. В окне “Создание отчета” укажитев качестве уровня группировки Город.Для получения итоговых данных по полюЦена щелкните на кнопке“Итоги”.Выберите подходящий стиль отчета,заголовок. Сохраните и просмотритеотчет.

Конструкторотчетов.Пользователь может улучшить созданныйранее отчет или создать новый в режиме“Kонструктораотчетов”.

По умолчанию Accessразличает в отчете пять областей:“Заголовок”, “Верхний колонтитул”,“Область данных”, “Нижний колонтитул”,“Область примечаний”. Заголовок ипримечание отчета печатаются один разв начале и в конце отчета.

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

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

После сохранения запросаможно приступить к созданию отчетапрямо из окна запроса. Для этого необходимовыбрать пиктограмму“Новыйотчет”.

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

Схема данных в Access — Базы данных Access

Реляционная база данных, созданная в соответствии с проектом канонической модели данных предметной области, состоит из нормализованных таблиц, связанных одно-многозначными отношениями.

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

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

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

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

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

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

Создание схемы данных начинается с выполнения команды Схема данных (Relationships) в группе Отношения (Relationships) на вкладке ленты Работа с базами данных (Database Tools).

В результате выполнения этой команды открывается окно схемы данных и диалоговое окно Добавление таблицы (Show Table), в котором осуществляется выбор таблиц, включаемых в схему (см. рис. 3.48).

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

Если окно не открылось, на ленте Работа со связями | Конструктор (Relationship Tools | Design) в группе Связи (Relationships) нажмите кнопку Отобразить таблицу (Show Table).

Включение таблиц в схему данных

В окне Добавление таблицы (Show Table) (рис. 3.48) отображены все таблицы и запросы, содержащиеся в базе данных.

Выберем вкладку Таблицы (Tables) и с помощью кнопки Добавить (Add) разместим в окне Схема данных (Relationships) все ранее созданные таблицы базы данных Поставка товаров, отображенные в окне Добавление таблицы (Show Table).

Затем нажмем кнопку Закрыть (Close). В результате в окне Схема данных (Relationships) таблицы базы будут представлены окнами со списками своих полей и выделенными жирным шрифтом ключами (см. рис. 3.52).

Создание связей между таблицами схемы данных

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

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

Создание связей по простому ключу

Установим связь между таблицами ПОКУПАТЕЛЬ и ДОГОВОР, которые находятся в отношении «один-ко-многим». Устанавливая связи между парой таблиц, находящихся в отношении типа 1 : M, выделим в главной таблице ПОКУПАТЕЛЬ ключевое поле КОД_ПОК, по которому устанавливается связь. Далее при нажатой кнопке мыши перетащим его в соответствующее поле подчиненной таблицы ДОГОВОР.

Поскольку поле связи является уникальным ключом в главной таблице связи, а в подчиненной таблице связи не является ключевым, схема данных в Access выявляет отношение «один-ко-многим» между записями этих таблиц. Значение «один-ко-многим» (One-To-Many) отобразится в окне Изменение связей (Edit Relationships) в строке Тип отношения (Relationship Type) (рис. 3.49).

ЗАМЕЧАНИЕ
Если поле связи является уникальным ключом в обеих связываемых таблицах, схема данных в Access выявляет отношение «один-к-одному«. Если для связи таблиц вместо ключевого поля главной таблицы используется некоторый уникальный индекс, система также констатирует отношение таблиц как 1 : М или 1 : 1.

Определим связи между таблицами НАКЛАДНАЯ ОТГРУЗКА, которые связаны по составному ключу НОМ_НАКЛ + КОД_СК. Для этого в главной таблице НАКЛАДНАЯ выделим оба этих поля, нажав клавишу , и перетащим их в подчиненную таблицу ОТГРУЗКА.

В окне Изменение связей (Edit Relationships) (рис. 3.50) для каждого поля составного ключа главной таблицы НАКЛАДНАЯ, названной Таблица/запрос (Table/Query), выберем соответствующее поле подчиненной таблицы ОТГРУЗКА, названной Связанная таблица/запрос (Related Table/Query).

Каскадное обновление и удаление связанных записей

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

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

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

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

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

Установить в окне Изменение связей (Edit Relationships) (см. рис. 3.49) флажки каскадное обновление связанных полей (Cascade Update Related Fields) и каскадное удаление связанных записей (Cascade Delete Related Records) можно только после задания параметра обеспечения целостности данных.

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

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

Вот основное, что мы хотели рассказать на тему «Схема данных в Access».

Дальше будем изучать запросы в Access.