Какие особенности связи 1 к 1

Система управления базами данных SQLite. Изучаем язык запросов SQL и реляционные базы данных на примере библиотекой SQLite3. Курс для начинающих.

Часть 3.2: Виды связей между таблицами в базе данных. Связи в реляционных базах данных. Отношения, кортежи, атрибуты

• 26.05.2016
• SQLite библиотека, Базы данных
• 3 комментария

Здравствуйте, уважаемые посетители сайта ZametkiNaPolyah.ru. Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. Продолжаем изучать теорию реляционных баз данных и в этой части мы познакомимся с видами и типами связей между таблицами в реляционных базах данных. Так же мы познакомимся с такими термина, как: кортеж, атрибут и отношения. Данная тема является базовой и ее понимание необходимо для работы с базами данных и для их проектирования.

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

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

Термины кортеж, атрибут и отношение в реляционных базах данных

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

Таблица с данными из базы данных World

У нас есть простая таблица City из базы данных World, в которой есть строки и столбцы. Но термины: таблица, строка, столбец – это термины стандарта SQL.
Кстати: ни одна из существующих в мире СУБД не имеет полной поддержки того или иного стандарта SQL, но и ни один стандарт SQL полностью не реализует математику реляционных баз данных.
В терминологии реляционных баз данных: таблица – это отношение (принимается такое допущение), строка – это кортеж, а столбец – атрибут. Иногда вы можете услышать, как некоторые разработчики называют строки записями. Чтобы не было путаницы в дальнейшем предлагаю использовать термины SQL.
Если рассматривать таблицу, как объект (например книга), то столбец – это характеристики объекта, а строки содержат информацию об объекте.

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

Давайте теперь рассмотрим то, как могут быть связаны таблицы в реляционных базах данных. Сразу скажу, что всего существует три вида связей между таблицами баз данных:
• связь один к одному;
• связь один ко многим;
• связь многие ко многим.
Рассмотрим, как такие связи между таблицами могут быть реализованы в реляционных базах данных.

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

Связь один ко многим в реляционных базах данных реализуется тогда, когда объекту А может принадлежать или же соответствовать несколько объектов Б, но объекту Б может соответствовать только один объект А. Не совсем понятно, поэтому смотрим пример ниже.

Реализация связи один ко многим в реляционных базах данных

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

Связь многие ко многим

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

Пример связи многие ко многим

У нас есть таблица с книгами и есть таблица с авторами. Приведу два верных утверждения. Первое: одну книгу может написать несколько авторов. Второе: автор может написать несколько книг. Здесь мы наблюдаем типичную ситуацию, когда связь между таблицами многие ко многим. Такая связь (связь многие ко многим) реализуется путем добавления третьей таблицы.

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

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

Пример связи один к одному

Таблицы будут связаны один к одному тогда, когда одному объекту таблицы А соответствует один объект таблицы Б, и одному объекту таблицы Б соответствует один объект таблицы А. Как я уже говорил: если вы видите, что связь один к одному – смело объединяйте таблицы в одну, за исключением тех случаев, когда происходит модернизация базы данных.
Например, у нас была таблица, в которой хранились данные о сотрудниках компании. Но произошли какие-то изменения в бизнес-процессе и появилась необходимость создать таблицы с теми же самыми сотрудниками, но не для всей компании, а разбив их по отделам. Таблицы отделов будут дочерними по отношению к таблице, в которой хранятся данные обо всех сотрудниках компании, и связаны такие таблицы будут связью один к одному.

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

Еще записи о создании сайтов и их продвижении, базах данных, IT-технология и сетевых протоколах

• Часть 11.2: Ограничения уровня таблицы в базах данных SQLite3
• Часть 11.4: Внешние ключи в базах данных SQLite: FOREIGN KEY в SQLite3
• Часть 3.3: Ключи и ключевые атрибуты в базах данных
• Тема 3: Теория реляционных баз данных
• Тема 10: Работа с таблицами в базах данных SQLite3
• Базы данных. Виды и типы баз данных. Структура реляционных баз данных. Проектирование баз данных. Сетевые и иерархические базы данных
• Часть 3.10: Словарь терминов реляционных баз данных
• Часть 11.7: Индексы в базах данных SQLite. Индексация таблиц в SQLite3. Алгоритм B-дерева в базах данных

Возможно, эти записи вам покажутся интересными

