Создание связей между таблицами
После распределения данных по таблицам необходимо получить возможность объединять их. Например, изображенная ниже форма включает сведения из нескольких таблиц.
Рисунок 1. Пример формы, включающей данные из двух таблиц
Access — это система управления реляционными базами данных. В реляционной базе данных сведения распределяются по отдельным, тематически организованным таблицам. Для объединения данных используются связи между таблицами.
Зачем создавать межтабличные связи?
Можно создать межтабличные связи непосредственно с помощью окна Схема данных или путем перетаскивания поля из области Список полей. Межтабличные связи используются для того, чтобы продемонстрировать, как связать таблицы для использования их в объекте базы данных. Существует несколько причин для создания межтабличных связей перед созданием других объектов базы данных (форм, запросов и отчетов).
Межтабличные связи предоставляют сведения для структуры запросов. Чтобы работать с записями нескольких таблиц, часто нужно создать запрос, объединяющий таблицы. Запрос сопоставляет значения в поле первичного ключа первой таблицы с полем внешнего ключа второй таблицы. Например, чтобы получить строки, в которых перечисляются все книги для каждого читателя, создается запрос, связывающий таблицу Читатели с таблицей Книги и таблицей Заказ книги. В окне Схема данных можно вручную указать поля для связи. Но если связь между таблицами уже существует, в приложении Office Access 2007 будет использована связь по умолчанию на основе существующей связи между таблицами. Кроме того, при использовании одного из мастеров запросов в Access будут использоваться сведения об уже определенных межтабличных связях, чтобы предоставить пользователю выбор и предварительно заполнить параметры свойств соответствующими значениями по умолчанию.
Межтабличные связи предоставляют сведения для структуры форм и отчетов. При создании формы или отчета в приложении Office Access 2007 используются сведения об уже определенных межтабличных связях, чтобы предоставить пользователю выбор и предварительно заполнить параметры свойств соответствующими значениями по умолчанию.
Межтабличные связи — это та основа, с помощью которой можно обеспечить целостность данных, чтобы в базе данных не было записей без подчиненных. Запись без подчиненных — это запись, у которой нет ссылки на другую запись — например, запись о взятой книге, у которой нет ссылки на запись читателя.
При создании базы данных сведения распределяются по таблицам, в каждой из которых есть первичный ключ. После этого к связанным таблицам добавляются внешние ключи, имеющие ссылки на первичные ключи. Эти пары внешнего и первичного ключей формируют основу для межтабличных связей и многотабличных запросов. Поэтому важно, чтобы эти ссылки «внешний ключ — первичный ключ» оставались синхронизованными. Целостность данных помогает убедиться, что ссылки остаются синхронизированными, и определяется межтабличными связями.
Типы межтабличных связей
Связь M:N, «многие-ко-многим». Эта связь означает, что одному объекту первого типа соответствует несколько объектов второго типа и каждому объекту второго типа соответствует несколько объектов первого типа. Для представления отношения «многие-ко-многим», создается третий (связующий) тип, в котором отношение «многие-ко-многим» разбивается на два отношения «один-ко-многим». В результате в третий тип входит несколько свойств от одного и от второго типа. Обратите внимание, что для обнаружения существующего отношения между таблицами важно рассмотреть обе его стороны.
Рассмотрим связь между таблицами Читатели и Книги. Один читатель может заказать несколько книг, с другой стороны, одну книгу могут заказать несколько читателей (при этом учитывается дата заказа и дата возврата книги). Следовательно, для каждой записи таблицы Книги может существовать несколько записей в таблице Читатели и наоборот.
Чтобы реализовать отношение «многие-ко-многим», нужно создать третью (связующую) таблицу, в которой отношение «многие-ко-многим» разбивается на два отношения «один-ко-многим». Первичные ключи двух таблиц вставляются в третью таблицу. В результате в третьей таблице сохраняются все экземпляры отношения.
Рисунок 2. Связь "многие ко многим"
Например, давайте представим, что произойдет, если создать связь между двумя таблицами путем добавления поля с Инв№ (инвентарным номером) книги в таблицу читателей. Чтобы читатель мог заказать несколько книг, каждый читатель в таблице должен включать несколько записей. В этом случае сведения о читателе должны повторяться в каждой строке таблицы Читатели, что может привести к неэффективности структуры таблицы и неточности данных. Такая же трудность возникает при создании поля с номером читательского билета читателя в таблице книги — для каждой книги в таблице существует несколько записей. Как решить эту проблему?Решением является создание третьей, связующей, таблицы, в которой отношение «многие-ко-многим» разбивается на два отношения «один-ко-многим». Первичные ключи двух таблиц вставляются в третью таблицу. В результате в третьей таблице сохраняются все экземпляры отношения.
Каждая запись в таблице Заказ книги представляет собой отдельный элемент строки книги. Первичный ключ таблицы состоит из двух полей — внешних ключей таблиц Читатели и Книги. Невозможно использовать для первичного ключа этой таблицы только поле с Инв№ книги, т. к. одна книга может содержать много элементов строк. Инв№ книги повторяется для каждого элемента строки, поэтому это поле не содержит уникальных значений. Невозможно также использовать только поле с номером читательского билета читателя, т. к. один читатель может заказать множество книг. Однако использование двух полей позволяет получить уникальное значение для каждой записи.
В данном случае таблицы Книги и Читатели не имеют непосредственной связи. Они связаны опосредованно через таблицу Заказ книги. Отношение «многие-ко-многим» между таблицами Книги и Читатели представлено в базе данных двумя отношениями «один-ко-многим»:
Таблицы Книги и Заказ книги связаны отношением «один-ко-многим». Каждая книга содержит несколько элементов строк, но каждый элемент связан только с одной книгой. О связи «один-ко-многим» читайте дальше.
Таблицы Читатели и Заказ книги связаны отношением «один-ко-многим». Каждый читатель может быть связан с несколькими элементами строк, но каждый элемент связан только с одним читателем.
В таблице Заказ книг можно определить книги для конкретного читателя. Можно также определить читателей для конкретной книги.
После создания таблицы Заказ книги список таблиц и полей может выглядеть следующим образом:
Рисунок
3.
Таблицы с полями базы данных «Библиотека»
Отсюда видно, что таблица Заказ книги содержит внешний ключ Номер на первичный ключ таблицы Читатели и внешний ключ Инв№ на первичный ключ таблицы Книги.
Связь 1:N, «один-ко-многим» (или N:1, «многие-к-одному»). Эта связь означает, что одному объекту первого типа соответствует несколько объектов второго типа, но каждому объекту второго типа соответствует только один объект первого типа. В такое отношение вступают несколько объектов – один объект первого типа, остальные второго, и если какой-то объект второго типа уже входит в отношение с каким-то объектом первого типа, то он уже не может входить в такое же отношение с другим объектом первого типа.
Как уже описывалось выше, что связь «многие-ко-многим» разбивается на два отношения «один-ко-многим», в нашем случае получаем, что таблицы Читатели и Книги связаны третьей таблицей Заказ книги. Тип связей будет «один-ко-многим». Рассмотрим связь между таблицами Читатели и Заказ книги. Один читатель может делать несколько заказов на книги, но один и тот же заказ не может быть у нескольких читателей. Аналогично, связь между сущностями Книги и Заказ книги.
Рисунок 4. Связь один ко многим
Чтобы создать отношение «один-ко-многим» в структуре базы данных, добавьте первичный ключ на стороне «один», в таблицу на стороне «многие» – в виде дополнительного поля (внешний ключ). В данном примере в таблице Читатели первичным ключом определим поле Номер. Далее необходимо добавить данное поле в таблицу Заказ книги и назвать его, например, Номер. После этого Access сможет использовать Номер из таблицы Заказ книги для поиска читателя каждого заказа книги. Столбец Номер в таблице Заказ книги называется внешним ключом.
Внешний ключ — это первичный ключ другой таблицы. Столбец Номер в таблице Заказ книги является внешним ключом, т. к. он является также первичным ключом таблицы Читатели.
В данном случае список таблиц и полей может выглядеть следующим образом:
Рисунок 5. Связь между таблицами Читатели и Заказ книги
Основой для связывания таблиц является объединение первичных и внешних ключей в пары. Если не удается определить таблицы с общим столбцом, создание отношения «один-ко-многим» обеспечивает необходимость общего столбца для двух таблиц.
Связь 1:1, «один-к-одному». Эта связь означает, что каждому объекту первого типа соответствует ровно один объект второго типа и, наоборот, каждому объекту второго типа соответствует ровно один объект первого типа. В отношение вступают два объекта разных типов.
Предположим, к книге прилагается какой-либо носитель (диск, дискета и т.п.) с дополнительной информацией. Тогда появится еще одна таблица Приложение, которая как минимум будет содержать поле тип приложения и описание. В результате получим связь «один-к-одному» между таблицами Книги и Приложение. Книга может иметь только одно Приложение, а Приложение может прилагаться только к одной Книге.
Рисунок
6.
Связь "один-к-одному"
При отношении «один-к-одному» каждая запись в первой таблице может иметь не более одной связанной записи во второй таблице и наоборот. Отношения этого типа используются нечасто, поскольку обычно сведения, связанные таким образом, хранятся в одной таблице. Отношение «один-к-одному» используется для разделения таблицы, содержащей много полей, с целью отделения части таблицы по соображениям безопасности, а также с целью сохранения сведений, относящихся к подмножеству записей в главной таблице. Также в случае, если какая-то информация присутствует не всегда, например Приложение может быть не у всех Книг. После определения такого отношения у обеих таблиц должно быть общее поле.
Если возникает необходимость в создании в базе данных отношения «один-к-одному», рассмотрите возможность объединения данных в одну таблицу. Если этот вариант неприемлем, например, по причине возникновения пустых полей, используйте приведенный, ниже список для определения отношения в структуре базы данных:
если таблицы объединены одной темой, отношение можно создать посредством использования общего первичного ключа;
если тематика и первичные ключи таблиц различаются, следует выбрать любую из таблиц и вставить ее первичный ключ в другую таблицу в качестве внешнего ключа.
Определение связей между таблицами позволяет обеспечить правильность таблиц и столбцов. При наличии отношения «один-к-одному» или «один-ко-многим» таблицы должны содержать общие столбцы. При наличии отношения «многие-ко-многим» требуется третья таблица.