Содержание
Одной из целей создания хорошей структуры базы данных является устранение избыточности (повторения) данных. Для этого нужно распределить данные по нескольким отдельным тематически организованным таблицам, чтобы каждый факт был представлен один раз. В приложении Access будет предоставлен способ сбора разбросанных данных — это делается путем помещения общих полей в связанные таблицы. Чтобы корректно выполнить это действие, нужно понять взаимосвязи между таблицами и описать эти взаимосвязи в базе данных.
В этой статье
Введение
После создания таблицы для каждой темы в базе данных нужно предоставить приложению Accessсредства, с помощью которых можно будет при необходимости объединять сведения. Это делается путем создания общих полей в связанных таблицах и настройки связей между таблицами. После этого можно создавать запросы, формы и отчеты, одновременно отображающие сведения из нескольких таблиц. Например, приведенная ниже форма содержит сведения из нескольких таблиц:
1. Эта форма содержит данные из таблицы клиентов,
4. и сведений о заказах.
Имя клиента в поле Плательщик получено из таблицы "Клиенты", значения кода заказа и даты заказа — из таблицы "Заказы", наименование товара — из таблицы "Товары", а цена и количество — из таблицы "Заказано". Чтобы можно было передать данные в форму, эти таблицы связаны друг с другом несколькими способами.
В приведенном примере поля в таблицах должны быть согласованы таким образом, чтобы отображать сведения об одном и том же заказе. Это согласование осуществляется путем установления связей между таблицами. Связь между таблицами устанавливает отношения между значениями в ключевых полях — часто между полями, имеющими одинаковые имена в обеих таблицах. В большинстве случаев с первичным ключом одной таблицы, являющимся уникальным идентификатором каждой записи, связывается внешний ключ другой таблицы. Например, для связывания сотрудников с заказами, за которые они отвечают, можно создать связь между полями "Код сотрудника" в таблицах "Сотрудники" и "Заказы".
1. Поле "Код сотрудника" отображается в двух таблицах: как первичный ключ.
2. и как внешний ключ.
Типы связей между таблицами
В Access есть три типа связей между таблицами.
Рассмотрим базу данных, в которой учитываются заказы, включающую таблицы "Клиенты" и "Заказы" в качестве примера. Клиент может разместить любое количество заказов. Следовательно, у любого клиента, представленного в таблице "Клиенты", может быть много заказов, представленных в таблице "Заказы". Поэтому связь между таблицами "Клиенты" и "Заказы" — это отношение "один-ко-многим".
Чтобы создать отношение "один-ко-многим" в структуре базы данных, добавьте первичный ключ на стороне "один" в таблицу на стороне "многие" в виде дополнительного поля или полей. В данном примере необходимо добавить новое поле — поле "Код" из таблицы "Клиенты" — в таблицу "Заказы" и назвать его "Код клиента". После этого Access сможет использовать номер "Код клиента" из таблицы "Заказы" для поиска клиента каждого заказа.
Рассмотрим связь между таблицами "Товары" и "Заказы". Отдельный заказ может включать несколько товаров. С другой стороны, один товар может входить в несколько заказов. Таким образом, для каждой записи в таблице "Заказы" может существовать несколько записей в таблицы "Товары". Таким образом, для каждой записи в таблице "Заказы" может существовать несколько записей в таблице "Заказы". Эта связь называется отношением "многие-ко-многим". Обратите внимание, что для определения существующей схемы отношений "многие ко многим" между вашими таблицами, очень важно рассматривать обе стороны отношений.
Чтобы представить связь "многие-ко-многим", нужно создать третью (связующую) таблицу, в которой она разбивается на две связи "один-ко-многим". Первичные ключи двух таблиц вставляются в третью таблицу. В результате в третьей таблице сохраняются все экземпляры связи. Например, таблицы "Заказы" и "Продукты" имеют связь "многие-ко-многим", определяемую путем создания двух связей "один-ко-многим" в таблице "Заказано". В одном заказе может быть много продуктов, и каждый продукт может быть указан во многих заказах.
При отношении "один-к-одному" каждая запись в первой таблице может иметь не более одной связанной записи во второй таблице, и наоборот. Отношения этого типа используются нечасто, поскольку обычно сведения, связанные таким образом, хранятся в одной таблице. Отношение "один-к-одному" используется для разделения таблицы, содержащей много полей, с целью отделения части таблицы по соображениям безопасности, а также с целью сохранения сведений, относящихся к подмножеству записей в главной таблице. После определения такого отношения у обеих таблиц должно быть общее поле.
Зачем создавать связи между таблицами?
Связи между таблицами можно создать непосредственно с помощью окна "Схема данных" или путем перетаскивания поля из области Список полей. Access использует связи между таблицами для того, чтобы решить, как связать таблицы для использования их в объекте базы данных. Существует несколько причин для создания связей между таблицами перед созданием других объектов базы данных (форм, запросов, отчетов).
Связи между таблицами предоставляют сведения для структурирования запросов
Для работы с записями из нескольких таблиц часто приходится создавать запросы, соединяющие таблицы. Запрос сопоставляет значения в поле первичного ключа первой таблицы с полем внешнего ключа второй таблицы. Например, чтобы получить строки, в которых перечисляются все заказы для каждого из клиентов, можно создать запрос, соединяющий таблицу "Клиенты" с таблицей "Заказы" на основе поля "Код клиента". В окне "Схема данных" можно вручную указать поля для соединения. Но если связь между таблицами уже существует, Access использует соединение по умолчанию на основе существующей связи между таблицами. Кроме того, при использовании одного из мастеров запросов Access использует сведения об уже определенных связях между таблицами, чтобы предоставить пользователю выбор и подставить в параметры свойств соответствующие значения по умолчанию.
Связи между таблицами предоставляют сведения для структурирования форм и отчетов
При создании формы или отчета в Access используются сведения об уже определенных межтабличных связях, чтобы предоставить пользователю выбор и предварительно заполнить параметры свойств соответствующими значениями по умолчанию.
Связи между таблицами — это та основа, с помощью которой можно обеспечить целостность данных, чтобы в базе данных не было потерянных записей. Потерянная запись — это запись со ссылкой на несуществующую запись (например, запись заказа со ссылкой на отсутствующую запись клиента).
При создании базы данных сведения распределяются по таблицам, в каждой из которых есть первичный ключ. После этого к связанным таблицам добавляются внешние ключи, имеющие ссылки на первичные ключи. Эти пары из внешнего и первичного ключей формируют основу для связей между таблицами и многотабличных запросов. Поэтому важно, чтобы ссылки "внешний ключ — первичный ключ" оставались синхронизированными. Целостность данных, которая зависит от связей в таблице, гарантирует, что ссылки остаются синхронизированными.
Понятие о целостности данных
При создании базы данных сведения распределяются по множеству тематически организованных таблиц, чтобы свести к минимуму избыточность данных. После этого в Access предоставляются средства сбора разбросанных данных путем создания в связанных таблицах общих полей. Например, чтобы создать связь "один-ко-многим", добавьте первичный ключ из таблицы на стороне "один" как дополнительное поле в таблицу на стороне "многие". Чтобы соединить данные, Access подставляет значение из таблицы на стороне "многие" в соответствующее поле таблицы на стороне "один". Таким образом, значения таблицы на стороне "многие" связаны с соответствующими значениями на стороне "один".
Предположим, между таблицами "Грузоотправители" и "Заказы" существует связь "один-ко-многим", и нужно удалить грузоотправителя. Если у грузоотправителя, которого нужно удалить, есть заказы в таблице "Заказы, они станут потерянными записями после удаления записи грузоотправителя. В таблице "Заказы" останется код грузоотправителя, но он будет недействителен, поскольку запись, на которую он ссылается, уже не существует.
Задача сохранения целостности данных состоит в предотвращении появления потерянных записей и поддержании ссылок в синхронизированном состоянии, чтобы описанная выше гипотетическая ситуация никогда не возникла.
Вы можете обеспечить целостность данных, активировав эту функцию для связь между таблицами (см. статью Обеспечение целостности данных для пошаговых инструкций). После активации, Access будет отклонять любые операции, нарушающие целостность данных для этой межтабличной связи. Это означает, что Access будет отклонять как любые обновления, изменяющие целевой объект ссылки, так и удаление такого целевого объекта. Возможно, у вас может быть полностью допустимая потребность в изменении первичного ключа для поставщика, у которого есть заказы в таблице «Заказы». В этом случае необходимо, чтобы Access выполнил автоматическое обновление всех задействованных строк в рамках одной операции. Таким образом, Access гарантирует, что обновление будет полностью завершено, а база данных не будет находиться в несогласованном состоянии, когда некоторые строки обновлены, а другие — нет. Для этого в Access имеется параметр Каскадное удаление связанных записей. Если при включении обеспечения целостности данных был включен параметр Каскадное удаление связанных полей, то при последующем обновлении первичного ключа Access автоматически обновляет все связанные с ним поля.
Может понадобиться удалить строку и все связанные записи — например, запись грузоотправителя и все связанные с ним заказы. Для этого в Access имеется параметр Каскадное удаление связанных записей. Если при обеспечении целостности данных выбрать параметр Каскадное удаление связанных записей, а затем удалить запись на стороне первичного ключа в отношении, Access автоматически удалит все записи со ссылкой на первичный ключ.
Просмотр связей между таблицами
Чтобы просмотреть межтабличные связи, щелкните Схема данных на вкладке Работа с базами данных. Откроется окно "Схема данных", в котором будут отображены все существующие связи. Если связи еще не были определены или это окно открывается впервые, приложение Access предложит добавить в окно таблицу или запрос.
Вызов окна "Схема данных"
На вкладке Файл выберите команду Открыть.
Выбор и открытые базы данных.
На вкладке Работа с базами данных в группе Отношения нажмите кнопку Схема данных.
Если в базе данных есть связи, будет отображено окно "Схема данных". Если в базе данных нет связей или это окно открывается впервые, будет отображено диалоговое окно Добавление таблицы. Нажмите кнопку Закрыть, чтобы закрыть это диалоговое окно.
На вкладке Конструктор в группе Связи щелкните элемент Все связи.
Будут отображены все связи, определенные в базе данных. Обратите внимание на то, что скрытые таблицы (таблицы, для которых установлен флажок скрытый в диалоговом окне Свойства) и их отношения не отображаются, если в диалоговом окне Параметры переходов не выбран параметр Показывать скрытые объекты.
Связь между таблицами представляется как линия между таблицами в окне "Схема данных". Связь, не обеспечивающая целостность данных, отображается как тонкая линия между общими полями, поддерживающими связь. Если выбрать связь, щелкнув линию, то линия станет жирной. Если обеспечить целостность данных для этой связи, линия станет толще на концах. Кроме того, над жирной частью линии с одной стороны связи будет отображаться цифра 1, а с другой стороны — символ бесконечности ( ∞).
Когда открыто окно "Схема данных", на ленте доступны указанные ниже команды.
На вкладке Конструктор в группе Сервис
Изменить связи . Открывает диалоговое окно Изменение связей . При выборе линии связи можно щелкнуть элемент Изменить связи, чтобы изменить связь между таблицами. Можно также дважды щелкнуть линию связи.
Очистить макет . Запрещает отображение всех таблиц и связей в окне "Схема данных". Имейте в виду, что эта команда только скрывает таблицы и связи, но не удаляет их.
Отчет о связях . Создает отчет, отображающий таблицы и связи базы данных. В отчете отображаются только таблицы и связи, не скрытые в окне "Схема данных".
На вкладке Конструктор в группе Отношения
Добавить таблицу . Открывает диалоговое окно Добавление таблицы, чтобы можно было выбрать таблицы и запросы для просмотра в окне "Схема данных".
Скрыть таблицу . Скрывает выбранную таблицу в окне "Схема данных".
Прямые связи . Отображает все связи и связанные таблицы для выбранной таблицы в окне "Схема данных", если они еще не отображены.
Все связи . Отображает все связи и связанные таблицы базы данных в окне "Схема данных". Имейте в виду, что скрытые таблицы (таблицы, для которых установлен флажок Скрытый в диалоговом окне Свойства) и их связи не будут отображены, если не установлен флажок "Показывать скрытые объекты" в диалоговом окне "Параметры переходов".
Закрыть . Закрывает окно "Схема данных". Если в макет окна "Схема данных" были внесены какие-либо изменения, будет предложено сохранить их.
До сих пор рассматриваются операции с объектами на примере базы данных, состоящей из одной таблицы, но на практике, как правило, в составе базы данных имеется несколько таблиц. Для координации таблиц между ними устанавливается связь. Связь между таблицами устанавливает отношения между совпадающими значениями в ключевых полях, обычно между полями, имеющими одинаковые имена в обеих таблицах. В большинстве случаев с ключевым полем одной таблицы, являющимся уникальным идентификатором каждой записи, связывается внешний ключ другой таблицы.
Типы связей между таблицами. При установлении связи между таблицами возможны три типа связей.
- 1. Отношение «один-ко-многим». Это наиболее часто используемый тип связи между таблицами. В отношении «один-ко-многим»каждой записи в таблице А могут соответствовать несколько записей в таблице В, но запись в таблице В не может иметь более одной соответствующей ей записи в таблице А.
- 2. Отношение «многие-ко-многим». При этом типе связи одной записи в таблице А могут соответствовать несколько записей в таблице В, а одной записи в таблице В — несколько записей в таблице А.Этот тип связи возможен только с помощью третьей (связующей) таблицы, первичный ключ которой состоит из двух полей, которые являются внешними ключами таблиц А и В. Отношение «многие-ко-многим» по сути представляет собой два отношения «один-ко-многим» с третьей таблицей. Например, отношение «многие-ко-многим» между таблицами «Заказы» и «Товары» определяется путем создания двух отношений «один-ко-многим» с таблицей «Заказано».
- 3. Отношение «один-к-одному». При отношении «один-к-одному» запись в таблице А может иметь не более одной связанной записи в таблице В и наоборот. Отношения этого типа используются не очень часто, поскольку большая часть сведений, связанных таким образом, может быть помещена в одну таблицу. Отношение «один-к-одному» может использоваться для разделения очень широких таблиц, для отделения части таблицы по соображениям защиты, а также для сохранения сведений, относящихся к подмножеству записей в главной таблице.
Например, можно создать таблицу для отслеживания участия сотрудников в благотворительных спортивных мероприятиях.
Тип отношения в создаваемой Microsoft Access связи между таблицами зависит от способа определения связываемых полей. Отношение «один-ко-многим» создается в том случае, когда только одно из полей является ключевым или имеет уникальный индекс. Отношение «один-к-одному» создается в том случае, когда оба связываемых поля являются ключевыми или имеют уникальные индексы. Отношение «многие-ко-многим» фактически является двумя отношениями «один-ко-многим» с третьей таблицей, первичный ключ которой состоит из полей — внешних ключей двух других таблиц.
Целостность данных. Для поддержания связей между записями в связанных таблицах, а также обеспечения защиты от случайного удаления или изменения связанных данных в Microsoft Access используется механизм поддержки целостности данных.
Целостность данных означает:
в связанное поле подчиненной таблицы можно вводить только те значения, которые имеются в связанном поле главной таблицы;
из главной таблицы нельзя удалить запись, у которой значение связанного поля совпадает хотя бы с одним значением того же поля в подчиненной таблице.
Установить целостность данных можно, если выполнены следующие условия.
- 1. Связанное поле главной таблицы является ключевым полем или имеет уникальный индекс. В большинстве случаев связывают первичный ключ (представленный в списке полей полужирным шрифтом) одной таблицы с соответствующим ему полем (часто имеющим то же имя) второй таблицы, которое называют полем внешнего ключа.
- 2. Связанные поля не обязательно должны иметь одинаковые имена, но они должны иметь одинаковые типы данных (из этого правила существуют два исключения) и иметь содержимое одного типа. Кроме того, связываемые поля числового типа должны иметь одинаковые значения свойства Размер поля (FieldSize).
- 3. Обе таблицы принадлежат одной базе данных Microsoft Access. Если таблицы являются связанными, то они должны быть таблицами Microsoft Access. Для установки целостности данных база данных, в которой находятся таблицы, должна быть открыта. Для связанных таблиц из баз данных других форматов установить целостность данных невозможно.
Примечание. Допускается определение связей не только для таблиц, но и для запросов. Причем для запросов соблюдение целостности данных не обязательно.
Установив целостность данных, необходимо следовать следующим правилам:
- 1. Невозможно ввести в поле внешнего ключа связанной таблицы значение, не содержащееся в ключевом поле главной таблицы. Однако в поле внешнего ключа возможен ввод значений Null, показывающих, что записи не являются связанными. Например, нельзя сохранить запись, регистрирующую заказ, сделанный несуществующим клиентом, но можно создать запись для заказа, который пока не отнесен ни к одному из клиентов, если ввести значение Null в поле Код Клиента.
- 2. Не допускается удаление записи из главной таблицы, если существуют связанные с ней записи в подчиненной таблице. Например, невозможно удалить запись из таблицы «Сотрудники», если в таблице «Заказы» имеются заказы, относящиеся к данному сотруднику.
- 3. Невозможно изменить значение первичного ключа в главной таблице, если существуют записи, связанные с данной записью. Например, невозможно изменить код сотрудника в таблице «Сотрудники», если в таблице «Заказы» имеются заказы, относящиеся к этому сотруднику.
Чтобы наложить эти правила на конкретную связь, при ее создании следует установить флажок Обеспечение целостности данных. Если данный флажок установлен, то любая попытка выполнить действие, нарушающее одно из перечисленных выше правил, приведет к выводу на экран предупреждения, а само действие выполнено не будет.
Чтобы преодолеть ограничения на удаление или изменение связанных записей, сохраняя при этом целостность данных, следует установить флажки Каскадное обновление связанных полей и Каскадное удаление связанных полей.
Часть 3.2: Виды связей между таблицами в базе данных. Связи в реляционных базах данных. Отношения, кортежи, атрибуты
- 26.05.2016
- SQLite библиотека, Базы данных
- Комментариев нет
Здравствуйте, уважаемые посетители сайта ZametkiNaPolyah.ru. Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. Продолжаем изучать теорию реляционных баз данных и в этой части мы познакомимся с видами и типами связей между таблицами в реляционных базах данных. Так же мы познакомимся с такими термина, как: кортеж, атрибут и отношения. Данная тема является базовой и ее понимание необходимо для работы с базами данных и для их проектирования.
Виды связей между таблицами в базе данных. Связи в реляционных базах данных. Отношения, кортежи, атрибуты.
Сразу скажу, что связей между таблицами в реляционной базе данных всего три. Поэтому их изучение, понимание и восприятие пройдет быстро, легко и безболезненно. Приступим к изучению.
Термины кортеж, атрибут и отношение в реляционных базах данных
В своей публикации я буду стараться объяснять теорию баз данных не с математической точки зрения, а на примерах. Грубо говоря, на пальцах. Во-первых, практические примеры позволяют легче усваивать материал. Во-вторых, с математической теорией проще разобраться, когда понимаешь суть происходящего.
Давайте разбираться с тем, что такое: отношение, кортеж, атрибут в реляционной базе данных.
Таблица с данными из базы данных World
У нас есть простая таблица City из базы данных World, в которой есть строки и столбцы. Но термины: таблица, строка, столбец – это термины стандарта SQL.
Кстати: ни одна из существующих в мире СУБД не имеет полной поддержки того или иного стандарта SQL, но и ни один стандарт SQL полностью не реализует математику реляционных баз данных.
В терминологии реляционных баз данных: таблица – это отношение (принимается такое допущение), строка – это кортеж, а столбец – атрибут. Иногда вы можете услышать, как некоторые разработчики называют строки записями. Чтобы не было путаницы в дальнейшем предлагаю использовать термины SQL.
Если рассматривать таблицу, как объект (например книга), то столбец – это характеристики объекта, а строки содержат информацию об объекте.
Виды и типы связей между таблицами в реляционных базах данных
Давайте теперь рассмотрим то, как могут быть связаны таблицы в реляционных базах данных. Сразу скажу, что всего существует три вида связей между таблицами баз данных:
• связь один к одному;
• связь один ко многим;
• связь многие ко многим.
Рассмотрим, как такие связи между таблицами могут быть реализованы в реляционных базах данных.
Реализация связи один ко многим в теории баз данных
Связь один ко многим в реляционных базах данных реализуется тогда, когда объекту А может принадлежать или же соответствовать несколько объектов Б, но объекту Б может соответствовать только один объект А. Не совсем понятно, поэтому смотрим пример ниже.
Реализация связи один ко многим в реляционных базах данных
У нас есть таблица, в которой содержатся данные о клиентах и у нас есть таблица, в которой хранятся их телефоны. Мы можем смело утверждать, что у одного клиента может быть несколько телефонов, но в тоже время мы можем быть уверены в том, что один конкретный номер может быть только у одного клиента. Это типичный пример связи один ко многим.
Связь многие ко многим
Связь многие ко многим реализуется в том случае, когда нескольким объектам из таблицы А может соответствовать несколько объектов из таблицы Б, и в тоже время нескольким объектам из таблицы Б соответствует несколько объектов из таблицы А. Рассмотрим простой пример.
Пример связи многие ко многим
У нас есть таблица с книгами и есть таблица с авторами. Приведу два верных утверждения. Первое: одну книгу может написать несколько авторов. Второе: автор может написать несколько книг. Здесь мы наблюдаем типичную ситуацию, когда связь между таблицами многие ко многим. Такая связь (связь многие ко многим) реализуется путем добавления третьей таблицы.
Связь один к одному
Связь один к одному – самая редко встречаемая связь между таблицами. В 97 случаях из 100, если вы видите такую связь, вам необходимо объединить две таблицы в одну.
Пример связи один к одному
Таблицы будут связаны один к одному тогда, когда одному объекту таблицы А соответствует один объект таблицы Б, и одному объекту таблицы Б соответствует один объект таблицы А. Как я уже говорил: если вы видите, что связь один к одному – смело объединяйте таблицы в одну, за исключением тех случаев, когда происходит модернизация базы данных.
Например, у нас была таблица, в которой хранились данные о сотрудниках компании. Но произошли какие-то изменения в бизнес-процессе и появилась необходимость создать таблицы с теми же самыми сотрудниками, но не для всей компании, а разбив их по отделам. Таблицы отделов будут дочерними по отношению к таблице, в которой хранятся данные обо всех сотрудниках компании, и связаны такие таблицы будут связью один к одному.
Мы рассмотрели все виды связей между таблицами и то, как они реализуются в базах данных. В дальнейшем, когда мы начнем создавать свои базы данных, информация о видах связи между таблицами нам очень поможет.