Related Posts

Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. При…

Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем рубрику реляционные базы данных и начинаем новый раздел библиотека SQLite. Данной…

Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. Работать…

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

This article has 3 comments

Deer-door 08.10.2020 Reply
> В 97 случаях из 100 Как производилось измерение частоты случаев? Каковы были условия измерения?
Автор жжёт 01.03.2022 Reply

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

Кирилл 24.03.2022 Reply

Из-за вашего комментария ко мне домой приехало два наряда пожарных, тушили весь вечер. Не нужно так, я очень впечатлительный. А теперь по факту: 1. Может вы все-таки подумаете, почему я так ответил, с учетом общей направленности всего цикла публикаций по SQLite. Может человеку сперва нужно понять как вещь работает в общем, прежде чем начинать к ней приделывать костыли? 2. Если хотите высказаться конструктивно, напишите свою публикацию с примерами и пояснениями, готов опубликовать со ссылками на вас и ваши ресурсы, если они не противоречат законам РФ.

Руководство по проектированию реляционных баз данных

Пример связи один-ко-многим — это связь, которая существует между матерью и ее детьми. Мать может иметь множество детей, но каждый ребенок может иметь только одну мать.

(Технически лучше говорить о женщине и ее детях вместо матери и ее детях потому, что, в контексте связи один-ко-многим, мать может иметь 0, 1 или множество потомков, но мать с 0 детей не может считаться матерью. Но давайте закроем на это глаза, хорошо?)

Когда одна запись в таблице А может быть связана с 0, 1 или множеством записей в таблице B, вы имеете дело со связью один-ко-многим. В реляционной модели данных связь один-ко-многим использует две таблицы.

Схематическое представление связи один-ко-многим. Запись в таблице А имеет 0, 1 или множество ассоциированных ей записей в таблице B.

Как опознать связь один-ко-многим?

Если у вас есть две сущности спросите себя:

1. Сколько объектов из B могут относится к объекту A?
2. Сколько объектов из A могут относиться к объекту из B?

Если на первый вопрос ответ — множество, а на второй — один (или возможно, что ни одного), то вы имеете дело со связью один-ко-многим.

Некоторые примеры связи один-ко-многим:

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

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

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

Связь многие-ко-многим

Связь многие-ко-многим — это связь, при которой множественным записям из одной таблицы (A) могут соответствовать множественные записи из другой (B).

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

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

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

Создание связи многие-ко-многим

Связь многие-ко-многим создается с помощью трех таблиц.

Две таблицы: источника и одна соединительная таблица. Первичный ключ соединительной таблицы A_B — составной. Она состоит из двух полей, двух внешних ключей, которые ссылаются на первичные ключи таблиц A и B.

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

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

Таблицы «о пиве»

Таблицы выше связывают поставщиков и пиво связью многие-ко-многим, используя соединительную таблицу. Обратите внимание, что пиво ‘Gentse Tripel’ (157) поставляют Horeca Import NL (157, AC001), Jansen Horeca (157, AB899) и Petersen Drankenhandel (157, AC009). И vice versa, Petersen Drankenhandel является поставщиком 3 видов пива из таблицы, а именно: Gentse Tripel (157, AC009), Uilenspiegel (158, AC009) и Jupiler (163, AC009).

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

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

Другой пример связи многие-ко-многим: заказ билетов в отеле

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

Соединительная таблица связи многие-ко-многим имеет дополнительные поля.

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

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

Связь один-к-одному

В связи один-к-одному каждый блок сущности A может быть ассоциирован с 0, 1 блоком сущности B.

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

В одной таблице

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

В отдельных таблицах

В редких случаях связь один-к-одному моделируется с использованием двух таблиц. Такой вариант иногда необходим, чтобы преодолеть ограничения РСУБД или с целью увеличения производительности (например, иногда — это вынесение поля с типом данных blob в отдельную таблицу для ускорения поиска по родительской таблице). Или порой вы можете решить, что вы хотите разделить две сущности в разные таблицы в то время, как они все еще имеют связь один-к-одному. Но обычно наличие двух таблиц в связи один-к-одному считается дурной практикой.

Примеры связи один-к-одному

• Люди и их паспорта. Каждый человек в стране имеет только один действующий паспорт и каждый паспорт принадлежит только одному человеку.

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

Какой же вид связи вам нужен?

