Типы связей в реляционных базах данных
Примечание:
Во всех статьях текущей категории уроков по SQL используются примеры и задачи, основанные на учебной базе данных.
Приступая к изучению данного материала, рекомендуется ознакомиться с описанием учебной БД.
Практически всегда БД не ограничивается одной таблицей. Сложно представить себе какой-либо бизнес-процесс на предприятии, который мог бы сконцентрироваться только на одном предмете в плане информации.
Рассмотрим пример учебной базы данных. Имеется отдел, который занимается обработкой звонков, поступающих на различные линии. Линии обслуживаются конкретными операторами. Операторы состоят в разных группах под присмотром супервайзеров.
Только из данного краткого описания можно выделить несколько самостоятельных объектов:
- Телефонные линии обслуживания;
- Сотрудники отдела;
- Должности сотрудников;
- Группы, по которым распределены сотрудники;
- Звонки.
Ознакомившись с диаграммой базы данных, можно обратить внимание на то, что некоторая информация из одних таблиц присутствует в других, т.е. между ними имеются связи.
В нашем конкретном случае, все таблицы можно соединить между собой. Чтобы понять, как это правильно сделать, необходимо рассмотреть типы связей.
Логику соединения таблиц в БД важно понять с самого начала изучения SQL, так как наверняка Вы не будете писать запросы только к одной таблице.
Всего существует 3 типа связей:
Примечание:
В данном материале обозначения связей приводятся на примере MS SQL Server. В иных СУБД они могут обозначаться по-разному, но у Вас не должно возникнуть проблем с определением их типа, т.к. они либо очень похожи, либо интуитивно понятны.
Связь «Один к одному»
Связь один к одному образуется, когда ключевой столбец (идентификатор) присутствует в другой таблице, в которой тоже является ключом либо свойствами столбца задана его уникальность (одно и тоже значение не может повторяться в разных строках).
На практике связь «один к одному» наблюдается не часто. Например, она может возникнуть, когда требуется разделить данных одной таблицы на несколько отдельных таблиц с целью безопасности.
В учебной безе данных нет подходящего примера, но гипотетически могла бы существовать необходимость разделения таблицы сотрудников.
Пример:
Представьте, что базой данных пользуются несколько менеджеров и аналитиков, а таблица «Сотрудники» содержит те же столбцы, что и учебная база. Следовательно, доступ к персональным данным может получить любой из упомянутых работников.
Чтобы устранить возможность утечки конфиденциальной информации, принимается решение о переносе информации паспортных данных в отдельную таблицу, доступ к которой предоставляется ограниченному кругу лиц.
Связь «Один ко многим»
В типе связей один ко многим одной записи первой таблицы соответствует несколько записей в другой таблице.
Рассмотрим связь учебной базы данных между должностями и сотрудниками, которая относится к рассматриваемому типу.
Записи должностей в таблице «Должность» уникальны, так как нет смысла повторно создавать имеющуюся запись. Записи в таблице «Сотрудники» также уникальны, но несколько различных сотрудников могут находиться на одинаковой должностной позиции.
Символ ключа на конце связи указывает, что таблица, к которой этой конец прилегает, находится на стороне «один» (связанный столбец является первичным ключом), а символ бесконечности находится на стороне «многие» (такой столбец является внешним ключом).
Связь «Многие ко многим»
Если нескольким записям из одной таблицы соответствует несколько записей из другой таблицы, то такая связь называется «многие ко многим» и организовывается посредством связывающей таблицы.
В нашей базе подобное наблюдается только между таблицами с сотрудниками и линиями.
Из диаграммы видно, что имеются две связи «один ко многим» (один сотрудник может обрабатывать несколько телефонных линий, и одну линию могут обрабатывать несколько сотрудников), но в совокупности они образуют связь «многие ко многим».
Для чего все это нужно?
Связи выполняют более важную роль, чем просто информация размещения данных по таблицам. Прежде всего они требуются разработчикам для поддержания целостности баз данных.
Правильно настроив связи, можно быть уверенным, что ничего не потеряется.
Представьте, что Вы решили удалить одну из групп в таблице учебной базы данных. Если бы связи не было, то для тех сотрудников, которые к ней были определены, остался идентификатор несуществующей группы. Связь не позволит удалить группу, пока она имеется во внешних ключах других таблиц. Для начала следовало определить сотрудников в другие имеющиеся или новые группы, а только затем удалить ненужную запись. Поэтому связи называют еще ограничениями.
Какие особенности связи 1 к 1
Проверьте, как это работает!
Что такое связь «один к одному»?
Связи «один к одному» часто используются для получения важных данных, необходимых для ведения бизнеса.
Связь «один-к-одному» — это связь между информацией из двух таблиц, когда каждая запись используется в каждой таблице только один раз. Например, связь типа «один-к-одному» может использоваться между сотрудниками и их служебными автомобилями. Каждый работник указан в таблице «Сотрудники» только один раз, как и каждый автомобиль в таблице «Служебный транспорт».
Связи «один-к-одному» можно использовать, если у вас есть таблица со списком элементов, но конкретные сведения о них зависят от типа. Например, у вас может быть таблица контактов, в которой некоторые сотрудники являются сотрудниками, а другие — субподрядчиками. Для сотрудников нужно знать их номера, расширения и другие ключевые сведения. Для субподрядчиков нужно знать, помимо прочего, название компании, номер телефона и тариф на выставление счета. В этом случае нужно создать три отдельные таблицы — «Контакты», «Сотрудники» и «Субподрядчики», а затем создать связь «один-к-одному» между таблицами «Контакты» и «Сотрудники» и связь «один-к-одному» между таблицами «Контакты» и «Субподрядчики».
Общие сведения о создании связи «один к одному»
Связи «один-к-одному» создаются путем связывания индекса первой таблицы, в качестве которого обычно выступает первичны ключ, с индексом второй таблицы, причем их значения совпадают. Пример:
Часто бывает, что лучший способ создать подобную связь — назначить вторичной таблице функцию поиска значений из первой таблицы. Например, вы можете сделать поле «Код автомобиля» в таблице «Сотрудники» полем подстановки, которое будет искать значение индекса «Код автомобиля» в таблице «Служебный транспорт». Таким образом исключается случайное добавление кода автомобиля, который на самом деле не существует.
Важно: При создании связи «один-к-одному» следует тщательно обдумать, требуется ли включать для нее обеспечение целостности данных.
Целостность данных помогает Access поддерживать порядок данных путем удаления связанных записей. Например, при удалении сотрудника из таблицы «Сотрудники» также удаляются записи о его льготах из таблицы «Льготы». Но в некоторых связях, таких как в этом примере, целостность данных не имеет смысла: если удалить сотрудника, мы не хотим, чтобы автомобиль удалялся из таблицы «Автомобиль компании», так как он по-прежнему будет принадлежать компании и будет назначен другому сотруднику.
Инструкции по созданию связи типа «один к одному»
Вы можете создать связь «один-к-одному», добавив в таблицу поле подстановки. (Инструкции см. в статье Создание таблиц и назначение типов данных.) Например, чтобы указать, какие автомобили назначены определенным сотрудникам, вы можете добавить в таблицу «Сотрудники» поле «Код автомобиля». После этого воспользуйтесь мастером подстановок для создания связи между полями.
В режиме конструктора добавьте новое поле, выберите значение Тип данных, а затем запустите мастер подстановок.
В мастере по умолчанию выбран поиск значений в другой таблице, поэтому нажмите кнопку Далее.
Выберите таблицу с ключом (обычно первичным), который вы хотите добавить в первую таблицу, и нажмите кнопку Далее. В рассмотренном примере следует выбрать таблицу «Служебный транспорт».
Добавьте в список Выбранные поля поле с необходимым ключом. Нажмите кнопку Далее.
Задайте порядок сортировки и, при необходимости, измените ширину поля.
В последнем окне установите флажок Включить проверку целостности данных и нажмите кнопку Готово.
Связь один — к — одному (1:1)
Тип связи один — к — одному используется, когда необходимо отделить некоторый набор сведений, однозначно связанный с конкретным экземпляром исходного структурного элемента. Так, например, если есть необходимость выделить паспортные данные в отдельный структурный элемент, чтобы обеспечить разграничение прав доступа к соответствующим сведениям, то между элементом "Паспортные данные" и "Сотрудник" будет установлена связь один — к — одному.
К связи один — к — одному (1:1) относят такое взаимодействие структурных элементов, у которых один экземпляр одного элемента может быть связан не более чем с одним экземпляром другого элемента.
Очень важно правильно интерпретировать соответствующие структурные элементы и связи между ними. Рассматривая связь между элементами "Паспортные данные" и "Сотрудник", нужно задаться вопросом: "Нужно ли хранить в базе данных сведения о паспортных данных, если сотрудник сменил паспорт, или паспортные данные должны заменяться?"
Пример данных, по паспортным сведениям, сотрудников
Серия: 45 01 № 657954, выдан ОВД "Выхино" 25.01.2002
Серия: 43 02 № 324891, выдан ОВД "Митино" 15.05.1999
Правильная оценка возможных значений по связям между структурными элементами является залогом дальнейшего проектирования базы данных. Анализ предметной области в рассматриваемом примере показал, что каждому сотруднику устанавливается в соответствие только один вариант паспортных данных. Эго очевидно из структуры табл. 2.11 с данными предметной области. В этой таблице видно, что каждый сотрудник представлен только один раз и каждому представлены паспортные данные, которые также не дублируются.
Особенности предметной области, связанные с паспортными данными, указывают на тот факт, что каждый паспорт является уникальным и совокупность его сведений встречается только один раз и только у одного человека (сотрудника). При этом, совокупность серии и номера можно рассматривать набором атрибутов, которые составляют уникальную комбинацию значений для каждого паспорта. Можно, конечно, проанализировать все данные по паспортным данным в документе "Личный листок" каждого сотрудника организации, но это может оказаться достаточно проблематичной задачей по причине больших объемов анализируемых данных или конфиденциальности сведений. Именно эти факторы требуют от разработчика хорошего знания предметной области и особенностей работы с определенными данными. Но даже знания о предметной области не дадут ответ на поставленный вопрос о возможности наличия нескольких паспортных сведений у одного сотрудника. Здесь важно иметь информацию от сотрудников организации, получаемую в процессе анализа предметной области и деятельности в организации.
Поскольку в рассматриваемом варианте ответом является тот факт, что каждый сотрудник будет в базе данных описываться только одним набором сведений о паспортных данных, то можно сказать, что:
- • для одного сотрудника будут храниться сведения только по одному паспорту (это определяется особенностями хранения информации по сотрудникам в рассматриваемой организации);
- • один паспорт будет идентифицировать только одного сотрудника (это определяется особенностями работы с паспортными данными в предметной области).
В итоге, для рассматриваемого примера связь между структурными элементами "Сотрудник" и "Паспортные данные" можно представить, как на рисунке 2.38.
В дальнейшем, при анализе связи в момент формирования модели базы данных, разработчик определит дополнительные составляющие: смысловую нагрузку связи, количество связываемых экземпляров структурных элементов (мощность, кардинальность), возможность хранения пустого значения и т.д.