Примеры связей таблиц на практике. Когда какие-то данные являются уникальными для конкретного объекта, например, человек и номера его паспортов, такая связь — связь один-ко-многим. Т.е. в одной таблице мы имеем список неких людей, а в другой таблице у нас есть перечисление номеров паспортов этого человека (напр., паспорт страны проживания и загранпаспорт). И эта комбинация данных уникальна для каждого человека. То есть, у каждого человека может быть несколько номеров паспортов, но у каждого паспорта может быть только один владелец. Итого: нужны две таблицы.

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

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

А когда у вас есть набор уникальных данных, которые имеют отношение только друг к другу, то храните все в одной таблице. Ваш выбор — связь один-к-одному. Например, у вас есть небольшая коллекция автомобилей и вы хотите хранить информацию о них (цвет, марка, год выпуска и пр.).

Связь один — к — одному (1:1)

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

К связи один — к — одному (1:1) относят такое взаимодействие структурных элементов, у которых один экземпляр одного элемента может быть связан не более чем с одним экземпляром другого элемента.

Очень важно правильно интерпретировать соответствующие структурные элементы и связи между ними. Рассматривая связь между элементами «Паспортные данные» и «Сотрудник», нужно задаться вопросом: «Нужно ли хранить в базе данных сведения о паспортных данных, если сотрудник сменил паспорт, или паспортные данные должны заменяться?»

Пример данных, по паспортным сведениям, сотрудников

Серия: 45 01 № 657954, выдан ОВД «Выхино» 25.01.2002

Серия: 43 02 № 324891, выдан ОВД «Митино» 15.05.1999

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

Особенности предметной области, связанные с паспортными данными, указывают на тот факт, что каждый паспорт является уникальным и совокупность его сведений встречается только один раз и только у одного человека (сотрудника). При этом, совокупность серии и номера можно рассматривать набором атрибутов, которые составляют уникальную комбинацию значений для каждого паспорта. Можно, конечно, проанализировать все данные по паспортным данным в документе «Личный листок» каждого сотрудника организации, но это может оказаться достаточно проблематичной задачей по причине больших объемов анализируемых данных или конфиденциальности сведений. Именно эти факторы требуют от разработчика хорошего знания предметной области и особенностей работы с определенными данными. Но даже знания о предметной области не дадут ответ на поставленный вопрос о возможности наличия нескольких паспортных сведений у одного сотрудника. Здесь важно иметь информацию от сотрудников организации, получаемую в процессе анализа предметной области и деятельности в организации.

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

• • для одного сотрудника будут храниться сведения только по одному паспорту (это определяется особенностями хранения информации по сотрудникам в рассматриваемой организации);
• • один паспорт будет идентифицировать только одного сотрудника (это определяется особенностями работы с паспортными данными в предметной области).

В итоге, для рассматриваемого примера связь между структурными элементами «Сотрудник» и «Паспортные данные» можно представить, как на рисунке 2.38.

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

Создание связи «один к одному»

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

Связь «один-к-одному» — это связь между информацией из двух таблиц, когда каждая запись используется в каждой таблице только один раз. Например, связь типа «один-к-одному» может использоваться между сотрудниками и их служебными автомобилями. Каждый работник указан в таблице «Сотрудники» только один раз, как и каждый автомобиль в таблице «Служебный транспорт».

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

Общие сведения о создании связи «один к одному»

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

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

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

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

Инструкции по созданию связи типа «один к одному»

Вы можете создать связь «один-к-одному», добавив в таблицу поле подстановки. (Инструкции см. в статье Создание таблиц и назначение типов данных.) Например, чтобы указать, какие автомобили назначены определенным сотрудникам, вы можете добавить в таблицу «Сотрудники» поле «Код автомобиля». После этого воспользуйтесь мастером подстановок для создания связи между полями.

1. Откройте таблицу.
2. В режиме конструктора добавьте новое поле, выберите значение Тип данных, а затем запустите мастер подстановок.
3. В мастере по умолчанию выбран поиск значений в другой таблице, поэтому нажмите кнопку Далее.
4. Выберите таблицу с ключом (обычно первичным), который вы хотите добавить в первую таблицу, и нажмите кнопку Далее. В рассмотренном примере следует выбрать таблицу «Служебный транспорт».
5. Добавьте в список Выбранные поля поле с необходимым ключом. Нажмите кнопку Далее.
6. Задайте порядок сортировки и, при необходимости, измените ширину поля.
7. В последнем окне установите флажок Включить проверку целостности данных и нажмите кнопку Готово.